Fluid interface for data access with SQL Server

While working on my current project I came up with this simple fluid interface used for data access with SQL Server. My current project doesnt use any ORMs for reasons that would wage war, so I won’t go into those details. I came up this with fluid interface to reduce the mundane ADO.NET code and to reduce repetition.

As with an ADO.NET Command object, this code executes non query or return a scalar or a  data reader.  My goal was to try and reduce as much code as possible especially with the data reader. I wanted to not use strings and ordinals and keep the code simple, so with a bit of experimenting I managed it with a class called “DynamicDbDataReader” which is a DynamicObject.

As a generic library, I had to allow for connections to be supplied / configured by allowing a connection instance to be supplied, a connection string or my preferred method, the name of the connection string that existing in the config file which I would keep as a string constant and pass it into the method.

The parameters that are supplied are done using a string for the parameter name and the object containing the parameter value. Method chaining is used to add more that one parameter and the data type is inferred automatically. All parameters are input only.

Example: Running a stored procedure with many parameters that does not return a result set (ExecuteNonQuery)

With.StoredProcedure("SomethingTo_Insert")
   .UsingConfiguredConnection("SomeConnectionStringName")
   .WithParameter("id", something.Id)
   .And.WithParameter("name", something.Name)
   .And.WithParameter("desc", something.Description)
   .And.WithParameter("severity", something.Severity)
   .And.WithParameter("systemKey", something.SystemKey)
   .ExecuteNonQuery();

Example: Running a stored procedure that returns a scalar which in this case is a boolean (ExecuteScalar)

 
bool result = With.StoredProcedure("Somthing_Exists")
                .UsingConfiguredConnection(DatabaseConnections.SomeDatabase)
                .WithParameter("name", name)
                .ExecuteScalar<bool>();

Example: Running a stored procedure with no parameters that does not return a result set using (ExecuteNonQuery)

With.StoredProcedure("SomethingTodo")
   .UsingConnection(sqlConnection)
   .WithNoParameters()
   .ExecuteNonQuery();

Example: Running a stored procedure that returns a data reader with parameters

dynamic reader = With.StoredProcedure("Somthing_SelectAllWithOtherStuff")
                    .UsingConfiguredConnection(DatabaseConnections.QueryStore)
                    .WithParameter("searchId", organsiationId)
                    .ExecuteReader(CommandBehavior.CloseConnection);

while (reader.Read())
{
    response.Something.Add(new SomeSummaryInfo
    {
        Id = reader.Id,
        Forename = reader.FirstName,
        Surname = reader.LastName
    });
}

reader.NextResult();

while (reader.Read())
{
    response.Tasks.Add(new OtherSummaryInfo
    {
        Id = reader.Id,
        Name = reader.Name,
        SomeId = reader.SomeId,
        SomeOtherName = reader.OtherName,
        DisplayOrder = reader.DisplayOrder,
    });
}

Example: Running a sql statement that has no parameters

With.SqlStatement("DELETE FROM SomeTable")
    .UsingConfiguredConnection("DatabaseName")
    .WithNoParameters()
    .ExecuteNonQuery();

In the event that a field name is spelt wrong when working with the dynamic reader, a ColumnNotFoundException will be thrown. The dynamic reader has methods on it for “Read()” and “NextResult()”.

That’s it in a nutshell.

You can download the code. its got a .doc extension but its really zip file. So download it and change the extension to get the code.
dataAccessBuilder