mcs/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.Data;
  30 using System.Data.Common;
  31 using System.ComponentModel;
  32
  33 namespace Npgsql
  34 {
  35
  36     public sealed class NpgsqlCommandBuilder : Component
  37     {
  38
  39         bool disposed = false;
  40
  41
  42         private NpgsqlDataAdapter data_adapter;
  43         private NpgsqlCommand insert_command;
  44         private NpgsqlCommand update_command;
  45         private NpgsqlCommand delete_command;
  46
  47         private string table_name = String.Empty;
  48
  49         public NpgsqlCommandBuilder ()
  50         {}
  51
  52         public NpgsqlCommandBuilder (NpgsqlDataAdapter adapter)
  53         {
  54             DataAdapter = adapter;
  55             adapter.RowUpdating += new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
  56         }
  57
  58         public NpgsqlDataAdapter DataAdapter {
  59             get
  60             {
  61                 return data_adapter;
  62             }
  63             set
  64             {
  65                 if (data_adapter != null)
  66                 {
  67                     throw new InvalidOperationException ("DataAdapter is already set");
  68                 }
  69                 data_adapter = value;
  70                 string select_text = data_adapter.SelectCommand.CommandText;
  71                 string[] words = select_text.Split(new char [] {' '});
  72                 bool from_found = false;
  73                 for (int i = 0; i < words.Length; i++)
  74                 {
  75                     if (from_found && (words[i] != String.Empty))
  76                     {
  77                         table_name = words[i];
  78                         break;
  79                     }
  80                     if (words[i].ToLower() == "from")
  81                     {
  82                         from_found = true;
  83                     }
  84                 }
  85             }
  86         }
  87
  88         private void OnRowUpdating(Object sender, NpgsqlRowUpdatingEventArgs value) {
  89             switch (value.StatementType)
  90             {
  91                 case StatementType.Insert:
  92                     value.Command = GetInsertCommand(value.Row);
  93                     break;
  94                 case StatementType.Update:
  95                     value.Command = GetUpdateCommand(value.Row);
  96                     break;
  97                 case StatementType.Delete:
  98                     value.Command = GetDeleteCommand(value.Row);
  99                     break;
 100             }
 101
 102             DataColumnMappingCollection columnMappings = value.TableMapping.ColumnMappings;
 103             foreach (IDataParameter parameter in value.Command.Parameters)
 104             {
 105
 106                 string dsColumnName = parameter.SourceColumn;
 107                 if (columnMappings.Contains(parameter.SourceColumn))
 108                 {
 109                     DataColumnMapping mapping = columnMappings[parameter.SourceColumn];
 110                     if (mapping != null)
 111                     {
 112                         dsColumnName = mapping.DataSetColumn;
 113                     }
 114                 }
 115
 116                 DataRowVersion rowVersion = DataRowVersion.Default;
 117                 if (value.StatementType == StatementType.Update)
 118                     rowVersion = parameter.SourceVersion;
 119                 if (value.StatementType == StatementType.Delete)
 120                     rowVersion = DataRowVersion.Original;
 121                 parameter.Value = value.Row [dsColumnName, rowVersion];
 122             }
 123             value.Row.AcceptChanges ();
 124         }
 125
 126         public string QuotePrefix {
 127             get
 128             {
 129                 return "";
 130             }
 131             set
 132             { }
 133         }
 134
 135         public string QuoteSuffix {
 136             get
 137             {
 138                 return "";
 139             }
 140             set
 141             { }
 142         }
 143
 144         public static void DeriveParameters (NpgsqlCommand command)
 145         {}
 146
 147         public NpgsqlCommand GetInsertCommand (DataRow row)
 148         {
 149             if (insert_command == null)
 150             {
 151                 string fields = "";
 152                 string values = "";
 153                 for (int i = 0; i < row.Table.Columns.Count; i++)
 154                 {
 155                     DataColumn column = row.Table.Columns[i];
 156                     if (i != 0)
 157                     {
 158                         fields += ", ";
 159                         values += ", ";
 160                     }
 161                     fields += column.ColumnName;
 162                     values += ":param_" + column.ColumnName;
 163                 }
 164                 if (table_name == String.Empty)
 165                 {
 166                     table_name = row.Table.TableName;
 167                 }
 168                 NpgsqlCommand cmdaux = new NpgsqlCommand("insert into " + table_name + " (" + fields + ") values (" + values + ")", data_adapter.SelectCommand.Connection);
 169                 foreach (DataColumn column in row.Table.Columns)
 170                 {
 171                     NpgsqlParameter aux = new NpgsqlParameter("param_" + column.ColumnName, row[column]);
 172                     aux.Direction = ParameterDirection.Input;
 173                     aux.SourceColumn = column.ColumnName;
 174                     cmdaux.Parameters.Add(aux);
 175                 }
 176                 insert_command = cmdaux;
 177             }
 178             return insert_command;
 179         }
 180
 181         public NpgsqlCommand GetUpdateCommand (DataRow row)
 182         {
 183             if (update_command == null)
 184             {
 185                 string sets = "";
 186                 string wheres = "";
 187                 for (int i = 0; i < row.Table.Columns.Count; i++)
 188                 {
 189                     if (i != 0)
 190                     {
 191                         sets += ", ";
 192                         wheres += " and ";
 193                     }
 194                     DataColumn column = row.Table.Columns[i];
 195                     sets += String.Format("{0} = :s_param_{0}", column.ColumnName);
 196                     wheres += String.Format("(({0} is null) or ({0} = :w_param_{0}))", column.ColumnName);
 197                 }
 198                 if (table_name == String.Empty)
 199                 {
 200                     table_name = row.Table.TableName;
 201                 }
 202                 NpgsqlCommand cmdaux = new NpgsqlCommand("update " + table_name + " set " + sets + " where ( " + wheres + " )", data_adapter.SelectCommand.Connection);
 203                 foreach (DataColumn column in row.Table.Columns)
 204                 {
 205                     NpgsqlParameter aux = new NpgsqlParameter("s_param_" + column.ColumnName, row[column]);
 206                     aux.Direction = ParameterDirection.Input;
 207                     aux.SourceColumn = column.ColumnName;
 208                     aux.SourceVersion = DataRowVersion.Current;
 209                     cmdaux.Parameters.Add(aux);
 210                 }
 211                 foreach (DataColumn column in row.Table.Columns)
 212                 {
 213                     NpgsqlParameter aux = new NpgsqlParameter("w_param_" + column.ColumnName, row[column]);
 214                     aux.Direction = ParameterDirection.Input;
 215                     aux.SourceColumn = column.ColumnName;
 216                     aux.SourceVersion = DataRowVersion.Original;
 217                     cmdaux.Parameters.Add(aux);
 218                 }
 219                 update_command = cmdaux;
 220
 221             }
 222             return update_command;
 223         }
 224
 225         public NpgsqlCommand GetDeleteCommand (DataRow row)
 226         {
 227             if (delete_command == null)
 228             {
 229                 string wheres = "";
 230                 for (int i = 0; i < row.Table.Columns.Count; i++)
 231                 {
 232                     DataColumn column = row.Table.Columns[i];
 233                     if (i != 0)
 234                     {
 235                         wheres += " and ";
 236                     }
 237                     wheres += String.Format("(({0} is null) or ({0} = :param_{0}))", column.ColumnName);
 238                 }
 239                 if (table_name == String.Empty)
 240                 {
 241                     table_name = row.Table.TableName;
 242                 }
 243                 NpgsqlCommand cmdaux = new NpgsqlCommand("delete from " + table_name + " where ( " + wheres + " )", data_adapter.SelectCommand.Connection);
 244                 foreach (DataColumn column in row.Table.Columns)
 245                 {
 246                     NpgsqlParameter aux = new NpgsqlParameter("param_" + column.ColumnName, row[column,DataRowVersion.Original]);
 247                     aux.Direction = ParameterDirection.Input;
 248                     aux.SourceColumn = column.ColumnName;
 249                     aux.SourceVersion = DataRowVersion.Original;
 250                     cmdaux.Parameters.Add(aux);
 251                 }
 252                 delete_command = cmdaux;
 253             }
 254             return delete_command;
 255         }
 256
 257         public void RefreshSchema ()
 258         {
 259             insert_command = null;
 260             update_command = null;
 261             delete_command = null;
 262         }
 263
 264         protected override void Dispose (bool disposing)
 265         {
 266             if (!disposed)
 267             {
 268                 if (disposing)
 269                 {
 270                     if (insert_command != null)
 271                     {
 272                         insert_command.Dispose();
 273                     }
 274                     if (update_command != null)
 275                     {
 276                         update_command.Dispose();
 277                     }
 278                     if (delete_command != null)
 279                     {
 280                         delete_command.Dispose();
 281                     }
 282                 }
 283             }
 284         }
 285
 286         /*~NpgsqlCommandBuilder ()
 287         {
 288             Dispose(false);
 289         }*/
 290
 291     }
 292
 293 }