public DataTable GetDataTable(string scriptOrProcedure, DBCommandType commandType = DBCommandType.Procedure, params Parameter[] parameters)
{
try
{
DataTable dataTable = new DataTable();
BindCommand(scriptOrProcedure, commandType);
BindParameters(parameters);
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
return(dataTable);
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection();
}
}
private void BindCommand(string scriptOrProcedure, DBCommandType commandType = DBCommandType.Procedure)
{
command = new SqlCommand();
command.Connection = connection;
command.CommandText = scriptOrProcedure;
command.CommandType = commandType == DBCommandType.Procedure
? CommandType.StoredProcedure
: CommandType.Text;
}
public DataTable ExecuteReader(string scriptOrProcedure, DBCommandType commandType = DBCommandType.Procedure, params Parameter[] parameters)
{
try
{
return(GetDataTable(scriptOrProcedure, commandType, parameters));
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection();
}
}
public void SetCommandType(DBCommandType dBCommandType)
{
CommandType command = CommandType.Text;
switch (dBCommandType)
{
case DBCommandType.Text:
command = CommandType.Text;
break;
case DBCommandType.StoredProcedure:
command = CommandType.StoredProcedure;
break;
case DBCommandType.TableDirect:
command = CommandType.TableDirect;
break;
}
sqlCommand.CommandType = command;
}
public int ExecuteNonQueryWithTransaction(string scriptOrProcedure, DBCommandType commandType = DBCommandType.Procedure, params Parameter[] parameters)
{
try
{
BindCommand(scriptOrProcedure, commandType);
BindParameters(parameters);
OpenConnection();
return(command.ExecuteNonQuery());
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection();
}
}
