C# SqlCommand.EndExecuteReader方法代码示例

        public AsyncConnect()
        {
            SqlConnection connection = new SqlConnection();
             SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
             connectionBuilder.DataSource = @"(local)\SQLEXPRESS";
             connectionBuilder.IntegratedSecurity = true;
             connectionBuilder.AsynchronousProcessing = true;
             connectionBuilder.InitialCatalog = "AutoLot";
             connection.ConnectionString = connectionBuilder.ConnectionString;
             connection.Open();

             string query = "WaitFor Delay '00:00:05';Select * From Inventory";
             SqlCommand command = new SqlCommand(query, connection);

             IAsyncResult connectionResult = command.BeginExecuteReader(CommandBehavior.CloseConnection);

             while (!connectionResult.IsCompleted)
             {
            Console.WriteLine("Working on Main thread...");
            Thread.Sleep(1000);
             }
             Console.WriteLine();

             SqlDataReader reader = command.EndExecuteReader(connectionResult);

             Console.WriteLine(DbUtils.ReaderToString(reader));
        }

        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=csharp;Integrated Security=True");
            conn.Open();
            SqlCommand cmd = new SqlCommand("select Id, Nome from Categoria", conn);

            SqlDataReader r = cmd.EndExecuteReader();

            while (r.Read()) {
                Console.WriteLine("Categoria: {0}", r["Nome"].ToString());
            }
            conn.Close();
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            string connString = "Async=true;SERVER=(local);DATABASE=northwind;UID=sa;";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand("SELECT lastname,firstname FROM employees", conn);
                conn.Open();
                IAsyncResult iar = cmd.BeginExecuteReader();

                // Do something here

                SqlDataReader reader = (SqlDataReader)cmd.EndExecuteReader(iar);
                ProcessData(reader);
            }
        }

        /// <summary>
        /// 执行查询语句(SqlDataReader)
        /// 调用前connection需要处于Open状态
        /// 调用得到SqlDataReader，使用后需要将connection关闭
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="connection"></param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader GetSqlDataReader(string cmdText, SqlConnection connection)
        {
            //判断调用本方法前，连接对象connection是否已经打开
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();//自动为其打开
            }

            try
            {
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                IAsyncResult result = cmd.BeginExecuteReader(); //异步访问数据库
                //使用异步访问，便后期拓展
                //等待BeginExecuteReader操作完
                while (true)
                {
                    if (result.IsCompleted)
                    {
                        SqlDataReader reader = cmd.EndExecuteReader(result); //将异步结果转换为SqlDataReader
                        return reader;
                    }
                    else
                    {
                        Thread.Sleep(500);//减少资源占用率
                    }
                }
            }
            /////////异常处理/////////
            catch (SqlException ex)
            {
                Debug.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
                return null;
            }
            catch (InvalidOperationException ex)
            {
                 Debug.WriteLine("Error: {0}", ex.Message);
                 return null;
            }
            catch (Exception ex)
            {
                // You might want to pass these errors
                // back out to the caller.
                Debug.WriteLine("Error: {0}", ex.Message);
                return null;
            }
        }

        private static void SampleAsyncMethods()
        {
            IAsyncResult asyncResult;

            /***** SQL Connection *****/
            // NOTE: "Async=true" setting required for asynchronous operations.
            using (SqlConnection connection = new SqlConnection(@"Async=true;Server=SERVER;Database=DATABASE;Integrated Security=true"))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT UserId, Name, LastLogIn FROM Users WHERE Email = '[email protected]'", connection))
                {
                    asyncResult = cmd.BeginExecuteReader();
                    // ... query executes asynchronously in background ...
                    using (IDataReader reader = cmd.EndExecuteReader(asyncResult))
                    {
                        // WARNING: The DbAsyncResult object returned by BeginExecuteReader always creates a ManualResetEvent, but
                        // never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
                        asyncResult.AsyncWaitHandle.Close();

                        while (reader.Read())
                        {
                            // do stuff
                        }
                    }
                }

                using (SqlCommand cmd = new SqlCommand("UPDATE Users SET LastLogIn = GETUTCDATE() WHERE UserId = 1", connection))
                {
                    asyncResult = cmd.BeginExecuteNonQuery();
                    // ... query executes asynchronously in background ...
                    int rowsAffected = cmd.EndExecuteNonQuery(asyncResult);

                    // WARNING: The DbAsyncResult object returned by BeginExecuteNonQuery always creates a ManualResetEvent, but
                    // never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
                    asyncResult.AsyncWaitHandle.Close();
                }
            }

            /***** File Operations *****/
            // NOTE: FileOptions.Asynchronous flag required for asynchronous operations.
            using (Stream stream = new FileStream(@"C:\Temp\test.dat", FileMode.Open, FileAccess.Read, FileShare.ReadWrite, 4096,
                FileOptions.Asynchronous))
            {
                byte[] buffer = new byte[65536];
                asyncResult = stream.BeginRead(buffer, 0, buffer.Length, null, null);
                // ... disk read executes asynchronously in background ...
                int bytesRead = stream.EndRead(asyncResult);
            }

            /***** HTTP Operation *****/
            // WARNING: DNS operations are synchronous, and will block!
            WebRequest request = WebRequest.Create(new Uri(@"http://www.example.com/sample/page"));
            request.Method = "POST";
            request.ContentType = "application/x-www-form-urlencoded";

            asyncResult = request.BeginGetRequestStream(null, null);
            // ... connection to server opened in background ...
            using (Stream stream = request.EndGetRequestStream(asyncResult))
            {
                byte[] bytes = Encoding.UTF8.GetBytes("Sample request");
                asyncResult = stream.BeginWrite(bytes, 0, bytes.Length, null, null);
                stream.EndWrite(asyncResult);
            }

            // WARNING: WebRequest will swallow any exceptions thrown from the AsyncCallback passed to BeginGetResponse.
            asyncResult = request.BeginGetResponse(null, null);
            // ... web request executes in background ...
            using (WebResponse response = request.EndGetResponse(asyncResult))
            using (Stream stream = response.GetResponseStream())
            {
                // read response from server
                // WARNING: This code should also use asynchronous operations (BeginRead, EndRead); "Using synchronous calls
                //   in asynchronous callback methods can result in severe performance penalties." (MSDN)
            }

            /***** DNS hostname resolution *****/
            // WARNING: Doesn't truly use async I/O, but simply queues the request to a ThreadPool thread.
            asyncResult = Dns.BeginGetHostEntry("www.example.com", null, null);
            // ... DNS lookup executes in background
            IPHostEntry entry = Dns.EndGetHostEntry(asyncResult);

            /***** Other: Sockets, Serial Ports, SslStream *****/
        }

        protected void QueryButton_Click(object sender, EventArgs e)
        {
            // You might want to move this code out to a separate class
            // Using two connection strings to simulate distinct databases

            string custID = CustomerList.SelectedValue.ToString();

            using (SqlConnection conn1 = new SqlConnection(ConnString))
            using (SqlConnection conn2 = new SqlConnection(ConnString))
            {
                // Fire the first command: get customer info
                SqlCommand cmd1 = new SqlCommand(CustomerInfoCmd, conn1);
                cmd1.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
                conn1.Open();
                IAsyncResult arCustomerInfo = cmd1.BeginExecuteReader();

                // Fire the second command: get order info
                SqlCommand cmd2 = new SqlCommand(CustomerOrderHistory, conn2);
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
                conn2.Open();
                IAsyncResult arOrdersInfo = cmd2.BeginExecuteReader();

                // Prepare wait objects to sync up 
                WaitHandle[] handles = new WaitHandle[2];
                handles[0] = arCustomerInfo.AsyncWaitHandle;
                handles[1] = arOrdersInfo.AsyncWaitHandle;
                SqlDataReader reader;

                // Wait (no longer than 5 secs)
                for (int i = 0; i < 2; i++)
                {
                    StringBuilder builder = new StringBuilder();
                    int index = WaitHandle.WaitAny(handles, 5000, false);
                    if (index == WaitHandle.WaitTimeout)
                        throw new Exception("Timeout expired");

                    if (index == 0)
                    {
                        reader = cmd1.EndExecuteReader(arCustomerInfo);

                        // Only one record caught here
                        if (!reader.Read())
                            continue;

                        builder.AppendFormat("Company Name: {0}\n", reader["companyname"]);
                        builder.AppendFormat("Address: {0}\n", reader["address"]);
                        builder.AppendFormat("Country: {0}\n", reader["country"]);
                        Info.Text = builder.ToString();
                        reader.Close();
                    }

                    if (index == 1)
                    {
                  
                        reader = cmd2.EndExecuteReader(arOrdersInfo);


                        // Only one record caught here
                        if (!reader.Read())
                            continue;

                        builder.AppendFormat("Product Name: {0}\n", reader["ProductName"]);
                        builder.AppendFormat("Total: {0}\n", reader["Total"]);
                       
                        MInfo.Text = builder.ToString();
                        reader.Close();

                                                
                    }
                }
            }
        }

        public DataTable ExecuteDataTableTest(String procName, SqlParameter[] parms)
        {
            DataTable dataTable = null;
            SqlDataReader reader1=null;
            try
            {
                SqlCommand command = new SqlCommand(procName);
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;
                if (_sqlTransAction != null)
                    command.Transaction = _sqlTransAction;

                if (parms != null)
                {
                    for (int i = 0; i <= parms.Length - 1; i++)
                    {
                        command.Parameters.Add(parms[i]);
                    }
                }

                IAsyncResult result = command.BeginExecuteReader();
                WaitHandle waitHandle = result.AsyncWaitHandle;
                WaitHandle[] waitHandles = {waitHandle};

                int index;
                for (int countWaits = 0; countWaits <= 1; countWaits++)
                {
                    //  WaitAny waits for any of the processes to
                    //  complete. The return value is either the index
                    //  of the array element whose process just
                    //  completed, or the WaitTimeout value.

                    index = WaitHandle.WaitAny(waitHandles,60000000, false);
                    //  This example doesn't actually do anything with
                    //  the data returned by the processes, but the
                    //  code opens readers for each just to demonstrate
                    //  the concept.
                    //  Instead of using the returned data to fill the
                    //  controls on the page, the example adds the time
                    //  the process was completed to the corresponding
                    //  text box.

                    switch (index)
                    {
                        case 0:
                            reader1 =
                                 command.EndExecuteReader(result);
                            reader1.Close();
                            break;

                    }
                }

                //SqlDataAdapter sqlDatatAdapter = new SqlDataAdapter(command);

                dataTable = new DataTable();
                dataTable.Load(reader1);
                //sqlDatatAdapter.Fill(dataTable);
            }
            catch
            {
                throw;
            }
            finally
            {

            }

            return dataTable;
        }

        public SqlDataReader EndExecuteReader(IAsyncResult result, string procName, SqlParameter[] parms)
        {
            try
            {
                SqlCommand command = new SqlCommand(procName);
                command.Connection = connection;
                command.CommandType = CommandType.StoredProcedure;

                if (parms != null)
                {
                    for (int i = 0; i <= parms.Length - 1; i++)
                    {
                        command.Parameters.Add(parms[i]);
                    }
                }

                return command.EndExecuteReader(result);
            }
            catch
            {
                throw;
            }
        }

        private void QueryButton_Click(object sender, EventArgs e)
        { 
            string custID = CustomerList.SelectedValue.ToString();
                        
            // You might want to move this code out to a separate class
            // Using two connection strings to simulate distinct databases
            
            // To make connection pooling work effectively, it is extremely 
            // important that connnection objects are returned to the pool 
            // as soon as possible. A connection object that goes out of scope
            // is not closed and, therfore, not immediately returned. 
            //  SqlConnection conn = new SqlConnection(ConnString);
            //  Try{
            //       Conn.Open();
            //       // Do something here
            //  }
            //  Catch{
            //      // Trap errors here
            //  }
            //  Finally{
            //      Conn.Close();
            //  }
            //  Or 
            //  Using (SqlConnection conn1 = new SqlConnection(ConnString))
            //  {
            //      // Do something here
            //  // Trap errors here
            //  }
    

            using (SqlConnection conn1 = new SqlConnection(ConnString))
            using (SqlConnection conn2 = new SqlConnection(ConnString))
            {
                // Fire the first command: get customer info
                SqlCommand cmd1 = new SqlCommand(CustomerInfoCmd, conn1);
                cmd1.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
                conn1.Open();
                IAsyncResult arCustomerInfo = cmd1.BeginExecuteReader();

                // Fire the second command: get order info
                SqlCommand cmd2 = new SqlCommand(CustomerOrderHistory, conn2);
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
                conn2.Open();
                IAsyncResult arOrdersInfo = cmd2.BeginExecuteReader();

                // Prepare wait objects to sync up 
                WaitHandle[] handles = new WaitHandle[2];
                handles[0] = arCustomerInfo.AsyncWaitHandle;
                handles[1] = arOrdersInfo.AsyncWaitHandle;
                SqlDataReader reader;

                // Wait (no longer than 5 secs)
                for (int i = 0; i < 2; i++)
                {
                    StringBuilder builder = new StringBuilder();
                    int index = WaitHandle.WaitAny(handles, 5000, false);
                    if (index == WaitHandle.WaitTimeout)
                        throw new Exception("Timeout expired");

                    if (index == 0)
                    {
                        reader = cmd1.EndExecuteReader(arCustomerInfo);

                        // Only one record caught here
                        if (!reader.Read())
                            continue;

                        builder.AppendFormat("{0}\n", reader["companyname"]);
                        builder.AppendFormat("{0}\n", reader["address"]);
                        builder.AppendFormat("{0}\n", reader["country"]);
                        Info.Text = builder.ToString();
                        reader.Close();
                    }

                    if (index == 1)
                    {
                        reader = cmd2.EndExecuteReader(arOrdersInfo);
                         gridOrders.DataSource = reader; // Does not work here! 
                        
                        
                        reader.Close();
                        
                                        
                        DataSet ds = new DataSet();
                        SqlDataAdapter da = new SqlDataAdapter(cmd2);
                        da.Fill(ds, "Customers");
                        gridOrders.DataSource = ds.Tables["Customers"].DefaultView;
                       
                        
                    }
                }
            }
        }

 /// <summary>
 /// 创建并准备数据执行命令对象，同时以适当的 CommandBehavior 属性调用 ExecuteReader 方法。
 /// </summary>
 /// <remarks>
 /// 如果创建并打开数据库连接对象，当 DataReader 被关闭时必须关闭数据库连接。
 /// 如果是由调用方提供数据库连接对象，不必进行任何关闭操作，由调用方进行管理。
 /// </remarks>
 /// <param name="connection">有效的数据库连接对象</param>
 /// <param name="transaction">有效的事务对象，或者为 null</param>
 /// <param name="commandType">获取或设置一个值，该值指示如何解释 CommandText 属性</param>
 /// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param>
 /// <param name="commandParameters">用来执行命令的参数数组，如果不必提供参数数组可设置为 null</param>
 /// <param name="connectionOwnership">指示调用方是否提供数据库连接，或者由数据访问帮助类创建</param>
 /// <param name="callback">异步期间需要调用的Callback函数</param>
 /// <param name="commandTimeout">设定Command执行时间，秒，大于0有效</param>
 /// <returns>执行命令后返回包含结果的数据读取对象</returns>
 private static SqlDataReader ExecuteReaderAsync(SqlConnection connection,SqlTransaction transaction,CommandType commandType,string commandText,AsyncCallback callback,int commandTimeout,IEnumerable<SqlParameter> commandParameters,SqlConnectionOwnership connectionOwnership)
 {
     if(connection == null){
         throw new ArgumentNullException("connection");
     }
     bool mustCloseConnection = false;
     SqlCommand cmd = new SqlCommand();
     SqlDataReader result;
     try{
         PrepareCommand(cmd,connection,transaction,commandType,commandText,commandParameters,out mustCloseConnection);
         if(commandTimeout > 0){
             cmd.CommandTimeout = commandTimeout;
         }
         SqlDataReader dataReader;
         if(connectionOwnership == SqlConnectionOwnership.External){
             IAsyncResult asyncResult = cmd.BeginExecuteReader(null,null);
             if(callback != null){
                 callback(asyncResult);
             }
             dataReader = cmd.EndExecuteReader(asyncResult);
         }
         else{
             IAsyncResult asyncResult2 = cmd.BeginExecuteReader(null,null,CommandBehavior.CloseConnection);
             if(callback != null){
                 callback(asyncResult2);
             }
             dataReader = cmd.EndExecuteReader(asyncResult2);
         }
         bool canClear = true;
         foreach(SqlParameter commandParameter in cmd.Parameters){
             if(commandParameter.Direction != ParameterDirection.Input){
                 canClear = false;
             }
         }
         if(canClear){
             cmd.Parameters.Clear();
         }
         result = dataReader;
     }
     catch{
         if(mustCloseConnection){
             connection.Close();
         }
         throw;
     }
     return result;
 }