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
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
|
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.Common;
using System.Data;
namespace CrmDbClient
{
public class CrmDbClientException : Exception
{
public CrmDbClientException(string Message) : base(Message) { }
public CrmDbClientException(string Message, Exception inerException) : base(Message, inerException) { }
}
public class CrmDbClient : IDisposable
{
private IDbConnection _DbConnection;
private IDbCommand _DbCommand;
private ConnectionStringSettings _DbConnectionString;
private DbProviderFactory _DbFactory = null;
private IDbTransaction _DbTransaction = null;
private IDbDataAdapter _DbDataAdapter = null;
private Dictionary<string, string> cnxProvider = new Dictionary<string, string>();
/// <summary>
/// Creates a new instance of CRMDbClient
/// </summary>
/// <param name="CurrentConnectionString"></param>
private bool CreateConnectionToDB (ConnectionStringSettings CurrentConnectionString)
{
try
{
_DbConnectionString = CurrentConnectionString;
_DbFactory = DbProviderFactories.GetFactory(CurrentConnectionString.ProviderName);
_DbConnection = _DbFactory.CreateConnection();
_DbConnection.ConnectionString = CurrentConnectionString.ConnectionString;
_DbCommand = _DbConnection.CreateCommand();
_DbDataAdapter = _DbFactory.CreateDataAdapter();
_DbDataAdapter.SelectCommand = _DbCommand;
OpenConnection();
return true;
}
catch
{
return false;
}
}
public CrmDbClient(string ServerName, int Port, string DbName , string UserName, string Password)
{
//Ici tu ajoute la chaine de connexion que tu veux gérer avec leur providers
cnxProvider.Add("System.Data.SqlClient", "Data Source={0};Initial Catalog={2};User ID={3};Password={4}"); //sql server
cnxProvider.Add("Provider de MySQL", "SERVER={0}; DATABASE={2}; User id={3}; PWD={4};");// mysql
// on parcourt toutes les chaines de connexion que tu as ajouté en haut
foreach(KeyValuePair<string,string> k in cnxProvider)
{
//Construction de la chaine de connexion + provider
ConnectionStringSettings cnx = new ConnectionStringSettings();
cnx.ProviderName = k.Key;
cnx.ConnectionString = string.Format(k.Value, new object[] { ServerName, Port, DbName, UserName, Password });
// Test de connexion.
if (CreateConnectionToDB(cnx))
{
// Si ok, on quitte la boucle foreach
break;
}
}
}
/// <summary>
/// Destroy the current instance
/// </summary>
public void Dispose()
{
CloseConnection();
_DbCommand.Dispose();
_DbConnection.Dispose();
_DbConnectionString = null;
_DbDataAdapter = null;
_DbFactory = null;
_DbTransaction = null;
}
~CrmDbClient()
{
GC.Collect();
}
#region Properties
/// <summary>
/// Gets or sets the command timeout in seconds
/// </summary>
public int CommandTimeout
{
get { return _DbCommand.CommandTimeout; }
set { _DbCommand.CommandTimeout = value; }
}
/// <summary>
/// Gets the database provider
/// </summary>
public string DataBaseProvider
{
get { return _DbConnectionString.ProviderName; }
}
/// <summary>
/// Gets or set the
/// </summary>
public int ConnectionTimeout
{
get { return _DbConnection.ConnectionTimeout; }
}
#endregion
/// <summary>
/// Begins transaction
/// </summary>
public void BeginTransaction()
{
_DbCommand.Transaction = _DbTransaction = _DbConnection.BeginTransaction();
}
/// <summary>
/// Commit transaction
/// </summary>
public void CommitTransaction()
{
if (_DbTransaction != null)
{
_DbTransaction.Commit();
_DbTransaction.Dispose();
_DbTransaction = null;
_DbCommand.Transaction = null;
}
else
{
throw new CrmDbClientException("BeginTransaction must be called before commit or rollback. No open transactions found");
}
}
/// <summary>
/// Rollbacks transaction
/// </summary>
public void RollbackTransaction()
{
try
{
_DbTransaction.Rollback();
}
catch (InvalidOperationException invexcept)
{
throw new CrmDbClientException(invexcept.Message, invexcept);
}
catch (Exception e)
{
throw new CrmDbClientException(e.Message, e);
}
finally
{
//dispose _dbTransaction
if (_DbTransaction != null)
_DbTransaction.Dispose();
_DbTransaction = null;
}
}
/// <summary>
/// Creates a generic parameter
/// </summary>
/// <param name="ParameterName">Represents the prameter's name</param>
/// <param name="ParameterValue">Represents the parameter's value</param>
/// <returns>IDbDataParameter</returns>
private IDbDataParameter CreateParameter(string ParameterName, object ParameterValue)
{
IDbDataParameter p = _DbCommand.CreateParameter(); //create the command using the DBCommand
p.Value = ParameterValue;
p.ParameterName = ParameterName;
return p;
}
/// <summary>
/// Open the connection with de server
/// </summary>
private void OpenConnection()
{
if (_DbConnection.State != ConnectionState.Open)
_DbConnection.Open();
}
/// <summary>
/// Closes the connection with the server
/// </summary>
private void CloseConnection()
{
if (_DbConnection.State != ConnectionState.Closed)
_DbConnection.Close();
}
/// <summary>
/// Executes a scalar query
/// </summary>
/// <param name="sql">Represents the query text</param>
/// <param name="CmdType">Represents the command type</param>
/// <returns>Object</returns>
public object ExecuteScalarQuery(string QueryText, CommandType CmdType)
{
_DbCommand.CommandText = QueryText; //set que querytext
_DbCommand.CommandType = CmdType;
OpenConnection();
object val = _DbCommand.ExecuteScalar(); //runs que query
_DbCommand.Parameters.Clear();
//CloseConnection();
return val;
}
/// <summary>
/// Executes a parametred scalar query
/// </summary>
/// <param name="QueryText">Query text</param>
/// <param name="Parameters">Parameters list</param>
/// <param name="CmdType">Command type</param>
/// <returns></returns>
public object ExecuteScalarQuery(string QueryText, Dictionary<string, object> Parameters, CommandType CmdType)
{
_DbCommand.CommandText = QueryText;
_DbCommand.CommandType = CommandType.Text;
_DbCommand.Parameters.Clear(); //clear parameters
foreach (KeyValuePair<string, object> k in Parameters)
{
_DbCommand.Parameters.Add(CreateParameter(k.Key, k.Value));
}
OpenConnection();
object val = _DbCommand.ExecuteScalar();
_DbCommand.Parameters.Clear(); //clear parameters
return val;
}
/// <summary>
/// Executes a query and retourns the result into a datatable.
/// </summary>
/// <param name="QueryText">Query text</param>
/// <param name="CmdType">Command type</param>
/// <returns>Datatable containing the result</returns>
public DataTable ExecuteReaderQuery(string QueryText, CommandType CmdType)
{
DataSet myDs = new DataSet();
_DbCommand.CommandText = QueryText;
_DbCommand.CommandType = CmdType;
OpenConnection();
_DbDataAdapter.Fill(myDs);
return myDs.Tables[0];
}
/// <summary>
/// Executes a parametred query and retourns the result into a datatable
/// </summary>
/// <param name="QueryText">Query text</param>
/// <param name="QueryParameters">Command type</param>
/// <param name="CmdType">Parameters list</param>
/// <returns></returns>
public DataTable ExecuteReaderQuery(string QueryText, Dictionary<string, object> QueryParameters, CommandType CmdType)
{
_DbDataAdapter.SelectCommand.Parameters.Clear();
foreach (KeyValuePair<string, object> k in QueryParameters)
{
_DbDataAdapter.SelectCommand.Parameters.Add(CreateParameter(k.Key, k.Value));
}
DataTable val = this.ExecuteReaderQuery(QueryText, CmdType);
_DbCommand.Parameters.Clear(); //clear parameters
return val;
}
/// <summary>
/// Executes a query and retourns affected rows number
/// </summary>
/// <param name="QueryText">Query text</param>
/// <param name="CmdType">Command Type</param>
/// <returns></returns>
public int ExecuteNonQuery(string QueryText, CommandType CmdType)
{
_DbCommand.CommandText = QueryText;
_DbCommand.CommandType = CmdType;
OpenConnection();
return _DbCommand.ExecuteNonQuery();
}
/// <summary>
/// Executes a parametred query and retourns affected rows number
/// </summary>
/// <param name="QueryText">Query text</param>
/// <param name="QueryParameters">Query parameters</param>
/// <param name="CmdType">Command type</param>
/// <returns>Integer value representing affected rows</returns>
public int ExecuteNonQuery(string QueryText, Dictionary<string, object> QueryParameters, CommandType CmdType)
{
_DbCommand.Parameters.Clear();
foreach (KeyValuePair<string, object> k in QueryParameters)
{
_DbCommand.Parameters.Add(this.CreateParameter(k.Key, k.Value));
}
int val = this.ExecuteNonQuery(QueryText, CmdType);
_DbCommand.Parameters.Clear();
return val;
}
}
} |
[BDD] permettre l'interrogation de plusieurs types de base de données
Répondre avec citation
Envoyé par bossun
je vais plancher la dessus à tête reposée et demanderais un coup de main, si besoins.
, il te suffit, d'implémenter ton propre fournisseur. En créant des objets implémentant les interfaces 
!
Partager