数据库C#操作类（支持所有MSSQL语法的数据库）

这个是我经过几天做出来的，原创的是我的好伙伴【埖ó髒 QQ：3624091】，我在他的基础上做了改进，增加了分页处理方法并改进了Insert,Update,Delete三种方法的操作形式，做到了更精 简，更方便，这里我给出这个方法，并演示我的集中关键的调用类里面的形式，给大家借鉴参考

/******************************************************  
 * 项目模块：  
 * 功能描述：数据库执行控制类  
 * 创建者：埖ó髒  
 * 创建日期：2007/9/22  
 * 修改者：陈峰  
 * 修改日期：2007/9/25  
 * ***************************************************/  
using System;   
using System.Collections.Generic;   
using System.Text;   
using System.Data;   
using System.ComponentModel;   
using System.Collections;   
using System.Data.SqlClient;   
  
namespace NMJU.Web.DataSQLServer   
{   
    /// <summary>   
    /// 数据库执行控制类   
    /// </summary>   
    public class CmdFactory   
    {   
        static SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["MSSQLConnectionString"]);   
  
        public CmdFactory()   
        {   
  
        }  
 
        #region 数据库执行操作   
  
        /* 执行更新删除插入数据库操作,成功则返回true　*/  
        /// <summary>   
        /// 功能：执行更新删除插入数据库操作,成功则返回true   
        /// </summary>   
        /// <param name="strSql"></param>   
        /// <returns></returns>   
        public static bool ExecuteScalar(string strSql)   
        {   
            bool flag = false;   
            cn.Open();   
            //操作   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSql;   
            try  
            {   
                cm.Connection = cn;   
                cm.ExecuteNonQuery();   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
  
            }   
            finally  
            {   
                cn.Close();   
                flag = true;   
            }   
            return flag;   
  
        }   
        /* 执行查询数据库操作是否有记录　*/  
        /// <summary>   
        /// 功能：执行查询数据库操作是否有记录　   
        /// </summary>   
        /// <param name="strSql"></param>   
        public static bool ExecuteScalarRead(string strSql)   
        {   
            cn.Open();   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSql;   
            cm.Connection = cn;   
            try  
            {   
                SqlDataReader dr = cm.ExecuteReader();   
                if (dr.Read())   
                {   
                    return true;   
                }   
                else  
                {   
                    return false;   
                }   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cm.Dispose();   
                cn.Close();   
            }   
  
        }   
        /* 执行查询数据库操作返回多少条记录　*/  
        /// <summary>   
        /// 功能：执行查询数据库操作返回多少条记录　   
        /// </summary>   
        /// <param name="strSql"></param>   
        public static int ExecuteScalarScalarCount(string strSql)   
        {   
            cn.Open();   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSql;   
            cm.Connection = cn;   
            try  
            {   
                int count = (int)cm.ExecuteScalar();   
                return count;   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cm.Dispose();   
                cn.Close();   
            }   
  
        }   
        /* 执行查询数据库操作返回影响行数　*/  
        /// <summary>   
        /// 功能：执行查询数据库操作返回影响行数　   
        /// </summary>   
        /// <param name="strSql"></param>   
        public static int ExecuteNonQueryCount(string strSql)   
        {   
            cn.Open();   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSql;   
            cm.Connection = cn;   
            try  
            {   
                int count = (int)cm.ExecuteNonQuery();   
                return count;   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cm.Dispose();   
                cn.Close();   
            }   
  
        }   
        /*执行查询操作*/  
        /// <summary>   
        /// 功能：执行查询操作   
        /// </summary>   
        /// <param name="strSql"></param>   
        /// <param name="ds">返回数据集</param>   
        /// <returns></returns>   
        public static DataSet ExecuteSelectCmmond(string strSQL, DataSet ds)   
        {   
            cn.Open();   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSQL;   
            cm.Connection = cn;   
            SqlDataAdapter da = new SqlDataAdapter(cm);   
            try  
            {   
                da.Fill(ds);   
                return ds;   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cm.Dispose();   
                cn.Close();   
            }   
        }   
        public static DataSet ExecuteSelectCmmond(string strSQL)   
        {   
            cn.Open();   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSQL;   
            cm.Connection = cn;   
            SqlDataAdapter da = new SqlDataAdapter(cm);   
            DataSet ds = new DataSet();   
            try  
            {   
                da.Fill(ds);   
                return ds;   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cm.Dispose();   
                cn.Close();   
            }   
        }   
        /* 查询表结构操作*/  
        /// <summary>   
        ///功能： 查询表结构操作   
        /// </summary>   
        /// <param name="strSql"></param>   
        /// <param name="ds">返回数据集</param>   
        /// <returns></returns>   
        public static DataSet GetTableCol(string TableName)   
        {   
            cn.Open();   
            string strSql = "select * from [" + TableName + "] where 1> 2 ";   
            SqlCommand cm = new SqlCommand();   
            cm.CommandText = strSql;   
            cm.Connection = cn;   
            DataSet ds = new DataSet();   
  
            SqlDataAdapter da = new SqlDataAdapter(cm);   
            try  
            {   
                da.Fill(ds);   
                return ds;   
            }   
            catch (Exception ex)   
            {   
                throw new Exception(ex.Message);   
            }   
            finally  
            {   
                cn.Close();   
            }   
        }  
 
 
        #endregion  
 
