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