首页 > 数据库 >C# 连接多种数据库组件，类库项目

C# 连接多种数据库组件，类库项目

时间：2024-03-23 18:33:05浏览次数：17  
标签：类库 case return DBProvider C# using 组件 new public

新建类库项目，添加DBHelper.cs，DBManager.cs，DBManagerFactory.cs，DBProvider.cs，IDBManager.cs类，app.config(需要放在启动项目里),内容依次如下

using System.Configuration;
using System.Data;

namespace DataAccess
{
    public class DBHelper
    {
        private static readonly IDBManager dbManager = new DBManager(GetDBProvider(), GetConnectionString());

        /// <summary>
        /// 从配置文件中选择数据库类型
        /// </summary>
        /// <returns>DBProvider枚举值</returns>
        private static DBProvider GetDBProvider()
        {
            string providerType = ConfigurationManager.AppSettings["DBProvider"];
            DBProvider DBProvider;
            switch (providerType)
            {
                case "Oracle":
                    DBProvider = DBProvider.Oracle;
                    break;
                case "SqlServer":
                    DBProvider = DBProvider.SqlServer;
                    break;
                case "OleDb":
                    DBProvider = DBProvider.OleDb;
                    break;
                case "Odbc":
                    DBProvider = DBProvider.Odbc;
                    break;
                case "MySql":
                    DBProvider = DBProvider.MySql;
                    break;
                default:
                    return DBProvider.Odbc;
            }
            return DBProvider;
        }

        /// <summary>
        /// 从配置文件获取连接字符串
        /// </summary>
        /// <returns>连接字符串</returns>
        private static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        }

