mcs/class/IBM.Data.DB2/IBM.Data.DB2/DB2CommandBuilder.cs

   1
   2 //
   3 // Permission is hereby granted, free of charge, to any person obtaining
   4 // a copy of this software and associated documentation files (the
   5 // "Software"), to deal in the Software without restriction, including
   6 // without limitation the rights to use, copy, modify, merge, publish,
   7 // distribute, sublicense, and/or sell copies of the Software, and to
   8 // permit persons to whom the Software is furnished to do so, subject to
   9 // the following conditions:
  10 //
  11 // The above copyright notice and this permission notice shall be
  12 // included in all copies or substantial portions of the Software.
  13 //
  14 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  15 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  16 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  17 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  18 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  19 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  20 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  21 //
  22 using System;
  23 using System.Data;
  24 using System.ComponentModel;
  25
  26 namespace IBM.Data.DB2
  27 {
  28
  29         public sealed class DB2CommandBuilder : Component
  30         {
  31
  32                 bool disposed = false;
  33
  34                 private DB2DataAdapter dataAdapter;
  35                 private DB2Command insertCommand;
  36                 private DB2Command updateCommand;
  37                 private DB2Command deleteCommand;
  38
  39                 private string tableName = String.Empty;
  40
  41                 public DB2CommandBuilder ()
  42                 {}
  43
  44                 public DB2CommandBuilder (DB2DataAdapter adapter)
  45                 {
  46                         DataAdapter = adapter;
  47                 }
  48
  49                 public DB2DataAdapter DataAdapter
  50                 {
  51                         get
  52                         {
  53                                 return dataAdapter;
  54                         }
  55                         set
  56                         {
  57                                 if (dataAdapter != null)
  58                                 {
  59                                         throw new Exception ("DataAdapter is already set");
  60                                 }
  61                                 dataAdapter = value;
  62                                 string select_text = dataAdapter.SelectCommand.CommandText;
  63                                 string[] words = select_text.Split(new char [] {' '});
  64                                 bool from_found = false;
  65                                 for (int i = 0; i < words.Length; i++)
  66                                 {
  67                                         if (from_found && (words[i] != String.Empty))
  68                                         {
  69                                                 tableName = words[i];
  70                                                 break;
  71                                         }
  72                                         if (words[i].ToLower() == "from")
  73                                         {
  74                                                 from_found = true;
  75                                         }
  76                                 }
  77                         }
  78                 }
  79
  80                 public string QuotePrefix
  81                 {
  82                         get
  83                         {
  84                                 return "";
  85                         }
  86                         set
  87                         { }
  88                 }
  89
  90                 public string QuoteSuffix
  91                 {
  92                         get
  93                         {
  94                                 return "";
  95                         }
  96                         set
  97                         { }
  98                 }
  99
 100                 public static void DeriveParameters (DB2Command command)
 101                 {}
 102
 103                 public DB2Command GetInsertCommand ()
 104                 {
 105                         DataTable dt = GetSchema();
 106                         if (insertCommand == null)
 107                         {
 108                                 string fields = "";
 109                                 string values = "";
 110                                 for (int i = 0; i < dt.Rows.Count; i++)
 111                                 {
 112                                         //DataColumn column = dt.Columns[i];
 113                                         DataRow dr = dt.Rows[i];
 114                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 115
 116                                         if (fields.Length != 0 && !((bool)dr["IsAutoIncrement"]))
 117                                         {
 118                                                 fields += ", ";
 119                                                 values += ", ";
 120                                         }
 121
 122                                         if(!((bool)dr["IsAutoIncrement"]))
 123                                         {
 124                                                 fields += column.ColumnName;
 125                                                 //values += ":v_" + column.ColumnName;
 126                                                 values += "?";
 127                                         }
 128                                 }
 129                                 if (tableName == String.Empty)
 130                                 {
 131                                         tableName = dt.TableName;
 132                                 }
 133                                 DB2Command cmdaux = new DB2Command("insert into " + tableName + " (" + fields + ") values (" + values + ")", dataAdapter.SelectCommand.Connection);
 134                                 for (int i = 0;i < dt.Rows.Count;i++)
 135                                 {
 136                                         //DataColumn column = dt.Columns[i];
 137                                         DataRow dr = dt.Rows[i];
 138                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 139                                         if (!((bool)dr["IsAutoIncrement"]))
 140                                         {
 141                                                 DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName,  column.DataType);
 142                                                 aux.Direction = ParameterDirection.Input;
 143                                                 aux.SourceColumn = column.ColumnName;
 144                                                 cmdaux.Parameters.Add(aux);
 145                                         }
 146                                 }
 147                                 insertCommand = cmdaux;
 148                         }
 149                         return insertCommand;
 150                 }
 151
 152                 public DB2Command GetUpdateCommand ()
 153                 {
 154                         DataTable dt = GetSchema();
 155                         if (updateCommand == null)
 156                         {
 157                                 string sets = "";
 158                                 string wheres = "";
 159                                 for (int i = 0; i < dt.Rows.Count; i++)
 160                                 {
 161                                         if (sets.Length != 0 && !((bool)dt.Rows[i]["IsAutoIncrement"]))
 162                                         {
 163                                                 sets += ", ";
 164                                         }
 165                                         if (i != 0)
 166                                         {
 167                                                 wheres += " and ";
 168                                         }
 169                                         DataRow dr = dt.Rows[i];
 170                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 171                                         if(!((bool)dr["IsAutoIncrement"])){sets += String.Format("{0} = ? ", column.ColumnName);}
 172                                         wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
 173                                 }
 174                                 if (tableName == String.Empty)
 175                                 {
 176                                         tableName = (string)dt.Rows[0]["BaseTableName"];
 177                                 }
 178                                 DB2Command cmdaux = new DB2Command("update " + tableName + " set " + sets + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
 179                                 for (int i = 0; i < dt.Rows.Count; i++)
 180                                 {
 181                                         DataRow dr = dt.Rows[i];
 182                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 183                                         if (!((bool)dr["IsAutoIncrement"]))
 184                                         {
 185                                                 DB2Parameter aux = new DB2Parameter("s_" + column.ColumnName, column.DataType);
 186                                                 aux.Direction = ParameterDirection.Input;
 187                                                 aux.SourceColumn = column.ColumnName;
 188                                                 aux.SourceVersion = DataRowVersion.Current;
 189                                                 cmdaux.Parameters.Add(aux);
 190                                         }
 191                                 }
 192                                 for (int i = 0; i < dt.Rows.Count; i++)
 193                                 {
 194                                         DataRow dr = dt.Rows[i];
 195                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 196                                         DB2Parameter aux = new DB2Parameter("w_" + column.ColumnName, column.DataType);
 197                                         aux.Direction = ParameterDirection.Input;
 198                                         aux.SourceColumn = column.ColumnName;
 199                                         aux.SourceVersion = DataRowVersion.Original;
 200                                         cmdaux.Parameters.Add(aux);
 201                                 }
 202                                 updateCommand = cmdaux;
 203
 204                         }
 205                         return updateCommand;
 206                 }
 207
 208                 public DB2Command GetDeleteCommand ()
 209                 {
 210                         DataTable dt = GetSchema();
 211                         if (deleteCommand == null)
 212                         {
 213                                 string wheres = "";
 214                                 for (int i = 0; i < dt.Rows.Count; i++)
 215                                 {
 216                                         //DataColumn column = row.Table.Columns[i];
 217                                         DataRow dr = dt.Rows[i];
 218                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 219                                         if (i != 0)
 220                                         {
 221                                                 wheres += " and ";
 222                                         }
 223                                         //wheres += String.Format("(({0} is null) or ({0} = v_{0}))", column.ColumnName);
 224                                         wheres += String.Format("(({0} is null) or ({0} = ?))", column.ColumnName);
 225                                 }
 226                                 if (tableName == String.Empty)
 227                                 {
 228                                         tableName = (string)dt.Rows[0]["BaseTableName"];
 229                                 }
 230                                 DB2Command cmdaux = new DB2Command("delete from " + tableName + " where ( " + wheres + " )", dataAdapter.SelectCommand.Connection);
 231                                 for (int i = 0; i < dt.Rows.Count; i++)
 232                                 {
 233                                         DataRow dr = dt.Rows[i];
 234                                         DataColumn column = new DataColumn((string)dr["ColumnName"], DB2TypeConverter.GetManagedType((int)dr["ProviderType"]));
 235
 236                                         DB2Parameter aux = new DB2Parameter("v_" + column.ColumnName, column.DataType);
 237                                         aux.Direction = ParameterDirection.Input;
 238                                         aux.SourceColumn = column.ColumnName;
 239                                         aux.SourceVersion = DataRowVersion.Original;
 240                                         cmdaux.Parameters.Add(aux);
 241                                 }
 242                                 deleteCommand = cmdaux;
 243                         }
 244                         return deleteCommand;
 245                 }
 246
 247                 public void RefreshSchema ()
 248                 {
 249                         insertCommand = null;
 250                         updateCommand = null;
 251                         deleteCommand = null;
 252                 }
 253
 254                 private DataTable GetSchema()
 255                 {
 256                         dataAdapter.SelectCommand.Connection.Open();
 257                         DB2Command cmd = new DB2Command(dataAdapter.SelectCommand.CommandText, dataAdapter.SelectCommand.Connection);
 258                         DB2DataReader fake = cmd.ExecuteReader(CommandBehavior.KeyInfo);
 259
 260                         DataTable dt = fake.GetSchemaTable();
 261                         fake.Close();
 262                         dataAdapter.SelectCommand.Connection.Close();
 263
 264                         return dt;
 265                 }
 266
 267                 protected override void Dispose (bool disposing)
 268                 {
 269                         if (!disposed)
 270                         {
 271                                 if (disposing)
 272                                 {
 273                                         if (insertCommand != null)
 274                                         {
 275                                                 insertCommand.Dispose();
 276                                         }
 277                                         if (updateCommand != null)
 278                                         {
 279                                                 updateCommand.Dispose();
 280                                         }
 281                                         if (deleteCommand != null)
 282                                         {
 283                                                 deleteCommand.Dispose();
 284                                         }
 285                                 }
 286                         }
 287                 }
 288
 289         }
 290
 291 }