就是在C#中调用存储过程时,不知道怎样将外部参数传入存储过程。在网上查到关于调用存储过程的方法时,看到也没有将怎样将外部传输传入存储过程。贴代码看看:exeprocedure(string arg1,string arg2,string arg3)
{
sqlconnection conn=new sqlconnection(“connectionstring”);
sqldataadapter da = new sqldataadapter();
da.selectcommand = new sqlcommand();
da.selectcommand.connection = conn;
da.selectcommand.commandtext = "nameofprocedure(’para1’,’para2’,para3)";///这里的para1,para2和para3是将字符串,是不是将arg1,arg2和arg3直接带入para1,para2和para3处呢???
da.selectcommand.commandtype = commandtype.storedprocedure;
}上面的说法是否正确,不正确要怎样写才正确呢?希望提出具体代码
{
sqlconnection conn=new sqlconnection(“connectionstring”);
sqldataadapter da = new sqldataadapter();
da.selectcommand = new sqlcommand();
da.selectcommand.connection = conn;
da.selectcommand.commandtext = "nameofprocedure(’para1’,’para2’,para3)";///这里的para1,para2和para3是将字符串,是不是将arg1,arg2和arg3直接带入para1,para2和para3处呢???
da.selectcommand.commandtype = commandtype.storedprocedure;
}上面的说法是否正确,不正确要怎样写才正确呢?希望提出具体代码
SqlCommand cmd = new SqlCommand("DEleteRecord", cn);
cmd.CommandType = CommandType.StoredProcedure; //这里说明调用的是存储过程
cmd.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(ID); //这里给参数赋值,有几个参数就要加几次
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServer"].ToString()))
{
conn.Open();
SqlCommand MyCommand = new SqlCommand("MYSQL", conn);
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
MyCommand.Parameters["@a"].Value = 20;
MyCommand.Parameters.Add(new SqlParameter("@b", SqlDbType.Int));
MyCommand.Parameters["@b"].Direction = ParameterDirection.Output;
MyCommand.ExecuteNonQuery();
Response.Write(MyCommand.Parameters["@b"].Value.ToString());
}
public int checkLogin(string userName ,string password)
{
//LoginCheck表示存储过程名称,connection表示数据库连接
SqlCommand scmd = new SqlCommand("LoginCheck",connection);
SqlDataAdapter sd = new SqlDataAdapter();
//添加存储过程的参数名称和输入 @name
SqlParameter para1 = new SqlParameter("@name",SqlDbType.VarChar,16);
para1.Direction = ParameterDirection.Input;
para1.Value = userName;
sd.SelectCommand.Parameters.Add(para1);
//添加存储过程参数输入 @password
SqlParameter para2 = new SqlParameter("@name",SqlDbType.VarChar,16);
para2.Direction = ParameterDirection.Input;
para2.Value = password;
sd.SelectCommand.Parameters.Add(para2);
//添加存储过程返回值设置 @returnNo
int returnNo = 0;
SqlParameter para3 = new SqlParameter("@returnNo",SqlDbType.Int,1);
para3.Direction = ParameterDirection.Output;
para3.Value = returnNo;
sd.SelectCommand.Parameters.Add(para3); //返回存储过程的返回值
return returnNo;
}
para1.Direction = ParameterDirection.Input;
para1.Value = userName;
sd.SelectCommand.Parameters.Add(para1);
//添加存储过程参数输入 @password
SqlParameter para2 = new SqlParameter("@name",SqlDbType.VarChar,16);有问题,存储过程的参数必须和数据库的参数保持一致。你这里怎么有两个@Name??
SqlCommand scmd = new SqlCommand("LoginCheck",connection);scmd.CommandType = CommandType.StoredProcedure;
要指明执行的是存储过程
这个scmd没有执行过。
另外SqlDataAdapter sd = new SqlDataAdapter();
这个做了什么?
public int checkLogin(string userName ,string password)
{
//LoginCheck表示存储过程名称,connection表示数据库连接
SqlCommand scmd = new SqlCommand("LoginCheck",connection);//如果这代码没有执行我怎么知道是执行的那个存储过程呢?
SqlDataAdapter sd = new SqlDataAdapter();
//添加存储过程的参数名称和输入 @name
SqlParameter para1 = new SqlParameter("@name",SqlDbType.VarChar,16);
para1.Direction = ParameterDirection.Input;
para1.Value = userName;
sd.SelectCommand.Parameters.Add(para1);
//添加存储过程参数输入 @password
SqlParameter para2 = new SqlParameter("@password",SqlDbType.VarChar,16);
para2.Direction = ParameterDirection.Input;
para2.Value = password;
sd.SelectCommand.Parameters.Add(para2);
//添加存储过程返回值设置 @returnNo
int returnNo = 0;
SqlParameter para3 = new SqlParameter("@returnNo",SqlDbType.Int,1);
para3.Direction = ParameterDirection.Output;
para3.Value = returnNo;
sd.SelectCommand.Parameters.Add(para3); //返回存储过程的返回值
return returnNo;
}
存储过程中不是都有参数吗!在页面新建Paramer参数,把值传给存储过程!!!
scmd.CommandType = CommandType.StoredProcedure;
//如果只是执行,怎么知道是执行的那个存储过程呢?
如果数据库中有n个存储过程,那么n个存储过程中有n-m个参数是一样,那么又是怎样识别是那个存储过程的呢?
这里如果不说明LoginCheck是存储过程,会把这个当做sql语句来执行。LoginCheck是sql语句吗?
如果你这样写,可以不指明是存储过程。SqlCommand scmd = new SqlCommand("select * from test where id=@id",connection);
scmd.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(ID); //这个跟存储过程的给参数赋值是一样的//这个语句就不是存储过程,是sql语句
也就说存储过程还是要在C#代码中用sql代码来查询了。然后执行查询。
as
.....
存储过程的名字在数据库里是不能重复的
@name varchar(16),@password varchar(6),@returnNo int output
as
begin
if exists(select 1 from enterpriseUserInfo where enterpUserName = @name and enterpUserPassword= @password)
begin
set @returnNo=1
else if exists (select 1 from personalUserInfo where userName = @name and userPassword =@password)
begin
set @returnNo=2
else if exists(select 1 from partTimeManInfo where partTimerName=@name and partPassword = @password)
begin
set @returnNo =3
else
begin
set @returnNo=0
end
/// <summary>
/// 查询语句
/// </summary>
/// <param name="SelectStr">查询字符串</param>
/// <param name="isProc">是否是存储过程</param>
/// <param name="sp">输入参数</param>
/// <returns>返回DATATABLE</returns>
public DataTable GetTable(string SelectStr, bool isProc, SqlParameter[] sp)
{
SqlConnection conn = new SqlConnection(connStr);
conn.ConnectionString = connStr;
conn.Open();
SqlCommand com = new SqlCommand(SelectStr, conn);
if (isProc) { com.CommandType = CommandType.StoredProcedure; }
if (sp != null) { com.Parameters.AddRange(sp); }
SqlDataAdapter sda = new SqlDataAdapter(com);
DataTable dtab = new DataTable();
sda.Fill(dtab);
if (conn != null)
{
conn.Close();
conn.Dispose();
}
return dtab;
}
执行存储过程的时候直接
/// <summary>
/// 查看所有结帐记录
/// </summary>
/// <returns></returns>
public DataTable GetAllsends()
{
string sql = "Exec 存储过程名";
SqlParameter[] pa ={new SqlParameter("@变量",变量), new SqlParameter("@变量",变量)}//有几个参数就New几个SqlParameter("@变量",变量)对象
return this.GetTable(sql, true, pa);
}
要加上这句
scmd.CommandType = CommandType.StoredProcedure;
SqlCommand scmd = new SqlCommand("LoginCheck",connection);//这句的"LoginCheck"是存储过程的名字
你这样写每次都要实例化一次,太麻烦了!定义个数组
SqlCommand scmd = new SqlCommand("LoginCheck",connection);//如果这代码没有执行我怎么知道是执行的那个存储过程呢?
SqlDataAdapter sd = new SqlDataAdapter();
//添加存储过程的参数名称和输入 @name
SqlParameter para1 = new SqlParameter("@name",SqlDbType.VarChar,16);
para1.Direction = ParameterDirection.Input;
para1.Value = userName;
sd.SelectCommand.Parameters.Add(para1);
//添加存储过程参数输入 @password
SqlParameter para2 = new SqlParameter("@password",SqlDbType.VarChar,16);
para2.Direction = ParameterDirection.Input;
para2.Value = password;
sd.SelectCommand.Parameters.Add(para2);
//添加存储过程返回值设置 @returnNo
int returnNo = 0;
SqlParameter para3 = new SqlParameter("@returnNo",SqlDbType.Int,1);
para3.Direction = ParameterDirection.Output;
para3.Value = returnNo;
sd.SelectCommand.Parameters.Add(para3);
//返回存储过程的返回值
return returnNo;改成这样的
sqlparameter[] para = new sqlparameter[3];
para[0] = new sqlparameter("@name",...)
para[1]=...
public int LoginCheckProc(string userName,string password)
{
Sqlparameter para = new SqlParameter[3];
para[0] = new SqlParameter("@name",SqlDbType.varcahr,16);
para[1] = new SqlParameter("@password",SqlDbType,varchar.16);
para[3] = new SqlParameter("@returnNo",SqlDbType.int,1);para[0].Diraction = ParameterDiraction.Input;
para[1].Diraction = ParameterDiraction.Input;
para[2].Diraction = ParameterDiraction.Output;para[0].Value = userName;
para[1].Value =password;
int returnNo = Convert.ToInt32(para[3].Value.toString());
return returnNo;}
/// <summary>
/// 查询语句
/// </summary>
/// <param name="SelectStr">查询字符串</param>
/// <param name="isProc">是否是存储过程</param>
/// <param name="sp">输入参数</param>
/// <returns>返回DATATABLE</returns>
public DataTable GetTable(string SelectStr, bool isProc, SqlParameter[] sp)
{
SqlConnection conn = new SqlConnection(connStr);
conn.ConnectionString = connStr;
conn.Open();
SqlCommand com = new SqlCommand(SelectStr, conn);
if (isProc) { com.CommandType = CommandType.StoredProcedure; }
if (sp != null) { com.Parameters.AddRange(sp); }
SqlDataAdapter sda = new SqlDataAdapter(com);
DataTable dtab = new DataTable();
sda.Fill(dtab);
if (conn != null)
{
conn.Close();
conn.Dispose();
}
return dtab;
}
执行存储过程的时候直接
/// <summary>
/// 查看所有结帐记录
/// </summary>
/// <returns></returns>
public DataTable GetAllsends()
{
string sql = "Exec 存储过程名";
SqlParameter[] pa ={new SqlParameter("@变量",变量), new SqlParameter("@变量",变量)}//有几个参数就New几个SqlParameter("@变量",变量)对象
return this.GetTable(sql, true, pa);
}
貌似能用哈,我还要后面实验看看哈。后面有什么意见哦?
public int checkLoginProc(string userName ,string password)
{
string execProc = "EXEC LoginCheckProc";
SqlCommand scmd = new SqlCommand(execProc,connection);
scmd.CommandType = CommandType.StoredProcedure;
// scmd.Parameters.Add("@name",SqlDbType.VarChar,16);
//scmd.Parameters.Add("@password",SqlDbType.VarChar,16);
SqlParameter[] para = new SqlParameter[3];
para[0] = new SqlParameter("@name",SqlDbType.VarChar,16);
para[1] = new SqlParameter("@password",SqlDbType.VarChar,16);
para[2] = new SqlParameter("@returnNo",SqlDbType.Int,1);
para[0].Direction = ParameterDirection.Input;
para[1].Direction = ParameterDirection.Input;
para[2].Direction = ParameterDirection.InputOutput;
//调用外部参数
para[0].Value = userName;
para[1].Value = password;
int returnNo=0;
returnNo = Convert.ToInt32(para[2].Value);
//返回存储过程返回值
return returnNo;
上面代码不能将参数传入,大家帮我看看问题出在哪里?