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