.Net Data Helper

This class file will help you to write .net SQL Operations easily.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace GMS.DAL {
public class DALHelper {#region Fields

public SqlConnection _Connection = null;

#endregion Fields

#region Constructors

public DALHelper(string connectionString) {
_Connection = new SqlConnection(connectionString);
}

#endregion Constructors

#region Methods

public static DataTable GetDatatable(List < string > dataList, string tableName, List < string > columnList) {
DataTable dt;
dt = new DataTable(tableName);

foreach(string column in columnList) {
dt.Columns.Add(column, typeof(string));
}

foreach(string item in dataList) {
dt.Rows.Add(item);
}

return dt;
}

public static DataTable GetDataTableOneColumn(List < object > dataList, string tableName, string columnName, Type columnType) {
DataTable dt;
dt = new DataTable(tableName);
dt.Columns.Add(columnName, columnType);

foreach(object item in dataList) {
dt.Rows.Add(item);
}

return dt;
}

public static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, object parameterValue) {
SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType);
paramParameter.Value = parameterValue;
return paramParameter;
}

public void Dispose() {
try {
if (_Connection != null && _Connection.State != ConnectionState.Closed) _Connection.Close();
}
catch(Exception e) {
throw e;
}
}

public int ExecuteNonQuery(string query, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = new SqlCommand(query, _Connection);
if (parameters != null) oCommand.Parameters.AddRange(parameters);

returnValue = oCommand.ExecuteNonQuery();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public int ExecuteNonQueryProcedure(string storedProcName, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = oCommand.ExecuteNonQuery();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public DataSet ExecuteProcedureReader(string storedProcName, params SqlParameter[] parameters) {
DataSet Result = new DataSet();
SqlDataAdapter objSqlDataAdapter = null;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
oCommand.CommandTimeout = 240;
objSqlDataAdapter = new SqlDataAdapter(oCommand);
objSqlDataAdapter.Fill(Result);
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}
return Result;
}

public DataSet ExecuteQueryReader(string query, params SqlParameter[] parameters) {
DataSet Result = new DataSet();
SqlDataAdapter objSqlDataAdapter = null;
try {
Open();

SqlCommand oCommand = new SqlCommand(query, _Connection);

if (parameters != null) oCommand.Parameters.AddRange(parameters);

objSqlDataAdapter = new SqlDataAdapter(oCommand);
objSqlDataAdapter.Fill(Result);

oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}
return Result;
}

public int ExecutScalarProcedure(string storedProcName, params SqlParameter[] parameters) {
int returnValue = 0;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = Convert.ToInt32(oCommand.ExecuteScalar());
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

public string ExecutScalarProcedureString(string storedProcName, params SqlParameter[] parameters) {
string returnValue = string.Empty;
try {
Open();
SqlCommand oCommand = GetSqlCommandObject(storedProcName, parameters);
returnValue = oCommand.ExecuteScalar().ToString();
oCommand.Dispose();
Dispose();
}
catch(Exception e) {
throw e;
}

return returnValue;
}

internal static SqlParameter SqlParameter(string parameterName, SqlDbType parameterType, int parameterSize, string parameterValue) {
SqlParameter paramParameter = new SqlParameter("@" + parameterName, parameterType, parameterSize);
paramParameter.Value = parameterValue;
return paramParameter;
}

private SqlCommand GetSqlCommandObject(string storedProcName, params SqlParameter[] parameters) {
SqlCommand oCommand = new SqlCommand(storedProcName, _Connection);
oCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null) oCommand.Parameters.AddRange(parameters);
return oCommand;
}

private void Open() {
try {
if (_Connection != null && _Connection.State == ConnectionState.Closed) _Connection.Open();
}
catch(Exception) {}
}

#endregion Methods
}
}