ADO.NET高级技术

1:异步数据访问

        static void Main(string[] args)
        {
            string connectionString = "";
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "Select * From Customers";
            cmd.BeginExecuteReader(new AsyncCallback(ExecuteAsync), cmd);
            Console.WriteLine("Asynchronous execution of command has begun");
        }
        public static void ExecuteAsync(IAsyncResult ar)
        {
            Thread.Sleep(3000);
            SqlCommand orginalCommand = (SqlCommand)ar.AsyncState;
            SqlDataReader dr = orginalCommand.EndExecuteReader(ar);
            while (dr.Read())
            {
                Console.WriteLine("[{0}] {1},{2} {3}", dr["ID"], dr["LastName"], dr["FirstName"], dr["middleName"]);
            }
        }

2:批量更新数据

string connectionString = "";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand selectCmd = conn.CreateCommand();
selectCmd.CommandText = "Select * From Customers";
SqlCommand updataCmd = conn.CreateCommand();
updataCmd.CommandText = "Update Customers Set FirstName =@ FirstName,LastName=@LastName,middleInitial=@middleInitial Where ID=@ID";
updataCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
updataCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
updataCmd.Parameters.Add(new SqlParameter("@middleInitial", SqlDbType.NChar, 1, "middleInitial"));
updataCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));
updataCmd.UpdatedRowSource = UpdateRowSource.None;

SqlCommand insertCmd = conn.CreateCommand();
insertCmd.CommandText = "Insert Into Customers(FirstName,LastName,middleInitial)" +
    "Values(@FirstName,@LastName,@middleInitial)";
insertCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
insertCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
insertCmd.Parameters.Add(new SqlParameter("@middleInitial", SqlDbType.NChar, 1, "middleInitial"));
insertCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));
insertCmd.UpdatedRowSource = UpdateRowSource.None;

SqlDataAdapter da = new SqlDataAdapter(selectCmd);
da.UpdateCommand = updataCmd;
da.InsertCommand = insertCmd;
da.UpdateBatchSize = 10;

DataSet ds = new DataSet();
da.Fill(ds,"Customers");
/**//*
 * 修改ds中的数据
 * 
 * 
*/
da.Update(ds, "Customers");

3:使用事务
注意:需添加程序集System.Transactions.dll;

string connectionString = "";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand Cmd = conn.CreateCommand();
Cmd.CommandText = "delete Customers Where ID > 3";
CommittableTransaction tx = new CommittableTransaction();
try
{
    conn.EnlistTransaction(tx);
    Cmd.ExecuteNonQuery();
}
catch
{
    tx.Rollback();
}
finally
{
    conn.Close();
}