EXCELENTE el articulo de Keyvan Nayyari sobre modo asincronico en net.
Link http://nayyeri.net/blog/Asynchronous-command-execution-in-NET-2-0/
Abajo recopilo
Asynchronous execution of commands was one of great new features in ADO.NET 2.0. The ability to execute commands that take long time to execute asynchronously is very helpful for developers.
The process of executing a command asynchronously is very simple and easy and I talk about it in this post.
Database
Before anything I create two stored procedures for SQL Server Northwind sample database to have a final stored procedure that takes time to execute.
First I create a stored procedure, SelectOrders, to select all orders from Orders table.
CREATE PROCEDURE dbo.SelectOrders
AS
SELECT * FROM dbo.Orders
Now it's time to create a stored procedure which takes time to execute. To write this stored procedure (MyStoredProcedure) I use new WAITFOR statement in SQL Server 2005 to make a five seconds delay in my stored procedure before executing SelectOrders procedure.
CREATE PROCEDURE dbo.MyStoredProcedure
AS
WAITFOR DELAY '00:00:05'
EXEC dbo.SelectOrders
Executing Command
There are three new methods for command objects in ADO.NET 2.0 which enable the ability to execute them asynchronously: BeginExecuteReader(), BeginExecuteNonQuery() and BeingExecuteXmlReader().
First one will be used for my sample. What I want is to get the list of orders via MyStoredProcedure and show them in a Windows Form GridView control.
I create a SqlConnection and SqlCommand to fetch my data and pass my connection string to SqlConnection object. Note that I add "asyn=true;" or "Asynchronous Processing=true;" to my connection string to enable asynchronous execution for my commands. If I don't add one of these strings, will get an error with following description when I want to call BeginExecuteReader() method:
This command requires an asynchronous connection. Set "Asynchronous Processing=true" in the connection string.
After opening a connection to database, I call BeginExecuteReader() by passing a delegate of my main logic method for asynchronous execution, my SqlCommand object and a CommandBehaviour enumeration to close the connection to database after finishing the execution automatically.
private void Execute(string connectionstring)
{
SqlConnection connection =
new SqlConnection(connectionstring + "async=true;");
SqlCommand command =
new SqlCommand("MyStoredProcedure", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
command.BeginExecuteReader(Asynchronous, command,
CommandBehavior.CloseConnection);
}
Now I want to work on main logic for my command execution. I use Asynchronous() method to do this. This method takes one argument of IAsyncResult type. I convert AsyncState property of this parameter to a SqlCommand then create a SqlDataReader and set it to appropriate value via the SqlCommand I created and finally load the data of this SqlDataReader to a DataTable and pass this DataTable through a delegate to another method.
private void Asynchronous(IAsyncResult asyncResult)
{
SqlCommand command =
asyncResult.AsyncState as SqlCommand;
SqlDataReader reader;
reader = command.EndExecuteReader(asyncResult);
DataTable table = new DataTable();
table.Load(reader);
reader.Close();
Invoke(new MyBindDelegate(MyBind), table);
}
Below is latest bits of my sample code to define a MyBindDelegate delegate and MyBind() method to bind my DataTable to GridView control.
private delegate void MyBindDelegate(DataTable table);
private void MyBind(DataTable table)
{
GridView.DataSource = table;
}
But why I used this delegate to bind my data to GridView control?
.NET uses threading to accomplish asynchronous execution of commands so I used a delegate to move my Asynchronous() method to a new thread and I must do same and go back to first thread to bind my data to GridView at the end because GridView is located on another thread and can't be accessed within my new asynchronous thread.
Link http://nayyeri.net/blog/Asynchronous-command-execution-in-NET-2-0/
Abajo recopilo
Asynchronous execution of commands was one of great new features in ADO.NET 2.0. The ability to execute commands that take long time to execute asynchronously is very helpful for developers.
The process of executing a command asynchronously is very simple and easy and I talk about it in this post.
Database
Before anything I create two stored procedures for SQL Server Northwind sample database to have a final stored procedure that takes time to execute.
First I create a stored procedure, SelectOrders, to select all orders from Orders table.
CREATE PROCEDURE dbo.SelectOrders
AS
SELECT * FROM dbo.Orders
Now it's time to create a stored procedure which takes time to execute. To write this stored procedure (MyStoredProcedure) I use new WAITFOR statement in SQL Server 2005 to make a five seconds delay in my stored procedure before executing SelectOrders procedure.
CREATE PROCEDURE dbo.MyStoredProcedure
AS
WAITFOR DELAY '00:00:05'
EXEC dbo.SelectOrders
Executing Command
There are three new methods for command objects in ADO.NET 2.0 which enable the ability to execute them asynchronously: BeginExecuteReader(), BeginExecuteNonQuery() and BeingExecuteXmlReader().
First one will be used for my sample. What I want is to get the list of orders via MyStoredProcedure and show them in a Windows Form GridView control.
I create a SqlConnection and SqlCommand to fetch my data and pass my connection string to SqlConnection object. Note that I add "asyn=true;" or "Asynchronous Processing=true;" to my connection string to enable asynchronous execution for my commands. If I don't add one of these strings, will get an error with following description when I want to call BeginExecuteReader() method:
This command requires an asynchronous connection. Set "Asynchronous Processing=true" in the connection string.
After opening a connection to database, I call BeginExecuteReader() by passing a delegate of my main logic method for asynchronous execution, my SqlCommand object and a CommandBehaviour enumeration to close the connection to database after finishing the execution automatically.
private void Execute(string connectionstring)
{
SqlConnection connection =
new SqlConnection(connectionstring + "async=true;");
SqlCommand command =
new SqlCommand("MyStoredProcedure", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
command.BeginExecuteReader(Asynchronous, command,
CommandBehavior.CloseConnection);
}
Now I want to work on main logic for my command execution. I use Asynchronous() method to do this. This method takes one argument of IAsyncResult type. I convert AsyncState property of this parameter to a SqlCommand then create a SqlDataReader and set it to appropriate value via the SqlCommand I created and finally load the data of this SqlDataReader to a DataTable and pass this DataTable through a delegate to another method.
private void Asynchronous(IAsyncResult asyncResult)
{
SqlCommand command =
asyncResult.AsyncState as SqlCommand;
SqlDataReader reader;
reader = command.EndExecuteReader(asyncResult);
DataTable table = new DataTable();
table.Load(reader);
reader.Close();
Invoke(new MyBindDelegate(MyBind), table);
}
Below is latest bits of my sample code to define a MyBindDelegate delegate and MyBind() method to bind my DataTable to GridView control.
private delegate void MyBindDelegate(DataTable table);
private void MyBind(DataTable table)
{
GridView.DataSource = table;
}
But why I used this delegate to bind my data to GridView control?
.NET uses threading to accomplish asynchronous execution of commands so I used a delegate to move my Asynchronous() method to a new thread and I must do same and go back to first thread to bind my data to GridView at the end because GridView is located on another thread and can't be accessed within my new asynchronous thread.
Comentarios
Publicar un comentario