At my current job we recently licensed and installed our first Apache and MySql based application. We are mostly a .Net shop. So while I am happy to have an Apache and MySql application running because it's going to bring us in money I really didn't want to add an additional programming language to our development roadmap. The unfortunate thing is that we have already been asked to create several nightly import processes for the MySql database.
Usually I would start with a more robust database abstraction layer using something like nHibernate but since these are some simply throw away scripts I figured I'd start by just connecting directly to the MySql database and perform the updates.
Installing the MySql .Net Connector
If you haven't already installed MySql you can download it here. We were using version 5.0.45. Just follow the instructions in the .msi to get the server installed.
Next I needed to download the MySql connectors for .Net. You should also be able to use a standard ODBC connection to access the MySql database but I like to use a database specific adapter when there's on available. The install is pretty straight forward and actually includes the MySql GUI Tools so there is no need to download those installers separately.
MySql Install #1 - Select 'Custom' to see available install components
MySql Install #2 - Select available components
Creating a Sample Project
Using Visual Studio 2008 I created a sample console application named "MySqlSample". I quickly added an Application Configuration file and a MySqlUpdater class to the project.
When you install the MySQL Connector for .Net the installer adds the MySQLDatabase as a connection option from the Server Explorer->Add Connection Dialog. If you like to be able to browse your database from within Visual Studio this is a nice feature and is very easy to setup.
Add new MySQL database Connection
Connection Properties
New Connection in Server Explorer
On to the Code...
Okay, so now that all my installs are out of the way and I know that my connections are working between my work station and my MySQL database server I can move on into the code. First I added a new connection string element to my app.config file:
<connectionStrings>
<add name="MySqlSample" connectionString="server=msdev01;database=am_centraloregon;user id=root;pwd=####"/>
</connectionStrings>
I then had to add a reference to the MySQL.Data library to my sample project.
I then went into the MySqlUpdater class and added the following code to create a connection and update a single row into the makes table and to return the id of the new row.
1: using System;
2: using System.Collections.Generic;
3: using System.Configuration;
4: using System.Linq;
5: using System.Text;
6: using System.Data;
7:
8: using MySql.Data.MySqlClient;
9:
10:
11: namespace MySqlSample
12: {
13: public class MySqlUpdater
14: {
15:
16: public long AddMake(string name)
17: {
18: MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlSample"].ConnectionString);
19:
20: try
21: {
22: conn.Open();
23:
24: MySqlCommand cmd = new MySqlCommand();
25: cmd.Connection = conn;
26: cmd.CommandText = "Insert makes (Name) Values (@name)";
27: cmd.CommandType = CommandType.Text;
28: cmd.Parameters.Add("@name", name);
29:
30: cmd.ExecuteNonQuery();
31:
32: return (cmd.LastInsertedId);
33: }
34: finally
35: {
36: if (conn != null)
37: conn.Close();
38: }
39:
40: }
41: }
42: }
Nothing super exciting here. Looks a lot like accessing a MS SQL 2005 database using System.Data.SqlClient. Obviously we're using the MySqlConnection and MySqlCommand classes but everything else is pretty standard.
Conclusion
Obviously this code is a very simplistic example but at least now I know that all my plumbing is working between Visual Studio and my MySQL installation. No I'm ready to move up to a higher levels of abstraction.
posted @ Monday, April 21, 2008 1:42 PM