这是一个说明如果使用DbProviderFactory来达到实现多数据库访问的文章,支持对ACCESS,SQL SERVER,ORACLE,SQLite等数据库的访问,在读取数据上使用DataReader但统一返回DataTable实例,支持以泛型的方式取得单个数据,并支持在有事务的情况下一次运行多条SQL语句.代码中关于参数缓存是抄袭了蛙蛙池塘曾发布过的一个数据库访问组件,不过在日常开发中,我一直没有用上.代码中有大量注释,且没有难懂的东西,应该都可以看懂,希望对大家有所帮助.如果存在安全性或性能问题,也希望大家指出来.代码中附带了三个数据库分页代码,SQL SERVER分页需要一个存储过程支持,及附带了一个显示分页页数的代码.使用时需要在web.config里面进入配置一下,如下>:
<connectionStrings>
<add name="ConnLink" connectionString="数据库连接驱动字符串" providerName="如:System.Data.SQLite"/>
</connectionStrings>
源程序下载地址:下载地址
下面是代码:
![](/Images/OutliningIndicators/ContractedBlock.gif)
Code
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**************************************************
2
* 文 件 名:dbbase.cs
3
* Copyright(c) 2009-2010
4
* 文件编号:001
5
* 创 建 人:晴天水族
6
* 日 期:2009-01-23
7
* 修 改 人:
8
* 修改日期:
9
* 备注描述:数据工厂类
10
* 支持ACCESS,SQL SERVER,ORACLE,SQLite
11
*************************************************/
12![](/Images/OutliningIndicators/ContractedBlock.gif)
" 导入的命名空间 "#region " 导入的命名空间 "
13
using System;
14
using System.Data;
15
using System.Data.SqlClient;
16
using System.Data.Common;
17
using System.Configuration;
18
using System.Collections;
19
using System.Text;
20
using System.Data.SQLite; //添加对SQLite数据库的支持
21
#endregion
22![](/Images/OutliningIndicators/None.gif)
23![](/Images/OutliningIndicators/ContractedBlock.gif)
" 新建通用的数据库操作命名空间,支持ACCESS,SQL SERVER,ORACLE,SQLite "#region " 新建通用的数据库操作命名空间,支持ACCESS,SQL SERVER,ORACLE,SQLite "
24
namespace dbbase
25![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
{
26![](/Images/OutliningIndicators/InBlock.gif)
27![](/Images/OutliningIndicators/ContractedSubBlock.gif)
"操作数据库类"#region "操作数据库类"
28
public class dbbases : IDisposable
29![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
30![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
31
/// 安全类型的集合
32
/// </summary>
33
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
34![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
35
/// 下面两个是静态变量
36
/// </summary>
37
private static readonly string strs = ConfigurationManager.ConnectionStrings["ConnLink"].ConnectionString;
38
private static readonly string pdn = ConfigurationManager.ConnectionStrings["ConnLink"].ProviderName;
39![](/Images/OutliningIndicators/InBlock.gif)
40![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
41
/// 属性用于接收数据的存取类型 及识别用户所启用的数据库
42
/// </summary>
43
private string providername;
44![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
45
/// 各种数据库的连接字符串
46
/// </summary>
47
private string connstring;
48![](/Images/OutliningIndicators/InBlock.gif)
49![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" 带参和不带参的构造函数 "#region " 带参和不带参的构造函数 "
50![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
51
/// 默认构造函数,有重载
52
/// </summary>
53
public dbbases()
54![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
55
this.providername = pdn; //使用的数据驱动类,默认
56
this.connstring = strs; //连接数据库的字符串,默认
57
}
58![](/Images/OutliningIndicators/InBlock.gif)
59![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
60
/// 初始构造函数
61
/// </summary>
62
/// <param name="provider">数据驱动类型[SqlClient|Access|Orarl|SQLite]</param>
63
/// <param name="links">数据库的连接字符串</param>
64
public dbbases(string provider, string links)
65![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
66
this.providername = provider; //使用的数据驱动类
67
this.connstring = links; //连接数据库的字符串
68
}
69
#endregion
70![](/Images/OutliningIndicators/InBlock.gif)
71![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
72
/// 析构函数
73
/// </summary>
74
~dbbases()
75![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
76
CloseCon(); //热行清理
77
}
78![](/Images/OutliningIndicators/InBlock.gif)
79![](/Images/OutliningIndicators/InBlock.gif)
80![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
81
/// 检测当前数据库连接状态
82
/// </summary>
83
/// <returns></returns>
84
public string isConnstate()
85![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
86
if (cmd != null)
87![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
88
return cmd.Connection.State.ToString();
89
}
90
return "变量已清除";
91
}
92![](/Images/OutliningIndicators/InBlock.gif)
93![](/Images/OutliningIndicators/ContractedSubBlock.gif)
"isclose属性,默认0指进行清理,1为不进行清理,可以用于多次循环之中,避免多次开关数据库"#region "isclose属性,默认0指进行清理,1为不进行清理,可以用于多次循环之中,避免多次开关数据库"
94![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
95
/// 是否进行各项数据库连接器的清理工作
96
/// </summary>
97
/// <returns></returns>
98
private int isclose = 0;
99
public int IsClose
100![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
101
get
102![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
103
return isclose;
104
}
105
set
106![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
107
isclose = value;
108
}
109
}
110
#endregion
111![](/Images/OutliningIndicators/InBlock.gif)
112![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" 分页记录反回变量 "#region " 分页记录反回变量 "
113![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
private int allpage = 0; /**////分页函数中记录共有多少页的变量
114
public int Allpage
115![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return allpage; }
117
}
118![](/Images/OutliningIndicators/InBlock.gif)
119![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
private int allrecord = 0; /**////分页函数中记录菜有数据总量的变量
120
public int Allrecord
121![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
122![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return allrecord; }
123
}
124
#endregion
125![](/Images/OutliningIndicators/InBlock.gif)
126![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" 数据库操作对像的属性[Adapter|Command|Begintransaction] "#region " 数据库操作对像的属性[Adapter|Command|Begintransaction] "
127![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
128
/// 属性DbDataAdapter
129
/// </summary>
130
private DbDataAdapter adp;
131![](/Images/OutliningIndicators/InBlock.gif)
132![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
133
/// 属性SqlCommand
134
/// </summary>
135
private DbCommand cmd;
136![](/Images/OutliningIndicators/InBlock.gif)
137![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
138
/// 事务
139
/// </summary>
140
private DbTransaction Tran;
141
#endregion
142![](/Images/OutliningIndicators/InBlock.gif)
143![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" CloseCon() 关闭相关的数据库连接 "#region " CloseCon() 关闭相关的数据库连接 "
144![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
145
/// 关闭数据库连接
146
/// </summary>
147
public void CloseCon()
148![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
149
if (cmd != null)
150![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
151
if (cmd.Connection.State != ConnectionState.Closed)
152![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
153
cmd.Connection.Close();
154
}
155
cmd.Dispose();
156
cmd = null;
157
}
158![](/Images/OutliningIndicators/InBlock.gif)
159
if (adp != null)
160![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
161
adp.Dispose();
162
adp = null;
163
}
164![](/Images/OutliningIndicators/InBlock.gif)
165
if (Tran != null)
166![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
167
Tran.Dispose();
168
Tran = null;
169
}
170![](/Images/OutliningIndicators/InBlock.gif)
171
//GC.Collect(); ///强制对所有代进行垃圾回收
172
}
173
#endregion
174
175![](/Images/OutliningIndicators/ContractedSubBlock.gif)
"getFace() 创建工厂对像"#region "getFace() 创建工厂对像"
176![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
177
/// 创建工厂对像
178
/// </summary>
179
/// <returns>DbProviderFactory</returns>
180
public DbProviderFactory getFace()
181![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
182
DbProviderFactory fact = null;
183
if (providername == "System.Data.SQLite")
184![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
185
fact = SQLiteFactory.Instance; //SQLite数据库创建数据工厂类
186
}
187
else
188![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
189
fact = DbProviderFactories.GetFactory(providername); //获得当前所调定的数据源存取类型
190
}
191
return fact;
192
}
193
#endregion
194![](/Images/OutliningIndicators/InBlock.gif)
195![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" 创建CMD对像,以供其它对像使用 "#region " 创建CMD对像,以供其它对像使用 "
196![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
197
/// 创建CMD对像
198
/// </summary>
199
/// <returns>DbComand对像实例</returns>
200
private DbCommand CreateDbCommand()
201![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
202
DbProviderFactory fact = getFace(); //工厂对像
203
DbConnection conn = fact.CreateConnection(); //创建Connection对像
204
conn.ConnectionString = connstring; //设定Connection对像的连接字符串
205
cmd = conn.CreateCommand(); //使用 conn 的函数 CreateCommand() 创建Command对像
206
return cmd; //返回Command对像
207
}
208
#endregion
209![](/Images/OutliningIndicators/InBlock.gif)
210![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" RemoveParames清除CMD的参数 及存储过程的参数缓存 "#region " RemoveParames清除CMD的参数 及存储过程的参数缓存 "
211![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
212
/// 清除参数
213
/// </summary>
214
/// <param name="cmd">DbCommand的对像</param>
215
private void RemoveParams(DbCommand cmd)
216![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
217
while (cmd.Parameters.Count > 0)
218![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
219
cmd.Parameters.RemoveAt(0);
220
}
221
}
222![](/Images/OutliningIndicators/InBlock.gif)
223![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
224
/// 从缓存中初使化SQL或存储过程的参数
225
/// </summary>
226
/// <param name="cmd">Command对像</param>
227
/// <returns>布尔值</returns>
228
public bool initParametersFromCache(DbCommand cmd)
229![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
230
DbParameter[] parms = GetCachedParameters(string.Format("{0}{1}", cmd.Connection.ConnectionString, cmd.CommandText));
231
if (parms == null)
232
return false;
233
for (int i = 0; i < parms.Length; i++)
234![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
235
cmd.Parameters.Add(parms[i]);
236
}
237
return true;
238
}
239![](/Images/OutliningIndicators/InBlock.gif)
240
public static void CacheParameters(string cacheKey, params DbParameter[] cmdParms)
241![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
242
parmCache[cacheKey] = cmdParms;
243
}
244![](/Images/OutliningIndicators/InBlock.gif)
245![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
246
/// 查找缓存中的变量
247
/// </summary>
248
/// <param name="cacheKey">缓存名称</param>
249
/// <returns>DbParameter</returns>
250
public DbParameter[] GetCachedParameters(string cacheKey)
251![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
252
DbParameter[] cachedParms = (DbParameter[])parmCache[cacheKey]; //parmCache 本程序第18行,安全类型的HASHtable
253![](/Images/OutliningIndicators/InBlock.gif)
254
if (cachedParms == null)
255
return null;
256![](/Images/OutliningIndicators/InBlock.gif)
257
DbParameter[] clonedParms = new DbParameter[cachedParms.Length];
258![](/Images/OutliningIndicators/InBlock.gif)
259
for (int i = 0, j = cachedParms.Length; i < j; i++)
260![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
261
clonedParms[i] = (DbParameter)((ICloneable)cachedParms[i]).Clone();
262
}
263![](/Images/OutliningIndicators/InBlock.gif)
264
return clonedParms;
265
}
266![](/Images/OutliningIndicators/InBlock.gif)
267![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
268
/// 缓存参数
269
/// </summary>
270
/// <param name="cmd">DbCommand</param>
271
public void CachedParameters(DbCommand cmd)
272![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
273
DbParameterCollection paramColl = cmd.Parameters;
274
DbParameter[] parms = new DbParameter[paramColl.Count];
275
for (int i = 0; i < paramColl.Count; i++)
276![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
277
parms[i] = paramColl[i];
278
}
279![](/Images/OutliningIndicators/InBlock.gif)
280
CacheParameters(string.Format("{0}{1}", cmd.Connection.ConnectionString, cmd.CommandText), parms);
281
}
282![](/Images/OutliningIndicators/InBlock.gif)
283![](/Images/OutliningIndicators/InBlock.gif)
284
#endregion
285![](/Images/OutliningIndicators/InBlock.gif)
286![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" ProTxtCmd创建操作存储过程与SQL的CMD对像 "#region " ProTxtCmd创建操作存储过程与SQL的CMD对像 "
287![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
288
/// 创建操作存储过程与SQL的CMD对像
289
/// </summary>
290
/// <param name="pronames">存储过程或SQL语句</param>
291
/// <param name="sid">识别ID</param>
292
/// <returns>cmd</returns>
293
private DbCommand ProTxtCmd(string proSqls, Byte sid)
294![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
295
cmd = CreateDbCommand(); //创建CMD对像
296
if (sid == 0)
297![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
298
cmd.CommandType = CommandType.Text; //设置SQL语句
299
}
300
else
301![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
302
cmd.CommandType = CommandType.StoredProcedure; //设置 cmd 的操作命令方式,此处为存储过程
303
}
304![](/Images/OutliningIndicators/InBlock.gif)
305
cmd.CommandText = proSqls; //设置 存储过程名或SQL语句
306
return cmd;
307
}
308
#endregion
309![](/Images/OutliningIndicators/InBlock.gif)
310![](/Images/OutliningIndicators/ContractedSubBlock.gif)
"executeRunSqlArray 执行多条SQL语句,有事务,批量删除等,传递一个数组"#region "executeRunSqlArray 执行多条SQL语句,有事务,批量删除等,传递一个数组"
311![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
312
/// 执行多条SQL语句,有事务
313
/// </summary>
314
/// <param name="sqllist">SQL语句集合数组</param>
315
/// <returns>Boolean</returns>
316
public bool executeRunSqlArray(string[] sqllist)
317![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
318
cmd = CreateDbCommand(); //创建CMD对像
319
try
320![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
321
if (cmd.Connection.State != ConnectionState.Open)
322![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
323
cmd.Connection.Open();
324
}
325
Tran = cmd.Connection.BeginTransaction(); //新增事务
326
cmd.Transaction = Tran; //事务
327
for (int i = 0; i < sqllist.Length; i++)
328![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
329
string sql = sqllist[i].ToString();
330
if (sql.Trim().Length > 1)
331![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
332
cmd.CommandText = sql;
333
cmd.ExecuteNonQuery();
334
}
335
}
336
Tran.Commit();
337
}
338
catch (DbException ex)
339![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
340
Tran.Rollback();
341
printEx(ex, 1);
342
return false;
343
}
344
finally
345![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
346
if (this.isclose == 0) //默认清理
347![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
348
CloseCon(); //热行清理
349
}
350
}
351![](/Images/OutliningIndicators/InBlock.gif)
352
return true;
353![](/Images/OutliningIndicators/InBlock.gif)
354
}
355
#endregion
356![](/Images/OutliningIndicators/InBlock.gif)
357![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" getdt 从SQL语句或存储过程中返回DataTable "#region " getdt 从SQL语句或存储过程中返回DataTable "
358![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
359
/// 返回SQLS中的dt
360
/// </summary>
361
/// <param name="sqlsPro">SQL语句</param>
362
/// <param name="parames">SQL语句参数</param>
363
/// <param name="sid">识别码,0为SQL语句,1为存储过程</param>
364
/// <returns>DateTable</returns>
365
public DataTable getdt(string sqlsPro, DbParameter[] parames, int sid)
366![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
367
DataTable dt = new DataTable();
368
DbDataReader dr = null;
369![](/Images/OutliningIndicators/InBlock.gif)
370
if (sid == 0) //SQL语句
371![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
372
cmd = ProTxtCmd(sqlsPro, 0);
373
}
374
else //存储过程
375![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
376
cmd = ProTxtCmd(sqlsPro, 1);
377
}
378![](/Images/OutliningIndicators/InBlock.gif)
379
if (parames != null && parames.Length > 0)
380![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
381
foreach (DbParameter param in parames)
382
if (param.Value != null)
383
cmd.Parameters.Add(param);
384
}
385![](/Images/OutliningIndicators/InBlock.gif)
386
try
387![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
388
if (cmd.Connection.State != ConnectionState.Open)
389![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
390
cmd.Connection.Open();
391
}
392
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
393
if (dr.HasRows)
394![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
395
dt.Load(dr);
396
dr.Close();
397
dr = null;
398
}
399
}
400
catch (DbException ex)
401![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
402
dr.Close();
403
dr = null;
404
printEx(ex, 1);
405
}
406
finally
407![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
408
if (this.isclose == 0) //默认清理
409![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
410
if (parames != null)
411![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
412
RemoveParams(cmd);
413
cmd.Parameters.Clear();
414
}
415
CloseCon(); //热行清理
416
}
417
}
418
return dt;
419
}
420
#endregion
421![](/Images/OutliningIndicators/InBlock.gif)
422![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" runsql 执行SQL语句 "#region " runsql 执行SQL语句 "
423![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
424
/// 运行SQL语句
425
/// </summary>
426
/// <param name="sqlsPro">sql语句</param>
427
/// <param name="parames">SQL语句参数</param>
428
/// <param name="sid">识别ID</param>
429
/// <returns>int</returns>
430
public int runsql(string sqlsPro, DbParameter[] parames, int sid)
431![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
432
int rsInt = 0;
433![](/Images/OutliningIndicators/InBlock.gif)
434
cmd = null;
435![](/Images/OutliningIndicators/InBlock.gif)
436
if (sid == 0) //SQL语句
437![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
438
cmd = ProTxtCmd(sqlsPro, 0);
439
}
440
else //存储过程
441![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
442
cmd = ProTxtCmd(sqlsPro, 1);
443
}
444![](/Images/OutliningIndicators/InBlock.gif)
445
if (parames != null && parames.Length > 0)
446![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
447
foreach (DbParameter param in parames)
448
if (param.Value != null)
449
cmd.Parameters.Add(param);
450
}
451![](/Images/OutliningIndicators/InBlock.gif)
452
try
453![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
454
if (cmd.Connection.State != ConnectionState.Open)
455![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
456
cmd.Connection.Open();
457
}
458![](/Images/OutliningIndicators/InBlock.gif)
459
rsInt = cmd.ExecuteNonQuery();
460
}
461
catch (DbException ex)
462![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
463
printEx(ex, 1);
464
}
465
finally
466![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
467
if (this.isclose == 0) //默认清理
468![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
469
if (parames != null)
470![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
471
RemoveParams(cmd);
472
cmd.Parameters.Clear();
473
}
474
CloseCon(); //热行清理
475
}
476
}
477![](/Images/OutliningIndicators/InBlock.gif)
478
return rsInt;
479![](/Images/OutliningIndicators/InBlock.gif)
480
}
481
#endregion
482![](/Images/OutliningIndicators/InBlock.gif)
483![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" getSca 泛型,取得单个数据ExecuteScalar,适用于SQL语句及存储过程 "#region " getSca 泛型,取得单个数据ExecuteScalar,适用于SQL语句及存储过程 "
484![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
485
/// 取得单个数据 (注意如果取整型数据请使用long代替T)
486
/// </summary>
487
/// <typeparam name="T">泛型所替换的数据类型</typeparam>
488
/// <param name="sqlsPro">sql语句</param>
489
/// <param name="parames">SQL语句的参数</param>
490
/// <param name="sid">识别ID,0为SQL语句,1为存储过程</param>
491
/// <returns></returns>
492
public T getSca<T>(string sqlsPro, DbParameter[] parames, int sid)
493![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
494
cmd = null;
495![](/Images/OutliningIndicators/InBlock.gif)
496
if (sid == 0) //SQL语句
497![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
498
cmd = ProTxtCmd(sqlsPro, 0);
499
}
500
else //存储过程
501![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
502
cmd = ProTxtCmd(sqlsPro, 1);
503
}
504![](/Images/OutliningIndicators/InBlock.gif)
505
if (parames != null && parames.Length > 0)
506![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
507
foreach (DbParameter param in parames)
508
if (param.Value != null)
509
cmd.Parameters.Add(param);
510
}
511![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
T Tstr = default(T); /**////泛型变量
512
try
513![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
514
if (cmd.Connection.State != ConnectionState.Open)
515![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
516
cmd.Connection.Open();
517
}
518![](/Images/OutliningIndicators/InBlock.gif)
519
if (System.DBNull.Value != cmd.ExecuteScalar())
520![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
521![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
Tstr = (T)cmd.ExecuteScalar(); /**////读取第一行第一列
522
}
523![](/Images/OutliningIndicators/InBlock.gif)
524
}
525
catch (DbException ex)
526![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
527
printEx(ex, 0);
528
return default(T);
529
}
530
finally
531![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
532
if (this.isclose == 0) //默认清理
533![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
534
if (parames != null)
535![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
536
RemoveParams(cmd);
537
cmd.Parameters.Clear();
538
}
539
CloseCon(); //热行清理
540
}
541
}
542![](/Images/OutliningIndicators/InBlock.gif)
543
return Tstr;
544![](/Images/OutliningIndicators/InBlock.gif)
545
}
546
#endregion
547![](/Images/OutliningIndicators/InBlock.gif)
548![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" 使用Adapter填充DataTable "#region " 使用Adapter填充DataTable "
549![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
550
/// 返回SQLS中的dt
551
/// </summary>
552
/// <param name="sqlsPro">SQL语句</param>
553
/// <param name="parames">SQL语句参数</param>
554
/// <param name="sid">识别码,0为SQL语句,1为存储过程</param>
555
/// <returns>DateTable</returns>
556
public DataTable getAdpdt(string sqlsPro, DbParameter[] parames, Byte sid)
557![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
558
DbProviderFactory dbfactory = getFace(); //取得数据库工厂对像
559![](/Images/OutliningIndicators/InBlock.gif)
560
adp = dbfactory.CreateDataAdapter();
561![](/Images/OutliningIndicators/InBlock.gif)
562
if (sid == 0) //SQL语句
563![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
564
cmd = ProTxtCmd(sqlsPro, 0);
565
}
566
else //存储过程
567![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
568
cmd = ProTxtCmd(sqlsPro, 1);
569
}
570![](/Images/OutliningIndicators/InBlock.gif)
571
if (parames != null && parames.Length > 0)
572![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
573
foreach (DbParameter param in parames)
574
if (param.Value != null)
575
cmd.Parameters.Add(param);
576
}
577![](/Images/OutliningIndicators/InBlock.gif)
578
adp.SelectCommand = cmd;
579
DataTable dt = new DataTable();
580
try
581![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
582
adp.Fill(dt);
583
}
584
catch (DbException ex)
585![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
586
printEx(ex, 1);
587
}
588
finally
589![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
590
if (this.isclose == 0) //默认清理
591![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
592
if (parames != null)
593![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
594
RemoveParams(cmd);
595
cmd.Parameters.Clear();
596
}
597![](/Images/OutliningIndicators/InBlock.gif)
598
CloseCon(); //热行清理
599
}
600
}
601
return dt;
602
}
603
#endregion
604![](/Images/OutliningIndicators/InBlock.gif)
605![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" Pagination_dt 分页,适用于SQL SERVER数据库 "#region " Pagination_dt 分页,适用于SQL SERVER数据库 "
606![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
607
/// 分页函数,返回dt
608
/// </summary>
609
/// <param name="table_name">需要分页显示的表名</param>
610
/// <param name="key">表的主键,必须唯一性</param>
611
/// <param name="orderstr">排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)</param>
612
/// <param name="cpage">当前页</param>
613
/// <param name="psize">每页大小</param>
614
/// <param name="fieles">显示的字段列表</param>
615
/// <param name="filter">条件语句,不加where</param>
616
/// <param name="g_str">分组字段</param> 以前都不再使用
617
/// <param name="pro_name">存储过程名</param>
618
/// <returns>返回内存表</returns>
619
public DataTable Pagination_dt(string table_name, string key, string orderstr, int cpage, int psize, string fieles, string filter, string g_str, string pro_name)
620![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
621![](/Images/OutliningIndicators/InBlock.gif)
622
DataTable dt = new DataTable();
623![](/Images/OutliningIndicators/InBlock.gif)
624
SqlCommand cmd = (SqlCommand)ProTxtCmd(pro_name, 1);
625![](/Images/OutliningIndicators/InBlock.gif)
626![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
SqlParameter[] parames =
{ new SqlParameter("@Tables", SqlDbType.VarChar, 50), new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 10), new SqlParameter("@Sort", SqlDbType.VarChar, 50), new SqlParameter("@CurrentPage", SqlDbType.Int, 4), new SqlParameter("@PageSize", SqlDbType.Int, 4), new SqlParameter("@fields", SqlDbType.VarChar, 1000), new SqlParameter("@Filter", SqlDbType.VarChar, 1000), new SqlParameter("@Group", SqlDbType.VarChar, 1000) };
627![](/Images/OutliningIndicators/InBlock.gif)
628
parames[0].Value = table_name;
629
parames[1].Value = key;
630
parames[2].Value = orderstr;
631
parames[3].Value = cpage;
632
parames[4].Value = psize;
633
parames[5].Value = fieles;
634
parames[6].Value = filter;
635
parames[7].Value = g_str;
636![](/Images/OutliningIndicators/InBlock.gif)
637![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (SqlParameter parameter in parames) /**////添加输入参数集合
638![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
639
cmd.Parameters.Add(parameter);
640
}
641![](/Images/OutliningIndicators/InBlock.gif)
642
SqlParameter s1 = cmd.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int));
643![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
SqlParameter s2 = cmd.Parameters.Add(new SqlParameter("@TotalRecord", SqlDbType.Int)); /**////返回值
644
s1.Direction = ParameterDirection.Output;
645
s2.Direction = ParameterDirection.Output;
646![](/Images/OutliningIndicators/InBlock.gif)
647
DbDataReader dr = null;
648![](/Images/OutliningIndicators/InBlock.gif)
649
try
650![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
651
if (cmd.Connection.State != ConnectionState.Open)
652![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
653
cmd.Connection.Open();
654
}
655![](/Images/OutliningIndicators/InBlock.gif)
656
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); //关闭数据库
657
if (dr.HasRows)
658![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
659
dt.Load(dr);
660
dr.Close();
661
dr.Dispose();
662![](/Images/OutliningIndicators/InBlock.gif)
663
if (cmd.Parameters["@TotalRecord"].Value != System.DBNull.Value)
664![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
665![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allrecord = int.Parse(cmd.Parameters["@TotalRecord"].Value.ToString()); /**////返回总记录数
666
}
667![](/Images/OutliningIndicators/InBlock.gif)
668![](/Images/OutliningIndicators/InBlock.gif)
669
if (cmd.Parameters["@TotalPage"].Value != System.DBNull.Value)
670![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
671![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allpage = int.Parse(cmd.Parameters["@TotalPage"].Value.ToString()); /**////返回首页数
672
}
673
}
674
}
675
catch (DbException ex)
676![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
677
dr.Close();
678
dr = null;
679
printEx(ex, 1);
680
}
681
finally
682![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
683
if (parames != null)
684![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
685
RemoveParams(cmd);
686
cmd.Parameters.Clear();
687
}
688
CloseCon(); //热行清理
689
}
690![](/Images/OutliningIndicators/InBlock.gif)
691
return dt;
692
}
693
#endregion
694![](/Images/OutliningIndicators/InBlock.gif)
695![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" Access_dt 分页,适用于ACCESS及其其它使用SQL语句的分页 "#region " Access_dt 分页,适用于ACCESS及其其它使用SQL语句的分页 "
696![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
697
/// SQL语句分页函数,返回dt
698
/// </summary>
699
/// <param name="table_name">需要分页显示的表名</param>
700
/// <param name="key">表的主键ID,且只能为ID必须唯一性</param>
701
/// <param name="orderstr">排序字段如id ASC,addTimes DESC或id DESC,addTimes ASC(可以有多个排序字段)</param>
702
/// <param name="cpage">当前页</param>
703
/// <param name="psize">每页大小</param>
704
/// <param name="fieles">显示的字段列表</param>
705
/// <param name="filter">条件语句,不加where</param>
706
/// <param name="isDesc">排序方式[true=desc倒序|false=asc顺序]</param>
707
/// <param name="allrecordsqls">客户端传来的计算总记录的SQL语句</param>
708
/// <param name="sql">客户端发来的分页SQL语句</param>
709
/// <returns>返回内存表</returns>
710
public DataTable Access_dt(string table_name, string key, string orderstr, int cpage, int psize, string fieles, string filter, bool isDesc, string allrecordsqls, string sql)
711![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
712
//先计算总记录,再计算总页数
713
string allCordSql = null;
714
if (filter == null) //不存在WHERE子句
715![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
716
allCordSql = "SELECT COUNT(" + key + ") FROM " + table_name + "";
717
}
718
else
719![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
720
allCordSql = "SELECT COUNT(" + key + ") FROM " + table_name + " WHERE " + filter + "";
721
}
722![](/Images/OutliningIndicators/InBlock.gif)
723
if (allrecordsqls == null)
724![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
725![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allrecord = getSca<int>(allCordSql, null, 0); /**////返回总记录数
726
}
727
else
728![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
729![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allrecord = getSca<int>(allrecordsqls, null, 0); /**////返回总记录数
730
}
731![](/Images/OutliningIndicators/InBlock.gif)
732
if (allrecord % psize == 0)
733![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
734
allpage = allrecord / psize; //返回总页数
735
}
736
else
737![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
738
allpage = allrecord / psize + 1; //返回总页数
739
}
740![](/Images/OutliningIndicators/InBlock.gif)
741
string sqls = null;
742![](/Images/OutliningIndicators/InBlock.gif)
743
if (isDesc) //倒序[从大到小]
744![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
745
if (filter == null) //不存在WHERE子句
746![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
747
if (cpage == 1)
748![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
749
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " ORDER BY " + orderstr + ""; //第一页
750
}
751
else
752![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
753
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + key + "<(SELECT MIN(" + key + ") FROM (SELECT TOP " + (cpage - 1) * psize + " " + key + " FROM " + table_name + " ORDER BY " + orderstr + ") as T) ORDER BY " + orderstr + ""; //非第一页
754
}
755
}
756
else
757![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
758
if (cpage == 1)
759![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
760
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + filter + " ORDER BY " + orderstr + ""; //第一页
761
}
762
else
763![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
764
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + filter + " AND " + key + "<(SELECT MIN(" + key + ") FROM (SELECT TOP " + (cpage - 1) * psize + " " + key + " FROM " + table_name + " WHERE " + filter + " ORDER BY " + orderstr + ") as T) ORDER BY " + orderstr + ""; //非第一页
765
}
766
}
767
}
768
else //顺序[从小到大]
769![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
770
if (filter == null) //不存在WHERE子句
771![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
772
if (cpage == 1)
773![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
774
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " ORDER BY " + orderstr + ""; //第一页
775
}
776
else
777![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
778
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + key + ">(SELECT MAX(" + key + ") FROM (SELECT TOP " + (cpage - 1) * psize + " " + key + " FROM " + table_name + " ORDER BY " + orderstr + ") as T) ORDER BY " + orderstr + ""; //非第一页
779
}
780
}
781
else
782![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
783
if (cpage == 1)
784![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
785
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + filter + " ORDER BY " + orderstr + ""; //第一页
786
}
787
else
788![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
789
sqls = "SELECT TOP " + psize + " " + fieles + " FROM " + table_name + " WHERE " + filter + " AND " + key + ">(SELECT MAX(" + key + ") FROM (SELECT TOP " + (cpage - 1) * psize + " " + key + " FROM " + table_name + " WHERE " + filter + " ORDER BY " + orderstr + ") as T) ORDER BY " + orderstr + ""; //非第一页
790
}
791
}
792
}
793
//到此构造分页语句完成
794![](/Images/OutliningIndicators/InBlock.gif)
795
DataTable dt = new DataTable();
796![](/Images/OutliningIndicators/InBlock.gif)
797
if (sql == null) //客户端没有传入SQL语句
798![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
799
dt = this.getdt(sqls.ToString(), null, 0);
800
}
801
else //有传入
802![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
803
dt = this.getdt(sql, null, 0);
804
}
805![](/Images/OutliningIndicators/InBlock.gif)
806
return dt;
807![](/Images/OutliningIndicators/InBlock.gif)
808
}
809
#endregion
810![](/Images/OutliningIndicators/InBlock.gif)
811![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" SQLite分页,只适用于SQLite数据库"#region " SQLite分页,只适用于SQLite数据库"
812![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <param name="tablename">需要分页显示的表名</param>
813
/// <param name="key">表的主键ID,且只能为ID必须唯一性</param>
814
/// <param name="orderstr">排序字段如id ASC,addTimes DESC或id DESC,addTimes ASC(可以有多个排序字段)</param>
815
/// <param name="cpage">当前页</param>
816
/// <param name="psize">每页大小</param>
817
/// <param name="fieles">显示的字段列表</param>
818
/// <param name="filter">条件语句,不加where</param>
819
/// <param name="allrecordsqls">客户端传来的计算总记录的SQL语句</param>
820
/// <param name="sql">客户端发来的分页SQL语句</param>
821
/// <returns>返回内存表</returns>
822
public DataTable SQLite_dt(string tablename, string key, string orderstr, int cpage, int psize, string fieles, string filter, string allrecordsqls, string sql)
823![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
824
//先计算总记录,再计算总页数
825
string allCordSql = null;
826
if (filter == null) //不存在WHERE子句
827![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
828
allCordSql = "SELECT COUNT([" + key + "]) FROM [" + tablename + "]";
829
}
830
else
831![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
832
allCordSql = "SELECT COUNT([" + key + "]) FROM [" + tablename + "] WHERE " + filter + "";
833
}
834![](/Images/OutliningIndicators/InBlock.gif)
835
if (allrecordsqls == null)
836![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
837![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allrecord = int.Parse(this.getSca<long>(allCordSql, null, 0).ToString()); /**////返回总记录数
838
}
839
else
840![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
841![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
allrecord = int.Parse(this.getSca<long>(allrecordsqls, null, 0).ToString()); /**////返回总记录数
842
}
843![](/Images/OutliningIndicators/InBlock.gif)
844
if (allrecord % psize == 0)
845![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
846
allpage = allrecord / psize; //返回总页数
847
}
848
else
849![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
850
allpage = allrecord / psize + 1; //返回总页数
851
}
852![](/Images/OutliningIndicators/InBlock.gif)
853
StringBuilder sqls = new StringBuilder("SELECT " + fieles + " FROM [" + tablename + "]");
854![](/Images/OutliningIndicators/InBlock.gif)
855
if (filter != null) //不存在WHERE子句
856![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
857
sqls.Append(" WHERE " + filter + " "); //非第一页
858
}
859![](/Images/OutliningIndicators/InBlock.gif)
860
if (cpage == 1)
861![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
862
sqls.Append(" ORDER BY " + orderstr + " LIMIT " + psize); //第一页
863
}
864
else
865![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
866
sqls.Append(" ORDER BY " + orderstr + " LIMIT " + (psize * cpage - psize) + "," + psize + ""); //非第一页
867
}
868![](/Images/OutliningIndicators/InBlock.gif)
869
//到此构造分页语句完成 throw new Exception("出现异常:" + sqls.ToString());
870![](/Images/OutliningIndicators/InBlock.gif)
871
DataTable dt = new DataTable();
872![](/Images/OutliningIndicators/InBlock.gif)
873
if (sql == null) //客户端没有传入SQL语句
874![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
875
dt = this.getdt(sqls.ToString(), null, 0);
876
}
877
else //有传入
878![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
879
dt = this.getdt(sql, null, 0);
880
}
881![](/Images/OutliningIndicators/InBlock.gif)
882
return dt;
883
}
884
#endregion
885![](/Images/OutliningIndicators/InBlock.gif)
886![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" IDisposable 成员 "#region " IDisposable 成员 "
887![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
888
/// 强迫释放数据库连接
889
/// </summary>
890
public void Dispose()
891![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
892
Dispose(true);
893
}
894![](/Images/OutliningIndicators/InBlock.gif)
895![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
896
/// 类回收时,关闭数据库连接
897
/// </summary>
898
/// <param name="disposing"></param>
899
public void Dispose(bool disposing)
900![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
901
if (disposing)
902![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
903
CloseCon();
904
}
905
else
906![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
907
CloseCon();
908
}
909
}
910![](/Images/OutliningIndicators/InBlock.gif)
911
#endregion
912![](/Images/OutliningIndicators/InBlock.gif)
913![](/Images/OutliningIndicators/ContractedSubBlock.gif)
" showpage 显示分页数 "#region " showpage 显示分页数 "
914![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
915
/// url调整
916
/// </summary>
917
/// <param name="str">网址</param>
918
/// <returns>string</returns>
919
public string joinchar(string str)
920![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
921
if (str == "")
922![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
923
return "";
924
}
925![](/Images/OutliningIndicators/InBlock.gif)
926
if (str.IndexOf("?") < str.Length)
927![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
928
if (str.IndexOf("?") > 1)
929![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
930
if (str.IndexOf("&") < str.Length)
931![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
932
return str + "&";
933
}
934
else
935![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
936
return str;
937
}
938
}
939
else
940![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
941
return str + "?";
942
}
943
}
944![](/Images/OutliningIndicators/InBlock.gif)
945
return str;
946
}
947![](/Images/OutliningIndicators/InBlock.gif)
948![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
949
/// 显示分页数
950
/// </summary>
951
/// <param name="total">记录数</param>
952
/// <param name="pagenum">每页个数</param>
953
/// <param name="current">当前页</param>
954
/// <param name="url">页面url</param>
955
/// <param name="unit">单位[条|个|位]</param>
956
/// <returns>string</returns>
957
public string showpage(int total, int pagenum, int current, string url, string unit)
958![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
959
StringBuilder str = new StringBuilder();
960
StringBuilder str1 = new StringBuilder();
961![](/Images/OutliningIndicators/InBlock.gif)
962
int page = 1;
963
if (total % pagenum == 0)
964![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
965
page = total / pagenum;
966
}
967
else
968![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
969
page = total / pagenum + 1;
970
}
971
url = joinchar(url);
972![](/Images/OutliningIndicators/InBlock.gif)
973
if (page > 10)
974![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
975
if (current <= 5)
976![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
977
for (int i = 1; i <= 9; i++)
978![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
979
if (i == current)
980![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
981
str.Append(" <b>[" + i + "]</b> ");
982
}
983
else
984![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
985
str.Append(" <a href='" + url + "page=" + i + "'>" + i + "</a> ");
986
}
987
}
988
str.Append("
" + " <a href='" + url + "page=" + page + "'>" + page + "</a> ");
989
}
990
else if (current >= page - 4)
991![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
992
str.Append(" <a href='" + url + "page=1'>1</a>
");
993
for (int i = page - 8; i <= page; i++)
994![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
995
if (i == current)
996![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
997
str.Append(" <b>[" + i + "]</b> ");
998
}
999
else
1000![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1001
str.Append(" <a href='" + url + "page=" + i + "'>" + i + "</a> ");
1002
}
1003
}
1004
}
1005
else
1006![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1007
str.Append(" <a href='" + url + "page=1'>1</a>
");
1008
for (int i = current - 4; i <= current + 4; i++)
1009![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1010
if (i == current)
1011![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1012
str.Append(" <b>[" + i + "]</b> ");
1013
}
1014
else
1015![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1016
str.Append(" <a href='" + url + "page=" + i + "'>" + i + "</a> ");
1017
}
1018
}
1019
str.Append("
" + " <a href='" + url + "page=" + page + "'>" + page + "</a> ");
1020
}
1021
}
1022
else
1023![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1024
for (int i = 1; i <= page; i++)
1025![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1026
if (page != 1)
1027![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1028
if (i == current)
1029![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1030
str.Append(" <b>[" + i + "]</b> ");
1031
}
1032
else
1033![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1034
str.Append(" <a href='" + url + "page=" + i + "'>" + i + "</a> ");
1035
}
1036
}
1037
}
1038
}
1039
int down = current + 1;
1040
int up = current - 1;
1041
if (page > 1)
1042![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1043
if (current == 1)
1044![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1045
str1.Append(" <a href='" + url + "page=2'>下一页</a>");
1046
}
1047
else if (current == page)
1048![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1049
str1.Append(" <a href='" + url + "page=" + up.ToString() + "'>上一页</a>");
1050
}
1051
else
1052![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1053
str1.Append(" <a href='" + url + "page=" + up.ToString() + "'>上一页</a> <a href='" + url + "page=" + down.ToString() + "'>下一页</a>");
1054
}
1055
}
1056![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (page == 0)
{ page = 1; }
1057![](/Images/OutliningIndicators/InBlock.gif)
1058
return "页次:" + pagenum + "/" + current + "/" + page.ToString() + ",共<span id='rs_count'>" + total.ToString() + "</span>" + unit + " " + str1.ToString() + str.ToString();
1059
}
1060
#endregion
1061![](/Images/OutliningIndicators/InBlock.gif)
1062![](/Images/OutliningIndicators/ContractedSubBlock.gif)
"printEx 打印异常"#region "printEx 打印异常"
1063![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
1064
/// 打印异常
1065
/// </summary>
1066
/// <param name="ex">异常集合</param>
1067
/// <param name="id">是否显示错误</param>
1068
private void printEx(DbException e, int id)
1069![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1070
if (this.isclose == 1) //如果isclose的值是1说明没有执行清理工作,所以在异常收集函数里面执行清理工作
1071![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1072
CloseCon(); //热行清理
1073
}
1074
if (id == 1)
1075![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
1076
string errstr = "错误如下:<br />" + "出错信息:" + e.Message + "<br />" + "出错来源:" + e.Source + "<br />" + "程序:" + e.ErrorCode + "<br />异常方法:" + e.TargetSite;
1077
throw new Exception("出现异常:" + errstr);
1078
}
1079
}
1080
#endregion
1081![](/Images/OutliningIndicators/InBlock.gif)
1082
}
1083
#endregion
1084![](/Images/OutliningIndicators/InBlock.gif)
1085
}
1086
#endregion