Windows应用程序开发入门到精通十三：深入ADO.NET开发—高级数据访问技术 (Level 400)

看了付仲恺的讲座的笔记

 

private void SimpleConnectionPooling() { SqlConnection conn = new SqlConnection("Server=(local);" + "DataBase=Northwind;" + "Integrated Security=True;" + //连接以后，在sql server企业管理器的管理的当前活动的进程信息中，可以显示 "Application Name=Advanced ADO.NET Simple Connection Pooling;" + //创建连接池，最大5个连接，最少3个连接（会创建3个连接） "Pooling=true;Max Pool Size=5;Min Pool Size=3" ////连接超时，如果不写这句，默认是15秒 +"Connect timeout = 45;"); // Max Pool Size，Min Pool Size，Connect timeout可以解决超时问题， //要注意搭配使用。Timeout设长点或者Max Pool Size设大点。 conn.Open(); SqlCommand cmd = new SqlCommand("Select @@version", conn); string sqlVersion = (string)cmd.ExecuteScalar(); //conn.Close(); MessageBox.Show(sqlVersion,"Sql Version", MessageBoxButtons.OK, MessageBoxIcon.Information); }

并发处理

DataSet.ContinueUpdateOnError为false（默认）时，如果有并发数据，则抛出异常，此数据后面的数据都不操作。如果为true，则不处理本数据，但继续处理后面的数据，且不抛出异常。

DataRow[] _Collisions=null;

DataSet.ContinueUpdateOnError = true;

DataSet.Update(DataTable);

If(DataTable.HasErrors)

{

    _Collisions = DataTable.GetErrors();

}

 

连接池

连接池对字符串敏感。比如：

“Integrated Security=SSPI;Database=Northwind”

“Integrated Security=SSPI ; Database=Northwind”

只是多了空格，就会建立2个连接池。

Close方法将连接放回连接池。

Dispose方法则直接销毁连接。

尽可能晚的打开连接，尽可能早的关闭连接。

 

 

 

本地事务 public void UpdateOrdersOrderDetails(dsOrdersOrderDetails orders) { //TODO: Declare a SqlTransaction variable SqlTransaction trans=null;//声明事务对象 try { //创建一个连接（daOrders是一个DataAdapter） daOrders.UpdateCommand.Connection=new SqlConnection(GetConnectionString()); daOrders.UpdateCommand.Connection.Open(); daOrders.InsertCommand.Connection = daOrders.UpdateCommand.Connection; daOrders.DeleteCommand.Connection = daOrders.UpdateCommand.Connection; //TODO: Begin the transaction //创建事务，级别是ReadCommitted trans= daOrders.UpdateCommand.Connection. BeginTransaction(IsolationLevel.ReadCommitted); daOrders.UpdateCommand.Transaction = trans; daOrders.DeleteCommand.Transaction = trans; daOrders.InsertCommand.Transaction = trans; daOrders.Update(orders); // daOrderDetails是一个DataAdapter daOrderDetails.UpdateCommand.Connection = daOrders.UpdateCommand.Connection; daOrderDetails.InsertCommand.Connection = daOrders.UpdateCommand.Connection; daOrderDetails.DeleteCommand.Connection = daOrders.UpdateCommand.Connection; //TODO: Participate in the already running transaction daOrderDetails.UpdateCommand.Transaction = trans; daOrderDetails.InsertCommand.Transaction = trans; daOrderDetails.DeleteCommand.Transaction = trans; daOrderDetails.Update(orders); //TODO: Commit the transaction trans.Commit();//提交事务 } finally { //TODO: Rollback the transaction if it // was not committed if(null!=trans && null!=trans.Connection) trans.Rollback(); if(null!=daOrders.UpdateCommand.Connection) { daOrders.InsertCommand.Connection = null; daOrders.DeleteCommand.Connection = null; daOrderDetails.UpdateCommand.Connection = null; daOrderDetails.InsertCommand.Connection = null; daOrderDetails.DeleteCommand.Connection = null; if(ConnectionState.Closed!=daOrders.UpdateCommand.Connection.State) daOrders.UpdateCommand.Connection.Close(); daOrders.UpdateCommand.Connection.Dispose(); daOrders.UpdateCommand.Connection = null; } } }

 

分布式高级事务 在中间层添加System.EnterpriseServices.Transaction [WebMethod(TransactionOption=TransactionOption.Required)] public void Transaction() { //creating a connection with the above WebMethod //attribute will cause it to automatically enlist with //the DTC in a transaction using(SqlConnection conn = new SqlConnection( "Server=(local);DataBase=Northwind;Integrated Security=True" + ";Application Name = Advanced ADO.NET + Cross Database Transactions" + ";Pooling=true;Max Pool Size=5;Min Pool Size = 2" + ";Connect timeout=30;")) { conn.Open(); SqlCommand cmd = new SqlCommand( "insert into TestTransactions(Value) values ('Northwind')", conn); cmd.ExecuteNonQuery(); conn.Close(); } //This connection, even though it's to a different database //will enlist in the same transaction as above via the DTC using(SqlConnection conn = new SqlConnection( "Server=(local);DataBase=pubs;Integrated Security=True" + ";Application Name = Advanced ADO.NET + Cross Database Transactions" + ";Pooling=true;Max Pool Size=5;Min Pool Size = 2" + ";Connect timeout=30;" )) { conn.Open(); SqlCommand cmd = new SqlCommand( "insert into TestTransactions(Value) values ('pubs')", conn); cmd.ExecuteNonQuery(); conn.Close(); } //By setting Enlist=false in the connection string below, this //connection will not belong to the above transaction using(SqlConnection conn = new SqlConnection( "Server=(local);DataBase=DataBaseCourse;Integrated Security=True" + ";Application Name = Advanced ADO.NET + Cross Database Transactions" + ";Pooling=true;Max Pool Size=5;Min Pool Size = 2" + ";Connect timeout=30;Enlist=false"//will not automatically enlist in transactions //注意这里有个Enlist=false，所以不会执行事务操作，数据写入了数据库，没有rollback )) { conn.Open(); SqlCommand cmd = new SqlCommand( "insert into TestTransactions(Value) values ('DataBaseCourse')", conn); cmd.ExecuteNonQuery(); conn.Close(); } //We can cause a connection setup like above to //enlist in the running transation using //EnlistDistributedTransaction using(SqlConnection conn = new SqlConnection( "Server=(local);DataBase=DataBaseCourse;Integrated Security=True" + ";Application Name = Advanced ADO.NET + Cross Database Transactions" + ";Pooling=true;Max Pool Size=5;Min Pool Size = 2" + ";Connect timeout=30;Enlist=false"//will not automatically enlist in transactions //这里虽然有Enlist=false，但是下面采用了EnlistDistributedTransaction方法，所以还是事务 )) { conn.Open(); conn.EnlistDistributedTransaction((ITransaction)ContextUtil.Transaction); SqlCommand cmd = new SqlCommand( "insert into TestTransactions(Value) values ('Enlisted')", conn); cmd.ExecuteNonQuery(); conn.Close(); } //Throwing an exception will cause the transaction to rollback //抛出异常，导致事务rollback throw new System.Web.Services.Protocols.SoapException("We blew up...", System.Web.Services.Protocols.SoapException.ServerFaultCode); }

 

 可抽取数据访问层 // Form1.cs Using System.Reflection; Namespace Demo5 { [STAThread] static void Main() { Application.Run(new Form1()); } private void ShowColor(string color) { IDAL dal = GetDAL(color); MessageBox.Show(dal.GetColor(), color); } private IDAL GetDAL(string Color) { //假设color是green //取得DALGreen.dll Assembly asbly = System.Reflection.Assembly.Load("DAL"+Color); //创建这个类的实例 return (IDAL)asbly.CreateInstance(String.Format("Demo5.DAL{0}", Color)); } private void btnGreen_Click(object sender, System.EventArgs e) { ShowColor("Green"); } } // IDAL.cs namespace Demo5 { public interface IDAL { string GetColor(); } } // DALGreen.cs namespace Demo5 { public class DALGreen: IDAL { public DALGreen() { } public string GetColor() { return "Green"; } } }