Code --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(); } |