Community post
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
{
}
}
}
}
Replies (0)
Conversation
No replies yet
Replies will appear here as people join the conversation.