代码
1
//
--名称:SQL数据库访问基类
2 // --功能:一般ADO.NET原理,数据装箱坼箱及通用数据库控件绑定
3 // --背景:快速开发,此类供页面直接调用
4
5 using System;
6 using System.Data;
7 using System.Data.SqlClient;
8 using System.Collections.Generic;
9 using System.Text;
10 using System.Configuration;
11 using System.Web.UI.WebControls;
12
13
14 namespace R.Lee
15 {
16 /// <summary>
17 /// 数据库访问基类(for SQL)
18 /// </summary>
19 public class DBHelper:IDisposable
20 {
21 #region 成员
22 private SqlConnection Conn = null ;
23 private SqlTransaction tran = null ;
24 /// <summary>
25 /// 事务标识
26 /// </summary>
27 public bool IsTran { get ; set ; }
28 #endregion
29
30 #region 构造函数,SqlConnection对象初始化
31 public DBHelper()
32 {
33 Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString);
34 }
35
36 public DBHelper( string ConnectionKey)
37 {
38 Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionKey].ConnectionString);
39 }
40 #endregion
41
42 #region 数据库事务
43 /// <summary>
44 /// 事务开始
45 /// </summary>
46 public void BeginTran()
47 {
48 OpenDB();
49 tran = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
50 IsTran = true ;
51 }
52
53 /// <summary>
54 /// 回滚事务
55 /// </summary>
56 public void RollbackTran()
57 {
58 tran.Rollback();
59 IsTran = false ;
60 }
61
62 /// <summary>
63 /// 提交事务
64 /// </summary>
65 public void CommitTran()
66 {
67 tran.Commit();
68 IsTran = false ;
69 }
70 #endregion
71
72 #region SqlParameter对象创建
73
74 private SqlParameter CreateSqlParameter( string paraName,DbType paraType, int paraSize, ParameterDirection paraDirection, object paraValue)
75 {
76 SqlParameter para = new SqlParameter();
77 para.DbType = paraType;
78 para.Direction = paraDirection;
79 para.ParameterName = paraName;
80 if (paraSize > 0 )
81 {
82 para.Size = paraSize;
83 }
84 para.Value = paraValue;
85 return para;
86 }
87
88 public SqlParameter CreateInSqlParameter( string paraName, DbType paraType, object paraValue)
89 {
90 return CreateSqlParameter(paraName, paraType, 0 , ParameterDirection.Input, paraValue);
91 }
92
93 public SqlParameter CreateInSqlParameter( string paraName, DbType paraType, int paraSize, object paraValue)
94 {
95 return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Input, paraValue);
96 }
97
98 public SqlParameter CreateOutSqlParameter( string paraName, DbType paraType, object paraValue)
99 {
100 return CreateSqlParameter(paraName, paraType, 0 , ParameterDirection.Output, paraValue);
101 }
102
103 public SqlParameter CreateOutSqlParameter( string paraName, DbType paraType, int paraSize, object paraValue)
104 {
105 return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Output, paraValue);
106 }
107 #endregion
108
109 #region 常用ADO.NET方法
110 /// <summary>
111 /// OpenDB
112 /// </summary>
113 private void OpenDB()
114 {
115 if (Conn.State != ConnectionState.Open)
116 {
117 try
118 {
119 Conn.Open();
120 }
121 catch (SqlException ex)
122 {
123 throw ex;
124 }
125 }
126 }
127 /// <summary>
128 /// 初始化一个SqlCommand对象
129 /// </summary>
130 private void CreateCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
131 {
132 if (IsTran)
133 {
134 cmd.Transaction = tran;
135 }
136 else
137 {
138 OpenDB();
139
140 }
141 cmd.Connection = Conn;
142 cmd.CommandType = cmdType;
143 cmd.CommandText = cmdText;
144 if (SqlParas.Length > - 1 )
145 {
146 foreach (SqlParameter p in SqlParas)
147 {
148 cmd.Parameters.Add(p);
149 }
150 }
151 }
152
153 /// <summary>
154 /// 执行SQL返回一个DataSet
155 /// </summary>
156 public DataSet ExecuteQuery(CommandType cmdType, string cmdText,SqlParameter[] SqlParas)
157 {
158 using (SqlCommand cmd = new SqlCommand())
159 {
160 CreateCommand(cmd, cmdType, cmdText, SqlParas);
161 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
162 {
163 DataSet ds = new DataSet();
164 da.Fill(ds);
165 return ds;
166 }
167 }
168 }
169
170 /// <summary>
171 /// 执行SQL返回受影响的行数
172 /// </summary>
173 public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
174 {
175 using (SqlCommand cmd = new SqlCommand())
176 {
177 CreateCommand(cmd, cmdType, cmdText, SqlParas);
178 return cmd.ExecuteNonQuery();
179 }
180 }
181
182 /// <summary>
183 /// 重载一:执行SQL返回第一行第一列的值
184 /// </summary>
185 public object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
186 {
187 using (SqlCommand cmd = new SqlCommand())
188 {
189 CreateCommand(cmd, cmdType, cmdText, SqlParas);
190 return cmd.ExecuteScalar();
191 }
192 }
193
194 /// <summary>
195 /// 重载二:执行SQL返回第一行第一列的值,可传参取代返回值为NULL的情况
196 /// </summary>
197 public string ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas, string WhenNull)
198 {
199 using (SqlCommand cmd = new SqlCommand())
200 {
201 CreateCommand(cmd, cmdType, cmdText, SqlParas);
202 object result = cmd.ExecuteScalar();
203 return result == null ? WhenNull:result.ToString();
204 }
205 }
206
207 /// <summary>
208 /// 执行一段SQL,返回一个DataReader对象
209 /// </summary>
210 public SqlDataReader ExecuteDataReader(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
211 {
212 using (SqlCommand cmd = new SqlCommand())
213 {
214 CreateCommand(cmd, cmdType, cmdText, SqlParas);
215 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
216 }
217 }
218
219 /// <summary>
220 /// 常用分页方法
221 /// </summary>
222 /// <param name="PageSize"> 页面大小 </param>
223 /// <param name="RecordCount"> 记录总量 </param>
224 /// <param name="CurruntPageIndex"> 当前位置 </param>
225 /// <param name="TableName"> 表名/视图名 </param>
226 /// <param name="Condition"> 查询条件 </param>
227 /// <param name="IsAsc"> 是否升序排序 </param>
228 /// <param name="OrderBy"> 按哪些字段排序 </param>
229 /// <returns></returns>
230 private SqlDataReader GetPageSql( string condition, Int16 pageSize, Int16 pageIndex, string tbNames, string sortNames, bool sortType)
231 {
232 System.Text.StringBuilder PageSql = new System.Text.StringBuilder();
233 string tbname, tbsortname, type;
234 type = sortType ? " ASC " : " DESC " ;
235 tbname = tbNames.ToUpper().IndexOf( " SELECT " ) >= 0 ? " ( " + tbNames + " ) " + " as DBHelper " : tbNames + " as DBHelper " ;
236 tbsortname = tbNames.ToUpper().IndexOf( " SELECT " ) >= 0 ? " ( " + tbNames + " ) as DBHelperID " : tbNames + " as DBHelperID " ;
237 if (pageIndex == 1 )
238 {
239 PageSql.Append( " select top " + pageSize.ToString() + " DBHelper.* from " + tbname + ( ! string .IsNullOrEmpty(condition) ? " where " + condition : string .Empty) + " order by " + sortNames + " " + type);
240 }
241 else
242 {
243 PageSql.AppendFormat( " Select top {0} DBHelper.* from " , pageSize);
244 PageSql.AppendFormat( " {0} " , tbname);
245 PageSql.AppendFormat( " where DBHelper.{0} not in(select top {1} DBHelperID.{0} " ,
246 sortNames.Substring(sortNames.LastIndexOf( " , " ) + 1 , sortNames.Length - sortNames.LastIndexOf( " , " ) - 1 ),
247 pageSize * (pageIndex - 1 ));
248 PageSql.AppendFormat( " from {0} " , tbsortname);
249 if ( ! string .IsNullOrEmpty(condition))
250 {
251 PageSql.AppendFormat( " where {0} order by {1} {2}) and {0} " , condition, sortNames, type);
252 }
253 else
254 {
255 PageSql.AppendFormat( " order by {0} {1}) " , sortNames, type);
256 }
257 PageSql.AppendFormat( " order by {0} {1} " , sortNames, type);
258 }
259 return ExecuteDataReader(CommandType.Text, PageSql.ToString(), null );
260 }
261
262 /// <summary>
263 /// 手动关闭数据库连接对象
264 /// </summary>
265 public void CloseDB()
266 {
267 if ( ! object .Equals(Conn, null ) && Conn.State != ConnectionState.Closed)
268 {
269 Conn.Close();
270 }
271 }
272 #endregion
273
274 #region 数据类型转换
275
276 public string ToStr( object obj)
277 {
278 if ( object .Equals(obj, DBNull.Value) || string .IsNullOrEmpty(obj.ToString()))
279 return "" ;
280 else
281 return obj.ToString();
282 }
283
284 public int ToInt( object obj)
285 {
286 if ( object .Equals(obj,DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
287 return 0 ;
288 else
289 return Convert.ToInt32(obj);
290 }
291
292 public Int16 ToInt16( object obj)
293 {
294 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
295 return 0 ;
296 else
297 return Convert.ToInt16(obj);
298 }
299
300 public double ToDouble( object obj)
301 {
302 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
303 return 0 ;
304 else
305 return Convert.ToDouble(obj);
306 }
307
308 public Single ToSingle( object obj)
309 {
310 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
311 return 0 ;
312 else
313 return Convert.ToSingle(obj);
314 }
315
316 public bool ToBool( object obj)
317 {
318 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ))
319 return false ;
320 else
321 return Convert.ToBoolean(obj);
322 }
323
324 public DateTime ToDateTime( object obj)
325 {
326 try
327 {
328 DateTime dt;
329 DateTime.TryParse(Convert.ToString(obj), out dt);
330 return dt;
331 }
332 catch
333 {
334 return DateTime.MinValue;
335 }
336 }
337
338 public DateTime ? ToNullDate( object obj)
339 {
340 if ( object .Equals(obj, DBNull.Value))
341 return null ;
342 else
343 try
344 {
345 DateTime dt;
346 DateTime.TryParse(Convert.ToString(obj), out dt);
347 return dt;
348 }
349 catch
350 {
351 return null ;
352 }
353 }
354
355 public int ? ToNullInt( object obj)
356 {
357 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
358 return null ;
359 else
360 return Convert.ToInt32(obj);
361 }
362
363 public Int16 ? ToNullInt16( object obj)
364 {
365 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
366 return null ;
367 else
368 return Convert.ToInt16(obj);
369 }
370
371 public double ? ToNulldouble( object obj)
372 {
373 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
374 return null ;
375 else
376 return Convert.ToDouble(obj);
377 }
378
379 public Single ? ToNullSingle( object obj)
380 {
381 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
382 return null ;
383 else
384 return Convert.ToSingle(obj);
385 }
386
387 #endregion
388
389 #region 常用控件数据绑定
390 public enum SelType
391 {
392 ByValue,
393 ByText
394 }
395
396 /// <summary>
397 /// 列表型数据控件绑定
398 /// </summary>
399 public void ListBind(ListControl LstCtrl, object Lst)
400 {
401 LstCtrl.DataSource = Lst;
402 LstCtrl.DataBind();
403 }
404
405 /// <summary>
406 /// 绑定GridView
407 /// </summary>
408 public void GrdBind(GridView grdView, object Lst)
409 {
410 grdView.DataSource = Lst;
411 grdView.DataBind();
412 }
413 /// <summary>
414 /// 绑定GridView,并为指定的一列加上序号
415 /// </summary>
416 public void GrdBind(GridView grdView, object Lst, int InsertNo)
417 {
418 GrdBind(grdView, Lst);
419 for ( int i = 0 ; i < grdView.Rows.Count; i ++ )
420 {
421 grdView.Rows[i].Cells[InsertNo].Text = (i + 1 ).ToString();
422 }
423 }
424
425 /// <summary>
426 /// 绑定DropDownList
427 /// </summary>
428 public void DdlBind(DropDownList ddlList, object Lst)
429 {
430 ddlList.DataSource = Lst;
431 ddlList.DataBind();
432 }
433 /// <summary>
434 /// 绑定DropDownList,指定文本及值的绑定项
435 /// </summary>
436 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField)
437 {
438 ddlList.DataSource = ddlList;
439 ddlList.DataTextField = TextField;
440 ddlList.DataValueField = ValueField;
441 ddlList.DataBind();
442 }
443 /// <summary>
444 /// 绑定DropDownList,指定文本及值的绑定项,插入一个名为defaultStr的默认项
445 /// </summary>
446 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField, string defaultStr)
447 {
448 DdlBind(ddlList, Lst, TextField, ValueField);
449 ddlList.Items.Insert( 0 , defaultStr);
450 }
451 /// <summary>
452 /// 绑定DropDownList,指定文本及值的绑定项,使DropDownList选择默认的值
453 /// </summary>
454 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField,SelType FindType, string FindStr)
455 {
456 DdlBind(ddlList, Lst, TextField, ValueField);
457 int selectIndex = - 1 ;
458 for ( int i = 0 ; i < ddlList.Items.Count; i ++ )
459 {
460 switch (FindType)
461 {
462 case SelType.ByText:
463 if (ddlList.Items[i].Text == FindStr)
464 {
465 selectIndex = i;
466 }
467 break ;
468 case SelType.ByValue:
469 if (ddlList.Items[i].Value == FindStr)
470 {
471 selectIndex = i;
472 }
473 break ;
474 }
475 if (selectIndex > - 1 )
476 {
477 ddlList.SelectedIndex = selectIndex;
478 break ;
479 }
480 }
481 }
482
483 #endregion
484
485 #region IDisposable 成员
486
487 public void Dispose()
488 {
489 if (Conn != null )
490 Conn.Dispose();
491 if (tran != null )
492 tran.Dispose();
493 }
494
495 #endregion
496 }
497 }
498
2 // --功能:一般ADO.NET原理,数据装箱坼箱及通用数据库控件绑定
3 // --背景:快速开发,此类供页面直接调用
4
5 using System;
6 using System.Data;
7 using System.Data.SqlClient;
8 using System.Collections.Generic;
9 using System.Text;
10 using System.Configuration;
11 using System.Web.UI.WebControls;
12
13
14 namespace R.Lee
15 {
16 /// <summary>
17 /// 数据库访问基类(for SQL)
18 /// </summary>
19 public class DBHelper:IDisposable
20 {
21 #region 成员
22 private SqlConnection Conn = null ;
23 private SqlTransaction tran = null ;
24 /// <summary>
25 /// 事务标识
26 /// </summary>
27 public bool IsTran { get ; set ; }
28 #endregion
29
30 #region 构造函数,SqlConnection对象初始化
31 public DBHelper()
32 {
33 Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString);
34 }
35
36 public DBHelper( string ConnectionKey)
37 {
38 Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionKey].ConnectionString);
39 }
40 #endregion
41
42 #region 数据库事务
43 /// <summary>
44 /// 事务开始
45 /// </summary>
46 public void BeginTran()
47 {
48 OpenDB();
49 tran = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
50 IsTran = true ;
51 }
52
53 /// <summary>
54 /// 回滚事务
55 /// </summary>
56 public void RollbackTran()
57 {
58 tran.Rollback();
59 IsTran = false ;
60 }
61
62 /// <summary>
63 /// 提交事务
64 /// </summary>
65 public void CommitTran()
66 {
67 tran.Commit();
68 IsTran = false ;
69 }
70 #endregion
71
72 #region SqlParameter对象创建
73
74 private SqlParameter CreateSqlParameter( string paraName,DbType paraType, int paraSize, ParameterDirection paraDirection, object paraValue)
75 {
76 SqlParameter para = new SqlParameter();
77 para.DbType = paraType;
78 para.Direction = paraDirection;
79 para.ParameterName = paraName;
80 if (paraSize > 0 )
81 {
82 para.Size = paraSize;
83 }
84 para.Value = paraValue;
85 return para;
86 }
87
88 public SqlParameter CreateInSqlParameter( string paraName, DbType paraType, object paraValue)
89 {
90 return CreateSqlParameter(paraName, paraType, 0 , ParameterDirection.Input, paraValue);
91 }
92
93 public SqlParameter CreateInSqlParameter( string paraName, DbType paraType, int paraSize, object paraValue)
94 {
95 return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Input, paraValue);
96 }
97
98 public SqlParameter CreateOutSqlParameter( string paraName, DbType paraType, object paraValue)
99 {
100 return CreateSqlParameter(paraName, paraType, 0 , ParameterDirection.Output, paraValue);
101 }
102
103 public SqlParameter CreateOutSqlParameter( string paraName, DbType paraType, int paraSize, object paraValue)
104 {
105 return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Output, paraValue);
106 }
107 #endregion
108
109 #region 常用ADO.NET方法
110 /// <summary>
111 /// OpenDB
112 /// </summary>
113 private void OpenDB()
114 {
115 if (Conn.State != ConnectionState.Open)
116 {
117 try
118 {
119 Conn.Open();
120 }
121 catch (SqlException ex)
122 {
123 throw ex;
124 }
125 }
126 }
127 /// <summary>
128 /// 初始化一个SqlCommand对象
129 /// </summary>
130 private void CreateCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
131 {
132 if (IsTran)
133 {
134 cmd.Transaction = tran;
135 }
136 else
137 {
138 OpenDB();
139
140 }
141 cmd.Connection = Conn;
142 cmd.CommandType = cmdType;
143 cmd.CommandText = cmdText;
144 if (SqlParas.Length > - 1 )
145 {
146 foreach (SqlParameter p in SqlParas)
147 {
148 cmd.Parameters.Add(p);
149 }
150 }
151 }
152
153 /// <summary>
154 /// 执行SQL返回一个DataSet
155 /// </summary>
156 public DataSet ExecuteQuery(CommandType cmdType, string cmdText,SqlParameter[] SqlParas)
157 {
158 using (SqlCommand cmd = new SqlCommand())
159 {
160 CreateCommand(cmd, cmdType, cmdText, SqlParas);
161 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
162 {
163 DataSet ds = new DataSet();
164 da.Fill(ds);
165 return ds;
166 }
167 }
168 }
169
170 /// <summary>
171 /// 执行SQL返回受影响的行数
172 /// </summary>
173 public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
174 {
175 using (SqlCommand cmd = new SqlCommand())
176 {
177 CreateCommand(cmd, cmdType, cmdText, SqlParas);
178 return cmd.ExecuteNonQuery();
179 }
180 }
181
182 /// <summary>
183 /// 重载一:执行SQL返回第一行第一列的值
184 /// </summary>
185 public object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
186 {
187 using (SqlCommand cmd = new SqlCommand())
188 {
189 CreateCommand(cmd, cmdType, cmdText, SqlParas);
190 return cmd.ExecuteScalar();
191 }
192 }
193
194 /// <summary>
195 /// 重载二:执行SQL返回第一行第一列的值,可传参取代返回值为NULL的情况
196 /// </summary>
197 public string ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas, string WhenNull)
198 {
199 using (SqlCommand cmd = new SqlCommand())
200 {
201 CreateCommand(cmd, cmdType, cmdText, SqlParas);
202 object result = cmd.ExecuteScalar();
203 return result == null ? WhenNull:result.ToString();
204 }
205 }
206
207 /// <summary>
208 /// 执行一段SQL,返回一个DataReader对象
209 /// </summary>
210 public SqlDataReader ExecuteDataReader(CommandType cmdType, string cmdText, SqlParameter[] SqlParas)
211 {
212 using (SqlCommand cmd = new SqlCommand())
213 {
214 CreateCommand(cmd, cmdType, cmdText, SqlParas);
215 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
216 }
217 }
218
219 /// <summary>
220 /// 常用分页方法
221 /// </summary>
222 /// <param name="PageSize"> 页面大小 </param>
223 /// <param name="RecordCount"> 记录总量 </param>
224 /// <param name="CurruntPageIndex"> 当前位置 </param>
225 /// <param name="TableName"> 表名/视图名 </param>
226 /// <param name="Condition"> 查询条件 </param>
227 /// <param name="IsAsc"> 是否升序排序 </param>
228 /// <param name="OrderBy"> 按哪些字段排序 </param>
229 /// <returns></returns>
230 private SqlDataReader GetPageSql( string condition, Int16 pageSize, Int16 pageIndex, string tbNames, string sortNames, bool sortType)
231 {
232 System.Text.StringBuilder PageSql = new System.Text.StringBuilder();
233 string tbname, tbsortname, type;
234 type = sortType ? " ASC " : " DESC " ;
235 tbname = tbNames.ToUpper().IndexOf( " SELECT " ) >= 0 ? " ( " + tbNames + " ) " + " as DBHelper " : tbNames + " as DBHelper " ;
236 tbsortname = tbNames.ToUpper().IndexOf( " SELECT " ) >= 0 ? " ( " + tbNames + " ) as DBHelperID " : tbNames + " as DBHelperID " ;
237 if (pageIndex == 1 )
238 {
239 PageSql.Append( " select top " + pageSize.ToString() + " DBHelper.* from " + tbname + ( ! string .IsNullOrEmpty(condition) ? " where " + condition : string .Empty) + " order by " + sortNames + " " + type);
240 }
241 else
242 {
243 PageSql.AppendFormat( " Select top {0} DBHelper.* from " , pageSize);
244 PageSql.AppendFormat( " {0} " , tbname);
245 PageSql.AppendFormat( " where DBHelper.{0} not in(select top {1} DBHelperID.{0} " ,
246 sortNames.Substring(sortNames.LastIndexOf( " , " ) + 1 , sortNames.Length - sortNames.LastIndexOf( " , " ) - 1 ),
247 pageSize * (pageIndex - 1 ));
248 PageSql.AppendFormat( " from {0} " , tbsortname);
249 if ( ! string .IsNullOrEmpty(condition))
250 {
251 PageSql.AppendFormat( " where {0} order by {1} {2}) and {0} " , condition, sortNames, type);
252 }
253 else
254 {
255 PageSql.AppendFormat( " order by {0} {1}) " , sortNames, type);
256 }
257 PageSql.AppendFormat( " order by {0} {1} " , sortNames, type);
258 }
259 return ExecuteDataReader(CommandType.Text, PageSql.ToString(), null );
260 }
261
262 /// <summary>
263 /// 手动关闭数据库连接对象
264 /// </summary>
265 public void CloseDB()
266 {
267 if ( ! object .Equals(Conn, null ) && Conn.State != ConnectionState.Closed)
268 {
269 Conn.Close();
270 }
271 }
272 #endregion
273
274 #region 数据类型转换
275
276 public string ToStr( object obj)
277 {
278 if ( object .Equals(obj, DBNull.Value) || string .IsNullOrEmpty(obj.ToString()))
279 return "" ;
280 else
281 return obj.ToString();
282 }
283
284 public int ToInt( object obj)
285 {
286 if ( object .Equals(obj,DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
287 return 0 ;
288 else
289 return Convert.ToInt32(obj);
290 }
291
292 public Int16 ToInt16( object obj)
293 {
294 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
295 return 0 ;
296 else
297 return Convert.ToInt16(obj);
298 }
299
300 public double ToDouble( object obj)
301 {
302 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
303 return 0 ;
304 else
305 return Convert.ToDouble(obj);
306 }
307
308 public Single ToSingle( object obj)
309 {
310 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
311 return 0 ;
312 else
313 return Convert.ToSingle(obj);
314 }
315
316 public bool ToBool( object obj)
317 {
318 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ))
319 return false ;
320 else
321 return Convert.ToBoolean(obj);
322 }
323
324 public DateTime ToDateTime( object obj)
325 {
326 try
327 {
328 DateTime dt;
329 DateTime.TryParse(Convert.ToString(obj), out dt);
330 return dt;
331 }
332 catch
333 {
334 return DateTime.MinValue;
335 }
336 }
337
338 public DateTime ? ToNullDate( object obj)
339 {
340 if ( object .Equals(obj, DBNull.Value))
341 return null ;
342 else
343 try
344 {
345 DateTime dt;
346 DateTime.TryParse(Convert.ToString(obj), out dt);
347 return dt;
348 }
349 catch
350 {
351 return null ;
352 }
353 }
354
355 public int ? ToNullInt( object obj)
356 {
357 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
358 return null ;
359 else
360 return Convert.ToInt32(obj);
361 }
362
363 public Int16 ? ToNullInt16( object obj)
364 {
365 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
366 return null ;
367 else
368 return Convert.ToInt16(obj);
369 }
370
371 public double ? ToNulldouble( object obj)
372 {
373 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
374 return null ;
375 else
376 return Convert.ToDouble(obj);
377 }
378
379 public Single ? ToNullSingle( object obj)
380 {
381 if ( object .Equals(obj, DBNull.Value) || object .Equals(obj, null ) || string .IsNullOrEmpty(obj.ToString()))
382 return null ;
383 else
384 return Convert.ToSingle(obj);
385 }
386
387 #endregion
388
389 #region 常用控件数据绑定
390 public enum SelType
391 {
392 ByValue,
393 ByText
394 }
395
396 /// <summary>
397 /// 列表型数据控件绑定
398 /// </summary>
399 public void ListBind(ListControl LstCtrl, object Lst)
400 {
401 LstCtrl.DataSource = Lst;
402 LstCtrl.DataBind();
403 }
404
405 /// <summary>
406 /// 绑定GridView
407 /// </summary>
408 public void GrdBind(GridView grdView, object Lst)
409 {
410 grdView.DataSource = Lst;
411 grdView.DataBind();
412 }
413 /// <summary>
414 /// 绑定GridView,并为指定的一列加上序号
415 /// </summary>
416 public void GrdBind(GridView grdView, object Lst, int InsertNo)
417 {
418 GrdBind(grdView, Lst);
419 for ( int i = 0 ; i < grdView.Rows.Count; i ++ )
420 {
421 grdView.Rows[i].Cells[InsertNo].Text = (i + 1 ).ToString();
422 }
423 }
424
425 /// <summary>
426 /// 绑定DropDownList
427 /// </summary>
428 public void DdlBind(DropDownList ddlList, object Lst)
429 {
430 ddlList.DataSource = Lst;
431 ddlList.DataBind();
432 }
433 /// <summary>
434 /// 绑定DropDownList,指定文本及值的绑定项
435 /// </summary>
436 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField)
437 {
438 ddlList.DataSource = ddlList;
439 ddlList.DataTextField = TextField;
440 ddlList.DataValueField = ValueField;
441 ddlList.DataBind();
442 }
443 /// <summary>
444 /// 绑定DropDownList,指定文本及值的绑定项,插入一个名为defaultStr的默认项
445 /// </summary>
446 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField, string defaultStr)
447 {
448 DdlBind(ddlList, Lst, TextField, ValueField);
449 ddlList.Items.Insert( 0 , defaultStr);
450 }
451 /// <summary>
452 /// 绑定DropDownList,指定文本及值的绑定项,使DropDownList选择默认的值
453 /// </summary>
454 public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField,SelType FindType, string FindStr)
455 {
456 DdlBind(ddlList, Lst, TextField, ValueField);
457 int selectIndex = - 1 ;
458 for ( int i = 0 ; i < ddlList.Items.Count; i ++ )
459 {
460 switch (FindType)
461 {
462 case SelType.ByText:
463 if (ddlList.Items[i].Text == FindStr)
464 {
465 selectIndex = i;
466 }
467 break ;
468 case SelType.ByValue:
469 if (ddlList.Items[i].Value == FindStr)
470 {
471 selectIndex = i;
472 }
473 break ;
474 }
475 if (selectIndex > - 1 )
476 {
477 ddlList.SelectedIndex = selectIndex;
478 break ;
479 }
480 }
481 }
482
483 #endregion
484
485 #region IDisposable 成员
486
487 public void Dispose()
488 {
489 if (Conn != null )
490 Conn.Dispose();
491 if (tran != null )
492 tran.Dispose();
493 }
494
495 #endregion
496 }
497 }
498