If you use C# or VB.NET through the ASP.NET stack (or just as a desktop application) to connect to a SQL database, you’ll have a few options. There’s the new entity data model from Microsoft, there’s also last year’s notion of LINQ. They both do a reasonably good job, but are sometimes on the over-kill side for smaller projects. Sometimes you just want to run a SQL command and get back a DataTable or have a SqlCommand executed. If you’re a new developer, it’s especially good to learn “the hard way” of writing SQL Commands because then you’ll actually know what’s going on behind all of the time-saving activities that .NET can perform now of days.
Here’s a tradition way to read data from a database and put it in a table. (There’s also the Data Reader Method that some would argue is more efficient)
//create command
SqlCommand cmdMyQuery = new SqlCommand("Select * From Users where username=@username");
cmdMyQuery.Parameters.AddWithValue("@username", "Matt");
//Get connection string
string ecmsConnectionString = ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;
SqlConnection SQLDatabaseConnection = new SqlConnection(ecmsConnectionString);
//Perform Command
cmdSQLQuery.Connection = SQLDatabaseConnection;
DataSet dsPageInfo = new DataSet();
SqlDataAdapter daPageInfo = new SqlDataAdapter(cmdSQLQuery);
SQLDatabaseConnection.Open();
daPageInfo.Fill(dsPageInfo);
SQLDatabaseConnection.Close();
DataTable dtResults = dsPageInfo.Tables[0];
That’s a lot of code to do something that you’re probably going to do on a very regular basis. If you wanted to run a SQL command such as an INSERT, UPDATE or DELETE, you traditionally would have to do something like this:
//create command
SqlCommand cmdMyQuery = new SqlCommand("update users set Active=1 where username=@username");
cmdMyQuery.Parameters.AddWithValue("@username", "Matt");
//get connection string
string ECMSconnString = ConfigurationManager.ConnectionStrings["csWebCMS"].ConnectionString;
SqlConnection SQLDatabaseConnection = new SqlConnection(ECMSconnString);
//execute command
CommandToExecute.Connection = SQLDatabaseConnection;
SQLDatabaseConnection.Open();
CommandToExecute.ExecuteNonQuery();
SQLDatabaseConnection.Close();
This isn’t nearly as bad as doing a SQL Select statement, but there’s an easier way to redo these two things. We can create a static class with two functions, run to do SQL Select statements, and one to do INSERTS, UPDATE’S, and DELETE’s. We want to do a static class with static methods because that mean a new copy of the class won’t be made each time it’s run, meaning that it’ll take less memory and be more efficient.
Here’s what the code will look like:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
/// Abstracts Database connectivity and provides SQL Injection Prevention Mechanism
public static class DBUtils
{
/// Returns the results of a SQL Query in the form of a DataTable
public static DataTable SQLSelect(SqlCommand cmdSQLQuery)
{
//Get connection string
string conConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection SQLDatabaseConnection = new SqlConnection(conConnectionString);
//Perform Command
cmdSQLQuery.Connection = SQLDatabaseConnection;
DataSet dsPageInfo = new DataSet();
SqlDataAdapter daPageInfo = new SqlDataAdapter(cmdSQLQuery);
SQLDatabaseConnection.Open();
daPageInfo.Fill(dsPageInfo);
SQLDatabaseConnection.Close();
return dsPageInfo.Tables[0];
}
/// Executes a SQL Command
public static void ExecuteSQLCommand(SqlCommand CommandToExecute)
{
//get connection sring
string conConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection SQLDatabaseConnection = new SqlConnection(conConnectionString);
//execute command
CommandToExecute.Connection = SQLDatabaseConnection;
SQLDatabaseConnection.Open();
CommandToExecute.ExecuteNonQuery();
SQLDatabaseConnection.Close();
}
}
Now, when you want to run a SQL Select Command and put the results in a DataTable for use, you can just do this:
SqlCommand cmdMyQuery = new SqlCommand("Select * From Users where username=@username");
cmdMyQuery.Parameters.AddWithValue("@username", "Matt");
DataTable dtResults = dbutils.SQLSelect(cmdMyQuery);
If you want to run a command, just do this:
SqlCommand cmdMyQuery = new SqlCommand("insert into users (username) VALUES (@username)");
cmdMyQuery.Parameters.AddWithValue("@username", "Matt");
DataTable dtResults = dbutils.ExecuteSQLCommand(cmdMyQuery);
Hopefully creating a data access class will make your coding experiences in .NET just a little bit easier. To download a copy of this class in C#, click here (1.86KB).
In the near future, I’m hoping to post about the C# code-generation tool that I created which works with class. Essentially, you point it at any table in the database, and it’ll give you full CRUD (Create, Read, Update Delete) functionality for any given row in that table. It makes data-access a heck of a lot easier.