Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.33/5 (4 votes)
12 Jan 2016CPOL3 min read 23.9K   340   16   4
An ADO.NET Layer for SQL Server and Oracle which makes it easier for interfacing with database

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:

C#
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:

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
United States United States
Started Programming career with VB 6 and VC++ and now Into .Net Development. Working with .Net since its first release (Version 1.0 Beta). Lucky enough to continue always with most updated versions of .Net and as of now; May 2007, fighting with .Net 3.0 (WPF,WCF...etc) and it's next most anticipated version LINQ.

Got familiarity up on the .Net Technologies and Guidelines like CAB, and Patterns and Practices, Enterprise Library and now with it's WPF version etc.

Specialized in Windows and Distributed and Service oriented applications.

Comments and Discussions

 
QuestionNothing new Pin
Member 436622013-Jan-16 1:21
Member 436622013-Jan-16 1:21 
GeneralRe: Nothing new Pin
shijo joseph13-Jan-16 8:02
shijo joseph13-Jan-16 8:02 
QuestionA Slightly More Generic Approach Pin
PeejayAdams11-Jan-16 3:38
PeejayAdams11-Jan-16 3:38 
AnswerRe: A Slightly More Generic Approach Pin
shijo joseph11-Jan-16 8:51
shijo joseph11-Jan-16 8:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.