/// <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(); }