AndrewDavey

Functional ADO.NET

Download this episode

Download Video

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

Format

Available formats for this video:

Actual format may change based on video formats available and browser capability.

    The Discussion

    • Pop Catalin Sever

      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.

    • 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?

    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.