class/Npgsql/Npgsql/NpgsqlCommandBuilder.cs

   1 // NpgsqlCommandBuilder.cs
   2 //
   3 // Author:
   4 //   Pedro Martínez Juliá (yoros@wanadoo.es)
   5 //
   6 // Copyright (C) 2003 Pedro Martínez Juliá
   7 //
   8 // Permission is hereby granted, free of charge, to any person obtaining
   9 // a copy of this software and associated documentation files (the
  10 // "Software"), to deal in the Software without restriction, including
  11 // without limitation the rights to use, copy, modify, merge, publish,
  12 // distribute, sublicense, and/or sell copies of the Software, and to
  13 // permit persons to whom the Software is furnished to do so, subject to
  14 // the following conditions:
  15 //
  16 // The above copyright notice and this permission notice shall be
  17 // included in all copies or substantial portions of the Software.
  18 //
  19 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  20 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  21 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  22 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  23 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  24 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  25 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  26
  27
  28 using System;
  29 using System.Resources;
  30 using System.Data;
  31 using System.Data.Common;
  32 using System.ComponentModel;
  33 using NpgsqlTypes;
  34
  35 namespace Npgsql
  36 {
  37
  38     ///<summary>
  39     /// This class is responsible to create database commands for automatic insert, update and delete operations.
  40     ///</summary>
  41     public sealed class NpgsqlCommandBuilder : Component
  42     {
  43
  44         // Logging related values
  45         private static readonly String CLASSNAME = "NpgsqlCommandBuilder";
  46         private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommandBuilder));
  47
  48         bool disposed = false;
  49
  50
  51         private NpgsqlDataAdapter data_adapter;
  52         private NpgsqlCommand insert_command;
  53         private NpgsqlCommand update_command;
  54         private NpgsqlCommand delete_command;
  55
  56         private string quotePrefix = "\"";
  57         private string quoteSuffix = "\"";
  58                 private DataTable select_schema;
  59
  60         public NpgsqlCommandBuilder ()
  61         {}
  62
  63         public NpgsqlCommandBuilder (NpgsqlDataAdapter adapter)
  64         {
  65             DataAdapter = adapter;
  66         }
  67
  68         public NpgsqlDataAdapter DataAdapter {
  69             get
  70             {
  71                 return data_adapter;
  72             }
  73             set
  74             {
  75                 if (data_adapter != null)
  76                 {
  77                     throw new InvalidOperationException ("DataAdapter is already set");
  78                 }
  79                 data_adapter = value;
  80                 data_adapter.RowUpdating += new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
  81             }
  82         }
  83
  84         private void OnRowUpdating(Object sender, NpgsqlRowUpdatingEventArgs value) {
  85             switch (value.StatementType)
  86             {
  87                 case StatementType.Insert:
  88                     value.Command = GetInsertCommand(value.Row, false);
  89                     break;
  90                 case StatementType.Update:
  91                     value.Command = GetUpdateCommand(value.Row, false);
  92                     break;
  93                 case StatementType.Delete:
  94                     value.Command = GetDeleteCommand(value.Row, false);
  95                     break;
  96             }
  97
  98             DataColumnMappingCollection columnMappings = value.TableMapping.ColumnMappings;
  99             foreach (IDataParameter parameter in value.Command.Parameters)
 100             {
 101
 102                 string dsColumnName = parameter.SourceColumn;
 103                 if (columnMappings.Contains(parameter.SourceColumn))
 104                 {
 105                     DataColumnMapping mapping = columnMappings[parameter.SourceColumn];
 106                     if (mapping != null)
 107                     {
 108                         dsColumnName = mapping.DataSetColumn;
 109                     }
 110                 }
 111
 112                 DataRowVersion rowVersion = DataRowVersion.Default;
 113                 if (value.StatementType == StatementType.Update)
 114                     rowVersion = parameter.SourceVersion;
 115                 if (value.StatementType == StatementType.Delete)
 116                     rowVersion = DataRowVersion.Original;
 117                 parameter.Value = value.Row [dsColumnName, rowVersion];
 118             }
 119         }
 120
 121         public string QuotePrefix {
 122             get
 123             {
 124                 return quotePrefix;
 125             }
 126             set
 127             {
 128                                 quotePrefix = value;
 129                         }
 130         }
 131
 132         public string QuoteSuffix {
 133             get
 134             {
 135                 return quoteSuffix;
 136             }
 137             set
 138             {
 139                                 quoteSuffix = value;
 140                         }
 141         }
 142
 143         ///<summary>
 144         ///
 145         /// This method is reponsible to derive the command parameter list with values obtained from function definition.
 146         /// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown.
 147         /// Parameters name will be parameter1, parameter2, ...
 148         /// For while, only parameter name and NpgsqlDbType are obtained.
 149         ///</summary>
 150         /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
 151         public static void DeriveParameters (NpgsqlCommand command)
 152         {
 153
 154             // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
 155             // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
 156             String query = null;
 157             string procedureName = null;
 158             string schemaName = null;
 159             string[] fullName = command.CommandText.Split('.');
 160             if (fullName.Length > 1 && fullName[0].Length > 0)
 161             {
 162                 query = "select proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
 163                 schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
 164                 procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
 165             }
 166             else
 167             {
 168                 query = "select proargtypes from pg_proc where proname = :proname";
 169                 procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
 170             }
 171
 172             NpgsqlCommand c = new NpgsqlCommand(query, command.Connection);
 173             c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
 174
 175
 176             c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
 177
 178             if (fullName.Length > 1 && schemaName.Length > 0)
 179             {
 180                 NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
 181                 prm.Value = schemaName.Replace("\"", "").Trim();
 182             }
 183
 184             String types = (String) c.ExecuteScalar();
 185
 186             if (types == null)
 187                 throw new InvalidOperationException (String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
 188
 189             command.Parameters.Clear();
 190             Int32 i = 1;
 191
 192             foreach(String s in types.Split())
 193             {
 194                 if (!c.Connector.OidToNameMapping.ContainsOID(Int32.Parse(s)))
 195                 {
 196                     command.Parameters.Clear();
 197                     throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", s));
 198                 }
 199                 command.Parameters.Add(new NpgsqlParameter("parameter" + i++, c.Connector.OidToNameMapping[Int32.Parse(s)].NpgsqlDbType));
 200             }
 201
 202         }
 203
 204         private string GetQuotedName(string str)
 205         {
 206             string result = str;
 207             if ((QuotePrefix != string.Empty) && !str.StartsWith(QuotePrefix))
 208             {
 209                 result = QuotePrefix + result;
 210             }
 211             if ((QuoteSuffix != string.Empty) && !str.EndsWith(QuoteSuffix))
 212             {
 213                 result = result + QuoteSuffix;
 214             }
 215             return result;
 216         }
 217
 218
 219         public NpgsqlCommand GetInsertCommand (DataRow row)
 220         {
 221             return GetInsertCommand(row, true);
 222         }
 223
 224         private NpgsqlCommand GetInsertCommand(DataRow row, bool setParameterValues)
 225         {
 226             if (insert_command == null)
 227             {
 228                 string fields = "";
 229                 string values = "";
 230                                 bool first = true;
 231                                 if (select_schema == null)
 232                                 {
 233                                         BuildSchema();
 234                                 }
 235                 string schema_name = string.Empty;
 236                                 string table_name = string.Empty;
 237                 string quotedName;
 238                 NpgsqlCommand cmdaux = new NpgsqlCommand();
 239                                 foreach(DataRow schemaRow in select_schema.Rows)
 240                                 {
 241                                         if (!(bool)schemaRow["IsAutoIncrement"])
 242                                         {
 243                                                 if (!first)
 244                                                 {
 245                                                         fields += ", ";
 246                                                         values += ", ";
 247                                                 }
 248                                                 else
 249                                                 {
 250                             schema_name = (string)schemaRow["BaseSchemaName"];
 251                                                         table_name = (string)schemaRow["BaseTableName"];
 252                                                         if (table_name == null || table_name.Length == 0)
 253                                                         {
 254                                                                 table_name = row.Table.TableName;
 255                                                         }
 256                                                 }
 257                         quotedName = GetQuotedName((string)schemaRow["BaseColumnName"]);
 258                         DataColumn column = row.Table.Columns[(string)schemaRow["ColumnName"]];
 259
 260                         fields += quotedName;
 261                         values += ":param_" + column.ColumnName;
 262                                                 first = false;
 263
 264                         NpgsqlParameter aux = new NpgsqlParameter("param_" + column.ColumnName, NpgsqlTypesHelper.GetNativeTypeInfo(column.DataType));
 265                         aux.Direction = ParameterDirection.Input;
 266                         aux.SourceColumn = column.ColumnName;
 267                         cmdaux.Parameters.Add(aux);
 268                                         }
 269                                 }
 270                 cmdaux.CommandText = "insert into " + QualifiedTableName(schema_name, table_name) + " (" + fields + ") values (" + values + ")";
 271                 cmdaux.Connection = data_adapter.SelectCommand.Connection;
 272                 insert_command = cmdaux;
 273             }
 274             if (setParameterValues)
 275             {
 276                 SetParameterValuesFromRow(insert_command, row);
 277             }
 278             return insert_command;
 279         }
 280
 281         public NpgsqlCommand GetUpdateCommand (DataRow row)
 282         {
 283             return GetUpdateCommand(row, true);
 284         }
 285
 286         private NpgsqlCommand GetUpdateCommand(DataRow row, bool setParameterValues)
 287         {
 288             if (update_command == null)
 289             {
 290                 string sets = "";
 291                                 string wheres = "";
 292                                 bool first = true;
 293                                 if (select_schema == null)
 294                                 {
 295                                         BuildSchema();
 296                                 }
 297                 string schema_name = string.Empty;
 298                 string table_name = string.Empty;
 299                 string quotedName;
 300                 NpgsqlCommand cmdaux = new NpgsqlCommand();
 301                                 foreach(DataRow schemaRow in select_schema.Rows)
 302                                 {
 303                                         if (!first)
 304                                         {
 305                                                 sets += ", ";
 306                                                 wheres += " and ";
 307                                         }
 308                                         else
 309                                         {
 310                         schema_name = (string)schemaRow["BaseSchemaName"];
 311                                                 table_name = (string)schemaRow["BaseTableName"];
 312                                                 if (table_name == null || table_name.Length == 0)
 313                                                 {
 314                                                         table_name = row.Table.TableName;
 315                                                 }
 316                                         }
 317                     quotedName = GetQuotedName((string)schemaRow["BaseColumnName"]);
 318                     DataColumn column = row.Table.Columns[(string)schemaRow["ColumnName"]];
 319                     sets += String.Format("{0} = :s_param_{1}", quotedName, column.ColumnName);
 320                     wheres += String.Format("(({0} is null) or ({0} = :w_param_{1}))", quotedName, column.ColumnName);
 321                                         first = false;
 322
 323                     NpgsqlNativeTypeInfo typeInfo = NpgsqlTypesHelper.GetNativeTypeInfo(column.DataType);
 324                     NpgsqlParameter aux_set = new NpgsqlParameter("s_param_" + column.ColumnName, typeInfo);
 325                     aux_set.Direction = ParameterDirection.Input;
 326                     aux_set.SourceColumn = column.ColumnName;
 327                     aux_set.SourceVersion = DataRowVersion.Current;
 328                     cmdaux.Parameters.Add(aux_set);
 329
 330                     NpgsqlParameter aux_where = new NpgsqlParameter("w_param_" + column.ColumnName, typeInfo);
 331                     aux_where.Direction = ParameterDirection.Input;
 332                     aux_where.SourceColumn = column.ColumnName;
 333                     aux_where.SourceVersion = DataRowVersion.Original;
 334                     cmdaux.Parameters.Add(aux_where);
 335                                 }
 336                 cmdaux.CommandText = "update " + QualifiedTableName(schema_name, table_name) + " set " + sets + " where ( " + wheres + " )";
 337                 cmdaux.Connection = data_adapter.SelectCommand.Connection;
 338                 update_command = cmdaux;
 339
 340             }
 341             if (setParameterValues)
 342             {
 343                 SetParameterValuesFromRow(update_command, row);
 344             }
 345             return update_command;
 346         }
 347
 348         public NpgsqlCommand GetDeleteCommand (DataRow row)
 349         {
 350             return GetDeleteCommand(row, true);
 351         }
 352
 353         private NpgsqlCommand GetDeleteCommand(DataRow row, bool setParameterValues)
 354         {
 355             if (delete_command == null)
 356             {
 357                                 string wheres = "";
 358                                 bool first = true;
 359                                 if (select_schema == null)
 360                                 {
 361                                         BuildSchema();
 362                                 }
 363                 string schema_name = string.Empty;
 364                 string table_name = string.Empty;
 365                 string quotedName;
 366                 NpgsqlCommand cmdaux = new NpgsqlCommand();
 367                                 foreach(DataRow schemaRow in select_schema.Rows)
 368                                 {
 369                                         if (!first)
 370                                         {
 371                                                 wheres += " and ";
 372                                         }
 373                                         else
 374                                         {
 375                         schema_name = (string)schemaRow["BaseSchemaName"];
 376                                                 table_name = (string)schemaRow["BaseTableName"];
 377                                                 if (table_name == null || table_name.Length == 0)
 378                                                 {
 379                                                         table_name = row.Table.TableName;
 380                                                 }
 381                     }
 382
 383                     quotedName = GetQuotedName((string)schemaRow["BaseColumnName"]);
 384                     DataColumn column = row.Table.Columns[(string)schemaRow["ColumnName"]];
 385
 386                                         wheres += String.Format("(({0} is null) or ({0} = :param_{1}))", quotedName , column.ColumnName);
 387                     first = false;
 388
 389                     NpgsqlParameter aux = new NpgsqlParameter("param_" + column.ColumnName, NpgsqlTypesHelper.GetNativeTypeInfo(column.DataType));
 390                     aux.Direction = ParameterDirection.Input;
 391                     aux.SourceColumn = column.ColumnName;
 392                     aux.SourceVersion = DataRowVersion.Original;
 393                     cmdaux.Parameters.Add(aux);
 394                                 }
 395                 cmdaux.CommandText = "delete from " + QualifiedTableName(schema_name, table_name) + " where ( " + wheres + " )";
 396                 cmdaux.Connection = data_adapter.SelectCommand.Connection;
 397                 delete_command = cmdaux;
 398             }
 399             if (setParameterValues)
 400             {
 401                 SetParameterValuesFromRow(delete_command, row);
 402             }
 403             return delete_command;
 404         }
 405
 406         public void RefreshSchema ()
 407         {
 408             insert_command = null;
 409             update_command = null;
 410             delete_command = null;
 411                         select_schema = null;
 412         }
 413
 414         protected override void Dispose (bool disposing)
 415         {
 416             if (!disposed)
 417             {
 418                 if (disposing)
 419                 {
 420                     if (insert_command != null)
 421                     {
 422                         insert_command.Dispose();
 423                     }
 424                     if (update_command != null)
 425                     {
 426                         update_command.Dispose();
 427                     }
 428                     if (delete_command != null)
 429                     {
 430                         delete_command.Dispose();
 431                     }
 432
 433                     data_adapter.RowUpdating -= new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
 434                 }
 435             }
 436             base.Dispose(disposing);
 437         }
 438
 439                 private void BuildSchema()
 440                 {
 441                         if (select_schema == null)
 442                         {
 443                                 bool openedConnection = false;
 444                                 try
 445                                 {
 446                                         if ((data_adapter.SelectCommand.Connection.State & ConnectionState.Open) != ConnectionState.Open)
 447                                         {
 448                                                 data_adapter.SelectCommand.Connection.Open();
 449                                                 openedConnection = true;
 450                                         }
 451                                         using (NpgsqlDataReader reader = data_adapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly|CommandBehavior.KeyInfo))
 452                                         {
 453                                                 select_schema = reader.GetSchemaTable();
 454                                         }
 455                                 }
 456                                 finally
 457                                 {
 458                                         if (openedConnection)
 459                                         {
 460                                                 data_adapter.SelectCommand.Connection.Close();
 461                                         }
 462                                 }
 463                         }
 464                 }
 465
 466         /*~NpgsqlCommandBuilder ()
 467         {
 468             Dispose(false);
 469         }*/
 470
 471         private string QualifiedTableName(string schema, string tableName)
 472         {
 473             if (schema == null || schema.Length == 0)
 474             {
 475                 return GetQuotedName(tableName);
 476             }
 477             else
 478             {
 479                 return GetQuotedName(schema) + "." + GetQuotedName(tableName);
 480             }
 481         }
 482
 483         private static void SetParameterValuesFromRow(NpgsqlCommand command, DataRow row)
 484         {
 485             foreach (NpgsqlParameter parameter in command.Parameters)
 486             {
 487                 parameter.Value = row[parameter.SourceColumn, parameter.SourceVersion];
 488             }
 489         }
 490     }
 491
 492 }