HOW TO CREATE COMMON DATABASE CONNECTION LOGIC IN DOT NET PROJECT

Here In This Blog We Will Create Common Database Class Called SqlDB.cs to create connection between database.

While Create A Project We Should Have Common Database Class So That It Help To Change Database Connection As Per Requirement.

Data Source =XYZ ----(Database Server Name)

eg :-DESKTOP-7O6OB9U\\SURYAKANT

Initial Catalog=XYZ----(Database Name)

eg :-gym

User ID=XYZ ----(Database Server User id)

eg :-sa

Password =XYZ----(Database Server Password)

eg :-user@123

public void BeginTransaction() -- To Start With Data Transaction

public void CommitTransaction() -- To Commit On Transaction

public void RollbackTransaction()--To Rollback On Transaction

public void CloseConnection()--To Close The Connection

private SqlConnection dbConnection -- Related To Connection Opening And Closing

public string runExecuteQuery(string strQuery, ArrayList alParams) -- To Run and Execute Query

public SqlDataReader getSqlDataReader(string strQuery, ArrayList alParams) -- To Read The Data 

public DataTable getDataTable(string strQuery, ArrayList alParams) -- To Get List Of Data From Table

public DataTable getDataTableQuery(string strQuery, ArrayList alParams) -- To Get List Of Data From Table

public string ExecuteStoreProcedure(string strQuery, ArrayList alParams) -- To Execute Store Procedure

Example:-

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Data.SqlTypes;

namespace SmartAdminMvc.Models

{

    public class SqlDB

    {

        public const string CONST_USER_ID = "";

        public string connStr = "Data Source=DESKTOP-7O6OB9U\\SURYAKANT;Initial Catalog=gym;Persist Security Info=True;User ID=sa;Password=user@123";

        private SqlTransaction sqlTrans;

        private SqlConnection sqlConn = null;

        public SqlDB()

        {

            this.connStr = "Data Source=DESKTOP-7O6OB9U\\SURYAKANT;Initial Catalog=gym;Persist Security Info=True;User ID=sa;Password=user@123";

        }

        public void BeginTransaction()

        {

            sqlTrans = dbConnection.BeginTransaction();

        }

        public void CommitTransaction()

        {

            sqlTrans.Commit();

        }

        public void RollbackTransaction()

        {

            sqlTrans.Rollback();

        }

        public void CloseConnection()

        {

            dbConnection.Close();

        }

        private SqlConnection dbConnection

        {

            get

            {

                if (sqlConn == null)

                {

                    sqlConn = new SqlConnection();

                    try

                    {

                        if (sqlConn.State == ConnectionState.Open)

                            sqlConn.Close();

                        sqlConn.ConnectionString = connStr;

                        if (sqlConn.State == ConnectionState.Closed)

                        {

                            sqlConn.Open();

                        }

                    }

                    catch (Exception)

                    {

                    }

                }

                return sqlConn;

            }

        }

        public SqlDataReader getSqlDataReader(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand();

                    objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandTimeout = 0;

                    objCommand.CommandText = strQuery;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    SqlDataReader sqlDR = objCommand.ExecuteReader();

                    objCommand.Parameters.Clear();

                    return sqlDR;

                }

            }

            catch

            {

                return null;

            }

        }

        public DataTable getDataTable(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand();

                    objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandTimeout = 0;

                    objCommand.CommandText = strQuery;

                    objCommand.Parameters.Clear();

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    SqlDataAdapter daSQL = new SqlDataAdapter(objCommand);

                    DataTable dt = new DataTable();

                    daSQL.Fill(dt);

                    objCommand.Parameters.Clear();

                    return dt;

                }

            }

            catch (Exception)

            {

                return null;

            }

        }

        public string runExecuteQuery(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandText = strQuery;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    objCommand.ExecuteNonQuery();

                    objCommand.Parameters.Clear();

                    return "done";

                }

            }

            catch (Exception ex)

            {

                return ex.Message;

            }

        }

public DataTable getDataTableQuery(string strQuery, ArrayList alParams)

{

try

{

using (var con = new SqlConnection(connStr))

{

if (con.State == ConnectionState.Closed)

{

con.Open();

}

SqlCommand objCommand = new SqlCommand();

objCommand = new SqlCommand(strQuery, con, sqlTrans);

objCommand.CommandText = strQuery;

objCommand.CommandType = CommandType.StoredProcedure;

objCommand.CommandTimeout = 0;

objCommand.Parameters.Clear();

foreach (SqlParameter param in alParams)

{

objCommand.Parameters.Add(param);

}

SqlDataAdapter daSQL = new SqlDataAdapter(objCommand);

DataTable dt = new DataTable();

daSQL.Fill(dt);

objCommand.Parameters.Clear();

return dt;

}

}

catch (Exception ex)

{

string meassge = ex.ToString();

return null;

}

}

public string ExecuteStoreProcedure(string strQuery, ArrayList alParams)

        {

            try

            {

                using (var con = new SqlConnection(connStr))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand objCommand = new SqlCommand(strQuery, con, sqlTrans);

                    objCommand.CommandText = strQuery;

                    objCommand.CommandType = CommandType.StoredProcedure;

                    objCommand.CommandTimeout = 0;

                    foreach (SqlParameter param in alParams)

                    {

                        objCommand.Parameters.Add(param);

                    }

                    objCommand.ExecuteNonQuery();

                    objCommand.Parameters.Clear();

                    return "done";

                }

            }

            catch (Exception ex)

            {

                return ex.Message;

            }

            finally

            {

            }

        }

    }

}