Wednesday, August 12, 2009

Using Parameters in ADO.NET

As a general rule, parameters should always be used when inserting values into SQL statements. Many people, generally new developers, don't follow this rule because they either don't realise that parameters exist or they don't understand the issues that their use helps to avoid. When I was new to .NET programming, I fell into the former category. Not having read extensively on ADO.NET at the time, I didn't know that parameters could be used to insert field values into SQL code. I started out writing code like this:

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES ('" + this.firstNameField.Text +
             "', '" + this.lastNameField.Text +
             "', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +
             "', " + this.childrenSpinner.Value + ")";
SqlCommand myCommand = new SqlCommand(sql);

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES ('" & Me.firstNameField.Text & _
                    "', '" & Me.lastNameField.Text & _
                    "', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _
                    "', " & Me.childrenSpinner.Value & ")"
Dim myCommand As New SqlCommand(sql)

N.B. I will initially write all examples using the SqlClient ADO.NET provider for SQL Server and later I will discuss the adjustments required when using other providers and other data sources.

The previous example demonstrates various issues associated with this type of code but let’s start with the most obvious: it’s hard to read. With all the various literals and property values being concatenated it’s a bit hard to immediately pick out what’s what. As a result, it’s very easy to make mistakes in such code. It’s very easy to miss a single quote here or a comma there. Of course, it’s not too hard to actually view the end result to see if it is what you intended it to be. I’m constantly surprised, though, by the number of people who build such strings and still don’t actually look at their contents, even when an error occurs.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES ('" + this.firstNameField.Text +
             "', '" + this.lastNameField.Text +
             "', '" + this.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") +
             "', " + this.childrenSpinner.Value + ")";
 
MessageBox.Show(sql);

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES ('" & Me.firstNameField.Text & _
                    "', '" & Me.lastNameField.Text & _
                    "', '" & Me.dateOfBirthPicker.Value.ToString("yyyy-MM-dd") & _
                    "', " & Me.childrenSpinner.Value & ")"
 
MessageBox.Show(sql)

While there are other, possibly better ways, this is the first thing that should occur to a new developer: a simple message box to display the string that was constructed. This will make it much easier to see if you’ve simply missed some punctuation. This is a lesson that should be taken into all development: always view your data and don’t just assume that it is what it’s supposed to be. If an error occurs then you’ve done something wrong and invalid or incorrect data is always a likely candidate.

Now, I’ve always been a big fan of the String.Format method in preference to straight string concatenation when I need to join more than two or three substrings together. Building SQL statements was one of the first scenarios in which I put it to work. It makes such code easier to read and, therefore, less error-prone.

C#

string sql = string.Format("INSERT INTO User (FirstName, LastName, " +
                           "DateOfBirth, ChildCount) " +
                           "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})",
                           this.firstNameField.Text,
                           this.lastNameField.Text,
                           this.dateOfBirthPicker.Value,
                           this.childrenSpinner.Value);
SqlCommand myCommand = new SqlCommand(sql);

VB

Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _
                                  "DateOfBirth, ChildCount) " & _
                                  "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _
                                  Me.firstNameField.Text, _
                                  Me.lastNameField.Text, _
                                  Me.dateOfBirthPicker.Value, _
                                  Me.childrenSpinner.Value)
Dim myCommand As New SqlCommand(sql)

As you can see, it’s much easier to tell where each single quote and comma is and, therefore, whether any are missing. It also makes formatting values such as dates a bit neater.

I thought that this was an improvement, and it was, but it brings me to the next issue: inserting dates and times into SQL code. As you can see in the last two examples, I’ve had to explicitly format the date of birth. That’s because you cannot simply rely on a date and/or time value to be formatted by the .NET Framework in a way that is valid for your database. Even if the format is valid you may end up using a different value to the one you expected because of regional settings. If your month and day values get transposed between your application and your database then you may end up saving incorrect data, which is even worse than your application failing with an error message. We can always do as I’ve done above and use a format that will always work. It’s not a big deal to find out how each database you use formats date literals and then explicitly create that format, but wouldn’t it be better to not have to worry about format at all? When you use parameters you don’t have to.

Before I provide a code example that does use parameters I will touch on another common issue: strings containing apostrophes. This is a very common problem which, again, can be diagnosed by actually viewing the string you build. Doing so should make it obvious that you end up with mismatched single quotes. The question is, how do you prevent the issue. To include a single quote in a text literal you escape it with another single quote. As string concatenation is creating a SQL statement containing literals, that’s what needs to be done:

C#

string sql = string.Format("INSERT INTO User (FirstName, LastName, " +
                           "DateOfBirth, ChildCount) " +
                           "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})",
                           this.firstNameField.Text.Replace("'", "''"),
                           this.lastNameField.Text.Replace("'", "''"),
                           this.dateOfBirthPicker.Value,
                           this.childrenSpinner.Value);
SqlCommand myCommand = new SqlCommand(sql);

VB

Dim sql As String = String.Format("INSERT INTO User (FirstName, LastName, " & _
                                  "DateOfBirth, ChildCount) " & _
                                  "VALUES ('{0}', '{1}', '{2:yyyy-MM-dd}', {3})", _
                                  Me.firstNameField.Text.Replace("'", "''"), _
                                  Me.lastNameField.Text.Replace("'", "''"), _
                                  Me.dateOfBirthPicker.Value, _
                                  Me.childrenSpinner.Value)
Dim myCommand As New SqlCommand(sql)

Now, that’s not a big deal but why do it if it’s not necessary? Also, what about other unusual characters like line breaks? The simple fact is that, if you use parameters, all these considerations go away. The values never become part of the literal string so there’s no need to escape single quotes. Values remain in binary form rather than being converted to strings, so there’s no need to worry about format. There’s no need to worry about what values need to be wrapped in single quotes or other delimiters and which don’t. None of that is a concern.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";
SqlCommand myCommand = new SqlCommand(sql);
SqlParameterCollection parameters = myCommand.Parameters;
 
parameters.AddWithValue("@FirstName", this.firstNameField.Text);
parameters.AddWithValue("@LastName", this.lastNameField.Text);
parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"
Dim myCommand As New SqlCommand(sql)
 
With myCommand.Parameters
   .AddWithValue("@FirstName", Me.firstNameField.Text)
   .AddWithValue("@LastName", Me.lastNameField.Text)
   .AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)
   .AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))
