论坛首页 编程语言技术论坛

针对小外包写的一个数据库访问基类

浏览 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
    }
}

 

   发表时间:2010-06-21  
不讨论这种东西有没有用,SqlParameter 可以让你的代码免于SQL Injection
0 请登录后投票
论坛首页 编程语言技术版

跳转论坛:
Global site tag (gtag.js) - Google Analytics