Functional ADO.NET (Channel 9)

Functional ADO.NET

Posted: Sep 02, 2007 at 9:41 AM

By: Andrew Davey

5,264 Views

2 Comments

Video format

Option selected may change based on video formats available and browser capability.

Auto

Use our suggested format for this video.

Smooth Streaming

Adaptive bitrate streaming - little buffering but lower video quality at times of low bandwidth. Silverlight 5 required

Progressive

Traditional buffered video format. Silverlight 5 required

HTML5

For browsers that support H.264 MP4 or WebM video playback such as:
IE9+, Firefox 4+, Chrome 5+, or Safari 4+.

Flash

For browsers that have the Adobe Flash Player plugin installed.

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).

Follow the Discussion

Oops, something didn't work.

Try again?

Getting "follow" information

Stop following these comments

Start following these comments

Removing your "follow"

Setting up your "follow"

Subscribe to this comments

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
Oops, something didn't work.

Try again?

Sign In to be begin to follow these comments

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in. You need to be signed in to Channel 9 to use this feature.

Getting "follow" information

Stop following these comments

Start following these comments

What does this mean?
Following an item on Channel 9 allows you to watch for new content and comments that you are interested in and view them all on your notifications page.

Removing your "follow"

Setting up your "follow"

Did you know you can
sign up for email notifications?
- Subscribe to this comments

Pop Catalin Sever

Sep 03, 2007 at 1:51 AM

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

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.