End With

The first point to note here is how much easier the SQL code is to read. There’s no need for any string concatenation (other than what I’ve used to fit the code within the width of this page), punctuation or formatting, so the opportunity for errors is greatly reduced. In this case (but not all), each value has a name that makes its purpose obvious. Those names match up with the names of the parameters that are added to the command, so it’s easy to trace exactly what’s happening.

Now, let’s examine how the parameters are added to the command. In cases where the SQL code will be executed only once the parameters will only ever have one value. In such cases it’s advisable to call the AddWithValue method as it’s the simplest way to add a parameter and set its value.

The SqlClient ADO.NET provider supports named parameters, so notice that the parameter names in the SQL code match the parameter names passed to AddWithValue. We’ll look at positional parameters later but, in this case, the names must match or you’ll either send the wrong data to the wrong place or an exception will be thrown, which are obviously both poor outcomes.

When calling AddWithValue, the data type of the parameter is inferred from the type of the specified value. That’s why, in the case of the parameter named @ChildCount, the Value property of the NumericUpDown control is converted to an Int32 before being assigned. If the Value property was used as is then the type of the parameter would be inferred from a Decimal instead of an Int32 and a data type mismatch would occur at the database.

Note also that, in the case of the parameter named @DateOfBirth, a DateTime is retrieved from the DateTimePicker’s Value property and then the Date property of that value is used. Because the code is not using formatting to create a date-only string, if we want only the date and not the time then we must explicitly assign only the date and not the time.

