ADO.NET Functions for Running Query and Procedures

public DataSet RunQuery(string Query)
{
OpenDB();
SqlCommand sqlCmd = new SqlCommand(Query, sqlConn);
sqlCmd.CommandType = CommandType.Text;

SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);

sqlCmd.Dispose();
this.Dispose();

return dtSetObject;
}

public int RunProc(string procName)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
this.Dispose();
return (int)sqlCmd.Parameters["ReturnValue"].Value;
}

public int RunProc(string procName, SqlParameter[] @params)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
this.Dispose();
return (int)sqlCmd.Parameters["ReturnValue"].Value;
}

private void RunProc(string procName, ref SqlDataReader dataReader)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
dataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}

public void RunProc(string procName, ref DataSet dataSetObject)
{
SqlCommand sqlCmd = CreateCommand(procName, null);
SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);
dataSetObject = dtSetObject;
sqlCmd.Dispose();
this.Dispose();
}

public void RunProc(string procName, SqlParameter[] @params, ref DataSet dataSetObject)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlCmd);
DataSet dtSetObject = new DataSet();
dtAdapter.Fill(dtSetObject);
dataSetObject = dtSetObject;
sqlCmd.Dispose();
this.Dispose();
}

private void RunProc(string procName, SqlParameter[] @params, ref SqlDataReader dataReader)
{
SqlCommand sqlCmd = CreateCommand(procName, @params);
dataReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
}


private SqlCommand CreateCommand( string procName,SqlParameter[] @params )
{
OpenDB();
SqlCommand sqlCmd = null;
sqlCmd = new SqlCommand(procName, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
if (@params != null)
{
foreach (SqlParameter parameter in @params)
{
sqlCmd.Parameters.Add(parameter);
}
}
sqlCmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return sqlCmd;
}

public SqlParameter MakeInParams(string paramName, SqlDbType paramType, int size, object obj)
{
return MakeParameter(paramName, paramType, size, ParameterDirection.Input, obj);

}

public SqlParameter MakeOutParams(string paramName, SqlDbType paramType, int size)
{
return MakeParameter(paramName, paramType, size, ParameterDirection.Output, null);
}

private SqlParameter MakeParameter(string paramName, SqlDbType paramType, int size, ParameterDirection paramDirection, object obj)
{
SqlParameter sqlParam = null;
if (size > 0)
{
sqlParam = new SqlParameter(paramName, paramType, size);
}
else
{
sqlParam = new SqlParameter(paramName, paramType);
}
sqlParam.Direction = paramDirection;
if (!(sqlParam.Direction == paramDirection & obj == null))
{
sqlParam.Value = obj;
}
return sqlParam;
}

private string getPoolConSting()
{
if (DBConnection.DBPoolConString == null)
{
string DBServer = ConfigurationManager.AppSettings.Get("DBServer");
string DBCredential = ConfigurationManager.AppSettings.Get("DBCredential");
DBCredential = Util.DecryptToString(DBCredential);
string PoolOn = ConfigurationManager.AppSettings.Get("PoolOn");
DBConnection.DBPoolConString = DBServer + DBCredential + PoolOn;
}
return DBConnection.DBPoolConString;
}

private string getnonPoolConString()
{
if (DBConnection.DBNonPoolConString == null)
{
string DBServer = ConfigurationManager.AppSettings.Get("DBServer");
string DBCredential = ConfigurationManager.AppSettings.Get("DBCredential");
DBCredential = Util.DecryptToString(DBCredential);
string PoolOff = ConfigurationManager.AppSettings.Get("PoolOff");
DBConnection.DBNonPoolConString = DBServer + DBCredential + PoolOff;
}
return DBConnection.DBNonPoolConString;
}

private void OpenDB()
{
if (sqlConn == null)
{
try
{
sqlConn = new SqlConnection(getPoolConSting());
sqlConn.Open();
}
catch (System.Data.SqlClient.SqlException)
{
if (sqlConn.State != ConnectionState.Closed)
{
sqlConn.Close();
sqlConn = new SqlConnection(getnonPoolConString());
sqlConn.Open();
}
}
}
}

public void Dispose()
{
if (!(System.Convert.IsDBNull(sqlConn)))
{
sqlConn.Dispose();
sqlConn = null;
}
}

public void Close()
{
if (!(System.Convert.IsDBNull(sqlConn)))
{
sqlConn.Close();
}
}