浏览 5308 次
锁定老帖子 主题:针对小外包写的一个数据库访问基类
精华帖 (0) :: 良好帖 (0) :: 新手帖 (1) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-02-21
最后修改:2010-02-21
//--名称:SQL数据库访问基类 //--功能:一般ADO.NET原理,数据装箱坼箱及通用数据库控件绑定 //--背景:针对一些个人形式外包,要求快速开发的数据库中小型项目特写此类供页面直接调用 //--创建人:KingWei //--创建日期:2010-02-20 using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Web.UI.WebControls; namespace KingWei { /// <summary> /// 数据库访问基类(for SQL) /// </summary> public class DBHelper:IDisposable { #region 成员 private SqlConnection Conn = null; private SqlTransaction tran = null; /// <summary> /// 事务标识 /// </summary> public bool IsTran { get; set; } #endregion #region 构造函数,SqlConnection对象初始化 public DBHelper() { Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); } public DBHelper(string ConnectionKey) { Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionKey].ConnectionString); } #endregion #region 数据库事务 /// <summary> /// 事务开始 /// </summary> public void BeginTran() { OpenDB(); tran = Conn.BeginTransaction(IsolationLevel.ReadCommitted); IsTran = true; } /// <summary> /// 回滚事务 /// </summary> public void RollbackTran() { tran.Rollback(); IsTran = false; } /// <summary> /// 提交事务 /// </summary> public void CommitTran() { tran.Commit(); IsTran = false; } #endregion #region SqlParameter对象创建 private SqlParameter CreateSqlParameter(string paraName,DbType paraType,int paraSize, ParameterDirection paraDirection, object paraValue) { SqlParameter para = new SqlParameter(); para.DbType = paraType; para.Direction = paraDirection; para.ParameterName = paraName; if (paraSize > 0) { para.Size = paraSize; } para.Value = paraValue; return para; } public SqlParameter CreateInSqlParameter(string paraName, DbType paraType, object paraValue) { return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Input, paraValue); } public SqlParameter CreateInSqlParameter(string paraName, DbType paraType,int paraSize, object paraValue) { return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Input, paraValue); } public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, object paraValue) { return CreateSqlParameter(paraName, paraType, 0, ParameterDirection.Output, paraValue); } public SqlParameter CreateOutSqlParameter(string paraName, DbType paraType, int paraSize, object paraValue) { return CreateSqlParameter(paraName, paraType, paraSize, ParameterDirection.Output, paraValue); } #endregion #region 常用ADO.NET方法 /// <summary> /// OpenDB /// </summary> private void OpenDB() { if (Conn.State != ConnectionState.Open) { try { Conn.Open(); } catch (SqlException ex) { throw ex; } } } /// <summary> /// 初始化一个SqlCommand对象 /// </summary> private void CreateCommand(SqlCommand cmd,CommandType cmdType, string cmdText, SqlParameter[] SqlParas) { if (IsTran) { cmd.Transaction = tran; } else { OpenDB(); } cmd.Connection = Conn; cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (SqlParas.Length > -1) { foreach (SqlParameter p in SqlParas) { cmd.Parameters.Add(p); } } } /// <summary> /// 执行SQL返回一个DataSet /// </summary> public DataSet ExecuteQuery(CommandType cmdType,string cmdText,SqlParameter[] SqlParas) { using (SqlCommand cmd = new SqlCommand()) { CreateCommand(cmd, cmdType, cmdText, SqlParas); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } /// <summary> /// 执行SQL返回受影响的行数 /// </summary> public int ExecuteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] SqlParas) { using (SqlCommand cmd = new SqlCommand()) { CreateCommand(cmd, cmdType, cmdText, SqlParas); return cmd.ExecuteNonQuery(); } } /// <summary> /// 重载一:执行SQL返回第一行第一列的值 /// </summary> public object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas) { using (SqlCommand cmd = new SqlCommand()) { CreateCommand(cmd, cmdType, cmdText, SqlParas); return cmd.ExecuteScalar(); } } /// <summary> /// 重载二:执行SQL返回第一行第一列的值,可传参取代返回值为NULL的情况 /// </summary> public string ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] SqlParas,string WhenNull) { using (SqlCommand cmd = new SqlCommand()) { CreateCommand(cmd, cmdType, cmdText, SqlParas); object result = cmd.ExecuteScalar(); return result == null?WhenNull:result.ToString(); } } /// <summary> /// 执行一段SQL,返回一个DataReader对象 /// </summary> public SqlDataReader ExecuteDataReader(CommandType cmdType, string cmdText, SqlParameter[] SqlParas) { using (SqlCommand cmd = new SqlCommand()) { CreateCommand(cmd, cmdType, cmdText, SqlParas); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } } /// <summary> /// 常用分页方法 /// </summary> /// <param name="PageSize">页面大小</param> /// <param name="RecordCount">记录总量</param> /// <param name="CurruntPageIndex">当前位置</param> /// <param name="TableName">表名/视图名</param> /// <param name="Condition">查询条件</param> /// <param name="IsAsc">是否升序排序</param> /// <param name="OrderBy">按哪些字段排序</param> /// <returns></returns> private SqlDataReader GetPageSql(string condition, Int16 pageSize, Int16 pageIndex, string tbNames, string sortNames, bool sortType) { System.Text.StringBuilder PageSql = new System.Text.StringBuilder(); string tbname, tbsortname, type; type = sortType ? "ASC" : "DESC"; tbname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ")" + " as DBHelper" : tbNames + " as DBHelper"; tbsortname = tbNames.ToUpper().IndexOf("SELECT") >= 0 ? "(" + tbNames + ") as DBHelperID" : tbNames + " as DBHelperID"; if (pageIndex == 1) { PageSql.Append("select top " + pageSize.ToString() + " DBHelper.* from " + tbname + (!string.IsNullOrEmpty(condition) ? " where " + condition : string.Empty) + " order by " + sortNames + " " + type); } else { PageSql.AppendFormat("Select top {0} DBHelper.* from ", pageSize); PageSql.AppendFormat("{0}", tbname); PageSql.AppendFormat(" where DBHelper.{0} not in(select top {1} DBHelperID.{0}", sortNames.Substring(sortNames.LastIndexOf(",") + 1, sortNames.Length - sortNames.LastIndexOf(",") - 1), pageSize * (pageIndex - 1)); PageSql.AppendFormat(" from {0}", tbsortname); if (!string.IsNullOrEmpty(condition)) { PageSql.AppendFormat(" where {0} order by {1} {2}) and {0}", condition, sortNames, type); } else { PageSql.AppendFormat(" order by {0} {1})", sortNames, type); } PageSql.AppendFormat(" order by {0} {1}", sortNames, type); } return ExecuteDataReader(CommandType.Text, PageSql.ToString(), null); } /// <summary> /// 手动关闭数据库连接对象 /// </summary> public void CloseDB() { if (!object.Equals(Conn, null) && Conn.State != ConnectionState.Closed) { Conn.Close(); } } #endregion #region 数据类型转换 public string ToStr(object obj) { if (object.Equals(obj, DBNull.Value) || string.IsNullOrEmpty(obj.ToString())) return ""; else return obj.ToString(); } public int ToInt(object obj) { if (object.Equals(obj,DBNull.Value)||object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return 0; else return Convert.ToInt32(obj); } public Int16 ToInt16(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return 0; else return Convert.ToInt16(obj); } public double ToDouble(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return 0; else return Convert.ToDouble(obj); } public Single ToSingle(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return 0; else return Convert.ToSingle(obj); } public bool ToBool(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null)) return false; else return Convert.ToBoolean(obj); } public DateTime ToDateTime(object obj) { try { DateTime dt; DateTime.TryParse(Convert.ToString(obj), out dt); return dt; } catch { return DateTime.MinValue; } } public DateTime? ToNullDate(object obj) { if (object.Equals(obj, DBNull.Value)) return null; else try { DateTime dt; DateTime.TryParse(Convert.ToString(obj), out dt); return dt; } catch { return null; } } public int? ToNullInt(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return null; else return Convert.ToInt32(obj); } public Int16? ToNullInt16(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return null; else return Convert.ToInt16(obj); } public double? ToNulldouble(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return null; else return Convert.ToDouble(obj); } public Single? ToNullSingle(object obj) { if (object.Equals(obj, DBNull.Value) || object.Equals(obj, null) || string.IsNullOrEmpty(obj.ToString())) return null; else return Convert.ToSingle(obj); } #endregion #region 常用控件数据绑定 public enum SelType { ByValue, ByText } /// <summary> /// 列表型数据控件绑定 /// </summary> public void ListBind(ListControl LstCtrl,object Lst) { LstCtrl.DataSource = Lst; LstCtrl.DataBind(); } /// <summary> /// 绑定GridView /// </summary> public void GrdBind(GridView grdView, object Lst) { grdView.DataSource = Lst; grdView.DataBind(); } /// <summary> /// 绑定GridView,并为指定的一列加上序号 /// </summary> public void GrdBind(GridView grdView, object Lst, int InsertNo) { GrdBind(grdView, Lst); for (int i = 0; i < grdView.Rows.Count; i++) { grdView.Rows[i].Cells[InsertNo].Text = (i + 1).ToString(); } } /// <summary> /// 绑定DropDownList /// </summary> public void DdlBind(DropDownList ddlList, object Lst) { ddlList.DataSource = Lst; ddlList.DataBind(); } /// <summary> /// 绑定DropDownList,指定文本及值的绑定项 /// </summary> public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField) { ddlList.DataSource = ddlList; ddlList.DataTextField = TextField; ddlList.DataValueField = ValueField; ddlList.DataBind(); } /// <summary> /// 绑定DropDownList,指定文本及值的绑定项,插入一个名为defaultStr的默认项 /// </summary> public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField, string defaultStr) { DdlBind(ddlList, Lst, TextField, ValueField); ddlList.Items.Insert(0, defaultStr); } /// <summary> /// 绑定DropDownList,指定文本及值的绑定项,使DropDownList选择默认的值 /// </summary> public void DdlBind(DropDownList ddlList, Object Lst, string TextField, string ValueField,SelType FindType, string FindStr) { DdlBind(ddlList, Lst, TextField, ValueField); int selectIndex = -1; for (int i = 0; i < ddlList.Items.Count; i++) { switch (FindType) { case SelType.ByText: if (ddlList.Items[i].Text == FindStr) { selectIndex= i; } break; case SelType.ByValue: if (ddlList.Items[i].Value == FindStr) { selectIndex = i; } break; } if (selectIndex > -1) { ddlList.SelectedIndex = selectIndex; break; } } } #endregion #region IDisposable 成员 public void Dispose() { if(Conn != null) Conn.Dispose(); if (tran != null) tran.Dispose(); } #endregion } }
声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2010-06-21
不讨论这种东西有没有用,SqlParameter 可以让你的代码免于SQL Injection
|
|
返回顶楼 | |