As mentioned previously, if the SQL code is being executed only once then the value of each parameter doesn’t need to change. In such cases it is easiest to specify the value when creating the parameter and let the data type be inferred. In cases where the SQL code will be executed multiple times with different parameter values each time we need to take a different approach. In such cases we need to specify the data type when creating the parameters, then set their Value properties each time we want to use new data.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";
SqlCommand myCommand = new SqlCommand(sql, myConnection);
SqlParameterCollection parameters = myCommand.Parameters;
 
parameters.Add("@FirstName", SqlDbType.VarChar, 50);
parameters.Add("@LastName", SqlDbType.VarChar, 50);
parameters.Add("@DateOfBirth", SqlDbType.DateTime);
parameters.Add("@ChildCount", SqlDbType.Int);
 
foreach (ListItem item in myList)
{
    parameters["@FirstName"].Value = item.FirstName;
    parameters["@LastName"].Value = item.LastName;
    parameters["@DateOfBirth"].Value = item.DateOfBirth;
    parameters["@ChildCount"].Value = item.ChildCount;
 
    myCommand.ExecuteNonQuery();
}

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"
Dim myCommand As New SqlCommand(sql, myConnection)
 
With myCommand.Parameters
    .Add("@FirstName", SqlDbType.VarChar, 50)
    .Add("@LastName", SqlDbType.VarChar, 50)
    .Add("@DateOfBirth", SqlDbType.DateTime)
    .Add("@ChildCount", SqlDbType.Int)
 
    For Each item As ListItem In myList
        .Item("@FirstName").Value = item.FirstName
        .Item("@LastName").Value = item.LastName
        .Item("@DateOfBirth").Value = item.DateOfBirth
        .Item("@ChildCount").Value = item.ChildCount
 
        myCommand.ExecuteNonQuery()
    Next
End With

The parameters are created and added to the command once only and then their Value properties are set each time new values are needed. The data source-specific data types of the parameters are specified when they are created because they cannot be inferred. Finally, the size is also specified for those parameters whose type requires it. Scale and precision can also be set if required.

There is another way to set parameter values also. Using a DataAdapter you might save data directly from a DataTable. In that case you won’t be looping through the records and setting parameter values yourself. It still needs to be done but the DataAdapter will do it for you. In that case though, you need to tell it exactly where to get the value for each parameter. You do this by specifying the SourceColumn of each parameter, which is the name of the column from which the parameter’s Value will be taken for each DataRow.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";
SqlCommand myCommand = new SqlCommand(sql);
SqlParameterCollection parameters = myCommand.Parameters;
 
parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName");
parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName");
parameters.Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth");
parameters.Add("@ChildCount", SqlDbType.Int, 0, "ChildCount");
 
SqlDataAdapter myAdapter = new SqlDataAdapter();
 
myAdapter.InsertCommand = myCommand;

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"
Dim myCommand As New SqlCommand(sql)
 
With myCommand.Parameters
   .Add("@FirstName", SqlDbType.VarChar, 50, "FirstName")
   .Add("@LastName", SqlDbType.VarChar, 50, "LastName")
   .Add("@DateOfBirth", SqlDbType.DateTime, 0, "DateOfBirth")
   .Add("@ChildCount", SqlDbType.Int, 0, "ChildCount")
End With
 
Dim myAdapter As New SqlDataAdapter
 
myAdapter.InsertCommand = myCommand

In this case the fourth parameter of the Add method is the parameter’s SourceColumn. Note that this is the name of a DataColumn in the DataTable that will be passed to the DataAdapter’s Update method, i.e. it is the name of the column the data is coming from, not the name of the column in the database the data is going to. In the majority of cases those two column names will be the same but certainly not in all.

It’s also important to note that, if we are passing a fourth argument to the Add method, we must be passing a third argument too. That third argument is the maximum size of the data. For variable-size data types, like VarChar, this value is important but for fixed-size data types, like Int, it is not. If you know the specific size of a fixed-size data type, e.g. 4 bytes for type Int, then you can specify that value but there’s really no need. All values of a fixed-size data type will be the same size so there’s no need to specify the maximum size. As such you can just pass zero for fixed-size data types.

