Asynchronous SQL Server Client

Yesterday reading about the SQL Client for Denali CTP3 at MSDN I realize out that exists an asynchronous possibilty of reading and executing queries. That is absolutely great for MVVM applications and WCF services after experimenting a bit I create a small example that shows the functionality:

public static class Create
{
    private static string Async = "Asynchronous Processing=true";
    private static string Security = "Integrated Security=False;User ID=username;Password=password";
    private static string Database = "database=Tasks";
    private static string Server = "Data Source=SERVER\\SQLExpress";
    public static SqlConnection AsyncConnection() { return new SqlConnection(String.Join(";", Server, Security, Database, Async)); }
    public static SqlConnection SyncConnection() { return new SqlConnection(String.Join(";", Server, Security, Database)); }
    public static string SelectUsers = "SELECT [ID],[Name],[Role] FROM [Users]";
    public static SqlCommand Command(string select, SqlConnection conn) { return new SqlCommand(select, conn); }
    
    
    public static SqlCommand AsyncCommand(IAsyncResult obj) { return obj.AsyncState as SqlCommand; }

    public static void CleanUp(SqlCommand command) { command.Connection.Close(); command.Connection.Dispose(); command.Dispose(); }

}

public static class SQLClient
{
    public static void Select(Action<Dictionary<string,object>> Process,Action End)
    {
        SqlConnection Connection = Create.AsyncConnection();
        SqlCommand Command = Create.Command(Create.SelectUsers, Connection);
        Connection.Open();
        Command.BeginExecuteReader((callback) =>
            {
                Int32 columns, i; 
                Dictionary<string, object> row;
                SqlCommand AsynCommand = Create.AsyncCommand(callback);
                using (SqlDataReader reader = 
                          AsynCommand.EndExecuteReader(callback))
                {
                    columns = reader.FieldCount;
                    while (reader.Read())
                    {
                        i=-1; row = new Dictionary<string, object>();
                        while(++i<columns)
                        {
                            row.Add(reader.GetName(i), reader[i]);
                        }
                        Process(row);
                    }
                    reader.Close();
                }
                Create.CleanUp(AsynCommand);
                End();

            }, Command);
    }
}

Of course there is not any exception, has rows and else checkins but the idea is right, in our viewmodel we have:

SQLClient.Select((result) =>
                {
                    //Add data to List
                    
                },
                () =>
                {
                    //Refresh ICollectionView
                });

I learned something very interesting today, I hope it helps you too. Comments are welcome.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s