asp.net source code blog

It may sound strange but using MySql database in ASP.NET is really easy. I spend few days trying to create ASP.NET site that uses
MySql DataBase and in this post I'll try to explain all points for that, also how to use MySql in SqlDataSources and GridViews.

I. You need to download MySql.Data.dll assembly and add reference to it. LINK

1. download sources and open the project

2. recompile the MySql.Data.Dll to allow for partially trusted callers. This can be done by adding the following to the AssemblyInfo.cs file.

[assembly:AllowPartiallyTrustedCallers]

3. copy-paste the recompiled in your project's bin folder

4. add a reference to it.

Note*: if you don't have problems using partially trusted callers you don't need to recompile the assembly.

Note*: some hosting providers supports it in the GAC, you'd better check before doing steps above.

II. Configure The Web.Config File

For ex. in the MySql DataBase, let's we have:

Server' URL: SERVER_URL

DataBase' Name: DATABASE_NAME

DataBase' Username: DB_USER_ID

DataBase' Password: DB_USER_PASSWORD

now, in the Web.Config file add:

1. <connectionStrings>
       ...
       <add name="YOUR_CONN_STRING_NAME" connectionString="Server=SERVER_URL;Database=DATABASE_NAME;Uid=DB_USER_ID;Pwd=DB_USER_PASSWORD;"/>
      ...
    </connectionStrings>

2. <compilation ...>
     ...
    <add assembly="MySql.Data, Version=VERSION_OF_THE_ASSEMBLY, Culture=neutral, PublicKeyToken=ASSEMBLY_PUBLIC_KEY" />
    ...
    </compilation>

   You can check VERSION_OF_THE_ASSEMBLY and ASSEMBLY_PUBLIC_KE from the MySql.Data.dll's source file: AssemblyInfo.cs

3. <pages ... >
     <controls>
       <add ... />
       ...
     </controls>
    <namespaces>
     <add namespace="MySql.Data"/>
     <add namespace="MySql.Data.MySqlClient"/>
    </namespaces>
  </pages>

4. <system.data>
     <DbProviderFactories>
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory,         MySql.Data,Version=VERSION_OF_THE_ASSEMBLY,Culture=neutral"/>
     </DbProviderFactories>
   </system.data>

III. Using MySql in your code (C# examples)

For ex. in the MySql DataBase DATABASE_NAME we have a table with name TABLE_NAME (ID, FIELD1)

1. INSERT INTO ... VALUES ...:

using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;

 ...

MySqlConnection msqlConnection;
string sql;
MySqlCommand msqlCommand;

try
{
    msqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings[YOUR_CONN_STRING_NAME].ConnectionString);

    sql = "INSERT INTO TABLE_NAME (FIELD_1) VALUES (?param1);";

    msqlCommand = new MySqlCommand(sql, msqlConnection);

    msqlCommand.Parameters.AddWithValue("?param1", SOME_LOCAL_VARIABLE);

    try
   {
      msqlConnection.Open();

      msqlCommand.ExecuteNonQuery();
   }
   finally
  {
     msqlConnection.Close();
   }
}
catch (Exception ex)
{
    // handle the exception
    ...
}

Note*: for parameters name we use ? instead of @

2. SELECT ... FROM ... WHERE:

using System.Configuration;
using MySql.Data;
using MySql.Data.MySqlClient;

 ...

MySqlConnection msqlConnection;
MySqlCommand msqlCommand;
MySqlDataReader msqlDataReader;
string sql;

try
{
     msqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings[YOUR_CONN_STRING_NAME].ConnectionString);

     sql = "SELECT FIELD_1 FROM TABLE_NAME WHERE ID = ?param1;";

     msqlCommand = new MySqlCommand(sql, msqlConnection);

     msqlCommand.Parameters.AddWithValue("?param1", SOME_LOCAL_VARIABLE);

     try
    {
       msqlConnection.Open();

      msqlDataReader = msqlCommand.ExecuteReader();

      if (msqlDataReader.Read())
         result = (msqlDataReader).GetString("message");
     }
     finally
    {
       msqlConnection.Close();
    }
}
catch (Exception ex)
{
   // handle the exception
    ...
}

3. SELECT COUNT(*):

string result = "";
MySqlConnection msqlConnection;
MySqlCommand msqlCommand;
string sql;
object obj;

try
{
    msqlConnection = new MySqlConnection(ConfigurationManager.ConnectionStrings[YOUR_CONN_STRING_NAME].ConnectionString);

    sql = "SELECT COUNT(*) FROM TABLE_NAME WHERE YEAR(created) = ?param1;";

    msqlCommand = new MySqlCommand(sql, msqlConnection);

    msqlCommand.Parameters.AddWithValue("?param1", SOME_LOCAL_VARIABLE);

    try
   {
      msqlConnection.Open();

     obj = msqlCommand.ExecuteScalar();

     if (obj != null)
      result = obj.ToString();
   }
   finally
  {
     msqlConnection.Close();
   }
 }
catch (Exception ex)
{
  // handle the exception
    ...
}

IV. Using MySql in aspx pages (SqlDataSource and GridView example)

<asp:GridView ID="_gridViewId" DataSourceID="_dataSourceId" runat="server"
 AutoGenerateColumns="false" DataKeyNames="ID" AllowPaging="true" PageSize="100">
   <Columns>
     <asp:BoundField DataField="FIELD_1" HeaderText="SOME_HEADER_TEXT" />
   </Columns>
</asp:GridView>

<asp:SqlDataSource ID="_dataSourceId" runat="server"
   ConnectionString="<%$ ConnectionStrings:YOUR_CONN_STRING_NAME %>"
   ProviderName="MySql.Data.MySqlClient"
   SelectCommand="SELECT * FROM TABLE_NAME">
</asp:SqlDataSource>

Well, That's all. I hope that this post'll be usefull to someone else, too :)