/// <summary>
/// Create Sql Command
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public CSqlDbCommand CreateCommand(string commandText)
{
var cmd = new CSqlDbCommand(commandText);
Parameters = cmd.Parameters;
return(cmd);
}
/// <summary>
/// /Create Sql Command
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <param name="collection"></param>
/// <returns></returns>
public CSqlDbCommand CreateCommand(string commandText, CommandType cmdType, CSqlDbParameterCollection collection)
{
var cmd = new CSqlDbCommand(commandText, cmdType);
cmd.Parameters = collection;
Parameters = cmd.Parameters;
return(cmd);
}
/// <summary>
/// Execute Reader
/// </summary>
/// <param name="procName">Procedure Name</param>
/// <param name="sqlParams">SqlParameterCollection
/// use the GetInitCollection() method to init the SqlparameterCollection.
/// Eg: SqlParameterCollection sqlParams = GetInitCollection();
/// </param>
/// <returns></returns>
public override void ExecReader(CSqlDbCommand dbCommand)
{
OpenConnection();
SqlCommand cmd = _sqlCon.CreateCommand();
try
{
cmd.CommandText = dbCommand.CommandText;
//Command Type
if (dbCommand.CommandType != 0)
{
cmd.CommandType = dbCommand.CommandType;
}
else
{
cmd.CommandType = this.CommandType;
}
if (dbCommand.CommandTimeout != 0)
{
cmd.CommandTimeout = dbCommand.CommandTimeout;
}
else if (this.CommandTimeout != 0)
{
cmd.CommandTimeout = this.CommandTimeout;
}
if (_sqlTransaction != null)
{
cmd.Transaction = _sqlTransaction;
}
if (dbCommand.Parameters != null && dbCommand.Parameters.Count > 0)
{
cmd.Parameters.AddRange(GetParametersWithNativeType(dbCommand.Parameters));
}
this._dataReader = cmd.ExecuteReader();
GetOutputParamterValuesIncludeInput((DbParameterCollection)cmd.Parameters, "@");
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd = null;
}
}
/// <summary>
/// Fill command result set into dataSet variable
/// </summary>
/// <param name="dataSet">DataSet object</param>
/// <param name="commandName">Stored Procedure Name</param>
/// <param name="paramters">SQL parameter colelction</param>
public override void FillData(DataSet dataSet, CSqlDbCommand dbCmd)
{
SqlCommand cmd = _sqlCon.CreateCommand();
if (dbCmd.CommandTimeout != 0)
{
cmd.CommandTimeout = dbCmd.CommandTimeout;
}
else if (this.CommandTimeout != 0)
{
cmd.CommandTimeout = this.CommandTimeout;
}
cmd.CommandText = dbCmd.CommandText;
//Command Type
if (dbCmd.CommandType != 0)
{
cmd.CommandType = dbCmd.CommandType;
}
else
{
cmd.CommandType = this.CommandType;
}
if (dbCmd.Parameters != null && dbCmd.Parameters.Count > 0)
{
cmd.Parameters.AddRange(GetParametersWithNativeType(dbCmd.Parameters));
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
try
{
adp.Fill(dataSet);
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd = null;
}
}
/// <summary>
/// SQL data access specific method
/// </summary>
/// <param name="commandText"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public IList <object[]> Query(string commandText, CommandType cmdType)
{
CSqlDbCommand cmd = new CSqlDbCommand(commandText, cmdType);
ExecReader(cmd);
List <object[]> list = new List <object[]>();
while (DataReader.Read())
{
object[] data = new object[DataReader.FieldCount];
for (int index = 0; index < DataReader.FieldCount; index++)
{
data[index] = DataReader[index];
}
list.Add(data);
}
CloseDataReader();
return(list);
}
/// <summary>
/// This call the underlying ExecuteReader and also it close the data reader.
/// Since 3.5.1.7
/// </summary>
/// <param name="dbCommand"></param>
/// <param name="reader"></param>
public void ExecReader(CSqlDbCommand dbCommand, Action <IDataReader> reader)
{
this.ExecReader(dbCommand);
reader(this.DataReader);
this.CloseDataReader();
}
/// <summary> /// This call the underlying ExecuteReader and also it close the data reader. /// Since 3.5.1.7 /// </summary> /// <param name="dbCommand"></param> public abstract void ExecReader(CSqlDbCommand dbCommand);
/// <summary> /// SQL Execute Command /// </summary> /// <param name="command"></param> /// <returns></returns> public abstract int ExecCommand(CSqlDbCommand command);
/// <summary> /// SQL ExecuteScalar /// </summary> /// <param name="command"></param> /// <returns></returns> public abstract object ExecScalar(CSqlDbCommand command);
/// <summary> /// Fill Data into dataset /// </summary> /// <param name="dataSet"></param> /// <param name="command"></param> public abstract void FillData(System.Data.DataSet dataSet, CSqlDbCommand command);
/// <summary>
/// Execute SQL Stored Procedure
/// </summary>
/// <param name="commandName">Stored Procedure Name</param>
/// <param name="paramters">SQL parameter colelction</param>
/// <returns></returns>
public override int ExecCommand(CSqlDbCommand dbCommand)
{
OpenConnection();
SqlCommand sqlCmd;
try
{
sqlCmd = _sqlCon.CreateCommand();
sqlCmd.CommandText = dbCommand.CommandText;
//Command Type
if (dbCommand.CommandType != 0)
{
sqlCmd.CommandType = dbCommand.CommandType;
}
else
{
sqlCmd.CommandType = this.CommandType;
}
if (dbCommand.CommandTimeout != 0)
{
sqlCmd.CommandTimeout = dbCommand.CommandTimeout;
}
else if (this.CommandTimeout != 0)
{
sqlCmd.CommandTimeout = this.CommandTimeout;
}
if (_sqlTransaction != null)
{
sqlCmd.Transaction = _sqlTransaction;
}
if (sqlCmd.CommandTimeout != 0)
{
sqlCmd.CommandTimeout = CommandTimeout;
}
if (dbCommand.Parameters != null && dbCommand.Parameters.Count > 0)
{
sqlCmd.Parameters.AddRange(GetParametersWithNativeType(dbCommand.Parameters));
}
int i = sqlCmd.ExecuteNonQuery();
GetOutputParamterValuesIncludeInput((DbParameterCollection)sqlCmd.Parameters, "@");
return(i);
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd = null;
if (_sqlTransaction == null)
{
CloseConnection();
}
}
}
/// <summary>
/// Execute Scalar
/// </summary>
/// <param name="commandName">Stored Procedure Name</param>
/// <param name="paramters">SQL parameter collection</param>
/// <returns></returns>
public override object ExecScalar(CSqlDbCommand dbCommand)
{
OpenConnection();
SqlCommand sqlCmd;
try
{
sqlCmd = _sqlCon.CreateCommand();
sqlCmd.CommandText = dbCommand.CommandText;
//set transaction object
if (_sqlTransaction != null)
{
sqlCmd.Transaction = _sqlTransaction;
}
//set command timeout
if (dbCommand.CommandTimeout != 0)
{
sqlCmd.CommandTimeout = dbCommand.CommandTimeout;
}
else if (this.CommandTimeout != 0)
{
sqlCmd.CommandTimeout = this.CommandTimeout;
}
//set Command Type
if (dbCommand.CommandType != 0)
{
sqlCmd.CommandType = dbCommand.CommandType;
}
else
{
sqlCmd.CommandType = this.CommandType;
}
//set parameters
if (dbCommand.Parameters != null && dbCommand.Parameters.Count > 0)
{
sqlCmd.Parameters.AddRange(GetParametersWithNativeType(dbCommand.Parameters));
}
return(sqlCmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd = null;
if (_sqlTransaction == null)
{
CloseConnection();
}
}
}