static void

ADO patterns

ORMs like NHibernate and Linq2Sql nicely hide all these details.

DataReader Pattern

private List<Order> ReadOrders()
{
    List<Order> orders = new List<Order>();
    using (SqlConnection cn =
        new SqlConnection("Server=localhost;Initial Catalog=Northwind;Integrated Security=True"))
    {
        cn.Open();
        string sql = @"SELECT OrderID, CustomerID, Freight, ShippedDate FROM Orders WHERE ShipCountry = @Country";
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.AddWithValue("@Country", "USA");
            using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                //you could set DataSource = rdr directly
                if (!rdr.HasRows) return orders;
                while (rdr.Read())
                {
                    int id = rdr.GetInt32(0);
                    string customerID = rdr["CustomerID"].ToString();
                    decimal freight = rdr.GetDecimal(2);
                    DateTime? shipDate = null;
                    if (!rdr.IsDBNull(3)) shipDate = rdr.GetDateTime(3);
                    orders.Add(new Order(id, customerID, freight, shipDate));
                }
                //rdr.NextResult(); //for multi-results
            }
        }
    }
    return orders;
}

DataAdaptor Pattern

private DataTable LoadViaDataTable()
{
    using (SqlConnection cn =
        new SqlConnection("Server=localhost;Initial Catalog=Northwind;Integrated Security=True"))
    {
        //no need to explicitly open/close connection unless doing several fills
        string sql = @"SELECT OrderID, CustomerID, Freight, ShippedDate FROM Orders WHERE ShipCountry = @Country";
        using (SqlCommand cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.AddWithValue("@Country", "USA");
            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                da.Fill(dt);
            return dt;
        }
    }
}

Update Pattern

private int InsertCategory(string categoryName)
{
    int id;
    //using System.Transactions;
    using (TransactionScope tscope = new TransactionScope())
    {
        using (SqlConnection cn =
                new SqlConnection("Server=localhost;Initial Catalog=Northwind;Integrated Security=True"))
        {
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText =
                    @"INSERT INTO Categories (CategoryName) VALUES (@Name);
SET @IdentityID = SCOPE_IDENTITY()";
 
                cmd.Parameters.AddWithValue("@Name", categoryName);
 
                SqlParameter idParam = new SqlParameter("@IdentityID", SqlDbType.Int);
                idParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(idParam);
 
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
 
                id = (int)idParam.Value;
            }
        }
        //tscope.Complete(); //testing, so let it rollback
    }
    return id;
}