Ancapsulating database operations into a dedicated helper class proomtes code reuse, maintainability, and separation of concerns.
Database Helper Clas
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessLayer
{
public class SqlDbManager
{
private SqlConnection _activeConnection;
private DataSet _dataSet;
private SqlDataAdapter _dataAdapter;
private SqlCommand _sqlCommand;
private SqlDataReader _dataReader;
// Connection string should be configured externally (e.g., in app.config)
public static string ConnectionString = "Server=YourServer;Database=YourDB;User Id=YourUser;Password=YourPassword;";
public SqlDbManager() { }
#region Utility Methods
public static bool DoesColumnExist(string targetTable, string columnName)
{
string query = $"SELECT COUNT(1) FROM syscolumns WHERE [id]=OBJECT_ID('{targetTable}') AND [name]='{columnName}'";
object result = ExecuteScalarQuery(query);
return result != null && Convert.ToInt32(result) > 0;
}
public static int GetNextId(string idField, string tableName)
{
string query = $"SELECT MAX({idField}) + 1 FROM {tableName}";
object result = ExecuteScalarQuery(query);
return (result == null || result == DBNull.Value) ? 1 : Convert.ToInt32(result);
}
public static bool RecordExists(string sqlQuery)
{
object result = ExecuteScalarQuery(sqlQuery);
int count = (result == null || result == DBNull.Value) ? 0 : Convert.ToInt32(result);
return count > 0;
}
public static bool TableExists(string tableName)
{
string query = $"SELECT COUNT(*) FROM sysobjects WHERE id = OBJECT_ID(N'[{tableName}]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1";
object result = ExecuteScalarQuery(query);
int count = (result == null || result == DBNull.Value) ? 0 : Convert.ToInt32(result);
return count > 0;
}
public static bool RecordExists(string sqlQuery, params SqlParameter[] parameters)
{
object result = ExecuteScalarQuery(sqlQuery, parameters);
int count = (result == null || result == DBNull.Value) ? 0 : Convert.ToInt32(result);
return count > 0;
}
#endregion
#region Basic SQL Execution
public static int RunNonQuery(string sqlStatement)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand(sqlStatement, conn))
{
conn.Open();
return cmd.ExecuteNonQuery();
}
}
public static int RunNonQueryWithTimeout(string sqlStatement, int timeoutSeconds)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand(sqlStatement, conn))
{
conn.Open();
cmd.CommandTimeout = timeoutSeconds;
return cmd.ExecuteNonQuery();
}
}
public static int ExecuteTransaction(List<string> sqlStatements)
{
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
var transaction = conn.BeginTransaction();
var cmd = new SqlCommand { Connection = conn, Transaction = transaction };
try
{
int totalRowsAffected = 0;
foreach (var stmt in sqlStatements)
{
if (string.IsNullOrWhiteSpace(stmt)) continue;
cmd.CommandText = stmt;
totalRowsAffected += cmd.ExecuteNonQuery();
}
transaction.Commit();
return totalRowsAffected;
}
catch
{
transaction.Rollback();
return 0;
}
}
}
public static object ExecuteScalarQuery(string sqlStatement)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand(sqlStatement, conn))
{
conn.Open();
object result = cmd.ExecuteScalar();
return (result == null || result == DBNull.Value) ? null : result;
}
}
public static SqlDataReader GetDataReader(string sqlStatement)
{
var conn = new SqlConnection(ConnectionString);
var cmd = new SqlCommand(sqlStatement, conn);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public static DataSet GetDataSet(string sqlStatement)
{
using (var conn = new SqlConnection(ConnectionString))
{
var ds = new DataSet();
var adapter = new SqlDataAdapter(sqlStatement, conn);
adapter.Fill(ds, "ResultTable");
return ds;
}
}
#endregion
#region Parameterized SQL Execution
public static int RunParameterizedNonQuery(string sqlStatement, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand())
{
SetupCommand(cmd, conn, null, sqlStatement, parameters);
int affectedRows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return affectedRows;
}
}
public static void ExecuteParameterizedTransaction(Hashtable sqlCommandsWithParams)
{
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
var cmd = new SqlCommand();
try
{
foreach (DictionaryEntry entry in sqlCommandsWithParams)
{
string sqlText = entry.Key.ToString();
SqlParameter[] cmdParams = (SqlParameter[])entry.Value;
SetupCommand(cmd, conn, transaction, sqlText, cmdParams);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public static object ExecuteParameterizedScalar(string sqlStatement, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(ConnectionString))
using (var cmd = new SqlCommand())
{
SetupCommand(cmd, conn, null, sqlStatement, parameters);
object result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return (result == null || result == DBNull.Value) ? null : result;
}
}
public static SqlDataReader GetParameterizedReader(string sqlStatement, params SqlParameter[] parameters)
{
var conn = new SqlConnection(ConnectionString);
var cmd = new SqlCommand();
SetupCommand(cmd, conn, null, sqlStatement, parameters);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
public static DataSet GetParameterizedDataSet(string sqlStatement, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(ConnectionString))
{
var cmd = new SqlCommand();
SetupCommand(cmd, conn, null, sqlStatement, parameters);
using (var adapter = new SqlDataAdapter(cmd))
{
var ds = new DataSet();
adapter.Fill(ds, "ResultTable");
cmd.Parameters.Clear();
return ds;
}
}
}
private static void SetupCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, string commandText, SqlParameter[] parameters)
{
if (connection.State != ConnectionState.Open)
connection.Open();
command.Connection = connection;
command.CommandText = commandText;
command.Transaction = transaction;
command.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (var param in parameters)
{
if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) && param.Value == null)
{
param.Value = DBNull.Value;
}
command.Parameters.Add(param);
}
}
}
#endregion
#region Stored Procedure Operations
public static SqlDataReader ExecuteStoredProcedureReader(string procedureName, IDataParameter[] parameters)
{
var conn = new SqlConnection(ConnectionString);
conn.Open();
var cmd = CreateProcedureCommand(conn, procedureName, parameters);
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public static DataSet ExecuteStoredProcedureDataSet(string procedureName, IDataParameter[] parameters, string resultTableName)
{
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
var adapter = new SqlDataAdapter();
adapter.SelectCommand = CreateProcedureCommand(conn, procedureName, parameters);
var ds = new DataSet();
adapter.Fill(ds, resultTableName);
return ds;
}
}
public static int ExecuteStoredProcedureNonQuery(string procedureName, IDataParameter[] parameters, out int rowsAffected)
{
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
var cmd = CreateProcedureCommandWithReturn(conn, procedureName, parameters);
rowsAffected = cmd.ExecuteNonQuery();
return (int)cmd.Parameters["ReturnValue"].Value;
}
}
private static SqlCommand CreateProcedureCommand(SqlConnection connection, string procedureName, IDataParameter[] parameters)
{
var cmd = new SqlCommand(procedureName, connection) { CommandType = CommandType.StoredProcedure };
if (parameters != null)
{
foreach (SqlParameter param in parameters)
{
if (param != null)
{
if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) && param.Value == null)
{
param.Value = DBNull.Value;
}
cmd.Parameters.Add(param);
}
}
}
return cmd;
}
private static SqlCommand CreateProcedureCommandWithReturn(SqlConnection connection, string procedureName, IDataParameter[] parameters)
{
var cmd = CreateProcedureCommand(connection, procedureName, parameters);
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return cmd;
}
#endregion
}
}
CRUD Operations Using the Helper
The helper class can be utilized within specific data access layers or service classes.
public class SalesRepository
{
public static string[] FetchOrderDetails(string orderNumber, string productCode)
{
string query = $"SELECT * FROM Orders WHERE OrderNum='{orderNumber}' AND ProductCode='{productCode}'";
DataSet result = SqlDbManager.GetDataSet(query);
if (result == null || result.Tables[0].Rows.Count == 0)
return null;
DataTable table = result.Tables[0];
int colCount = table.Columns.Count;
string[] rowData = new string[colCount];
for (int col = 0; col < colCount; col++)
{
rowData[col] = table.Rows[0][col].ToString();
}
return rowData;
}
public static DataTable GetOrdersByNumber(string orderNumber)
{
string query = $"SELECT * FROM Orders WHERE OrderNum='{orderNumber}'";
DataSet result = SqlDbManager.GetDataSet(query);
return result?.Tables[0];
}
public static int AddSalesRecord(string customerName, decimal totalAmount)
{
string insertCommand = $"INSERT INTO Sales (CustomerName, TotalAmount) VALUES ('{customerName}', {totalAmount})";
return SqlDbManager.RunNonQuery(insertCommand);
}
public static DataTable GetAllPurchases()
{
string query = "SELECT * FROM Purchases";
DataSet result = SqlDbManager.GetDataSet(query);
return result?.Tables[0];
}
}
Usage Example:
// Retrieve data
string[] orderInfo = SalesRepository.FetchOrderDetails("ORD1001", "PCODE456");
DataTable purchases = SalesRepository.GetAllPurchases();
// Insert data
int rowsInserted = SalesRepository.AddSalesRecord("John Doe", 249.99m);