C#-继承IDbConnection连接不同数据库,通用的DbHelper（1）_c# idbconnection

作者：Gausst松鼠会 | 2024-04-07 09:44:05

踩

c# idbconnection

C#-继承IDbConnection连接不同数据库,通用的DbHelper（1）

本方案可实现仅修改app.config即可连接不同数据库，但是设计数据库时需要注意各种数据库的数据类型是不一样的。
各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承自IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter，用一个工厂来实现接口的实例即可实现连接不同数据库。

主要包含以下类：

1. DbProvider.cs     # 创建一个枚举类型  
2. DBManagerFactory.cs   # 建一个工厂类，用来产生以上不同数据库的实例  
3. IDBManager.cs  # 创建一个接口  
4. DBManager.cs   # 创建一个类来实现IDBManager接口  
5. DBHelper.cs  #  来调用DBManager类，外部来直接调用DBHelper类即可  
1
2
3
4
5

1. DbProvider.cs # 创建一个枚举类型

namespace DbManager
{ 

    public enum DataProvider
    {
        Oracle,
        SqlServer,
        OleDb,
        Odbc,
        MySql
    }
}1
2
3
4
5
6
7
8
9
10
11
12

2. DBManagerFactory.cs # 建一个工厂类，用来产生以上不同数据库的实例

using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient; //需要添加引用
using MySql.Data.MySqlClient;   //请自行安装MySQLConnector/Net后添加引用

namespace DbManager
{
    public sealed class DBManagerFactory
    {
        private DBManagerFactory()
        {
        }

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

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

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

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

        public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
        {
            IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
            switch (providerType)
            {
                case DataProvider.SqlServer:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new SqlParameter();
                    }
                    break;
                case DataProvider.OleDb:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OleDbParameter();
                    }
                    break;
                case DataProvider.Odbc:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OdbcParameter();
                    }
                    break;
                case DataProvider.Oracle:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new OracleParameter();
                    }
                    break;
                case DataProvider.MySql:
                    for (int i = 0; i < paramsCount; i++)
                    {
                        idbParams[i] = new MySqlParameter();
                    }
                    break;
                default:
                    idbParams = null;
                    break;
            }
            return idbParams;
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

3. IDBManager.cs # 创建一个接口

using System.Data;

namespace DbManager
{
    public interface IDBManager
    {
        DataProvider 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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

4. DBManager.cs # 创建一个类来实现IDBManager接口

using System;
using System.Data;

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

        private DataProvider _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(DataProvider providerType)
        {
            ProviderType = providerType;
        }

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

        #endregion

        #region 属性

        public DataProvider 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
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230

5. DBHelper.cs # 来调用DBManager类，外部来直接调用DBHelper类即可

using System;
using System.Data;
using System.Configuration;

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

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

        /// <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);
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
        <!-- 通过改变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="DataProvider" value="MySql"/>
        <!-- 通过改变value值来更换数据库
        <add key="DataProvider" value="Oracle"/>
        <add key="DataProvider" value="SqlServer"/>
        <add key="DataProvider" value="OleDb"/>
        <add key="DataProvider" value="Odbc"/> 
        <add key="DataProvider" value="MySql"/>
        -->
    </appSettings>
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

测试

举个简单的例子，我们就创建一个控制台应用程序，然后添加DbManager.dll的引用

Program.cs文件

using System;
using System.Data;
using DbManager;  //记得引入命名空间

namespace DBDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            SelectWithoutParams();
            Console.WriteLine("------安全sql语句string.Format()的查询结果------");
            SelectWithSafeSql(4);
            Console.WriteLine("------参数化语句的查询结果-------");
            SelectWithParams("总统套间");
        }



        private static void SelectWithoutParams()

        {
            const string sql = "select * from RoomType";
            IDataReader reader = DBHelper.ExecuteReader(sql);
            while (reader.Read())
            {
                Console.WriteLine(reader["TypeName"].ToString());
            }
            DBHelper.Close();  //记得关闭reader
        }

        private static void SelectWithSafeSql(int TypeId)
        {
            string sql = string.Format("select * from RoomType where TypeId={0}", TypeId);
            IDataReader reader = DBHelper.ExecuteReader(sql);
            while (reader.Read())
            {
                Console.WriteLine(reader["TypeName"].ToString());
            }
            DBHelper.Close();
        }

        private static void SelectWithParams(string typeName)
        {
            string sql = "select * from RoomType where TypeName=@TypeName";



            //先创建参数，然后才能添加参数 

            DBHelper.CreateParameters(1);  //参数个数，1个
            DBHelper.AddParameters(0, "@TypeName", typeName);
            IDataReader reader = DBHelper.ExecuteReader(sql);
            while (reader.Read())
            {
                Console.WriteLine(reader["TypeName"].ToString());
            }
            DBHelper.Close();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

OK！全部完成！在具体的DAL层中，调用DBHelper的相关方法即可，如果是查询方法，记得最后要写关闭代码。只要表结构一样，可以在app.config中随意切换数据库。
最后注意的是：
各个数据库的插入语句不一样，假设我们有4个字段，第一个字段fieldName1为自增字段。
对于SQLServer，不需要写自增字段。
语句是：INSERT INTO table VALUES(value2, value3, value4);
对于MySQL，自增字段位置需要写null代替，
语句是：INSERT INTO table VALUES(NULL, value2, value3, value4);
而对于ACCESS数据库，则必须写完整，
语句是：INSERT INTO table(fieldName2, fieldName3,fieldName4) VALUES(value2, value3, value4);

为了实现兼容，大家还是都按完整的来写，就不会有错了。
1

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/377845