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)); }
private IAsyncResult BeginTask(object sender, EventArgs e, AsyncCallback cb, object state) { string connectionString = WebConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString; con = new SqlConnection(connectionString); cmd = new SqlCommand("SELECT * FROM Employees", con); con.Open(); return cmd.BeginExecuteReader(cb, state); }
public IAsyncResult BeginFindAll(AsyncCallback callback, Object asyncState) { var query = from w in _db.Widgets select w; _beginFindAllCmd = _db.GetCommand(query) as SqlCommand; _db.Connection.Open(); return _beginFindAllCmd.BeginExecuteReader(callback, asyncState, System.Data.CommandBehavior.CloseConnection); }
/// <summary> /// Checks when a client's characters were last cached, against a timestamp received from the client. /// If the client's timestamp doesn't match the one in the DB (meaning it was older or newer), information /// about all the characters is sent to the client. /// </summary> /// <param name="Timestamp">The timestamp received from the client.</param> public static void CheckCharacterTimestamp(string AccountName, LoginClient Client, DateTime Timestamp) { SqlCommand Command = new SqlCommand("SELECT AccountName, NumCharacters, Character1, Character2, Character3 " + "FROM Accounts"); Command.Connection = m_Connection; Command.BeginExecuteReader(new AsyncCallback(EndCheckCharacterID), new DatabaseAsyncObject(AccountName, ref Client, Timestamp, Command)); }
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state) { sourceConnection = new SqlConnection(connectionStringOrigem.Get(context)); sourceConnection.Open(); commandSourceData = new SqlCommand(Query.Get(context), sourceConnection); var task = commandSourceData.BeginExecuteReader(callback, state, System.Data.CommandBehavior.Default); return task; }
private void btnAsynchronousCall_Click(object sender, EventArgs e) { if (isExecuting) { MessageBox.Show(this, "Already executing. Please wait until " + "the current query has completed."); } else { SqlCommand command = null; try { DisplayStatus("Connecting..."); //To Execute the command asynchronously we need to make Asynchronous Processing=true conn= new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;Asynchronous Processing=true"); command = new SqlCommand(); // To emulate a long-running query, wait for a few seconds before working with the data. // This command doesn't do much, but that's the point--it doesn't change your data, // in the long run. command.CommandText = "WAITFOR DELAY '00:00:05' ; SELECT * FROM Customers"; command.Connection = conn; conn.Open(); DisplayStatus("Executing..."); isExecuting = true; AsyncCallback callback = new AsyncCallback(HandleCallback); // Although it's not required that you pass the SqlCommand object as the second parameter // in the BeginExecuteNonQuery call, doing so makes it easier to call EndExecuteNonQuery // in the callback procedure. // Once the BeginExecuteNonQuery method is called, the code continues--and the user can // interact with the form--while the server executes the query. command.BeginExecuteReader(callback, command); } catch (Exception ex) { isExecuting = false; //DisplayStatus("Error: " + ex.Message); DisplayStatus( string.Format("Ready (last error: {0})", ex.Message)); if (conn!= null) { conn.Close(); } } } }
/// <summary> /// 异步执行SQL /// </summary> /// <param name="sql"></param> public void Execute(string sql) { if (string.IsNullOrEmpty(sql)) return; SqlConnection con = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(sql, con); con.Open(); cmd.BeginExecuteReader(new AsyncCallback((arg) => { if (null != this.OnExecuteCompleted && arg.IsCompleted) this.OnExecuteCompleted(this, new SqlAsyncExecuteResultArg { AsyncResult = arg }); }), null,System.Data.CommandBehavior.CloseConnection); }
/// <summary> /// 异步执行SQL /// </summary> /// <param name="connstr"></param> /// <param name="sql"></param> /// <param name="cmdtype"></param> /// <param name="prms"></param> public static void AsyncExecute(string connstr, string sql,CommandType cmdtype, params SqlParameter[] prms) { SqlConnectionStringBuilder connBuild = new SqlConnectionStringBuilder(connstr); connBuild.AsynchronousProcessing = true; SqlCommand cmd = new SqlCommand(); cmd.Connection = new SqlConnection(connBuild.ConnectionString); cmd.CommandTimeout = 6000 * 100; cmd.CommandType = cmdtype; cmd.CommandText = sql; if (null != prms) cmd.Parameters.AddRange(prms.ToArray()); cmd.Connection.Open(); cmd.BeginExecuteReader(CommandBehavior.CloseConnection); }
private void btnExecute_Click(object sender, EventArgs e) { //string connString = "Async=true;SERVER=(local);DATABASE=northwind;UID=sa;"; string connString = "Async=true;SERVER=www.j2ee.ca;DATABASE=northwind;uid=aspx;password=aspx;"; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand("SELECT lastname,firstname FROM employees", conn); _cmd = cmd; conn.Open(); AsyncCallback callback = new AsyncCallback(ProcessData); IAsyncResult iar = cmd.BeginExecuteReader(callback, null); iar.AsyncWaitHandle.WaitOne(); }
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); } }
public static void AsyncExecute(string connstr, string sql, CommandType cmdtype, params SqlParameter[] prms) { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connstr) { AsynchronousProcessing = true }; SqlCommand command = new SqlCommand { Connection = new SqlConnection(builder.ConnectionString), CommandTimeout = 0x927c0, CommandType = cmdtype, CommandText = sql }; if (prms != null) { command.Parameters.AddRange(prms.ToArray<SqlParameter>()); } command.Connection.Open(); command.BeginExecuteReader(CommandBehavior.CloseConnection); }
/// <summary> /// Checks whether or not an account existed, and whether or not the password supplied was correct. /// </summary> /// <param name="AccountName">The name of the account.</param> /// <param name="Client">The client that supplied the account.</param> /// <param name="Hash">The hash of the password (with the username as a salt).</param> public static void CheckAccount(string AccountName, LoginClient Client, byte[] Hash) { if (m_Connection == null) { if (GlobalSettings.Default.CreateAccountsOnLogin == false) { //TODO: Check if a flat file database exists, otherwise send an accountlogin failed packet. } else { //TODO: Write account into flat file DB if it doesn't exist. } } //Gets the data from both rows (AccountName & Password) SqlCommand Command = new SqlCommand("SELECT AccountName, Password FROM Accounts"); Command.Connection = m_Connection; Command.BeginExecuteReader(new AsyncCallback(EndCheckAccountName), new DatabaseAsyncObject(AccountName, ref Client, Command, Hash)); }
// Listen for outgoing message queue. private static void Consume() { // pooling timeouts const int listenCommandTimeoutValue = 10000; const int listenCommandTimeout = listenCommandTimeoutValue + 10000; // prepare call of sp to dequeue message from outgoing message queue var command = new SqlCommand("ListenOut", connection) { CommandType = CommandType.StoredProcedure, CommandTimeout = listenCommandTimeout }; command.Parameters.AddRange(new[] { new SqlParameter("@timeout", SqlDbType.Int) { Value = listenCommandTimeoutValue }, new SqlParameter(listenCommandMessageBodyName, SqlDbType.Xml) { Direction = ParameterDirection.Output } }); // wait for outgoing message command.BeginExecuteReader(OnConsumeComplete, command); }
/// <summary> /// Callback mehod for CheckCharacterTimestamp. /// This queries for the existence of a particular account /// in the DB and retrieves the character IDs associated with it. /// </summary> private static void EndCheckCharacterID(IAsyncResult AR) { DatabaseAsyncObject AsyncObject = AR.AsyncState as DatabaseAsyncObject; bool FoundAccountName = false; int NumCharacters = 0; int CharacterID1 = 0; int CharacterID2 = 0; int CharacterID3 = 0; using (SqlDataReader Reader = AsyncObject.Cmd.EndExecuteReader(AR)) { while (Reader.Read()) { if (((string)Reader[0]).ToUpper() == AsyncObject.AccountName.ToUpper()) { FoundAccountName = true; NumCharacters = (int)Reader[1]; if (NumCharacters == 0) break; else if (NumCharacters == 1) CharacterID1 = (int)Reader[2]; else if (NumCharacters == 2) { CharacterID1 = (int)Reader[2]; CharacterID2 = (int)Reader[3]; } else if (NumCharacters == 3) { CharacterID1 = (int)Reader[2]; CharacterID2 = (int)Reader[3]; CharacterID3 = (int)Reader[4]; } if (FoundAccountName == true) break; } } } if (FoundAccountName) { if (NumCharacters > 0) { SqlCommand Command = new SqlCommand("SELECT CharacterID, LastCached, Name, Sex FROM Character"); AsyncObject.NumCharacters = NumCharacters; AsyncObject.CharacterID1 = CharacterID1; AsyncObject.CharacterID2 = CharacterID2; AsyncObject.CharacterID3 = CharacterID3; Command.Connection = m_Connection; Command.BeginExecuteReader(new AsyncCallback(EndCheckCharacterTimestamp), AsyncObject); } else { PacketStream Packet = new PacketStream(0x05, 0); Packet.WriteByte(0x00); //0 characters. AsyncObject.Client.SendEncrypted(0x05, Packet.ToArray()); } } }
/// <summary> /// Save import file record /// </summary> public static int SaveImportFile(string SourceFileName,string ListName, int CustomerId, int CampaignId, int CountryId, SqlConnection objConnection, SqlTransaction objTransaction) { SqlCommand objCommand = new SqlCommand("bvSaveImportFile_sp", objConnection, objTransaction); objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@p_source_file_name", SqlDbType.NVarChar).Value = Path.GetFileName(SourceFileName); objCommand.Parameters.Add("@p_list_name", SqlDbType.NVarChar).Value = ListName; objCommand.Parameters.Add("@p_customer_id", SqlDbType.Int).Value = CustomerId; objCommand.Parameters.Add("@p_campaign_id", SqlDbType.Int).Value = CampaignId; objCommand.Parameters.Add("@p_country_id", SqlDbType.Int).Value = CountryId; objCommand.Parameters.Add("@p_created_by_id", SqlDbType.Int).Value = UserSession.CurrentUser.UserId; IAsyncResult objResult = objCommand.BeginExecuteReader(CommandBehavior.SingleRow); SqlDataReader objReader = objCommand.EndExecuteReader(objResult); objReader.Read(); int ImportFileId = Convert.ToInt32(objReader[0]); objReader.Close(); return ImportFileId; }
protected void Page_Load(object sender, EventArgs e) { if (Page.ClientQueryString == null) Response.Redirect("Error.aspx"); // Get user info from SQL // Query the DB string queryStr = Request.QueryString.ToString(); SqlConnection sqlCon = new SqlConnection(); SqlCommand sqlComm = new SqlCommand(); sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString); // Parse query string queryStr = Page.Request.QueryString["name"].ToString(); // Assure user is themselves or is admin for edit purposes bool canEdit; if (User.Identity.Name.Equals(queryStr) || User.Identity.Name.Equals("admin") || User.Identity.Equals("admin2")) canEdit = true; else canEdit = false; // Begin building the SQL query to populated the Edit boxes sqlComm.Parameters.Add("@Fname", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@MI", System.Data.SqlDbType.Char); sqlComm.Parameters.Add("@Lname", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@PNum", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Street", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@State", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@ZIP", System.Data.SqlDbType.Char); sqlComm.Parameters.Add("@School", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Degree", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Major", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Minor", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@GradDate", System.Data.SqlDbType.Date); sqlComm.Parameters.Add("@GPA", System.Data.SqlDbType.Float); sqlComm.Parameters.Add("@UEmail", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Employer", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpTitle", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@Sched", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpCtctInf", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpEmail", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpStrtDt", System.Data.SqlDbType.Date); sqlComm.Parameters.Add("@EmpEndDt", System.Data.SqlDbType.Date); sqlComm.Parameters.Add("@EmpHist", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpHistTitle", System.Data.SqlDbType.VarChar); sqlComm.Parameters.Add("@EmpHistEmail", System.Data.SqlDbType.VarChar); // SQL query here sqlComm.BeginExecuteReader(); // Return results will go here bool readOnly = !canEdit; // Populate with values FirstNameBox.Text = ""; // Some stuff to go here, can't leave it as just "" MiddleInitialBox.Text = ""; LastNameBox.Text = ""; PhoneNumBox.Text = ""; StreetBox.Text = ""; CityBox.Text = ""; StateDropdown.Text = ""; ZIPBox.Text = ""; UniversityTextBox.Text = ""; DegreeDropdown.Text = ""; MajorDropdown.Text = ""; MinorDropdown.Text = ""; GPABox.Text = ""; GraduationMonth.Text = ""; GradYearDropdown.Text = ""; UniversityEmailBox.Text = ""; EmployerBox.Text = ""; EmployeeTitleBox.Text = ""; ScheduleBox.Text = ""; EmployerContactInfoBox.Text = ""; EmployerEmailBox.Text = ""; EmployerStartDateDDDay.Text = ""; EmployerStartDateDDMonth.Text = ""; EmployerStartDateDDYear.Text = ""; EmployerEndDateDay.Text = ""; EmployerEndDateMonth.Text = ""; EmployerEndDateYear.Text = ""; EmployerHistoryBox.Text = ""; EmployerHistoryTitleBox.Text = ""; // Read only? FirstNameBox.ReadOnly = readOnly; MiddleInitialBox.ReadOnly = readOnly; LastNameBox.ReadOnly = readOnly; PhoneNumBox.ReadOnly = readOnly; StreetBox.ReadOnly = readOnly; CityBox.ReadOnly = readOnly; StateDropdown.Enabled = canEdit; ZIPBox.ReadOnly = readOnly; UniversityTextBox.ReadOnly = readOnly; DegreeDropdown.Enabled = canEdit; MajorDropdown.Enabled = canEdit; MinorDropdown.Enabled = canEdit; GPABox.ReadOnly = readOnly; GraduationMonth.Enabled = canEdit; GradYearDropdown.Enabled = canEdit; UniversityEmailBox.ReadOnly = readOnly; EmployerBox.ReadOnly = readOnly; EmployeeTitleBox.ReadOnly = readOnly; ScheduleBox.ReadOnly = readOnly; EmployerContactInfoBox.ReadOnly = readOnly; EmployerEmailBox.ReadOnly = readOnly; EmployerStartDateDDDay.Enabled = canEdit; EmployerStartDateDDMonth.Enabled = canEdit; EmployerStartDateDDYear.Enabled = canEdit; EmployerEndDateDay.Enabled = canEdit; EmployerEndDateMonth.Enabled = canEdit; EmployerEndDateYear.Enabled = canEdit; EmployerHistoryBox.ReadOnly = readOnly; EmployerHistoryTitleBox.ReadOnly = readOnly; // Finally, is edit button at bottom visible? EditUserButton.Enabled = canEdit; EditUserButton.Visible = canEdit; }
void GetData() { try { DisplayStatus("Connecting..."); var constr = new SqlAsyncConnectionString(); connection = new SqlConnection(constr); string commandText = " Select top 1 * from ITEMMAST order by 1 desc "; var command = new SqlCommand(commandText, connection); connection.Open(); DisplayStatus("Executing..."); isExecuting = true; AsyncCallback callback = HandleCallback; command.BeginExecuteReader(callback, command); // "WAITFOR DELAY '00:00:05';" + FOR XML raw , ELEMENTS XSINIL commandText = " SELECT top 1 * FROM supplier "; connection1 = new SqlConnection(constr); connection1.Open(); var command1 = new SqlCommand(commandText, connection1); AsyncCallback callback1 = HandleCallback1; command1.BeginExecuteReader(callback1, command1); } catch (Exception ex) { isExecuting = false; DisplayStatus(string.Format("Ready (last error:{0})", ex.Message)); if (connection != null) { connection.Close(); } if (connection1 != null) { connection1.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 IAsyncResult BeginExecuteReader(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.BeginExecuteReader(); } catch { throw; } }
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 void LoadExistingInstances() { EnsureOpen(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(findExistingInstancesSql, connection); try { connection.Open(); command.BeginExecuteReader(new AsyncCallback(OnEndFindExistingInstances), command); } catch (SqlException exception) { this.hostView.ErrorWriter.WriteLine("Could not load existing instances due to a Sql Exception: " + exception.ToString()); } }
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 = '*****@*****.**'", 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 *****/ }
public Sql ExecuteReader(SqlCommand cmd, Action<Exception, SqlDataReader> callback) { if (callback == null) throw new ArgumentNullException("callback"); cmd.Connection.Open(); var state = new ExecuteReaderState(cmd, callback); cmd.BeginExecuteReader(EndExecuteReader, state); return this; }
private void UploadNIDTtoAAMS() { SqlCommand SqlCommandHL = null; try { lblHLbar.Visible = true; pbarHL.Visible = true; lblHL.Visible = false; lblTimeSHL.Visible = true; hlstime.Visible = true; lblTimeEHL.Visible = true; hletime.Visible = true; hlstime.Text = String.Format("{0:T}", DateTime.Now); pbarHL.Maximum = 100; pbarHL.Minimum = 1; pbarHL.Value = 1; pbarHL.Value = pbarHL.Value + 10; DisplayStatus("Extracting NIDT data..."); isExecutingHL = true; btnExport.Enabled = true; //SqlCommandHL = new SqlCommand("select top 1000 location_code,name,address from location_master", objConHL); //call to make stored Proc name strStoredProcName = GetProcdureName(drpHLMonth.SelectedItem.ToString()); //strStoredProcName = "UP_NIDT_PRODUCTIVITY_FEB_NEW"; int param_month = System.Convert.ToInt16(drpHLMonth.SelectedIndex); int param_year = System.Convert.ToInt16(drpHLYear.SelectedItem); String param_country = System.Convert.ToString(drpHLcountry.Text); intYear = param_year; intMonth = param_month; StrCountry = param_country; StrCountryCode = System.Convert.ToString(drpHLcountry.SelectedValue); //String param_country = System.Convert.ToString(drpHLcountry.SelectedValue); strMonthyearHL = drpHLMonth.SelectedItem.ToString() + "" + param_year.ToString(); SqlCommandHL = new SqlCommand(); SqlCommandHL.CommandType = CommandType.StoredProcedure; SqlCommandHL.CommandText = strStoredProcName; SqlCommandHL.Connection = objConHL; SqlCommandHL.Parameters.Add(new SqlParameter("@MONTH", SqlDbType.Int)); SqlCommandHL.Parameters["@MONTH"].Value = param_month; SqlCommandHL.Parameters.Add(new SqlParameter("@YEAR", SqlDbType.Int)); SqlCommandHL.Parameters["@YEAR"].Value = param_year; SqlCommandHL.Connection.Open(); //start clock for HL myCallBackWatchHL.Start(); AsyncCallback myCallBackHL = new AsyncCallback(HandleCallbackHL); SqlCommandHL.BeginExecuteReader(myCallBackHL, SqlCommandHL); } catch (Exception exe) { DisplayStatus("Error while export HL.." + exe.Message); if (((System.Data.SqlClient.SqlException)(exe)).Number == 53) { MessageBox.Show("NIDT server line may be down.please contact to Admin!", "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Stop); } else { MessageBox.Show(exe.Message, "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Stop); } isExecutingHL = false; pbarHL.Maximum = 100; pbarHL.Minimum = 1; pbarHL.Value = 1; pbarHL.Visible = false; } finally { } }
private static void TestExecuteReaderMethod() { Console.WriteLine("Staring of Execute Reader Method...."); _reset.Reset(); try { using (SqlConnection conn = new SqlConnection(ConnectionStr)) { using (SqlCommand cmd = new SqlCommand(StoredProc, conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.InfoMessage += ConnectionInfoMessage; AsyncCallback result = ReaderCallback; cmd.Connection.Open(); cmd.BeginExecuteReader(result, cmd); Console.WriteLine("Waiting for completion of executing stored procedure...."); _reset.WaitOne(); } } } catch (SqlException ex) { Console.WriteLine("Problem with executing command! - [{0}]", ex.Message); } Console.WriteLine("Completion of Execute Reader Method...."); }
/// <summary> /// Save and get import file header ids /// </summary> public static List<ImportFileHeaderIdInstance> SaveImportFileHeaders(int ImportFileId, DataTable objTable, SqlConnection objConnection, SqlTransaction objTransaction) { SqlCommand objCommand = null; SqlDataReader objReader = null; IAsyncResult objResult = null; List<ImportFileHeaderIdInstance> objList = new List<ImportFileHeaderIdInstance>(); ImportFileHeaderIdInstance objFileHeader = null; foreach (DataRow Item in objTable.Rows) { objCommand = null; objCommand = new SqlCommand("bvSaveImportFileHeaders_sp", objConnection, objTransaction); objCommand.CommandType = CommandType.StoredProcedure; objCommand.Parameters.Add("@p_import_file_id", SqlDbType.Int).Value = Item["imported_file_id"]; objCommand.Parameters.Add("@p_column_order", SqlDbType.Int).Value = Item["column_order"]; objCommand.Parameters.Add("@p_column_name", SqlDbType.NVarChar).Value = Item["column_name"]; objReader = null; objResult = null; objResult = objCommand.BeginExecuteReader(CommandBehavior.SingleRow); objReader = objCommand.EndExecuteReader(objResult); objReader.Read(); objFileHeader = null; objFileHeader = new ImportFileHeaderIdInstance(); objFileHeader.id = (int) objReader[0]; objFileHeader.column_name = Item["column_name"].ToString(); objReader.Close(); objList.Add(objFileHeader); } return objList; }
void btnExecute_Click(object sender, EventArgs e) { timer1.Enabled = true; Boolean objvalidate; EnableDisableMenuItems(); objvalidate = ValidateControls(); if (objvalidate == false) { if (isExecutingCODD || isExecutingHL || isExecutingHX) { MessageBox.Show(this, "Already executing. Please wait until " + "the current query has completed."); } else { SqlCommand SqlCommandCODD = null; SqlCommand SqlCommandHX = null; SqlCommand SqlCommandGrp = null; dataGridView1.DataSource = null; dataGridView2.DataSource = null; dataGridView3.DataSource = null; this.toolStripStatusLabel1.Text = ""; grpStatusbar.Visible = true; clearControls(); try { if (chkCODD.Checked == true && chkHL.Checked == true && chkHX.Checked == true) { DisplayStatus("Connecting..."); DisplayStatus("Executing..."); } if (chkCODD.Checked == false && chkHL.Checked == false && chkHX.Checked == false) { isExecutingCODD = false; } if (chkCODD.Checked == true) { lblCbar.Visible = true; pbarCODD.Visible = true; lblC.Visible = false; lblTimeSCODD.Visible = true; coddstime.Visible = true; lblTimeECODD.Visible = true; coddetime.Visible = true; coddstime.Text = String.Format("{0:T}", DateTime.Now); pbarCODD.Maximum = 100; pbarCODD.Minimum = 1; pbarCODD.Value = 1; pbarCODD.Value = pbarCODD.Value + 10; //objConCODD.Open(); //start the watch for myCallBackWatchCODD.Start(); isExecutingCODD = true; btnExport.Enabled = true; int param_month = System.Convert.ToInt16(drpCMonth.SelectedIndex); int param_year = System.Convert.ToInt16(drpCyear.SelectedItem); String param_country = System.Convert.ToString(drpCcountry.Text); intYear = param_year; intMonth = param_month; StrCountry = param_country; StrCountryCode = System.Convert.ToString(drpCcountry.SelectedValue); //SqlCommandCODD = new SqlCommand("select top 100 * from location_master", objConCODD); SqlCommandCODD = new SqlCommand(); SqlCommandCODD.CommandType = CommandType.StoredProcedure; SqlCommandCODD.CommandText = "UP_NIDT_PRODUCTIVITY_CODD"; SqlCommandCODD.Connection = objConCODD; SqlCommandCODD.Parameters.Add(new SqlParameter("@MONTH", SqlDbType.Int)); SqlCommandCODD.Parameters["@MONTH"].Value = param_month; SqlCommandCODD.Parameters.Add(new SqlParameter("@YEAR", SqlDbType.Int)); SqlCommandCODD.Parameters["@YEAR"].Value = param_year; strMonthyearCODD = drpCMonth.SelectedItem.ToString() + "" + param_year.ToString(); SqlCommandCODD.Parameters.Add(new SqlParameter("@COUNTRY", SqlDbType.VarChar, 3)); if (StrCountryCode == "0") SqlCommandCODD.Parameters["@COUNTRY"].Value = DBNull.Value; else SqlCommandCODD.Parameters["@COUNTRY"].Value = StrCountryCode; SqlCommandCODD.Connection.Open(); AsyncCallback myCallBackCODD = new AsyncCallback(HandleCallbackCODD); SqlCommandCODD.BeginExecuteReader(myCallBackCODD, SqlCommandCODD); } else { lblCbar.Visible = false; pbarCODD.Visible = false; lblTimeSCODD.Visible = false; coddstime.Visible = false; lblTimeECODD.Visible = false; coddetime.Visible = false; } if (chkHL.Checked == true) { /*New code implemented for the Group data date 30/04/2015 * 1. check group data availability in system * 2. Alert to User i.e data found/not found * 3. keep found data in global datatable * 4. Alert Payments exists for the selected Month and Year */ strGrpdata_Process_Status = ""; strStoredProcName = "[UP_GET_INC_AIRLINE_GROUP_DATA]"; int param_monthgrp = System.Convert.ToInt16(drpHLMonth.SelectedIndex); int param_yeargrp = System.Convert.ToInt16(drpHLYear.SelectedItem); SqlCommandGrp = new SqlCommand(); SqlCommandGrp.CommandType = CommandType.StoredProcedure; SqlCommandGrp.CommandText = strStoredProcName; SqlCommandGrp.Connection = objConLivedatabase; SqlCommandGrp.Parameters.Add(new SqlParameter("@MONTH", SqlDbType.Int)); SqlCommandGrp.Parameters["@MONTH"].Value = param_monthgrp; SqlCommandGrp.Parameters.Add(new SqlParameter("@YEAR", SqlDbType.Int)); SqlCommandGrp.Parameters["@YEAR"].Value = param_yeargrp; if (SqlCommandGrp.Connection.State == ConnectionState.Open) { SqlCommandGrp.Connection.Close(); } SqlCommandGrp.Connection.Open(); AsyncCallback myCallBackGrp = new AsyncCallback(HandleCallbackGrp); SqlCommandGrp.BeginExecuteReader(myCallBackGrp, SqlCommandGrp); /*end New code implemented for the Group data date 30/04/2015*/ } else { lblHLbar.Visible = false; pbarHL.Visible = false; lblTimeSHL.Visible = false; hlstime.Visible = false; lblTimeEHL.Visible = false; hletime.Visible = false; } if (chkHX.Checked == true) { lblHXbar.Visible = true; pbarHX.Visible = true; lblHX.Visible = false; lblTimeSHX.Visible = true; hxstime.Visible = true; lblTimeEHX.Visible = true; hxetime.Visible = true; hxstime.Text = String.Format("{0:T}", DateTime.Now); pbarHX.Maximum = 100; pbarHX.Minimum = 1; pbarHX.Value = 1; pbarHX.Value = pbarHX.Value + 10; //objConHX.Open(); //start the watch for myCallBackWatchHX.Start(); isExecutingHX = true; btnExport.Enabled = true; //SqlCommandHX = new SqlCommand("select top 1000 location_code,name,address from location_master", objConHX); int param_month = System.Convert.ToInt16(drpHXMonth.SelectedIndex); int param_year = System.Convert.ToInt16(drpHXYear.SelectedItem); String param_country = System.Convert.ToString(drpHXcountry.Text); intYear = param_year; intMonth = param_month; StrCountry = param_country; StrCountryCode = System.Convert.ToString(drpHXcountry.SelectedValue); //SqlCommandCODD = new SqlCommand("select top 100 * from location_master", objConCODD); SqlCommandHX = new SqlCommand(); SqlCommandHX.CommandType = CommandType.StoredProcedure; SqlCommandHX.CommandText = "UP_NIDT_PRODUCTIVITY_HX"; SqlCommandHX.Connection = objConHX; SqlCommandHX.Parameters.Add(new SqlParameter("@MONTH", SqlDbType.Int)); SqlCommandHX.Parameters["@MONTH"].Value = param_month; SqlCommandHX.Parameters.Add(new SqlParameter("@YEAR", SqlDbType.Int)); SqlCommandHX.Parameters["@YEAR"].Value = param_year; SqlCommandHX.Parameters.Add(new SqlParameter("@COUNTRY", SqlDbType.VarChar, 3)); if (StrCountryCode == "0") SqlCommandHX.Parameters["@COUNTRY"].Value = DBNull.Value; else SqlCommandHX.Parameters["@COUNTRY"].Value = StrCountryCode; strMonthyearHX = drpHXMonth.SelectedItem.ToString() + "" + param_year.ToString(); SqlCommandHX.Connection.Open(); AsyncCallback myCallBackHX = new AsyncCallback(HandleCallbackHX); SqlCommandHX.BeginExecuteReader(myCallBackHX, SqlCommandHX); } else { lblHXbar.Visible = false; pbarHX.Visible = false; lblTimeSHX.Visible = false; hxstime.Visible = false; lblTimeEHX.Visible = false; hxetime.Visible = false; } } catch (Exception ex) { isExecutingCODD = false; DisplayStatus(string.Format("Ready (last error: {0})", ex.Message)); if (objConCODD != null) { objConCODD.Close(); } if (objConHL != null) { objConHL.Close(); } if (objConHX != null) { objConHX.Close(); } //MessageBox.Show(ex.Message); } } } }
/// <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 IAsyncResult DoBeginExecuteReader(SqlCommand command, bool disposeCommand, AsyncCallback callback, object state) { CommandBehavior commandBehavior = command.Transaction == null ? CommandBehavior.CloseConnection : CommandBehavior.Default; try { return WrappedAsyncOperation.BeginAsyncOperation( callback, cb => command.BeginExecuteReader(cb, state, commandBehavior), ar => new DaabAsyncResult(ar, command, disposeCommand, false, DateTime.Now)); } catch (Exception e) { instrumentationProvider.FireCommandFailedEvent(command.CommandText, ConnectionStringNoCredentials, e); throw; } }
/// <summary> /// Executes a query statement asynchronously expecting a set of return value. /// </summary> /// <param name="queryID">Query ID for result set identification.</param> /// <param name="tableName">Name of the table.</param> /// <param name="query">SQL query.</param> /// <param name="param">SQL parameter with name and value.</param> public void BeginExecuteReader(int queryID, string tableName, string query, params SqlParameter[] param) { try { _semaphore.WaitOne(); Open(); using (var command = new SqlCommand(query, _sqlConnection)) { foreach (var p in param) command.Parameters.Add(p); command.Prepare(); command.BeginExecuteReader(new AsyncCallback(HandleCallback), new Tuple<int, string, SqlCommand>(queryID, tableName, command)); } } catch { Close(); _semaphore.Release(); throw; } }