来教教新手！SQLHelper里执行存储过程的写法

[海盗色色]

执行存储过程 带参数 返回DataSet
public DataSet ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand sqlcom = new SqlCommand(sql, conn);
sqlcom.CommandType = CommandType.StoredProcedure;

foreach (System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = sqlcom;
DataSet ds = new DataSet();
da.Fill(ds);

conn.Close();
return ds;
}
这东西怎么用
两个参数是什么？
尤其是IDataParameter[] paramers这个东西
我前面要怎样给它赋值？
麻烦请给讲解一下
能给个实际的用法例子最好了
谢谢

[kkbac]

前面是一个sql语句,后面是一个数组比如 SqlParameter[]

[子夜__]

遍历添加参数啊。
DEMO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[InsertUsers]
(
    @UserName varchar(255),@Password varchar(255),@UserID int output
)
as
insert into Users(UserName,Password)values(@UserName,@Password)
set @UserID=@@Identity

调用

 using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            conn.Open();
            SqlCommand cmd = new SqlCommand("[InsertUsers]", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@UserName", TextBox1.Text));
            cmd.Parameters.Add(new SqlParameter("@Password", TextBox2.Text));
            cmd.Parameters.Add(new SqlParameter("@UserID", 0)).Direction = ParameterDirection.Output;
            int result = cmd.ExecuteNonQuery();
            Response.Write(result + " UserID：" + cmd.Parameters["@UserID"].Value);
        }

[呆子罗]

/// <summary>
/// SqlHelper执行存储过程通用方法:返回SqlDataReader对象
/// </summary>
/// <param name="proText">存储过程名字</param>
/// <param name="cmdType">查询类型</param>
/// <param name="pars">参数列表</param>
/// <returns>返回:SqlDataReader</returns>
public static SqlDataReader ExecProcdureReturnDataReader(string cmdText, CommandType cmdType, params SqlParameter[] pars)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (pars != null)
{
//遍历参数,添加到SqlCommand 对象子执行
foreach (SqlParameter parm in pars)
cmd.Parameters.Add(parm);//
}
return cmd.ExecuteReader();
}
//-----------------数据访问类里面调用上面的方法--------------
/// <summary>
/// 存储过程分页,全部数据
/// </summary>
/// <param name="c_cid">当前用户ID</param>
/// <param name="pageSize">每页显示行数</param>
/// <param name="pageIndex">页码,第n页</param>
/// <param name="c_sid">状态ID,[1表示正常],[2表示已删除]</param>
/// <returns></returns>
public static IList<MuCard> GetAllCardsByProc(int c_cid, int pageSize, int pageIndex,int c_sid)
{

string procText = ConfigurationManager.AppSettings["Pages"].ToString();//读取存储过程名
IList<MuCard> cardList = new List<MuCard>();
MuCard card = null;
SqlParameter[] pars = new SqlParameter[]
{
//这些参数都要对应数据库里面的参数,大小写一致
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@pageIndex",pageIndex),
new SqlParameter("@c_cid",c_cid),
new SqlParameter("@c_sid",c_sid)
};
//调用上面的方法
using (SqlDataReader dr = SqlHelper.ExecProcdureReturnDataReader(procText,CommandType.StoredProcedure,pars))
{
while (dr.Read())
{
card = new MuCard();
card.CID = Convert.ToInt32(dr[0]);
card.C_Name = dr[1].ToString();
card.C_ComName = dr[2].ToString();
card.C_Tel = dr[3].ToString();
card.C_Mobile = dr[4].ToString();
card.C_GID =Convert.ToInt32( dr[5]);
cardList.Add(card);
}
}
return cardList;
}

[呆子罗]

忘了标签

/// <summary>
  /// SqlHelper执行存储过程通用方法:返回SqlDataReader对象
  /// </summary>
  /// <param name="proText">存储过程名字</param>
  /// <param name="cmdType">查询类型</param>
  /// <param name="pars">参数列表</param>
  /// <returns>返回:SqlDataReader</returns>
  public static SqlDataReader ExecProcdureReturnDataReader(string cmdText, CommandType cmdType, params SqlParameter[] pars)
  {
  SqlCommand cmd = new SqlCommand();
  cmd.Connection = Connection;
  cmd.CommandType = cmdType;
  cmd.CommandText = cmdText;
  if (pars != null)
  {
  //遍历参数,添加到SqlCommand 对象子执行
  foreach (SqlParameter parm in pars)
  cmd.Parameters.Add(parm);//
  }
  return cmd.ExecuteReader();
  }