        #region 数据库应用操作   
  
        /* 绑定列表*/  
        /// <summary>   
        /// 绑定列表   
        /// </summary>   
        /// <param name="TableName">表名</param>   
        /// <param name="WhereStr">条件,若条件为空，则不执行该参数</param>   
        /// <param name="OrderStr">排序,若条件为空，则不执行该参数</param>   
        /// <returns></returns>   
        public static DataSet BuildSelectTable(string TableName, string WhereStr, string OrderStr)   
        {   
            string strSQL = "";   
            DataSet ds = new DataSet();   
            strSQL = "select * from [" + TableName + "] ";   
            if (WhereStr != String.Empty)   
            {   
                strSQL += " where " + WhereStr;   
            }   
            if (OrderStr != String.Empty)   
            {   
                strSQL += " order by " + OrderStr;   
            }   
            // ExecuteClass ec = new ExecuteClass();   
            ExecuteSelectCmmond(strSQL, ds);   
            ds.Tables[0].TableName = TableName;   
            return ds;   
        }   
        public static DataSet BuildSelectTable(string TableName)   
        {   
            string strSQL = "";   
            DataSet ds = new DataSet();   
            strSQL = "select * from [" + TableName + "]";   
            // ExecuteClass ec = new ExecuteClass();   
            ExecuteSelectCmmond(strSQL, ds);   
            ds.Tables[0].TableName = TableName;   
            return ds;   
        }   
  
        /*进行insert,update,delete三纵操作*/  
        /// <summary>   
        /// 功能：进行insert,update,delete三纵操作   
        /// </summary>   
        /// <param name="htValues">字段数据的一个Hashtable</param>   
        /// <param name="WhereStr">条件语句</param>   
        /// <param name="TableName">表名</param>   
        /// <param name="operators">执行操作的标识：insert,update,delete</param>   
        /// <returns></returns>   
        public static bool BuildSQLExecute(Hashtable htValues, string WhereStr, string TableName, string Operate)   
        {   
            //构架SQL语句   
            string strSQL = "";   
            string strCol = "";   
  
  
            if (Operate.ToLower() == "insert")   
            {   
                int j = 1;   
                foreach (DictionaryEntry de in htValues) //更新的字段列   
                {   
  
                    if (j < htValues.Count)   
                    {   
  
                        strCol += de.Key + ",";   
                    }   
                    else  
                    {   
  
                        strCol += de.Key;   
                    }   
                    j++;   
                }   
  
                strSQL = "insert into [" + TableName + "] (" + strCol + ") values (";   
                j = 1;   
                foreach (DictionaryEntry de in htValues) //更新的字段列   
                {   
                    if (j < htValues.Count)   
                    {   
                        strSQL += FormatColumns(de.Value) + ",";   
                    }   
                    else  
                    {   
                        strSQL += FormatColumns(de.Value) + ")";   
                    }   
                    j++;   
  
                }   
  
            }   
            else if (Operate.ToLower() == "update")   
            {   
                strSQL = "update [" + TableName + "] set ";   
                int j = 1;   
  
                foreach (DictionaryEntry de in htValues) //更新的字段列   
                {   
                    if (j < htValues.Count)   
                    {   
                        strSQL += de.Key + "=" + FormatColumns(de.Value) + ",";   
                    }   
                    else  
                    {   
                        strSQL += de.Key + "=" + FormatColumns(de.Value);   
                    }   
                    j++;   
                }   
                if (WhereStr != String.Empty)   
                {   
                    strSQL += " where " + WhereStr;   
  
                }   
  
            }   
            else if (Operate.ToLower() == "delete")   
            {   
                strSQL = "delete from [" + TableName + "] ";   
                if (WhereStr != String.Empty)   
                {   
                    strSQL += " where " + WhereStr;   
  
                }   
            }   
            return ExecuteScalar(strSQL);   
        }   
  
