1.调用具有输入参数和返回结果的存储过程
-------------------建表------------------
CREATE TABLE [tab_cJ] (
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[name] [varchar] (50) ,
[age] [int] NULL ,
[info] [varchar] (200)
)
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[name] [varchar] (50) ,
[age] [int] NULL ,
[info] [varchar] (200)
)
------------------建存储过程-------------------------
CREATE PROCEDURE QueryInfoByName
@name varchar(50),
@age int
AS
select info from tab_cj where [name]=@name and age=@age
GO
@name varchar(50),
@age int
AS
select info from tab_cj where [name]=@name and age=@age
GO
--------------------调用-------------------------------
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = "QueryInfoByName"; //存储过程名称
//对应存储过程QueryInfoByName的第一个参数@name
SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50);
//指定参数@name要转入的值
parName.Value = "aa";
//对应存储过程QueryInfoByName的第二个参数@age
SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int);
//指定参数@age要转入的值
parAge.Value = 12;
//这一步非常重要，一定将设置好的两个参数类型添加到Command对象的参数集合里
cmd.Parameters.Add(parName);
cmd.Parameters.Add(parAge);
//方式一，查询回来的结果需要显示在DataGrid之类的控件上
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
//方式二，按单个值读取
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Response.Write(reader.GetString(0));
}
}
}
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = "QueryInfoByName"; //存储过程名称
//对应存储过程QueryInfoByName的第一个参数@name
SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50);
//指定参数@name要转入的值
parName.Value = "aa";
//对应存储过程QueryInfoByName的第二个参数@age
SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int);
//指定参数@age要转入的值
parAge.Value = 12;
//这一步非常重要，一定将设置好的两个参数类型添加到Command对象的参数集合里
cmd.Parameters.Add(parName);
cmd.Parameters.Add(parAge);
//方式一，查询回来的结果需要显示在DataGrid之类的控件上
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
//方式二，按单个值读取
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Response.Write(reader.GetString(0));
}
}
}
2.调用具有输出参数的存储过程
------------------建存储过程-------------------------
create procedure getAge
(
@name varchar(50),
@age int output
)
as
select @age=age from tab_cJ where [name]=@name
(
@name varchar(50),
@age int output
)
as
select @age=age from tab_cJ where [name]=@name
--------------------调用-------------------------------
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = "getAge"; //存储过程名称
SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50);
parName.Value = "aa";
//对应存储过程getAge的输出参数@age
SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int);
parAge.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parName);
cmd.Parameters.Add(parAge);
try
{
conn.Open();
cmd.ExecuteNonQuery();
int i = int.Parse(parAge.Value.ToString());
Response.Write(i.ToString());
conn.Close();
}
catch(Exception ex)
{
Response.Write(ex.ToString());
}
}
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
cmd.CommandText = "getAge"; //存储过程名称
SqlParameter parName = new SqlParameter("@name", SqlDbType.VarChar, 50);
parName.Value = "aa";
//对应存储过程getAge的输出参数@age
SqlParameter parAge = new SqlParameter("@age", SqlDbType.Int);
parAge.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parName);
cmd.Parameters.Add(parAge);
try
{
conn.Open();
cmd.ExecuteNonQuery();
int i = int.Parse(parAge.Value.ToString());
Response.Write(i.ToString());
conn.Close();
}
catch(Exception ex)
{
Response.Write(ex.ToString());
}
}
原文：https://www.cnblogs.com/wl-blog/p/13179365.html