2 // Mono.Data.Sqlite.SQLiteCommandBuilder.cs
5 // Robert Simpson (robert@blackcastlesoft.com)
7 // Adapted and modified for the Mono Project by
8 // Marek Habersack (grendello@gmail.com)
11 // Copyright (C) 2006 Novell, Inc (http://www.novell.com)
12 // Copyright (C) 2007 Marek Habersack
14 // Permission is hereby granted, free of charge, to any person obtaining
15 // a copy of this software and associated documentation files (the
16 // "Software"), to deal in the Software without restriction, including
17 // without limitation the rights to use, copy, modify, merge, publish,
18 // distribute, sublicense, and/or sell copies of the Software, and to
19 // permit persons to whom the Software is furnished to do so, subject to
20 // the following conditions:
22 // The above copyright notice and this permission notice shall be
23 // included in all copies or substantial portions of the Software.
25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
34 /********************************************************
35 * ADO.NET 2.0 Data Provider for Sqlite Version 3.X
36 * Written by Robert Simpson (robert@blackcastlesoft.com)
38 * Released to the public domain, use at your own risk!
39 ********************************************************/
41 namespace Mono.Data.Sqlite
45 using System.Data.Common;
46 using System.Globalization;
47 using System.ComponentModel;
50 /// Sqlite implementation of DbCommandBuilder.
52 public sealed class SqliteCommandBuilder : DbCommandBuilder
54 private EventHandler<RowUpdatingEventArgs> _handler;
57 /// Default constructor
59 public SqliteCommandBuilder() : this(null)
64 /// Initializes the command builder and associates it with the specified data adapter.
66 /// <param name="adp"></param>
67 public SqliteCommandBuilder(SqliteDataAdapter adp)
75 /// Minimal amount of parameter processing. Primarily sets the DbType for the parameter equal to the provider type in the schema
77 /// <param name="parameter">The parameter to use in applying custom behaviors to a row</param>
78 /// <param name="row">The row to apply the parameter to</param>
79 /// <param name="statementType">The type of statement</param>
80 /// <param name="whereClause">Whether the application of the parameter is part of a WHERE clause</param>
81 protected override void ApplyParameterInfo(DbParameter parameter, DataRow row, StatementType statementType, bool whereClause)
83 SqliteParameter param = (SqliteParameter)parameter;
84 param.DbType = (DbType)row[SchemaTableColumn.ProviderType];
88 /// Returns a valid named parameter
90 /// <param name="parameterName">The name of the parameter</param>
91 /// <returns>Error</returns>
92 protected override string GetParameterName(string parameterName)
94 return String.Format(CultureInfo.InvariantCulture, "@{0}", parameterName);
98 /// Returns a named parameter for the given ordinal
100 /// <param name="parameterOrdinal">The i of the parameter</param>
101 /// <returns>Error</returns>
102 protected override string GetParameterName(int parameterOrdinal)
104 return String.Format(CultureInfo.InvariantCulture, "@param{0}", parameterOrdinal);
108 /// Returns a placeholder character for the specified parameter i.
110 /// <param name="parameterOrdinal">The index of the parameter to provide a placeholder for</param>
111 /// <returns>Returns a named parameter</returns>
112 protected override string GetParameterPlaceholder(int parameterOrdinal)
114 return GetParameterName(parameterOrdinal);
118 /// Sets the handler for receiving row updating events. Used by the DbCommandBuilder to autogenerate SQL
119 /// statements that may not have previously been generated.
121 /// <param name="adapter">A data adapter to receive events on.</param>
122 protected override void SetRowUpdatingHandler(DbDataAdapter adapter)
124 SqliteDataAdapter adp = (SqliteDataAdapter)adapter;
126 _handler = new EventHandler<RowUpdatingEventArgs>(RowUpdatingEventHandler);
127 adp.RowUpdating += _handler;
130 private void RowUpdatingEventHandler(object sender, RowUpdatingEventArgs e)
132 base.RowUpdatingHandler(e);
136 /// Gets/sets the DataAdapter for this CommandBuilder
138 public new SqliteDataAdapter DataAdapter
140 get { return (SqliteDataAdapter)base.DataAdapter; }
141 set { base.DataAdapter = value; }
145 /// Returns the automatically-generated Sqlite command to delete rows from the database
147 /// <returns></returns>
148 public new SqliteCommand GetDeleteCommand()
150 return (SqliteCommand)base.GetDeleteCommand();
154 /// Returns the automatically-generated Sqlite command to delete rows from the database
156 /// <param name="useColumnsForParameterNames"></param>
157 /// <returns></returns>
158 public new SqliteCommand GetDeleteCommand(bool useColumnsForParameterNames)
160 return (SqliteCommand)base.GetDeleteCommand(useColumnsForParameterNames);
164 /// Returns the automatically-generated Sqlite command to update rows in the database
166 /// <returns></returns>
167 public new SqliteCommand GetUpdateCommand()
169 return (SqliteCommand)base.GetUpdateCommand();
173 /// Returns the automatically-generated Sqlite command to update rows in the database
175 /// <param name="useColumnsForParameterNames"></param>
176 /// <returns></returns>
177 public new SqliteCommand GetUpdateCommand(bool useColumnsForParameterNames)
179 return (SqliteCommand)base.GetUpdateCommand(useColumnsForParameterNames);
183 /// Returns the automatically-generated Sqlite command to insert rows into the database
185 /// <returns></returns>
186 public new SqliteCommand GetInsertCommand()
188 return (SqliteCommand)base.GetInsertCommand();
192 /// Returns the automatically-generated Sqlite command to insert rows into the database
194 /// <param name="useColumnsForParameterNames"></param>
195 /// <returns></returns>
196 public new SqliteCommand GetInsertCommand(bool useColumnsForParameterNames)
198 return (SqliteCommand)base.GetInsertCommand(useColumnsForParameterNames);
202 /// Overridden to hide its property from the designer
204 #if !PLATFORM_COMPACTFRAMEWORK
207 public override CatalogLocation CatalogLocation
211 return base.CatalogLocation;
215 base.CatalogLocation = value;
220 /// Overridden to hide its property from the designer
222 #if !PLATFORM_COMPACTFRAMEWORK
225 public override string CatalogSeparator
229 return base.CatalogSeparator;
233 base.CatalogSeparator = value;
238 /// Overridden to hide its property from the designer
240 #if !PLATFORM_COMPACTFRAMEWORK
244 public override string QuotePrefix
248 return base.QuotePrefix;
252 base.QuotePrefix = value;
257 /// Overridden to hide its property from the designer
259 #if !PLATFORM_COMPACTFRAMEWORK
262 public override string QuoteSuffix
266 return base.QuoteSuffix;
270 base.QuoteSuffix = value;
275 /// Places brackets around an identifier
277 /// <param name="unquotedIdentifier">The identifier to quote</param>
278 /// <returns>The bracketed identifier</returns>
279 public override string QuoteIdentifier(string unquotedIdentifier)
281 if (String.IsNullOrEmpty(QuotePrefix)
282 || String.IsNullOrEmpty(QuoteSuffix)
283 || String.IsNullOrEmpty(unquotedIdentifier))
284 return unquotedIdentifier;
286 return QuotePrefix + unquotedIdentifier.Replace(QuoteSuffix, QuoteSuffix + QuoteSuffix) + QuoteSuffix;
290 /// Removes brackets around an identifier
292 /// <param name="quotedIdentifier">The quoted (bracketed) identifier</param>
293 /// <returns>The undecorated identifier</returns>
294 public override string UnquoteIdentifier(string quotedIdentifier)
296 if (String.IsNullOrEmpty(QuotePrefix)
297 || String.IsNullOrEmpty(QuoteSuffix)
298 || String.IsNullOrEmpty(quotedIdentifier))
299 return quotedIdentifier;
301 if (quotedIdentifier.StartsWith(QuotePrefix, StringComparison.InvariantCultureIgnoreCase) == false
302 || quotedIdentifier.EndsWith(QuoteSuffix, StringComparison.InvariantCultureIgnoreCase) == false)
303 return quotedIdentifier;
305 return quotedIdentifier.Substring(QuotePrefix.Length, quotedIdentifier.Length - (QuotePrefix.Length + QuoteSuffix.Length)).Replace(QuoteSuffix + QuoteSuffix, QuoteSuffix);
309 /// Overridden to hide its property from the designer
311 #if !PLATFORM_COMPACTFRAMEWORK
314 public override string SchemaSeparator
318 return base.SchemaSeparator;
322 base.SchemaSeparator = value;
327 /// Override helper, which can help the base command builder choose the right keys for the given query
329 /// <param name="sourceCommand"></param>
330 /// <returns></returns>
331 protected override DataTable GetSchemaTable(DbCommand sourceCommand)
333 using (IDataReader reader = sourceCommand.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
335 DataTable schema = reader.GetSchemaTable();
337 // If the query contains a primary key, turn off the IsUnique property
338 // for all the non-key columns
339 if (HasSchemaPrimaryKey(schema))
340 ResetIsUniqueSchemaColumn(schema);
342 // if table has no primary key we use unique columns as a fall back
347 private bool HasSchemaPrimaryKey(DataTable schema)
349 DataColumn IsKeyColumn = schema.Columns[SchemaTableColumn.IsKey];
351 foreach (DataRow schemaRow in schema.Rows)
353 if ((bool)schemaRow[IsKeyColumn] == true)
360 private void ResetIsUniqueSchemaColumn(DataTable schema)
362 DataColumn IsUniqueColumn = schema.Columns[SchemaTableColumn.IsUnique];
363 DataColumn IsKeyColumn = schema.Columns[SchemaTableColumn.IsKey];
365 foreach (DataRow schemaRow in schema.Rows)
367 if ((bool)schemaRow[IsKeyColumn] == false)
368 schemaRow[IsUniqueColumn] = false;
371 schema.AcceptChanges();