Now, let’s look at how using parameters differs amongst ADO.NET providers and data sources. First up, let’s look using OleDb and an Access database. The code should look almost exactly the same, except using OleDb classes instead of SqlClient classes.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)";
OleDbCommand myCommand = new OleDbCommand(sql);
OleDbParameterCollection parameters = myCommand.Parameters;
 
parameters.AddWithValue("@FirstName", this.firstNameField.Text);
parameters.AddWithValue("@LastName", this.lastNameField.Text);
parameters.AddWithValue("@DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("@ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"
Dim myCommand As New OleDbCommand(sql)
 
With myCommand.Parameters
    .AddWithValue("@FirstName", Me.firstNameField.Text)
    .AddWithValue("@LastName", Me.lastNameField.Text)
    .AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)
    .AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))
End With

It’s important to understand that there’s a subtle difference in the way the code behaves though. As I said earlier, SqlClient supports named parameters. That is to say that when you add each parameter to a SqlCommand its name is used as a key to identify it. When it comes time to insert the values into the SQL code, every instance of that name is replaced with the value of the parameter with that name. Even though the code in the preceding example uses the same parameter names in the SQL code and the same names when adding the parameters, those names are not actually related.

The Jet OLEDB provider uses the position of each parameter to decide what values to substitute into the SQL code. All the parameter names in the SQL code are numbered and all the parameters added to the OleDbCommand are numbered. Values are then substituted based on those positions, whether or not the names match. This leads to two important considerations when using OleDb and Access. Firstly, while it makes no difference what order the parameters are added to a SqlCommand, you must ensure that parameters are added to an OleDbCommand in the order they appear in the SQL code. Secondly, while the value of a single SqlParameter can be substituted into SQL code as many times as required, even if the same value is to be used more than once in an OleDbCommand, you must add a discrete parameter for each place it will be used.

Next, let’s look at using OleDb with a SQL Server database. This should rarely be done, given that SqlClient provides more features and better performance, but there may be occasions where it’s necessary. Like the Jet OLEDB provider, the SQL Server OLEDB provider supports only positional parameters. Unlike the Jet provider though, it also doesn’t support pseudo-names for the parameters in the SQL code. In this case you must use “?” symbols in the code to represent a parameter.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (?, ?, ?, ?)";
OleDbCommand myCommand = new OleDbCommand(sql);
OleDbParameterCollection parameters = myCommand.Parameters;
 
