Anatoly Lubarsky Logo
programming, design, integration, games, music

Data Access Helper Class Implementation

Below is Data Access helper class implementation for .NET. Unlike Data Access blocks and classes provided by Microsoft - this class does not have numerous sql types bugs and most important - it is a bit over-optimized for performance. Therefore it:


  • It works only with stored procedures.
  • SqlCommand parameter access is provided by index only.

However it is a very performant, scalable and well-written example that demonstrates how to work with stored procedures in ADO.NET, open/close connections to database correctly, work with SQLCommand, command input/output parameters, etc.


public class DataAccessHelper : IDisposable 
{
    // connection to data source
    private SqlConnection con;
    private string strConnection = 
       ConfigurationSettings.AppSettings["ConnectionString"];
      
    public int RunProcedure(string procedureName) 
    {
        SqlCommand cmd = CreateCommand(procedureName, null);
        cmd.ExecuteNonQuery();
        this.Close();
        return (int)cmd.Parameters["ReturnValue"].Value;
    }

    public int RunProcedure(string procedureName, 
        SqlParameter[] prams) 
    {
        SqlCommand cmd = CreateCommand(procedureName, prams);
        cmd.ExecuteNonQuery();
        this.Close();
        return (int)cmd.Parameters["ReturnValue"].Value;
    }

    public void RunProcedure(string procedureName, 
        out SqlDataReader dataReader) 
    {
        SqlCommand cmd = CreateCommand(procedureName, null);
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public void RunProcedure(string procedureName, 
        SqlParameter[] prams, 
        out SqlDataReader dataReader) 
    {
        SqlCommand cmd = CreateCommand(procedureName, prams);
        dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
      
    private SqlCommand CreateCommand(string procedureName,
        SqlParameter[] prams) 
    {
        // make sure connection is open
        this.Open();
        SqlCommand cmd = new SqlCommand(procedureName, con);
        cmd.CommandType = CommandType.StoredProcedure;
        
        // add proc parameters
        if (prams != null) 
        {
            foreach (SqlParameter parameter in prams)
            {
                cmd.Parameters.Add(parameter);
            }
        }
         
        // return param
        cmd.Parameters.Add(new SqlParameter("ReturnValue", 
            SqlDbType.Int,
            4,
            ParameterDirection.ReturnValue, 
            false,
            0,
            0,
            String.Empty,
            DataRowVersion.Default,
            null));

        return cmd;
    }
      
    public SqlParameter MakeInParameter(string parameterName,
        SqlDbType dbType, 
        int size, 
        object hisValue) 
    {
        return MakeParameter(parameterName, 
            dbType, 
            size, 
            ParameterDirection.Input, 
            hisValue);
    }      

    public SqlParameter MakeOutParameter(string parameterName,
        SqlDbType dbType, 
        int size) 
    {
        return MakeParameter(parameterName, 
            dbType, 
            size, 
            ParameterDirection.Output, 
            null);
    }      

    public SqlParameter MakeParameter(string parameterName,
        SqlDbType dbType, 
        int size, 
        ParameterDirection direction, 
        object hisValue) 
    {
        SqlParameter param = (size > 0) ? 
            new SqlParameter(parameterName, dbType, size) : 
            new SqlParameter(parameterName, dbType);

        param.Direction = direction;
        if (!((direction == ParameterDirection.Output) &&
            (hisValue == null)))
        {
            param.Value = hisValue;
        }

        return param;
    }

    private void Open() 
    {
        // open connection
        if (con == null) 
        {
            con = new SqlConnection(strConnection);
            con.Open();
        }            
    }

    public void Close() 
    {
        if (con != null)
        {
            con.Close();
        }
    }

    public void Dispose() 
    {
        // make sure connection is closed
        if (con != null) 
        {
            con.Dispose();
            con = null;
        }            
    }
}

Please, pay attention to how IDisposable interface is implemented in this class. All data access classes, that are supposed to work with database are supposed to call the functions of this class.


Related Posts:

Wednesday, August 04, 2004 6:03 AM

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs