forked from cyq1162/cyqdata
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSqlCreateForProc.cs
More file actions
200 lines (193 loc) · 6.68 KB
/
Copy pathSqlCreateForProc.cs
File metadata and controls
200 lines (193 loc) · 6.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
using System.Text;
namespace CYQ.Data.SQL
{
/// <summary>
/// 分页存储过程语句类
/// </summary>
internal partial class SqlCreateForPager
{
#region 分页存储过程
/// <summary>
/// 获取sql2005分页存储过程[输出带"br换行标签"]
/// </summary>
public static string GetSelectBaseForSql2005()
{
return @"Create procedure [SelectBase]
@PageIndex int,
@PageSize int,
@TableName nvarchar(4000),
@Where nvarchar(max)=''
as
Declare @rowcount int
Declare @intStart int
Declare @intEnd int
Declare @SQl nvarchar(max), @WhereR nvarchar(max), @OrderBy nvarchar(max)
set @rowcount=0
set nocount on
if @Where<>''
begin
set @Where=' and '+@Where
end
if CHARINDEX('order by', @Where)>0
begin
set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1) --取得条件
set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where)) --取得排序方式(order by 字段 方式)
end
else
begin
set @WhereR=@Where
set @OrderBy=' order by id asc'
end
set @SQl='SELECT @rowcount=count(*) from '+cast(@TableName as varchar(4000))+' where 1=1 '+@WhereR
exec sp_executeSql @SQl,N'@rowcount int output',@rowcount output
if @PageIndex=0 and @PageSize=0 --不进行分页,查询所有数据列表
begin
set @SQl='SELECT * from '+cast(@TableName as varchar(4000))+' where 1=1 '+@Where
end
else --进行分页查询数据列表
begin
set @intStart=(@PageIndex-1)*@PageSize+1;
set @intEnd=@intStart+@PageSize-1
set @SQl='select * from(select *,ROW_NUMBER() OVER('+cast(@OrderBy as nvarchar(400))+') as cyqrownum from '
set @SQl=@SQL+@TableName+' where 1=1 '+@WhereR+') as a where cyqrownum between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar)
end
exec sp_executesql @SQl
return @rowcount
set nocount off";
}
/*
/// <summary>
/// 获取sql2000分页存储过程[输出带"br换行标签"]
/// </summary>
public static string GetSelectBaseForSql2000()
{
bool isForSybase = false;
string executeCount= isForSybase ? "execute(@Sql)" : "exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output";
string executeSql = isForSybase ? "execute(@Sql)" : "exec sp_executeSql @Sql ";
return @"Create procedure [dbo].[SelectBase]
@PageIndex int,
@PageSize int,
@TableName varchar(4000),
@Where varchar(2000)=''
as
Declare @rowcount int
Declare @Sql nvarchar(4000), @WhereOnly varchar(1000),@OrderbyColumn varchar(64)
set @rowcount=0
set nocount on
if @Where<>''
begin
set @Where=' where '+@Where
if CHARINDEX('order by', @Where)>0
begin
set @rowcount=CHARINDEX('order by',@Where)
set @WhereOnly=substring(@Where, 1, @rowcount-1)
set @OrderbyColumn=RTRIM(LTRIM(substring(@Where,@rowcount+9,64)))
set @rowcount=CHARINDEX(' ',@OrderbyColumn)
if @rowcount>0
begin
if CHARINDEX('+',@OrderbyColumn)=0
begin
set @OrderbyColumn=substring(@OrderbyColumn,1,@rowcount-1)
end
else set @OrderbyColumn='' end
end
else
begin
set @WhereOnly=@Where
end
end
else
begin
set @WhereOnly=''
end
set @Sql='SELECT @rowcount=count(*) from '+cast(@TableName as varchar(4000))+@WhereOnly
exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output
if @PageIndex=0 and @PageSize=0
begin
set @Sql='SELECT * from '+cast(@TableName as varchar(4000))+@Where
end
else
begin
Declare @intStart int
Declare @intEnd int
declare @Column1 varchar(64)
if CHARINDEX('(', @TableName)>0
begin
set @TableName=reverse(@TableName)
set @TableName=reverse(substring(@TableName,charindex(')',@TableName),4000))+' t '
if @OrderbyColumn<>'' begin set @Column1=@OrderbyColumn end
else begin set @Column1='ID' end
end
else
begin
set @Column1=col_name(object_id(@TableName),1)
set @TableName=@TableName+' t '
end
set @intStart=(@PageIndex-1)*@PageSize+1
set @intEnd=@intStart+@PageSize-1
set @Sql='Create table #tem(tempID int identity(1,1) not null,cyqrownum varchar(64)) '
set @Sql=@Sql+'insert #tem(cyqrownum) select cast('+@Column1+' as varchar(64)) from '+@TableName+@Where
set @Sql=@Sql+' select t.* from #tem left join '+@TableName+' on cast('+@Column1+' as varchar(64))=#tem.cyqrownum '
set @Sql=@Sql+' where #tem.tempID between '+cast(@intStart as varchar)+' and '+cast(@intEnd as varchar)+' order by #tem.tempID asc'
set @Sql=@Sql+' drop table #tem'
end
exec sp_executeSql @Sql
return @rowcount
set nocount off";
}
//public static string GetSelectBaseOutPutToHtmlForOracle()
//{
// return GetPackageHeadForOracle() + GetPackageBodyForOracle();
//}
public static string GetPackageHeadForOracle()
{
return @"
create or replace package MyPackage as
type MyCursor is ref cursor;
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
resultCount out int, resultCursor out MyCursor);
end MyPackage;";
}
public static string GetPackageBodyForOracle()
{
return @"
create or replace package Body MyPackage is
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
resultCount out int, resultCursor out MyCursor)
is
rowStart int;
rowEnd int;
mySql varchar2(8000);
whereOnly varchar2(8000);
OrderOnly varchar2(400);
begin
mySql:='select count(*) from '||tableName;
whereOnly:=whereStr;
rowStart:=instr(whereStr,'order by');
if whereStr is not null and rowStart>0
then
whereOnly:=substr(whereStr, 1,rowStart-1);
OrderOnly:=substr(whereStr,rowStart, length(whereStr)-rowStart+1);
end if;
if length(whereOnly)>1
then
whereOnly:=' where '|| whereOnly;
mySql:=mySql||whereOnly;
end if;
execute immediate mySql into resultCount;
if pageIndex=0 and pageSize=0
then
mySql:='select * from '||tableName||whereOnly||OrderOnly;
else
rowStart:=(pageIndex-1)*pageSize+1;
rowEnd:=rowStart+pageSize-1;
mySql:='select * from (select t.*,RowNum as cyqrownum from ('||tableName||') t'||whereOnly||OrderOnly||') where cyqrownum between '||rowStart||' and '||rowEnd;
end if;
open ResultCursor for mySql;
end SelectBase;
end MyPackage;";
}
* */
#endregion
}
}