1 // NpgsqlCommandBuilder.cs
4 // Pedro Martínez Juliá (yoros@wanadoo.es)
6 // Copyright (C) 2003 Pedro Martínez Juliá
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:
16 // The above copyright notice and this permission notice shall be
17 // included in all copies or substantial portions of the Software.
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.
29 using System.Resources;
31 using System.Data.Common;
32 using System.ComponentModel;
39 /// This class is responsible to create database commands for automatic insert, update and delete operations.
41 public sealed class NpgsqlCommandBuilder : Component
44 // Logging related values
45 private static readonly String CLASSNAME = "NpgsqlCommandBuilder";
46 private static ResourceManager resman = new ResourceManager(typeof(NpgsqlCommandBuilder));
48 bool disposed = false;
51 private NpgsqlDataAdapter data_adapter;
52 private NpgsqlCommand insert_command;
53 private NpgsqlCommand update_command;
54 private NpgsqlCommand delete_command;
56 private string quotePrefix = "\"";
57 private string quoteSuffix = "\"";
58 private DataTable select_schema;
60 public NpgsqlCommandBuilder ()
63 public NpgsqlCommandBuilder (NpgsqlDataAdapter adapter)
65 DataAdapter = adapter;
68 public NpgsqlDataAdapter DataAdapter {
75 if (data_adapter != null)
77 throw new InvalidOperationException ("DataAdapter is already set");
80 data_adapter.RowUpdating += new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
84 private void OnRowUpdating(Object sender, NpgsqlRowUpdatingEventArgs value) {
85 switch (value.StatementType)
87 case StatementType.Insert:
88 value.Command = GetInsertCommand(value.Row, false);
90 case StatementType.Update:
91 value.Command = GetUpdateCommand(value.Row, false);
93 case StatementType.Delete:
94 value.Command = GetDeleteCommand(value.Row, false);
98 DataColumnMappingCollection columnMappings = value.TableMapping.ColumnMappings;
99 foreach (IDataParameter parameter in value.Command.Parameters)
102 string dsColumnName = parameter.SourceColumn;
103 if (columnMappings.Contains(parameter.SourceColumn))
105 DataColumnMapping mapping = columnMappings[parameter.SourceColumn];
108 dsColumnName = mapping.DataSetColumn;
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];
121 public string QuotePrefix {
132 public string QuoteSuffix {
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.
150 /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
151 public static void DeriveParameters (NpgsqlCommand command)
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)
157 string procedureName = null;
158 string schemaName = null;
159 string[] fullName = command.CommandText.Split('.');
160 if (fullName.Length > 1 && fullName[0].Length > 0)
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();
168 query = "select proargtypes from pg_proc where proname = :proname";
169 procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
172 NpgsqlCommand c = new NpgsqlCommand(query, command.Connection);
173 c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
176 c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
178 if (fullName.Length > 1 && schemaName.Length > 0)
180 NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
181 prm.Value = schemaName.Replace("\"", "").Trim();
184 String types = (String) c.ExecuteScalar();
187 throw new InvalidOperationException (String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
189 command.Parameters.Clear();
192 foreach(String s in types.Split())
194 if (!c.Connector.OidToNameMapping.ContainsOID(Int32.Parse(s)))
196 command.Parameters.Clear();
197 throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", s));
199 command.Parameters.Add(new NpgsqlParameter("parameter" + i++, c.Connector.OidToNameMapping[Int32.Parse(s)].NpgsqlDbType));
204 private string GetQuotedName(string str)
207 if ((QuotePrefix != string.Empty) && !str.StartsWith(QuotePrefix))
209 result = QuotePrefix + result;
211 if ((QuoteSuffix != string.Empty) && !str.EndsWith(QuoteSuffix))
213 result = result + QuoteSuffix;
219 public NpgsqlCommand GetInsertCommand (DataRow row)
221 return GetInsertCommand(row, true);
224 private NpgsqlCommand GetInsertCommand(DataRow row, bool setParameterValues)
226 if (insert_command == null)
231 if (select_schema == null)
235 string schema_name = string.Empty;
236 string table_name = string.Empty;
238 NpgsqlCommand cmdaux = new NpgsqlCommand();
239 foreach(DataRow schemaRow in select_schema.Rows)
241 if (!(bool)schemaRow["IsAutoIncrement"])
250 schema_name = (string)schemaRow["BaseSchemaName"];
251 table_name = (string)schemaRow["BaseTableName"];
252 if (table_name == null || table_name.Length == 0)
254 table_name = row.Table.TableName;
257 quotedName = GetQuotedName((string)schemaRow["BaseColumnName"]);
258 DataColumn column = row.Table.Columns[(string)schemaRow["ColumnName"]];
260 fields += quotedName;
261 values += ":param_" + column.ColumnName;
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);
270 cmdaux.CommandText = "insert into " + QualifiedTableName(schema_name, table_name) + " (" + fields + ") values (" + values + ")";
271 cmdaux.Connection = data_adapter.SelectCommand.Connection;
272 insert_command = cmdaux;
274 if (setParameterValues)
276 SetParameterValuesFromRow(insert_command, row);
278 return insert_command;
281 public NpgsqlCommand GetUpdateCommand (DataRow row)
283 return GetUpdateCommand(row, true);
286 private NpgsqlCommand GetUpdateCommand(DataRow row, bool setParameterValues)
288 if (update_command == null)
293 if (select_schema == null)
297 string schema_name = string.Empty;
298 string table_name = string.Empty;
300 NpgsqlCommand cmdaux = new NpgsqlCommand();
301 foreach(DataRow schemaRow in select_schema.Rows)
310 schema_name = (string)schemaRow["BaseSchemaName"];
311 table_name = (string)schemaRow["BaseTableName"];
312 if (table_name == null || table_name.Length == 0)
314 table_name = row.Table.TableName;
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);
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);
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);
336 cmdaux.CommandText = "update " + QualifiedTableName(schema_name, table_name) + " set " + sets + " where ( " + wheres + " )";
337 cmdaux.Connection = data_adapter.SelectCommand.Connection;
338 update_command = cmdaux;
341 if (setParameterValues)
343 SetParameterValuesFromRow(update_command, row);
345 return update_command;
348 public NpgsqlCommand GetDeleteCommand (DataRow row)
350 return GetDeleteCommand(row, true);
353 private NpgsqlCommand GetDeleteCommand(DataRow row, bool setParameterValues)
355 if (delete_command == null)
359 if (select_schema == null)
363 string schema_name = string.Empty;
364 string table_name = string.Empty;
366 NpgsqlCommand cmdaux = new NpgsqlCommand();
367 foreach(DataRow schemaRow in select_schema.Rows)
375 schema_name = (string)schemaRow["BaseSchemaName"];
376 table_name = (string)schemaRow["BaseTableName"];
377 if (table_name == null || table_name.Length == 0)
379 table_name = row.Table.TableName;
383 quotedName = GetQuotedName((string)schemaRow["BaseColumnName"]);
384 DataColumn column = row.Table.Columns[(string)schemaRow["ColumnName"]];
386 wheres += String.Format("(({0} is null) or ({0} = :param_{1}))", quotedName , column.ColumnName);
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);
395 cmdaux.CommandText = "delete from " + QualifiedTableName(schema_name, table_name) + " where ( " + wheres + " )";
396 cmdaux.Connection = data_adapter.SelectCommand.Connection;
397 delete_command = cmdaux;
399 if (setParameterValues)
401 SetParameterValuesFromRow(delete_command, row);
403 return delete_command;
406 public void RefreshSchema ()
408 insert_command = null;
409 update_command = null;
410 delete_command = null;
411 select_schema = null;
414 protected override void Dispose (bool disposing)
420 if (insert_command != null)
422 insert_command.Dispose();
424 if (update_command != null)
426 update_command.Dispose();
428 if (delete_command != null)
430 delete_command.Dispose();
433 data_adapter.RowUpdating -= new NpgsqlRowUpdatingEventHandler(OnRowUpdating);
436 base.Dispose(disposing);
439 private void BuildSchema()
441 if (select_schema == null)
443 bool openedConnection = false;
446 if ((data_adapter.SelectCommand.Connection.State & ConnectionState.Open) != ConnectionState.Open)
448 data_adapter.SelectCommand.Connection.Open();
449 openedConnection = true;
451 using (NpgsqlDataReader reader = data_adapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly|CommandBehavior.KeyInfo))
453 select_schema = reader.GetSchemaTable();
458 if (openedConnection)
460 data_adapter.SelectCommand.Connection.Close();
466 /*~NpgsqlCommandBuilder ()
471 private string QualifiedTableName(string schema, string tableName)
473 if (schema == null || schema.Length == 0)
475 return GetQuotedName(tableName);
479 return GetQuotedName(schema) + "." + GetQuotedName(tableName);
483 private static void SetParameterValuesFromRow(NpgsqlCommand command, DataRow row)
485 foreach (NpgsqlParameter parameter in command.Parameters)
487 parameter.Value = row[parameter.SourceColumn, parameter.SourceVersion];