static void

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;
}

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;
        }
    }
}

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;
}

        private async Task<IList<Row>> Query(DateTime from, DateTime to)
        {
            var list = new List<Row>();
            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                using (var cmd = new SqlCommand(@"Select COUNT(*) AS COUNT from Events
WHERE Modified Between @from AND @to;
Select * from Events
WHERE Modified Between @from AND @to
ORDER BY Modified
", conn))
                {
                    cmd.Parameters.AddWithValue("from", from);
                    cmd.Parameters.AddWithValue("to", to);

                    using (var reader = await cmd.ExecuteReaderAsync())
                    {
                        while (reader.HasRows)
                        {
                            while (await reader.ReadAsync())
                            {
                                var row = new Row();
                                for (var i = 0; i < reader.FieldCount; i++)
                                {
                                    var key = reader.GetName(i);
                                    var value = reader.GetValue(i);

                                    row.Add(key, value);
                                }
                                list.Add(row);
                            }
                            //if two queries
                            await reader.NextResultAsync();
                        }
                    }
                }
            }

            return list;
        }

        public class Row
        {
            public Row()
            {
                DataRow = new Dictionary<string, object>();
            }

            public void Add(string key, object value)
            {
                if(Convert.IsDBNull(value)) value= string.Empty;
                DataRow.Add(key, value);
            }
            public IDictionary<string, object> DataRow { get; set; }
        }