using System.ComponentModel;
using System.Data;
+using System.Globalization;
using System.Text;
namespace System.Data.Common {
public abstract class DbCommandBuilder : Component
{
- bool _setAllValues = false;
- bool _disposed = false;
+ bool _setAllValues;
+ bool _disposed;
DataTable _dbSchemaTable;
- DbDataAdapter _dbDataAdapter = null;
+ DbDataAdapter _dbDataAdapter;
private CatalogLocation _catalogLocation = CatalogLocation.Start;
- private ConflictOption _conflictOption;
+ private ConflictOption _conflictOption = ConflictOption.CompareAllSearchableValues;
private string _tableName;
- private string _catalogSeperator = ".";
+ private string _catalogSeparator;
private string _quotePrefix;
private string _quoteSuffix;
- private string _schemaSeperator = ".";
+ private string _schemaSeparator;
private DbCommand _dbCommand;
- // Used to construct WHERE clauses
- static readonly string clause1 = "({0} = 1 AND {1} IS NULL)";
- static readonly string clause2 = "({0} = {1})";
-
DbCommand _deleteCommand;
DbCommand _insertCommand;
DbCommand _updateCommand;
- #region Constructors
+ static readonly string SEPARATOR_DEFAULT = ".";
+ // Used to construct WHERE clauses
+ static readonly string clause1 = "({0} = 1 AND {1} IS NULL)";
+ static readonly string clause2 = "({0} = {1})";
protected DbCommandBuilder ()
{
}
- #endregion // Constructors
-
- #region Properties
-
private void BuildCache (bool closeConnection)
{
DbCommand sourceCommand = SourceCommand;
get { return GetQuotedString (_tableName); }
}
+ bool IsCommandGenerated {
+ get {
+ return (_insertCommand != null || _updateCommand != null || _deleteCommand != null);
+ }
+ }
+
private string GetQuotedString (string value)
{
if (value == String.Empty || value == null)
return value;
- if (_quotePrefix == String.Empty && _quoteSuffix == String.Empty)
+
+ string prefix = QuotePrefix;
+ string suffix = QuoteSuffix;
+
+ if (prefix.Length == 0 && suffix.Length == 0)
return value;
- return String.Format ("{0}{1}{2}", _quotePrefix, value, _quoteSuffix);
+ return String.Format ("{0}{1}{2}", prefix, value, suffix);
}
private void BuildInformation (DataTable schemaTable)
// If no table was found, then we can't do an delete
if (QuotedTableName == String.Empty)
return null;
-
+
CreateNewCommand (ref _deleteCommand);
string command = String.Format ("DELETE FROM {0}", QuotedTableName);
- StringBuilder columns = new StringBuilder ();
StringBuilder whereClause = new StringBuilder ();
- string dsColumnName = String.Empty;
bool keyFound = false;
int parmIndex = 1;
foreach (DataRow schemaRow in _dbSchemaTable.Rows) {
- if ((bool)schemaRow["IsExpression"] == true)
+ if (!schemaRow.IsNull ("IsExpression") && (bool)schemaRow["IsExpression"] == true)
continue;
if (!IncludedInWhereClause (schemaRow))
continue;
bool isKey = (bool) schemaRow ["IsKey"];
DbParameter parameter = null;
+ string sourceColumnName;
if (isKey)
keyFound = true;
if (!isKey && allowNull) {
parameter = _deleteCommand.CreateParameter ();
if (option) {
- parameter.ParameterName = String.Format ("@{0}",
+ parameter.ParameterName = String.Format ("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
} else {
parameter.ParameterName = String.Format ("@p{0}", parmIndex++);
}
- String sourceColumnName = (string) schemaRow ["BaseColumnName"];
parameter.Value = 1;
-
+ parameter.DbType = DbType.Int32;
+ // This should be set for nullcheckparam
+ sourceColumnName = (string) schemaRow ["BaseColumnName"];
+ parameter.SourceColumn = sourceColumnName;
+ parameter.SourceColumnNullMapping = true;
+ parameter.SourceVersion = DataRowVersion.Original;
+ _deleteCommand.Parameters.Add (parameter);
+
whereClause.Append ("(");
whereClause.Append (String.Format (clause1, parameter.ParameterName,
GetQuotedString (sourceColumnName)));
whereClause.Append (" OR ");
}
- int index = 0;
- if (option) {
- index = CreateParameter (_deleteCommand, schemaRow);
- } else {
- index = CreateParameter (_deleteCommand, parmIndex++, schemaRow);
- }
- parameter = _deleteCommand.Parameters [index];
+ if (option)
+ parameter = CreateParameter (_deleteCommand, schemaRow, true);
+ else
+ parameter = CreateParameter (_deleteCommand, parmIndex++, schemaRow);
+
parameter.SourceVersion = DataRowVersion.Original;
+ ApplyParameterInfo (parameter, schemaRow, StatementType.Delete, true);
+ //parameter.IsNullable = allowNull;
whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
// We're all done, so bring it on home
string sql = String.Format ("{0} WHERE ({1})", command, whereClause.ToString ());
_deleteCommand.CommandText = sql;
+ _dbCommand = _deleteCommand;
return _deleteCommand;
}
- private DbCommand CreateInsertCommand (bool option)
+ private DbCommand CreateInsertCommand (bool option, DataRow row)
{
if (QuotedTableName == String.Empty)
return null;
string sql;
StringBuilder columns = new StringBuilder ();
StringBuilder values = new StringBuilder ();
- string dsColumnName = String.Empty;
int parmIndex = 1;
+ DbParameter parameter = null;
foreach (DataRow schemaRow in _dbSchemaTable.Rows) {
if (!IncludedInInsert (schemaRow))
continue;
- if (parmIndex > 1) {
+ if (columns.Length > 0) {
columns.Append (", ");
values.Append (", ");
}
- int index = -1;
- if (option) {
- index = CreateParameter (_insertCommand, schemaRow);
- } else {
- index = CreateParameter (_insertCommand, parmIndex++, schemaRow);
- }
- DbParameter parameter = _insertCommand.Parameters [index];
+ if (option)
+ parameter = CreateParameter (_insertCommand, schemaRow, false);
+ else
+ parameter = CreateParameter (_insertCommand, parmIndex++, schemaRow);
parameter.SourceVersion = DataRowVersion.Current;
-
+ ApplyParameterInfo (parameter, schemaRow, StatementType.Insert, false);
+
columns.Append (GetQuotedString (parameter.SourceColumn));
- values.Append (parameter.ParameterName);
+
+ // Workaround for columns that may have a default/bound value and for now,
+ // the framework, don't provide a mechanism to read these values yet
+ // AllowDBNull and DataRow is used to workaround #385028 by using DEFAULT
+ string colName = schemaRow ["ColumnName"] as string;
+ bool allowDBNull = !schemaRow.IsNull ("AllowDBNull") & (bool) schemaRow ["AllowDBNull"];
+ if (!allowDBNull && row != null &&
+ (row [colName] == DBNull.Value || row [colName] == null)) {
+ values.Append ("DEFAULT");
+ } else {
+ values.Append (parameter.ParameterName);
+ }
}
sql = String.Format ("{0} ({1}) VALUES ({2})", command, columns.ToString (), values.ToString ());
_insertCommand.CommandText = sql;
+ _dbCommand = _insertCommand;
return _insertCommand;
}
StringBuilder columns = new StringBuilder ();
StringBuilder whereClause = new StringBuilder ();
int parmIndex = 1;
- string dsColumnName = String.Empty;
bool keyFound = false;
+ DbParameter parameter = null;
// First, create the X=Y list for UPDATE
foreach (DataRow schemaRow in _dbSchemaTable.Rows) {
if (columns.Length > 0)
columns.Append (", ");
-
- int index = -1;
- if (option) {
- index = CreateParameter (_updateCommand, schemaRow);
- } else {
- index = CreateParameter (_updateCommand, parmIndex++, schemaRow);
- }
- DbParameter parameter = _updateCommand.Parameters [index];
+ if (option)
+ parameter = CreateParameter (_updateCommand, schemaRow, false);
+ else
+ parameter = CreateParameter (_updateCommand, parmIndex++, schemaRow);
parameter.SourceVersion = DataRowVersion.Current;
-
+ ApplyParameterInfo (parameter, schemaRow, StatementType.Update, false);
+ //parameter.IsNullable = (bool) schemaRow ["AllowDBNull"];
columns.Append (String.Format ("{0} = {1}", GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
}
// Now, create the WHERE clause. This may be optimizable, but it would be ugly to incorporate
// into the loop above. "Premature optimization is the root of all evil." -- Knuth
foreach (DataRow schemaRow in _dbSchemaTable.Rows) {
- if ((bool) schemaRow ["IsExpression"] == true)
+ if (!schemaRow.IsNull ("IsExpression") && (bool) schemaRow ["IsExpression"] == true)
continue;
if (!IncludedInWhereClause (schemaRow))
whereClause.Append (" AND ");
bool isKey = (bool) schemaRow ["IsKey"];
- DbParameter parameter = null;
-
if (isKey)
keyFound = true;
//while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
//following the 2.0 approach
bool allowNull = (bool) schemaRow ["AllowDBNull"];
- int index;
if (!isKey && allowNull) {
parameter = _updateCommand.CreateParameter ();
if (option) {
- parameter.ParameterName = String.Format ("@{0}",
+ parameter.ParameterName = String.Format ("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
} else {
parameter.ParameterName = String.Format ("@p{0}", parmIndex++);
}
+ parameter.DbType = DbType.Int32;
parameter.Value = 1;
+ parameter.SourceColumn = (string) schemaRow ["BaseColumnName"];
+ parameter.SourceColumnNullMapping = true;
+ parameter.SourceVersion = DataRowVersion.Original;
whereClause.Append ("(");
whereClause.Append (String.Format (clause1, parameter.ParameterName,
GetQuotedString ((string) schemaRow ["BaseColumnName"])));
whereClause.Append (" OR ");
+ _updateCommand.Parameters.Add (parameter);
}
- if (option) {
- index = CreateParameter (_updateCommand, schemaRow);
- } else {
- index = CreateParameter (_updateCommand, parmIndex++, schemaRow);
- }
- parameter = _updateCommand.Parameters [index];
+ if (option)
+ parameter = CreateParameter (_updateCommand, schemaRow, true);
+ else
+ parameter = CreateParameter (_updateCommand, parmIndex++, schemaRow);
parameter.SourceVersion = DataRowVersion.Original;
+ //parameter.IsNullable = allowNull;
+ ApplyParameterInfo (parameter, schemaRow, StatementType.Update, true);
whereClause.Append (String.Format (clause2, GetQuotedString (parameter.SourceColumn), parameter.ParameterName));
// We're all done, so bring it on home
string sql = String.Format ("{0}{1} WHERE ({2})", command, columns.ToString (), whereClause.ToString ());
_updateCommand.CommandText = sql;
+ _dbCommand = _updateCommand;
return _updateCommand;
}
- private int CreateParameter (DbCommand _dbCommand, int parmIndex, DataRow schemaRow)
+ private DbParameter CreateParameter (DbCommand _dbCommand, DataRow schemaRow, bool whereClause)
{
+ string sourceColumn = (string) schemaRow ["BaseColumnName"];
DbParameter parameter = _dbCommand.CreateParameter ();
- parameter.ParameterName = String.Format ("@p{0}", parmIndex);
- parameter.SourceColumn = (string) schemaRow ["BaseColumnName"];
- parameter.Size = (int) schemaRow ["ColumnSize"];
- return _dbCommand.Parameters.Add (parameter);
+ if (whereClause)
+ parameter.ParameterName = GetParameterName ("Original_" + sourceColumn);
+ else
+ parameter.ParameterName = GetParameterName (sourceColumn);
+ parameter.SourceColumn = sourceColumn;
+ //parameter.Size = (int) schemaRow ["ColumnSize"];
+ _dbCommand.Parameters.Add (parameter);
+ return parameter;
}
- private int CreateParameter (DbCommand _dbCommand, DataRow schemaRow)
+ private DbParameter CreateParameter (DbCommand _dbCommand, int paramIndex, DataRow schemaRow)
{
+ string sourceColumn = (string) schemaRow ["BaseColumnName"];
DbParameter parameter = _dbCommand.CreateParameter ();
- parameter.ParameterName = String.Format ("@{0}",
- schemaRow ["BaseColumnName"]);
- parameter.SourceColumn = (string) schemaRow ["BaseColumnName"];
- parameter.Size = (int) schemaRow ["ColumnSize"];
- return _dbCommand.Parameters.Add (parameter);
+ parameter.ParameterName = GetParameterName (paramIndex);
+ parameter.SourceColumn = sourceColumn;
+ //parameter.Size = (int) schemaRow ["ColumnSize"];
+ _dbCommand.Parameters.Add (parameter);
+ return parameter;
}
-
+
[DefaultValue (CatalogLocation.Start)]
public virtual CatalogLocation CatalogLocation {
get { return _catalogLocation; }
- set { _catalogLocation = value; }
+ set {
+ CheckEnumValue (typeof (CatalogLocation),
+ (int) value);
+ _catalogLocation = value;
+ }
}
[DefaultValue (".")]
public virtual string CatalogSeparator {
- get { return _catalogSeperator; }
- set { if (value != null) _catalogSeperator = value; }
+ get {
+ if (_catalogSeparator == null || _catalogSeparator.Length == 0)
+ return SEPARATOR_DEFAULT;
+ return _catalogSeparator;
+ }
+ set { _catalogSeparator = value; }
}
[DefaultValue (ConflictOption.CompareAllSearchableValues)]
public virtual ConflictOption ConflictOption {
get { return _conflictOption; }
- set { _conflictOption = value; }
+ set {
+ CheckEnumValue (typeof (ConflictOption),
+ (int) value);
+ _conflictOption = value;
+ }
}
[DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
[Browsable (false)]
public DbDataAdapter DataAdapter {
get { return _dbDataAdapter; }
- set { if (value != null) _dbDataAdapter = value; }
+ set { if (value != null)
+ SetRowUpdatingHandler (value);
+ _dbDataAdapter = value;
+ }
}
[DefaultValue ("")]
public virtual string QuotePrefix {
- get { return _quotePrefix; }
- set { if (value != null) _quotePrefix = value; }
+ get {
+ if (_quotePrefix == null)
+ return string.Empty;
+ return _quotePrefix;
+ }
+ set {
+ if (IsCommandGenerated)
+ throw new InvalidOperationException (
+ "QuotePrefix cannot be set after " +
+ "an Insert, Update or Delete command " +
+ "has been generated.");
+ _quotePrefix = value;
+ }
}
[DefaultValue ("")]
public virtual string QuoteSuffix {
- get { return _quoteSuffix; }
- set { if (value != null) _quoteSuffix = value; }
+ get {
+ if (_quoteSuffix == null)
+ return string.Empty;
+ return _quoteSuffix;
+ }
+ set {
+ if (IsCommandGenerated)
+ throw new InvalidOperationException (
+ "QuoteSuffix cannot be set after " +
+ "an Insert, Update or Delete command " +
+ "has been generated.");
+ _quoteSuffix = value;
+ }
}
[DefaultValue (".")]
public virtual string SchemaSeparator {
- get { return _schemaSeperator; }
- set { if (value != null) _schemaSeperator = value; }
+ get {
+ if (_schemaSeparator == null || _schemaSeparator.Length == 0)
+ return SEPARATOR_DEFAULT;
+ return _schemaSeparator;
+ }
+ set { _schemaSeparator = value; }
}
[DefaultValue (false)]
public bool SetAllValues {
get { return _setAllValues; }
set { _setAllValues = value; }
- }
+ }
private DbCommand SourceCommand {
get {
return null;
}
}
- #endregion // Properties
-
- #region Methods
protected abstract void ApplyParameterInfo (DbParameter parameter,
DataRow row,
public DbCommand GetDeleteCommand ()
{
- BuildCache (true);
- if (_deleteCommand == null)
- return CreateDeleteCommand (false);
- return _deleteCommand;
+ return GetDeleteCommand (false);
}
public DbCommand GetDeleteCommand (bool option)
{
BuildCache (true);
- if (_deleteCommand == null)
+ if (_deleteCommand == null || option)
return CreateDeleteCommand (option);
return _deleteCommand;
}
public DbCommand GetInsertCommand ()
{
- BuildCache (true);
- if (_insertCommand == null)
- return CreateInsertCommand (false);
- return _insertCommand;
+ return GetInsertCommand (false, null);
}
public DbCommand GetInsertCommand (bool option)
+ {
+ return GetInsertCommand (option, null);
+ }
+
+ internal DbCommand GetInsertCommand (bool option, DataRow row)
{
BuildCache (true);
- if (_insertCommand == null)
- return CreateInsertCommand (option);
+ if (_insertCommand == null || option)
+ return CreateInsertCommand (option, row);
return _insertCommand;
}
public DbCommand GetUpdateCommand ()
{
- BuildCache (true);
- if (_updateCommand == null)
- return CreateUpdateCommand (false);
- return _updateCommand;
+ return GetUpdateCommand (false);
}
public DbCommand GetUpdateCommand (bool option)
{
BuildCache (true);
- if (_updateCommand == null)
+ if (_updateCommand == null || option)
return CreateUpdateCommand (option);
return _updateCommand;
}
- [MonoTODO]
protected virtual DbCommand InitializeCommand (DbCommand command)
{
- throw new NotImplementedException ();
+ if (_dbCommand == null) {
+ _dbCommand = SourceCommand;
+ } else {
+ _dbCommand.CommandTimeout = 30;
+ _dbCommand.Transaction = null;
+ _dbCommand.CommandType = CommandType.Text;
+ _dbCommand.UpdatedRowSource = UpdateRowSource.None;
+ }
+ return _dbCommand;
+
}
public virtual string QuoteIdentifier (string unquotedIdentifier)
{
- if (unquotedIdentifier == null) {
- throw new ArgumentNullException("Unquoted identifier parameter cannot be null");
- }
- return String.Format ("{0}{1}{2}", this.QuotePrefix, unquotedIdentifier, this.QuoteSuffix);
+ throw new NotSupportedException ();
}
public virtual string UnquoteIdentifier (string quotedIdentifier)
{
_tableName = String.Empty;
_dbSchemaTable = null;
- CreateNewCommand (ref _deleteCommand);
- CreateNewCommand (ref _updateCommand);
- CreateNewCommand (ref _insertCommand);
+ _deleteCommand = null;
+ _updateCommand = null;
+ _insertCommand = null;
}
protected void RowUpdatingHandler (RowUpdatingEventArgs args)
try {
switch (args.StatementType) {
case StatementType.Insert:
- args.Command = GetInsertCommand ();
+ args.Command = GetInsertCommand (false, args.Row);
break;
case StatementType.Update:
args.Command = GetUpdateCommand ();
return rdr.GetSchemaTable ();
}
- #endregion // Methods
+ static void CheckEnumValue (Type type, int value)
+ {
+ if (Enum.IsDefined (type, value))
+ return;
+
+ string typename = type.Name;
+ string msg = string.Format (CultureInfo.CurrentCulture,
+ "Value {0} is not valid for {1}.", value,
+ typename);
+ throw new ArgumentOutOfRangeException (typename, msg);
+ }
}
}