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

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

c# 代码

/******************************************************
 * 项目模块：
 * 功能描述：数据库执行控制类
 * 创建者：埖ó髒
 * 创建日期：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
    }
}

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

c# 代码

/*修改记录 */
/// <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;
}

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

c# 代码

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();


}