Question

ADO.net如何调用带参数的存储过程

在数据库中有book 这张表,建立了一个存储过程

Create proc proc_select
@count int output
As

Select @count=count(*) from book

现在, 新建了一个控制台程序,是想在ado.net里面调用带输出参数的存储过程.
代码如下:

Sqlconnecticon=new Sqlconnection(“server=.;uid=sa;pwd=123;database=MyTest”);
Sqlcommand cmd=new Sqlcommand(“proc_select”,con);
cmd.commandType=commandType.storedprocedure;
cmd.parameters.add(@Count,sqldbtype.int);
cmd.parameters= parameterdirection.output;

con.open();
cmd.executenonquery();
con.close();
tmpid = (int)@count.value;

可是最后运行的结果，却是报异常．请帮忙看一下代码，是哪里的语法出了问题．

Answer 1

cmd.parameters.add(@Count,sqldbtype.int); ---错误，@Count加双引
cmd.Parameters.Add("@Count", SqlDbType.Int);
cmd.parameters= parameterdirection.output; --错误parameters是参数集合。非参数属性
cmd.Parameters["@Count" ].Direction = ParameterDirection.Output;

con.open();
cmd.executenonquery(); --错误：大小写。。失误？
con.close();
tmpid = (int)@count.value;--错误
tmpid = (int)cmd.Parameters["@Count"].value;

Answer 2

修改两句代码:

cmd.parameters.add("@Count",sqldbtype.int);

tmpid = (int)cmd.parameters["@Count"].Value;

这样就可以了!

例子如下:

SqlConnection conDB = new SqlConnection(linkDB);
conDB.Open();
SqlCommand cmdDB = new SqlCommand("SelectQuestion", conDB);
cmdDB.CommandType = CommandType.StoredProcedure;
cmdDB.Parameters.Add("@Link", SqlDbType.VarChar).Value = link;
cmdDB.Parameters.Add("@typeID", SqlDbType.Int).Value = typeID;
cmdDB.Parameters.Add("@questionID", SqlDbType.Int);

cmdDB.Parameters["@questionID"].Direction = ParameterDirection.Output;
cmdDB.ExecuteNonQuery();
conDB.Close();
int questionID = (int)cmdDB.Parameters["@questionID"].Value;
return questionID;

Answer 3

tring connString = "Data Source = localhost; Initial Catalog = hkjc;User ID = sa;Pwd = 123";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("GetManagement", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@management", SqlDbType.VarChar, 50).Value = managementname;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt=new DataTable();
sda.Fill(dt);
cmd.Dispose();
cmd.Clone();
conn.Dispose();
conn.Close();
return dt;

注：GetManagement为存储过程名称 @management为参数名称