public static DataTable GetDataSet(string proc)
{
SqlConnection conn = Con();
SqlCommand cmd = new SqlCommand(proc, conn);
cmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
conn.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
conn.Close();
return ds.Tables[0];
}此函数用来调用存储过程返回结果集,但是不能带参数。
就算 cmd.Parameters.Add 加上参数,需要固定参数的个数。
由于调用这个函数的地方比较多,存储过程的参数也不一样,
想在类写个通用的函数能行吗? 求解?
{
....
}
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);
}