different databases if we add all different types of System.Data like System.Data.SqlClient, System.Data.Odbc, System.Data.OleDb but for simplicity I consider only Microsoft SQL Server. You do not need to change the connection configuration line as you can keep the SQLDBConnection as is and only change it in your Web.Config file
private string
strConnection = ConfigurationManager.ConnectionStrings["SQLDBConnection"].ConnectionString;
So here is the entire code below
using
System;
System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessLayer
{
public sealed class DBManagerFactory
{
private DBManagerFactory(){}
public static SqlConnection GetConnection()
{
SqlConnection mySQLDbConnection = null;
mySQLDbConnection = new SqlConnection();
return mySQLDbConnection;
}
public static SqlCommand GetCommand()
{
return new SqlCommand();
}
public static SqlDataAdapter GetDataAdapter()
{
return new SqlDataAdapter();
}
public static SqlTransaction GetTransaction()
{
SqlConnection mySQLDbConnection = GetConnection();
SqlTransaction mySQLTransaction = mySQLDbConnection.BeginTransaction();
return mySQLTransaction;
}
public static SqlParameter GetParameter()
{
SqlParameter myStoreProcParameter = null;
myStoreProcParameter = new SqlParameter();
return myStoreProcParameter;
}
public static SqlParameter[] GetParameters(int paramsCount)
{
SqlParameter[] idbParams = new SqlParameter[paramsCount];
for (int i = 0; i < paramsCount; ++i)
{
idbParams[i] = new SqlParameter();
}
return idbParams;
}
}
public sealed class SQLDBManager : IDisposable
{
private SqlConnection mySQLDbConnection;
private SqlDataReader mySQLdataReader;
private SqlDataAdapter mySQLdataAdapter;
private SqlCommand mySQLCommand;
private SqlTransaction mySQLTransaction =null;
private SqlParameter[]mySQLParameters =null;
private string strConnection = ConfigurationManager.ConnectionStrings["SQLDBConnection"].ConnectionString;
public SQLDBManager()
{
Open();
}
public SQLDBManager(int paramNum)
{
Open();
if (paramNum >= 1)
{ CreateParameters(paramNum); }
}
public SqlConnection Connection
{
get
{
return mySQLDbConnection;
}
}
public SqlDataReader DataReader
{
get
{
return mySQLdataReader;
}
set
{
mySQLdataReader = value;
}
}
public SqlDataAdapter DataAdapter
{
get
{
return mySQLdataAdapter;
}
set
{
mySQLdataAdapter = value;
}
}
public string ConnectionString
{
get
{
return strConnection;
}
set
{
strConnection = value;
}
}
public SqlCommand Command
{
get
{
return mySQLCommand;
}
}
public SqlTransaction Transaction
{
get
{
return mySQLTransaction;
}
}
public SqlParameter[] Parameters
{
get
{
return mySQLParameters;
}
}
public void Open()
{
mySQLDbConnection = DBManagerFactory.GetConnection();
mySQLDbConnection.ConnectionString = this.ConnectionString;
if (mySQLDbConnection.State != ConnectionState.Open)
mySQLDbConnection.Open();
this.mySQLCommand = DBManagerFactory.GetCommand();
}
public void Close()
{
if (mySQLDbConnection.State != ConnectionState.Closed)
mySQLDbConnection.Close();
}
public void Dispose()
{
GC.SuppressFinalize(this);
this.Close();
this.mySQLCommand = null;
this.mySQLTransaction = null;
this.mySQLDbConnection = null;
}
public void CreateParameters(int paramsCount)
{
mySQLParameters = new SqlParameter[paramsCount];
mySQLParameters = DBManagerFactory.GetParameters(paramsCount);
}
public void AddParameters(int index, string paramName, object objValue)
{
if (index < mySQLParameters.Length)
{
mySQLParameters[index].ParameterName = paramName;
mySQLParameters[index].Value = objValue;
}
}
public void BeginTransaction()
{
if (this.mySQLTransaction == null)
mySQLTransaction = DBManagerFactory.GetTransaction();
this.mySQLCommand.Transaction = mySQLTransaction;
}
public void CommitTransaction()
{
if (this.mySQLTransaction != null)
this.mySQLTransaction.Commit();
mySQLTransaction = null;
}
public SqlDataReader ExecuteReader(CommandType commandType, string commandText)
{
this.mySQLCommand = DBManagerFactory.GetCommand();
mySQLCommand.Connection = this.Connection;
PrepareCommand(mySQLCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
this.DataReader = mySQLCommand.ExecuteReader();
mySQLCommand.Parameters.Clear();
return this.DataReader;
}
public void CloseReader()
{
if (this.DataReader != null)
this.DataReader.Close();
}
private void AttachParameters(SqlCommand command, SqlParameter[]commandParameters)
{
foreach (SqlParameter idbParameter in commandParameters)
{
if ((idbParameter.Direction == ParameterDirection.InputOutput) && (idbParameter.Value == null))
{
idbParameter.Value = DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[]commandParameters)
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
}
public int ExecuteNonQuery(CommandType commandType, string commandText)
{
this.mySQLCommand = DBManagerFactory.GetCommand();
PrepareCommand(mySQLCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
int returnValue = mySQLCommand.ExecuteNonQuery();
mySQLCommand.Parameters.Clear();
Dispose();
return returnValue;
}
public object ExecuteScalar(CommandType commandType, string commandText)
{
this.mySQLCommand = DBManagerFactory.GetCommand();
PrepareCommand(mySQLCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
object returnValue = mySQLCommand.ExecuteScalar();
mySQLCommand.Parameters.Clear();
Dispose();
return returnValue;
}
public DataSet ExecuteDataSet(CommandType commandType, string commandText, string dsName)
{
this.mySQLCommand = DBManagerFactory.GetCommand();
PrepareCommand(mySQLCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
SqlDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter();
dataAdapter.SelectCommand = mySQLCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, dsName);
mySQLCommand.Parameters.Clear();
Dispose();
return dataSet;
}
}
}
SQLDBManager mySQLdbManager = new SQLDBManager(1);
mySQLdbManager.AddParameters(0, "@RegID", RegID);
DataSet dsPersonalInfo = new DataSet();
dsPersonalInfo = mySQLdbManager.ExecuteDataSet(CommandType.StoredProcedure, "CI_GetPersonalInfo", "Personal");
Label1.Text = "welcome " + dsPersonalInfo.Tables["Personal"].Rows[0]["FirstName"]
+ " " + dsPersonalInfo.Tables["Personal"].Rows[0]["LastName"]
