Your data includes table and column names that
conflict
with SQL keywords. You can overcome this with brackets or quotes in
SELECT
statements that you write, but the
CommandBuilder
creates illegal update statements.
You need to know how to use the CommandBuilder
with your data.
Use the QuotePrefix
and
QuoteSuffix
properties of the
CommandBuilder
object to delimit database server
object names containing spaces or other illegal characters.
The sample code contains two event handlers:
- Preview
Button.Click
Displays the delete, insert, and update commands using the delimiters specified by the user for the
QuotePrefix
andQuoteSuffix
properties, and either the OLE DB or SQL Server data provider as specified by the user.- Retrieve OLE DB
Button.Click
Uses the
GetOleDbSchemaTable( )
method of theOleDbConnection
object to retrieve the default prefix and suffix delimiters for the data source.
The C# code is shown in Example 4-33.
Example 4-33. File: CommandBuilderKeywordConflictForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; // . . . private void previewButton_Click(object sender, System.EventArgs e) { String sqlText = "SELECT OrderID, ProductID, Quantity, " + "UnitPrice, Discount FROM [Order Details]"; if (oleDbRadioButton.Checked) { // Build the DataAdapter and the CommandBuilder. OleDbDataAdapter da = new OleDbDataAdapter(sqlText, ConfigurationSettings.AppSettings["OleDb_ConnectString"]); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); // Set the prefix and suffix. cb.QuotePrefix = quotePrefixTextBox.Text; cb.QuoteSuffix = quoteSuffixTextBox.Text; // Display CommandBuilder commands with the prefix and suffix. resultTextBox.Text = "DeleteCommand: " + cb.GetDeleteCommand( ).CommandText + Environment.NewLine + Environment.NewLine + "InsertCommand: " + cb.GetInsertCommand( ).CommandText + Environment.NewLine + Environment.NewLine + "UpdateCommand: " + cb.GetUpdateCommand( ).CommandText; } else { // Build the DataAdapter and the CommandBuilder. SqlDataAdapter da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommandBuilder cb = new SqlCommandBuilder(da); // Set the prefix and suffix. cb.QuotePrefix = quotePrefixTextBox.Text; cb.QuoteSuffix = quoteSuffixTextBox.Text; // Display CommandBuilder commands with the prefix and suffix. resultTextBox.Text = "DeleteCommand: " + cb.GetDeleteCommand( ).CommandText + Environment.NewLine + Environment.NewLine + "InsertCommand: " + cb.GetInsertCommand( ).CommandText + Environment.NewLine + Environment.NewLine + "UpdateCommand: " + cb.GetUpdateCommand( ).CommandText; } } private void retrieveOleDbButton_Click(object sender, System.EventArgs e) { // Retrieve the quote prefix and suffix for the server. OleDbConnection conn = new OleDbConnection( ConfigurationSettings.AppSettings["OleDb_ConnectString"]); conn.Open( ); DataTable tableSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.DbInfoLiterals, new object[] {}); conn.Close( ); // Set the primary key to enable find on LiteralName column. tableSchema.PrimaryKey = new DataColumn[] {tableSchema.Columns["LiteralName"]}; // Get the prefix and suffix from the OleDbLiteral enumeration. DataRow row; row = tableSchema.Rows.Find("Quote_Prefix"); quotePrefixTextBox.Text = ((row == null) ? "" : row["LiteralValue"].ToString( )); row = tableSchema.Rows.Find("Quote_Suffix"); quoteSuffixTextBox.Text = ((row == null) ? "" : row["LiteralValue"].ToString( )); }
The QuotePrefix
and QuoteSuffix
properties of the CommandBuilder
object specify
the beginning and ending characters used to delimit database server
object names, such as tables and columns in the updating commands
generated by the CommandBuilder
. This is necessary
when the object names contain special characters or reserved tokens;
otherwise, the commands will fail when executed.
For example, in SQL Server 2000 and later, database object names can
contain any valid Microsoft Windows NT/2000/XP character including
spaces and punctuation marks. T-SQL is more restrictive with
identifiers that can be used without delimiters. You can use
QuotePrefix
and QuoteSuffix
to
delimit the SQL Server object names when required by T-SQL.
The QuotePrefix
and QuoteSuffix
values have no effect on the CommandText
of
SelectCommand
. These delimiters must be specified
as part of the SelectCommand
that the
CommandBuilder
is based on.
The default values for QuotePrefix
and
QuoteSuffix
are empty strings.
The example demonstrates using QuotePrefix
and
QuoteSuffix
with both the OLE DB and SQL Server
CommandBuilder
objects. As you can see, they
function nearly identically.
Note
The CommandBuilder
makes it easy to update the
data source with changes made to the DataSet
.
Update logic is created automatically so no understanding is required
of how to code the actual delete, insert, and update SQL statements.
The CommandBuilder
drawbacks include slower
performance because of the time that it takes to request metadata and
construct the updating logic, updates that are limited to simple
single-table scenarios, and a lack of support for stored procedures.
Because of these drawbacks, the CommandBuilder
is
seldom used in enterprise application
development.
The example also demonstrates retrieving the default quote prefix and
suffix from the database server. The GetOleDbSchemaTable( )
method of the OleDbConnection
object
returns schema information from a data source based on a GUID
argument indicating one of the OleDbSchemaGuid
values. The value DbInfoLiterals
returns a list of
provider-specific literals used in text commands. The literals are
returned as a table of rows. Within this table, there is a column
named LiteralName
. The rows containing the default
values for the quote prefix and suffix are identified by a
LiteralName
value of
Quote_Prefix
and Quote_Suffix
,
respectively. The actual values are stored in the
LiteralValue
column.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.