This component provides 5 classes to make SQL statements quicker in ASP.NET applications that use MySQL Server. If you have any suggestions for making this code more efficient, please let me know.

• Source: source.txt (5 kb)
• Compiled Binary: SQLHelper.MySQL.dll (6 kb)

Compiling the Source

Assuming you have csc.exe in your PATH statement and the MySql.Data.dll in the current directory, you can use the following command to compile:

csc.exe /t:library /debug /out:SQLHelper.dll toolbox.cs /r:MySql.Data.dll

Setup

There are two requirements for using this DLL:

1. Copy MySql.Data.dll, as part of the MySQL Connector/Net library, to your application’s /bin folder.
2. Copy the compiled SQLHelper.dll to your application’s /bin folder.

Usage Examples

Returning a Record Set

This code selects all records in the 'authors' table, loops through them, and displays all the returned values.

<%
MySqlConnection conn = new MySqlConnection("server=SERVER; user id=USERNAME; " +
     "password=; database=test; pooling=false");

DataRowCollection drc = SQLHelper.SQL.Select(SELECT title, date, author " +
     "FROM articles", conn);

foreach(DataRow dr in drc) {
     Response.Write("

");
     Response.Write("Article Title = " + dr["title"] + "");
     Response.Write("Article Date = " + dr["date"] + "");
     Response.Write("Author = " + dr["author"] + "");
     Response.Write("

");
}
%>

Returning a Single Row

<%
MySqlConnection conn = new MySqlConnection("server=SERVER; user id=USERNAME; " +
     "password=; database=test; pooling=false");

DataRow rs = SQLHelper.SQL.GetRow("SELECT title, article_date, author " +
     "FROM articles WHERE id = 100", conn);

Response.Write("Article Title: " + rs["title"] + "");
Response.Write("Article Date: " + rs["article_date"] + "");
Response.Write("Author: " + rs["author"]);
%>

Returning a Single Field

<%
MySqlConnection conn = new MySqlConnection("server=SERVER; user id=USERNAME; " +
     "password=; database=test; pooling=false");

string authorName = SQLHelper.SQL.GetField("SELECT author FROM articles " +
     "WHERE id = 100", conn);

Response.Write("Author's Name: " + authorName);
%>

Executing a SQL Statement

<%
MySqlConnection conn = new MySqlConnection("server=SERVER; user id=USERNAME; " +
     "password=; database=test; pooling=false");

// INSERT A RECORD
SQLHelper.SQL.Exec("INSERT INTO authors VALUES " +
     "('Article Title', '01/01/2004', 'Author Name')", conn);

// DELETE A RECORD
SQLHelper.SQL.Exec("DELTE FROM authors WHERE id = 100", conn);
%>

Inserting a Record and Returning Identity (Key)

This function is useful when you want to insert a record and return the MySQL insert key (primary key) for the newly inserted record. This INSERT command assumes a table structure of the following:

+--------+---------------+-----+----------+
| Field  | Type          | Key | Extra    |
+--------+---------------+-----+----------+
| ID     | int(4)        | PRI | IDENTITY |
| title  | varchar(255)  |     |          |
| date   | datetime      |     |          |
| author | varchar(100)  |     |          |
+--------+---------------+-----+----------+

<%
MySqlConnection conn = new MySqlConnection("server=SERVER; user id=USERNAME; " +
     "password=; database=test; pooling=false");

// INSERT A RECORD AND RETURN INSERT IDENTITY (PRIMARY KEY)
string insertID = SQLHelper.SQL.InsertReturnIdentity("INSERT INTO authors " +
     "VALUES ('Article Title', '01/01/2004', 'Author Name')", conn);

Response.Write("Record Inserted. Inserted Record ID = " + insertID);
%>