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
{
}
}
}
}