Configure MySql .Net Connector with Entity Framework
To communicate from .NET code to MySQL database using Entity Framework we installed MySql .NET Connector. It worked fine out of the box on developer's workstations but once deployed to hosted web server application started to throw following error:
[NotSupportedException: Unable to determine the provider name for connection of type 'MySql.Data.MySqlClient.MySqlConnection'.] System.Data.Entity.ModelConfiguration.Utilities.DbConnectionExtensions.GetProviderInvariantName(DbConnection connection)
System.Data.Entity.ModelConfiguration.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest)
System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
System.Data.Entity.Internal.Linq.InternalSet`1.ActOnSet(Action action, EntityState newState, Object entity, String methodName)
System.Data.Entity.Internal.Linq.InternalSet`1.Add(Object entity)
System.Data.Entity.DbSet`1.Add(TEntity entity)
Apparently, somehow MySQL .NET provider was not registered on server although installation was done properly and all assemblies were present in GAC. Since on shared hosting we did not have access to machine.config by adding the same registration entry to the application web.config file resolved the issue:
<system.data>
<DbProviderFactories>
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.4.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
</DbProviderFactories>
</system.data>
Important: 1) Make sure to register the same version that is installed on the server 2) Provide precise fully qualified name in the type attribute. For example, adding extra space between MySql.Data.MySqlClient.MySqlClientFactory and MySql.Data will make this entry useless and will result in the same error
What is the use of command objects in .NET
They are used to connect connection object to Data reader or dataset. Following are the methods provided by command object:-
• ExecuteNonQuery: -
Executes the command defined in the Command Text property against the connection defined in the Connection property for a query that does not return any row (an UPDATE, DELETE, or INSERT). Returns an Integer indicating the number of rows affected by the query.
• ExecuteReader: -
Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns a "reader" object that is connected to the resulting row set within the database, allowing the rows to be retrieved.
• ExecuteScalar: -
Executes the command defined in the Command Text property against the connection defined in the Connection property. Returns only single value (effectively the first column of the first row of the resulting row set any other returned columns and rows are discarded. It is fast and efficient when only a "singleton" value is required
ASP.NET 2.0 Interview Questions
24. While developing ASP.NET 2.0 web application you have a DataSet containing a Customer DataTable and an Order DataTable. You want to easily navigate from an Order DataRow to the Customer who placed the order. What object will allow you to easily navigate from the Order to the Customer? A. The DataColumn object B. The DataTable object C. The DataRow object D. The DataRelation object
25. Which of the following is a requirement when merging modified data into a DataSet? A. A primary key must be defined on the DataTable objects. B. The DataSet schemas must match in order to merge. C. The destination DataSet must be empty prior to merging. D. A DataSet must be merged into the same DataSet that created it.
26. You are working with a DataSet and want to be able to display data, sorted different ways. How do you do so? A. Use the Sort method on the DataTable object. B. Use the DataSet object’s Sort method. C. Use a DataView object for each sort. D. Create a DataTable for each sort, using the DataTable object’s Copy method, and then Sort the result.
27. Which of the following ways can you proactively clean up a database connection’s resources? A. Execute the DbConnection object’s Cleanup method. B. Execute the DbConnection object’s Close method. C. Assign Nothing (C# null) to the variable that references the DbConnection object. D. Create a using block for the DbConnection object.
29. What event can you subscribe to if you want to display information from SQL Print statements? A. InfoMessage B. MessageReceived C. PostedMessage D. NewInfo
30. To perform asynchronous data access, what must be added to the connection string? A. BeginExecute=true B. MultiThreaded=true C. MultipleActiveResultSets=trueD. Asynchronous=true
31. Which class can be used to create an XML document from scratch? A. XmlConvert B. XmlDocument C. XmlNew D. XmlSettings
32. Which class can be used to perform data type conversion between .NET data types and XML types? A. XmlType B. XmlCast C. XmlConvert D. XmlSettings
ADO.NET and Database Questions
- What is the wildcard character in SQL?
Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’. - What is the role of the DataReader class in ADO.NET connections?
It returns a read-only, forward-only rowset from the data source. A DataReader provides fast access when a forward-only sequential read is needed. - What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix. OLE-DB.NET is a .NET layer on top of the OLE layer, so it’s not as fastest and efficient as SqlServer.NET. - What connections does Microsoft SQL Server support?
Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and password). - Between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction. - What does the Dispose method do with the connection object?
Deletes it from the memory.
To Do: answer better. The current answer is not entirely correct. - What is a pre-requisite for connection pooling?
Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings. The connection string must be identical. - What does the Initial Catalog parameter define in the connection string?
The database name to connect to. - Explain ACID rule of thumb for transactions.
A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.
ASP.NET Interview Questions on Database Administrator
Why is "Connecting to SQL Server using Integrated Security" considered a best practice?
Connecting to SQL Server using integrated security instead of using an explicit user name and password, helps avoid the possibility of the connection string being compromised and your user ID and password being exposed.
What are the best practices to follow to secure connection strings in an ASP.NET web application?
1. Always store connection strings in the site's Web.config file. Web.config is very secure. Users will not be able to access web.config from the browser.
2. Do not store connection strings as plain text. To help keep the connection to your database server secure, it is recommended that you encrypt connection string information in the configuration file.
3. Never store connection strings in an aspx page.
4. Never set connection strings as declarative properties of the SqlDataSource control or other data source controls.
What is Script injection?
A script injection attack attempts to send executable script to your application with the intent of having other users run it. A typical script injection attack sends script to a page that stores the script in a database, so that another user who views the data inadvertently runs the code.
What is the advantage of storing an XML file in the applications App_Data folder? The contents of the App_Data folder will not be returned in response to direct HTTP requests.
What is SQL injection?
A SQL injection attack attempts to compromise your database by creating SQL commands that are executed instead of, or in addition to, the commands that you have built into your application.
What are the best practices to keep in mind when accepting user input on a web application?
1. Always use validation controls whenever possible to limit user input to acceptable values.
2. Always check the IsValid property of the aspx page. Run the server side code only if the IsValid property value is true. A value of false means that one or more validation controls have failed a validation check.
3. Always perform server side validation irrespective of client side validation being performed or not. This will protect your web application even if the client has by passed the client side validation by disabling javascript in the web browser.
4. Also make sure to re validate user input in the business logic layer of your application.
What are the steps to follow to avoid SQL Injection attacks?
Always use parameterized queries or stored procedures instead of creating SQL commands by concatenating strings together.
What are the steps to follow to avoid Script Injection attacks?
1. Encode user input with the HtmlEncode method. This method turns HTML into its text representation.
2. If you are using the GridView control with bound fields, set the BoundField object's HtmlEncode property to true. This causes the GridView control to encode user input when the row is in edit mode.
Can you encrypt view state data of an aspx page?
Yes, you encrypt view state data of an aspx page by setting the page's ViewStateEncryptionMode property to true.