        /*识别传入的字段属于什么类型，并返回是否该字段需要带单引号*/  
        /// <summary>   
        /// 功能:识别传入的字段属于什么类型，并返回是否该字段需要带单引号   
        /// </summary>   
        /// <param name="columns">字段变量</param>   
        /// <returns></returns>   
        private static string FormatColumns(object columns)   
        {   
            string rtncolumns = "";   
            switch (columns.GetType().Name.ToString())   
            {   
                //整型   
                case "Int16":   
                    rtncolumns = columns.ToString();   
  
                    break;   
                case "Int32":   
                    rtncolumns = columns.ToString();   
  
                    break;   
                case "Int64":   
                    rtncolumns = columns.ToString();   
  
                    break;   
                //浮点数   
                case "Double":   
                    rtncolumns = columns.ToString();   
  
                    break;   
                case "Float":   
                    rtncolumns = columns.ToString();   
  
                    break;   
                //字符型   
                default:   
                    rtncolumns = "'" + columns.ToString() + "'";   
                    break;   
  
            }   
  
            return rtncolumns;   
        }  
 
        #region 获取根据指定字段排序并分页查询。   
  
        /*智能返回分页SQL语句*/  
        /// <summary>   
        /// 功能:智能返回分页SQL语句   
        /// </summary>   
        /// <param name="primaryKey">主键（不能为空）</param>   
        /// <param name="queryFields">提取字段（不能为空）</param>   
        /// <param name="tableName">表（理论上允许多表）</param>   
        /// <param name="condition">条件（可以空）</param>   
        /// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>   
        /// <param name="pageSize">分页数（不能为空）</param>   
        /// <param name="pageIndex">当前页，起始为：1（不能为空）</param>   
        /// <returns></returns>   
        private static string getPageListSql(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)   
        {   
            string strTmp = ""; //---strTmp用于返回的SQL语句   
            string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = "";   
            //0：分页数量   
            //1:提取字段   
            //2:表   
            //3:条件   
            //4:主键不存在的记录   
            //5:排序   
            SqlSelect = " select top {0} {1} from {2} {3} {4} {5}";   
            //0:主键   
            //1:TOP数量,为分页数*(排序号-1)   
            //2:表   
            //3:条件   
            //4:排序   
            SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";   
  
            if (orderBy != "")   
                strOrderBy = " order by " + orderBy;   
            if (condition != "")   
                strWhere += " and " + condition;   
            int pageindexsize = (pageIndex - 1) * pageSize;   
            if (pageindexsize > 0)   
            {   
                strTop = " top " + pageindexsize.ToString();   
  
                SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy);   
  
                strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy);   
  
            }   
            else  
            {   
                strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy);   
  
            }   
            return strTmp;   
        }   
        /*获取根据指定字段排序并分页查询。DataSet*/  
        /// <summary>   
        ///功能: 获取根据指定字段排序并分页查询。DataSet   
        /// </summary>   
        /// <param name="pageSize">每页要显示的记录的数目</param>   
        /// <param name="pageIndex">要显示的页的索引</param>   
        /// <param name="tableName">要查询的数据表</param>   
        /// <param name="queryFields">要查询的字段,如果是全部字段请填写：*</param>   
        /// <param name="primaryKey">主键字段，类似排序用到</param>   
        /// <param name="orderBy">是否为升序排列：0为升序，1为降序</param>   
        /// <param name="condition">查询的筛选条件</param>   
        /// <returns>返回排序并分页查询的DataSet</returns>   
        public static DataSet GetPageDataList(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex)   
        {   
            // ExecuteClass ec = new ExecuteClass();   
  
            string strSQL = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);   
  
            return ExecuteSelectCmmond(strSQL);   
        }   
  
        /*分页查询数据记录总数获取*/  
        /// <summary>   
        ///功能: 分页查询数据记录总数获取   
        /// </summary>   
        /// <param name="_tbName">----要显示的表或多个表的连接</param>   
        /// <param name="_ID">----主表的主键</param>   
        /// <param name="_strCondition">----查询条件,不需where</param>           
        /// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>   
        /// <returns></returns>   
        private static string getPageListCounts(string _ID, string _tbName, string _strCondition, int _Dist)   
        {   
            //---存放取得查询结果总数的查询语句                       
            //---对含有DISTINCT的查询进行SQL构造   
            //---对含有DISTINCT的总数查询进行SQL构造   
            string strTmp = "", SqlSelect = "", SqlCounts = "";   
  
            if (_Dist == 0)   
            {   
                SqlSelect = "Select ";   
                SqlCounts = "COUNT(*)";   
            }   
            else  
            {   
                SqlSelect = "Select DISTINCT ";   
                SqlCounts = "COUNT(DISTINCT " + _ID + ")";   
            }   
            if (_strCondition == string.Empty)   
            {   
                strTmp = SqlSelect + " " + SqlCounts + " FROM " + _tbName;   
            }   
            else  
            {   
                strTmp = SqlSelect + " " + SqlCounts + " FROM " + _tbName + " Where (1=1) and " + _strCondition;   
            }   
            return strTmp;   
        }   
        /*分页查询数据记录总数获取*/  
        /// <summary>   
        /// 功能:分页查询数据记录总数获取   
        /// </summary>   
        /// <param name="_tbName">----要显示的表或多个表的连接</param>   
        /// <param name="_ID">----主表的主键</param>   
        /// <param name="_strCondition">----查询条件,不需where</param>           
        /// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>   
        /// <returns></returns>   
        public static int GetDataRecordCount(string _ID, string _tbName, string _strCondition, int _Dist)   
        {   //ExecuteClass ec = new ExecuteClass();   
            //  DataTable dt=new DataTable();   
            string strSQL = getPageListCounts(_ID, _tbName, _strCondition, _Dist);   
  
  
            return ExecuteScalarScalarCount(strSQL);   
  
  
        }  
        #endregion  
        #endregion   
    }   
}   
  

