public static DataTable ExecQuery(string query, string connectionString, DBXParameters parameters, CommandType?commandType)
{
DataTable functionReturnValue = null;
DataTable objDataTable = new DataTable();
System.Data.SqlClient.SqlConnection sqlConnection = null;
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.CommandTimeout = CommandTimeout;
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
try
{
sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
sqlCommand.CommandText = query;
if (parameters != null && parameters.Any())
{
foreach (var param in parameters)
{
sqlCommand.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
}
}
if (commandType.HasValue)
{
sqlCommand.CommandType = commandType.Value;
}
sqlDataReader = sqlCommand.ExecuteReader();
objDataTable.Load(sqlDataReader);
}
catch (Exception ex)
{
KillDBObjects(sqlConnection, sqlCommand, sqlDataReader);
throw new Exception(query, ex);
}
KillDBObjects(sqlConnection, sqlCommand, sqlDataReader);
functionReturnValue = objDataTable;
objDataTable.Dispose();
return(functionReturnValue);
}
public static object ExecScalarQuery(string query, string connectionString, DBXParameters parameters, CommandType?commandType)
{
object objReturn = null;
System.Data.SqlClient.SqlConnection sqlConnection = null;
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.CommandTimeout = CommandTimeout;
try
{
sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
sqlCommand.CommandText = query;
if (parameters != null && parameters.Any())
{
foreach (var param in parameters)
{
sqlCommand.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
}
}
if (commandType.HasValue)
{
sqlCommand.CommandType = commandType.Value;
}
objReturn = sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
KillDBObjects(sqlConnection, sqlCommand, null);
throw new Exception(query, ex);
}
KillDBObjects(sqlConnection, sqlCommand, null);
return(objReturn);
}