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

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

Compiling the Source

Assuming you have csc.exe in your PATH statement, you can use the following command to compile:

csc.exe /t:library /debug /out:SQLHelper.dll source.cs

Setup

To use this component, simple copy the compiled 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.

<%
SqlConnection conn = new SqlConnection("Password=PASSWORD;Persist Security " +
     "Info=True;User ID=USERNAME;Initial Catalog=DBNAME;Data Source=SERVER");

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

<%
SqlConnection conn = new SqlConnection("Password=PASSWORD;Persist Security " +
     "Info=True;User ID=USERNAME;Initial Catalog=DBNAME;Data Source=SERVER");
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

<%
SqlConnection conn = new SqlConnection("Password=PASSWORD;Persist Security " +
     "Info=True;User ID=USERNAME;Initial Catalog=DBNAME;Data Source=SERVER");
string authorName = SQLHelper.SQL.GetField("SELECT author FROM articles " +
     "WHERE id = 100", conn);

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

Executing a SQL Statement

<%
SqlConnection conn = new SqlConnection("Password=PASSWORD;Persist Security " +
     "Info=True;User ID=USERNAME;Initial Catalog=DBNAME;Data Source=SERVER");

// 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 SQL SERVER IDENTITY (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)  |     |          |
+--------+---------------+-----+----------+

<%
SqlConnection conn = new SqlConnection("Password=PASSWORD;Persist Security " +
     "Info=True;User ID=USERNAME;Initial Catalog=DBNAME;Data Source=SERVER");

// 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);
%>