HOW TO CREATE COMMON DATABASE CONNECTION LOGIC IN DOT NET PROJECT

2년 전

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

            {


            }

        }

    }

}



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!
STEEMKR.COM IS SPONSORED BY
ADVERTISEMENT