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

分类:数据库| 发布:camnprbubuol| 查看: | 发表时间: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,
                
false00string.Empty, DataRowVersion.Default, null));
                CMD.ExecuteScalar();
                Response.Write(
"没有输入参数调用有返回参数:"+CMD.Parameters["ReturnValue"].Value);
                Response.Write(
"<br>");

                
//有一个传入参数和返回参数
                
//------------------------------------
                
//--用途:是否已经存在 
                
//--项目名称:
                
//--说明:
                
//--时间: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,false00string.Empty, DataRowVersion.Default, null));
                
int j = CMD.ExecuteNonQuery();
                Response.Write(
"有一个传入参数和返回参数:"+CMD.Parameters["ReturnValue"].Value);
                Response.Write(
"<br>");
                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(
"<br>");

                
//有多个转入参数没有近回值
                
//------------------------------------
                
//--用途:修改一条记录 
                
//--项目名称:
                
//--说明:
                
//--时间: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(
"<br>");


                
//没有输入参数获得数据集
                
//------------------------------------
                
//--用途:查询记录信息 
                
//--项目名称:
                
//--说明:
                
//--时间: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(
"<br>");
                
//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据说看到好文章不转的人,服务器容易宕机
原创文章如转载,请注明:转载自郑州网建-前端开发 http://camnpr.com/
本文链接:http://camnpr.com/archives/167.html