LINQ with MySQL using DbLinq

Microsoft’s Language INtegrated Query, or LINQ extensions to C# allow SQL-like queries to be run on a variety of data sources, ranging from simple types such as arrays and lists to more complex data types such as XML files and databases. The built-in support for databases extends only as far as Microsoft’s own SQL Server, which is fair enough considering that’s their main database product. However, I’ve used MySQL for my own modest database requirements for many years, largely because it’s free and does everything I need.

We’ve already seen how to connect to MySQL from within a C# program, but in that example, we interacted with the database by constructing SQL commands as strings within C# and then using interface methods to pass these commands to the MySQL database, which took care of making the actual changes to the data. The main purpose of LINQ is to move the data processing commands into the C# language directly.

Doing this, however, does require that there is a lot of underlying code that handles the connection between C# and the database. LINQ as provided by Visual Studio contains all the tools you need to interact with regular data structures, XML and SQL Server, but if you want to talk to MySQL, you’ll need a third-party package to handle the interaction.

One such package that I’ve used only briefly is DbLinq, which provides interfaces between LINQ and not only MySQL, but a number of other popular databases as well. Since I’m interested only in MySQL, that’s all I’ll look at here.

Using DbLinq is fairly straightforward, although the lack of documentation can make it a bit of a trial to get running. I’ll run through the steps that I followed here, although if you’re reading this some time after a new version has come out, things may have changed.

First, go to the DbLinq site, follow the link to the zipped releases downloads page, and then get the zip file containing the source code (with an ‘src’ in its name). This is useful since this contains a ready-made Visual Studio project with several examples for the various databases supported. Unzip this file, go into the src folder and load DbLinq.sln in Visual Studio. Build the solution (which for me ran without errors).

If you want to run the MySQL example provided with the zip file, you’ll need to create the Northwind database on your local MySQL installation. An SQL file is provided which will allow you to do this. In the extracted folder from the zip file, go to the folder examples\DbLinq.MySql.Example\sql where you’ll find a file called create_Northwind.sql. You can use this file to create the database by opening a cmd window, cd to the folder where the SQL file is, then running mysql in command-line mode. You can then give the command “source create_Northwind.sql”, and this should create the database. Alternatively, there are several front-end programs such as sqlyog which can be used to load the file in a GUI.

Once you’ve got the Northwind database installed, you should be able to run the example program. In Visual Studio’s Solution Explorer, open the ‘examples’ folder and set DbLinq.MySql.Example as the startup project.  Open the Program.cs file and find the definition of the string connStr in the Main() method.  You’ll need to edit this to provide the login credentials for your own MySQL database. Once you’ve done that, you should be able to run the example and have it show you the results of a few LINQ commands.

If you know a bit of LINQ, you can play around with the Program class’s code at this point and experiment with accessing the Northwind database (although I found the program crashed when attempting to remove an item from the database). Obviously, though, you’ll want to use LINQ with your own MySQL databases at some point, so we’ll need to examine how you do that.

I mentioned above that Visual Studio provides a lot of background code to make LINQ work with various data sources, and that to get it to work with MySQL, you’ll need to provide this code. You might notice in the Northwind example that there is a large file called Northwind.cs, and if you look at the top of that file you’ll see it’s automatically generated code from a program called DbMetal. This is the one fiddly bit about using DbLinq: you’ll need to run DbMetal externally (outside Visual Studio) in order to generate the required code for the database you want to access.

DbMetal reads the structure of your database from MySQL and generates the interface code required to get LINQ to work with that database. Since each database has a different structure (different tables and so on), you’ll need to run DbMetal for each database you want to use. You’ll need to run it only once per database, unless you change the structure of the database by adding or deleting tables or adding or deleting columns from tables.

You’ll find a .bat file for running DbMetal in the src\DbMetal folder from your zip file. There is one .bat file for each database type, so for MySQL, look at run_myMetal.bat. If you open this file in notepad, you’ll see it looks like this:

REM: note that the '-sprocs' option is turned on

bin\DbMetal.exe -provider=MySql -database:Northwind -server:localhost -user:LinqUser -password:linq2 -namespace:nwind -code:Northwind.cs -sprocs

There are a few changes you’ll need to make to get this to work for your own database. First, there is no ‘bin’ folder below the one in which the .bat file is located, so the file won’t find DbMetal unless you delete the ‘bin\’ and then move the file to the folder where DbMetal.exe is located.

Second, of course, you’ll need to change the user and password to whatever is needed to access your MySQL installation. You will also need to change the name of the database, and you’ll probably also want to change the name of the namespace and code file that DbMetal will produce. I also deleted the -sprocs option since I got an error when it was there. Once you’ve done all that, you can run the .bat file in a cmd window, and it will produce the C# file (Northwind.cs in the example above). You can then copy this file into your Visual Studio project so you can start writing your own LINQ code on your own database.

To use the class generated by DbMetal, define the string connStr for connecting to the database as in the Northwind example, and then create an object from the DbMetal-generated class. For example, if your database is called Comics and you told DbMetal to create a file called Comics.cs in a namespace called comics you would add a “using comics;” line at the top of your file and then open a Comics object with lines:

            string dbServer = Environment.GetEnvironmentVariable("DbLinqServer") ?? "localhost";
            string connStr = String.Format("server={0};user id={1}; password={2}; database={3}"
                , dbServer, "<Your username>", "<Your password>", "Comics");

            Comics db = new Comics(new MySqlConnection(connStr));

One final thing you will need to do though: you’ll need to make sure the various dll files are available to Visual Studio. To do this, right-click on References in Solution Explorer and select Add Reference. Select the Browse tab and then navigate to the ‘build’ folder produced by building the original DbLinq project. To use DbLinq with MySQL, you’ll need to add DbLinq.dll and DbLinq.MySql.dll. To use MySQL itself, you’ll also need MySql.Data.dll, which is found in the ‘lib’ folder. You’ll know when you’ve got all the right files as without them, you’ll get compiler errors about symbols that can’t be found.

One final caution about DbLinq. As the web site itself says, it’s still prototype software and may not work for complex queries, so make sure you test it thoroughly before relying on it too much. For most simple queries, though, it should be fine.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: