mcs/class/referencesource/System.Data/System/Data/OleDb/OleDbCommandBuilder.cs

   1 //------------------------------------------------------------------------------
   2 // <copyright file="OleDbCommandBuilder.cs" company="Microsoft">
   3 //      Copyright (c) Microsoft Corporation.  All rights reserved.
   4 // </copyright>
   5 // <owner current="true" primary="true">Microsoft</owner>
   6 // <owner current="true" primary="false">Microsoft</owner>
   7 //------------------------------------------------------------------------------
   8
   9 namespace System.Data.OleDb {
  10
  11     using System;
  12     using System.ComponentModel;
  13     using System.Data;
  14     using System.Data.Common;
  15     using System.Data.ProviderBase;
  16     using System.Diagnostics;
  17     using System.Globalization;
  18     using System.Text;
  19
  20     public sealed class OleDbCommandBuilder : DbCommandBuilder {
  21
  22         public OleDbCommandBuilder() : base() {
  23             GC.SuppressFinalize(this);
  24         }
  25
  26         public OleDbCommandBuilder(OleDbDataAdapter adapter) : this() {
  27             DataAdapter = adapter;
  28         }
  29
  30         [
  31         DefaultValue(null),
  32         ResCategoryAttribute(Res.DataCategory_Update),
  33         ResDescriptionAttribute(Res.OleDbCommandBuilder_DataAdapter), // MDAC 60524
  34         ]
  35         new public OleDbDataAdapter DataAdapter {
  36             get {
  37                 return (base.DataAdapter as OleDbDataAdapter);
  38             }
  39             set {
  40                 base.DataAdapter = value;
  41             }
  42         }
  43
  44         private void OleDbRowUpdatingHandler(object sender, OleDbRowUpdatingEventArgs ruevent) {
  45             RowUpdatingHandler(ruevent);
  46         }
  47
  48         new public OleDbCommand GetInsertCommand() {
  49             return (OleDbCommand) base.GetInsertCommand();
  50         }
  51         new public OleDbCommand GetInsertCommand(bool useColumnsForParameterNames) {
  52             return (OleDbCommand) base.GetInsertCommand(useColumnsForParameterNames);
  53         }
  54
  55         new public OleDbCommand GetUpdateCommand() {
  56             return (OleDbCommand) base.GetUpdateCommand();
  57         }
  58         new public OleDbCommand GetUpdateCommand(bool useColumnsForParameterNames) {
  59             return (OleDbCommand) base.GetUpdateCommand(useColumnsForParameterNames);
  60         }
  61
  62         new public OleDbCommand GetDeleteCommand() {
  63             return (OleDbCommand) base.GetDeleteCommand();
  64         }
  65         new public OleDbCommand GetDeleteCommand(bool useColumnsForParameterNames) {
  66             return (OleDbCommand) base.GetDeleteCommand(useColumnsForParameterNames);
  67         }
  68
  69         override protected string GetParameterName(int parameterOrdinal) {
  70             return "p" + parameterOrdinal.ToString(System.Globalization.CultureInfo.InvariantCulture);
  71         }
  72         override protected string GetParameterName(string parameterName) {
  73             return parameterName;
  74         }
  75
  76         override protected string GetParameterPlaceholder(int parameterOrdinal) {
  77             return "?";
  78         }
  79
  80         override protected void ApplyParameterInfo(DbParameter parameter, DataRow datarow, StatementType statementType, bool whereClause) {
  81             OleDbParameter p = (OleDbParameter) parameter;
  82             object valueType = datarow[SchemaTableColumn.ProviderType];
  83             p.OleDbType = (OleDbType) valueType;
  84
  85             object bvalue = datarow[SchemaTableColumn.NumericPrecision];
  86             if (DBNull.Value != bvalue) {
  87                 byte bval = (byte)(short)bvalue;
  88                 p.PrecisionInternal = ((0xff != bval) ? bval : (byte)0);
  89             }
  90
  91             bvalue = datarow[SchemaTableColumn.NumericScale];
  92             if (DBNull.Value != bvalue) {
  93                 byte bval = (byte)(short)bvalue;
  94                 p.ScaleInternal = ((0xff != bval) ? bval : (byte)0);
  95             }
  96         }
  97
  98         static public void DeriveParameters(OleDbCommand command) { // MDAC 65927
  99             OleDbConnection.ExecutePermission.Demand();
 100
 101             if (null == command) {
 102                 throw ADP.ArgumentNull("command");
 103             }
 104             switch (command.CommandType) {
 105             case System.Data.CommandType.Text:
 106                 throw ADP.DeriveParametersNotSupported(command);
 107             case System.Data.CommandType.StoredProcedure:
 108                 break;
 109             case System.Data.CommandType.TableDirect:
 110                 // CommandType.TableDirect - do nothing, parameters are not supported
 111                 throw ADP.DeriveParametersNotSupported(command);
 112             default:
 113                 throw ADP.InvalidCommandType(command.CommandType);
 114             }
 115             if (ADP.IsEmpty(command.CommandText)) {
 116                 throw ADP.CommandTextRequired(ADP.DeriveParameters);
 117             }
 118             OleDbConnection connection = command.Connection;
 119             if (null == connection) {
 120                 throw ADP.ConnectionRequired(ADP.DeriveParameters);
 121             }
 122             ConnectionState state = connection.State;
 123             if (ConnectionState.Open != state) {
 124                 throw ADP.OpenConnectionRequired(ADP.DeriveParameters, state);
 125             }
 126             OleDbParameter[] list = DeriveParametersFromStoredProcedure(connection, command);
 127
 128             OleDbParameterCollection parameters = command.Parameters;
 129             parameters.Clear();
 130
 131             for(int i = 0; i < list.Length; ++i) {
 132                 parameters.Add(list[i]);
 133             }
 134         }
 135
 136         // known difference: when getting the parameters for a sproc, the
 137         //   return value gets marked as a return value but for a sql stmt
 138         //   the return value gets marked as an output parameter.
 139         static private OleDbParameter[] DeriveParametersFromStoredProcedure(OleDbConnection connection, OleDbCommand command) {
 140             OleDbParameter[] plist = new OleDbParameter[0];
 141
 142             if (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedure_Parameters)) {
 143                 string quotePrefix, quoteSuffix;
 144                 connection.GetLiteralQuotes(ADP.DeriveParameters, out quotePrefix, out quoteSuffix);
 145
 146                 Object[] parsed = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quotePrefix, quoteSuffix, '.', 4, true, Res.OLEDB_OLEDBCommandText, false); // MDAC 70930
 147                 if (null == parsed[3]) {
 148                     throw ADP.NoStoredProcedureExists(command.CommandText);
 149                 }
 150
 151                 Object[] restrictions = new object[4];
 152                 object value;
 153
 154                 // Parse returns an enforced 4 part array
 155                 // 0) Server - ignored but removal would be a run-time breaking change from V1.0
 156                 // 1) Catalog
 157                 // 2) Schema
 158                 // 3) ProcedureName
 159
 160                 // Restrictions array which is passed to OleDb API expects:
 161                 // 0) Catalog
 162                 // 1) Schema
 163                 // 2) ProcedureName
 164                 // 3) ParameterName (leave null)
 165
 166                 // Copy from Parse format to OleDb API format
 167                 Array.Copy(parsed, 1, restrictions, 0, 3);
 168
 169                 //if (cmdConnection.IsServer_msdaora) {
 170                 //    restrictions[1] = Convert.ToString(cmdConnection.UserId).ToUpper();
 171                 //}
 172                 DataTable table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedure_Parameters, restrictions);
 173
 174                 if (null != table) {
 175                     DataColumnCollection columns = table.Columns;
 176
 177                     DataColumn parameterName = null;
 178                     DataColumn parameterDirection = null;
 179                     DataColumn dataType = null;
 180                     DataColumn maxLen = null;
 181                     DataColumn numericPrecision = null;
 182                     DataColumn numericScale = null;
 183                     DataColumn backendtype = null;
 184
 185                     int index = columns.IndexOf(ODB.PARAMETER_NAME);
 186                     if (-1 != index) parameterName = columns[index];
 187
 188                     index = columns.IndexOf(ODB.PARAMETER_TYPE);
 189                     if (-1 != index) parameterDirection = columns[index];
 190
 191                     index = columns.IndexOf(ODB.DATA_TYPE);
 192                     if (-1 != index) dataType = columns[index];
 193
 194                     index = columns.IndexOf(ODB.CHARACTER_MAXIMUM_LENGTH);
 195                     if (-1 != index) maxLen = columns[index];
 196
 197                     index = columns.IndexOf(ODB.NUMERIC_PRECISION);
 198                     if (-1 != index) numericPrecision = columns[index];
 199
 200                     index = columns.IndexOf(ODB.NUMERIC_SCALE);
 201                     if (-1 != index) numericScale = columns[index];
 202
 203                     index = columns.IndexOf(ODB.TYPE_NAME); // MDAC 72315
 204                     if (-1 != index) backendtype = columns[index];
 205
 206                     DataRow[] dataRows = table.Select(null, ODB.ORDINAL_POSITION_ASC, DataViewRowState.CurrentRows); // MDAC 70928
 207                     plist = new OleDbParameter[dataRows.Length];
 208                     for(index = 0; index < dataRows.Length; ++index) {
 209                         DataRow dataRow = dataRows[index];
 210
 211                         OleDbParameter parameter = new OleDbParameter();
 212
 213                         if ((null != parameterName) && !dataRow.IsNull(parameterName, DataRowVersion.Default)) {
 214                             // $
 215                             parameter.ParameterName = Convert.ToString(dataRow[parameterName, DataRowVersion.Default], CultureInfo.InvariantCulture).TrimStart(new char[] { '@', ' ', ':'});
 216                         }
 217                         if ((null != parameterDirection) && !dataRow.IsNull(parameterDirection, DataRowVersion.Default)) {
 218                             short direction = Convert.ToInt16(dataRow[parameterDirection, DataRowVersion.Default], CultureInfo.InvariantCulture);
 219                             parameter.Direction = ConvertToParameterDirection(direction);
 220                         }
 221                         if ((null != dataType) && !dataRow.IsNull(dataType, DataRowVersion.Default)) {
 222                             // need to ping FromDBType, otherwise WChar->WChar when the user really wants VarWChar
 223                             short wType = Convert.ToInt16(dataRow[dataType, DataRowVersion.Default], CultureInfo.InvariantCulture);
 224                             parameter.OleDbType = NativeDBType.FromDBType(wType, false, false).enumOleDbType;
 225                         }
 226                         if ((null != maxLen) && !dataRow.IsNull(maxLen, DataRowVersion.Default)) {
 227                             parameter.Size = Convert.ToInt32(dataRow[maxLen, DataRowVersion.Default], CultureInfo.InvariantCulture);
 228                         }
 229                         switch(parameter.OleDbType) {
 230                             case OleDbType.Decimal:
 231                             case OleDbType.Numeric:
 232                             case OleDbType.VarNumeric:
 233                                 if ((null != numericPrecision) && !dataRow.IsNull(numericPrecision, DataRowVersion.Default)) {
 234                                     // @devnote: unguarded cast from Int16 to Byte
 235                                     parameter.PrecisionInternal = (Byte) Convert.ToInt16(dataRow[numericPrecision], CultureInfo.InvariantCulture);
 236                                 }
 237                                 if ((null != numericScale) && !dataRow.IsNull(numericScale, DataRowVersion.Default)) {
 238                                     // @devnote: unguarded cast from Int16 to Byte
 239                                     parameter.ScaleInternal = (Byte) Convert.ToInt16(dataRow[numericScale], CultureInfo.InvariantCulture);
 240                                 }
 241                                 break;
 242                             case OleDbType.VarBinary: // MDAC 72315
 243                             case OleDbType.VarChar:
 244                             case OleDbType.VarWChar:
 245                                 value = dataRow[backendtype, DataRowVersion.Default];
 246                                 if (value is string) {
 247                                     string backendtypename = ((string) value).ToLower(CultureInfo.InvariantCulture);
 248                                     switch(backendtypename) {
 249                                     case "binary":
 250                                         parameter.OleDbType = OleDbType.Binary;
 251                                         break;
 252                                     //case "varbinary":
 253                                     //    parameter.OleDbType = OleDbType.VarBinary;
 254                                     //    break;
 255                                     case "image":
 256                                         parameter.OleDbType = OleDbType.LongVarBinary;
 257                                         break;
 258                                     case "char":
 259                                         parameter.OleDbType = OleDbType.Char;
 260                                         break;
 261                                     //case "varchar":
 262                                     //case "varchar2":
 263                                     //    parameter.OleDbType = OleDbType.VarChar;
 264                                     //    break;
 265                                     case "text":
 266                                         parameter.OleDbType = OleDbType.LongVarChar;
 267                                         break;
 268                                     case "nchar":
 269                                         parameter.OleDbType = OleDbType.WChar;
 270                                         break;
 271                                     //case "nvarchar":
 272                                     //    parameter.OleDbType = OleDbType.VarWChar;
 273                                     case "ntext":
 274                                         parameter.OleDbType = OleDbType.LongVarWChar;
 275                                         break;
 276                                     }
 277                                 }
 278                                 break;
 279                         }
 280                         //if (AdapterSwitches.OleDbSql.TraceVerbose) {
 281                         //    ADP.Trace_Parameter("StoredProcedure", parameter);
 282                         //}
 283                         plist[index] = parameter;
 284                     }
 285                 }
 286                 if ((0 == plist.Length) && connection.SupportSchemaRowset(OleDbSchemaGuid.Procedures)) {
 287                     restrictions = new Object[4] { null, null, command.CommandText, null};
 288                     table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedures, restrictions);
 289                     if (0 == table.Rows.Count) {
 290                         throw ADP.NoStoredProcedureExists(command.CommandText);
 291                     }
 292                 }
 293             }
 294             else if (connection.SupportSchemaRowset(OleDbSchemaGuid.Procedures)) {
 295                 Object[] restrictions = new Object[4] { null, null, command.CommandText, null};
 296                 DataTable table = connection.GetSchemaRowset(OleDbSchemaGuid.Procedures, restrictions);
 297                 if (0 == table.Rows.Count) {
 298                     throw ADP.NoStoredProcedureExists(command.CommandText);
 299                 }
 300                 // we don't ever expect a procedure with 0 parameters, they should have at least a return value
 301                 throw ODB.NoProviderSupportForSProcResetParameters(connection.Provider); // MDAC 71968
 302             }
 303             else {
 304                 throw ODB.NoProviderSupportForSProcResetParameters(connection.Provider); // MDAC 70918
 305             }
 306             return plist;
 307         }
 308
 309         static private ParameterDirection ConvertToParameterDirection(int value) {
 310             switch (value) {
 311                 case ODB.DBPARAMTYPE_INPUT:
 312                     return System.Data.ParameterDirection.Input;
 313                 case ODB.DBPARAMTYPE_INPUTOUTPUT:
 314                     return System.Data.ParameterDirection.InputOutput;
 315                 case ODB.DBPARAMTYPE_OUTPUT:
 316                     return System.Data.ParameterDirection.Output;
 317                 case ODB.DBPARAMTYPE_RETURNVALUE:
 318                     return System.Data.ParameterDirection.ReturnValue;
 319                 default:
 320                     return System.Data.ParameterDirection.Input;
 321             }
 322         }
 323
 324         public override string QuoteIdentifier(string unquotedIdentifier){
 325             return QuoteIdentifier(unquotedIdentifier, null /* use DataAdapter.SelectCommand.Connection if available */);
 326         }
 327         public string QuoteIdentifier( string unquotedIdentifier, OleDbConnection connection){
 328             ADP.CheckArgumentNull(unquotedIdentifier, "unquotedIdentifier");
 329
 330             // if the user has specificed a prefix use the user specified  prefix and suffix
 331             // otherwise get them from the provider
 332             string quotePrefix = QuotePrefix;
 333             string quoteSuffix = QuoteSuffix;
 334             if (ADP.IsEmpty(quotePrefix) == true) {
 335                 if (connection == null) {
 336                     // VSTFDEVDIV 479567: use the adapter's connection if QuoteIdentifier was called from
 337                     // DbCommandBuilder instance (which does not have an overload that gets connection object)
 338                     connection = base.GetConnection() as OleDbConnection;
 339                     if (connection == null) {
 340                         throw ADP.QuotePrefixNotSet(ADP.QuoteIdentifier);
 341                     }
 342                 }
 343                 connection.GetLiteralQuotes(ADP.QuoteIdentifier, out quotePrefix, out quoteSuffix);
 344                 // if the quote suffix is null assume that it is the same as the prefix (See OLEDB spec
 345                 // IDBInfo::GetLiteralInfo DBLITERAL_QUOTE_SUFFIX.)
 346                 if (quoteSuffix == null) {
 347                     quoteSuffix = quotePrefix;
 348                 }
 349             }
 350
 351             return ADP.BuildQuotedString(quotePrefix,quoteSuffix,unquotedIdentifier);
 352         }
 353
 354         override protected void SetRowUpdatingHandler(DbDataAdapter adapter) {
 355             Debug.Assert(adapter is OleDbDataAdapter, "!OleDbDataAdapter");
 356             if (adapter == base.DataAdapter) { // removal case
 357                 ((OleDbDataAdapter)adapter).RowUpdating -= OleDbRowUpdatingHandler;
 358             }
 359             else { // adding case
 360                 ((OleDbDataAdapter)adapter).RowUpdating += OleDbRowUpdatingHandler;
 361             }
 362         }
 363
 364         public override string UnquoteIdentifier( string quotedIdentifier){
 365             return UnquoteIdentifier(quotedIdentifier, null /* use DataAdapter.SelectCommand.Connection if available */);
 366         }
 367
 368         public string UnquoteIdentifier(string quotedIdentifier, OleDbConnection connection){
 369
 370             ADP.CheckArgumentNull(quotedIdentifier, "quotedIdentifier");
 371
 372
 373             // if the user has specificed a prefix use the user specified  prefix and suffix
 374             // otherwise get them from the provider
 375             string quotePrefix = QuotePrefix;
 376             string quoteSuffix = QuoteSuffix;
 377             if (ADP.IsEmpty(quotePrefix) == true) {
 378                 if (connection == null) {
 379                     // VSTFDEVDIV 479567: use the adapter's connection if UnquoteIdentifier was called from
 380                     // DbCommandBuilder instance (which does not have an overload that gets connection object)
 381                     connection = base.GetConnection() as OleDbConnection;
 382                     if (connection == null) {
 383                         throw ADP.QuotePrefixNotSet(ADP.UnquoteIdentifier);
 384                     }
 385                 }
 386                 connection.GetLiteralQuotes(ADP.UnquoteIdentifier, out quotePrefix, out quoteSuffix);
 387                 // if the quote suffix is null assume that it is the same as the prefix (See OLEDB spec
 388                 // IDBInfo::GetLiteralInfo DBLITERAL_QUOTE_SUFFIX.)
 389                 if (quoteSuffix == null) {
 390                     quoteSuffix = quotePrefix;
 391                 }
 392             }
 393
 394             String unquotedIdentifier;
 395             // ignoring the return value because it is acceptable for the quotedString to not be quoted in this
 396             // context.
 397             ADP.RemoveStringQuotes(quotePrefix, quoteSuffix, quotedIdentifier, out unquotedIdentifier);
 398             return unquotedIdentifier;
 399
 400         }
 401
 402     }
 403 }