C#调用带参数和不带参数的存储过程实例

分类：数据库| 发布：camnprbubuol| 查看：1161 | 发表时间：2011/1/17

--T-SQL不带参数
if exists(select * from sysobjects where name=’SelectStu’)
drop proc SelectStu
GO
create proc SelectStu
as
select * from student
GO

--c#
SqlCommand cmd = new SqlCommand("SelectStu",con);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlDataReader dr = cmd.ExecuteReader();
 while(dr.Read())
 {
 Console.WriteLine(dr["LoginId"]);
 }

--T-SQL 带一个参数
if exists(select * from sysobjects where name=’SelectStu’)
drop proc SelectStu
GO
create proc SelectStu
@name varchar(50)
as
select * from student where loginid=@name
GO

--C#
SqlCommand cmd = new SqlCommand("SelectStu",con);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@name", "LiDifei");
 SqlDataReader dr = cmd.ExecuteReader();
 while(dr.Read())
 {
 Console.WriteLine(dr["LoginId"]);
 }

--T-SQL 有返回值
if exists(select * from sysobjects where name=’SelectStu’)
drop proc SelectStu
GO
create proc SelectStu
@name varchar(50),
@id int output
as
select @id=studentid from student where loginid=@name
return @id
GO

--C#
SqlCommand cmd = new SqlCommand("SelectStu",con);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@name", "LiDifei");
 cmd.Parameters.Add("@id",SqlDbType.Int);
 cmd.Parameters["@id"].Direction = ParameterDirection.Output;
 cmd.ExecuteScalar();
 Console.WriteLine(cmd.Parameters["@id"].Value);

-------------------------------------------------------------------------------------

//以前写过的一个调用存储过程的方法

