Asp.Net中对操作Sql Server 简单处理的SqlDB类

    好久不接触这些闲暇时间回顾一下以前的基础。因为平常使用的时候都是直接调用SqlDB.dll这个类。先看这个类的结构

 

 

纸上得来终觉浅，绝知此事要躬行。个人觉得里面的标准操作就是对数据库增删查改 。特别适合初学者操作数据库，当初我也是那么来的。下面直接贴代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Text;

namespace FileUpDownThroou
{
    /// <summary>
    /// SqlServer访问类
    /// </summary>
    public class SqlDB
    {
        #region <——————————定义参数——————————>
        /// <summary>
        /// 数据库链接字符串
        /// </summary>
        protected readonly string SqlConning = ConfigurationManager.ConnectionStrings["ConnSqlString"].ConnectionString;
        /// <summary>
        /// 数据库链接对象
        /// </summary>
        private SqlConnection sqlConnection;
        /// <summary>
        /// 数据库事务对象
        /// </summary>
        private SqlTransaction sqlTransaction;
        /// <summary>
        /// 构造函数创建数据库链接
        /// </summary>
        #endregion

        #region<——————————构造函数——————————>
        public SqlDB()
        {
            if(sqlConnection==null)
            {
                sqlConnection = new SqlConnection(SqlConning);
                sqlConnection.Open();
            }
        }
        #endregion