parameters.AddWithValue("FirstName", this.firstNameField.Text);
parameters.AddWithValue("LastName", this.lastNameField.Text);
parameters.AddWithValue("DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (?, ?, ?, ?)"
Dim myCommand As New OleDbCommand(sql)
 
With myCommand.Parameters
   .AddWithValue("FirstName", Me.firstNameField.Text)
   .AddWithValue("LastName", Me.lastNameField.Text)
   .AddWithValue("DateOfBirth", Me.dateOfBirthPicker.Value.Date)
   .AddWithValue("ChildCount", CInt(Me.childrenSpinner.Value))
End With

The Jet OLEDB provider also supports this notation, as do many others.

There are various other ADO.NET providers that support named parameters however. One such example is the Connector/Net provider available for MySQL. It supports a similar notation to the SqlClient provider except that parameter names must be prefixed with “?” instead of “@”.

C#

string sql = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " +
             "VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)";
MySqlCommand myCommand = new MySqlCommand(sql);
MySqlParameterCollection parameters = myCommand.Parameters;
 
parameters.AddWithValue("?FirstName", this.firstNameField.Text);
parameters.AddWithValue("?LastName", this.lastNameField.Text);
parameters.AddWithValue("?DateOfBirth", this.dateOfBirthPicker.Value.Date);
parameters.AddWithValue("?ChildCount", Convert.ToInt32(this.childrenSpinner.Value));

VB

Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
                    "VALUES (?FirstName, ?LastName, ?DateOfBirth, ?ChildCount)"
Dim myCommand As New MySqlCommand(sql)
 
With myCommand.Parameters
   .AddWithValue("?FirstName", Me.firstNameField.Text)
   .AddWithValue("?LastName", Me.lastNameField.Text)
   .AddWithValue("?DateOfBirth", Me.dateOfBirthPicker.Value.Date)
   .AddWithValue("?ChildCount", CInt(Me.childrenSpinner.Value))
End With

You should check the documentation for other providers and data sources to see what notation they support.

I should also point out that the use of parameters is no different when using stored procedures. Whether your CommandType is Text or StoredProcedure, if your SQL code contains parameters then you add them to your command in exactly the same way.

Finally, let’s look at one of the most important reasons to use parameters in the first place: security. When you use string concatenation to build SQL statements you can insert anything into the code at any position. You may intend to insert just a single value but, if the data is provided by the user, it’s often difficult or impossible to ensure that that’s all you’re getting. This can allow malicious users to perform what’s called “SQL injection”, where they inject a chunk of SQL code into your statement where you intended to use only a value. For example, let’s consider a situation where you want to allow the user to search for an employee record by payroll number:

C#

string sql = "SELECT * " +
             "FROM Employee " +
             "WHERE PayrollNumber = '" +
             this.payrollNumberField.Text +
             "'";

VB

Dim sql As String = "SELECT * " & _
                    "FROM Employee " & _
                    "WHERE PayrollNumber = '" & _
                    Me.payrollNumberField.Text & _
                    "'"

In that case you expect the user to enter a payroll number and the system will find the matching record if such a record exists. For instance, if the user enters the value “12345” then the SQL statement that will eventually be executed will be:

SELECT * FROM Employee WHERE PayrollNumber = ‘12345’

That’s exactly as you intended, but what happens if a malicious user enters the value “12345’; DELETE FROM Employee; SELECT * FROM Employee WHERE PayrollNumber = ‘12345”? The actual SQL code that eventually gets executed will be:

SELECT * FROM Employee WHERE PayrollNumber = ‘12345’
DELETE FROM Employee
SELECT * FROM Employee WHERE PayrollNumber = ‘12345’

Congratulations, you just deleted the entire contents of your Employee table. By using a parameter to insert the payroll number you ensure that whatever the user enters gets treated as a value and cannot be interpreted as SQL code.

There will be situations where SQL injection will not be possible but you should still use parameters all the time. Doing so helps you to avoid various issues and is just a good habit to get into. If you always use parameters then you never have to decide whether you should or not and you can never make the wrong choice.

Friday, August 7, 2009

Retrieving Database Schema Information

ADO.NET allows you to retrieve a great deal of schema information from a database without resorting to writing your own queries to access system tables. There will be times you still need to write your own queries but the majority of commonly used schema information can be retrieved using the GetSchema method of your connection object.

Exactly what's available and the details of how to retrieve it may vary from database to database but the code pattern remains the same. Note that GetSchema is a member of the DbConnection class, thus it is inherited and overridden in each class that is derived from DbConnection, e.g. SqlConnection and OleDbConnection.

So, the first step to using the GetSchema method is to determine what information your database and ADO.NET provider expose. To do that you call the overload of GetSchema that takes no arguments. I’ll use the SqlClient provider for these examples but, as I said, the pattern is the same no matter the provider and no matter the database. Start by creating a new Windows Forms Application project and adding a DataGridView to your form. In the form’s Load event handler, add the following code:

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema();
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema()
End Using

Note that you’ll have to provide your own connection string as appropriate for your database. When you run that project you should see your grid populated with information about the schema collections available. Let’s look at each column of that grid.

The first column is named “CollectionName” and it contains values such as “Tables”, “Views” and “Procedures”. Not surprisingly, those collections provide information about the tables, views and procedures in the database. When calling one of the overloads of GetSchema with parameters, the first argument must be one of the collection names from that column.

The second column is named “NumberOfRestrictions”. A restriction is basically a filter for a collection that you can specify to reduce the amount of information returned. For instance, many collections can be filtered by name of the schema they belong to, e.g. “dbo”. Note that one of the collections you can query is named “Restrictions” and it will give you information about the restrictions available for each collection. When calling the overload of GetSchema with two parameters, the second parameter must be an array of restriction values. We’ll look at restrictions more closely later.

The third column is named “NumberOfIdentifierParts”. It contains the number of columns required to uniquely identify a record in each collection.

Let’s look at getting information from a specific collection. One of the most commonly queried collections is Tables, so let’s take a look at that. Viewing the data in our grid after executing the previous code, we can see that the Tables collection has 4 restrictions and 3 identifier parts. Store that information away for future use. For now, edit the code in your form to get the schema for the Tables collection specifically:

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema("Tables");
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema("Tables")
End Using

Note that, as mentioned earlier, the first argument to GetSchema is the name of the collection you want to query. Executing that code populates our grid with information about all the tables in the database we’re connected to. The columns are named “TABLE_CATALOG”, “TABLE_SCHEMA”, “TABLE_NAME” and “TABLE_TYPE” and they contain the name of the database containing the table, the name of the schema that owns the table, the name of the table and the type of the table respectively.

Looking at the data, consider what would be required to uniquely identify a record. Recall that the NumberOfIdentifierParts for the Tables collection was 3. Those three identifier parts are the TABLE_CATALOG, TABLE_SCHEMA and TABLE_NAME, all required to uniquely identify a table within a server instance.

Now let’s look at the Restrictions collection, the contents of which are important when querying many other collections. Edit your code once again to query the Restrictions collection:

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema("Restrictions");
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema("Restrictions")
End Using

Having executed that code, we see our grid populated with information about all the restrictions that can be applied to schema queries. The first column is named “CollectionName” and contains the name of the collection that the restriction applies to. Note that there are four rows containing the collection name “Tables” and recall that the NumberOfRestrictions for the Tables collection was 4.

The restrictions for the Tables collection are named “Catalog”, “Owner”, “Table” and “TableType” with RestrictionOrder values of 1, 2, 3 and 4 respectively. Those restrictions relate to the TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE columns of the Tables collection respectively.

Let’s say that we want to filter the Tables collection by database. When viewing the contents of the Restrictions collection you should have noticed that the restriction named “Catalog” had a RestrictionOrder of 1, meaning that that must be the first filter value we provide. Try changing the previous code to specify the Tables collection and an array of strings containing the name of the database you’re connected to as the value for the first restriction. For instance, if you don’t specify an Initial Catalog in your SqlClient connection string then you’ll be connected to the “master” database.

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema("Tables",
                                                         new string[]
                                                             {
                                                                 "master",
                                                                 null,
                                                                 null,
                                                                 null
                                                             });
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _
                                                       New String() {"master", _
                                                                     Nothing, _
                                                                     Nothing, _
                                                                     Nothing})
