Datei: NDODLL/SqlPersistenceHandling/SqlPersistenceHandler.cs

Last Commit (08b23aa)
1 //
2 // Copyright (c) 2002-2016 Mirko Matytschak
3 // (www.netdataobjects.de)
4 //
5 // Author: Mirko Matytschak
6 //
7 // Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
8 // documentation files (the "Software"), to deal in the Software without restriction, including without limitation
9 // the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the
10 // Software, and to permit persons to whom the Software is furnished to do so, subject to the following
11 // conditions:
12
13 // The above copyright notice and this permission notice shall be included in all copies or substantial portions
14 // of the Software.
15 //
16 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
17 // TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
18 // THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
19 // CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
20 // DEALINGS IN THE SOFTWARE.
21
22
23 using System;
24 using System.Text.RegularExpressions;
25 using System.Reflection;
26 using System.Collections;
27 using System.Collections.Generic;
28 using System.Linq;
29 using System.Data;
30 using System.Data.Common;
31 using NDO.Mapping;
32 using NDOInterfaces;
33 using NDO.Query;
34 using System.Globalization;
35 using Microsoft.Extensions.Logging;
36 using Microsoft.Extensions.DependencyInjection;
37
38 namespace NDO.SqlPersistenceHandling
39 {
40 ····/// <summary>
41 ····/// Parameter type for the IProvider function RegisterRowUpdateHandler
42 ····/// </summary>
43 ····public delegate void RowUpdateHandler(DataRow row);
44
45 ····/// <summary>
46 ····/// Summary description for NDOPersistenceHandler.
47 ····/// </summary>
48 ····///
49 ····public class SqlPersistenceHandler : IPersistenceHandler
50 ····{
51 ········/// <summary>
52 ········/// This event will be triggered, if a concurrency error occurs
53 ········/// </summary>
54 ········public event ConcurrencyErrorHandler ConcurrencyError;
55
56 ········private IDbCommand selectCommand;
57 ········private IDbCommand insertCommand;
58 ········private IDbCommand updateCommand;
59 ········private IDbCommand deleteCommand;
60 ········private IDbConnection conn;
61 ········private IDbTransaction transaction;
62 ········private DbDataAdapter dataAdapter;
63 ········private Class classMapping;
64 ········private string selectFieldList;
65 ········private string selectFieldListWithAlias;
66 ········private string tableName;
67 ········private string qualifiedTableName;
68 ········private ILogger logger;
69 ········private Dictionary<string, IMappingTableHandler> mappingTableHandlers = new Dictionary<string, IMappingTableHandler>();
70 ········private IProvider provider;
71 ········private NDOMapping ndoMapping;
72 ········private string timeStampColumn = null;
73 ········private Column typeNameColumn = null;
74 ········private bool hasAutoincrementedColumn;
75 ········private OidColumn autoIncrementColumn;
76 ········private Dictionary<string,MemberInfo> persistentFields;
77 ········private List<RelationFieldInfo> relationInfos;
78 ········private Type type;
79 ········private int guidlength;
80 ········private string hollowFields;
81 ········private string hollowFieldsWithAlias;
82 ········private string fieldList;
83 ········private string namedParamList;
84 ········private bool hasGuidOid;
85 ········private readonly IServiceProvider serviceProvider;
86 ········private readonly ILoggerFactory loggerFactory;
87 ········private Action<Type,IPersistenceHandler> disposeCallback;
88
89 ········/// <summary>
90 ········/// Constructs a SqlPersistenceHandler object
91 ········/// </summary>
92 ········/// <param name="serviceProvider"></param>
93 ········public SqlPersistenceHandler(IServiceProvider serviceProvider)
94 ········{
95 ············this.serviceProvider = serviceProvider;
96 ············this.logger = serviceProvider.GetRequiredService<ILogger<SqlPersistenceHandler>>();
 
97 ········}
98
99 ········private void GenerateSelectCommand()
100 ········{
101 ············this.selectCommand.CommandText = string.Empty;
102 ········}
103
104 ········private int ParameterLength(Mapping.Field fieldMapping, Type memberType)
105 ········{
106 ············if (0 == fieldMapping.Column.Size)
107 ················return provider.GetDefaultLength(memberType);
108 ············else
109 ················return fieldMapping.Column.Size;
110 ········}
111
112 ········private void GenerateInsertCommand()
113 ········{
114 ············// Generate Parameters
115 ············foreach (OidColumn oidColumn in this.classMapping.Oid.OidColumns)
116 ············{
117 ················if (!oidColumn.AutoIncremented && oidColumn.FieldName == null && oidColumn.RelationName == null)
118 ················{
119 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( oidColumn.Name ), provider.GetDbType( oidColumn.SystemType ), provider.GetDefaultLength( oidColumn.SystemType ), oidColumn.Name );
120 ················}
121 ············}
122
123 ············foreach (var e in this.persistentFields)
124 ············{
125 ················Type memberType;
126 ················if (e.Value is FieldInfo)
127 ····················memberType = ((FieldInfo)e.Value).FieldType;
128 ················else
129 ····················memberType = ((PropertyInfo)e.Value).PropertyType;
130
131 ················var fieldMapping = classMapping.FindField( (string)e.Key );
132
133 ················// Ignore fields without mapping.
134 ················if (null == fieldMapping)
135 ····················continue;
136
137 ················if (null == fieldMapping.Column.DbType)
138 ················{
139 ····················fieldMapping.ColumnDbType = (int)provider.GetDbType( memberType );
140 ················}
141 ················else
142 ················{
143 ····················fieldMapping.ColumnDbType = (int)provider.GetDbType( fieldMapping.Column.DbType );
144 ················}
145
146 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( fieldMapping.Column.Name ), fieldMapping.ColumnDbType, ParameterLength( fieldMapping, memberType ), fieldMapping.Column.Name );
147 ············}
148
149 ············foreach (RelationFieldInfo ri in relationInfos)
150 ············{
151 ················Relation r = ri.Rel;
152 ················foreach (ForeignKeyColumn fkColumn in r.ForeignKeyColumns)
153 ················{
154 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( fkColumn.Name ), provider.GetDbType( fkColumn.SystemType ), provider.GetDefaultLength( fkColumn.SystemType ), fkColumn.Name );
155 ················}
156 ················if (r.ForeignKeyTypeColumnName != null)
157 ················{
158 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( r.ForeignKeyTypeColumnName ), provider.GetDbType( typeof( int ) ), provider.GetDefaultLength( typeof( int ) ), r.ForeignKeyTypeColumnName );
159 ················}
160
161 ············}
162
163 ············if (this.timeStampColumn != null)
164 ············{
165 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( timeStampColumn ), provider.GetDbType( typeof( Guid ) ), guidlength, this.timeStampColumn );
166 ············}
167
168 ············if (this.typeNameColumn != null)
169 ············{
170 ················Type tncType = Type.GetType( this.typeNameColumn.NetType );
171 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( typeNameColumn.Name ), provider.GetDbType( tncType ), provider.GetDefaultLength( tncType ), this.typeNameColumn.Name );
172 ············}
173
174 ············string sql;
175 ············//{0} = TableName: Mitarbeiter············
176 ············//{1} = FieldList: vorname, nachname
177 ············//{2} = NamedParamList mit @: @vorname, @nachname
178 ············//{3} = FieldList mit Id: id, vorname, nachname
179 ············//{4} = Name der Id-Spalte
180 ············if (hasAutoincrementedColumn && provider.SupportsLastInsertedId && provider.SupportsInsertBatch)
181 ············{
182 ················sql = "INSERT INTO {0} ({1}) VALUES ({2}); SELECT {3} FROM {0} WHERE ({4} = " + provider.GetLastInsertedId(this.tableName, this.autoIncrementColumn.Name) + ")";
183 ················sql = string.Format(sql, qualifiedTableName, this.fieldList, this.namedParamList, selectFieldList, this.autoIncrementColumn.Name);
184 ················this.insertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
185 ············}
186 ············else
187 ············{
188 ················sql = "INSERT INTO {0} ({1}) VALUES ({2})";
189 ················sql = string.Format(sql, qualifiedTableName, this.fieldList, this.namedParamList);
190 ············}
191 ············if (hasAutoincrementedColumn && !provider.SupportsInsertBatch)
192 ············{
193 ················if (provider.SupportsLastInsertedId)
194 ····················provider.RegisterRowUpdateHandler(this);
195 ················else
196 ····················throw new NDOException(32, "The provider of type " + provider.GetType().FullName + " doesn't support Autonumbered Ids. Use Self generated Ids instead.");
197 ············}
198 ············this.insertCommand.CommandText = sql;
199 ············this.insertCommand.Connection = this.conn;
200 ········}
201
202 ········/// <summary>
203 ········/// Row update handler for providers that require Row Update Handling
204 ········/// </summary>
205 ········/// <param name="row"></param>
206 ········public void OnRowUpdate(DataRow row)
207 ········{
208 ············if (row.RowState == DataRowState.Deleted)
209 ················return;
210
211 ············if (!hasAutoincrementedColumn)
212 ················return;
213 ············
214 ············string oidColumnName = this.autoIncrementColumn.Name;
215 ············Type t = row[oidColumnName].GetType();
216 ············if (t != typeof(int))
217 ················return;
218 ············
219 ············// Ist schon eine ID vergeben?
220 ············if (((int)row[oidColumnName]) > 0)
221 ················return;
222 ············bool unchanged = (row.RowState == DataRowState.Unchanged);
223 ············IDbCommand cmd = provider.NewSqlCommand(this.conn);
224
225 ············cmd.CommandText = provider.GetLastInsertedId(this.tableName, this.autoIncrementColumn.Name);
226 ············DumpBatch(cmd.CommandText);
227
228 ············using (IDataReader reader = cmd.ExecuteReader())
229 ············{
230 ················if (reader.Read())
231 ················{
232 ····················object oidValue = reader.GetValue(0);
233 ····················if ( oidValue == DBNull.Value )
234 ························this.logger.LogDebug( oidColumnName + " = DbNull" );
235 ····················else
236 ························this.logger.LogDebug( oidColumnName + " = " + oidValue );
237
238 ····················row[oidColumnName] = oidValue;
239 ····················if (unchanged)
240 ························row.AcceptChanges();
241 ················}
242 ················else
243 ····················throw new NDOException(33, "Can't read autonumbered id from the database.");
244 ············}
245 ········}
246
247 ········private void GenerateUpdateCommand()
248 ········{
249 ············string sql;
250
251 ············NDO.Mapping.Field fieldMapping;
252
253 ············sql = @"UPDATE {0} SET {1} WHERE ({2})";
254
255 ········
256 ············//{0} = Tabellenname: Mitarbeiter
257 ············//{1} = Zuweisungsliste: vorname = @vorname, nachname = @nachname
258 ············//{2} = Where-Bedingung: id = @Original_id [ AND TimeStamp = @Original_timestamp ]
259 ············AssignmentGenerator assignmentGenerator = new AssignmentGenerator(this.classMapping);
260 ············string zuwListe = assignmentGenerator.Result;
261
262 ············foreach (var e in this.persistentFields)
263 ············{
264 ················Type memberType;
265 ················if (e.Value is FieldInfo)
266 ····················memberType = ((FieldInfo)e.Value).FieldType;
267 ················else
268 ····················memberType = ((PropertyInfo)e.Value).PropertyType;
269
270 ················fieldMapping = classMapping.FindField( (string)e.Key );
271 ················if (fieldMapping != null)
272 ················{
273 ····················provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + fieldMapping.Column.Name ), fieldMapping.ColumnDbType, ParameterLength( fieldMapping, memberType ), fieldMapping.Column.Name );
274 ················}
275 ············}
276
277 ············foreach (RelationFieldInfo ri in relationInfos)
278 ············{
279 ················Relation r = ri.Rel;
280 ················if (r.Multiplicity == RelationMultiplicity.Element && r.MappingTable == null
281 ····················|| r.Multiplicity == RelationMultiplicity.List && r.MappingTable == null && r.Parent.FullName != classMapping.FullName)
282 ················{
283 ····················foreach (ForeignKeyColumn fkColumn in r.ForeignKeyColumns)
284 ····················{
285 ························Type systemType = fkColumn.SystemType;
286 ························provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + fkColumn.Name ), provider.GetDbType( systemType ), provider.GetDefaultLength( systemType ), fkColumn.Name );
287 ····················}
288 ····················if (r.ForeignKeyTypeColumnName != null)
289 ····················{
290 ························provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + r.ForeignKeyTypeColumnName ), provider.GetDbType( typeof( int ) ), provider.GetDefaultLength( typeof( int ) ), r.ForeignKeyTypeColumnName );
291 ····················}
292 ················}
293 ············}
294
295 ············string where = string.Empty;
296
297 ············if (this.timeStampColumn != null)
298 ············{
299 ················if (provider.UseNamedParams)
300 ····················where += provider.GetQuotedName(timeStampColumn) + " = " + provider.GetNamedParameter("U_Original_" + timeStampColumn) + " AND ";
301 ················else
302 ····················where += provider.GetQuotedName(timeStampColumn) + " = ? AND ";
303 ················// The new timestamp value as parameter
304 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, timeStampColumn);
305 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), timeStampColumn, System.Data.DataRowVersion.Original, null);
306 ············}
307
308 ············int oidCount = classMapping.Oid.OidColumns.Count;
309 ············for (int i = 0; i < oidCount; i++)
310 ············{
311 ················OidColumn oidColumn = (OidColumn)classMapping.Oid.OidColumns[i];
312 ················// Oid as parameter
313 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + oidColumn.Name), provider.GetDbType(oidColumn.SystemType), oidColumn.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), oidColumn.Name, System.Data.DataRowVersion.Original, null);
314 ················if (provider.UseNamedParams)
315 ····················where += provider.GetQuotedName(oidColumn.Name) + " = " + provider.GetNamedParameter("U_Original_" + oidColumn.Name);
316 ················else
317 ····················where += provider.GetQuotedName(oidColumn.Name) + " = ?";
318
319 ················Relation r = oidColumn.Relation;
320 ················if (!this.hasGuidOid && r != null && r.ForeignKeyTypeColumnName != null)
321 ················{
322 ····················where += " AND " +
323 ························provider.GetQuotedName(r.ForeignKeyTypeColumnName) + " = " + provider.GetNamedParameter("U_Original_" + r.ForeignKeyTypeColumnName);
324 ····················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + r.ForeignKeyTypeColumnName), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), r.ForeignKeyTypeColumnName, System.Data.DataRowVersion.Original, null);
325 ················}
326
327 ················if (i < oidCount - 1)
328 ····················where += " AND ";
329 ············}
330 ············//else
331 ············//{
332 ············//····// Dual oids are defined using two relations.
333 ············//····MultiKeyHandler dkh = new MultiKeyHandler(this.classMapping);
334 ················
335 ············//····for (int i = 0; i < 2; i++)
336 ············//····{
337 ············//········where += provider.GetQuotedName(dkh.ForeignKeyColumnName(i)) + " = " + provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyColumnName(i));
338 ············//········provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyColumnName(i)), provider.GetDbType(dkh.GetClass(i).Oid.FieldType), dkh.GetClass(i).Oid.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), dkh.ForeignKeyColumnName(i), System.Data.DataRowVersion.Original, null);
339 ············//········if (dkh.ForeignKeyTypeColumnName(i) != null && dkh.GetClass(i).Oid.FieldType != typeof(Guid))
340 ············//········{
341 ············//············where += " AND " +
342 ············//················provider.GetQuotedName(dkh.ForeignKeyTypeColumnName(i)) + " = " + provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyTypeColumnName(i));
343 ············//············provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyTypeColumnName(i)), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), dkh.ForeignKeyTypeColumnName(i), System.Data.DataRowVersion.Original, null);
344 ············//········}
345 ············//········if (i == 0)
346 ············//············where += " AND ";
347 ············//····}
348 ············//}
349
350 ············sql = string.Format(sql, qualifiedTableName, zuwListe, where);
351 ············//Console.WriteLine(sql);
352 ············this.updateCommand.CommandText = sql;
353 ········}
354
355
356 ········private void GenerateDeleteCommand()
357 ········{
358 ············string sql = "DELETE FROM {0} WHERE ({1})";
359 ············//{0} = Tabellenname: Mitarbeiter
360 ············//{1} = Where-Bedingung: id = @Original_id
361
362 ············string where = string.Empty;
363
364 ············int oidCount = this.classMapping.Oid.OidColumns.Count;
365 ············for(int i = 0; i < oidCount; i++)
366 ············{
367 ················OidColumn oidColumn = (OidColumn)this.classMapping.Oid.OidColumns[i];
368 ················if (provider.UseNamedParams)
369 ····················where += provider.GetQuotedName(oidColumn.Name) + " = " + provider.GetNamedParameter("D_Original_" + oidColumn.Name);
370 ················else
371 ····················where += provider.GetQuotedName(oidColumn.Name) + " = ?";
372 ················provider.AddParameter(deleteCommand, provider.GetNamedParameter("D_Original_" + oidColumn.Name), provider.GetDbType(oidColumn.SystemType), oidColumn.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), oidColumn.Name, System.Data.DataRowVersion.Original, null);
373
374 ················Relation r = oidColumn.Relation;
375 ················if (!this.hasGuidOid && r != null && r.ForeignKeyTypeColumnName != null)
376 ················{
377 ····················where += " AND " +
378 ························provider.GetQuotedName(r.ForeignKeyTypeColumnName) + " = " + provider.GetNamedParameter("D_Original_" + r.ForeignKeyTypeColumnName);
379 ····················provider.AddParameter(updateCommand, provider.GetNamedParameter("D_Original_" + r.ForeignKeyTypeColumnName), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), r.ForeignKeyTypeColumnName, System.Data.DataRowVersion.Original, null);
380 ················}
381
382 ················if (i < oidCount - 1)
383 ····················where += " AND ";
384 ············}
385
386 ············string whereTS = string.Empty;
387 ············if (this.timeStampColumn != null)
388 ············{
389 ················if (provider.UseNamedParams)
390 ····················whereTS = " AND " + provider.GetQuotedName(timeStampColumn) + " = " + provider.GetNamedParameter("D_Original_" + timeStampColumn);
391 ················else
392 ····················whereTS = " AND " + provider.GetQuotedName(timeStampColumn) + " = ?";
393 ················provider.AddParameter(deleteCommand, provider.GetNamedParameter("D_Original_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), timeStampColumn, System.Data.DataRowVersion.Original, null);
394 ············}
395
396 ············sql = string.Format(sql, qualifiedTableName, where + whereTS);
397 ············this.deleteCommand.CommandText = sql;
398 ········}
399
400 ········private void CollectFields()
401 ········{
402 ············FieldMap fm = new FieldMap(this.classMapping);
403 ············this.persistentFields = fm.PersistentFields;
404 ········}
405
406 ········/// <summary>
407 ········/// Initializes the PersistenceHandler
408 ········/// </summary>
409 ········/// <param name="ndoMapping">Mapping information.</param>
410 ········/// <param name="t">Type for which the Handler is constructed.</param>
411 ········/// <param name="disposeCallback">Method to be called at the end of the usage. The method can be used to push back the object to the PersistenceHandlerPool.</param>
412 ········public void Initialize(NDOMapping ndoMapping, Type t, Action<Type,IPersistenceHandler> disposeCallback)
413 ········{
414 ············this.ndoMapping = ndoMapping;
415 ············this.classMapping = ndoMapping.FindClass(t);
416 ············this.timeStampColumn = classMapping.TimeStampColumn;
417 ············this.typeNameColumn = classMapping.TypeNameColumn;
418 ············this.hasAutoincrementedColumn = false;
419 ············foreach (OidColumn oidColumn in this.classMapping.Oid.OidColumns)
420 ············{
421 ················if (oidColumn.AutoIncremented)
422 ················{
423 ····················this.hasAutoincrementedColumn = true;
424 ····················this.autoIncrementColumn = oidColumn;
425 ····················break;
426 ················}
427 ············}
428 ············this.hasGuidOid = this.classMapping.HasGuidOid;
429 ············this.tableName = classMapping.TableName;
430 ············Connection connInfo = ndoMapping.FindConnection(classMapping);
431 ············this.provider = ndoMapping.GetProvider(connInfo);
432 ············this.qualifiedTableName = provider.GetQualifiedTableName( tableName );
433 ············// The connection object will be initialized by the pm, to
434 ············// enable connection string housekeeping.
435 ············// CheckTransaction is the place, where this happens.
436 ············this.conn = null;
437
438 ············var columnListGenerator = SqlColumnListGenerator.Get( classMapping );····
439 ············this.hollowFields = columnListGenerator.HollowFields;
440 ············this.hollowFieldsWithAlias = columnListGenerator.HollowFieldsWithAlias;
441 ············this.namedParamList = columnListGenerator.ParamList;
442 ············this.fieldList = columnListGenerator.BaseList;
443 ············this.selectFieldList = columnListGenerator.SelectList;
444 ············this.selectFieldListWithAlias = columnListGenerator.SelectListWithAlias;
445 ············this.guidlength = provider.GetDefaultLength(typeof(Guid));
446 ············if (this.guidlength == 0)
447 ················this.guidlength = provider.SupportsNativeGuidType ? 16 : 36;
448 ············this.disposeCallback = disposeCallback;
449
450
451 ············this.selectCommand = provider.NewSqlCommand(conn);
452 ············this.insertCommand = provider.NewSqlCommand(conn);
453 ············this.updateCommand = provider.NewSqlCommand(conn);
454 ············this.deleteCommand = provider.NewSqlCommand(conn);
455 ············this.dataAdapter = provider.NewDataAdapter(selectCommand, updateCommand, insertCommand, deleteCommand);
456 ············this.type = t;
457
458 ············CollectFields();····// Alle Feldinformationen landen in persistentField
459 ············// determine the relations, which have a foreign key
460 ············// column in the table of the given class
461 ············relationInfos = new RelationCollector(this.classMapping)
462 ················.CollectRelations().ToList();
463
464
465 ············GenerateSelectCommand();
466 ············GenerateInsertCommand();
467 ············GenerateUpdateCommand();
468 ············GenerateDeleteCommand();
469 ········}
470 ····
471 ········#region Implementation of IPersistenceHandler
472
473 ········/// <summary>
474 ········/// Saves Changes to a DataTable
475 ········/// </summary>
476 ········/// <param name="dt"></param>
477 ········public void Update(DataTable dt)
478 ········{
479 ············DataRow[] rows = null;
480 ············try
481 ············{
482 ················rows = Select(dt, DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
483 ················if (rows.Length == 0)
484 ····················return;
485 ················Dump(rows);
486 ················if (this.timeStampColumn != null)
487 ················{
488 ····················Guid newTs = Guid.NewGuid();
489 ····················foreach(DataRow r in rows)
490 ························r[timeStampColumn] = newTs;
491 ················}
492 ················dataAdapter.Update(rows);
493 ············}
494 ············catch (System.Data.DBConcurrencyException dbex)
495 ············{
496 ················if (this.ConcurrencyError != null)
497 ················{
498 ····················// This is a Firebird Hack because Fb doesn't set the row
499 ····················if (dbex.Row == null)
500 ····················{
501 ························foreach (DataRow r in rows)
502 ························{
503 ····························if (r.RowState == DataRowState.Added ||
504 ································r.RowState == DataRowState.Modified)
505 ····························{
506 ································dbex.Row = r;
507 ································break;
508 ····························}
509 ························}
510 ····················}
511 ····················ConcurrencyError( dbex );
512 ················}
513 ················else
514 ················{
515 ····················throw;
516 ················}
517 ············}
518 ············catch (System.Exception ex)
519 ············{
520 ················string text = "Exception of type " + ex.GetType().Name + " while updating or inserting data rows: " + ex.Message + "\n";
521 ················if ((ex.Message.IndexOf("Die Variable") > -1 && ex.Message.IndexOf("muss deklariert") > -1) || (ex.Message.IndexOf("Variable") > -1 && ex.Message.IndexOf("declared") > -1))
522 ····················text += "Check the field names in the mapping file.\n";
523 ················text += "Sql Update statement: " + updateCommand.CommandText + "\n";
524 ················text += "Sql Insert statement: " + insertCommand.CommandText;
525 ················throw new NDOException(37, text);
526 ············}
527 ········}
528
529
530 ········private void DumpBatch(string sql)
531 ········{
532 ············this.logger.LogDebug( "Batch: \r\n" + sql );
533 ········}
534
535 ········private void Dump(DataRow[] rows)
536 ········{
537 ············new SqlDumper(this.loggerFactory, this.provider, insertCommand, selectCommand, updateCommand, deleteCommand).Dump(rows);
538 ········}
539
540 ········DataRow[] Select(DataTable dt, DataViewRowState rowState)
541 ········{
542 ············// Mono Hack: Some rows in Mono are null after Select.
543 ············DataRow[] rows = dt.Select(null, null, rowState).Where(dr=>dr != null).ToArray();
544 ············return rows;
545 ········}
546
547 ········/// <summary>
548 ········/// Delets all rows of a DataTable marked as deleted
549 ········/// </summary>
550 ········/// <param name="dt"></param>
551 ········public void UpdateDeletedObjects(DataTable dt)
552 ········{
553 ············DataRow[] rows = Select(dt, DataViewRowState.Deleted);
554 ············if (rows.Length == 0) return;
555 ············Dump(rows);
556 ············try
557 ············{
558 ················dataAdapter.Update(rows);
559 ············}
560 ············catch (System.Data.DBConcurrencyException dbex)
561 ············{
562 ················if (this.ConcurrencyError != null)
563 ····················ConcurrencyError(dbex);
564 ················else
565 ····················throw;
566 ············}
567 ············catch (System.Exception ex)
568 ············{
569 ················string text = "Exception of type " + ex.GetType().Name + " while deleting data rows: " + ex.Message + "\n";
570 ················text += "Sql statement: " + deleteCommand.CommandText + "\n";
571 ················throw new NDOException(38, text);
572 ············}
573 ········}
574
575 ········private DataTable GetTemplateTable(DataSet templateDataset, string name)
576 ········{
577 ············// The instance of ds is actually static,
578 ············// since the SqlPersistenceHandler lives as
579 ············// a static instance in the PersistenceHandlerCache.
580 ············DataTable dt = templateDataset.Tables[name];
581 ············if (dt == null)
582 ················throw new NDOException(39, "Can't find table '" + name + "' in the schema. Check your mapping file.");
583 ············return dt;
584 ········}
585
586 ········/// <summary>
587 ········/// Executes a batch of sql statements.
588 ········/// </summary>
589 ········/// <param name="statements">Each element in the array is a sql statement.</param>
590 ········/// <param name="parameters">A list of parameters (see remarks).</param>
591 ········/// <returns>An List of Hashtables, containing the Name/Value pairs of the results.</returns>
592 ········/// <remarks>
593 ········/// For emty resultsets an empty Hashtable will be returned.
594 ········/// If parameters is a NDOParameterCollection, the parameters in the collection are valid for
595 ········/// all subqueries. If parameters is an ordinary IList, NDO expects to find a NDOParameterCollection
596 ········/// for each subquery. If an element is null, no parameters are submitted for the given query.
597 ········/// </remarks>
598 ········public IList<Dictionary<string, object>> ExecuteBatch( string[] statements, IList parameters )
599 ········{
600 ············List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
601 ············bool closeIt = false;
602 ············IDataReader dr = null;
603 ············int i;
604 ············try
605 ············{
606 ················if (this.conn.State != ConnectionState.Open)
607 ················{
608 ····················closeIt = true;
609 ····················this.conn.Open();
610 ················}
611 ················string sql = string.Empty;
612
613 ················if (this.provider.SupportsBulkCommands)
614 ················{
615 ····················IDbCommand cmd = this.provider.NewSqlCommand( conn );
616 ····················sql = this.provider.GenerateBulkCommand( statements );
617 ····················cmd.CommandText = sql;
618 ····················if (parameters != null && parameters.Count > 0)
619 ····················{
620 ························// Only the first command gets parameters
621 ························for (i = 0; i < statements.Length; i++)
622 ························{
623 ····························if (i == 0)
624 ································CreateQueryParameters( cmd, parameters );
625 ····························else
626 ································CreateQueryParameters( null, null );
627 ························}
628 ····················}
629
630 ····················// cmd.CommandText can be changed in CreateQueryParameters
631 ····················DumpBatch( cmd.CommandText );
632 ····················if (this.transaction != null)
633 ························cmd.Transaction = this.transaction;
634
635 ····················dr = cmd.ExecuteReader();
636
637 ····················for (; ; )
638 ····················{
639 ························var dict = new Dictionary<string, object>();
640 ························while (dr.Read())
641 ························{
642 ····························for (i = 0; i < dr.FieldCount; i++)
643 ····························{
644 ································dict.Add( dr.GetName( i ), dr.GetValue( i ) );
645 ····························}
646 ························}
647 ························result.Add( dict );
648 ························if (!dr.NextResult())
649 ····························break;
650 ····················}
651
652 ····················dr.Close();
653 ················}
654 ················else
655 ················{
656 ····················for (i = 0; i < statements.Length; i++)
657 ····················{
658 ························string s = statements[i];
659 ························sql += s + ";\n"; // For DumpBatch only
660 ························var dict = new Dictionary<string, object>();
661 ························IDbCommand cmd = this.provider.NewSqlCommand( conn );
662
663 ························cmd.CommandText = s;
664 ························if (parameters != null && parameters.Count > 0)
665 ························{
666 ····························CreateQueryParameters( cmd, parameters );
667 ························}
668
669 ························if (this.transaction != null)
670 ····························cmd.Transaction = this.transaction;
671
672 ························dr = cmd.ExecuteReader();
673
674 ························while (dr.Read())
675 ························{
676 ····························for (int j = 0; j < dr.FieldCount; j++)
677 ····························{
678 ································dict.Add( dr.GetName( j ), dr.GetValue( j ) );
679 ····························}
680 ························}
681
682 ························dr.Close();
683 ························result.Add( dict );
684 ····················}
685
686 ····················DumpBatch( sql );
687 ················}
688 ············}
689 ············finally
690 ············{
691 ················if (dr != null && !dr.IsClosed)
692 ····················dr.Close();
693 ················if (closeIt)
694 ····················this.conn.Close();
695 ············}
696
697 ············return result;
698 ········}
699
700 ········private void CreateQueryParameters(IDbCommand command, IList parameters)
701 ········{
702 ············if (parameters == null || parameters.Count == 0)
703 ················return;
704
705 ············string sql = command.CommandText;
706
707 ············Regex regex = new Regex( @"\{(\d+)\}" );
708
709 ············MatchCollection matches = regex.Matches( sql );
710 ············Dictionary<string, object> tcValues = new Dictionary<string, object>();
711 ············int endIndex = parameters.Count - 1;
712 ············foreach (Match match in matches)
713 ············{
714 ················int nr = int.Parse( match.Groups[1].Value );
715 ················if (nr > endIndex)
716 ····················throw new QueryException( 10009, "Parameter-Reference " + match.Value + " has no matching parameter." );
717
718 ················sql = sql.Replace( match.Value,
719 ····················this.provider.GetNamedParameter( "p" + nr.ToString() ) );
720 ············}
721
722 ············command.CommandText = sql;
723
724 ············for (int i = 0; i < parameters.Count; i++)
725 ············{
726 ················object p = parameters[i];
727 ················if (p == null)
728 ····················p = DBNull.Value;
729 ················Type type = p.GetType();
730 ················if (type.FullName.StartsWith("System.Nullable`1"))
731 ····················type = type.GetGenericArguments()[0];
732 ················if (type == typeof( Guid ) && Guid.Empty.Equals( p ) || type == typeof( DateTime ) && DateTime.MinValue.Equals( p ))
733 ················{
734 ····················p = DBNull.Value;
735 ················}
736 ················if (type.IsEnum)
737 ················{
738 ····················type = Enum.GetUnderlyingType(type);
739 ····················p = ((IConvertible)p ).ToType(type, CultureInfo.CurrentCulture);
740 ················}
741 ················else if (type == typeof(Guid) && !provider.SupportsNativeGuidType)
742 ················{
743 ····················type = typeof(string);
744 ····················if (p != DBNull.Value)
745 ························p = p.ToString();
746 ················}
747 ················string name = "p" + i.ToString();
748 ················int length = this.provider.GetDefaultLength(type);
749 ················if (type == typeof(string))
750 ················{
751 ····················length = ((string)p).Length;
752 ····················if (provider.GetType().Name.IndexOf("Oracle") > -1)
753 ····················{
754 ························if (length == 0)
755 ····························throw new QueryException(10001, "Empty string parameters are not allowed in Oracle. Use IS NULL instead.");
756 ····················}
757 ················}
758 ················else if (type == typeof(byte[]))
759 ················{
760 ····················length = ((byte[])p).Length;
761 ················}
762 ················IDataParameter par = provider.AddParameter(
763 ····················command,
764 ····················this.provider.GetNamedParameter(name),
765 ····················this.provider.GetDbType( type == typeof( DBNull ) ? typeof( string ) : type ),
766 ····················length,
767 ····················this.provider.GetQuotedName(name));
768 ················par.Value = p;
769 ················par.Direction = ParameterDirection.Input;····················
770 ············}
771 ········}
772
773 ········/// <summary>
774 ········/// Performs a query and returns a DataTable
775 ········/// </summary>
776 ········/// <param name="sql"></param>
777 ········/// <param name="parameters"></param>
778 ········/// <param name="templateDataSet"></param>
779 ········/// <returns></returns>
780 ········public DataTable PerformQuery( string sql, IList parameters, DataSet templateDataSet )
781 ········{
782 ············if (sql.Trim().StartsWith( "EXEC", StringComparison.InvariantCultureIgnoreCase ))
783 ················this.selectCommand.CommandType = CommandType.StoredProcedure;
784 ············else
785 ················this.selectCommand.CommandType = CommandType.Text;
786
787 ············DataTable table = GetTemplateTable(templateDataSet, this.tableName).Clone();
788
789 ············this.selectCommand.CommandText = sql;
790
791 ············CreateQueryParameters(this.selectCommand, parameters);
792
793 ············Dump(null); // Dumps the Select Command
794
795 ············try
796 ············{
797 ················dataAdapter.Fill(table);
798 ············}
799 ············catch (System.Exception ex)
800 ············{
801 ················string text = "Exception of type " + ex.GetType().Name + " while executing a Query: " + ex.Message + "\n";
802 ················text += "Sql Statement: " + sql + "\n";
803 ················throw new NDOException(40, text);
804 ············}
805
806 ············return table;········
807 ········}
808
809 ········/// <summary>
810 ········/// Gets a Handler which can store data in relation tables
811 ········/// </summary>
812 ········/// <param name="r">Relation information</param>
813 ········/// <returns>The handler</returns>
814 ········public IMappingTableHandler GetMappingTableHandler(Relation r)
815 ········{
816 ············IMappingTableHandler handler;
817 ············if (!mappingTableHandlers.TryGetValue( r.FieldName, out handler ))
818 ············{
819 ················handler = new NDOMappingTableHandler( this.loggerFactory );
820 ················handler.Initialize(ndoMapping, r);
821 ················mappingTableHandlers[r.FieldName] = handler;
822 ············}
823 ············return handler;
824 ········}
825
826 ········/// <summary>
827 ········/// Disposes a SqlPersistenceHandler
828 ········/// </summary>
829 ········public void Dispose()
830 ········{
831 ············this.disposeCallback( this.type, this );
832 ········}
833
834 ········/// <summary>
835 ········/// Gets or sets the connection to be used for the handler
836 ········/// </summary>
837 ········public IDbConnection Connection
838 ········{
839 ············get { return this.conn; }
840 ············set
841 ············{
842 ················this.conn = value;
843 ················this.selectCommand.Connection = value;
844 ················this.deleteCommand.Connection = value;
845 ················this.updateCommand.Connection = value;
846 ················this.insertCommand.Connection = value;
847 ············}
848 ········}
849
850 ········/// <summary>
851 ········/// Gets or sets the connection to be used for the handler
852 ········/// </summary>
853 ········public IDbTransaction Transaction
854 ········{
855 ············get { return this.transaction; }
856 ············set
857 ············{
858 ················this.transaction = value;
859 ················this.selectCommand.Transaction = value;
860 ················this.deleteCommand.Transaction = value;
861 ················this.updateCommand.Transaction = value;
862 ················this.insertCommand.Transaction = value;
863 ············}
864 ········}
865
866
867 ········/// <summary>
868 ········/// Gets the current DataAdapter.
869 ········/// </summary>
870 ········/// <remarks>
871 ········/// This is needed by RegisterRowUpdateHandler.
872 ········/// See the comment in SqlCeProvider.
873 ········/// </remarks>
874 ········public DbDataAdapter DataAdapter => this.dataAdapter;
875
876 ········#endregion
877 ····}
878 }
879
New Commit (60aa080)
1 //
2 // Copyright (c) 2002-2016 Mirko Matytschak
3 // (www.netdataobjects.de)
4 //
5 // Author: Mirko Matytschak
6 //
7 // Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
8 // documentation files (the "Software"), to deal in the Software without restriction, including without limitation
9 // the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the
10 // Software, and to permit persons to whom the Software is furnished to do so, subject to the following
11 // conditions:
12
13 // The above copyright notice and this permission notice shall be included in all copies or substantial portions
14 // of the Software.
15 //
16 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
17 // TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
18 // THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
19 // CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
20 // DEALINGS IN THE SOFTWARE.
21
22
23 using System;
24 using System.Text.RegularExpressions;
25 using System.Reflection;
26 using System.Collections;
27 using System.Collections.Generic;
28 using System.Linq;
29 using System.Data;
30 using System.Data.Common;
31 using NDO.Mapping;
32 using NDOInterfaces;
33 using NDO.Query;
34 using System.Globalization;
35 using Microsoft.Extensions.Logging;
36 using Microsoft.Extensions.DependencyInjection;
37
38 namespace NDO.SqlPersistenceHandling
39 {
40 ····/// <summary>
41 ····/// Parameter type for the IProvider function RegisterRowUpdateHandler
42 ····/// </summary>
43 ····public delegate void RowUpdateHandler(DataRow row);
44
45 ····/// <summary>
46 ····/// Summary description for NDOPersistenceHandler.
47 ····/// </summary>
48 ····///
49 ····public class SqlPersistenceHandler : IPersistenceHandler
50 ····{
51 ········/// <summary>
52 ········/// This event will be triggered, if a concurrency error occurs
53 ········/// </summary>
54 ········public event ConcurrencyErrorHandler ConcurrencyError;
55
56 ········private IDbCommand selectCommand;
57 ········private IDbCommand insertCommand;
58 ········private IDbCommand updateCommand;
59 ········private IDbCommand deleteCommand;
60 ········private IDbConnection conn;
61 ········private IDbTransaction transaction;
62 ········private DbDataAdapter dataAdapter;
63 ········private Class classMapping;
64 ········private string selectFieldList;
65 ········private string selectFieldListWithAlias;
66 ········private string tableName;
67 ········private string qualifiedTableName;
68 ········private ILogger logger;
69 ········private Dictionary<string, IMappingTableHandler> mappingTableHandlers = new Dictionary<string, IMappingTableHandler>();
70 ········private IProvider provider;
71 ········private NDOMapping ndoMapping;
72 ········private string timeStampColumn = null;
73 ········private Column typeNameColumn = null;
74 ········private bool hasAutoincrementedColumn;
75 ········private OidColumn autoIncrementColumn;
76 ········private Dictionary<string,MemberInfo> persistentFields;
77 ········private List<RelationFieldInfo> relationInfos;
78 ········private Type type;
79 ········private int guidlength;
80 ········private string hollowFields;
81 ········private string hollowFieldsWithAlias;
82 ········private string fieldList;
83 ········private string namedParamList;
84 ········private bool hasGuidOid;
85 ········private readonly IServiceProvider serviceProvider;
86 ········private readonly ILoggerFactory loggerFactory;
87 ········private Action<Type,IPersistenceHandler> disposeCallback;
88
89 ········/// <summary>
90 ········/// Constructs a SqlPersistenceHandler object
91 ········/// </summary>
92 ········/// <param name="serviceProvider"></param>
93 ········public SqlPersistenceHandler(IServiceProvider serviceProvider)
94 ········{
95 ············this.serviceProvider = serviceProvider;
96 ············this.logger = serviceProvider.GetRequiredService<ILogger<SqlPersistenceHandler>>();
97 ············this.loggerFactory = serviceProvider.GetRequiredService<ILoggerFactory>();
98 ········}
99
100 ········private void GenerateSelectCommand()
101 ········{
102 ············this.selectCommand.CommandText = string.Empty;
103 ········}
104
105 ········private int ParameterLength(Mapping.Field fieldMapping, Type memberType)
106 ········{
107 ············if (0 == fieldMapping.Column.Size)
108 ················return provider.GetDefaultLength(memberType);
109 ············else
110 ················return fieldMapping.Column.Size;
111 ········}
112
113 ········private void GenerateInsertCommand()
114 ········{
115 ············// Generate Parameters
116 ············foreach (OidColumn oidColumn in this.classMapping.Oid.OidColumns)
117 ············{
118 ················if (!oidColumn.AutoIncremented && oidColumn.FieldName == null && oidColumn.RelationName == null)
119 ················{
120 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( oidColumn.Name ), provider.GetDbType( oidColumn.SystemType ), provider.GetDefaultLength( oidColumn.SystemType ), oidColumn.Name );
121 ················}
122 ············}
123
124 ············foreach (var e in this.persistentFields)
125 ············{
126 ················Type memberType;
127 ················if (e.Value is FieldInfo)
128 ····················memberType = ((FieldInfo)e.Value).FieldType;
129 ················else
130 ····················memberType = ((PropertyInfo)e.Value).PropertyType;
131
132 ················var fieldMapping = classMapping.FindField( (string)e.Key );
133
134 ················// Ignore fields without mapping.
135 ················if (null == fieldMapping)
136 ····················continue;
137
138 ················if (null == fieldMapping.Column.DbType)
139 ················{
140 ····················fieldMapping.ColumnDbType = (int)provider.GetDbType( memberType );
141 ················}
142 ················else
143 ················{
144 ····················fieldMapping.ColumnDbType = (int)provider.GetDbType( fieldMapping.Column.DbType );
145 ················}
146
147 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( fieldMapping.Column.Name ), fieldMapping.ColumnDbType, ParameterLength( fieldMapping, memberType ), fieldMapping.Column.Name );
148 ············}
149
150 ············foreach (RelationFieldInfo ri in relationInfos)
151 ············{
152 ················Relation r = ri.Rel;
153 ················foreach (ForeignKeyColumn fkColumn in r.ForeignKeyColumns)
154 ················{
155 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( fkColumn.Name ), provider.GetDbType( fkColumn.SystemType ), provider.GetDefaultLength( fkColumn.SystemType ), fkColumn.Name );
156 ················}
157 ················if (r.ForeignKeyTypeColumnName != null)
158 ················{
159 ····················provider.AddParameter( insertCommand, provider.GetNamedParameter( r.ForeignKeyTypeColumnName ), provider.GetDbType( typeof( int ) ), provider.GetDefaultLength( typeof( int ) ), r.ForeignKeyTypeColumnName );
160 ················}
161
162 ············}
163
164 ············if (this.timeStampColumn != null)
165 ············{
166 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( timeStampColumn ), provider.GetDbType( typeof( Guid ) ), guidlength, this.timeStampColumn );
167 ············}
168
169 ············if (this.typeNameColumn != null)
170 ············{
171 ················Type tncType = Type.GetType( this.typeNameColumn.NetType );
172 ················provider.AddParameter( insertCommand, provider.GetNamedParameter( typeNameColumn.Name ), provider.GetDbType( tncType ), provider.GetDefaultLength( tncType ), this.typeNameColumn.Name );
173 ············}
174
175 ············string sql;
176 ············//{0} = TableName: Mitarbeiter············
177 ············//{1} = FieldList: vorname, nachname
178 ············//{2} = NamedParamList mit @: @vorname, @nachname
179 ············//{3} = FieldList mit Id: id, vorname, nachname
180 ············//{4} = Name der Id-Spalte
181 ············if (hasAutoincrementedColumn && provider.SupportsLastInsertedId && provider.SupportsInsertBatch)
182 ············{
183 ················sql = "INSERT INTO {0} ({1}) VALUES ({2}); SELECT {3} FROM {0} WHERE ({4} = " + provider.GetLastInsertedId(this.tableName, this.autoIncrementColumn.Name) + ")";
184 ················sql = string.Format(sql, qualifiedTableName, this.fieldList, this.namedParamList, selectFieldList, this.autoIncrementColumn.Name);
185 ················this.insertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
186 ············}
187 ············else
188 ············{
189 ················sql = "INSERT INTO {0} ({1}) VALUES ({2})";
190 ················sql = string.Format(sql, qualifiedTableName, this.fieldList, this.namedParamList);
191 ············}
192 ············if (hasAutoincrementedColumn && !provider.SupportsInsertBatch)
193 ············{
194 ················if (provider.SupportsLastInsertedId)
195 ····················provider.RegisterRowUpdateHandler(this);
196 ················else
197 ····················throw new NDOException(32, "The provider of type " + provider.GetType().FullName + " doesn't support Autonumbered Ids. Use Self generated Ids instead.");
198 ············}
199 ············this.insertCommand.CommandText = sql;
200 ············this.insertCommand.Connection = this.conn;
201 ········}
202
203 ········/// <summary>
204 ········/// Row update handler for providers that require Row Update Handling
205 ········/// </summary>
206 ········/// <param name="row"></param>
207 ········public void OnRowUpdate(DataRow row)
208 ········{
209 ············if (row.RowState == DataRowState.Deleted)
210 ················return;
211
212 ············if (!hasAutoincrementedColumn)
213 ················return;
214 ············
215 ············string oidColumnName = this.autoIncrementColumn.Name;
216 ············Type t = row[oidColumnName].GetType();
217 ············if (t != typeof(int))
218 ················return;
219 ············
220 ············// Ist schon eine ID vergeben?
221 ············if (((int)row[oidColumnName]) > 0)
222 ················return;
223 ············bool unchanged = (row.RowState == DataRowState.Unchanged);
224 ············IDbCommand cmd = provider.NewSqlCommand(this.conn);
225
226 ············cmd.CommandText = provider.GetLastInsertedId(this.tableName, this.autoIncrementColumn.Name);
227 ············DumpBatch(cmd.CommandText);
228
229 ············using (IDataReader reader = cmd.ExecuteReader())
230 ············{
231 ················if (reader.Read())
232 ················{
233 ····················object oidValue = reader.GetValue(0);
234 ····················if ( oidValue == DBNull.Value )
235 ························this.logger.LogDebug( oidColumnName + " = DbNull" );
236 ····················else
237 ························this.logger.LogDebug( oidColumnName + " = " + oidValue );
238
239 ····················row[oidColumnName] = oidValue;
240 ····················if (unchanged)
241 ························row.AcceptChanges();
242 ················}
243 ················else
244 ····················throw new NDOException(33, "Can't read autonumbered id from the database.");
245 ············}
246 ········}
247
248 ········private void GenerateUpdateCommand()
249 ········{
250 ············string sql;
251
252 ············NDO.Mapping.Field fieldMapping;
253
254 ············sql = @"UPDATE {0} SET {1} WHERE ({2})";
255
256 ········
257 ············//{0} = Tabellenname: Mitarbeiter
258 ············//{1} = Zuweisungsliste: vorname = @vorname, nachname = @nachname
259 ············//{2} = Where-Bedingung: id = @Original_id [ AND TimeStamp = @Original_timestamp ]
260 ············AssignmentGenerator assignmentGenerator = new AssignmentGenerator(this.classMapping);
261 ············string zuwListe = assignmentGenerator.Result;
262
263 ············foreach (var e in this.persistentFields)
264 ············{
265 ················Type memberType;
266 ················if (e.Value is FieldInfo)
267 ····················memberType = ((FieldInfo)e.Value).FieldType;
268 ················else
269 ····················memberType = ((PropertyInfo)e.Value).PropertyType;
270
271 ················fieldMapping = classMapping.FindField( (string)e.Key );
272 ················if (fieldMapping != null)
273 ················{
274 ····················provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + fieldMapping.Column.Name ), fieldMapping.ColumnDbType, ParameterLength( fieldMapping, memberType ), fieldMapping.Column.Name );
275 ················}
276 ············}
277
278 ············foreach (RelationFieldInfo ri in relationInfos)
279 ············{
280 ················Relation r = ri.Rel;
281 ················if (r.Multiplicity == RelationMultiplicity.Element && r.MappingTable == null
282 ····················|| r.Multiplicity == RelationMultiplicity.List && r.MappingTable == null && r.Parent.FullName != classMapping.FullName)
283 ················{
284 ····················foreach (ForeignKeyColumn fkColumn in r.ForeignKeyColumns)
285 ····················{
286 ························Type systemType = fkColumn.SystemType;
287 ························provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + fkColumn.Name ), provider.GetDbType( systemType ), provider.GetDefaultLength( systemType ), fkColumn.Name );
288 ····················}
289 ····················if (r.ForeignKeyTypeColumnName != null)
290 ····················{
291 ························provider.AddParameter( updateCommand, provider.GetNamedParameter( "U_" + r.ForeignKeyTypeColumnName ), provider.GetDbType( typeof( int ) ), provider.GetDefaultLength( typeof( int ) ), r.ForeignKeyTypeColumnName );
292 ····················}
293 ················}
294 ············}
295
296 ············string where = string.Empty;
297
298 ············if (this.timeStampColumn != null)
299 ············{
300 ················if (provider.UseNamedParams)
301 ····················where += provider.GetQuotedName(timeStampColumn) + " = " + provider.GetNamedParameter("U_Original_" + timeStampColumn) + " AND ";
302 ················else
303 ····················where += provider.GetQuotedName(timeStampColumn) + " = ? AND ";
304 ················// The new timestamp value as parameter
305 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, timeStampColumn);
306 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), timeStampColumn, System.Data.DataRowVersion.Original, null);
307 ············}
308
309 ············int oidCount = classMapping.Oid.OidColumns.Count;
310 ············for (int i = 0; i < oidCount; i++)
311 ············{
312 ················OidColumn oidColumn = (OidColumn)classMapping.Oid.OidColumns[i];
313 ················// Oid as parameter
314 ················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + oidColumn.Name), provider.GetDbType(oidColumn.SystemType), oidColumn.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), oidColumn.Name, System.Data.DataRowVersion.Original, null);
315 ················if (provider.UseNamedParams)
316 ····················where += provider.GetQuotedName(oidColumn.Name) + " = " + provider.GetNamedParameter("U_Original_" + oidColumn.Name);
317 ················else
318 ····················where += provider.GetQuotedName(oidColumn.Name) + " = ?";
319
320 ················Relation r = oidColumn.Relation;
321 ················if (!this.hasGuidOid && r != null && r.ForeignKeyTypeColumnName != null)
322 ················{
323 ····················where += " AND " +
324 ························provider.GetQuotedName(r.ForeignKeyTypeColumnName) + " = " + provider.GetNamedParameter("U_Original_" + r.ForeignKeyTypeColumnName);
325 ····················provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + r.ForeignKeyTypeColumnName), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), r.ForeignKeyTypeColumnName, System.Data.DataRowVersion.Original, null);
326 ················}
327
328 ················if (i < oidCount - 1)
329 ····················where += " AND ";
330 ············}
331 ············//else
332 ············//{
333 ············//····// Dual oids are defined using two relations.
334 ············//····MultiKeyHandler dkh = new MultiKeyHandler(this.classMapping);
335 ················
336 ············//····for (int i = 0; i < 2; i++)
337 ············//····{
338 ············//········where += provider.GetQuotedName(dkh.ForeignKeyColumnName(i)) + " = " + provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyColumnName(i));
339 ············//········provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyColumnName(i)), provider.GetDbType(dkh.GetClass(i).Oid.FieldType), dkh.GetClass(i).Oid.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), dkh.ForeignKeyColumnName(i), System.Data.DataRowVersion.Original, null);
340 ············//········if (dkh.ForeignKeyTypeColumnName(i) != null && dkh.GetClass(i).Oid.FieldType != typeof(Guid))
341 ············//········{
342 ············//············where += " AND " +
343 ············//················provider.GetQuotedName(dkh.ForeignKeyTypeColumnName(i)) + " = " + provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyTypeColumnName(i));
344 ············//············provider.AddParameter(updateCommand, provider.GetNamedParameter("U_Original_" + dkh.ForeignKeyTypeColumnName(i)), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), dkh.ForeignKeyTypeColumnName(i), System.Data.DataRowVersion.Original, null);
345 ············//········}
346 ············//········if (i == 0)
347 ············//············where += " AND ";
348 ············//····}
349 ············//}
350
351 ············sql = string.Format(sql, qualifiedTableName, zuwListe, where);
352 ············//Console.WriteLine(sql);
353 ············this.updateCommand.CommandText = sql;
354 ········}
355
356
357 ········private void GenerateDeleteCommand()
358 ········{
359 ············string sql = "DELETE FROM {0} WHERE ({1})";
360 ············//{0} = Tabellenname: Mitarbeiter
361 ············//{1} = Where-Bedingung: id = @Original_id
362
363 ············string where = string.Empty;
364
365 ············int oidCount = this.classMapping.Oid.OidColumns.Count;
366 ············for(int i = 0; i < oidCount; i++)
367 ············{
368 ················OidColumn oidColumn = (OidColumn)this.classMapping.Oid.OidColumns[i];
369 ················if (provider.UseNamedParams)
370 ····················where += provider.GetQuotedName(oidColumn.Name) + " = " + provider.GetNamedParameter("D_Original_" + oidColumn.Name);
371 ················else
372 ····················where += provider.GetQuotedName(oidColumn.Name) + " = ?";
373 ················provider.AddParameter(deleteCommand, provider.GetNamedParameter("D_Original_" + oidColumn.Name), provider.GetDbType(oidColumn.SystemType), oidColumn.TypeLength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), oidColumn.Name, System.Data.DataRowVersion.Original, null);
374
375 ················Relation r = oidColumn.Relation;
376 ················if (!this.hasGuidOid && r != null && r.ForeignKeyTypeColumnName != null)
377 ················{
378 ····················where += " AND " +
379 ························provider.GetQuotedName(r.ForeignKeyTypeColumnName) + " = " + provider.GetNamedParameter("D_Original_" + r.ForeignKeyTypeColumnName);
380 ····················provider.AddParameter(updateCommand, provider.GetNamedParameter("D_Original_" + r.ForeignKeyTypeColumnName), provider.GetDbType(typeof(int)), provider.GetDefaultLength(typeof(int)), System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), r.ForeignKeyTypeColumnName, System.Data.DataRowVersion.Original, null);
381 ················}
382
383 ················if (i < oidCount - 1)
384 ····················where += " AND ";
385 ············}
386
387 ············string whereTS = string.Empty;
388 ············if (this.timeStampColumn != null)
389 ············{
390 ················if (provider.UseNamedParams)
391 ····················whereTS = " AND " + provider.GetQuotedName(timeStampColumn) + " = " + provider.GetNamedParameter("D_Original_" + timeStampColumn);
392 ················else
393 ····················whereTS = " AND " + provider.GetQuotedName(timeStampColumn) + " = ?";
394 ················provider.AddParameter(deleteCommand, provider.GetNamedParameter("D_Original_" + timeStampColumn), provider.GetDbType(typeof(Guid)), guidlength, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), timeStampColumn, System.Data.DataRowVersion.Original, null);
395 ············}
396
397 ············sql = string.Format(sql, qualifiedTableName, where + whereTS);
398 ············this.deleteCommand.CommandText = sql;
399 ········}
400
401 ········private void CollectFields()
402 ········{
403 ············FieldMap fm = new FieldMap(this.classMapping);
404 ············this.persistentFields = fm.PersistentFields;
405 ········}
406
407 ········/// <summary>
408 ········/// Initializes the PersistenceHandler
409 ········/// </summary>
410 ········/// <param name="ndoMapping">Mapping information.</param>
411 ········/// <param name="t">Type for which the Handler is constructed.</param>
412 ········/// <param name="disposeCallback">Method to be called at the end of the usage. The method can be used to push back the object to the PersistenceHandlerPool.</param>
413 ········public void Initialize(NDOMapping ndoMapping, Type t, Action<Type,IPersistenceHandler> disposeCallback)
414 ········{
415 ············this.ndoMapping = ndoMapping;
416 ············this.classMapping = ndoMapping.FindClass(t);
417 ············this.timeStampColumn = classMapping.TimeStampColumn;
418 ············this.typeNameColumn = classMapping.TypeNameColumn;
419 ············this.hasAutoincrementedColumn = false;
420 ············foreach (OidColumn oidColumn in this.classMapping.Oid.OidColumns)
421 ············{
422 ················if (oidColumn.AutoIncremented)
423 ················{
424 ····················this.hasAutoincrementedColumn = true;
425 ····················this.autoIncrementColumn = oidColumn;
426 ····················break;
427 ················}
428 ············}
429 ············this.hasGuidOid = this.classMapping.HasGuidOid;
430 ············this.tableName = classMapping.TableName;
431 ············Connection connInfo = ndoMapping.FindConnection(classMapping);
432 ············this.provider = ndoMapping.GetProvider(connInfo);
433 ············this.qualifiedTableName = provider.GetQualifiedTableName( tableName );
434 ············// The connection object will be initialized by the pm, to
435 ············// enable connection string housekeeping.
436 ············// CheckTransaction is the place, where this happens.
437 ············this.conn = null;
438
439 ············var columnListGenerator = SqlColumnListGenerator.Get( classMapping );····
440 ············this.hollowFields = columnListGenerator.HollowFields;
441 ············this.hollowFieldsWithAlias = columnListGenerator.HollowFieldsWithAlias;
442 ············this.namedParamList = columnListGenerator.ParamList;
443 ············this.fieldList = columnListGenerator.BaseList;
444 ············this.selectFieldList = columnListGenerator.SelectList;
445 ············this.selectFieldListWithAlias = columnListGenerator.SelectListWithAlias;
446 ············this.guidlength = provider.GetDefaultLength(typeof(Guid));
447 ············if (this.guidlength == 0)
448 ················this.guidlength = provider.SupportsNativeGuidType ? 16 : 36;
449 ············this.disposeCallback = disposeCallback;
450
451
452 ············this.selectCommand = provider.NewSqlCommand(conn);
453 ············this.insertCommand = provider.NewSqlCommand(conn);
454 ············this.updateCommand = provider.NewSqlCommand(conn);
455 ············this.deleteCommand = provider.NewSqlCommand(conn);
456 ············this.dataAdapter = provider.NewDataAdapter(selectCommand, updateCommand, insertCommand, deleteCommand);
457 ············this.type = t;
458
459 ············CollectFields();····// Alle Feldinformationen landen in persistentField
460 ············// determine the relations, which have a foreign key
461 ············// column in the table of the given class
462 ············relationInfos = new RelationCollector(this.classMapping)
463 ················.CollectRelations().ToList();
464
465
466 ············GenerateSelectCommand();
467 ············GenerateInsertCommand();
468 ············GenerateUpdateCommand();
469 ············GenerateDeleteCommand();
470 ········}
471 ····
472 ········#region Implementation of IPersistenceHandler
473
474 ········/// <summary>
475 ········/// Saves Changes to a DataTable
476 ········/// </summary>
477 ········/// <param name="dt"></param>
478 ········public void Update(DataTable dt)
479 ········{
480 ············DataRow[] rows = null;
481 ············try
482 ············{
483 ················rows = Select(dt, DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
484 ················if (rows.Length == 0)
485 ····················return;
486 ················Dump(rows);
487 ················if (this.timeStampColumn != null)
488 ················{
489 ····················Guid newTs = Guid.NewGuid();
490 ····················foreach(DataRow r in rows)
491 ························r[timeStampColumn] = newTs;
492 ················}
493 ················dataAdapter.Update(rows);
494 ············}
495 ············catch (System.Data.DBConcurrencyException dbex)
496 ············{
497 ················if (this.ConcurrencyError != null)
498 ················{
499 ····················// This is a Firebird Hack because Fb doesn't set the row
500 ····················if (dbex.Row == null)
501 ····················{
502 ························foreach (DataRow r in rows)
503 ························{
504 ····························if (r.RowState == DataRowState.Added ||
505 ································r.RowState == DataRowState.Modified)
506 ····························{
507 ································dbex.Row = r;
508 ································break;
509 ····························}
510 ························}
511 ····················}
512 ····················ConcurrencyError( dbex );
513 ················}
514 ················else
515 ················{
516 ····················throw;
517 ················}
518 ············}
519 ············catch (System.Exception ex)
520 ············{
521 ················string text = "Exception of type " + ex.GetType().Name + " while updating or inserting data rows: " + ex.Message + "\n";
522 ················if ((ex.Message.IndexOf("Die Variable") > -1 && ex.Message.IndexOf("muss deklariert") > -1) || (ex.Message.IndexOf("Variable") > -1 && ex.Message.IndexOf("declared") > -1))
523 ····················text += "Check the field names in the mapping file.\n";
524 ················text += "Sql Update statement: " + updateCommand.CommandText + "\n";
525 ················text += "Sql Insert statement: " + insertCommand.CommandText;
526 ················throw new NDOException(37, text);
527 ············}
528 ········}
529
530
531 ········private void DumpBatch(string sql)
532 ········{
533 ············this.logger.LogDebug( "Batch: \r\n" + sql );
534 ········}
535
536 ········private void Dump(DataRow[] rows)
537 ········{
538 ············new SqlDumper(this.loggerFactory, this.provider, insertCommand, selectCommand, updateCommand, deleteCommand).Dump(rows);
539 ········}
540
541 ········DataRow[] Select(DataTable dt, DataViewRowState rowState)
542 ········{
543 ············// Mono Hack: Some rows in Mono are null after Select.
544 ············DataRow[] rows = dt.Select(null, null, rowState).Where(dr=>dr != null).ToArray();
545 ············return rows;
546 ········}
547
548 ········/// <summary>
549 ········/// Delets all rows of a DataTable marked as deleted
550 ········/// </summary>
551 ········/// <param name="dt"></param>
552 ········public void UpdateDeletedObjects(DataTable dt)
553 ········{
554 ············DataRow[] rows = Select(dt, DataViewRowState.Deleted);
555 ············if (rows.Length == 0) return;
556 ············Dump(rows);
557 ············try
558 ············{
559 ················dataAdapter.Update(rows);
560 ············}
561 ············catch (System.Data.DBConcurrencyException dbex)
562 ············{
563 ················if (this.ConcurrencyError != null)
564 ····················ConcurrencyError(dbex);
565 ················else
566 ····················throw;
567 ············}
568 ············catch (System.Exception ex)
569 ············{
570 ················string text = "Exception of type " + ex.GetType().Name + " while deleting data rows: " + ex.Message + "\n";
571 ················text += "Sql statement: " + deleteCommand.CommandText + "\n";
572 ················throw new NDOException(38, text);
573 ············}
574 ········}
575
576 ········private DataTable GetTemplateTable(DataSet templateDataset, string name)
577 ········{
578 ············// The instance of ds is actually static,
579 ············// since the SqlPersistenceHandler lives as
580 ············// a static instance in the PersistenceHandlerCache.
581 ············DataTable dt = templateDataset.Tables[name];
582 ············if (dt == null)
583 ················throw new NDOException(39, "Can't find table '" + name + "' in the schema. Check your mapping file.");
584 ············return dt;
585 ········}
586
587 ········/// <summary>
588 ········/// Executes a batch of sql statements.
589 ········/// </summary>
590 ········/// <param name="statements">Each element in the array is a sql statement.</param>
591 ········/// <param name="parameters">A list of parameters (see remarks).</param>
592 ········/// <returns>An List of Hashtables, containing the Name/Value pairs of the results.</returns>
593 ········/// <remarks>
594 ········/// For emty resultsets an empty Hashtable will be returned.
595 ········/// If parameters is a NDOParameterCollection, the parameters in the collection are valid for
596 ········/// all subqueries. If parameters is an ordinary IList, NDO expects to find a NDOParameterCollection
597 ········/// for each subquery. If an element is null, no parameters are submitted for the given query.
598 ········/// </remarks>
599 ········public IList<Dictionary<string, object>> ExecuteBatch( string[] statements, IList parameters )
600 ········{
601 ············List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
602 ············bool closeIt = false;
603 ············IDataReader dr = null;
604 ············int i;
605 ············try
606 ············{
607 ················if (this.conn.State != ConnectionState.Open)
608 ················{
609 ····················closeIt = true;
610 ····················this.conn.Open();
611 ················}
612 ················string sql = string.Empty;
613
614 ················if (this.provider.SupportsBulkCommands)
615 ················{
616 ····················IDbCommand cmd = this.provider.NewSqlCommand( conn );
617 ····················sql = this.provider.GenerateBulkCommand( statements );
618 ····················cmd.CommandText = sql;
619 ····················if (parameters != null && parameters.Count > 0)
620 ····················{
621 ························// Only the first command gets parameters
622 ························for (i = 0; i < statements.Length; i++)
623 ························{
624 ····························if (i == 0)
625 ································CreateQueryParameters( cmd, parameters );
626 ····························else
627 ································CreateQueryParameters( null, null );
628 ························}
629 ····················}
630
631 ····················// cmd.CommandText can be changed in CreateQueryParameters
632 ····················DumpBatch( cmd.CommandText );
633 ····················if (this.transaction != null)
634 ························cmd.Transaction = this.transaction;
635
636 ····················dr = cmd.ExecuteReader();
637
638 ····················for (; ; )
639 ····················{
640 ························var dict = new Dictionary<string, object>();
641 ························while (dr.Read())
642 ························{
643 ····························for (i = 0; i < dr.FieldCount; i++)
644 ····························{
645 ································dict.Add( dr.GetName( i ), dr.GetValue( i ) );
646 ····························}
647 ························}
648 ························result.Add( dict );
649 ························if (!dr.NextResult())
650 ····························break;
651 ····················}
652
653 ····················dr.Close();
654 ················}
655 ················else
656 ················{
657 ····················for (i = 0; i < statements.Length; i++)
658 ····················{
659 ························string s = statements[i];
660 ························sql += s + ";\n"; // For DumpBatch only
661 ························var dict = new Dictionary<string, object>();
662 ························IDbCommand cmd = this.provider.NewSqlCommand( conn );
663
664 ························cmd.CommandText = s;
665 ························if (parameters != null && parameters.Count > 0)
666 ························{
667 ····························CreateQueryParameters( cmd, parameters );
668 ························}
669
670 ························if (this.transaction != null)
671 ····························cmd.Transaction = this.transaction;
672
673 ························dr = cmd.ExecuteReader();
674
675 ························while (dr.Read())
676 ························{
677 ····························for (int j = 0; j < dr.FieldCount; j++)
678 ····························{
679 ································dict.Add( dr.GetName( j ), dr.GetValue( j ) );
680 ····························}
681 ························}
682
683 ························dr.Close();
684 ························result.Add( dict );
685 ····················}
686
687 ····················DumpBatch( sql );
688 ················}
689 ············}
690 ············finally
691 ············{
692 ················if (dr != null && !dr.IsClosed)
693 ····················dr.Close();
694 ················if (closeIt)
695 ····················this.conn.Close();
696 ············}
697
698 ············return result;
699 ········}
700
701 ········private void CreateQueryParameters(IDbCommand command, IList parameters)
702 ········{
703 ············if (parameters == null || parameters.Count == 0)
704 ················return;
705
706 ············string sql = command.CommandText;
707
708 ············Regex regex = new Regex( @"\{(\d+)\}" );
709
710 ············MatchCollection matches = regex.Matches( sql );
711 ············Dictionary<string, object> tcValues = new Dictionary<string, object>();
712 ············int endIndex = parameters.Count - 1;
713 ············foreach (Match match in matches)
714 ············{
715 ················int nr = int.Parse( match.Groups[1].Value );
716 ················if (nr > endIndex)
717 ····················throw new QueryException( 10009, "Parameter-Reference " + match.Value + " has no matching parameter." );
718
719 ················sql = sql.Replace( match.Value,
720 ····················this.provider.GetNamedParameter( "p" + nr.ToString() ) );
721 ············}
722
723 ············command.CommandText = sql;
724
725 ············for (int i = 0; i < parameters.Count; i++)
726 ············{
727 ················object p = parameters[i];
728 ················if (p == null)
729 ····················p = DBNull.Value;
730 ················Type type = p.GetType();
731 ················if (type.FullName.StartsWith("System.Nullable`1"))
732 ····················type = type.GetGenericArguments()[0];
733 ················if (type == typeof( Guid ) && Guid.Empty.Equals( p ) || type == typeof( DateTime ) && DateTime.MinValue.Equals( p ))
734 ················{
735 ····················p = DBNull.Value;
736 ················}
737 ················if (type.IsEnum)
738 ················{
739 ····················type = Enum.GetUnderlyingType(type);
740 ····················p = ((IConvertible)p ).ToType(type, CultureInfo.CurrentCulture);
741 ················}
742 ················else if (type == typeof(Guid) && !provider.SupportsNativeGuidType)
743 ················{
744 ····················type = typeof(string);
745 ····················if (p != DBNull.Value)
746 ························p = p.ToString();
747 ················}
748 ················string name = "p" + i.ToString();
749 ················int length = this.provider.GetDefaultLength(type);
750 ················if (type == typeof(string))
751 ················{
752 ····················length = ((string)p).Length;
753 ····················if (provider.GetType().Name.IndexOf("Oracle") > -1)
754 ····················{
755 ························if (length == 0)
756 ····························throw new QueryException(10001, "Empty string parameters are not allowed in Oracle. Use IS NULL instead.");
757 ····················}
758 ················}
759 ················else if (type == typeof(byte[]))
760 ················{
761 ····················length = ((byte[])p).Length;
762 ················}
763 ················IDataParameter par = provider.AddParameter(
764 ····················command,
765 ····················this.provider.GetNamedParameter(name),
766 ····················this.provider.GetDbType( type == typeof( DBNull ) ? typeof( string ) : type ),
767 ····················length,
768 ····················this.provider.GetQuotedName(name));
769 ················par.Value = p;
770 ················par.Direction = ParameterDirection.Input;····················
771 ············}
772 ········}
773
774 ········/// <summary>
775 ········/// Performs a query and returns a DataTable
776 ········/// </summary>
777 ········/// <param name="sql"></param>
778 ········/// <param name="parameters"></param>
779 ········/// <param name="templateDataSet"></param>
780 ········/// <returns></returns>
781 ········public DataTable PerformQuery( string sql, IList parameters, DataSet templateDataSet )
782 ········{
783 ············if (sql.Trim().StartsWith( "EXEC", StringComparison.InvariantCultureIgnoreCase ))
784 ················this.selectCommand.CommandType = CommandType.StoredProcedure;
785 ············else
786 ················this.selectCommand.CommandType = CommandType.Text;
787
788 ············DataTable table = GetTemplateTable(templateDataSet, this.tableName).Clone();
789
790 ············this.selectCommand.CommandText = sql;
791
792 ············CreateQueryParameters(this.selectCommand, parameters);
793
794 ············Dump(null); // Dumps the Select Command
795
796 ············try
797 ············{
798 ················dataAdapter.Fill(table);
799 ············}
800 ············catch (System.Exception ex)
801 ············{
802 ················string text = "Exception of type " + ex.GetType().Name + " while executing a Query: " + ex.Message + "\n";
803 ················text += "Sql Statement: " + sql + "\n";
804 ················throw new NDOException(40, text);
805 ············}
806
807 ············return table;········
808 ········}
809
810 ········/// <summary>
811 ········/// Gets a Handler which can store data in relation tables
812 ········/// </summary>
813 ········/// <param name="r">Relation information</param>
814 ········/// <returns>The handler</returns>
815 ········public IMappingTableHandler GetMappingTableHandler(Relation r)
816 ········{
817 ············IMappingTableHandler handler;
818 ············if (!mappingTableHandlers.TryGetValue( r.FieldName, out handler ))
819 ············{
820 ················handler = new NDOMappingTableHandler( this.loggerFactory );
821 ················handler.Initialize(ndoMapping, r);
822 ················mappingTableHandlers[r.FieldName] = handler;
823 ············}
824 ············return handler;
825 ········}
826
827 ········/// <summary>
828 ········/// Disposes a SqlPersistenceHandler
829 ········/// </summary>
830 ········public void Dispose()
831 ········{
832 ············this.disposeCallback( this.type, this );
833 ········}
834
835 ········/// <summary>
836 ········/// Gets or sets the connection to be used for the handler
837 ········/// </summary>
838 ········public IDbConnection Connection
839 ········{
840 ············get { return this.conn; }
841 ············set
842 ············{
843 ················this.conn = value;
844 ················this.selectCommand.Connection = value;
845 ················this.deleteCommand.Connection = value;
846 ················this.updateCommand.Connection = value;
847 ················this.insertCommand.Connection = value;
848 ············}
849 ········}
850
851 ········/// <summary>
852 ········/// Gets or sets the connection to be used for the handler
853 ········/// </summary>
854 ········public IDbTransaction Transaction
855 ········{
856 ············get { return this.transaction; }
857 ············set
858 ············{
859 ················this.transaction = value;
860 ················this.selectCommand.Transaction = value;
861 ················this.deleteCommand.Transaction = value;
862 ················this.updateCommand.Transaction = value;
863 ················this.insertCommand.Transaction = value;
864 ············}
865 ········}
866
867
868 ········/// <summary>
869 ········/// Gets the current DataAdapter.
870 ········/// </summary>
871 ········/// <remarks>
872 ········/// This is needed by RegisterRowUpdateHandler.
873 ········/// See the comment in SqlCeProvider.
874 ········/// </remarks>
875 ········public DbDataAdapter DataAdapter => this.dataAdapter;
876
877 ········#endregion
878 ····}
879 }
880