ADO.NET Cookbook

4.14. Overcoming Keyword Conflicts When Using CommandBuilders

Problem

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.

Solution

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 and QuoteSuffix 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 the OleDbConnection 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 ...