Sql Database Engine

• Download SqlDataEngine - 1.6 KB

Introduction

This time I am going to present article over SqlHelper, named Sql Database Engine. This provides methods for various data access operations over Sql Server. 

Using the code

Using code is quite easy. Download the dll attached with this article. Reference it in your project and start using it. This articles intended audience are beginners / intermediate level developers.   

In general what we do for accessing database result to our page is that we use to write lots data access code every time on same page, whenever required.Making your code messy and heavy for debugging or further modifications in future. This is what I did too in my early days. 

This Class helps you in eliminating such messy codes and making you code lightweight and easy to understand.  

Lets move with code given below:

NameSpace : I think every .Net Developer must be aware of this Name. These are as same as header files of C/C++ having definitions and declaration classes , methods , properties used here. One can create his own namespace.  

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data; 

Here AmitRanjan.DataAccessBlock.DBEngine is my namespace by which I can call my class and their methods in any application. I have my class SqlDataEngine as it contains only Sql Server specific data access methods.  This is a sealed class, as I dont want it to get inherited. Also made its constructor private so as to prevent creating its object using new keyword. 

namespace AmitRanjan.DataAccessBlock.DBEngine
{
    public sealed class SqlDataEngine
    {
        private SqlDataEngine() { }

Global variable for holding Connectionstring from Configuration file and instance of SqlConnection class. Made private so as to prevent its access outside the SqlDataEngine Class.

private static string connectionString = Common.ConnectionString;
private static SqlConnection connection;

EstablishConnection() : This methods checks for the current state of the SqlConnection instance, if its found open, it closes existing connection and reopens a new connection to interact with database. You have to call this method at the beginning of the Data Access Block. Something like this. Say I am trying to get Info about students and my method name is GetStudentDetails. So my method will be :

private void GetStudentDetails(int StudentID)
{
    try
    {
        EstablishConnection();
        //Your Statements for Data Access
    }
    catch(Exception ex){// catch block statements}

}

/// <summary>
/// Establishes Connection with SqlServer Instance
/// </summary>
public static void EstablishConnection()
{
    try
    {
        connection = new SqlConnection(connectionString);
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
        }
        connection.Open();
    }
    catch
    {

        throw new DatabaseServerNotFoundException("Unable to connect with database server. Please contact your network administrator.");
    }

}

TerminateConnection : This is opposite of the EstablishConnection method. You can use it as cleanup code in finally block of your application. So that connection will get closed every time even if there is any exception.

/// <summary>
/// Teminates existing connection with SqlServer Instance
/// </summary>
public static void TerminateConnection()
{
    if (connection != null)
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
            connection.Dispose();
        }
    }
}

BeginTransaction: This associates SqlTransaction with current SqlConnection,if a user wants transaction while operating on multiple tables.
Usage : Declare a instance of SqlTransaction in the Code Block.
C# : SqlTransaction transaction;
VB : Dim transaction as new SqlTransaction
In your methods with data access layer methods mentioned below like, use something like this.ExecuteNonQuery(yourSqlStatement, transaction)

