代码
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
浙公网安备 33010602011771号