/// <summary>
/// Creates a return parameter and inserts into the command list
/// </summary>
/// <param name="db">The database object</param>
/// <param name="cmd">The command object</param>
/// <remarks>The DBCommand parameter argument that is modified</remarks>
public static void CreateReturnParameter(Database db, ref DbCommand cmd)
{
#region Defensive
#region db
Debug.Assert(db != null, "The argument supplied for parameter 'db' should not be null");
if(db == null)
{
throw new ArgumentNullException("db", "The argument supplied for parameter 'db' should not be null");
}
#endregion
#region cmd
Debug.Assert(cmd != null, "The argument supplied for parameter 'cmd' should not be null");
if(cmd == null)
{
throw new ArgumentNullException("cmd", "The argument supplied for parameter 'cmd' should not be null");
}
#endregion
#endregion
db.AddParameter(cmd, RETURN_VALUE, DbType.Int32, sizeof(int), ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null);
}
/// <summary>
/// 为储存过程加载输出变量参数
/// </summary>
/// <param name="db">database</param>
/// <param name="dbCommand">db command</param>
/// <param name="cmdParms">参数数组</param>
public static void BuildDBParameterForReturnValue(Database db, DbCommand dbCommand, params IDataParameter[] cmdParms)
{
foreach (SqlParameter sp in cmdParms)
{
db.AddParameter(dbCommand, sp.ParameterName, sp.DbType, sp.Size, ParameterDirection.Output, false, 0, 0, sp.SourceColumn, DataRowVersion.Current, DBNull.Value);
}
}
/// <summary>
/// ExecuteNonQuery 数据添加、修改、删除
/// </summary>
/// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
{
try
{
this.Open();
if (cmdType == CommandType.StoredProcedure)
{
mDbCommand = mDatabase.GetStoredProcCommand(cmdText);
}
else
{
mDbCommand = mDatabase.GetSqlStringCommand(cmdText);
}
mDbCommand.Connection = Connection;
if (Transaction != null)
{
mDbCommand.Transaction = Transaction;
}
if ((commandParameters != null) && (commandParameters.Length > 0))
{
foreach (IDbDataParameter mParameter in commandParameters)
{
mDatabase.AddParameter(mDbCommand, mParameter.ParameterName, mParameter.DbType, mParameter.Direction, mParameter.SourceColumn, mParameter.SourceVersion, mParameter.Value);
}
}
return(mDbCommand.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (Transaction == null)
{
this.Close();
}
}
}
public void AddOutParameter(Database database, string parameterName)
{
database.AddParameter(dbCommand, parameterName, DbType.Int32,
ParameterDirection.ReturnValue, null,
DataRowVersion.Default, null);
}
public void AddInParameter(Database database, object value, string parameterName)
{
database.AddParameter(dbCommand, parameterName,
DbType.String, ParameterDirection.Input,
null, DataRowVersion.Default, value);
}
/// <summary>
/// 执行存储过程，获得存储过程的返回值[注:必须在目标存储过程中设置return返回值,否则始终返回0]
/// </summary>
/// <param name="dc">存储过程语句</param>
/// <param name="db">操作目标数据库</param>
/// <returns></returns>
public static int RunProcedureWithResult(DbCommand dc, Database db)
{
int result = 0;
try
{
PrepareCommand(ref dc, db);
db.AddParameter(dc, "ReturnValue", DbType.Int32, ParameterDirection.ReturnValue, string.Empty, DataRowVersion.Default, null);
db.ExecuteNonQuery(dc);
result = (int)dc.Parameters[db.BuildParameterName("ReturnValue")].Value;
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
return result;
}
/// <summary>
/// Prepare a DbCommand object with its parameters
/// </summary>
/// <param name="pStoredProcedureName">Stored procedure name using dbo.Name sintax</param>
/// <param name="pParameters">Array of cParametroDatos</param>
/// <param name="pOutputParameters">List of DataParameters that are for output purpose</param>
/// <param name="pDbAccess">Database Object</param>
/// <returns>Prepared DbCommand</returns>
protected virtual DbCommand PrepareCommand(string pStoredProcedureName, IEnumerable<cParametroDatos> pParameters, out IList<cParametroDatos> pOutputParameters, out Database pDbAccess)
{
pDbAccess = GetDatabase();
DbCommand command = pDbAccess.GetStoredProcCommand(pStoredProcedureName);
pOutputParameters = new List<cParametroDatos>();
pOutputParameters.Add(new cParametroDatos(RETURN_VALUE, DbType.Int32, ParameterDirection.ReturnValue, 0));
SqlParameter param = new SqlParameter(RETURN_VALUE, SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
foreach (cParametroDatos parameter in pParameters)
{
pDbAccess.AddParameter(command, parameter.ParameterName, parameter.DbType, parameter.Direction, parameter.ParameterName, DataRowVersion.Current, parameter.Value);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
pOutputParameters.Add(parameter);
}
}
return command;
}
