A Generic ADO.NET Overlay to Simplify DB Calls to SQL Server and Oracle

• Download source - 74.4 KB

Introduction

This tip is all about making the life of an ADO.NET programmer easier. (Well… I like to call him like that if a programmer deals a lot of ADO.NET code while writing WCF code, or writing Entity framework against stored procedures, inline queries, etc.).

As an everyday programmer, I have gone through ADO.NET connections, connection strings, commands, readers and in-out parameters where lot of steps are repetitive and yet cannot be taken out of my responsibility because it comes as part of the package and cannot be moved into a base class or method call. It looks simple and like an ignorable problem but for me whatever is repetitive should be moved out of my way.

Let me give a simple example here:

This is the common way of calling a DB and updating values using ADO.NET:

OracleConnection con = new OracleConnection("connection string");
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SQL query";
con.Open();
cmd.ExecuteNonQuery();

If you look above, only two statements are going to change and all the rest of the 4 statements are repeated.

The above can be simplified like this?

DBA.Execute("connection string","SQL query");

Things get more complicated when calling procedures. Look into this sample:

OracleConnection con = new OracleConnection("connection string");
OracleCommand cmd = con.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "schema.PROC_FUNC_NAME";
OracleParameter p = new OracleParameter("Param", OracleDbType.Varchar2, 100);
p.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p);
con.Open();
cmd.ExecuteNonQuery();
object t = cmd.Parameters["Param"].Value;

And here let me say it could be as simple as;

DBA.Execute("connection string","schema.PROC_FUNC_NAME","Param1",myvalue);

These are just beginning and programmers who need to write code against database need to handle so many overheads which can be generalized like:

1. Transactions
2. Connection timeouts
3. Custom exception handling and logging
4. Much more .. who know what!

Well, that’s the story of this package where I started trying out different approaches where the whole ADO.NET library can be utilized in a much simpler way. 

Now let me talk about how you can take it out into your implementation.

Projects/Assemblies Required - ADO.NET for Oracle

1. System.Diagnostics.Logging.dll
2. DBBase.dll
3. Oracle.DataAccess.Client.OracleDBBase.dll

Projects/Assemblies Required - ADO.NET for SQL Server

1. System.Diagnostics.Logging.dll
2. DBBase.dll
3. System.Data.SqlClient.SQLDBBase.dll

Feature Overview

1. Handles 3 modes of database interaction
2. Can be used against a connection string or connection object
3. Optional built-in transactions
4. Exception handling with configurable response behaviors
5. Configurable timeout handler and retry options
6. Extendable for other providers

How to Use the Library

There are two classes in project ‘DBBaseTest’ which show the implementation details which can be used as a baseline for the starters. Class file OracleDBTest.cs contains implementation samples for Oracle and SQLServerDBTest.cs contains implementation samples for SQL Server.

1. ExecuteBasicQuery(..)/ExecuteBasicStoredProcedure(..) - Executes a query or procedure and returns number of rows affected.
   Examples:

   C#

   1)  //executes an update query and returns number of records affected.
   
   cnt = Sql.DB.ExecuteBasicQuery(con, "update Debug_test set FirstName = 'First3' _
   	where ID = 2; update Debug_test set FirstName = 'First3' where ID = 5");
   
   2)  // executes the procedure returns number of records affected.
   
   cnt = Sql.DB.ExecuteBasicStoredProcedure(con, "DEBUG_TEST_SPROC", _
   	(cmd) => Sql.DB.InsertInParams(cmd, "Age", 10));

2. ExecuteScalarQuery(..)/ExecuteScalarStoredProcedure(..) - Executes a query or procedure and returns a single output.
   C#

   1) //Gets a single value after executing query.
   
   decimal count = Ora.DB.ExecuteScalarQuery<decimal>(con, "Select count(*) as cnt from Debug_test", "cnt");
   
   2) //grabbing a string out value from procedure
   
   FirstName = Sql.DB.ExecuteScalarStoredProcedure<string>(con,"DEBUG_TEST_GET_SPROC", 
   100, SqlDbType.VarChar, "FirstName"
   ,
       (cmd) =>
       {
           Sql.DB.InsertInParams(cmd, "ID", 1);
       }
   ,doSimpleTransaction: true);

3. ExecuteComplexQuery (..)/ExecuteComplexStoredProcedure (..) - Executes a query or procedure and returns a single output.

   Examples:

   C#

   1)  //Executes a query and returns multiple values at once.
   
   bool status = Sql.DB.ExecuteComplexQuery<bool>
   (con, "select * from Debug_test where id = 1"
   ,
      (rdr) =>
      {
          while (rdr.Read())
          {
               FirstName = rdr["FirstName"].ToString();
               Lastname = rdr["LastName"].ToString();
               Age = int.Parse(rdr["Age"].ToString());
          }
           return true;
       }
   ,
       (cmd,rdr,ex)=>
       {
           return cmd.CommandText + rdr.GetValue(0).ToString() + ex.Message;
       }
   );
   
   2)    //Passes values to SPROC and returns values from sproc.
   Sql.DB.ExecuteComplexStoredProcedure<bool>(con, "DEBUG_TEST_GET_SPROC",
   (
       rdr, cmd) =>
       {
            FirstName = cmd.Parameters["FirstName"].Value.ToString();
            Lastname = cmd.Parameters["LastName"].Value.ToString();
            DOB = DateTime.Parse(cmd.Parameters["DOB"].Value.ToString());
            return true;
       }
   ,
       (cmd) =>
       {
            Sql.DB.InsertInParams(cmd, "ID", 1);
            Sql.DB.InsertOutParams(cmd, "FirstName", 
            System.Data.SqlDbType.NVarChar, 100, "LastName", SqlDb_
            Type.NVarChar, 100, "DOB", SqlDbType.DateTime, 0);
       }
   );

4. Optional Utilities

   C#

   1) //Autogenerate Command Parameters for a stored procedure.
   
   SQL.DB.AutoGenerateCommandParametersFromDB(cmd);
   
   2) //Helper method for Adding new IN parameters and then inserting values to a command object. 
      //This can be used in Complex stored procedure calls and any other places 
      //where command object requires parameters to be passed.
   
   Sql.DB.InsertInParams(...)
   
   3) //Helper method for Adding new OUT parameters and then returning values from a command object. 
      //This can be used in Complex stored procedure calls and any other places 
      //where command object requires parameters to be returned.
   
   Sql.DB.InsertOutParams(...)          
   
   4) //Helper method for creating a return parameter from a command object. 
      //This can be used while calling functions in DBs.
   
   Sql.DB.InsertReturnParameter<String>(...)
   
   5) //Helper method for Adding values to existing command object parameters. 
      //This can be used in Complex stored procedure calls and any other places 
      //where command object requires parameters to be passed in with values. 
   
   Sql.DB.InsertValues(...)

Summary

This library I developed for implementation in one of my projects and I used in it everywhere where a DB call was required. The database used there was SQL Server and when I came across issues during development, I fixed them. Later, I found this package could be useful for projects on Oracle and so created an additional module targeting it. This means this project is not extensively tested and there may be scenarios where it might not behave as expected with design limitations or bugs which need to be fixed against certain cases.

Do not hesitate to include comments in case you see any issues with the implementation. I will look into the issues and will try to make revisions with more options and bug fixes.