XS_HttpServer_ART/XS_DAL/DBHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace XS_DAL
{
    public class DBHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        string dbConnectionString;
        /// <summary>
        /// 数据库提供程序的固定名称
        /// </summary>
        string dbProviderName;
        /// <summary>
        /// 数据库类型
        /// </summary>
        string dbType;

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connstr">连接字符串</param>
        /// <param name="provider">数据库提供程序固定名称</param>
        public DBHelper(string dbConnectionString, string dbProviderName)
        {
            this.dbConnectionString = dbConnectionString;
            this.dbProviderName = dbProviderName;
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        private DbConnection CreateConnection()
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dbProviderName);
            DbConnection dbConnection = dbFactory.CreateConnection();
            dbConnection.ConnectionString = dbConnectionString;
            return dbConnection;
        }

        /// <summary>
        /// 创建数据库适配器
        /// </summary>
        /// <returns></returns>
        private DbDataAdapter CreateDataAdapter()
        {
            DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dbProviderName);
            DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter();
            return dbDataAdapter;
        }

        /// <summary>
        /// 测试数据是否正常连接
        /// </summary>
        /// <returns></returns>
        public bool IsDbConnected()
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                dbConnection.Open();
                return true;
            }
        }

        /// <summary>
        /// 使用事务执行SQL语句返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = sql;
                    dbCommand.Connection.Open();

                    using (DbTransaction dbTransaction = dbCommand.Connection.BeginTransaction())
                    {
                        try
                        {
                            dbCommand.Transaction = dbTransaction;
                            int result = dbCommand.ExecuteNonQuery();
                            dbTransaction.Commit();

                            return result;
                        }
                        catch (Exception)
                        {
                            dbTransaction.Rollback();
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 使用事务执行多条SQL语句返回受影响的总行数
        /// </summary>
        /// <param name="sqls">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(List<string> sqls)
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.Connection.Open();

                    using (DbTransaction dbTransaction = dbCommand.Connection.BeginTransaction())
                    {
                        try
                        {
                            dbCommand.Transaction = dbTransaction;

                            int result = 0;

                            for (int i = 0; i < sqls.Count; i++)
                            {
                                dbCommand.CommandText = sqls[i];
                                result += dbCommand.ExecuteNonQuery();
                            }

                            dbCommand.Transaction.Commit();

                            return result;
                        }
                        catch (Exception)
                        {
                            dbCommand.Transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行带参数存储过程返回受影响的行数
        /// </summary>
        /// <param name="spt">存储过程名</param>
        /// <param name="dbParameters">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string spt, ref List<DbParameter> dbParameters)
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = spt;
                    dbCommand.CommandType = CommandType.StoredProcedure;

                    foreach (var item in dbParameters)
                    {
                        if ((item.Direction == ParameterDirection.Input || item.Direction == ParameterDirection.InputOutput) && item.Value == null)
                        {
                            item.Value = DBNull.Value;
                        }

                        dbCommand.Parameters.Add(item);
                    }

                    dbCommand.Connection.Open();

                    int result = dbCommand.ExecuteNonQuery();

                    return result;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句返回第一行第列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = sql;
                    dbCommand.Connection.Open();

                    return dbCommand.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句返回数据表
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            using (DbConnection dbConnection = CreateConnection())
            {
                using (DbCommand dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandType = CommandType.Text;
                    dbCommand.CommandText = sql;
                    dbCommand.Connection.Open();

                    DbDataAdapter dbDataAdapter = CreateDataAdapter();
                    dbDataAdapter.SelectCommand = dbCommand;

                    DataSet ds = new DataSet();
                    dbDataAdapter.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }
    }
}