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 :)

The DataContext class is the core channel that you use to work with (for example,
to query or update) your database. For each LTS DBML file you add to your solution, a new
DataContext will be created.
The DataContext class is a generated partial class that exists in your AdventureWorks.
designer.cs file. In this example, your partial class is called AdventureWorksDataContext, which
is based on the name that you gave the DBML file. As you can see in the example below, the DataContext
class contains the properties and methods for interacting with the table(s) that have been added
to the designer.
In addition to the properties and methods in the AdventureWorksDataContext class, you
can also see in the example below that the System.Data.Linq.DataContext is used as the base class. This
base class is part of the LINQ framework and contains the contract and implementation logic
to work with the database and the entities in the database. Some examples of what is in the
DataContext class include Refresh, CreateDatabase, GetTable, and SubmitChanges.

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
using System.ComponentModel;
using System;

[System.Data.Linq.Mapping.DatabaseAttribute(Name="AdventureWorks")]
public partial class AdventureWorksDataContext : System.Data.Linq.DataContext
{
private static System.Data.Linq.Mapping.MappingSource
mappingSource = new AttributeMappingSource ();
#region Extensibility Method Definitions
partial void OnCreated();
partial void InsertDepartment(Department instance);
partial void UpdateDepartment(Department instance);
partial void DeleteDepartment(Department instance);
#endregion
static AdventureWorksDataContext()
{
}
public AdventureWorksDataContext(string connection) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}

public AdventureWorksDataContext(string connection,
System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext(System.Data.IDbConnection connection,
System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public AdventureWorksDataContext() :
base(global::
LTS_Harness.Properties.Settings.Default.
AdventureWorksConnectionString, mappingSource)
{
OnCreated();
}
public System.Data.Linq.Table<Department> Departments
{
get
{
return this.GetTable<Department>();
}
}
}

Prior to answering this question, you must first understand what LINQ is. LINQ stands for the
.NET Language Integrated Query and is a new framework extension in .NET 3.5. As the name
suggests, LINQ is a language construct that enables the user to write data access queries natively in
.NET languages.
In days of yore, you had to write inline T-SQL or use a third-party tool to be able to write
queries in your .NET code. With LINQ, your inline queries natively use the metadata from your
project, so you have syntax checking, IntelliSense, and type-checking functionality. In addition,
you also benefit from a single declarative language construct for all your data sources (for
example, XML or a database). LINQ defines a set of standard query operators that allow your
queries to be applied to any IEnumerable<T> interface.
LINQ is the foundation for many native substructures in .NET that enable you to query
disparate data sources. For example, LINQ to XML gives you the ability to write LINQ to query
XML stores; LINQ to DataSets allows you to use DataSets as your data source; LINQ to Objects
provides you with the means to query over collections of objects; and, finally, LINQ to SQL
gives you the ability to work with relational databases.
LTS is the focus of the current and next few chapters and is Microsoft’s first attempt at
ORM. LTS allows you to model a database and gives you the ability to query the database by
using native .NET classes, such as LINQ. Additionally, LTS supports transactional processing,
views, and stored procedures, and provides a centralized structure for business validation.