Educate ADO.NET's CommandBuilder Class

Prevent SqlExceptions by using QuotePrefix and QuoteSuffix.

Hot Tip

LANGUAGES: C# | SQL

ASP.NET VERSIONS: 1.0 | 1.1

 

Educate ADO.NET's CommandBuilder Class

Prevent SqlExceptions by using QuotePrefix and QuoteSuffix.

 

By Jeff Prosise

 

Most developers don't realize it until they bump into the problem head-on, but ADO.NET's CommandBuilder class isn't smart enough on its own to figure out that table names with embedded spaces must be delimited with special characters such as "[" and "]." Consider this example, which uses a SqlDataAdapter and a SqlCommandBuilder to query and update a data source:

 

SqlDataAdapter adapter = new SqlDataAdapter (

    "select * from [account info]",

    "server=localhost;database=mybank;uid=sa;pwd="

);

// Query the data source

DataSet ds = new DataSet ();

adapter.Fill (ds, "Accounts");

  .

  .

  .

// Update the data source

SqlCommandBuilder builder = new SqlCommandBuilder (adapter);

adapter.Update (table);

 

The call to Fill works fine, but the call to Update throws a SqlException. Why? Say that between calls to Fill and Update, you add a record to the DataSet. Here's what the INSERT command that SqlCommandBuilder generated looks like:

 

INSERT INTO account info ...

 

This generates an exception because the table name includes a space character. The proper command looks like this for most databases:

 

INSERT INTO [account info] ...

 

The solution lies in CommandBuilder's QuotePrefix and QuoteSuffix properties. Here's a revised code snippet that initializes these properties with the proper delimiters before calling Update (changes are highlighted in bold):

 

SqlDataAdapter adapter = new SqlDataAdapter (

    "select * from [account info]",

    "server=localhost;database=mybank;uid=sa;pwd="

);

// Query the data source

DataSet ds = new DataSet ();

adapter.Fill (ds, "Accounts");

  .

  .

  .

// Update the data source

SqlCommandBuilder builder = new SqlCommandBuilder (adapter);

builder.QuotePrefix = "[";

builder.QuoteSuffix = "]";

adapter.Update (table);

 

Now the update works just fine because CommandBuilder delimits the table name properly. Of course, it's best to avoid using spaces in table names to begin with. Or better yet, forget CommandBuilder altogether and provide the DataAdapter with your own INSERT, UPDATE, and DELETE commands (preferably in the form of stored procedures). Even so, this is a handy trick to know.

 

Jeff Prosise is author of several books, including Programming Microsoft .NET (Microsoft Press). He also is a co-founder of Wintellect (http://www.wintellect.com), a software consulting and education firm that specializes in .NET. Got a question for this column? Submit queries to [email protected].

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish