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 headersif (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 containsorderListBox.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 itif (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 rowsSqlCommand verifyCmd = new SqlCommand("SELECT COUNT(*) FROM dbo.Products", destConn);int initialCount = System.Convert.ToInt32(verifyCmd.ExecuteScalar());//Perform the copy operationusing (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 transferint 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 eventpublic void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e){logString.AppendLine("Starting row update");}// handler for RowUpdated eventpublic 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 eventdep.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 datathis.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 isolationtran = 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 datacmd.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);}}}}
