LINQ is one of the most popular Microsoft technologies for reading and writing to SQL Server databases. LINQ to SQL builds upon LINQ technology to allow working with business objects, generated from the database tables. Tranditionally, LINQ to SQL is compatible with SQL Server databases and does not easily support other database formats. Since LINQ is a leap forward for developers accessing databases, it’s important to be able to use LINQ with other popular database formats as well. This article describes how to use LINQ to SQL with a MySQL database in C# ASP .NET.
Prior to LINQ, working with databases from C# ASP .NET web applications involved handling database connections, SQLConnection and SqlCommand objects, and working with data readers. While this technique provided a lower level of approach to database interactivity from C#, it was also more prone to error (ie. leaving connections open, reading wrongly-typed database column parameters, etc). You may have used the following code to access a database:
The above code illustrates one way to access a MySQL database, prior to using LINQ. However, using LINQ, we can execute the same SQL queries using strongly typed objects and automatic connection handling.
The above code illustrates a simple LINQ query to return a set of rows. While LINQ to SQL helps refine our database access code in C# ASP .NET, it only supports Microsoft SQL Server databases. With the popularity of MySQL, it can be incredibly handly to utilize LINQ with MySQL as well. To handle this, we’ll need to obtain a MySQL data context provider for LINQ. There are a few LINQ to SQL data context providers available, but we’ll start with DbLinq.
DbLinq is a LINQ to SQL data context provider and allows you to create LINQ to SQL business objects from a MySQL database and perform LINQ queries directly against MySQL tables. Utilizing LINQ, it functions in the same way as a typical SQL Server data layer.
Setting up DbLinq takes a few steps, including the generation of the data context file for your MySQL database. The following steps will walk you through the process of generating the data context, including it in your project, and writing LINQ queries for MySQL.
Run the LINQ to SQL generation tool DbMetal.exe as follows:
dbmetal /server:126.96.36.199 /user:dbuser /password:password /provider:MySql /database:people /language:C#
A LINQ to SQL data context class will be created from your MySQL database. You can now include this file in your Visual Studio 2008 project, preferably in a Data class library project inside the solution.
- You’ll need to tweak the generated class slightly, to allow it to work properly with MySQL. First, edit the “using” section of the class to include the last two libraries below. You’ll also need to reference System.Configuration and MySql.Data (from the MySQL .NET Connector in C:\Program Files\MySQL\MySQL Connector Net 5.2.5\Binaries.NET 2.0\MySql.Data.dll).
- Modify the class declaration to inherit from the MySQLDataContext class, as follows:
- Finally, delete the constructors from the DataContext class and add the following constructor:
This new constructor allows you to use the data context class without having to worry about the connection string each time. Of course, in your web.config file you’ll need to add the following connection string with your own database settings:
With the MySQL data context class created, you now have a set of LINQ to SQL business objects that you can use to read and write to your MySQL database with LINQ queries.
One of the first tasks you’ll want to perform is to read data from the MySQL table, such as in the case of populating a GridView control with the information from the table. You could do so with the following code:
The above LINQ query in your MySQL database would result in the following SQL language:
SELECT ID, FIRSTNAME, LASTNAME, AGE FROM PERSON ORDER BY LASTNAME DESC LIMIT 0,25
You can actually display the query language in the Debug Console, Output window, or other streams via the context.Log property. For example, context.Log = new DebuggerWriter().
Adding records to a MySQL table with LINQ2SQL can also be done with the generated data context class. Note in the code below, we’re passing in a LINQ to SQL business object, generated by DbLinq from the MySQL table, just as you would with an SQL Server database:
Since LINQ is being used to access the MySQL database from C# ASP .NET, we have a large variety of query types that we can execute when retrieving data. Below is an example of a query to select people by keyword:
While LINQ to SQL is traditionally made to generate SQL Server business objects and access SQL Server databases from C# ASP .NET, we can also use it to access other popular databases, including MySQL, through the help of a data context generation tool, such as DbLinq. Utilizing LINQ and LINQ to SQL to access database tables provides for strongly-typed database column access and easier handling of database connectivity. By using LINQ and LINQ to SQL in your MySQL database access layer, you can help make your C# ASP .NET web applications more robust, expandable, and less prone to error.
This article was written by Kory Becker, software developer and architect, skilled in a range of technologies, including web application development, machine learning, artificial intelligence, and data science.