使用存储过程执行多个查询和输出数据Output

存儲過程 

Create   Procedure   MoreTable  
  As  
  Select   User_Name   From   Physical_Users  
  Select   Order_Number   From   ProductOrders   

 

cs代碼

DataSet   ds   =   new   DataSet();  
  SqlConnection   conn   =   new   SqlConnection(ConnectionString);  
  SqlCommand   cmd   =   new   SqlCommand();  
  cmd.CommandType   =   CommandType.StoredProcedure;  
  cmd.CommandText   =   "MoreTable";  
  cmd.Connection   =   conn;  
  SqlDataAdapter   sdp   =   new   SqlDataAdapter(cmd);  
  sdp.Fill(ds);  
   
  GridView1.DataSource   =   ds.Tables[0];  
  GridView2.DataSource   =   ds.Tables[1];  
  GridView1.DataBind();  
  GridView2.DataBind();

 

  用ds.tables.count来访问这个dataset中有多少个表

 

使用Output参数时

 

 ALTER proc [dbo].[proc_SelectGoodsOutPut]
(
    @UserID int,
    @TotalCount int output,
    @TotalMoney float output
)
as
    --select [Order].[ID] OrderID,T_GoodsInfo.GoodsName GName,T_GoodsInfo.GoodsPrice GPrice,[Order].Num GNum,GoodsPrice*Num TotlePrice
    select [Order].[ID] keyID,T_GoodsInfo.GoodsName GName,T_GoodsInfo.GoodsPrice GPrice,[Order].Num GNum,GoodsPrice*Num TotlePrice
    from T_GoodsInfo,[Order]
    where T_GoodsInfo.GoodsID=[Order].GoodsID
    and [Order].UserID=@UserID

    select @TotalCount=sum(Num),@TotalMoney=sum(GoodsPrice*Num)
    from [Order],T_GoodsInfo
    where T_GoodsInfo.GoodsID=[Order].GoodsID
    and [Order].UserID=@UserID 
 

在CS文件中

 

 string strSqlSelectGoods = "proc_SelectGoodsOutPut"; 
            SqlParameter[] paraSelectGoods ={
                                           new SqlParameter("@UserID", DbType.Int32),
                                           new SqlParameter("@TotalCount", DbType.Int32),
                                           new SqlParameter("@TotalMoney",DbType.Single)
                                       };
            paraSelectGoods[0].Value = iUserID;
            paraSelectGoods[1].Direction = ParameterDirection.Output;
            paraSelectGoods[2].Direction = ParameterDirection.Output;
            DataSet ds = new DataSet();

            sqlHelper help = new sqlHelper();
            ds = help.doExceutForDataset(strSqlSelectGoods, paraSelectGoods, CommandType.StoredProcedure);
            
            GV_GoodsCar.DataSource = ds;
            GV_GoodsCar.DataBind();            
            lbGoosdCount.Text = paraSelectGoods[1].Value.ToString();
            lbTotalMoney.Text = paraSelectGoods[2].Value.ToString();

 

Technorati 标签: SQL,存储过程