Functional ADO.NET

Sign in to queue

Description

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

Tag:

ADO.NET

Embed

Download

Download this episode

The Discussion

  • User profile image
    CatalinPop

    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.

  • User profile image
    Andrew Davey
    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 Smiley
    Also, does anyone know if this pattern has a name?

Add Your 2 Cents