        /// <summary>
        /// 关闭数据库连接的方法
        /// </summary>
        public static void Close()
        {
            dbManager.Dispose();
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="paramsCount">参数个数</param>
        public static void CreateParameters(int paramsCount)
        {
            dbManager.CreateParameters(paramsCount);
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="index">参数索引</param>
        /// <param name="paramName">参数名</param>
        /// <param name="objValue">参数值</param>
        public static void AddParameters(int index, string paramName, object objValue)
        {
            dbManager.AddParameters(index, paramName, objValue);
        }

        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sqlString">安全的sql语句string.Format()</param>
        /// <returns>操作成功返回true</returns>
        public static bool ExecuteNonQuery(string sqlString)
        {
            try
            {
                dbManager.Open();
                return dbManager.ExecuteNonQuery(CommandType.Text, sqlString) > 0 ? true : false;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sqlString">安全的sql语句string.Format()</param>
        /// <returns>返回IDataReader</returns>
        public static IDataReader ExecuteReader(string sqlString)
        {
            try
            {
                dbManager.Open();
                return dbManager.ExecuteReader(CommandType.Text, sqlString);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
    }
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public sealed class DBManager : IDBManager, IDisposable
    {
        #region 字段

        private DBProvider _providerType;
        private IDbConnection _idbConnection;
        private IDataReader _iDataReader;
        private IDbCommand _idbCommand;
        private IDbTransaction _idbTransaction;
        private IDbDataParameter[] _idbParameters;
        private string _connectionString;

        #endregion

        #region 构造方法

        public DBManager()
        {
        }

        public DBManager(DBProvider providerType)
        {
            ProviderType = providerType;
        }

        public DBManager(DBProvider providerType, string connectionString)
        {
            ProviderType = providerType;
            ConnectionString = connectionString;
        }

        #endregion

        #region 属性

        public DBProvider ProviderType
        {
            get { return _providerType; }
            set { _providerType = value; }
        }

        public IDbConnection Connection
        {
            get { return _idbConnection; }
            set { _idbConnection = value; }
        }

        public IDataReader DataReader
        {
            get { return _iDataReader; }
            set { _iDataReader = value; }
        }

        public IDbCommand Command
        {
            get { return _idbCommand; }
            set { _idbCommand = value; }
        }

        public IDbTransaction Transaction
        {
            get { return _idbTransaction; }
            set { _idbTransaction = value; }
        }

        public IDbDataParameter[] Parameters
        {
            get { return _idbParameters; }
            set { _idbParameters = value; }
        }

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        #endregion

        #region 公有方法

        public void Open()
        {
            Connection = DBManagerFactory.GetConnection(ProviderType);
            Connection.ConnectionString = ConnectionString;
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
            Command = DBManagerFactory.GetCommand(ProviderType);
        }

        public void Close()
        {
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
            Close();
            Command = null;
            Transaction = null;
            Connection = null;
        }

        public void CreateParameters(int paramsCount)
        {
            Parameters = new IDbDataParameter[paramsCount];
            Parameters = DBManagerFactory.GetParameters(ProviderType, paramsCount);
        }

        public void AddParameters(int index, string paramName, object objValue)
        {
            if (index < Parameters.Length)
            {
                Parameters[index].ParameterName = paramName;
                Parameters[index].Value = objValue;
            }
        }

        public void BeginTransaction()
        {
            if (Transaction == null)
            {
                Transaction = DBManagerFactory.GetTransaction(ProviderType);
            }
            Command.Transaction = Transaction;
        }

        public void CommitTransaction()
        {
            if (Transaction != null)
            {
                Transaction.Commit();
            }
            Transaction = null;
        }

        public void CloseReader()
        {
            if (DataReader != null)
            {
                DataReader.Close();
            }
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            Command.Connection = Connection;
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
            DataReader = Command.ExecuteReader();
            Command.Parameters.Clear();
            return DataReader;
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
            int returnValue = Command.ExecuteNonQuery();
            Command.Parameters.Clear();
            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
            object returnValue = Command.ExecuteScalar();
            Command.Parameters.Clear();
            return returnValue;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            Command = DBManagerFactory.GetCommand(ProviderType);
            PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
            IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(ProviderType);
            dataAdapter.SelectCommand = Command;
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            Command.Parameters.Clear();
            return dataSet;
        }

        #endregion

        #region 私有方法

        private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
        {
            foreach (IDbDataParameter idbParameter in commandParameters)
            {
                if (idbParameter.Direction == ParameterDirection.InputOutput && idbParameter.Value == null)
                {
                    idbParameter.Value = DBNull.Value;
                }
                command.Parameters.Add(idbParameter);
            }
        }

        private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction,
                                    CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
        {
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        #endregion
    }
}
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;


namespace DataAccess
{
    internal class DBManagerFactory
    {
        private DBManagerFactory()
        {
        }

        public static IDbConnection GetConnection(DBProvider providerType)
        {
            IDbConnection iDbConnection;
            switch (providerType)
            {
                case DBProvider.SqlServer:
                    iDbConnection = new SqlConnection();
                    break;
                case DBProvider.OleDb:
                    iDbConnection = new OleDbConnection();
                    break;
                case DBProvider.Odbc:
                    iDbConnection = new OdbcConnection();
                    break;
                case DBProvider.Oracle:
                    iDbConnection = new OracleConnection();
                    break;
                case DBProvider.MySql:
                    iDbConnection = new MySqlConnection();
                    break;
                default:
                    return null;
            }
            return iDbConnection;
        }

        public static IDbCommand GetCommand(DBProvider providerType)
        {
            switch (providerType)
            {
                case DBProvider.SqlServer:
                    return new SqlCommand();
                case DBProvider.OleDb:
                    return new OleDbCommand();
                case DBProvider.Odbc:
                    return new OdbcCommand();
                case DBProvider.Oracle:
                    return new OracleCommand();
                case DBProvider.MySql:
                    return new MySqlCommand();
                default:
                    return null;
            }
        }

        public static IDbDataAdapter GetDataAdapter(DBProvider providerType)
        {
            switch (providerType)
            {
                case DBProvider.SqlServer:
                    return new SqlDataAdapter();
                case DBProvider.OleDb:
                    return new OleDbDataAdapter();
                case DBProvider.Odbc:
                    return new OdbcDataAdapter();
                case DBProvider.Oracle:
                    return new OracleDataAdapter();
                case DBProvider.MySql:
                    return new MySqlDataAdapter();
                default:
                    return null;
            }
        }

        public static IDbTransaction GetTransaction(DBProvider providerType)
        {
            IDbConnection iDbConnection = GetConnection(providerType);
            IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
            return iDbTransaction;
        }

        public static IDbDataParameter[] GetParameters(DBProvider providerType, int paramsCount)
        {
            IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
            switch (providerType)
            {
                case DBProvider.SqlServer:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new SqlParameter();
                    }
                    break;
                case DBProvider.OleDb:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OleDbParameter();
                    }
                    break;
                case DBProvider.Odbc:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OdbcParameter();
                    }
                    break;
                case DBProvider.Oracle:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OracleParameter();
                    }
                    break;
                case DBProvider.MySql:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new MySqlParameter();
                    }
                    break;
                default:
                    idbParams = null;
                    break;
            }
            return idbParams;
        }
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public enum DBProvider
    {
        Oracle,
        SqlServer,
        OleDb,
        Odbc,
        MySql
    }
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public interface IDBManager
    {
        DBProvider ProviderType
        {
            get;
            set;
        }

        IDbConnection Connection
        {
            get;
            set;
        }

        IDataReader DataReader
        {
            get;
            set;
        }

        IDbCommand Command
        {
            get;
            set;
        }

        IDbTransaction Transaction
        {
            get;
            set;
        }

        IDbDataParameter[] Parameters
        {
            get;
            set;
        }

        string ConnectionString
        {
            get;
            set;
        }

        void Open();
        void Close();
        void Dispose();
        void CreateParameters(int paramsCount);
        void AddParameters(int index, string paramName, object objValue);
        void BeginTransaction();
        void CommitTransaction();
        void CloseReader();
        IDataReader ExecuteReader(CommandType commandType, string commandText);
        int ExecuteNonQuery(CommandType commandType, string commandText);
        object ExecuteScalar(CommandType commandType, string commandText);
        DataSet ExecuteDataSet(CommandType commandType, string commandText);
    }
}
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="ConnString" connectionString="server=localhost;port=3306;database=mytest;Persist Security Info=False;uid=root;pwd=123456"/>
        <!-- 通过改变ConnectionString的值来更换数据库连接字符串
        <add name="ConnString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBDemo.mdb;Jet OLEDB:Database Password=1234"/>
        <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;Integrated Security=SSPI"/>
        <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=sa;pwd=1234"/>
        <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
        -->
    </connectionStrings>
    <appSettings>
        <add key="DBProvider" value="MySql"/>
        <!-- 通过改变value值来更换数据库
        <add key="DBProvider" value="Oracle"/>
        <add key="DBProvider" value="SqlServer"/>
        <add key="DBProvider" value="OleDb"/>
        <add key="DBProvider" value="Odbc"/> 
        <add key="DBProvider" value="MySql"/>
        -->
    </appSettings>
</configuration>

 

标签：类库,case,return,DBProvider,C#,using,组件,new,public
From： https://www.cnblogs.com/itsone/p/18091526

相关文章

  • EI级！高创新原创未发表！VMD-TCN-BiGRU-MATT变分模态分解卷积神经网络双向门控循环单元融
    EI级！高创新原创未发表！VMD-TCN-BiGRU-MATT变分模态分解卷积神经网络双向门控循环单元融合多头注意力机制多变量时间序列预测（Matlab）目录EI级！高创新原创未发表！VMD-TCN-BiGRU-MATT变分模态分解卷积神经网络双向门控循环单元融合多头注意力机制多变量时间序列预测（Matlab）预测......
  • 4.摄像头组件-Serializer( Max96717)
    MAX96717概述:     CSI-2toGMSL2Serializer   Max96717PinConfiguration1.D0/1/2/3_P/N,CKP/CKN: MIPI输入接口2.SIOP/N: 差分输出3.PWDNB: 断电输入(低电平有效)，具有1MΩ下拉电阻至地。                    将P......
  • Java应用中的JDBC数据库连接完全指南
    1、简介1.1介绍JDBC连接数据库的重要性是Java平台中用于连接和操作数据库的标准API。它的重要性体现在以下几个方面跨平台性： JDBC允许Java应用程序与各种数据库进行通信，而无需关心底层数据库系统的具体细节实时连接： 通过JDBC，Java应用程序可以实时连接到数据库，从而实现动......
  • 【漏洞复现】福建科立迅通信指挥调度平台pwd_update.php SQL注入漏洞 (CVE-2024-2621)
        免责声明：文章来源互联网收集整理，请勿利用文章内的相关技术从事非法测试，由于传播、利用此文所提供的信息或者工具而造成的任何直接或者间接的后果及损失，均由使用者本人负责，所产生的一切不良后果与文章作者无关。该文章仅供学习用途使用。          ......
  • python小白学习笔记Mac版本
    和win系统的不同之处python的cmd验证在win系统中，只需要输入python就可以得到相关python的版本信息但是在mac系统中，需要输入python3.12(3.12是具体版本的号码)只输入python和pip也显示找不到相关文件（已经成功的安装前提下）只有输入第三行代码python3.12才会显示pyth......
  • C++之引用
    1.引用的概念引用不是定义一个变量，而是给已经存在的变量取了一个别名，编译器不会为引用变量开辟内存空间，它和它所引用的变量使用同一块内存空间。类型&引用变量名（对象名）=引用实体 inta=10;int&b=a;//表示b是a的别名运行结果如下： 注意：引用类型必须和引用实......
  • python合并多个csv文件需要注意的问题（合并多个列名问题）
    问题描述起初我使用python语言合并多个csv文件时，存在这几个csv文件有不止一个列名相同，仅仅依靠一个列名内容进行合并；就很容易造成相同列名对应相同内容分别占据了两个列，感官极为不好，还会影响到后面的数据清洗；问题解决#在on的这里加一个中括号，这样就能够同时包括多个列名，依据......
  • UE4 c++ -- 简单的UMG
    说明学习一下如何将Widget蓝图与C++连接起来，将处理逻辑写在C++中基础在蓝图中，我们显示Widget是通过一个Actor或者PlayerController，甚至关卡蓝图，利用CreateWidget以及AddToViewPort使得Widget显示在屏幕上的所以在C++中也是一样，一般来说由于Actor的销毁比如角色死亡，会导致其......
  • MongoNetworkError: connect ECONNREFUSED 127.0.0.1:27017
    打开mongoDB的mongoshell，出现以下提示，此时输入任意键都会退出界面，无法进行命令输入。这时，我们首先在网址栏中输入http://localhost:27017/是否连接到27017端口，如果返回结果如下：接着在cmd命令提示符中输入mongod，确认MongoDB服务器是否启动，返回结果如下：从红色框标记的部分......
  • 关于scrapy框架爬某站出现DEBUG: Redirecting (meta refresh)的问题
    项目场景：Spider框架爬取m.baidu.com搜索结果问题描述访问地址为https://m.baidu.com/s?word=电影&pn=0最后结果变成了http://m.baidu.com/s?cip6=240e:390:6a52:67e5:b0fa:e9d6:226e:376d&word=电影&pn=0&pu=sz%401321_480&t_noscript=jump导致结果不对importjson......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99