如何调用存储过程

 /// <summary>
  /// 执行存储过程
  /// </summary>
  /// <param name="procName">存储过程名称</param>
  /// <param name="prams">存储过程所需参数</param>
  /// <returns>返回存储过程返回值</returns>
  public int RunProc(string procName, SqlParameter[] prams)
  {
   SqlCommand cmd = CreateCommand(procName, prams);
   try
   {
    cmd.ExecuteNonQuery();
   }
   catch(Exception ex)
   {
    SystemError.SystemLog(ex.Message);
   }
   Close();
   return (int)cmd.Parameters["ReturnValue"].Value;
  } 

 /// <summary>
  /// 生成存储过程参数
  /// </summary>
  /// <param name="ParamName">存储过程名称</param>
  /// <param name="DbType">参数类型</param>
  /// <param name="Size">参数大小</param>
  /// <param name="Direction">参数方向</param>
  /// <param name="Value">参数值</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
  {
   SqlParameter param;

   ///当参数大小为0时，不使用该参数大小值
   if(Size > 0)
   {
    param = new SqlParameter(ParamName, DbType, Size);
   }
   else
   {
    ///当参数大小为0时，不使用该参数大小值
    param = new SqlParameter(ParamName, DbType);
   }

   ///创建输出类型的参数
   param.Direction = Direction;
   if (!(Direction == ParameterDirection.Output && Value == null))
   {
    param.Value = Value;
   }

   ///返回创建的参数
   return param;
  }

  /// <summary>
  /// 传入输入参数
  /// </summary>
  /// <param name="ParamName">存储过程名称</param>
  /// <param name="DbType">参数类型</param></param>
  /// <param name="Size">参数大小</param>
  /// <param name="Value">参数值</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int Size, object Value)
  {
   return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
  }  

  /// <summary>
  /// 传入返回值参数
  /// </summary>
  /// <param name="ParamName">存储过程名称</param>
  /// <param name="DbType">参数类型</param>
  /// <param name="Size">参数大小</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int Size)
  {
   return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null);
  }  

  /// <summary>
  /// 传入返回值参数
  /// </summary>
  /// <param name="ParamName">存储过程名称</param>
  /// <param name="DbType">参数类型</param>
  /// <param name="Size">参数大小</param>
  /// <returns>新的 parameter 对象</returns>
  public SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size)
  {
   return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
  }

        public SqlParameter CreateInParam(string p, SqlDbType sqlDbType, int sDisplayDot_Main)
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
}

 

相应的调用存储过程类

1.查询的存储过程：

    public class CheckUser
    {
        public SqlDataReader checkuser(String id)
        {
            Database db = new Database();
            SqlDataReader dr = null;
            SqlParameter[] param = {
            db.CreateInParam("@id",SqlDbType.VarChar,50,id)
           };
            db.RunProc("Pr_checkuser", param, out dr);

            //返回 dr
            return dr;
        }
    }

2.插入数据的存储过程：

public class AddHOUSING
    {
        public int sAddHOUSING(string sItemNO, string sModel,string sSupplier, string sRecorder, DateTime sCreatedate, int sInputQTY)
        {
            int nHOUSING = 0;
            Database db = new Database();
            //创建访问数据库的参数
            SqlParameter[] param ={
                db.CreateInParam("@ItemNO",SqlDbType.VarChar,300,sItemNO),
                db.CreateInParam("@Model",SqlDbType.VarChar,300,sModel),
                db.CreateInParam("@Supplier",SqlDbType.VarChar,300,sSupplier),
                db.CreateInParam("@Recorder",SqlDbType.VarChar,300,sRecorder),
                db.CreateInParam("@Createdate",SqlDbType.DateTime,8,sCreatedate),
                db.CreateInParam("@InputQTY",SqlDbType.Int,4,sInputQTY),
                db.CreateInParam("@DefectType",SqlDbType.VarChar,300,sDefectType)
            };
            nHOUSING = db.RunProc("pr_AddHOUSING", param);
            return(nHOUSING);
        }
    }

 

前台调用这个存储过程类的方法：

插入数据与查询数据只是返回值不同，前者是0和1，0代表插入成功，而后者返回值是所查询的datareader：      

          AddDIECAST adddiecast = new AddDIECAST(); //调用AddDIECAST类
                int sAddDIECAST = 0;
                DateTime NowTime = DateTime.Now;
                try
                {
                    sAddDIECAST = adddiecast.sAddDIECAST(ItemNO.Text.Trim(), Model.Text.Trim(),Supplier.Text.Trim(), Session["UserID"].ToString(), NowTime, Convert.ToInt32(InputQTY.Text), DefectType.SelectedValue.Trim());
                }
                catch (Exception ex)
                {
                    Response.Write("<script>alert(\"保存不成功！\")</script>");
                }

 

 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1037845