End Using

Note that a null reference is passed for each restriction that is not to be used. Restriction values must be provided in the order specified by the Restrictions collection. As such, a value must be specified for each restriction up to and including the last one you want to use. Any after that can be omitted if desired though. That means that the above code could be rewritten as:

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema("Tables",
                                                         new string[] {"master"});
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _
                                                       New String() {"master"})
End Using

Now, let’s say that we wanted to filter the Tables collection by TABLE_NAME, perhaps to check whether a specific table exists. The Table restriction has a RestrictionOrder of 3 so, to filter by TABLE_NAME, we must provide at least three restriction values. If we don’t want to filter by TABLE_CATALOG and TABLE_SCHEMA then we simply provide null references for those restrictions.

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.dataGridView1.DataSource = connection.GetSchema("Tables",
                                                         new string[]
                                                             {
                                                                 null,
                                                                 null,
                                                                 "SomeTable"
                                                             });
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.DataGridView1.DataSource = connection.GetSchema("Tables", _
                                                       New String() {Nothing, _
                                                                     Nothing, _
                                                                     "SomeTable"})
End Using

Run that code and the grid will contain only information about the table with the specified name.

Because the GetSchema method returns a DataTable, as I’ve shown, the result can be bound very simply, just like any other DataTable. An example of how it might be commonly used is to display a list of tables in a ComboBox. You could query the Tables collection using the GetSchema method and then display the TABLE_NAME values in the drop-down list.