以下这段是修改数据调用方法，插入和删除都是类似的，就不一一做演示了

 

    /*修改记录 */  
    /// <summary>   
    /// --功能：修改记录   
    /// </summary>   
    /// <param name="UserID"></param>   
    /// <param name="U_Name">登陆用户名</param>   
    /// <param name="U_PWD">登陆密码</param>   
    /// <param name="UserName">姓名</param>   
    /// <param name="EMail">邮箱</param>   
    /// <param name="Tel">电话</param>   
    /// <param name="RegDate">注册时间</param>   
    /// <param name="DelFlag">删除锁定标志</param>   
    public bool Edit(string UserID, string U_Name, string U_PWD, string UserName, string EMail, string Tel, string RegDate, string DelFlag)   
    {   
              
           bool flag = false;   
        try  
        {   
            string tablename = "NMGE_Users";   
            string where = " UserID=" + UserID;   
            Hashtable ht = new Hashtable();   
            if (U_Name != "")   
            ht.Add("U_Name", U_Name);   
            if (U_PWD != "")   
                ht.Add("U_PWD", U_PWD);   
            if (UserName != "")   
                ht.Add("UserName", UserName);   
            if (EMail != "")   
                ht.Add("EMail", EMail);   
            if (Tel != "")   
                ht.Add("Tel", Tel);   
            if (RegDate != "")   
                ht.Add("RegDate", RegDate);   
            if (DelFlag != "")   
                ht.Add("DelFlag", DelFlag);   
      
      
            flag = CmdSQLFactory.BuildSQLExecute(ht, where, tablename, "update");   
        }   
        catch (Exception ex)   
        {   
            PageError.showError(ex.Message);   
      
        }   
        return flag;   
    }    

 

以下这段是分页处理显示方法，这里用到了数据库列表控件，三种常用的数据库列表控件通用

 

    public void GetDateListBind()   
    {   
        string table = "NMGE_Users";   
        string where = " 1=1 ";   
      
         
        if (this.hidKeyword.Value != "")   
        {   
            where += " and U_Name like '%" + this.hidKeyword.Value + "%' ";   
        }   
              if (this.hidSortID.Value != "")   
        {   
            //  where += " and NewsSortID=" + this.hidSortID.Value;   
        }   
      
      
        AspNetPager1.RecordCount = CmdSQLFactory.GetDataRecordCount("UserID", table, where, 0);//统计行数   
      
        DataTable dtbl = CmdSQLFactory.GetPageDataList("UserID", "*", table, where,“UserID desc”, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex).Tables[0];   
        this.GVList.DataSource = dtbl;   
        this.GVList.DataBind();   
      
      
    }