NET Framework 2.0中的数据访问新特性

1异步数据访问
　　a)支持异步数据编程
　　b)SqlConnection
　　　　– BeginOpen
　　　　– EndOpen 
　　c)SqlCommand
　　　　– BeginExecuteNonQuery
　　　　– BeginExecuteReader
　　　　– BeginExecuteXmlReader
　　　　– EndExecuteNonQuery
　　　　– EndExecuteReader
　　　　– EndExecuteXmlReader
代码如下:(注意字符串连接,Asynchronous Processing=true)

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

代码如下:

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 提供的性能要明显优于这些方式。
代码如下(此处只做演示):

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 一致。

代码如下:

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 类，使用它可以用来访问自定义通知队列

代码如下:

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

代码如下:

    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"

代码如下(注意链接字符串):

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