private void TransactionTest( string cmdText)
{
    SqlTransaction transaction = new SqlTransaction();
    try
    {
        SqlDataEngine.EstablishConnection();
        SqlDataEngine.BeginTransaction(transaction); //Start a new transaction taking condition your query is inserting/updating / deleting on multiple tables.
        SqlDataEngine.ExecuteNonQuery(cmdText,transaction); // Execute your query
        SqlDataEngine.CommitTransaction(); // Commit on successful completion
    }
    catch
    {
        SqlDataEngine.RollbackTransaction();
    }
    finally
    {
        SqlDataEngine.TerminateConnection();
    }
}

        /// <summary>
        /// Starts a transaction with existing connection
        /// </summary>
        /// <param name="transaction"></param>
        public static void BeginTransaction(SqlTransaction transaction)
        {
            if (transaction != null)
            {
                transaction = connection.BeginTransaction();
            }
            else
            {
                throw new NullReferenceException("Transaction object is null.");
            }
        }

        /// <summary>
        /// Commits a successfully executed statement
        /// </summary>
        /// <param name="transaction">sql transaction</param>
        public static void CommitTransaction(SqlTransaction transaction)
        {
            if (transaction != null)
            {
                transaction.Commit();
            }
            else
            {
                throw new NullReferenceException("Transaction object is null.");
            }
        }

        /// <summary>
        /// Rollbacks a transaction if error occurred while processing.
        /// </summary>
        /// <param name="transaction">sql transaction</param>
        
        public static void RollbackTransaction(SqlTransaction transaction)
        {
            if (transaction != null)
            {
                transaction.Rollback();

            }
            else
            {
                throw new NullReferenceException("Transaction object is null.");
            }
        }

        /// <summary>
        /// Executes Non queries like Insert,Update,Delete 
        /// </summary>
        /// <param name="commandText">Sql Statement as string</param>
        /// <returns>no. of rows affected by the query as integer</returns>
        public static int ExecuteNonQuery(string commandText)
        {
            int returnValue = 0;

            using (SqlCommand command = new SqlCommand(commandText, connection))
            {

               returnValue = command.ExecuteNonQuery();
            }
            return returnValue;
        }

        /// <summary>
        ///  Executes Non queries like Insert,Update,Delete, in case Sql parameters are required
        /// </summary>
        /// <param name="commandText">Sql Statement as String</param>
        /// <param name="parameters">Array of SqlParameters</param>
        /// <returns>no. of rows affected as integer</returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
        {
            int returnValue = 0;

            using (SqlCommand command = new SqlCommand(commandText, connection))
            {

                if (parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                    returnValue = command.ExecuteNonQuery();
                }
                else
                {
                    throw new SqlParametersException("If using Sql Parameters, count could not be Zero. ");
                }
               
            }
            return returnValue;
        }

       /// <summary>
        ///  Executes Non queries like Insert,Update,Delete, in case Sql parameters are required
        /// </summary>
        /// <param name="commandText">Sql Statement as String</param>
        /// <param name="parameters">Array of SqlParameters</param>
        /// <param name="transaction">Sql Transaction</param>
        /// <returns>no. of rows affected as integer</returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
        {
            int returnValue = 0;
            if (transaction != null)
            {

                using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
                {
                   
                    if (parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                        returnValue = command.ExecuteNonQuery();
                    }
                    else
                    {
                        throw new SqlParametersException("If using Sql Parameters, count could not be Zero. ");
                    }

                }
            }
            else
            {
                throw new NullReferenceException( "Transaction object is null.");
            }
            return returnValue;

        }
        /// <summary>
        /// Executes select statement and loads result to datatable
        /// </summary>
        /// <param name="commandText">Sql Statement as String</param>
        /// <returns>Result as Datatable</returns>
        public static DataTable ExecuteReader(string commandText)
        {
            DataTable dtResult = new DataTable();
            using (SqlCommand command = new SqlCommand(commandText,connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    dtResult.Load(reader);
                }
            }
            return dtResult;
        }

        /// <summary>
        /// Executes select statement and loads result to datatable with parameters supplied
        /// </summary>
        /// <param name="commandText">Sql Statement as String</param>
        /// <param name="parameters">Array of sql parameters</param>
        /// <returns>Result as Datatable</returns>
        public static DataTable ExecuteReader(string commandText, SqlParameter[] parameters)
        {
            DataTable dtResult = new DataTable();
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                if (parameters.Length > 0)
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        dtResult.Load(reader);
                    }
                }
                else
                {
                    throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
                }
            }
            return dtResult;
        }

        /// <summary>
        /// Executes select statement and loads result to datatable with parameters supplied
        /// </summary>
        /// <param name="commandText">Sql Statement as String</param>
        /// <param name="parameters">Array of sql parameters</param>
        /// <param name="transaction">Sql Transaction</param>
        /// <returns>Result as Datatable</returns>
        public static DataTable ExecuteReader(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
        {
            DataTable dtResult = new DataTable();
            if (transaction != null)
            {
                using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
                {
                    if (parameters.Length > 0)
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            dtResult.Load(reader);
                        }
                    }
                    else
                    {
                        throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
                    }
                }
            }
            else
            {
                throw new NullReferenceException("Transaction object is null.");
            }
            return dtResult;
        }

        /// <summary>
        /// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
        /// </summary>
        /// <param name="commandText">Sql statement as string</param>
        /// <returns>value of first row and first column as object</returns>
        public static object ExecuteScalar(string commandText)
        {
            object oResult = null;
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                oResult = command.ExecuteScalar();
            }
            return oResult;
        }

        /// <summary>
        /// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
        /// </summary>
        /// <param name="commandText">Sql statement as string</param>
        /// /// <param name="parameters">Array of SqlParameters</param>
        /// <returns>value of first row and first column as object</returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] parameters)
        {
            object oResult = null;
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                if (parameters.Length > 0)
                {
                    oResult = command.ExecuteScalar();
                }
                else
                {
                    throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
                }
            }
            return oResult;
        }

        /// <summary>
        /// Executes the query and returns the value of first row and first column from result set. Ignores the rest column
        /// </summary>
        /// <param name="commandText">Sql statement as string</param>
        /// <param name="parameters">Array of SqlParameters</param>
        /// <param name="transaction">Sql transaction </param>
        /// <returns>value of first row and first column as object</returns>
        public static object ExecuteScalar(string commandText, SqlParameter[] parameters, SqlTransaction transaction)
        {
            object oResult = null;
            if (transaction != null)
            {
                if (parameters.Length > 0)
                {
                    using (SqlCommand command = new SqlCommand(commandText, connection, transaction))
                    {
                        oResult = command.ExecuteScalar();
                    }
                }
                else
                {
                    throw new SqlParametersException("If using parameters, atleast one parameter should be provided.");
                }
            }
            else
            {
                throw new NullReferenceException("Transaction object is null.");
            }
            return oResult;
        }

    }
}