Functional ADO.NET (Channel 9)

Functional ADO.NET

Posted: Sep 02, 2007 at 9:41 AM

By: Andrew Davey

5,159 Views

2 Comments

Download

Right click “Save as…”

Mid Quality WMV (Lo-band, Mobile)

format
< > embed
+ queue

Sign In first to access your queue.
Close

Sorry, video was not added to the queue, an error occurred. Please Contact Us and let us know.
Close

Embed code for this video

Copy the code above to embed our video on your website/blog.

Video format

Auto
Use our suggested format for this video.
Smooth Streaming
Adaptive bitrate streaming - little buffering but lower video quality at times of low bandwidth.
Progressive
Traditional buffered video format.
HTML5
For browsers that support h264 MP4 or WebM video playback such as:
IE9+, Chrome 5+, or Safari 4+.

Note: These selections will fall back to the next best format depending upon browser capability.

Whilst LINQ is almost here, there is still a place in the world for plain old ADO.NET programming. However, procedural ADO.NET code can often be cumbersome to write. Executing some SQL for a data reader can involve three nested layers of C# using statements.
In this screencast, I present a functional approach to using ADO.NET that wraps most of the boiler-plate code into a Database class.
It makes use of anonymous methods to allow the developer to provide the required code at different points in the ADO.NET code.
The result is a syntactically lightweight way to interact with a database.

The code used in the project can be downloaded here:
C# 3.0 http://www.aboutcode.net/screencasts/functional-ado.3.zip
C# 2.0 http://www.aboutcode.net/screencasts/functional-ado.2.zip (same as 3.0 but with non-inferred anonymous methods).

Instead of:

          reader =>
          {
               while (reader.Read())
              {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  reader.GetInt32(0),
                  reader.GetString(1),
                  reader.GetString(2));
              }
          });

I sugest to abstract away the reader usage and put it inside the DataBase.ExecuteReader(...) method and replace the last parameter Action<IDataReader> action with Action<IDataRecord> and iterate inside the method, like so :
replace :
...
using (IDataReader reader = cmd.ExecuteReader())
{
       action(reader);
}
...

with :

using (IDataReader reader = cmd.ExecuteReader())
{
       while (reader.Read()) {
            action(reader);
      }
}

so:

          reader =>
          {
               while (reader.Read())
              {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  reader.GetInt32(0),
                  reader.GetString(1),
                  reader.GetString(2));
              }
          });

becomes:

          record =>
          {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  record .GetInt32(0),
                  record .GetString(1),
                  record .GetString(2));
          });

... and all loops are gone inside user code. (perfect candidate for thread level parallelism now Smiley ).

Other than that nice vid and good way of using the old ado.net.

Follow the Discussion

Subscribe to these comments

Pop Catalin Sever

Sep 03, 2007 at 1:51 AM quote reply

Instead of:

          reader =>
          {
               while (reader.Read())
              {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  reader.GetInt32(0),
                  reader.GetString(1),
                  reader.GetString(2));
              }
          });

I sugest to abstract away the reader usage and put it inside the DataBase.ExecuteReader(...) method and replace the last parameter Action<IDataReader> action with Action<IDataRecord> and iterate inside the method, like so :
replace :
...
using (IDataReader reader = cmd.ExecuteReader())
{
       action(reader);
}
...

with :

using (IDataReader reader = cmd.ExecuteReader())
{
       while (reader.Read()) {
            action(reader);
      }
}

so:

          reader =>
          {
               while (reader.Read())
              {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  reader.GetInt32(0),
                  reader.GetString(1),
                  reader.GetString(2));
              }
          });

becomes:

          record =>
          {
                  Console.WriteLine("{0}\t{1}\t{2}",
                  record .GetInt32(0),
                  record .GetString(1),
                  record .GetString(2));
          });

... and all loops are gone inside user code. (perfect candidate for thread level parallelism now ).

Other than that nice vid and good way of using the old ado.net.
Andrew Davey www.aboutcode.net

Sep 03, 2007 at 2:57 AM quote reply

It depends on if you need the reader or not. Sometimes I need to know that no records were returned.

A good approach would be to have an overloaded method. One that passes the reader, another that passes each data record.

I hope people find this pattern useful and adapt it to meet their needs as you have done
Also, does anyone know if this pattern has a name?

Remove this comment

Remove this thread

Comments Closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums,
or Contact Us and let us know.