ADO.NET 3.5 Cookbook, 2nd Edition

5.17. Overcoming Keyword Conflicts When Using a CommandBuilder

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 solution displays the default database object name delimiters; the delete, insert, and update commands using the default delimiters; and the delete, insert, and update commands using explicitly specified delimiters through the QuotePrefix and QuoteSuffix properties for both the OLE DB and SQL Server .NET data providers.

The C# code in Program.cs in the project ResolveCommandBuilderKeywordConflict is shown in Example 5-34.

Example 5-34. File: Program.cs for ResolveCommandBuilderKeywordConflict solution

using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace ResolveCommandBuilderKeywordConflict { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" + "Integrated Security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT ContactID, FirstName, ...