When commands are executed synchronously, the thread is waiting for a response from the database server and it is getting into a blocking state (until the command is finishing to be executed).
protected void ExecuteCommandInSynchronousAccess()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText("UPDATE CUSTOMERS SET NAME='Yuval'");
// Processing the command on the SQL server...
// Waiting for return results
int affectedRowNumber = command.ExecuteNonQuery();
}
}
}
protected void ExecuteCommandInAsynchronousAccess()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand command = new SqlCommand(connection);
command.CommandText("Select CUSTOMERS.NAME FROM CUSTOMERS");
command.BeginExecuteNonQuery(new AsyncCallback(ProcessResult),command);
}
// Run in a new thread,and not blocking the current program
public void ProcessResult(IAsyncResult asyncResult)
{
SqlCommand command = (SqlCommand)asyncResult.AsyncState;
using (command.Connection)
{
using (command)
{
int affectedRowNumber = command.EndExecuteNonQuery();
}
}
}