C#中使用存储过程,C#分页存储过程

存储过程 相关文章：
《通用高效分页存储过程代码》
《一个高效的数据分页的存储过程 可以轻松应付百万数据》 千万数量级分页存储过程
存储过程编写经验和优化措施
使用SQL存储过程要特别注意的问题-注意顺序读取
在项目中该不该使用存储过程代替SQL语句?
Sql存储过程加密和解密
本站更多数据库相关文章请点击

存储过程示范
其中包括存储过程分页三种方法、作者自已写的储存过程分页、以及c#使用存储过程的示例

程序代码

/* 创建一个名为Get的储存过程
先定义两个参数 ,其中@count为输出参数 <script type="text/javascript">google_ad_client = "pub-4475724770859924";google_alternate_color = "FFBBE8";google_ad_width = 468;google_ad_height = 60;google_ad_format = "468x60_as";google_ad_type = "text_image";google_ad_channel ="9379930647";google_color_border = "F8F8F8";google_color_bg = "FFFFFF";google_color_link = "FF6FCF";google_color_url = "38B63C";google_color_text = "B3B3B3";</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>

sql语句中 第一句返回该表所有内容, 第二句 返回表的行数,并把值赋给输出参数
*/
Create PROCEDURE [Get] @name varchar (50),
@count int output
AS

