/// <summary>
/// Execute As DataReader
/// </summary>
/// <param name="StroredProcedureName">Store Procedure Name </param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters</param>
/// <returns></returns>
public async Task <SqlDataReader> ExecuteAsDataReaderAsync(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
try
{
SqlDataReader SQLReader;
await SQLConn.OpenAsync();
SQLReader = await SQLCmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
return(SQLReader);
}
catch (Exception e)
{
throw e;
}
}
}
}
/// <summary>
/// Execute As Scalar
/// </summary>
/// <typeparam name="T"><T></typeparam>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters</param>
/// <returns></returns>
public async Task <T> ExecuteAsScalarAsync <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
try
{
await SQLConn.OpenAsync();
object t = await SQLCmd.ExecuteScalarAsync();
return((T)t);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Accept only int, Int16, long, DateTime, string (NVarcha of size 50),
/// bool, decimal ( of size 16,2), float
/// </summary>
/// <typeparam name="T">Return the given type of object</typeparam>
/// <param name="StroredProcedureName">Accet SQL procedure name in string</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection for parameters</param>
/// <param name="OutPutParamerterName">Accept Output parameter for the stored procedures</param>
/// <returns></returns>
public async Task <T> ExecuteNonQueryAsGivenTypeAsync <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection, string OutPutParamerterName, object OutPutParamerterValue)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
SqlCommand SQLCmd = new SqlCommand
{
Connection = SQLConn,
CommandText = StroredProcedureName,
CommandType = CommandType.StoredProcedure
};
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLCmd = AddOutPutParametrofGivenType <T>(SQLCmd, OutPutParamerterName, OutPutParamerterValue);
try
{
await SQLConn.OpenAsync();
await SQLCmd.ExecuteNonQueryAsync();
return((T)SQLCmd.Parameters[OutPutParamerterName].Value);;
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
/// <summary>
/// Execute As DataSet
/// </summary>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters</param>
/// <returns></returns>
public async Task <DataSet> ExecuteAsDataSetAsync(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SqlDataAdapter SQLAdapter = new SqlDataAdapter();
DataSet SQLds = new DataSet();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLAdapter.SelectCommand = SQLCmd;
try
{
await SQLConn.OpenAsync();
SQLAdapter.Fill(SQLds);
SQLConn.Close();
return(SQLds);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Returning Bool After Execute Non Query
/// </summary>
/// <param name="StroredProcedureName">Store Procedure Name</param>
/// <param name="ParaMeterCollection"> Parameter Collection</param>
/// <param name="OutPutParamerterName">Out Parameter Collection</param>
/// <returns>Bool</returns>
public async Task <bool> ExecuteNonQueryAsBoolAsync(string StroredProcedureName, List <SQLParam> ParaMeterCollection, string OutPutParamerterName)
{
using (SqlConnection SQLConn = new SqlConnection(_connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLCmd.Parameters.Add(new SqlParameter(OutPutParamerterName, SqlDbType.Bit));
SQLCmd.Parameters[OutPutParamerterName].Direction = ParameterDirection.Output;
try
{
await SQLConn.OpenAsync();
await SQLCmd.ExecuteNonQueryAsync();
bool ReturnValue = (bool)SQLCmd.Parameters[OutPutParamerterName].Value;
return(ReturnValue);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Execute Non Query
/// </summary>
/// <param name="StroredProcedureName">Store Procedure Name In String</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters<SQLParam> </param>
public async Task <int> ExecuteNonQueryAsync(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
try
{
int effectedRows = 0;
await SQLConn.OpenAsync();
effectedRows = await SQLCmd.ExecuteNonQueryAsync();
return(effectedRows);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Execute As list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection"></param>
/// <returns></returns>
public IList <T> ExecuteAsList <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
IList <T> mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Returning Bool After Execute Non Query
/// </summary>
/// <param name="StroredProcedureName">Store Procedure Name</param>
/// <param name="ParaMeterCollection">Parameter Collection</param>
/// <param name="OutPutParamerterName">OutPut Parameter Name</param>
/// <param name="OutPutParamerterValue">OutPut Parameter Value</param>
/// <returns>Bool</returns>
public bool ExecuteNonQueryAsBool(string StroredProcedureName, IList <SQLParam> ParaMeterCollection, string OutPutParamerterName, object OutPutParamerterValue)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
//End of for loop
SQLCmd.Parameters.Add(new SqlParameter(OutPutParamerterName, SqlDbType.Bit));
SQLCmd.Parameters[OutPutParamerterName].Direction = ParameterDirection.Output;
SQLCmd.Parameters[OutPutParamerterName].Value = OutPutParamerterValue;
SQLConn.Open();
SQLCmd.ExecuteNonQuery();
bool ReturnValue = (bool)SQLCmd.Parameters[OutPutParamerterName].Value;
return(ReturnValue);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Accept only int, Int16, long, DateTime, string (NVarcha of size 50),
/// bool, decimal ( of size 16,2), float
/// </summary>
/// <typeparam name="T">Return the given type of object</typeparam>
/// <param name="StroredProcedureName">Accet SQL procedure name in string</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection for parameters</param>
/// <param name="OutPutParamerterName">Accept Output parameter for the stored procedures</param>
/// <returns></returns>
public T ExecuteNonQueryAsGivenType <T>(string StroredProcedureName, IList <SQLParam> ParaMeterCollection, string OutPutParamerterName, object OutPutParamerterValue)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
//Loop for Paramets
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
//End of for loop
SQLCmd = AddOutPutParametrofGivenType <T>(SQLCmd, OutPutParamerterName, OutPutParamerterValue);
SQLConn.Open();
SQLCmd.ExecuteNonQuery();
return((T)SQLCmd.Parameters[OutPutParamerterName].Value);;
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute Non Query
/// </summary>
/// <param name="StroredProcedureName">Store Procedure Name In String</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters<SQLParam> </param>
public void ExecuteNonQuery(string StroredProcedureName, IList <SQLParam> ParaMeterCollection)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLConn.Open();
SQLCmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
public void ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, List <SQLParam> ParaMeterCollection)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText);
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
cmd.Parameters.AddRange(sqlParameters);
//finally, execute the command.
cmd.ExecuteNonQuery();
// detach the OracleParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
}
/// <summary>
/// Execute As Object
/// </summary>
/// <typeparam name="T"><T></typeparam>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters</param>
/// <returns></returns>
public async Task <T> ExecuteAsObjectAsync <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
try
{
SqlDataReader SQLReader;
await SQLConn.OpenAsync();
SQLReader = await SQLCmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
ArrayList arrColl = DataSourceHelper.FillCollection(SQLReader, typeof(T));
SQLConn.Close();
if (SQLReader != null)
{
SQLReader.Close();
}
if (arrColl != null && arrColl.Count > 0)
{
return((T)arrColl[0]);
}
else
{
return(default(T));
}
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Executes non query with multipal output.
/// </summary>
/// <param name="StroredProcedureName">Strored procedure name.</param>
/// <param name="InputParamColl">Accept Key Value collection for parameters.</param>
/// <param name="OutPutParamColl">Output Key Value collection for parameters.</param>
/// <returns>List Key Value collection</returns>
public async Task <List <KeyValuePair <int, string> > > ExecuteNonQueryWithMultipleOutputAsync(string StroredProcedureName, List <SQLParam> InputParamColl, List <SQLParam> OutPutParamColl)
{
using (SqlConnection SQLConn = new SqlConnection(base.connectionString))
{
try
{
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(InputParamColl).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
foreach (SQLParam kvp in OutPutParamColl)
{
SqlParameter sqlParaMeter = new SqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = kvp.Key;
sqlParaMeter.Value = kvp.Value;
sqlParaMeter.Direction = ParameterDirection.InputOutput;
sqlParaMeter.Size = 256;
SQLCmd.Parameters.Add(sqlParaMeter);
}
SQLConn.OpenAsync().Wait();
await SQLCmd.ExecuteNonQueryAsync();
List <KeyValuePair <int, string> > lstRetValues = new List <KeyValuePair <int, string> >();
for (int i = 0; i < OutPutParamColl.Count; i++)
{
lstRetValues.Add(new KeyValuePair <int, string>(i, SQLCmd.Parameters[InputParamColl.Count + i].Value.ToString()));
}
return(lstRetValues);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
public int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, List <SQLParam> ParaMeterCollection, string outParamName)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText);
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
cmd.Parameters.AddRange(sqlParameters);
cmd.Parameters.Add(new SqlParameter(outParamName, SqlDbType.Int));
cmd.Parameters[outParamName].Direction = ParameterDirection.Output;
//finally, execute the command.
cmd.ExecuteNonQuery();
int id = (int)cmd.Parameters[outParamName].Value;
// detach the OracleParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return(id);
}
