|
0
|
1 using System;
|
|
|
2 using System.Collections;
|
|
|
3 using System.Collections.Generic;
|
|
|
4 using System.Data;
|
|
|
5 using System.Data.Common;
|
|
|
6 using System.Data.SqlClient;
|
|
|
7 using System.Linq;
|
|
|
8
|
|
|
9 using SqlException = System.Data.SqlClient.SqlException;
|
|
|
10 using SqlParameter = System.Data.SqlClient.SqlParameter;
|
|
|
11
|
|
|
12 namespace BLToolkit.Data.DataProvider
|
|
|
13 {
|
|
|
14 using Mapping;
|
|
|
15 using Sql.SqlProvider;
|
|
|
16
|
|
|
17 /// <summary>
|
|
|
18 /// Implements access to the Data Provider for SQL Server.
|
|
|
19 /// </summary>
|
|
|
20 /// <remarks>
|
|
|
21 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
22 /// </remarks>
|
|
|
23 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
|
|
|
24 public abstract class SqlDataProviderBase : DataProviderBase
|
|
|
25 {
|
|
|
26 /// <summary>
|
|
|
27 /// Creates the database connection object.
|
|
|
28 /// </summary>
|
|
|
29 /// <remarks>
|
|
|
30 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
31 /// </remarks>
|
|
|
32 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
|
|
|
33 /// <returns>The database connection object.</returns>
|
|
|
34 public override IDbConnection CreateConnectionObject()
|
|
|
35 {
|
|
|
36 return new SqlConnection();
|
|
|
37 }
|
|
|
38
|
|
|
39 /// <summary>
|
|
|
40 /// Creates the data adapter object.
|
|
|
41 /// </summary>
|
|
|
42 /// <remarks>
|
|
|
43 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
44 /// </remarks>
|
|
|
45 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
|
|
|
46 /// <returns>A data adapter object.</returns>
|
|
|
47 public override DbDataAdapter CreateDataAdapterObject()
|
|
|
48 {
|
|
|
49 return new SqlDataAdapter();
|
|
|
50 }
|
|
|
51
|
|
|
52 /// <summary>
|
|
|
53 /// Populates the specified <see cref="IDbCommand"/> object's Parameters collection with
|
|
|
54 /// parameter information for the stored procedure specified in the <see cref="IDbCommand"/>.
|
|
|
55 /// </summary>
|
|
|
56 /// <remarks>
|
|
|
57 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
58 /// </remarks>
|
|
|
59 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
|
|
|
60 /// <param name="command">The <see cref="IDbCommand"/> referencing the stored procedure for which the parameter
|
|
|
61 /// information is to be derived. The derived parameters will be populated into the
|
|
|
62 /// Parameters of this command.</param>
|
|
|
63 public override bool DeriveParameters(IDbCommand command)
|
|
|
64 {
|
|
|
65 SqlCommandBuilder.DeriveParameters((SqlCommand)command);
|
|
|
66
|
|
|
67 #if !MONO
|
|
|
68 foreach (SqlParameter p in command.Parameters)
|
|
|
69 {
|
|
|
70 // We have to clear UDT type names.
|
|
|
71 // Otherwise it will fail with error
|
|
|
72 // "Database name is not allowed with a table-valued parameter"
|
|
|
73 // but this is exactly the way how they are discovered.
|
|
|
74 //
|
|
|
75 if (p.SqlDbType == SqlDbType.Structured)
|
|
|
76 {
|
|
9
|
77 var firstDot = p.UdtTypeName.IndexOf('.');
|
|
0
|
78 if (firstDot >= 0)
|
|
9
|
79 p.UdtTypeName = p.UdtTypeName.Substring(firstDot + 1);
|
|
0
|
80 }
|
|
|
81 }
|
|
|
82 #endif
|
|
|
83
|
|
|
84 return true;
|
|
|
85 }
|
|
|
86
|
|
|
87 public override void PrepareCommand(ref CommandType commandType, ref string commandText, ref IDbDataParameter[] commandParameters)
|
|
|
88 {
|
|
|
89 base.PrepareCommand(ref commandType, ref commandText, ref commandParameters);
|
|
|
90
|
|
|
91 if (commandParameters == null)
|
|
|
92 return;
|
|
|
93
|
|
|
94 foreach (var p in commandParameters)
|
|
|
95 {
|
|
|
96 var val = p.Value;
|
|
|
97
|
|
|
98 if (val == null || !val.GetType().IsArray || val is byte[] || val is char[])
|
|
|
99 continue;
|
|
|
100
|
|
|
101 var dt = new DataTable();
|
|
|
102
|
|
|
103 dt.Columns.Add("column_value", val.GetType().GetElementType());
|
|
|
104
|
|
|
105 dt.BeginLoadData();
|
|
|
106
|
|
|
107 foreach (object o in (Array)val)
|
|
|
108 {
|
|
|
109 var row = dt.NewRow();
|
|
|
110 row[0] = o;
|
|
|
111 dt.Rows.Add(row);
|
|
|
112 }
|
|
|
113
|
|
|
114 dt.EndLoadData();
|
|
|
115
|
|
|
116 p.Value = dt;
|
|
|
117 }
|
|
|
118 }
|
|
|
119
|
|
|
120 public override void SetUserDefinedType(IDbDataParameter parameter, string typeName)
|
|
|
121 {
|
|
|
122 #if !MONO
|
|
|
123 if (!(parameter is SqlParameter))
|
|
|
124 throw new ArgumentException("SqlParameter expected.", "parameter");
|
|
|
125
|
|
9
|
126 ((SqlParameter)parameter).UdtTypeName = typeName;
|
|
0
|
127 #else
|
|
|
128 throw new NotSupportedException();
|
|
|
129 #endif
|
|
|
130 }
|
|
|
131
|
|
|
132 public override object Convert(object value, ConvertType convertType)
|
|
|
133 {
|
|
|
134 switch (convertType)
|
|
|
135 {
|
|
|
136 case ConvertType.ExceptionToErrorNumber:
|
|
|
137 if (value is SqlException)
|
|
|
138 return ((SqlException)value).Number;
|
|
|
139 break;
|
|
|
140 }
|
|
|
141
|
|
|
142 return SqlProvider.Convert(value, convertType);
|
|
|
143 }
|
|
|
144
|
|
|
145 public override DataExceptionType ConvertErrorNumberToDataExceptionType(int number)
|
|
|
146 {
|
|
|
147 switch (number)
|
|
|
148 {
|
|
|
149 case 1205: return DataExceptionType.Deadlock;
|
|
|
150 case -2: return DataExceptionType.Timeout;
|
|
|
151 case 547: return DataExceptionType.ForeignKeyViolation;
|
|
|
152 case 2601: return DataExceptionType.UniqueIndexViolation;
|
|
|
153 case 2627: return DataExceptionType.ConstraintViolation;
|
|
|
154 }
|
|
|
155
|
|
|
156 return DataExceptionType.Undefined;
|
|
|
157 }
|
|
|
158
|
|
|
159 /// <summary>
|
|
|
160 /// Returns connection type.
|
|
|
161 /// </summary>
|
|
|
162 /// <remarks>
|
|
|
163 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
164 /// </remarks>
|
|
|
165 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataManager Method</seealso>
|
|
|
166 /// <value>An instance of the <see cref="Type"/> class.</value>
|
|
|
167 public override Type ConnectionType
|
|
|
168 {
|
|
|
169 get { return typeof(SqlConnection); }
|
|
|
170 }
|
|
|
171
|
|
|
172 public const string NameString = DataProvider.ProviderName.MsSql;
|
|
|
173
|
|
|
174 /// <summary>
|
|
|
175 /// Returns the data provider name.
|
|
|
176 /// </summary>
|
|
|
177 /// <remarks>
|
|
|
178 /// See the <see cref="DbManager.AddDataProvider(DataProviderBase)"/> method to find an example.
|
|
|
179 /// </remarks>
|
|
|
180 /// <seealso cref="DbManager.AddDataProvider(DataProviderBase)">AddDataProvider Method</seealso>
|
|
|
181 /// <value>Data provider name.</value>
|
|
|
182 public override string Name
|
|
|
183 {
|
|
|
184 get { return NameString; }
|
|
|
185 }
|
|
|
186
|
|
|
187 public override ISqlProvider CreateSqlProvider()
|
|
|
188 {
|
|
|
189 return new MsSql2005SqlProvider();
|
|
|
190 }
|
|
|
191
|
|
|
192 public override int MaxParameters
|
|
|
193 {
|
|
|
194 get { return 2100 - 20; }
|
|
|
195 }
|
|
|
196
|
|
|
197 public override int MaxBatchSize
|
|
|
198 {
|
|
|
199 get { return 65536; }
|
|
|
200 }
|
|
|
201
|
|
|
202 public override bool IsMarsEnabled(IDbConnection conn)
|
|
|
203 {
|
|
|
204 if (conn.ConnectionString != null)
|
|
|
205 {
|
|
|
206 return conn.ConnectionString.Split(';')
|
|
|
207 .Select(s => s.Split('='))
|
|
|
208 .Where (s => s.Length == 2 && s[0].Trim().ToLower() == "multipleactiveresultsets")
|
|
|
209 .Select(s => s[1].Trim().ToLower())
|
|
|
210 .Any (s => s == "true" || s == "1" || s == "yes");
|
|
|
211 }
|
|
|
212
|
|
|
213 return false;
|
|
|
214 }
|
|
|
215
|
|
|
216 #region GetDataReader
|
|
|
217
|
|
|
218 public override IDataReader GetDataReader(MappingSchema schema, IDataReader dataReader)
|
|
|
219 {
|
|
|
220 return dataReader is SqlDataReader?
|
|
|
221 new SqlDataReaderEx((SqlDataReader)dataReader):
|
|
|
222 base.GetDataReader(schema, dataReader);
|
|
|
223 }
|
|
|
224
|
|
|
225 class SqlDataReaderEx : DataReaderEx<SqlDataReader>
|
|
|
226 {
|
|
|
227 public SqlDataReaderEx(SqlDataReader rd): base(rd)
|
|
|
228 {
|
|
|
229 }
|
|
|
230
|
|
|
231 public override DateTimeOffset GetDateTimeOffset(int i)
|
|
|
232 {
|
|
|
233 #if !MONO
|
|
|
234 return DataReader.GetDateTimeOffset(i);
|
|
|
235 #else
|
|
|
236 throw new NotSupportedException();
|
|
|
237 #endif
|
|
|
238 }
|
|
|
239 }
|
|
|
240
|
|
|
241 #endregion
|
|
|
242
|
|
|
243 public override int InsertBatch<T>(
|
|
|
244 DbManager db,
|
|
|
245 string insertText,
|
|
|
246 IEnumerable<T> collection,
|
|
|
247 MemberMapper[] members,
|
|
|
248 int maxBatchSize,
|
|
|
249 DbManager.ParameterProvider<T> getParameters)
|
|
|
250 {
|
|
|
251 if (db.Transaction != null)
|
|
|
252 return base.InsertBatch(db, insertText, collection, members, maxBatchSize, getParameters);
|
|
|
253
|
|
|
254 var idx = insertText.IndexOf('\n');
|
|
|
255 var tbl = insertText.Substring(0, idx).Substring("INSERT INTO ".Length).TrimEnd('\r');
|
|
|
256 var rd = new BulkCopyReader(members, collection);
|
|
|
257 var bc = new SqlBulkCopy((SqlConnection)db.Connection)
|
|
|
258 {
|
|
|
259 BatchSize = maxBatchSize,
|
|
|
260 DestinationTableName = tbl,
|
|
|
261 };
|
|
|
262
|
|
|
263 foreach (var memberMapper in members)
|
|
|
264 bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping(memberMapper.Ordinal, memberMapper.Name));
|
|
|
265
|
|
|
266 bc.WriteToServer(rd);
|
|
|
267
|
|
|
268 return rd.Count;
|
|
|
269 }
|
|
|
270
|
|
|
271 class BulkCopyReader : IDataReader
|
|
|
272 {
|
|
|
273 readonly MemberMapper[] _members;
|
|
|
274 readonly IEnumerable _collection;
|
|
|
275 readonly IEnumerator _enumerator;
|
|
|
276
|
|
|
277 public int Count;
|
|
|
278
|
|
|
279 public BulkCopyReader(MemberMapper[] members, IEnumerable collection)
|
|
|
280 {
|
|
|
281 _members = members;
|
|
|
282 _collection = collection;
|
|
|
283 _enumerator = _collection.GetEnumerator();
|
|
|
284 }
|
|
|
285
|
|
|
286 #region Implementation of IDisposable
|
|
|
287
|
|
|
288 public void Dispose()
|
|
|
289 {
|
|
|
290 }
|
|
|
291
|
|
|
292 #endregion
|
|
|
293
|
|
|
294 #region Implementation of IDataRecord
|
|
|
295
|
|
|
296 public string GetName(int i)
|
|
|
297 {
|
|
|
298 return _members[i].Name;
|
|
|
299 }
|
|
|
300
|
|
|
301 public Type GetFieldType(int i)
|
|
|
302 {
|
|
|
303 return _members[i].Type;
|
|
|
304 }
|
|
|
305
|
|
|
306 public object GetValue(int i)
|
|
|
307 {
|
|
|
308 return _members[i].GetValue(_enumerator.Current);
|
|
|
309 }
|
|
|
310
|
|
|
311 public int FieldCount
|
|
|
312 {
|
|
|
313 get { return _members.Length; }
|
|
|
314 }
|
|
|
315
|
|
|
316 public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
|
|
|
317 {
|
|
|
318 throw new NotImplementedException();
|
|
|
319 }
|
|
|
320
|
|
|
321 public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
|
|
|
322 {
|
|
|
323 throw new NotImplementedException();
|
|
|
324 }
|
|
|
325
|
|
|
326 public string GetDataTypeName(int i) { throw new NotImplementedException(); }
|
|
|
327 public int GetValues (object[] values) { throw new NotImplementedException(); }
|
|
|
328 public int GetOrdinal (string name) { throw new NotImplementedException(); }
|
|
|
329 public bool GetBoolean (int i) { throw new NotImplementedException(); }
|
|
|
330 public byte GetByte (int i) { throw new NotImplementedException(); }
|
|
|
331 public char GetChar (int i) { throw new NotImplementedException(); }
|
|
|
332 public Guid GetGuid (int i) { throw new NotImplementedException(); }
|
|
|
333 public short GetInt16 (int i) { throw new NotImplementedException(); }
|
|
|
334 public int GetInt32 (int i) { throw new NotImplementedException(); }
|
|
|
335 public long GetInt64 (int i) { throw new NotImplementedException(); }
|
|
|
336 public float GetFloat (int i) { throw new NotImplementedException(); }
|
|
|
337 public double GetDouble (int i) { throw new NotImplementedException(); }
|
|
|
338 public string GetString (int i) { throw new NotImplementedException(); }
|
|
|
339 public decimal GetDecimal (int i) { throw new NotImplementedException(); }
|
|
|
340 public DateTime GetDateTime (int i) { throw new NotImplementedException(); }
|
|
|
341 public IDataReader GetData (int i) { throw new NotImplementedException(); }
|
|
|
342 public bool IsDBNull (int i) { throw new NotImplementedException(); }
|
|
|
343
|
|
|
344 object IDataRecord.this[int i]
|
|
|
345 {
|
|
|
346 get { throw new NotImplementedException(); }
|
|
|
347 }
|
|
|
348
|
|
|
349 object IDataRecord.this[string name]
|
|
|
350 {
|
|
|
351 get { throw new NotImplementedException(); }
|
|
|
352 }
|
|
|
353
|
|
|
354 #endregion
|
|
|
355
|
|
|
356 #region Implementation of IDataReader
|
|
|
357
|
|
|
358 public void Close()
|
|
|
359 {
|
|
|
360 throw new NotImplementedException();
|
|
|
361 }
|
|
|
362
|
|
|
363 public DataTable GetSchemaTable()
|
|
|
364 {
|
|
|
365 throw new NotImplementedException();
|
|
|
366 }
|
|
|
367
|
|
|
368 public bool NextResult()
|
|
|
369 {
|
|
|
370 throw new NotImplementedException();
|
|
|
371 }
|
|
|
372
|
|
|
373 public bool Read()
|
|
|
374 {
|
|
|
375 var b = _enumerator.MoveNext();
|
|
|
376
|
|
|
377 if (b)
|
|
|
378 Count++;
|
|
|
379
|
|
|
380 return b;
|
|
|
381 }
|
|
|
382
|
|
|
383 public int Depth
|
|
|
384 {
|
|
|
385 get { throw new NotImplementedException(); }
|
|
|
386 }
|
|
|
387
|
|
|
388 public bool IsClosed
|
|
|
389 {
|
|
|
390 get { throw new NotImplementedException(); }
|
|
|
391 }
|
|
|
392
|
|
|
393 public int RecordsAffected
|
|
|
394 {
|
|
|
395 get { throw new NotImplementedException(); }
|
|
|
396 }
|
|
|
397
|
|
|
398 #endregion
|
|
|
399 }
|
|
|
400
|
|
|
401 public override void SetParameterValue(IDbDataParameter parameter, object value)
|
|
|
402 {
|
|
|
403 if (value is sbyte)
|
|
|
404 {
|
|
|
405 parameter.Value = (byte)(sbyte)value;
|
|
|
406 }
|
|
|
407 else if (value is ushort)
|
|
|
408 {
|
|
|
409 parameter.Value = (short)(ushort)value;
|
|
|
410 }
|
|
|
411 else if (value is uint)
|
|
|
412 {
|
|
|
413 parameter.Value = (int)(uint)value;
|
|
|
414 }
|
|
|
415 else if (value is ulong)
|
|
|
416 {
|
|
|
417 parameter.Value = (long)(ulong)value;
|
|
|
418 }
|
|
|
419 else if (value is string)
|
|
|
420 {
|
|
|
421 parameter.Value = value;
|
|
|
422 if (parameter.DbType == DbType.String && ((string)value).Length == 0) parameter.Size = 1;
|
|
|
423 }
|
|
|
424 else
|
|
|
425 {
|
|
|
426 base.SetParameterValue(parameter, value);
|
|
|
427 }
|
|
|
428 }
|
|
|
429 }
|
|
|
430 }
|
