You are reading the sequel to the "LINQ to MySQL, Oracle and PostgreSQL Providers" article that was also published here on Dev Shed. In this part we are going to provide an example of implementing LINQ-to-MySQL using the DbLinq Provider. We are going to use the C# programming language in the Visual Studio 2008 IDE.
This article presumes a sound knowledge of LINQ and familiarity with RDBMS and C#. We are going to use the DbLinq Provider because it's free and open source. Even though right now it's heavily under development and doesn't match production quality, it is highly promising. MySQL is also a free open source RDBMS. And while LINQ was specifically designed for MS SQL Server and Access, providers are kind of struggling.
It is strongly advised that you reread the previous article of this series. On the third page of that article I summed up in a nutshell the basic guidelines for working with DbLinq, starting from how to configure through implementing it into your project. Eventually, we will still go through those actions in this article, too.
Before we begin I should clarify something. The application we are creating relies heavily on the database scheme. It also assumes that you are absolutely sure that MySQL server is up and running on your system. Likewise, it assumes that you know the details of the configuration of the server (like the IP address, or localhost if it runs on your system, root password, etc.).
As mentioned in our previous article, the example that will be presented here in this article is a minimalist hotel management application. There is a database called hotelsdb that contains numerous tables. Right now we are going to work only with the Hotels and Clients tables. These two tables have particular fields.
Hotels(IdHotel, Name, Rating, [...])
Clients(IdClient, Name, Address, ZIP, Phone)
The [...] part of the Hotels table stands for various foreign keys that maintain the relationships between other tables, such as the Location, Manager, Company, Facilities, and so forth. Obviously, you need to implement the application to your own database scheme. But this information was required so that you can imagine the scheme we are working with during this article. These are all of the details you need to know.
These being said, fire up your Visual Studio 2008 and let's begin.
First you need to download the latest release of DbLinq. As we discussed in our previous article, both the source code and the archived (.zip) versions can be downloaded via Google Code. The project is featured as dblinq2007. For now you should download the archived version because it contains the binaries as well.
Take a minute or two to familiarize yourself with the structure of DbLinq (such as its folders and files). You are going to find DbMetal.exe under the build folder. That is the file that generates the C# class from the database schema and mapping attributes (ORM). It is a command-line utility; currently there is a GUI version of it n development. Under the srcDbMetal folder you are going to find numerous batch files as examples.
Those aforementioned batch files are called something like run_myMetal.bat; that suggests it gives an example of how to run the DbMetal utility with its arguments in the case of a MySQL server. The same applies for run_oraMetal.bat, run_sqliteMetal.bat, and so forth. Now we are going to copy the run_myMetal.bat into the build folder where the DbMetal executable is to be found, and edit the batch file accordingly.
Running the batch file will create the filename.cs C# class that "describes" your MySQL database along with its tables, and has the necessary resources so that you can work on it later and implement the provider. Now you need to copy the following files into the directory of your project and include them into your project:
DbLinq.MySql.dll
MySql.Data.dll
And the newly generated C# class file
The output of the DbMetal.exe in the case of successful execution is the following:
DbLinq Database mapping generator 2008 version 0.18.0.0
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT license (http://linq.to/db/license)
>>> Reading schema from MySQL database
<<< writing C# classes in file 'hotelsdb.cs'
Should the script (batch file) fail, then there are problems with the arguments you specified in the batch file, such as privileges, invalid password, and so forth. Moving on, when you first include the generated C sharp class file, you may eventually compile the project, and if there are errors, let's fix those. There won't be any algorithm-related problems, but rather issues like ambiguous references, some objects that cannot be resolved, etc.
For example, in my case, there were ambiguous references between the following:
System.Data.Linq.Table<hotels_Clients> and DbLinq.Data.Linq.Table<hotels.Clients>
And the error was found in the following snippet:
public Table<Clients> Clients { get { return GetTable<Clients>(); } }
As you can see, the IDE couldn't resolve the references. The type "Table" is to be found in both "System.Data.Linq" and "DbLinq.Data.Linq." The first is the native LINQ while the latter is our DbLinq provider. Therefore, to alleviate this ambiguous reference we can easily rewrite the code as follows (this is one of many solutions, though):
public DbLinq.Data.Linq.Table<Clients> Clients { get { return GetTable<Clients>(); } }
Anyway, with basic knowledge of C# and knowing its syntax, you shouldn't have any problems getting around these errors. In a nutshell, the entire database schema is mapped into the class. Another error that may happen is that, within the using directives at the beginning of the class, the following cannot be resolved: "DbLinq.Linq" and "DbLinq.Linq.Mapping." Write them as DbLinq.Data.Linq and DbLinq.Data.Linq.Mapping. Simply put, the structure differs a bit.
First of all you should do the basic groundwork of the application. That is, design its forms if you are not creating a console application. This article focuses strictly on the database part-where we are using DbLinq Provider and the LINQ native querying.
You can connect to the database in the following fashion:
hotels.HotelsDb db = new hotels.HotelsDb(new MySqlConnection("Database=hotelsdb;Data Source=localhost;User Id=root;Password=rootpassword"));
As you can see in the code snippet above, the namespace in my case is hotels, while hotelsdb is the name of the database. We're going to use db as the handler after the connection is established. Don't forget to write the correct server details.
Natively querying entries from the Hotels table can be done in the following way. This solution iterates through the hotels collection and writes the name of each hotel to a textbox (Name is a string attribute of the Hotel table). Should you want to create a more advanced view of results, you should plan on doing it with DataGridView.
var hotels = from h in db.Hotels select h;
foreach (var hotel in hotels){
txtBox1.Text += hotel.Name + "rn";
}
Now we want to exemplify the insert operation also. We have the following form.
And by using the following code snippet we are inserting the newly-created client in the appropriate Client table. First, we will count the total number of clients that are to be found in the table. This is important because we want to add +1 to this value; it is going to stand for the IDClient (primary key). Then insert each string appropriately.
var countClients = (from c in db.Clients
select c).Count();
int newID = countClients + 1;
db.Clients.InsertOnSubmit(new hotels.Clients{
IDClient = newID,
Name = txtName.Text.ToString(),
Address = txtAddress.Text.ToString(),
Phone = txtPhone.Text.ToString(),
Zip = txtZIP.Text.ToString()
});
db.SubmitChanges();
Deleting an entry from the table can be accomplished quite easily. We want to ask the user for the name of the Client, and then we can execute the delete operation. First we want to locate where that deleteable (the row which is going to be deleted) exists, and then we delete it. Check out the form below together with the code snippet.
hotels.Clients deleteable =
(from c in db.Clients
where c.Name == txtClient.Text.ToString()
select c).First();
db.Clients.DeleteOnSubmit(deleteable);
These basic actions should suffice as an example of how to work with DbLinq.
As you can see, we have arrived at the end of this article. By now you should have completed your first .NET application in C# based on the DbLinq provider. Keep in mind that the example presented in this article heavily depends on the tables contained within the database (their fields, types, etc.).
This means that you should pay extra attention when implementing everything in correlation with your database and your specific needs. This is possible only if you understand the basic concepts of LINQ, have the necessary experience with RDBMS, and, of course, are familiar with working with Visual C#. You can always refer back to the official Microsoft LINQ documentation should the need arise.
Moving on, here's one final screen shot of the application up and running after the "List Clients" queries has been executed. In a nutshell, the entire process isn't a black art. The DbLinq provider does a really good job as long as the queries remain simple, as they did in this article. Once they become complex and have sub-queries, things change...
Another thing worth mentioning is that at the time of writing, DbLinq has a huge limitation, and that's not being able to handle more than one foreign key. Surely, foreign keys aren't necessary theoretically-you can read about this here, here, and here-but that's an ugly workaround with which we don't really like to deal. The project requires further development, and this will fortunately change in the near future.
Meanwhile, you are free to explore other LINQ-to-other database providers. Every so often check the status of currently available providers and hope for the best. You should also refer back to the previous article in this series-the one that covered other providers along with DbLinq such as the LightSpeed(by Mindscape) and NHibernate.
And finally, we can't really finish without inviting you to join our helpful forums at DevHardware Forums. We've a strong base of resident professionals, enthusiasts, and tech experts. If you want to hear opinions on some service or ask for some clarification regarding some details, just shoot us your questions. We'll do our best to help. And you may also want to pay a visit to the forums of our sister-site: DevShed Forums.
/ 18 
LINQ to MySQL, Oracle and PostgreSQL Providers
