今天用了用存储过程，写了几个简单的例子
今天用了用存储过程，写了几个简单的例子
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();
}
