|
1
|
using System;
|
|
2
|
using System.Collections.Generic;
|
|
3
|
using System.Linq;
|
|
4
|
using System.Web;
|
|
5
|
using System.Data;
|
|
6
|
using System.Data.Common;
|
|
7
|
|
|
8
|
namespace ID2PSN
|
|
9
|
{
|
|
10
|
public class ColInfo
|
|
11
|
{
|
|
12
|
public string Name { get; set; }
|
|
13
|
public string DataType { get; set; }
|
|
14
|
}
|
|
15
|
|
|
16
|
/// <summary>
|
|
17
|
/// Abstract base class for encapsulating provider independant database interactin logic.
|
|
18
|
/// </summary>
|
|
19
|
/// <remarks>Version 2.0</remarks>
|
|
20
|
/// <typeparam name="CONNECTION_TYPE"><see cref="DbConnection"/> derived Connection type.</typeparam>
|
|
21
|
/// <typeparam name="COMMAND_TYPE"><see cref="DbCommand"/> derived Command type.</typeparam>
|
|
22
|
/// <typeparam name="ADAPTER_TYPE"><see cref="DbDataAdapater"/> derived Data Adapter type.</typeparam>
|
|
23
|
public abstract class AbstractDatabase<CONNECTION_TYPE, COMMAND_TYPE, ADAPTER_TYPE> : IAbstractDatabase
|
|
24
|
where CONNECTION_TYPE : DbConnection, new()
|
|
25
|
where COMMAND_TYPE : DbCommand
|
|
26
|
where ADAPTER_TYPE : DbDataAdapter, new()
|
|
27
|
{
|
|
28
|
#region : Connection :
|
|
29
|
|
|
30
|
/// <summary>Gets the Connection object associated with the current instance.</summary>
|
|
31
|
public DbConnection Connection
|
|
32
|
{
|
|
33
|
get
|
|
34
|
{
|
|
35
|
if (internal_currentConnection == null)
|
|
36
|
{
|
|
37
|
internal_currentConnection = new CONNECTION_TYPE
|
|
38
|
{
|
|
39
|
ConnectionString = GetConnectionString()
|
|
40
|
};
|
|
41
|
}
|
|
42
|
return internal_currentConnection;
|
|
43
|
}
|
|
44
|
}
|
|
45
|
private DbConnection internal_currentConnection = null;
|
|
46
|
|
|
47
|
/// <summary>When overridden in derived classes returns the connection string for the database.</summary>
|
|
48
|
/// <returns>The connection string for the database.</returns>
|
|
49
|
protected abstract string GetConnectionString();
|
|
50
|
|
|
51
|
#endregion
|
|
52
|
|
|
53
|
#region : Commands :
|
|
54
|
|
|
55
|
/// <summary>Gets a DbCommand object with the specified <see cref="DbCommand.CommandText"/>.</summary>
|
|
56
|
/// <param name="sqlString">The SQL string.</param>
|
|
57
|
/// <returns>A DbCommand object with the specified <see cref="DbCommand.CommandText"/>.</returns>
|
|
58
|
public DbCommand GetSqlStringCommand(string sqlString)
|
|
59
|
{
|
|
60
|
if (this.Connection.State != ConnectionState.Open)
|
|
61
|
this.Connection.Open();
|
|
62
|
|
|
63
|
DbCommand cmd = this.Connection.CreateCommand();
|
|
64
|
cmd.CommandType = CommandType.Text;
|
|
65
|
cmd.CommandText = sqlString;
|
|
66
|
cmd.CommandTimeout = 600; // 15.11.04 added by soohyun - Extend Time out of query
|
|
67
|
return cmd;
|
|
68
|
}
|
|
69
|
|
|
70
|
/// <summary>Gets a DbCommand object with the specified <see cref="DbCommand.CommandText"/>.</summary>
|
|
71
|
/// <param name="sqlStringFormat">The SQL string format.</param>
|
|
72
|
/// <param name="args">The format arguments.</param>
|
|
73
|
/// <returns>A DbCommand object with the specified <see cref="DbCommand.CommandText"/>.</returns>
|
|
74
|
public DbCommand GetSqlStringCommand(string sqlStringFormat, params object[] args)
|
|
75
|
{
|
|
76
|
return GetSqlStringCommand(string.Format(sqlStringFormat, args));
|
|
77
|
}
|
|
78
|
|
|
79
|
/// <summary>Gets a DbCommand object for the specified Stored Procedure.</summary>
|
|
80
|
/// <param name="storedProcName">The name of the stored procedure.</param>
|
|
81
|
/// <returns>A DbCommand object for the specified Stored Procedure.</returns>
|
|
82
|
public DbCommand GetStoredProcedureCommand(string storedProcName)
|
|
83
|
{
|
|
84
|
if (this.Connection.State != ConnectionState.Open)
|
|
85
|
this.Connection.Open();
|
|
86
|
|
|
87
|
DbCommand cmd = this.Connection.CreateCommand();
|
|
88
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
89
|
cmd.CommandText = storedProcName;
|
|
90
|
return cmd;
|
|
91
|
}
|
|
92
|
|
|
93
|
#region : Parameters :
|
|
94
|
|
|
95
|
/// <summary>Adds an input parameter to the given <see cref="DbCommand"/>.</summary>
|
|
96
|
/// <param name="cmd">The command object the parameter should be added to.</param>
|
|
97
|
/// <param name="paramName">The identifier of the parameter.</param>
|
|
98
|
/// <param name="paramType">The type of the parameter.</param>
|
|
99
|
/// <param name="value">The value of the parameter.</param>
|
|
100
|
/// <returns>The <see cref="DbParameter"/> that was created.</returns>
|
|
101
|
public DbParameter AddInParameter(DbCommand cmd, string paramName, DbType paramType, object value)
|
|
102
|
{
|
|
103
|
return AddParameter(cmd, paramName, paramType, ParameterDirection.Input, value);
|
|
104
|
}
|
|
105
|
|
|
106
|
/// <summary>Adds an input parameter to the given <see cref="DbCommand"/>.</summary>
|
|
107
|
/// <param name="cmd">The command object the parameter should be added to.</param>
|
|
108
|
/// <param name="paramName">The identifier of the parameter.</param>
|
|
109
|
/// <param name="paramType">The type of the parameter.</param>
|
|
110
|
/// <param name="size">The maximum size in bytes, of the data table column to be affected.</param>
|
|
111
|
/// <param name="value">The value of the parameter.</param>
|
|
112
|
/// <returns>The <see cref="DbParameter"/> that was created.</returns>
|
|
113
|
public DbParameter AddInParameter(DbCommand cmd, string paramName, DbType paramType, int size, object value)
|
|
114
|
{
|
|
115
|
DbParameter param = AddInParameter(cmd, paramName, paramType, value);
|
|
116
|
param.Size = size;
|
|
117
|
cmd.Parameters.Add(param);
|
|
118
|
return param;
|
|
119
|
}
|
|
120
|
|
|
121
|
/// <summary>Adds the out parameter to the given <see cref="DbCommand"/></summary>
|
|
122
|
/// <param name="cmd">The command object the parameter should be added to.</param>
|
|
123
|
/// <param name="paramName">The identifier of the parameter.</param>
|
|
124
|
/// <param name="paramType">The type of the parameter.</param>
|
|
125
|
/// <param name="value">The value of the parameter.</param>
|
|
126
|
/// <returns>The <see cref="DbParameter"/> that was created.</returns>
|
|
127
|
public DbParameter AddOutParameter(DbCommand cmd, string paramName, DbType paramType, object value)
|
|
128
|
{
|
|
129
|
return AddParameter(cmd, paramName, paramType, ParameterDirection.Output, value);
|
|
130
|
}
|
|
131
|
|
|
132
|
/// <summary>Adds a parameter to the given <see cref="DbCommand"/>.</summary>
|
|
133
|
/// <param name="cmd">The command object the parameter should be added to.</param>
|
|
134
|
/// <param name="paramName">The identifier of the parameter.</param>
|
|
135
|
/// <param name="paramType">The type of the parameter.</param>
|
|
136
|
/// <param name="direction"><see cref="ParameterDirection"/> of the parameter.</param>
|
|
137
|
/// <param name="value">The value of the parameter.</param>
|
|
138
|
/// <returns>The <see cref="DbParameter"/> that was created.</returns>
|
|
139
|
public DbParameter AddParameter(DbCommand cmd, string paramName,
|
|
140
|
DbType paramType,
|
|
141
|
ParameterDirection direction,
|
|
142
|
object value)
|
|
143
|
{
|
|
144
|
DbParameter param = cmd.CreateParameter();
|
|
145
|
param.DbType = paramType;
|
|
146
|
param.ParameterName = paramName;
|
|
147
|
param.Value = value;
|
|
148
|
param.Direction = direction;
|
|
149
|
cmd.Parameters.Add(param);
|
|
150
|
return param;
|
|
151
|
}
|
|
152
|
|
|
153
|
/// <summary>Adds a parameter to the given <see cref="DbCommand"/>.</summary>
|
|
154
|
/// <param name="cmd">The command object the parameter should be added to.</param>
|
|
155
|
/// <param name="paramName">The identifier of the parameter.</param>
|
|
156
|
/// <param name="paramType">The type of the parameter.</param>
|
|
157
|
/// <param name="direction"><see cref="ParameterDirection"/> of the parameter.</param>
|
|
158
|
/// <param name="size">The maximum size in bytes, of the data table column to be affected.</param>
|
|
159
|
/// <param name="value">The value of the parameter.</param>
|
|
160
|
/// <returns>The <see cref="DbParameter"/> that was created.</returns>
|
|
161
|
public DbParameter AddParameter(DbCommand cmd, string paramName,
|
|
162
|
DbType paramType,
|
|
163
|
ParameterDirection direction,
|
|
164
|
int size,
|
|
165
|
object value)
|
|
166
|
{
|
|
167
|
DbParameter param = AddParameter(cmd, paramName, paramType, direction, value);
|
|
168
|
param.Size = size;
|
|
169
|
return param;
|
|
170
|
}
|
|
171
|
|
|
172
|
#endregion
|
|
173
|
|
|
174
|
#region : Executes :
|
|
175
|
|
|
176
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
177
|
/// <param name="cmd">The command to be executed.</param>
|
|
178
|
/// <returns>Result returned by the database engine.</returns>
|
|
179
|
public int ExecuteNonQuery(DbCommand cmd)
|
|
180
|
{
|
|
181
|
if (this.Connection.State != ConnectionState.Open)
|
|
182
|
this.Connection.Open();
|
|
183
|
|
|
184
|
return cmd.ExecuteNonQuery();
|
|
185
|
}
|
|
186
|
|
|
187
|
public int ExecuteNonQuery(string sSql)
|
|
188
|
{
|
|
189
|
if (this.Connection.State != ConnectionState.Open)
|
|
190
|
this.Connection.Open();
|
|
191
|
|
|
192
|
return GetSqlStringCommand(sSql).ExecuteNonQuery();
|
|
193
|
}
|
|
194
|
|
|
195
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
196
|
/// <param name="cmd">The command to be executed.</param>
|
|
197
|
/// <param name="txn">The database transaction inside which the command should be executed.</param>
|
|
198
|
/// <returns>Result returned by the database engine.</returns>
|
|
199
|
public int ExecuteNonQuery(DbCommand cmd, DbTransaction txn)
|
|
200
|
{
|
|
201
|
if (this.Connection.State != ConnectionState.Open)
|
|
202
|
this.Connection.Open();
|
|
203
|
|
|
204
|
cmd.Transaction = txn;
|
|
205
|
return cmd.ExecuteNonQuery();
|
|
206
|
}
|
|
207
|
|
|
208
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
209
|
/// <param name="cmd">The command to be executed.</param>
|
|
210
|
/// <returns>Result returned by the database engine.</returns>
|
|
211
|
public DbDataReader ExecuteReader(DbCommand cmd)
|
|
212
|
{
|
|
213
|
if (this.Connection.State != ConnectionState.Open)
|
|
214
|
this.Connection.Open();
|
|
215
|
|
|
216
|
return cmd.ExecuteReader();
|
|
217
|
}
|
|
218
|
|
|
219
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
220
|
/// <param name="cmd">The command to be executed.</param>
|
|
221
|
/// <param name="behavior">One of the <see cref="System.Data.CommandBehavior"/> values.</param>
|
|
222
|
/// <returns>Result returned by the database engine.</returns>
|
|
223
|
public DbDataReader ExecuteReader(DbCommand cmd, CommandBehavior behavior)
|
|
224
|
{
|
|
225
|
if (this.Connection.State != ConnectionState.Open)
|
|
226
|
this.Connection.Open();
|
|
227
|
|
|
228
|
return cmd.ExecuteReader(behavior);
|
|
229
|
}
|
|
230
|
|
|
231
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
232
|
/// <param name="cmd">The command to be executed.</param>
|
|
233
|
/// <returns>Result returned by the database engine.</returns>
|
|
234
|
public T ExecuteScalar<T>(DbCommand cmd, T defaultValue)
|
|
235
|
{
|
|
236
|
if (this.Connection.State != ConnectionState.Open)
|
|
237
|
this.Connection.Open();
|
|
238
|
|
|
239
|
object retVal = cmd.ExecuteScalar();
|
|
240
|
if (null == retVal || DBNull.Value == retVal)
|
|
241
|
return defaultValue;
|
|
242
|
else
|
|
243
|
return (T)retVal;
|
|
244
|
}
|
|
245
|
|
|
246
|
/// <summary>Executes the specified command against the current connection.</summary>
|
|
247
|
/// <param name="cmd">The command to be executed.</param>
|
|
248
|
/// <returns>Result returned by the database engine.</returns>
|
|
249
|
public DataSet ExecuteDataSet(DbCommand cmd, DbTransaction txn = null)
|
|
250
|
{
|
|
251
|
ADAPTER_TYPE adapter = new ADAPTER_TYPE();
|
|
252
|
if (null != txn) cmd.Transaction = txn;
|
|
253
|
adapter.SelectCommand = (COMMAND_TYPE)cmd;
|
|
254
|
|
|
255
|
DataSet retVal = new DataSet();
|
|
256
|
retVal.Locale = System.Globalization.CultureInfo.InvariantCulture;
|
|
257
|
adapter.Fill(retVal);
|
|
258
|
return retVal;
|
|
259
|
}
|
|
260
|
|
|
261
|
#endregion
|
|
262
|
|
|
263
|
#endregion
|
|
264
|
|
|
265
|
/// <summary>Begins a transaction.</summary>
|
|
266
|
/// <returns>Created transaction.</returns>
|
|
267
|
public DbTransaction BeginTransaction()
|
|
268
|
{
|
|
269
|
if (this.Connection.State != ConnectionState.Open)
|
|
270
|
this.Connection.Open();
|
|
271
|
return Connection.BeginTransaction();
|
|
272
|
}
|
|
273
|
|
|
274
|
#region : Consturction / Destruction :
|
|
275
|
|
|
276
|
/// <summary>Disposes the resources associated with the current database connection.</summary>
|
|
277
|
~AbstractDatabase()
|
|
278
|
{
|
|
279
|
Dispose();
|
|
280
|
}
|
|
281
|
|
|
282
|
#region IDisposable Members
|
|
283
|
|
|
284
|
/// <summary>Disposes the resources associated with the current database connection.</summary>
|
|
285
|
public void Dispose()
|
|
286
|
{
|
|
287
|
if (null != internal_currentConnection)
|
|
288
|
{
|
|
289
|
internal_currentConnection.Dispose();
|
|
290
|
internal_currentConnection = null;
|
|
291
|
}
|
|
292
|
}
|
|
293
|
|
|
294
|
#endregion
|
|
295
|
|
|
296
|
#endregion
|
|
297
|
|
|
298
|
public abstract List<string> GetTableNames();
|
|
299
|
public abstract List<string> GetCommonTableNames();
|
|
300
|
public abstract List<string> GetColumnNames(string TableName);
|
|
301
|
}
|
|
302
|
}
|
