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