Overcoming Keyword Conflicts When Using CommandBuilders

[ Team LiB ] Recipe 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.
Nội dung trích xuất từ tài liệu:
Overcoming Keyword Conflicts When Using CommandBuilders[ Team LiB ]Recipe 4.14 Overcoming Keyword Conflicts When Using CommandBuildersProblemYour data includes table and column names that conflict with SQL keywords. You canovercome this with brackets or quotes in SELECT statements that you write, but theCommandBuilder creates illegal update statements. You need to know how to use theCommandBuilder with your data.SolutionUse the QuotePrefix and QuoteSuffix properties of the CommandBuilder object todelimit 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 constantsusing 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( ));}DiscussionThe QuotePrefix and QuoteSuffix properties of the CommandBuilder object specify thebeginning and ending characters used to delimit database server object names, such astables and columns in the updating commands generated by the CommandBuilder. This isnecessary 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 validMicrosoft 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 useQuotePrefix and QuoteSuffix to delimit the SQL Server object names when required byT-SQL.The QuotePrefix and QuoteSuffix values have no effect on the CommandText ofSelectCommand. These delimiter ...