Coding against the DB

If you have to embed SQL in your code, this is how to do it in C#.

Highlights:

• the using statement, which is like try-finally
• @-quoted string literals
• Parameters.AddWithValue(parameterName, value) (new in .NET Framework 2.0)

Coming from an ASP/PHP-MySQL background, it’s great to not have to escape special SQL characters or to enclose varchar values within single quotes.

using System.Data;
using System.Data.SqlClient;

using (SqlConnection cn = new SqlConnection(...))
{
	cn.Open();
	using (SqlCommand cmd = cn.CreateCommand())
	{
		cmd.CommandType = CommandType.Text;
		cmd.CommandText = @"
			SELECT address, phone, email
			FROM contacts
			WHERE surname = @surname
		";
		cmd.Parameters.AddWithValue("@surname", "O'Hara");
		using (SqlDataReader dr = cmd.ExecuteReader())
		{
			while (dr.Read())
			{
				...
			}
			dr.Close();
		}
	}
	cn.Close();
}

6 June 2007 | .NET, Software engineering, C# | Comments