public static int regInsert(string Pwd, int Friend, string NickName, int FaceID, string Sex, int Age, string Name, int starId, int BloodTypeId)
 {
 try
 {
 SqlConnection conn = createConnection();
 conn.Open();
 SqlCommand cmd = new SqlCommand("proc_reg", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@id",SqlDbType.Int);
 cmd.Parameters["@id"].Direction = ParameterDirection.Output;
 cmd.Parameters.AddWithValue("@LoginPwd",Pwd);
 cmd.Parameters.AddWithValue("@FriendshipPolicyId",Friend);
 cmd.Parameters.AddWithValue("@NickName",NickName);
 cmd.Parameters.AddWithValue("@FaceID",FaceID);
 cmd.Parameters.AddWithValue("@Sex",Sex);
 cmd.Parameters.AddWithValue("@Age",Age);
 cmd.Parameters.AddWithValue("@Name",Name);
 cmd.Parameters.AddWithValue("@starId",starId);
 cmd.Parameters.AddWithValue("@BloodTypeId", BloodTypeId);



 cmd.ExecuteNonQuery();
 return (int)cmd.Parameters["@id"].Value;


 }
 catch (SqlException ex)
 {
 Console.WriteLine(ex.Message);
 }
 return 0;

 }

-------------------------------------------------------------------------------

//从网上摘抄

string ConnectionString = "server=.;database=PubData;uid=sa;pwd=123456";
 using (SqlConnection Conn = new SqlConnection(ConnectionString))
 {

 SqlCommand CMD = new SqlCommand();
 CMD.CommandType = CommandType.StoredProcedure;
 CMD.Connection = Conn;
 //没有输入参数调用有返回参数
 //存储过程如下
 //------------------------------------
 //--用途：得到最大ID
 //--项目名称：
 //--说明：
 //--时间：2007-12-6 17:15:27
 //------------------------------------
 //ALTER PROCEDURE SysBaseData_GetMaxId
 //AS
 //DECLARE @TempID int
 //SELECT @TempID = max([BaseDataID])+1 FROM SysBaseData
 //IF @TempID IS NULL
 // RETURN 1
 //ELSE
 // RETURN @TempID

 CMD.CommandText = "SysBaseData_GetMaxId";
 Conn.Open();
 CMD.Parameters.Add(new SqlParameter("ReturnValue",
 SqlDbType.Int, 4, ParameterDirection.ReturnValue,
 false, 0, 0, string.Empty, DataRowVersion.Default, null));
 CMD.ExecuteScalar();
 Response.Write("没有输入参数调用有返回参数:"+CMD.Parameters["ReturnValue"].Value);
 Response.Write(" ");

 //有一个传入参数和返回参数
 //------------------------------------
 //--用途：是否已经存在
 //--项目名称：
 //--说明：
 //--时间：2007-12-6 17:15:27
 //------------------------------------
 //ALTER PROCEDURE SysBaseData_Exists
 //@BaseDataID int
 //AS
 // DECLARE @TempID int
 // SELECT @TempID = count(1) FROM SysBaseData WHERE [BaseDataID] = @BaseDataID
 // IF @TempID = 0
 // RETURN 0
 // ELSE
 // RETURN 1

 CMD.CommandText = "SysBaseData_Exists";
 SqlParameter[] parameters = { new SqlParameter("@BaseDataID", SqlDbType.Int) };
 parameters[0].Value = 22;
 CMD.Parameters.Add(parameters[0]);
 CMD.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int, 4, ParameterDirection.ReturnValue,false, 0, 0, string.Empty, DataRowVersion.Default, null));
 int j = CMD.ExecuteNonQuery();
 Response.Write("有一个传入参数和返回参数:"+CMD.Parameters["ReturnValue"].Value);
 Response.Write(" ");
 CMD.Parameters.Clear();

 //有一个传入参数没有返回值
 //------------------------------------
 //--用途：删除一条记录
 //--项目名称：
 //--说明：
 //--时间：2007-12-6 17:15:27
 //------------------------------------
 //ALTER PROCEDURE SysBaseData_Delete
 //@BaseDataID int
 // AS
 // DELETE SysBaseData
 // WHERE [BaseDataID] = @BaseDataID
 CMD.CommandText = "SysBaseData_Delete";
 SqlParameter[] parameters2 = { new SqlParameter("@BaseDataID",SqlDbType.Int) };
 parameters2[0].Value = 29;
 CMD.Parameters.Add(parameters2[0]);
 int k = CMD.ExecuteNonQuery();
 Response.Write("有一个传入参数没有返回值:" + k);
 CMD.Parameters.Clear();
 Response.Write(" ");

 //有多个转入参数没有近回值
 //------------------------------------
 //--用途：修改一条记录
 //--项目名称：
 //--说明：
 //--时间：2007-12-6 17:15:27
 //------------------------------------
 //ALTER PROCEDURE SysBaseData_Update
 //@BaseDataID int,
 //@BaseDataCode char(10),
 //@BaseDataName varchar(30),
 //@ParentCode char(10),
 //@Description varchar(100),
 //@LevelType int,
 //@VaildType char(1),
 //@PathCode varchar(100),
 //@SerialNumberID int,
 //@msrepl_tran_version uniqueidentifier
 // AS
 // UPDATE SysBaseData SET
 // [BaseDataCode] = @BaseDataCode,[BaseDataName] = @BaseDataName,[ParentCode] = @ParentCode,[Description] = @Description,[LevelType] = @LevelType,[VaildType] = @VaildType,[PathCode] = @PathCode,[SerialNumberID] = @SerialNumberID,[msrepl_tran_version] = @msrepl_tran_version
 // WHERE [BaseDataID] = @BaseDataID
 CMD.CommandText = "SysBaseData_Update";
 SqlParameter[] parameters3 = {
 new SqlParameter("@BaseDataID",SqlDbType.Int),
 new SqlParameter("@BaseDataCode",SqlDbType.VarChar),
 new SqlParameter("@BaseDataName",SqlDbType.VarChar),
 new SqlParameter("@ParentCode",SqlDbType.VarChar),
 new SqlParameter("@Description",SqlDbType.VarChar),
 new SqlParameter("@LevelType",SqlDbType.Int),
 new SqlParameter("@VaildType",SqlDbType.VarChar),
 new SqlParameter("@PathCode",SqlDbType.VarChar),
 new SqlParameter("@SerialNumberID",SqlDbType.Int)
 };
 parameters3[0].Value = 28;
 parameters3[1].Value = "DCCK02";
 parameters3[2].Value = "中秋节0";
 parameters3[3].Value = "DCCK";
 parameters3[4].Value = "";
 parameters3[5].Value = 3;
 parameters3[6].Value = "0";
 parameters3[7].Value = "";
 parameters3[8].Value = 0;
 foreach(SqlParameter parameter in parameters3)
 {
 CMD.Parameters.Add(parameter);
 }
 int m = CMD.ExecuteNonQuery();
 Response.Write("有多个转入参数没有近回值:" + m);
 CMD.Parameters.Clear();
 Response.Write(" ");

 //没有输入参数获得数据集
 //------------------------------------
 //--用途：查询记录信息
 //--项目名称：
 //--说明：
 //--时间：2007-12-6 17:15:27
 //------------------------------------
 //ALTER PROCEDURE UP_SysBaseData_GetList
 // AS
 // SELECT
 // [BaseDataID],[BaseDataCode],[BaseDataName],[ParentCode],[Description],[LevelType],[VaildType],[PathCode],[SerialNumberID],[msrepl_tran_version]
 // FROM SysBaseData
 CMD.CommandText = "UP_SysBaseData_GetList";
 SqlDataAdapter DA = new SqlDataAdapter();
 DA.SelectCommand = CMD;
 DataSet DS = new DataSet();
 int n = DA.Fill(DS);
 Response.Write("没有输入参数获得数据集:" + n);
 Response.Write(" ");
 //GridView1.DataSource = DS;
 //GridView1.DataBind();

 //有输入输出参数获得数据集
 //-- =============================================
 //-- Author: <Author,,Name>
 //-- Create date: <Create Date,,>
 //-- Description: <Description,,>
 //-- =============================================
 //ALTER PROCEDURE [dbo].[UP_GetRecordByPage]
 // @PageSize int,
 // @PageIndex int,
 // @NCount int output
 //AS
 //declare @StarRow int
 //declare @EndRow int
 //select @NCount=count(BaseDataID) from SysBaseData
 //BEGIN
 // SET NOCOUNT ON;
 // with SysDataBaseList as
 // (
 // select row_number() over(order by BaseDataID desc) as rownumbers,
 // BaseDataID,BaseDataName,ParentCode
 &nbs

365据说看到好文章不转的人，服务器容易宕机！

标签：SQL Server