NET Framework 2.0中的数据访问新特性
1异步数据访问
a)支持异步数据编程
b)SqlConnection
– BeginOpen
– EndOpen
c)SqlCommand
– BeginExecuteNonQuery
– BeginExecuteReader
– BeginExecuteXmlReader
– EndExecuteNonQuery
– EndExecuteReader
– EndExecuteXmlReader
代码如下:(注意字符串连接,Asynchronous Processing=true)
View Code
public partial class Form1 : Form { public Form1() { InitializeComponent(); } delegate void PopulateListEventHandler(SqlDataReader reader); SqlConnection conn; SqlCommand comm; private void button2_Click(object sender, EventArgs e) { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString); comm = conn.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = "SELECT Name FROM Production.Product"; conn.Open(); comm.BeginExecuteReader(new AsyncCallback(HandleAsyncCallBack), null); this.label1.Visible = true; this.button2.Enabled = false; } public void HandleAsyncCallBack(IAsyncResult result) { System.Threading.Thread.Sleep(5000); SqlDataReader reader = comm.EndExecuteReader(result); this.Invoke(new PopulateListEventHandler(populateList), reader); } void populateList(SqlDataReader reader) { while (reader.Read()) { this.comboBox2.Items.Add(reader[0]); } reader.Close(); conn.Close(); this.comboBox2.SelectedIndex = 0; this.label1.Visible = false; this.button2.Enabled = true; } }
2.多活动结果集(MARKS)
a)在SQL Server 2005 中支持多活动结果集
b)允许在单个连接上执行多个批处理
c)启用MARS
string connectionString = "Data Source=MSSQL1;" + "Initial Catalog=AdventureWorks;Integrated Security=SSPI; MultipleActiveResultSets=True";
代码如下:
View Code
public partial class Form1 : Form { public Form1() { InitializeComponent(); } SqlConnection conn; private void listOrdersButton_Click(object sender, EventArgs e) { //Open the connection (if not already open) and retrieve all order headers if (conn.State != ConnectionState.Open) { conn.Open(); } SqlDataReader orderReader; SqlCommand getOrders = new SqlCommand("SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE SalesOrderID > 70000", conn); orderReader = getOrders.ExecuteReader(); while (orderReader.Read()) { orderListBox.Items.Add(orderReader["SalesOrderID"]); } //Select the first order and display the products it contains orderListBox.SelectedIndex = 0; DisplayProducts(orderListBox.SelectedItem.ToString()); } private void orderListBox_SelectedIndexChanged(object sender, EventArgs e) { DisplayProducts(orderListBox.SelectedItem.ToString()); } private void DisplayProducts(string OrderID) { //Open the connection if it's closed, otherwise just use it if (conn.State != ConnectionState.Open) { conn.Open(); } //Display the products for the selected order SqlDataReader detailReader; SqlCommand getDetails = new SqlCommand("SELECT ProductID FROM Sales.SalesOrderDetail WHERE SalesOrderID = " + OrderID, conn); detailReader = getDetails.ExecuteReader(); detailListBox.Items.Clear(); while (detailReader.Read()) { detailListBox.Items.Add(detailReader["ProductID"]); } conn.Close(); } private void Form1_Load(object sender, EventArgs e) { conn = new SqlConnection(); conn.ConnectionString = "SERVER=localhost;DATABASE=AdventureWorks;INTEGRATED SECURITY=true; MIN POOL SIZE=2; MAX POOL SIZE=10;MultipleActiveResultSets=true;"; } }
3.批量复制操作
a)Microsoft SQL Server 包含名为bcp的常用命令行应用程序，
用于快速将大文件批量复制到SQL Server 数据库的表或视图中。
b)使用SqlBulkCopy 类可以编写提供类似功能的托管代码解决方案。
c)还可以通过其他方式将数据加载到SQL Server 表中（例如INSERT 语句），
但是SqlBulkCopy 提供的性能要明显优于这些方式。
代码如下(此处只做演示):
View Code
public partial class Form1 : Form { public Form1() { //This call is required by the Windows Form Designer. InitializeComponent(); } private void bulkCopyForm_Load(System.Object sender, System.EventArgs e) { //Use a utility function to create the destination database for the sample CreateDestination(); } private void copyDataButton_Click(System.Object sender, System.EventArgs e) { // Retrieve data from the source server. SqlConnection sourceConn = new SqlConnection("SERVER=localhost;DATABASE=AdventureWorks;INTEGRATED SECURITY=true;"); SqlDataAdapter dA = new SqlDataAdapter("SELECT ProductID, Name, ListPrice FROM Production.Product", sourceConn); DataSet ds = new DataSet(); dA.Fill(ds, "Products"); // Connect to the destination server. SqlConnection destConn = new SqlConnection("SERVER=localhost;DATABASE=AWProductsData;Integrated Security=TRUE"); destConn.Open(); //count the existing rows SqlCommand verifyCmd = new SqlCommand("SELECT COUNT(*) FROM dbo.Products", destConn); int initialCount = System.Convert.ToInt32(verifyCmd.ExecuteScalar()); //Perform the copy operation using (SqlBulkCopy bcp = new SqlBulkCopy(destConn)) { bcp.DestinationTableName = "dbo.Products"; // Note that if column names matched, no mappings would be needed. bcp.ColumnMappings.Add("ProductID", "ProductCode"); bcp.ColumnMappings.Add("Name", "ProductName"); bcp.ColumnMappings.Add("ListPrice", "Price"); bcp.WriteToServer(ds.Tables["Products"]); } //Verify the data transfer int postCopyCount = System.Convert.ToInt32(verifyCmd.ExecuteScalar()); int copiedRows = postCopyCount - initialCount; MessageBox.Show(copiedRows.ToString() + " rows copied"); destConn.Close(); } #region "Utility code" private void CreateDestination() { try { using (SqlConnection conn = new SqlConnection("SERVER=localhost;DATABASE=master;INTEGRATED SECURITY=true")) { conn.Open(); SqlCommand SqlCmd = new SqlCommand("CREATE DATABASE AWProductsData", conn); SqlCmd.ExecuteNonQuery(); SqlCmd.CommandText = "CREATE TABLE AWProductsData.dbo.Products (ProductCode integer, ProductName nvarchar(40), Price money)"; SqlCmd.ExecuteNonQuery(); conn.Close(); } } catch (Exception Ex) { MessageBox.Show(Ex.Message); } } #endregion }
4)批处理更新
a)在上一个版本的ADO.NET 当中，SqlDataAdapter的Update方法
将会为DataSet当中的每一行调用一次更新操作。
b)在ADO.NET 2.0中，您可以设置UpdateBatchSize 属性，在单步中执行多个更新。
这样，可以提高数据更新的效率。
c)UpdateBatchSize 的默认值为1 使得默认的更新行为与以前版本的ADO.NET 一致。
代码如下:
View Code
public partial class Form1 : Form { public Form1() { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString); dAdapt = new SqlDataAdapter("SELECT ProductID, Name, ListPrice FROM Production.Product", conn); InitializeComponent(); } SqlConnection conn; SqlDataAdapter dAdapt; DataSet dSet = new DataSet(); StringBuilder logString = new StringBuilder(""); private void batchUpdateForm_Load(System.Object sender, System.EventArgs e) { dAdapt.RowUpdating += OnRowUpdating; dAdapt.RowUpdated += OnRowUpdated; } private void getDataButton_Click(System.Object sender, System.EventArgs e) { dAdapt.Fill(dSet, "Product"); productGrid.DataSource = dSet.Tables["Product"]; } private void updateDataButton_Click(System.Object sender, System.EventArgs e) { SqlCommandBuilder cb = new SqlCommandBuilder(dAdapt); logString.Remove(0, logString.Length); // Enable batching by setting batch size != 1. dAdapt.UpdateBatchSize = int.Parse(batchSizeTextBox.Text); // Execute the update. dAdapt.Update(dSet.Tables["Product"]); MessageBox.Show(logString.ToString()); } //handler for the RowUpdating event public void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e) { logString.AppendLine("Starting row update"); } // handler for RowUpdated event public void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) { logString.AppendLine("Completed row update"); } }
5)通知
a)SQL Server 2005 中的查询通知可以在数据修改时
通知客户端应用程序
b)ADO.NET 提供两种方式来利用查询通知功能：
– 使用SqlDependency类，并处理OnChanged事件
– 使用SqlNotifcationRequest 类，使用它可以用来访问自定义通知队列
代码如下:
View Code
public partial class Form1 : Form { public Form1() { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString); cmd = new SqlCommand("SELECT ProductID, Name, ListPrice FROM Production.Product", conn); dep = new SqlDependency(cmd); InitializeComponent(); } SqlConnection conn; SqlCommand cmd; SqlDependency dep; delegate void PopulateList(); private void notificationForm_Load(System.Object sender, System.EventArgs e) { //Assign the event handler for the dependency's OnChanged event dep.OnChange += new System.Data.SqlClient.OnChangeEventHandler(OnDependencyChanged); SqlDependency.Start(conn.ConnectionString); //Retrieve the initial data ListProducts(); } public void OnDependencyChanged(object sender, SqlNotificationEventArgs e) { //Event handler for OnChanged event of Dependency DialogResult dR; dR = MessageBox.Show("The data has changed. Refresh?", e.Info.ToString(), MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dR == System.Windows.Forms.DialogResult.Yes) { //Refresh the data this.Invoke(new PopulateList(ListProducts)); } } public void ListProducts() { productListBox.Items.Clear(); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { productListBox.Items.Add(reader["Name"].ToString() + ": " + reader["ListPrice"].ToString()); } conn.Close(); } }
6)快照隔离级别
a)SQL Server 2005 提供了快照隔离级别，用户可以访问行中上一个已提交的版本
b)ADO.NET SqlTransaction 类技术一个新的IsolationLevel Snapshot枚举值
使得ADO.NET 客户端应用程序可以利用快照隔离级别
c)先在数据库上启用
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
代码如下:
View Code
public partial class Form1 : Form { public Form1() { conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AWConnectionString"].ConnectionString); InitializeComponent(); } SqlConnection conn; SqlCommand cmd = new SqlCommand(); SqlTransaction tran; private void snapshotForm_Load(System.Object sender, System.EventArgs e) { try { conn.Open(); //Start a transaction using snapshot isolation tran = conn.BeginTransaction(IsolationLevel.Snapshot); cmd.Connection = conn; cmd.Transaction = tran; RetrieveData(); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } } private void updateButton_Click(System.Object sender, System.EventArgs e) { try { //update the data cmd.CommandText = "Update Production.Product SET ListPrice = ListPrice + 2 WHERE ProductID = 1"; cmd.ExecuteNonQuery(); RetrieveData(); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } } private void commitButton_Click(System.Object sender, System.EventArgs e) { try { //commit the transaction tran.Commit(); conn.Close(); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } } public void RetrieveData() { productListBox.Items.Clear(); cmd.CommandText = "SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID < 10"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { productListBox.Items.Add(reader["Name"].ToString() + ": " + reader["ListPrice"].ToString()); } reader.Close(); } }
7)数据库镜像
a)服务器角色
– 主服务器
存储主数据库的服务器
用户连接到服务器
– 镜像服务器
存储镜像数据库的服务器
在主服务器出现故障后，用户连接到该服务器
– 见证服务器
在主服务器与镜像服务器之间它们的监视连通性
8)配置客户端应用程序
a)使用.NET Framework 2.0进行开发
b)连接字符串: 连接字符串:
– 只需要添加“failover partner”参数
– 例如: connectionString="server=(local);database=AdventureWorks;Integrated Security=true;Failover Partner=(local)\MIRROR"
代码如下(注意链接字符串):
View Code
static class Program { /// <summary> /// 应用程序的主入口点。 /// </summary> static void Main() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString); while (true) { try { conn.Open(); SqlCommand comm = conn.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = "SELECT @@ServerName"; Console.WriteLine(comm.ExecuteScalar().ToString()); Thread.Sleep(2000); conn.Close(); } catch (Exception e) { Console.WriteLine(e.Message); } } } }