//-----------------数据访问类里面调用上面的方法--------------
 /// <summary>
  /// 存储过程分页,全部数据
  /// </summary>
  /// <param name="c_cid">当前用户ID</param>
  /// <param name="pageSize">每页显示行数</param>
  /// <param name="pageIndex">页码,第n页</param>
  /// <param name="c_sid">状态ID,[1表示正常],[2表示已删除]</param>
  /// <returns></returns>
  public static IList<MuCard> GetAllCardsByProc(int c_cid, int pageSize, int pageIndex,int c_sid)
  {
    
  string procText = ConfigurationManager.AppSettings["Pages"].ToString();//读取存储过程名
  IList<MuCard> cardList = new List<MuCard>();
  MuCard card = null;
  SqlParameter[] pars = new SqlParameter[]  
  {
  //这些参数都要对应数据库里面的参数,大小写一致
  new SqlParameter("@pageSize",pageSize),
  new SqlParameter("@pageIndex",pageIndex),
  new SqlParameter("@c_cid",c_cid),
  new SqlParameter("@c_sid",c_sid)
  };
  //调用上面的方法
  using (SqlDataReader dr = SqlHelper.ExecProcdureReturnDataReader(procText,CommandType.StoredProcedure,pars))
  {
  while (dr.Read())
  {
  card = new MuCard();
  card.CID = Convert.ToInt32(dr[0]);
  card.C_Name = dr[1].ToString();
  card.C_ComName = dr[2].ToString();
  card.C_Tel = dr[3].ToString();
  card.C_Mobile = dr[4].ToString();
  card.C_GID =Convert.ToInt32( dr[5]);
  cardList.Add(card);
  }
  }
  return cardList;
  }

[呆子罗]

存储过程

if exists (select * from sysobjects where name='proc_Pages')
drop procedure proc_Pages
go
create procedure proc_Pages
@pageSize int,--每页显示行数
@pageIndex int,--第N页
@c_cid int,--当前用户ID
@c_sid int--状态ID
as
begin
declare @sizes int,@index int
set @sizes=@pageSize
set @index=@pageIndex

select top (@sizes) CID,C_Name,C_ComName,C_Tel,C_Mobile,C_GID 
from MuCard 
where CID not in(select top (@sizes*(@index-1)) CID from MuCard where C_CID=@c_cid and C_SID=@c_sid order by CID desc) 
and C_CID=@c_cid and C_SID=@c_sid order by CID desc

end
go

[zhangmin0525]

学习 学习

[zgxiangwei]

第一个是带参的sql语句 也就是你要查询的sql语句 第二个是与sql语句对应的参数

[海盗色色]

还有没
前面要怎样写
然后怎样调用ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers)
最后怎样得到返回值
再请教

[ASPNETCHENGXU]

[Quote=引用 4 楼 zooen2011 的回复:]

忘了标签
C# code

/// <summary>
/// SqlHelper执行存储过程通用方法:返回SqlDataReader对象
/// </summary>
/// <param name="proText">存储过程名字</param>
/// <param name="cmdType">查询类型</param>
/// <param name="pars"……
[/Quote]
就是这样写

[qq290032431]

/// <summary>
       /// 根据收件人得到消息的总数
       /// </summary>
       /// <param name="PageIndex">分页的索引</param>
       /// <param name="PageSize">每页的大小</param>
       /// <param name="ToUser">收件人</param>
       /// <returns>返回一个数据集</returns>
       public DataSet GetList(int PageIndex, int PageSize, string ToUser)
       {
           SqlParameter[] parameters = {
					   new SqlParameter("@PageIndex", SqlDbType.Int,4),
                     new SqlParameter("@PageSize", SqlDbType.Int,4),
                      new SqlParameter("@ToUser", SqlDbType.NVarChar,50)};
           parameters[0].Value = PageIndex;
           parameters[1].Value = PageSize;
           parameters[2].Value = ToUser;

           DataSet ds = SqlHelper.ExecuteDataset(SqlConnectionString, CommandType.StoredProcedure, "up_DotBBS_t130dft_Mail_PageList", parameters);
          return ds;
       }

up_DotBBS_t130dft_Mail_PageList 就是数据库里的存储过程。

[海盗色色]

先是不是要这样

                             SqlParameter[] pars = new SqlParameter[]  
  {
  new SqlParameter("@sTableName","ac_TransBooks"),
  new SqlParameter("@sFldName","RecID"),
  new SqlParameter("@nAddAmount",1),
  new SqlParameter("@nCurrNum",0).Direction = ParameterDirection.Output //这里这样写报错
  };

然后调用DataSet ds = ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers)
最后得到返回值

代码上不会写啊
该怎么写？

[手可摘星辰]

                         SqlParameter[] pars = new SqlParameter[]  
  {
  new SqlParameter("sTableName",@sTableName),
  new SqlParameter("sFldName",@sFldName),
   };

这么写