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.

PLAIN TEXT
C#:
  1. using System.Data;
  2. using System.Data.SqlClient;
  3.  
  4. using (SqlConnection cn = new SqlConnection(...))
  5. {
  6.     cn.Open();
  7.     using (SqlCommand cmd = cn.CreateCommand())
  8.     {
  9.         cmd.CommandType = CommandType.Text;
  10.         cmd.CommandText = @"
  11.             SELECT address, phone, email
  12.             FROM contacts
  13.             WHERE surname = @surname
  14.         ";
  15.         cmd.Parameters.AddWithValue("@surname", "O'Hara");
  16.         using (SqlDataReader dr = cmd.ExecuteReader())
  17.         {
  18.             while (dr.Read())
  19.             {
  20.                 ...
  21.             }
  22.             dr.Close();
  23.         }
  24.     }
  25.     cn.Close();
  26. }

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

Comments:

  1.  
  2.  
  3.