Programming.NET

Using MySQL and LINQ to SQL in C# ASP .NET

modified | by

Introduction

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.

The Old Way of Doing Things

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using System;
using System.Data;
using MySql.Data.MySqlClient;

MySqlConnection MyConnection = null;
MySqlDataReader MyReader = null;

// Create the SQL connection.
MyConnection = new MySqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"))
MyConnection.Open();

// Create the command.
MySqlCommand MyCommand = new MySqlCommand("SELECT ID, NAME, AGE FROM PEOPLE WHERE ID > 5", MyConnection);

// Execute the command
MyReader = MyCommand.ExecuteReader();

// ...

MyReader.Close();
MyConnection.Close();

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.

1
2
3
4
5
6
7
8
using (MyDataContext context = new MyDataContext())
{
   var results = from s in context.People
                 where s.ID > 5
                 select s;

   return results.ToList();
}

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.

Introducing 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.

  1. Download DbLinq.

  2. Run the LINQ to SQL generation tool DbMetal.exe as follows:

dbmetal /server:1.2.3.4 /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.

  1. 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).
1
2
3
4
5
6
7
8
9
10
11
using System;
using System.Data;
using System.Data.Linq.Mapping;
using System.Diagnostics;
using System.Reflection;
using DbLinq.Data.Linq;
using DbLinq.Vendor;
using System.ComponentModel;
// Add these libraries //
using System.Configuration;
using MySql.Data.MySqlClient;
  1. Modify the class declaration to inherit from the MySQLDataContext class, as follows:
1
2
3
public partial class PeopleDataContext : DbLinq.MySql.MySqlDataContext
{
}
  1. Finally, delete the constructors from the DataContext class and add the following constructor:
1
2
3
4
public PeopleDataContext()
    : base(new MySqlConnection(ConfigurationManager.ConnectionStrings["people"].ConnectionString))
{
}

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:

1
2
3
4
 <connectionStrings>
  <clear/>
  <add providerName="MySQLProv" name="people" connectionString="Server=1.2.3.4;User=dbuser;Password=password;database=people"/>
</connectionStrings>

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.

Reading a MySQL Table with LINQ

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public static List<Person> GetPeople()
{
    using (PeopleDataContext context = new PeopleDataContext())
    {
        var results = from s in context.Person
                      orderby s.LastName ascending
                      select s;

        return results.Take(25).ToList();
    }
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        lstMyGridView.DataSource = GetPeople();
        lstMyGridView.DataBind();
    }
}

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().

Inserting into a MySQL Table with LINQ

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public static Person AddPerson(Person person)
{
    using (PeopleDataContext context = new PeopleDataContext())
    {
        context.Person.InsertOnSubmit(person);
        context.SubmitChanges();

        return person;
    }           
}

protected void btnAdd_Click(object sender, EventArgs e)
{
    // Create a LINQ to SQL class to fill the properties.
    Person person = new Person();
    person.FirstName = txtFirstName.Text;
    person.LastName = txtLastName.Text;
    person.Age = Convert.ToInt32(txtAge.Text);

    person = AddPerson(person);
}

A Simple Searching Query with LINQ to SQL and MySQL

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public static List<Person> GetPeople(string keywords)
{
    using (PeopleDataContext context = new PeopleDataContext())
    {
        var results = from s in context.Person
                      where s.FirstName.ToLower().Contains(keywords.ToLower()) ||
                            s.LastName.ToLower().Contains(keywords.ToLower())
                      orderby s.LastName ascending
                      select s;

        return results.Take(25).ToList();
    }
}

protected void btnSearch_Click(object sender, EventArgs e)
{
    lstMyGridView.DataSource = GetPeople(txtKeywords.Text);
    lstMyGridView.DataBind();
}

Conclusion

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.

About the Author

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.

Share