Accessing and Querying SQL database with SqlClient and C#

Data access from a database is one of the important aspects of any programming language.

There are numerous ways to work with databases. in C#: Most common is using ORM  (Object Rational Mapping) library, EntityFramework for example, to do all the data binding, reteival, insertion and so on. Other method is “directly” accessing the data with Transact-SQL with a driver, such as .SqlClient.

In this post, I’ll show some of the common use cases of using .Net’s SqlClient, accompanied with sample code.

Few comments before we dive in:

• I am wrapping the using block with a try-catch block, as this ensured that all exceptions are caught and handled, regardless if it is related to SqlClient or not.
• I am throwing the exception again after it’s caught, assuming this is a base layer, and that upper layer should do more error handling and recovery procedures (logging, retries, etc…)
• Always make sure to use SqlParameter in the SqlCommand to avoid SQL-injection attacks.

Executing a stored procedure, without expecting any returned results
Calling a stored procedure from code, then reading all the results rows in at once using DataTable
Calling a stored procedure from code, then reading a single row

Querying data with T-SQL directly from code

string queryString = "SELECT ProductID, UnitPrice, ProductName from dbo.products " + "WHERE UnitPrice > @pricePoint " + "ORDER BY UnitPrice DESC;";

// Specify the parameter value.
int pricePointParameter = 5;

using(SqlConnection connection = new SqlConnection(connectionString)) {

	SqlCommand command = new SqlCommand(queryString, connection);
	command.Parameters.AddWithValue("@pricePoint", pricePointParameter);

	try {
		connection.Open();
		SqlDataReader reader = command.ExecuteReader();
		while (reader.Read()) {
			// Do stuff here
		}
		reader.Close();
	} catch(Exception ex) {
		// Logging
	}
}

Executing a stored procedure, without expecting any returned results

try
{
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
		connection.Open();

		using (SqlCommand command = new SqlCommand())
		{
			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "USP_UPDATE_URL";

			SqlParameter pURL = new SqlParameter("@URL", sealedDocument.URL);
			command.Parameters.Add(pURL);

			command.ExecuteNonQuery();
		}

		connection.Close();

	}
}
catch (Exception Ex)
{
	log.Error("Unable to update URL : " + Ex.Message);
	throw;
}

Calling a stored procedure from code, then reading all the results rows in at once using DataTable.

try
{
	DataTable dtOutput;
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
		connection.Open();

		using (SqlCommand command = new SqlCommand())
		{
			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "USP_UPDATE_URL";

			SqlParameter pURL = new SqlParameter("@URL", sealedDocument.URL);
			command.Parameters.Add(pURL);

			using (SqlDataReader dr = command.ExecuteReader())
			{
				if (dr.HasRows)
				{
					dtOutput.Load(dr);
				}
			}
		}
		connection.Close();
	}
}
catch (Exception Ex)
{
	log.Error("Unable to update URL : " + Ex.Message);
	throw;
}

However, if there is one row to read, or you need to read one row at a time without using DataTable, you can use the following code:

using (SqlCommand command = new SqlCommand())
{

	command.Connection = connection;
	command.CommandType = System.Data.CommandType.StoredProcedure;
	command.CommandText = "GET_NAME";

	SqlDataReader sqlDataReader = command.ExecuteReader();
	if (reader.Read())
	{
		fname = (string)reader["fname"];
		lname = (string)reader["lname"];
	}
}