C#

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    this.comboBox1.ValueMember = "TABLE_NAME";
    this.comboBox1.DataSource = connection.GetSchema("Tables");
}

VB

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Me.ComboBox1.ValueMember = "TABLE_NAME"
    Me.ComboBox1.DataSource = connection.GetSchema("Tables")
End Using

I’m sure that, given time, you’ll be able to find other uses for the GetSchema method too.

Monday, August 3, 2009

Using Yes/No or OK/Cancel Buttons

I see a great many people mistakenly using MessageBoxButtons.YesNo buttons when they are prompting the user for confirmation. In that case you should be using MessageBoxButtons.OKCancel buttons. Think of it like this: if the user asked you to do something and you're asking for confirmation then you should always use OKCancel.

To show the difference, consider the situation where the user closes a form with unsaved changes pending. You might use the following code to prompt the user to either close the form and lose the changes or keep the form open:

C#

if (MessageBox.Show("Are you sure you want to close?  " +
                    "If you do so will lose any pending changes",
                    "Confirm Close",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Warning) == DialogResult.No)
{
    // Cancel close here.
}

VB

If MessageBox.Show("Are you sure you want to close?  " & _
                   "If you do so you will lose any pending changes", _
                   "Confirm Close", _
                   MessageBoxButtons.YesNo, _
                   MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
    'Cancel close here.
End If
That would be incorrect though. The correct code would be:

C#

if (MessageBox.Show("Are you sure you want to close?  " +
                    "If you do so will lose any pending changes",
                    "Confirm Close",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Warning) == DialogResult.Cancel)
{
    // Cancel close here.
}

VB

If MessageBox.Show("Are you sure you want to close?  " & _
                   "If you do so you will lose any pending changes", _
                   "Confirm Close", _
                   MessageBoxButtons.OKCancel, _
                   MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Cancel Then
    'Cancel close here.
End If
To see why, consider what would happen if you wanted to expand the choices and prompt the user to either save and close, close without saving or not close at all. In that case you'd use this code:

C#

switch (MessageBox.Show("Are you sure you want to close?  " +
                        "If you do so will lose any pending changes",
                        "Confirm Close",
                        MessageBoxButtons.YesNoCancel,
                        MessageBoxIcon.Warning))
{
    case DialogResult.Yes:
        // Save changes and allow close.
        break;
    case DialogResult.No:
        // Allow close.
        break;
    case DialogResult.Cancel:
        // Cancel close here.
        break;
}

VB

Select Case MessageBox.Show("Are you sure you want to close?  " & _
                            "If you do so you will lose any pending changes", _
                            "Confirm Close", _
                            MessageBoxButtons.YesNoCancel, _
                            MessageBoxIcon.Warning)
    Case Windows.Forms.DialogResult.Yes
        'Save changes and allow close.
    Case Windows.Forms.DialogResult.No
        'Allow close.
    Case Windows.Forms.DialogResult.Cancel
        'Cancel close here.
End Select
Comparing that to the first code example, notice that the meanings of the Yes and No buttons have now changed. Previously Yes meant close without saving and now it means save and close. Previously No meant don't close and now it means close without saving, which is what Yes meant before. Now Cancel means don't close, which is what No meant before. Do you see the inconsistency?

Now, consider the third case compared to the second. Previously Cancel meant don't close and it still does. Previously the OK option meant close and now it has been replaced by two new options, Yes and No, both of which still mean close but add the option of either saving first or not saving.

To generalise, if you are asking the user "do you really want to do what you just asked for" then you use an OK button and if you're asking "would you rather not do what you just asked for" then you use a Cancel button, hence MessageBoxButtons.OKCancel is the correct choice when asking for straight confirmation. When you want to break that first question into two: "do you want to do what you asked for and do this extra thing as well" and "do you want to do just what you asked and not anything extra", then you would use MessageBoxButtons.YesNoCancel. There are plenty of situations where the use of MessageBoxButtons.YesNo is appropriate but confirming an action is not one of them.