存储过程

http://www.cnblogs.com/mgod/archive/2009/04/14/1258585.html

 

 

 --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("<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,false, 0, 0, string.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 
                //        from SysBaseData
                //    )
                //    select * from SysDataBaseList where rownumbers between (@PageIndex-1)*@PageSize+1 and (@PageIndex-1)*@PageSize+@PageSize

                //END
                CMD.CommandText = "UP_GetRecordByPage";
                SqlDataAdapter DA2 = new SqlDataAdapter();
                SqlParameter[] parameters4 = {
                    new SqlParameter("@PageSize",SqlDbType.Int),
                    new SqlParameter("@PageIndex",SqlDbType.Int),
                    new SqlParameter("@NCount",SqlDbType.Int, 4, ParameterDirection.Output,false, 0, 0, string.Empty, DataRowVersion.Default, null)
                    };
                parameters4[0].Value = 5;
                parameters4[1].Value = 2;

                foreach (SqlParameter parameter in parameters4)
                {
                    CMD.Parameters.Add(parameter);
                }
                DA2.SelectCommand = CMD;
                DataSet DS2 = new DataSet();
                int n2 = DA2.Fill(DS2);
                Response.Write("有输入输出参数获得数据集:" + n2 + "一共" + CMD.Parameters[2].Value + "行");
                GridView1.DataSource = DS2;
                GridView1.DataBind();
           }