2 // System.Data.SqlClient.SqlCommandBuilder.cs
5 // Tim Coleman (tim@timcoleman.com)
7 // Copyright (C) Tim Coleman, 2002
11 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
13 // Permission is hereby granted, free of charge, to any person obtaining
14 // a copy of this software and associated documentation files (the
15 // "Software"), to deal in the Software without restriction, including
16 // without limitation the rights to use, copy, modify, merge, publish,
17 // distribute, sublicense, and/or sell copies of the Software, and to
18 // permit persons to whom the Software is furnished to do so, subject to
19 // the following conditions:
21 // The above copyright notice and this permission notice shall be
22 // included in all copies or substantial portions of the Software.
24 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
25 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
26 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
27 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
28 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
29 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
30 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
34 using System.Collections;
35 using System.ComponentModel;
37 using System.Data.Common;
40 namespace System.Data.SqlClient {
42 public sealed class SqlCommandBuilder : DbCommandBuilder
44 public sealed class SqlCommandBuilder : Component
49 bool disposed = false;
51 DataTable dbSchemaTable;
52 SqlDataAdapter adapter;
58 SqlCommand deleteCommand;
59 SqlCommand insertCommand;
60 SqlCommand updateCommand;
62 // Used to construct WHERE clauses
63 static readonly string clause1 = "({0} = 1 AND {1} IS NULL)";
64 static readonly string clause2 = "({0} = {1})";
70 public SqlCommandBuilder ()
78 quoteSuffix = String.Empty;
79 quotePrefix = String.Empty;
83 public SqlCommandBuilder (SqlDataAdapter adapter)
86 DataAdapter = adapter;
89 #endregion // Constructors
94 [DataSysDescription ("The DataAdapter for which to automatically generate SqlCommands")]
97 public new SqlDataAdapter DataAdapter {
98 get { return adapter; }
101 adapter.RowUpdating -= new SqlRowUpdatingEventHandler (RowUpdatingHandler);
106 adapter.RowUpdating += new SqlRowUpdatingEventHandler (RowUpdatingHandler);
110 private string QuotedTableName {
111 get { return GetQuotedString (tableName); }
116 [DataSysDescription ("The character used in a text command as the opening quote for quoting identifiers that contain special characters.")]
118 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
124 get { return quotePrefix; }
126 if (dbSchemaTable != null)
127 throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
134 [DataSysDescription ("The character used in a text command as the closing quote for quoting identifiers that contain special characters. ")]
136 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
142 get { return quoteSuffix; }
144 if (dbSchemaTable != null)
145 throw new InvalidOperationException ("The QuotePrefix and QuoteSuffix properties cannot be changed once an Insert, Update, or Delete command has been generated.");
150 private SqlCommand SourceCommand {
153 return adapter.SelectCommand;
158 #endregion // Properties
162 private void BuildCache (bool closeConnection)
164 SqlCommand sourceCommand = SourceCommand;
165 if (sourceCommand == null)
166 throw new InvalidOperationException ("The DataAdapter.SelectCommand property needs to be initialized.");
167 SqlConnection connection = sourceCommand.Connection;
168 if (connection == null)
169 throw new InvalidOperationException ("The DataAdapter.SelectCommand.Connection property needs to be initialized.");
171 if (dbSchemaTable == null) {
172 if (connection.State == ConnectionState.Open)
173 closeConnection = false;
177 SqlDataReader reader = sourceCommand.ExecuteReader (CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
178 dbSchemaTable = reader.GetSchemaTable ();
182 BuildInformation (dbSchemaTable);
186 private void BuildInformation (DataTable schemaTable)
188 tableName = String.Empty;
189 foreach (DataRow schemaRow in schemaTable.Rows) {
190 if (schemaRow.IsNull ("BaseTableName") ||
191 schemaRow ["BaseTableName"] == String.Empty)
194 if (tableName == String.Empty)
195 tableName = (string) schemaRow ["BaseTableName"];
196 else if (tableName != (string) schemaRow["BaseTableName"])
197 throw new InvalidOperationException ("Dynamic SQL generation is not supported against multiple base tables.");
199 if (tableName == String.Empty)
200 throw new InvalidOperationException ("Dynamic SQL generation is not supported with no base table.");
201 dbSchemaTable = schemaTable;
204 private SqlCommand CreateDeleteCommand ()
206 // If no table was found, then we can't do an delete
207 if (QuotedTableName == String.Empty)
210 CreateNewCommand (ref deleteCommand);
212 string command = String.Format ("DELETE FROM {0}", QuotedTableName);
213 StringBuilder columns = new StringBuilder ();
214 StringBuilder whereClause = new StringBuilder ();
215 string dsColumnName = String.Empty;
216 bool keyFound = false;
219 foreach (DataRow schemaRow in dbSchemaTable.Rows) {
220 if ((bool)schemaRow["IsExpression"] == true)
222 if (!IncludedInWhereClause (schemaRow))
225 if (whereClause.Length > 0)
226 whereClause.Append (" AND ");
228 bool isKey = (bool) schemaRow ["IsKey"];
229 SqlParameter parameter = null;
234 //ms.net 1.1 generates the null check for columns even if AllowDBNull is false
235 //while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
236 //following the 2.0 approach
237 bool allowNull = (bool) schemaRow ["AllowDBNull"];
238 if (!isKey && allowNull) {
239 parameter = deleteCommand.Parameters.Add (String.Format ("@p{0}", parmIndex++),
241 String sourceColumnName = (string) schemaRow ["BaseColumnName"];
244 whereClause.Append ("(");
245 whereClause.Append (String.Format (clause1, parameter.ParameterName,
246 GetQuotedString (sourceColumnName)));
247 whereClause.Append (" OR ");
250 parameter = deleteCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
251 parameter.SourceVersion = DataRowVersion.Original;
253 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
255 if (!isKey && allowNull)
256 whereClause.Append (")");
259 throw new InvalidOperationException ("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
261 // We're all done, so bring it on home
262 string sql = String.Format ("{0} WHERE ({1})", command, whereClause.ToString ());
263 deleteCommand.CommandText = sql;
264 return deleteCommand;
267 private SqlCommand CreateInsertCommand ()
269 if (QuotedTableName == String.Empty)
272 CreateNewCommand (ref insertCommand);
274 string command = String.Format ("INSERT INTO {0}", QuotedTableName);
276 StringBuilder columns = new StringBuilder ();
277 StringBuilder values = new StringBuilder ();
278 string dsColumnName = String.Empty;
281 foreach (DataRow schemaRow in dbSchemaTable.Rows) {
282 if (!IncludedInInsert (schemaRow))
286 columns.Append (", ");
287 values.Append (", ");
290 SqlParameter parameter = insertCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
291 parameter.SourceVersion = DataRowVersion.Current;
293 columns.Append (GetQuotedString (parameter.SourceColumn));
294 values.Append (parameter.ParameterName);
297 sql = String.Format ("{0} ({1}) VALUES ({2})", command, columns.ToString (), values.ToString ());
298 insertCommand.CommandText = sql;
299 return insertCommand;
302 private void CreateNewCommand (ref SqlCommand command)
304 SqlCommand sourceCommand = SourceCommand;
305 if (command == null) {
306 command = sourceCommand.Connection.CreateCommand ();
307 command.CommandTimeout = sourceCommand.CommandTimeout;
308 command.Transaction = sourceCommand.Transaction;
310 command.CommandType = CommandType.Text;
311 command.UpdatedRowSource = UpdateRowSource.None;
312 command.Parameters.Clear ();
315 private SqlCommand CreateUpdateCommand ()
317 // If no table was found, then we can't do an update
318 if (QuotedTableName == String.Empty)
321 CreateNewCommand (ref updateCommand);
323 string command = String.Format ("UPDATE {0} SET ", QuotedTableName);
324 StringBuilder columns = new StringBuilder ();
325 StringBuilder whereClause = new StringBuilder ();
327 string dsColumnName = String.Empty;
328 bool keyFound = false;
330 // First, create the X=Y list for UPDATE
331 foreach (DataRow schemaRow in dbSchemaTable.Rows) {
332 if (!IncludedInUpdate (schemaRow))
334 if (columns.Length > 0)
335 columns.Append (", ");
337 SqlParameter parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
338 parameter.SourceVersion = DataRowVersion.Current;
340 columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
343 // Now, create the WHERE clause. This may be optimizable, but it would be ugly to incorporate
344 // into the loop above. "Premature optimization is the root of all evil." -- Knuth
345 foreach (DataRow schemaRow in dbSchemaTable.Rows) {
346 if ((bool)schemaRow["IsExpression"] == true)
349 if (!IncludedInWhereClause (schemaRow))
352 if (whereClause.Length > 0)
353 whereClause.Append (" AND ");
355 bool isKey = (bool) schemaRow ["IsKey"];
356 SqlParameter parameter = null;
362 //ms.net 1.1 generates the null check for columns even if AllowDBNull is false
363 //while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
364 //following the 2.0 approach
365 bool allowNull = (bool) schemaRow ["AllowDBNull"];
366 if (!isKey && allowNull) {
367 parameter = updateCommand.Parameters.Add (String.Format ("@p{0}", parmIndex++),
370 whereClause.Append ("(");
371 whereClause.Append (String.Format (clause1, parameter.ParameterName,
372 GetQuotedString ((string) schemaRow ["BaseColumnName"])));
373 whereClause.Append (" OR ");
376 parameter = updateCommand.Parameters.Add (CreateParameter (parmIndex++, schemaRow));
377 parameter.SourceVersion = DataRowVersion.Original;
379 whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
381 if (!isKey && allowNull)
382 whereClause.Append (")");
385 throw new InvalidOperationException ("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
387 // We're all done, so bring it on home
388 string sql = String.Format ("{0}{1} WHERE ({2})", command, columns.ToString (), whereClause.ToString ());
389 updateCommand.CommandText = sql;
390 return updateCommand;
393 private SqlParameter CreateParameter (int parmIndex, DataRow schemaRow)
395 string name = String.Format ("@p{0}", parmIndex);
396 string sourceColumn = (string) schemaRow ["BaseColumnName"];
397 SqlDbType sqlDbType = (SqlDbType) schemaRow ["ProviderType"];
398 int size = (int) schemaRow ["ColumnSize"];
400 return new SqlParameter (name, sqlDbType, size, sourceColumn);
403 public static void DeriveParameters (SqlCommand command)
405 command.DeriveParameters ();
408 protected override void Dispose (bool disposing)
412 if (insertCommand != null)
413 insertCommand.Dispose ();
414 if (deleteCommand != null)
415 deleteCommand.Dispose ();
416 if (updateCommand != null)
417 updateCommand.Dispose ();
418 if (dbSchemaTable != null)
419 dbSchemaTable.Dispose ();
429 SqlCommand GetDeleteCommand ()
432 if (deleteCommand == null)
433 return CreateDeleteCommand ();
434 return deleteCommand;
441 SqlCommand GetInsertCommand ()
444 if (insertCommand == null)
445 return CreateInsertCommand ();
446 return insertCommand;
449 private string GetQuotedString (string value)
451 if (value == String.Empty || value == null)
453 if (quotePrefix == String.Empty && quoteSuffix == String.Empty)
455 return String.Format ("{0}{1}{2}", quotePrefix, value, quoteSuffix);
462 SqlCommand GetUpdateCommand ()
465 if (updateCommand == null)
466 return CreateUpdateCommand ();
467 return updateCommand;
470 private bool IncludedInInsert (DataRow schemaRow)
472 // If the parameter has one of these properties, then we don't include it in the insert:
473 // AutoIncrement, Hidden, Expression, RowVersion, ReadOnly
475 if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
477 if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
479 if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
481 if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
483 if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
488 private bool IncludedInUpdate (DataRow schemaRow)
490 // If the parameter has one of these properties, then we don't include it in the insert:
491 // AutoIncrement, Hidden, RowVersion
493 if (!schemaRow.IsNull ("IsAutoIncrement") && (bool) schemaRow ["IsAutoIncrement"])
495 if (!schemaRow.IsNull ("IsHidden") && (bool) schemaRow ["IsHidden"])
497 if (!schemaRow.IsNull ("IsRowVersion") && (bool) schemaRow ["IsRowVersion"])
499 if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"])
501 if (!schemaRow.IsNull ("IsReadOnly") && (bool) schemaRow ["IsReadOnly"])
507 private bool IncludedInWhereClause (DataRow schemaRow)
509 if ((bool) schemaRow ["IsLong"])
514 [MonoTODO ("Figure out what else needs to be cleaned up when we refresh.")]
519 void RefreshSchema ()
521 tableName = String.Empty;
522 dbSchemaTable = null;
523 CreateNewCommand (ref deleteCommand);
524 CreateNewCommand (ref updateCommand);
525 CreateNewCommand (ref insertCommand);
530 protected override void ApplyParameterInfo (DbParameter dbParameter,
532 StatementType statementType,
535 throw new NotImplementedException ();
539 protected override string GetParameterName (int position)
541 throw new NotImplementedException ();
545 protected override string GetParameterName (string parameterName)
547 throw new NotImplementedException ();
552 protected override string GetParameterPlaceholder (int position)
554 throw new NotImplementedException ();
558 #endregion // Methods
560 #region Event Handlers
562 private void RowUpdatingHandler (object sender, SqlRowUpdatingEventArgs args)
564 if (args.Command != null)
567 switch (args.StatementType) {
568 case StatementType.Insert:
569 args.Command = GetInsertCommand ();
571 case StatementType.Update:
572 args.Command = GetUpdateCommand ();
574 case StatementType.Delete:
575 args.Command = GetDeleteCommand ();
578 } catch (Exception e) {
580 args.Status = UpdateStatus.ErrorsOccurred;
586 protected override void SetRowUpdatingHandler (DbDataAdapter adapter)
588 throw new NotImplementedException ();
592 #endregion // Event Handlers