SYNCHRONOUS AND ASYNCHRONOUS COMMAND EXECUTION IN C#.NET

In this blog, I will tell you that what is synchronous command execution and what is asynchronous command execution. Normally whatever command we will execute in ado.net that will be executed synchronously that means until the process of execution of current command is begin we cannot move for next line to be executed. We know that command are executed in SQL server and If it will take about 10 minutes to complete its process the we have to wait 10 minutes then we move to execute next statement. In this situation we need concept of asynchronous programming where we don’t need to wait to finish execution of current command.

Although every mechanism has their own advantage and disadvantage. We need to analyze the situation that which mechanism is important to implement this task.

Here I will give you a small demonstration which will help you to understand the concept of asynchronous and synchronous database programming in ADO.NET.

Example which represent use of Synchronous programming in ADO.NET

/// <summary>/// ExecuteNonQuery(), ExecuteReader() and ExecuteScaler() supports functionality for synchronous execution./// The connection string for these process will be same as you will provide for rest of example./// </summary>public void synchronousExample(){SqlConnection con = new SqlConnection();//Create an SqlConnection object.//Create a connection string. For Synchronous it will same as usual you use but for asynchronous an addition keyword is added in connection string.string connString = "Data Source=AA-PC;User Id=sa;Password=bbbb;Initial Catalog=Workbook";con.ConnectionString = connString;//Pass connection string to SqlConnection class object.con.Open();//Open connection.string query = "select * from product";//create a query variable.SqlCommand cmd = new SqlCommand(query, con);//Create a sqlcommand object and pass query and connection object as parameter. First parameter should be query and second parameter is connection object.//Execute query by calling ExecuteReader method.cmd.ExecuteReader();//It will return a DataReader object which contains query result. Here we does not store it.Console.WriteLine("Command executed successfully.");con.Close();//Close SqlConnection object. }

Example which represents use of Asynchronous programing in ado.net

/// <summary>/// This method will display example of asynchronous execution which/// will perform by calling method BeginExecuteNonQuery(), BeginExecuteReader(), EndExecuteQuery()/// EndExecuteReader() etc. When we call BeginExecuteReader() method then it will return IAsyncResult object/// which will be passed to EndExecuteReader() method when we want output of query./// Additional key word which is added in connection string is Asynchronous Processing=true; which tell us/// that this command will be executed as asynchronously./// </summary>public void asynchronousExample(){SqlConnection con = new SqlConnection();//Create an SqlConnection object.string connString = "Data Source= AA-PC;User Id=sa;Password=bbbb;Initial Catalog=Workbook;Asynchronous Processing=true;";con.ConnectionString = connString;con.Open();//Open SqlConnaction object by calling Open() method of SqlConnection class.string query = "select * from product";//create a query variable.SqlCommand cmd = new SqlCommand(query, con);//Create a sqlcommand object and pass query and connection object as parameter. First parameter should be query and second parameter is connection object.IAsyncResult result = cmd.BeginExecuteReader(CommandBehavior.SequentialAccess);//Call BeginExecuteReader() method which will start Asynchronous execution.Console.WriteLine("Asynchronous execution is started.");SqlDataReader dr = cmd.EndExecuteReader(result);//Call end execute reader method which will display that execution has finished.//Display record form datareader object and close connection from server.con.Close(); }