Select * FROM Table1
Select @count=COUNT(*) FROM Table1 Where UserName = @name
GO
private void ProcedureRead()
{
conn = new SqlConnection(strConn);
cmd = new SqlCommand("Get", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value="呵呵";
cmd.Parameters.Add("@count", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
Response.Write(dr[0].ToString()+"<br>");
Response.Write("结束!");
dr.Close();
cmd.Connection.Close();
Response.Write("<script>alert("+cmd.Parameters["@count"].Value.ToString()+");</script>");
}

// 存储过程

程序代码

//创建新表
Create PROCEDURE [dbo].[NewTable]
AS
Create TABLE [dbo].[NewTables] (
[HitDate] [datetime] NOT NULL , /*不能为空*/
[TotalHits] [float] NULL /*可以为空*/
) ON [PRIMARY]
GO

//动态创建新表
Create PROCEDURE [dbo].[TestNew]
@TableName VARCHAR(100)
AS
declare @sql VARCHAR(100) /*定义内部变量*/
SET @sql = @TableName /*内部变量赋值时加上 SET*/
SET @sql = 'Create TABLE '+@sql+'(HitDate datetime NULL,TotalHits float NULL)' /*以字符串型式赋值给内部变量*/
EXEC (@sql) /*执行@sql变量内的sql语句*/
GO

// 更新
Create PROCEDURE [dbo].[testUpdate]
AS
declare @sql VARCHAR(100)
Update Table1 SET UserName = (Select UserName FROM Table1 Where UserInfo = '信息1')
Where UserName = '第一名字'
GO
// 以上语句中如果select子句返回不子一个值(或者说UserInfo不只一个值匹配'信息1'),将导制错误
// 不过可以用以下方法把select分开,在取得返回值后在给更新语句,这样如果有多个匹配值,Select
// 也只返回最后一个匹配字符的值
Create PROCEDURE [dbo].[testUpdate]
AS
declare @sql VARCHAR(100)
Select @sql = UserName FROM Table1 Where UserInfo = '信息1'
Update Table1 SET UserName = @sql
Where UserName = '第一名字'
GO

// 判断语句的示范

Create TABLE [dbo].[PageViews] (
[HitDate] [datetime] NULL ,
[TotalHits] [float] NULL
) ON [PRIMARY]
GO

Create TABLE [dbo].[Unique] (
[HitDate] [datetime] NOT NULL ,
[TotalHits] [float] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_ IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

Create PROCEDURE [dbo].[HitsTotal]
@todaysdate datetime
AS
DECLARE @TOTAL int
SET @TOTAL = (Select COUNT(*) FROM [PageViews] Where HitDate = @todaysdate)

IF @TOTAL > 0
BEGIN
Update PageViews SET TotalHits = (Select TotalHits FROM PageViews Where HitDate = @todaysdate)
Where HitDate = @todaysdate
END
ELSE
BEGIN
Insert INTO PageViews
(
HitDate,
TotalHits
)
VALUES
(
@todaysdate,
1
)
END
GO
SET QUOTED_ IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_ IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Create PROCEDURE [dbo].[HitsUnique]
@todaysdate datetime

AS
DECLARE @TOTAL int

SET @TOTAL = (Select COUNT(*) FROM [Unique] Where HitDate = @todaysdate)

IF @TOTAL > 0
BEGIN
Update [Unique]
SET
TotalHits =
((Select TotalHits FROM [Unique] Where HitDate = @todaysDate ) +1)
Where HitDate = @todaysdate
END
ELSE
BEGIN
Insert INTO [Unique]
(
HitDate,
TotalHits
)
VALUES
(
@todaysdate,
1
)
END
GO
SET QUOTED_ IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
编号储存过程注意事项:
1 储存过程以"Create PROCEDURE dbo.库名 AS" 开始,GO 结束
在查询分析器内写完储存过程,执行后该储存过程在自动创建
在该数据库存储过程表中. 如要测试,可把 储存过程开始和结
束标识符去掉后执行.
2 变量声明格式为"@name varchar(100)" ,注意声明字符串变量
时初始变量大小, 声明全局变量都在 AS 句语之去, 局部变量
都在 AS 之后,格式为: "DECLARE @name varchar(100)" 赋
值是前面另上 SET 如: "SET @name = 'create table ' + name"
*/

/*
T - SQL 语法 :
BEGIN 和 END ---- BEGIN...END 块内类似于C#中的大括号, 如: if(1>2)BEGIN...END else BEGIN...END
COUNT ----- 行数合计 如:COUNT(*)
declare ----- 内部变量声名 如:@name VARCHAR(100)
SET ----- 变量赋值时用 如: SET @name = '美女' , 在Select等子句中
内赋值不用SET 如: Select @name=COUNT(*) FROM tab
Drop PROCEDURE ---- 删除存储过程
EXEC ------ 执行其它"sql字符串"或"存储过程"如:
sql字符串: exec("select * from tab")
存储过程 : DECLARE @percent int //以下执行了三个"存储过程" 其它有第一个执行后的参数给第二个执行体使用
exec Get '名字1', @userinfo = @percent output
exec [inser] @percent,@percent
exec Get @percent, @userinfo = @percent output
*/



/*
数据类型:

字符型
char 与 varchar 的区别在于,前者为定长,如果字符串不够该定义和度,则以英文空格填充

char 存放非Unicode字符集,英文以1个字节存放,汉字以2个字节存放,可存放1-8000 = 8kb
nchar 存放Unicode字符集. 所有字符都以2个字节存放 ,可存放1-4000

Text 是存放最大的字符类型

数字类型:
bigint ：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。
int ：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据。
tinyint ：从0到255的整数数据。
bit ：1或0的整数数据。

decimal和numeric
这两种数据类型是等效的。都有两个参数：p（精度）和s（小数位数）。
p指定小数点左边和右边可以存储的十进制数字的最大个数，p必须是从 1到38之间的值。
s指定小数点右边可以存储的十进制数字的最大个数，s必须是从0到p之间的值，默认小数位数是0。

float和real
float ：从-1.79^308到1.79^308之间的浮点数字数据。
real ：从-3.40^38到3.40^38之间的浮点数字数据。在SQL Server中，real的同义词为float(24)。


日期型:
datetime ：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。
smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。

二进制数据包括 Binary、Varbinary 和 Image
　　 Binary 数据类型既可以是固定长度的(Binary),也可以是变长度的。
　　 Binary[(n)] 是 n 位固定的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4 个字节。
　　 Varbinary[(n)] 是 n 位变长度的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节，不是n 个字节。
　　 在 Image 数据类型中存储的数据是以位字符串存储的，不是由 SQL Server 解释的，必须由应用程序来解释。例如，应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 Image 数据类型中。

*/



//存储过程分页三种方法：

3
4 Create TABLE [TestTable] (
5 [ID] [int] IDENTITY (1, 1) NOT NULL ,
6 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
7 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
8 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
9 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
10 ) ON [PRIMARY]
11 GO
12
13
14
15 插入数据：(2万条，用更多的数据测试会明显一些)
16 SET IDENTITY_Insert TestTable ON
17
18 declare @i int
19 set @i=1
20 while @i<=20000
21 begin
22 insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
23 set @i=@i+1
24 end
25
26 SET IDENTITY_Insert TestTable OFF
27
28
29
30 -------------------------------------
31
32 分页方案一：(利用Not In和Select TOP分页)
33 语句形式：
34
50
51 -------------------------------------
52 Select TOP 10 *
35 FROM TestTable
36 Where (ID NOT IN
37 (Select TOP 20 id
38 FROM TestTable
39 orDER BY id))
40 orDER BY ID
41
42
43 Select TOP 页大小 *
44 FROM TestTable
45 Where (ID NOT IN
46 (Select TOP 页大小*页数 id
47 FROM 表
48 orDER BY id))
49 orDER BY ID
53 分页方案二：(利用ID大于多少和Select TOP分页）
54 语句形式：
55 Select TOP 10 *
56 FROM TestTable
57 Where (ID >
58 (Select MAX(id)
59 FROM (Select TOP 20 id
60 FROM TestTable
61 orDER BY id) AS T))
62 orDER BY ID
63
64
65 Select TOP 页大小 *
66 FROM TestTable
67 Where (ID >
68 (Select MAX(id)
69 FROM (Select TOP 页大小*页数 id
70 FROM 表
71 orDER BY id) AS T))
72 orDER BY ID
73
74
75 -------------------------------------
76
77 分页方案三：(利用SQL的游标存储过程分页)
78 create procedure XiaoZhengGe
79 @sqlstr nvarchar(4000), --查询字符串
80 @currentpage int, --第N页
81 @pagesize int --每页行数
82 as
83 set nocount on
84 declare @P1 int, --P1是游标的id
85 @rowcount int
86 exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
87 select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
88 set @currentpage=(@currentpage-1)*@pagesize+1
89 exec sp_cursorfetch @P1,16,@currentpage,@pagesize
90 exec sp_cursorclose @P1
91 set nocount off
92
93 其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
94 建议优化的时候，加上主键和索引，查询效率会提高。
95
96 通过SQL 查询分析器，显示比较：我的结论是:
97 分页方案二：(利用ID大于多少和Select TOP分页）效率最高，需要拼接SQL语句
98 分页方案一：(利用Not In和Select TOP分页) 效率次之，需要拼接SQL语句
99 分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
100
101 在实际情况中，要具体分析。



// 自已写的储存过程分页
<script type="text/javascript">google_ad_client = "pub-4475724770859924";google_alternate_color = "FFBBE8";google_ad_width = 468;google_ad_height = 60;google_ad_format = "468x60_as";google_ad_type = "text_image";google_ad_channel ="9379930647";google_color_border = "F8F8F8";google_color_bg = "FFFFFF";google_color_link = "FF6FCF";google_color_url = "38B63C";google_color_text = "B3B3B3";</script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>
/*-- classify参数查询指定分类的所有帖子 */

Create PROCEDURE GetForumInfo
@pageing int,
@pagesize int,
@classify varchar(10)
AS

declare @_pageing VARCHAR(100)
IF @pageing <= 0
BEGIN
SET @pageing = 1
END
set @_pageing = cast(@pagesize*(@pageing-1) AS varchar)
exec ('Select TOP '+@pagesize+'
id,state,
(select title from Classify where id=classify_id) as
classify_id,author,revert_number,see_number,end_data
FROM ForumInfo
Where classify_id='+@classify+'
and
id not in (select top '+@_pageing+' id from ForumInfo where classify_id='+@classify+'order by end_data ASC)

orDER BY end_data ASC')
GO

C#中使用存储过程,C#分页存储过程

引用通告地址: http://www.ad0.cn/netfetch/trackback.asp?tbID=339