        #region <——————————事务处理——————————>
        public SqlTransaction SqlTran
        {
            get { return sqlTransaction; }
            set { sqlTransaction = value; }
        }
        /// <summary>
        /// 打开数据链接
        /// </summary>
        public void OpenConnection()
        {
            if(sqlConnection != null && sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void CloseConnection()
        {
            if(sqlConnection != null && sqlConnection.State==ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }
        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        /// <param name="isolationlevel">链接的事务锁定行为</param>
        public void BeginTransaction(IsolationLevel isolationlevel)
        {
            if(sqlConnection.State==ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            try
            {
                SqlTran = sqlConnection.BeginTransaction(isolationlevel);
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch(Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 开始一个数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }

            try
            {
                SqlTran = sqlConnection.BeginTransaction();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 提交一个数据库事务
        /// </summary>
        public void CommitTransaction()
        {
            try
            {
                sqlTransaction.Commit();
                CloseConnection();
            }
            catch(SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch(Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        /// <summary>
        /// 回退一个数据库事务
        /// </summary>
        public void RollBackTransaction()
        {
            try
            {
                SqlTran.Rollback();
                CloseConnection();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.StackTrace);
            }
            catch (Exception e)
            {
                throw new Exception(e.StackTrace);
            }
        }
        #endregion

        #region <——————————标准操作——————————>

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandBehavior">查询影响说明</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType, CommandBehavior commandBehavior)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteReader(commandBehavior);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return ExecuteDataReader(Sql, parameters, commandType, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteDataReader(Sql, parameters, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回SqlDataReader
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的SqlDataReader</returns>
        public SqlDataReader ExecuteDataReader(string Sql)
        {
            return ExecuteDataReader(Sql, null, CommandType.Text, CommandBehavior.Default);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteScalar();
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteScalar(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回单个对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>满足条件的单个对象</returns>
        public object ExecuteScalar(string Sql)
        {
            return ExecuteScalar(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            return CreateSqlCommand(Sql, parameters, commandType).ExecuteNonQuery();
        }

        /// <summary>
        /// 执行非查询类的Sql语句z
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql, List<SqlParameter> parameters)
        {
            return ExecuteNonQuery(Sql, parameters, CommandType.Text);
        }

        /// <summary>
        /// 执行非查询类的Sql语句
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>成功执行所影响的记录数</returns>
        public int ExecuteNonQuery(string Sql)
        {
            return ExecuteNonQuery(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataTable(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataTable
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string Sql)
        {
            return ExecuteDataTable(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
        {
            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
        }

        /// <summary>
        /// 执行Sql语句返回DataSet
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string Sql)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text);
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
        {
            //设置导入的起始地址
            int firstPage = PageIndex * PageSize;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable");

            return dataSet;
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 执行查询，返回查询的分页结果数据集
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="PageSize">每页的记录数</param>
        /// <returns>分页结果数据集</returns>
        public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
        {
            return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
        }

        /// <summary>
        /// 创建Sql命令对象
        /// </summary>
        /// <param name="Sql">Sql语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="commandType">命令类型</param>
        /// <returns>成功创建的SqlCommand对象</returns>
        private SqlCommand CreateSqlCommand(string Sql, List<SqlParameter> parameters, CommandType commandType)
        {
            SqlCommand sqlCommand = new SqlCommand(Sql, sqlConnection, SqlTran);
            sqlCommand.CommandType = commandType;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    sqlCommand.Parameters.Add(parameter);
                }
            }
            return sqlCommand;
        }

        #endregion

        #region <——————————缓存操作——————————>

        /// <summary>
        /// Hashtable中缓存的参数
        /// </summary>
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 缓存查询的OleDb参数对象.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="cmdParms"></param>
        public void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        /// <summary>
        /// 从缓存获取指定的参数对象数组.
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
        public SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
            {
                return null;
            }

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
            for (int i = 0, j = cachedParms.Length; i < j; i++)
            {
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            }

            return clonedParms;
        }

        #endregion

        #region <——————————增删改查——————————>

        private static bool haveTransaction = false;

        public static bool HaveTransaction
        {
            get
            {
                return haveTransaction;
            }
            set
            {
                haveTransaction = value;
            }
        }

        /// <summary>
        /// 查询字符串的默认长度
        /// </summary>
        private const int defaultCommandTextLength = 256;

        /// <summary>
        /// 根据表名等参数创建数据库查询语句
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="topNum">返回的记录数</param>
        /// <param name="field">字段名称</param>
        /// <param name="condition">条件</param>
        /// <param name="order">排序字段</param>
        /// <returns>数据库查询语句</returns>
        private StringBuilder CreateSqlCommandText(string tableName, int topNum, string field, string condition, string order)
        {
            StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
            Sql.Append("SELECT ");
            if (topNum > 0)
            {
                Sql.AppendFormat("TOP {0} ", topNum);
            }
            Sql.AppendFormat("{0} FROM {1} ", field, tableName);
            if (condition != null && condition.Trim().Length != 0)
            {
                Sql.AppendFormat("WHERE {0} ", condition);
            }
            if (order != null && order.Trim().Length != 0)
            {
                Sql.AppendFormat(" ORDER BY {0}", order);
            }
            return Sql;
        }

        /// <summary>
        /// 向表中插入数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="field">字段名称字符串</param>
        /// <param name="fieldValue">字段值参数字符串</param>
        /// <param name="parameters">字段参数集合</param>
        /// <returns>成功插入的记录数</returns>
        public int Insert(string tableName, string field, string fieldValue, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("INSERT INTO {0}({1}) VALUES({2});", tableName, field,
                    fieldValue);
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }

        }

        /// <summary>
        /// 从表中删除数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="condition">删除条件</param>
        /// <param name="parameters">删除条件参数集合</param>
        /// <returns>成功删除的记录数</returns>
        public int Delete(string tableName, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("DELETE FROM {0} ", tableName);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        /// <summary>
        /// 根据条件修改表中的数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="fieldValue">字段名城</param>
        /// <param name="condition">修改条件</param>
        /// <param name="parameters">修改参数集合</param>
        /// <returns>成功修改的记录数</returns>
        public int Update(string tableName, string fieldValue, string condition, List<SqlParameter> parameters)
        {
            try
            {
                if (HaveTransaction == false)
                {
                    OpenConnection();
                }
                StringBuilder Sql = new StringBuilder(defaultCommandTextLength);
                Sql.AppendFormat("UPDATE {0} SET {1} ", tableName, fieldValue);
                if (condition != null && condition.Trim().Length != 0)
                {
                    Sql.AppendFormat("WHERE {0}", condition);
                }
                return ExecuteNonQuery(Sql.ToString(), parameters);
            }
            finally
            {
                if (HaveTransaction == false)
                {
                    CloseConnection();
                }
            }
        }

        #endregion

    }
}

里面的每个方法都有注解，应该都看的清楚。增删查改都有不同的重载

就像这个DetSet就有5个重载 用的时候根据需要就可以。

 

/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}

/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text);
}

/// <summary>
/// 执行Sql语句返回DataSet
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(string Sql)
{
return ExecuteDataSet(Sql, null, CommandType.Text);
}

/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="commandType">命令类型</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, CommandType commandType, int PageIndex, int PageSize)
{
//设置导入的起始地址
int firstPage = PageIndex * PageSize;

SqlDataAdapter dataAdapter = new SqlDataAdapter(CreateSqlCommand(Sql, sqlParameters, commandType));
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, firstPage, PageSize, "PranaTable");

return dataSet;
}

/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="parameters">参数集合</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, List<SqlParameter> sqlParameters, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, sqlParameters, CommandType.Text, PageIndex, PageSize);
}

/// <summary>
/// 执行查询，返回查询的分页结果数据集
/// </summary>
/// <param name="Sql">Sql语句</param>
/// <param name="PageIndex">页索引</param>
/// <param name="PageSize">每页的记录数</param>
/// <returns>分页结果数据集</returns>
public DataSet ExecuteDataSet(string Sql, int PageIndex, int PageSize)
{
return ExecuteDataSet(Sql, null, CommandType.Text, PageIndex, PageSize);
}