HOW TO CREATE COMMON DATABASE CONNECTION LOGIC IN DOT NET PROJECT

in programming •  last month 

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 :[email protected]


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;[email protected]";

        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;[email protected]";


        }


        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

            {


            }

        }

    }

}



Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!