用于快速开发的数据访问类1.1版

//--名称：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;
using System.Web.UI;
using System.Web;


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 !=null)
            {
                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 常用客户端脚本
        /// <summary>
        /// 弹出提示框
        /// </summary>
        public  void Alert(Page page, string msg)
        {
            string js = @"alert('" + msg + "')";
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "Alert"))
                page.ClientScript.RegisterStartupScript(page.GetType(), "Alert", js, true);
        }

        /// <summary>
        /// 弹出提示框并跳转到指定页面
        /// </summary>
        public  void AlertAndRedirect(Page page, string msg, string ToURL)
        {
            string js = @"alert('{0}');window.location.replace('{1}');";
            js = string.Format(js, msg, ToURL);
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "AlertAndRedirect"))
                page.ClientScript.RegisterStartupScript(page.GetType(), "AlertAndRedirect", js, true);
        }

        /// <summary>
        /// 返回历史
        /// </summary>
        public  void GoHistory(Page page, int value)
        {
            string js = @"history.go('{0}')";
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "GoHistory"))
                page.ClientScript.RegisterStartupScript(page.GetType(), "GoHistory", string.Format(js, value), true);
        }

        /// <summary>
        /// 关闭并使父窗口根据URL刷新
        /// </summary>
        public  void RefreshParent(Page page, string url)
        {
            string js = @"window.openner.location.href='{0}'; window.close();";
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "RefreshParent"))
                page.ClientScript.RegisterStartupScript(page.GetType(), "RefreshParent", string.Format(js, url), true);
        }

        //// <summary> 
        /// 打开指定大小的新窗体 
        /// </summary> 
        /// <param name="url">地址</param> 
        /// <param name="width">宽</param> 
        /// <param name="heigth">高</param> 
        /// <param name="top">头位置</param> 
        /// <param name="left">左位置</param> 
        /// <param name="scoll">是否带滚动条</param>
        public  void OpenWebFormSize(string url, int width, int heigth, int top, int left, bool scoll, Page page)
        {

            string js = @"window.open('" + url + @"','','height=" + heigth + ",width=" + width + ",top=" + top + ",left=" + left + "," +
                         "location=no,menubar=no,resizable=no,scrollbars=" + (scoll ? "yes" : "no") + ",status=yes,titlebar=no,toolbar=no,directories=no');";
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "OpenWebFormSize"))
            {
                page.ClientScript.RegisterStartupScript(page.GetType(), "OpenWebFormSize", js, true);
                //HttpContext.Current.Server.Transfer
            }
        }

        public void OpenModalDialog(string ModalDialogURL, string parentwin, Page page)
        {
            string js = @"window.showModalDialog('{0}','{1}');";
            js = string.Format(js, ModalDialogURL, parentwin);
            //if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "OpenModalDialog"))
            //    page.ClientScript.RegisterStartupScript(page.GetType(), "OpenModalDialog", js, true);
            HttpContext.Current.Response.Write("<script>" + js + "</script>");
        }
        /// <summary>
        /// 转向指定路径
        /// </summary>
        public void TransferURL(Page page, string URL)
        {
            string js = @"window.location.href='{0}'";
            js = string.Format(js, URL);
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "TransferURL"))
            {
                page.ClientScript.RegisterStartupScript(page.GetType(), "TransferURL", js, true);
            }
        }

        /// <summary>
        /// 执行JS
        /// </summary>
        public void WriteJs(Page page, string js)
        {
            page.ClientScript.RegisterStartupScript(page.GetType(), "WriteJs", js, true);
        }

        /// <summary>
        /// 关闭窗口
        /// </summary>
        public void CloseWindow(Page page)
        {
            string js = @"window.close()";
            if (!page.ClientScript.IsStartupScriptRegistered(page.GetType(), "CloseWindow"))
            {
                page.ClientScript.RegisterStartupScript(page.GetType(), "CloseWindow", js, true);
            }
        }
        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            if(Conn != null)
                Conn.Dispose();
            if (tran != null)
                tran.Dispose();
        }

        #endregion
    }
}

1.1版本新增常用脚本操作.