/// <summary>
/// The GetCommand method is used by public facing method(s) of the helper library.
/// </summary>
/// <param name="sqlText">The SQL query/stored Procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/stored procedure
/// requires in order to successfully execute.</param>
/// <returns></returns>
private void InitializeCommand(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
//Pseudocode
//1. Add the sql query/stored procedure and connection to db.
//2. See what command type we need to set - text for query and stored proc for sp.
//3. Add the parameterCollection after checking whether it is empty or not.
//4. Return the command.
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
Command = new SqlCommand(sqlText, ConnectToDB());
switch (sqlTextType)
{
case SQLTextType.Query:
Command.CommandType = CommandType.Text;
break;
default:
Command.CommandType = CommandType.StoredProcedure;
break;
}
if (parameterCollection != null && parameterCollection.Count > 0)
{
foreach (var parameter in parameterCollection)
{
Command.Parameters.Add(parameter);
}
}
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set in a data view.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data view containing the result set.</returns>
public DataView GetDataView(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
return(GetDataTable(sqlText, sqlTextType, parameterCollection).DefaultView);
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set in a data table.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data table containing the result set.</returns>
public DataTable GetDataTable(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
InitializeCommand(sqlText, sqlTextType, parameterCollection);
using (DataTable = new DataTable())
{
DataTable.Load(Command.ExecuteReader(CommandBehavior.CloseConnection));
return(DataTable);
}
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the total number of rows affected.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns the total number of rows affected.</returns>
public async Task <int> GetRowsAffectedAsync(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
InitializeCommand(sqlText, sqlTextType, parameterCollection);
return(await Command.ExecuteNonQueryAsync());
}
/// <summary>
/// Executes a given SQL query or stored procedure.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
public void ExecSQL(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
InitializeCommand(sqlText, sqlTextType, parameterCollection);
Command.ExecuteNonQuery();
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set in a data view.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data view containing the result set.</returns>
//public DataView GetDataView(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)
//{
// return GetDataTable(sqlText, sqlTextType, parameterCollection).AsDataView();
//}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set in a data view.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <returns>Returns a data view containing the result set.</returns>
//public DataView GetDataView(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
//{
// return GetDataView(sqlText, sqlTextType, null);
//}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set(s) in a data set.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data set containing the result set(s).</returns>
public DataSet GetDataSet(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
InitializeCommand(sqlText, sqlTextType, parameterCollection);
using (SqlDataAdapter adapter = new SqlDataAdapter(Command))
{
DataSet = new DataSet();
adapter.Fill(DataSet);
return(DataSet);
}
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set in a data table.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data table containing the result set.</returns>
public DataTable GetDataTable(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
InitializeCommand(sqlText, sqlTextType, parameterCollection);
using (DataTable = new DataTable())
{
DataTable.Load(Command.ExecuteReader(CommandBehavior.CloseConnection));
return(DataTable);
}
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set(s) in a data set.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns a data set containing the result set(s).</returns>
public DataSet GetDataSet(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
if (sqlText == null || sqlText == string.Empty)
{
throw new ArgumentNullException("The provided SQL text should be non-null and non-empty.");
}
InitializeCommand(sqlText, sqlTextType, parameterCollection);
using (SqlDataAdapter adapter = new SqlDataAdapter(Command))
{
DataSet = new DataSet();
adapter.Fill(DataSet);
return(DataSet);
}
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the result set(s) in a data set.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <returns>Returns a data set containing the result set(s).</returns>
public DataSet GetDataSet(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
{
return(GetDataSet(sqlText, sqlTextType, null));
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the total number of rows affected.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <returns>Returns the total number of rows affected.</returns>
public int GetRowsAffected(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
{
return(GetRowsAffected(sqlText, sqlTextType, null));
}
/// <summary>
/// Executes a given SQL query/stored procedure and returns the total number of rows affected.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
/// <returns>Returns the total number of rows affected.</returns>
public int GetRowsAffected(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
InitializeCommand(sqlText, sqlTextType, parameterCollection);
return(Command.ExecuteNonQuery());
}
/// <summary>
/// Executes a given SQL query or stored procedure.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
public void ExecSQL(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
{
ExecSQL(sqlText, sqlTextType, null);
}
/// <summary>
/// Executes a given SQL query or stored procedure.
/// </summary>
/// <param name="sqlText">The SQL query/stored procedure passed in to be executed.</param>
/// <param name="sqlTextType">The enumerated value used to denote whether the passed in sql text is a
/// sql query or a stored procedure.</param>
/// <param name="parameterCollection">The collection of parameters that the sql query/ stored procedure
/// requires in order to successfully execute.</param>
public void ExecSQL(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List <SqlParameter> parameterCollection = null)
{
InitializeCommand(sqlText, sqlTextType, parameterCollection);
Command.ExecuteNonQuery();
}