Three tier Architecture

Try this also

using System;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

namespace Profile_Database

{

public class Database

{

///

/// Variable Declaration

///

#region Variable Declaration

//private static bool connect = Convert.ToBoolean(ConfigurationSettings.AppSettings["ConnectionString"].ToString());

private static String OconString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();

protected static SqlConnection Ocon;

protected static SqlCommand Ocmd;

protected static SqlTransaction transaction;

protected static String queryString;

protected static SqlDataReader dataReader;

protected static DataSet ods;

protected static SqlDataAdapter odr;

int i;

#endregion Variable Declaration

///

/// Sql Database Coonection Open

///

#region Connect

public void JSconnect()

{

Ocon = new SqlConnection();

if (Ocon.State == ConnectionState.Closed)

{

Ocon.ConnectionString = OconString.ToString();

Ocon.Open();

}

#endregion Connect

///

/// Sql Database Coonection Close

///

#region Close

public void JSclose()

{

if (Ocon.State == ConnectionState.Open)

{

Ocon.Close();

}

#endregion Close

///

/// JSExecution

///

#region Execution

public int JSExecution(string JSSPname, string[] JSParameters, object[] JSParametervalue)

{

int i;

Ocmd = new SqlCommand();

JSconnect();

Ocmd.CommandText = JSSPname;

Ocmd.Connection = Ocon;

Ocmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

for (int cnt = 0; cnt < JSParameters.Length; cnt++)

{

param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);

Ocmd.Parameters.Add(param);

}

try

{

i = Ocmd.ExecuteNonQuery();

return i;

}

catch (Exception ex)

{

throw ex;

}

finally

{

JSclose();

}

#endregion Execution

///

/// DataSet

///

#region JSDataset

public DataSet JSDataset(string JSSPname, string[] JSParameters, object[] JSParametervalue)

{

Ocmd = new SqlCommand();

JSconnect();

Ocmd.CommandText = JSSPname;

Ocmd.Connection = Ocon;

Ocmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

for (int cnt = 0; cnt < JSParameters.Length; cnt++)

{

param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);

Ocmd.Parameters.Add(param);

}

try

{

odr = new SqlDataAdapter(Ocmd);

ods = new DataSet();

odr.Fill(ods, "JSTable");

return ods;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//ods.Clear();

JSclose();

}

#endregion JSDataset

///

/// SqlDataReader

///

#region JSDatareader

public SqlDataReader JSDatareader(string JSSPname, string[] JSParameters, object[] JSParametervalue)

{

Ocmd = new SqlCommand();

JSconnect();

Ocmd.CommandText = JSSPname;

Ocmd.Connection = Ocon;

Ocmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

for (int cnt = 0; cnt < JSParameters.Length; cnt++)

{

param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);

Ocmd.Parameters.Add(param);

}

try

{

SqlDataReader odr = Ocmd.ExecuteReader();

return odr;

}

catch (Exception ex)

{

throw ex;

}

finally

{

//JSclose();

}

#endregion JSDatareader

}

Hi,

as your requirements I shared my project DAL(Data access layer ) class.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using INI;
namespace DAL
{
public class DMLSql
{
private static SqlDataAdapter myAdapter;
private static DMLSql Instance = null;
private DMLSql() { }
private static object lockThis = new object();
private static SqlConnection conn;
private static SqlConnection localConnection;
private static string DatabaseName = string.Empty;
private static string UserID = string.Empty;
private static string Password = string.Empty;
private static string ServerName = string.Empty;
//Local connection details
private static string localDatabaseName = string.Empty;
private static string localUserID = string.Empty;
private static string localPassword = string.Empty;
private static string localServerName = string.Empty;
// Initialise Connection
public static DMLSql MYInstance
{
get
{
try
{ if (Instance == null)
{
Instance = new DMLSql();
myAdapter = new SqlDataAdapter();
ReadINIFile objReadINIFile = new ReadINIFile(@"D:\a-master.ini");
//ReadINIFile objReadINIFile = new ReadINIFile(@"D:\NICSYNC.ini");
DatabaseName = objReadINIFile.GetSetting("DatabaseName", "DatabaseName");
UserID = objReadINIFile.GetSetting("UserName", "UserName");
Password = objReadINIFile.GetSetting("Password", "Password");
ServerName = objReadINIFile.GetSetting("ServerName", "ServerName");
localDatabaseName = objReadINIFile.GetSetting("localDatabaseName", "localDatabaseName");
localUserID = objReadINIFile.GetSetting("localUserName", "localUserName");
localPassword = objReadINIFile.GetSetting("localPassword", "localPassword");
localServerName = objReadINIFile.GetSetting("localServerName", "localServerName");
string LocalconncetionString = @"Data Source=" + localServerName + ";Database=" + localDatabaseName + ";User ID=" + localUserID + ";Password=" + localPassword + ";";
string TempConnectionstring = @"Data Source=" + ServerName + ";Database=" + DatabaseName + ";User ID=" + UserID + ";Password=" + Password + ";";
conn = new SqlConnection(TempConnectionstring);
localConnection = new SqlConnection(LocalconncetionString);
}
return Instance;
}
catch (Exception)
{
throw;
}
}
}
// Open Database Connection if Closed or Broken
private SqlConnection openConnection(bool isLocalConnection)
{
SqlConnection Connection = null;
if (isLocalConnection)
Connection = localConnection;
else
Connection = conn;
if (Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken)
Connection.Open();
return Connection;
}
// Close Connection....
private SqlConnection CloseConnection(bool isLocalConnection)
{
SqlConnection Connection = null;
if (isLocalConnection)
Connection = localConnection;
else
Connection = conn;
if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
{
Connection.Close();
}
return Connection;
}
///
/// Get A Single Records
///
///
///
///
public DataTable GetSingleRecord(SqlCommand Cmd, CommandType commandType, bool isLocal = false)
{
string Result = string.Empty;
DataTable dt = new DataTable();
try
{
Cmd.Connection = openConnection(isLocal);
Cmd.CommandType = commandType;
dt.Load(Cmd.ExecuteReader());
}
catch (SqlException)
{
CloseConnection(isLocal);
throw;
}
finally
{
CloseConnection(isLocal);
}
return dt;
}
// Insert data through Text/Procedure with sql parameters
public int ExecuteNonquery(string query, SqlParameter[] sqlParameter, CommandType commandType,bool isLocal=false)
{
int TempValue = 0;
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlCommand.Connection = openConnection(isLocal);
sqlCommand.CommandText = query;
sqlCommand.CommandType = commandType;
if (sqlParameter != null)
sqlCommand.Parameters.AddRange(sqlParameter);
TempValue = sqlCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return TempValue;
}
public int ExecuteNonquery(SqlCommand cmd, bool isLocal = false)
{
int TempValue = 0;
try
{
cmd.Connection = openConnection(isLocal);
TempValue = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return TempValue;
}
// To get Single Record with passing parameters
public string GetSingleRecord(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false)
{
string TempValue = string.Empty;
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlCommand.Connection = openConnection(isLocal);
sqlCommand.CommandText = query;
sqlCommand.CommandType = commandType;
if (sqlParameter != null)
sqlCommand.Parameters.AddRange(sqlParameter);
TempValue = Convert.ToString(sqlCommand.ExecuteScalar());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return TempValue;
}
// To get Single Record without passing parameters
public string GetSingleRecord(string query, CommandType commandType, bool isLocal = false)
{
string TempValue = string.Empty;
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlCommand.Connection = openConnection(isLocal);
sqlCommand.CommandText = query;
sqlCommand.CommandType = commandType;
TempValue = Convert.ToString(sqlCommand.ExecuteScalar());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return TempValue;
}
// To get Single Record without passing parameters
public string GetSingleRecord(SqlCommand cmd, bool isLocal = false)
{
string TempValue = string.Empty;
try
{
cmd.Connection = openConnection(isLocal);
TempValue = Convert.ToString(cmd.ExecuteScalar());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return TempValue;
}
// To get multiple records with passing parameters
public DataTable GetRecords(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false)
{
SqlCommand sqlCommand = new SqlCommand();
DataTable records = null;
try
{
records = new DataTable();
sqlCommand.Connection = openConnection(isLocal);
sqlCommand.CommandText = query;
sqlCommand.CommandType = commandType;
if (sqlParameter != null)
sqlCommand.Parameters.AddRange(sqlParameter);
records.Load(sqlCommand.ExecuteReader());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return records;
}
public DataTable GetRecords(SqlCommand cmd, bool isLocal = false)
{
DataTable records = null;
try
{
records = new DataTable();
cmd.Connection = openConnection(isLocal);
records.Load(cmd.ExecuteReader());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return records;
}
// To get multiple records without passing parameters
public DataTable GetRecords(string query, CommandType commandType, bool isLocal = false)
{
SqlCommand sqlCommand = new SqlCommand();
DataTable records = null;
try
{
records = new DataTable();
sqlCommand.Connection = openConnection(isLocal);
sqlCommand.CommandText = query;
sqlCommand.CommandType = commandType;
records.Load(sqlCommand.ExecuteReader());
}
catch (SqlException ex)
{
CloseConnection(isLocal);
throw ex;
}
catch (Exception ex)
{
CloseConnection(isLocal);
throw ex;
}
finally
{
CloseConnection(isLocal);
}
return records;
}
}
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gnanavel Sekar9y ago
Accepted answer
Try this also

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace Profile_Database
{
public class Database
{
///
/// Variable Declaration
///
///
#region Variable Declaration
//private static bool connect = Convert.ToBoolean(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
private static String OconString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
protected static SqlConnection Ocon;
protected static SqlCommand Ocmd;
protected static SqlTransaction transaction;
protected static String queryString;
protected static SqlDataReader dataReader;
protected static DataSet ods;
protected static SqlDataAdapter odr;
int i;
#endregion Variable Declaration
///
/// Sql Database Coonection Open
///
///
#region Connect
public void JSconnect()
{
Ocon = new SqlConnection();
if (Ocon.State == ConnectionState.Closed)
{
Ocon.ConnectionString = OconString.ToString();
Ocon.Open();
}
}
#endregion Connect
///
/// Sql Database Coonection Close
///
///
#region Close
public void JSclose()
{
if (Ocon.State == ConnectionState.Open)
{
Ocon.Close();
}
}
#endregion Close
///
/// JSExecution
///
///
#region Execution
public int JSExecution(string JSSPname, string[] JSParameters, object[] JSParametervalue)
{
int i;
Ocmd = new SqlCommand();
JSconnect();
Ocmd.CommandText = JSSPname;
Ocmd.Connection = Ocon;
Ocmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter();
for (int cnt = 0; cnt < JSParameters.Length; cnt++)
{
param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);
Ocmd.Parameters.Add(param);
}
try
{
i = Ocmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
JSclose();
}
}
#endregion Execution
///
/// DataSet
///
///
#region JSDataset
public DataSet JSDataset(string JSSPname, string[] JSParameters, object[] JSParametervalue)
{
Ocmd = new SqlCommand();
JSconnect();
Ocmd.CommandText = JSSPname;
Ocmd.Connection = Ocon;
Ocmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter();
for (int cnt = 0; cnt < JSParameters.Length; cnt++)
{
param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);
Ocmd.Parameters.Add(param);
}
try
{
odr = new SqlDataAdapter(Ocmd);
ods = new DataSet();
odr.Fill(ods, "JSTable");
return ods;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//ods.Clear();
JSclose();
}
}
#endregion JSDataset
///
/// SqlDataReader
///
///
#region JSDatareader
public SqlDataReader JSDatareader(string JSSPname, string[] JSParameters, object[] JSParametervalue)
{
Ocmd = new SqlCommand();
JSconnect();
Ocmd.CommandText = JSSPname;
Ocmd.Connection = Ocon;
Ocmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter();
for (int cnt = 0; cnt < JSParameters.Length; cnt++)
{
param = new SqlParameter(JSParameters[cnt], JSParametervalue[cnt]);
Ocmd.Parameters.Add(param);
}
try
{
SqlDataReader odr = Ocmd.ExecuteReader();
return odr;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//JSclose();
}
}
#endregion JSDatareader
}
}
+2
SARDAR MUKHTAR9y ago
//class Execution
string result = cs.ExecuteCmd_WithOutput(cs.ReturnCommand("PROCEDURENAME", "@parameter1^@parameter2^@parameter3", "Value1^Value2^Value3", '^'), "@outputParameter");
//class : Data Access Layer
--------------------------------
//Create Command Dynamically by using parameters
public static SqlCommand ReturnCommand(string SP_Name, string Parameter, string Fields, char separator)
{

SqlCommand cmd = null;
using (SqlConnection con = new SqlConnection(strConnectionString))
{
try
{
string[] Param = Parameter.Split(separator);
string[] Field = Fields.Split(separator);
cmd = new SqlCommand(SP_Name, con);
cmd.CommandType = CommandType.StoredProcedure;
if (Param.Length == Field.Length)
{
for (int x = 0; x < Param.Length; x++)
{
if (Field[x].ToUpper() == "NULL")
{
cmd.Parameters.AddWithValue(Param[x].ToString(), DBNull.Value);
}
else if (Field[x].ToUpper() == "")
{
cmd.Parameters.AddWithValue(Param[x].ToString(), DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue(Param[x].ToString(), Field[x].ToString());
}
}
}
return cmd;
}
catch (Exception ee)
{
return cmd;
}
}
}
//Execute the Command
-------------------
public string ExecuteCmd_WithOutput(SqlCommand cmd, string Out)
{
using (SqlConnection con = new SqlConnection(strConnectionString))
{
try
{
cmd.Connection = con;
SqlParameter par1 = new SqlParameter(Out, SqlDbType.VarChar, 200);
par1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(par1);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
string returnValue = Convert.ToString(cmd.Parameters[Out].Value);
if (con.State == ConnectionState.Open)
{
con.Close();
}
return returnValue;
}
catch (Exception ee)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
return "ERROR";
}
}
}
0
Joginder Banger9y ago
Hi,
as your requirements I shared my project DAL(Data access layer ) class.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using INI;
namespace DAL
{
    public class DMLSql
    {
        private static SqlDataAdapter myAdapter;
        private static DMLSql Instance = null;
        private DMLSql() { }
        private static object lockThis = new object();
        private static SqlConnection conn;
        private static SqlConnection localConnection;
        private static string DatabaseName = string.Empty;
        private static string UserID = string.Empty;
        private static string Password = string.Empty;
        private static string ServerName = string.Empty;
        //Local connection details
        private static string localDatabaseName = string.Empty;
        private static string localUserID = string.Empty;
        private static string localPassword = string.Empty;
        private static string localServerName = string.Empty;
        // Initialise Connection
        public static DMLSql MYInstance
        {
            get
            {
                try
                {       if (Instance == null)
                        {
                            Instance = new DMLSql();
                            myAdapter = new SqlDataAdapter();
                            ReadINIFile objReadINIFile = new ReadINIFile(@"D:\a-master.ini");
                            //ReadINIFile objReadINIFile = new ReadINIFile(@"D:\NICSYNC.ini");
                            DatabaseName = objReadINIFile.GetSetting("DatabaseName", "DatabaseName");
                            UserID = objReadINIFile.GetSetting("UserName", "UserName");
                            Password = objReadINIFile.GetSetting("Password", "Password");
                            ServerName = objReadINIFile.GetSetting("ServerName", "ServerName");
                            localDatabaseName = objReadINIFile.GetSetting("localDatabaseName", "localDatabaseName");
                            localUserID = objReadINIFile.GetSetting("localUserName", "localUserName");
                            localPassword = objReadINIFile.GetSetting("localPassword", "localPassword");
                            localServerName = objReadINIFile.GetSetting("localServerName", "localServerName");
                            string LocalconncetionString = @"Data Source=" + localServerName + ";Database=" + localDatabaseName + ";User ID=" + localUserID + ";Password=" + localPassword + ";";
                            string TempConnectionstring = @"Data Source=" + ServerName + ";Database=" + DatabaseName + ";User ID=" + UserID + ";Password=" + Password + ";";
                            conn = new SqlConnection(TempConnectionstring);
                            localConnection = new SqlConnection(LocalconncetionString);
                        }
                        return Instance;
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
        // Open Database Connection if Closed or Broken
        private SqlConnection openConnection(bool isLocalConnection)
        {
            SqlConnection Connection = null;
            if (isLocalConnection)
                Connection = localConnection;
            else
                Connection = conn;
            if (Connection.State == ConnectionState.Closed || Connection.State == ConnectionState.Broken)
                Connection.Open();
            return Connection;
        }
        // Close Connection....
        private SqlConnection CloseConnection(bool isLocalConnection)
        {
            SqlConnection Connection = null;
            if (isLocalConnection)
                Connection = localConnection;
            else
                Connection = conn;

            if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
            }
            return Connection;
        }
        ///

        /// Get A Single Records
        ///
        ///
        ///
        ///
        public DataTable GetSingleRecord(SqlCommand Cmd, CommandType commandType, bool isLocal = false)
        {
            string Result = string.Empty;
            DataTable dt = new DataTable();
            try
            {
                Cmd.Connection = openConnection(isLocal);
                Cmd.CommandType = commandType;
                dt.Load(Cmd.ExecuteReader());
            }
            catch (SqlException)
            {
                CloseConnection(isLocal);
                throw;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return dt;
        }
        // Insert data through Text/Procedure with sql parameters
        public int ExecuteNonquery(string query, SqlParameter[] sqlParameter, CommandType commandType,bool isLocal=false)
        {
            int TempValue = 0;
            SqlCommand sqlCommand = new SqlCommand();
            try
            {
                sqlCommand.Connection = openConnection(isLocal);
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = commandType;
                if (sqlParameter != null)
                    sqlCommand.Parameters.AddRange(sqlParameter);
                TempValue = sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return TempValue;
        }
        public int ExecuteNonquery(SqlCommand cmd, bool isLocal = false)
        {
            int TempValue = 0;
            try
            {
                cmd.Connection = openConnection(isLocal);
                TempValue = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return TempValue;
        }
        // To get Single Record with passing parameters
        public string GetSingleRecord(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false)
        {
            string TempValue = string.Empty;
            SqlCommand sqlCommand = new SqlCommand();
            try
            {
                sqlCommand.Connection = openConnection(isLocal);
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = commandType;
                if (sqlParameter != null)
                    sqlCommand.Parameters.AddRange(sqlParameter);
                TempValue = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return TempValue;
        }
        // To get Single Record without passing parameters
        public string GetSingleRecord(string query, CommandType commandType, bool isLocal = false)
        {
            string TempValue = string.Empty;
            SqlCommand sqlCommand = new SqlCommand();
            try
            {
                sqlCommand.Connection = openConnection(isLocal);
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = commandType;
                TempValue = Convert.ToString(sqlCommand.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return TempValue;
        }
        // To get Single Record without passing parameters
        public string GetSingleRecord(SqlCommand cmd, bool isLocal = false)
        {
            string TempValue = string.Empty;
            try
            {
                cmd.Connection = openConnection(isLocal);
                TempValue = Convert.ToString(cmd.ExecuteScalar());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return TempValue;
        }
        // To get multiple records with passing parameters
        public DataTable GetRecords(string query, SqlParameter[] sqlParameter, CommandType commandType, bool isLocal = false)
        {
            SqlCommand sqlCommand = new SqlCommand();
            DataTable records = null;
            try
            {
                records = new DataTable();
                sqlCommand.Connection = openConnection(isLocal);
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = commandType;
                if (sqlParameter != null)
                    sqlCommand.Parameters.AddRange(sqlParameter);
                records.Load(sqlCommand.ExecuteReader());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return records;
        }
        public DataTable GetRecords(SqlCommand cmd, bool isLocal = false)
        {
            DataTable records = null;
            try
            {
                records = new DataTable();
                cmd.Connection = openConnection(isLocal);
                records.Load(cmd.ExecuteReader());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return records;
        }
        // To get multiple records without passing parameters
        public DataTable GetRecords(string query, CommandType commandType, bool isLocal = false)
        {
            SqlCommand sqlCommand = new SqlCommand();
            DataTable records = null;
            try
            {
                records = new DataTable();
                sqlCommand.Connection = openConnection(isLocal);
                sqlCommand.CommandText = query;
                sqlCommand.CommandType = commandType;
                records.Load(sqlCommand.ExecuteReader());
            }
            catch (SqlException ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            catch (Exception ex)
            {
                CloseConnection(isLocal);
                throw ex;
            }
            finally
            {
                CloseConnection(isLocal);
            }
            return records;
        }
    }
}
+2