Using MySQL with .Net
Friday, September 30th, 2005
Continuing research for porting my blog over to WordPress, I started learning how to interact with a MySQL database with C#. I lack experience with MySQL, so this would be a nice little project. At first, I was using an OleDb provider, but continued to get an error DB_E_NOTABLE (0×80040E37) when I tried to access longtext fields or blobs.
After stumbling many times over, I found that the MySQL development site had an article entitled
Exploring MySQL in the Microsoft .NET Environment
. In the article, Venu Anuganti states that MySQL doesn’t officially support OleDb providers such as the MyOLEDB provider. Fortunately, ODBC was a recommended driver. I was able to download the MyODBC driver and install it without and problems. Running my little C# test, I was finally able to read text fields without any errors.
One other thing that I noticed is that I could add a database connection to VS.Net 2005 with both MyODBC and MyOLEDB. The Oledb provider would only show me tables and columns, and would complain about asynchronous connections every time I tried to access data. With MyOLEDB, I saw tables, views, and stored procedures as nodes and I was also able to see data within each table.
Here is a screen shot of my testing tool:
And here is some basic code that I use for it:
7 using System.Data.Odbc;
8 using System.Diagnostics;
9 using System.Globalization;
…
110 {
111 get
112 {
113 return string.Format(
114 CultureInfo.InvariantCulture,
115 @”DRIVER={{MySQL ODBC 3.51 Driver}};SERVER={0};DATABASE={1};UID={2};PASSWORD={3};OPTION=3″,
116 this.Location, this.DataSource,
117 this.UserId, this.Password);
118 }
119 }
120 private void button1_Click(object sender, System.EventArgs e)
121 {
122 OdbcConnection connection = null;
123 OdbcCommand command = null;
124 OdbcDataAdapter adapter = null;
125 DataSet data = null;
126 try
127 {
128 connection = new OdbcConnection(this.ConnectionString);
129 command = connection.CreateCommand();
130 command.CommandText = this.Sql;
131 adapter = new OdbcDataAdapter(command);
132 data = new DataSet();
133 adapter.Fill(data);
134 this.dataGrid1.DataSource = data;
135 }
136 catch(Exception ex)
137 {
138 MessageBox.Show(this, ex.Message);
139 }
140 finally
141 {
142 if(adapter != null)
143 adapter.Dispose();
144 if(command != null)
145 command.Dispose();
146 if(connection != null)
147 {
148 if(connection.State != ConnectionState.Closed)
149 connection.Close();
150 connection.Dispose();
151 }
152
153 }
154 }
I am hoping that my web hosting provider will permit me to access MySQL over the internet rather then restricting me to a local connection from the website itself. If I can reach the database, then I may be able to import blogger entries and haloscan comments without too many problems over to WordPress.
Tags: MySQL, ODBC, OLEDB, MyODBC, MyOLEDB, .Net, C#, Databases, Programming, Source Code
