Beginning Visual Basic 2005 (2006)

Chapter 16

Parameter	Description
Server	The name of the SQL Server that you want to access. This is usually the
	name of the computer that is running SQL Server. You can use (local) or
	localhost if SQL Server is on the same machine as the one running the
	application. If you are using named instances of SQL Server, then this
	parameter would contain the computer name followed by a backslash
	followed by the named instance of SQL Server.
Database	The name of the database that you want to connect to.

You also need some form of authentication information, which you can accomplish in two ways: by providing a username and password in the connection string or by connecting to SQL Server using the NT account under which the application is running. If you want to connect to the server by specifying a username and password, you need to include additional parameters in your connection string, as shown in the following table.

Parameter	Description
User ID	The username to use to connect to the database. An account with this
	user ID will need to exist in SQL Server and have permission to access the
	specified database.
Password	The password for the specified user.

However, SQL Server can be set up to use the Windows NT account of the user who is running the program to open the connection. In this case, you don’t need to specify a username and password. You just need to specify that you are using integrated security. (The method is called integrated security because SQL Server is integrating with Windows NT’s security system and provides the most secure connection because the User ID and Password parameters need not be specified in the code.) You do this using the Integrated Security parameter, which you set to True when you want the application to connect to SQL Server using the current user’s NT account.

Of course, for this to work, the user of the application must have permission to use the SQL Server database. This is granted using the SQL Server Enterprise Manager.

To see how these parameters function in a connection string to initialize a connection object, look at the following code fragment. It uses the SqlConnection class to initialize a connection object that uses a specific user ID and password in the connection string:

Dim objConnection As SqlConnection = New _ SqlConnection(“Server=localhost;Database=pubs;” & _ “User ID=sa;Password=vbdotnet;”)

This connection string connects to a SQL Server database. The Server parameter specifies that the database resides on the local machine. The Database parameter specifies the database that you want to access — in this case it is the pubs database. Finally, the User ID and Password parameters specify the User ID and password of the user defined in the database. As you can see, each parameter has a value assigned to it using =, and each parameter-value pair is separated by a semicolon.

496

Database Programming with SQL Server and ADO.NET

Opening and Closing the Connection

After you initialize a connection object with a connection string, as shown previously, you can invoke the methods of the SqlConnection object such as Open and Close, which actually open and close a connection to the database specified in the connection string. An example of this is shown in the following code fragment:

‘Open the database connection...

objConnection.Open()

‘... Use the connection

‘Close the database connection...

objConnection.Close()

Although many more properties and methods are available in the SqlConnection class, the ones mentioned so far are all you are really interested in to complete the hands-on exercises, and they should be enough to get you started.

SqlCommand

The SqlCommand class represents a SQL command to execute against a data store. The command will usually be a select, insert, update, or delete query, and can be a SQL string or a call to a stored procedure. The query being executed may contain parameters or it may not.

In the example in Chapter 15, the Data Adapter Configuration Wizard generated a command object

for you (although in that case it was an OleDbCommand). In that case, a data adapter was using the command to fill a dataset. You look at how to write code to do this later in the chapter. For the moment, look at command objects alone. You learn how they relate to data adapters in the next section.

The constructor for the SqlCommand class has several variations, but the simplest method is to initialize a SqlCommand object with no parameters. Then, after the object has been initialized, you can set the properties you need to perform the task at hand. The following code fragment shows how to initialize a SqlCommand object:

Dim objCommand As SqlCommand = New SqlCommand()

When using data adapters and datasets, there isn’t much call for using command objects on their own. They will mainly be used for executing a particular select, delete, insert, or update, so that is what you will cover in this chapter. You can also use command objects with a data reader. A data reader is an alternative to a DataSet that uses fewer system resources but provides far less flexibility. In this book, you will concentrate on using the DataSet, because it is the more common and useful of the two.

The Connection Property

Certain properties must be set on the SqlCommand object before you can execute the query. The first of these properties is the Connection property. This property is set to a SqlConnection object, as shown in the next code fragment.

objCommand.Connection = objConnection

For the command to execute successfully, the connection must be open at the time of execution.

497

Chapter 16

The CommandText Property

The next property that must be set is the CommandText property. This property specifies the SQL string or stored procedure to be executed. Most databases require that you place all string values in single quote marks, as shown here:

Dim objConnection As SqlConnection = New _ SqlConnection(“server=(local);database=pubs;user id=sa;password=”)

Dim objCommand As SqlCommand = New SqlCommand() objCommand.Connection = objConnection objCommand.CommandText = “INSERT INTO authors “ & _

“(au_id, au_lname, au_fname, contract) “ & _ “VALUES(‘123-45-6789’, ‘Barnes’, ‘David’, 1)”

The INSERT statement is a very simple one that means “Insert a new row into the authors table. In the au_id column put ‘123-45-6789’, in the au_lname column put ‘Barnes’, in the au_fname column put ‘David’, and in the contract column put ‘1’.”

This is the basic way that INSERT statements work in SQL. You have INSERT INTO followed by a table name. You then have a series of column names, in parentheses. You then have the VALUES keyword followed by a set of values, to be inserted into the columns that you’ve just named and in the same order.

This assumes that you know the values to insert when you are writing the program, which is unlikely in most cases. Fortunately, you can create commands with parameters and then set the values of these parameters separately. Let’s have a look at how to use parameters.

The Parameters Collection

Placeholders are variables prefixed with an at (@) sign in the SQL statement; they get filled in by parameters. So if you want to update the authors table as discussed in the previous section, but didn’t know the values at design time, you would do this:

Dim objConnection As SqlConnection = New _ SqlConnection(“server=(local);database=pubs;user id=sa;password=”)

Dim objCommand As SqlCommand = New SqlCommand() objCommand.Connection = objConnection objCommand.CommandText = “INSERT INTO authors “ & _

“(au_id, au_lname, au_fname, contract) “ & _ “VALUES(@au_id,@au_lname,@au_fname,@au_contract)”

Here, instead of providing values, you provided placeholders. Placeholders, as mentioned, always start with an @ symbol. They do not need to be named after the database column that they represent, but it is often easier if they are, and it helps to self-document your code.

Next, you need to create parameters that will be used to insert the values into the placeholders when the SQL statement is executed. You need to create and add parameters to the Parameters collection of the SqlCommand object. The term parameters here refers to the parameters required to provide data to your SQL statement or stored procedure, not to the parameters that are required to be passed to a Visual Basic 2005 method.

498

Database Programming with SQL Server and ADO.NET

You can access the Parameters collection of the SqlCommand object by specifying the Parameters property. After you access the Parameters collection, you can use its properties and methods to create one or more parameters in the collection. The easiest way to add a parameter to a command is demonstrated in the following example:

Dim objConnection As SqlConnection = New _ SqlConnection(“server=(local);database=pubs;user id=sa;password=”)

Dim objCommand As SqlCommand = New SqlCommand() objCommand.Connection = objConnection objCommand.CommandText = “INSERT INTO authors “ & _

“(au_id, au_lname, au_fname, contract) “ & _ “VALUES(@au_id,@au_lname,@au_fname,@au_contract)”

objCommand.Parameters.AddWithValue (“@au_id”, txtAuId.Text) objCommand.Parameters.AddWithValue (“@au_lname”, txtLastName.Text)

objCommand.Parameters.AddWithValue (“@au_fname”, txtFirstName.Text) objCommand.Parameters.AddWithValue (“@au_contract”, chkContract.Checked)

The AddWithValue method here accepts the name of the parameter and the object that you want to add. In this case, you are using the Text property of various Text box objects on a (fictitious) form for most of the columns. For the Contract column you use the Checked property of a check box on the same form. In previous versions of ADO.NET, you could use the add method to add a parameter with a value. That overload is now obsolete.

The ExecuteNonQuery Method

Finally, you can execute the command. To do this, the connection needs to be opened. You can invoke the ExecuteNonQuery method of the SqlCommand object. This method executes the SQL statement and causes the data to be inserted into the database. It then returns the number of rows that were affected by the query, which can be a useful way to check that the command worked as expected. To complete your code fragment, you need to open the connection, execute the query, and close the connection again:

Dim objConnection As SqlConnection = New _ SqlConnection(“server=(local);database=pubs;user

id=sa;password=”)

Dim objCommand As SqlCommand = New SqlCommand() objCommand.Connection = objConnection objCommand.CommandText = “INSERT INTO authors “ & _

“(au_id, au_lname, au_fname, contract) “ & _ “VALUES(@au_id,@au_lname,@au_fname,@au_contract)”

objCommand.Parameters.AddWithValue(“@au_id”, txtAuId.Text) objCommand.Parameters.AddWithValue(“@au_lname”, txtLastName.Text) objCommand.Parameters.AddWithValue(“@au_fname”, txtFirstName.Text) objCommand.Parameters.AddWithValue(“@au_contract “, chkContract.Checked) objConnection.Open()

objCommand.ExecuteNonQuery()

objConnection.Close()

SqlDataAdapter

The SqlDataAdapter class is similar to the OleDbDataAdapter that you configured with wizards in the previous chapter. The main difference is that the OleDbDataAdapter can access any data source that supports OLE DB, while the SqlDataAdapter supports only SQL Server databases. You can use them in

499

Chapter 16

a similar way though; you can configure a SqlDataAdapter using wizards, just as you configured an OleDbDataAdapter in the previous chapter (provided you are accessing an SQL Server data source). In this chapter, you look at how to configure and use an SqlDataAdapter in code, but these guidelines also apply to the OleDbDataAdapter.

Data adapters act as bridges between your data source and in-memory data objects such as the DataSet. To access the data source, they use the command objects you’ve just looked at. These command objects are associated with connections, so the data adapter relies on command and connection objects to access and manipulate the data source.

The SqlDataAdapter class’s SelectCommand property is used to hold a SqlCommand that retrieves data from the data source. The data adapter then places the result of the query into a DataSet or DataTable. The SqlDataAdapter also has UpdateCommand, DeleteCommand, and InsertCommand properties. These are also SqlCommand objects, used to write changes made to a DataSet or DataTable back to the data source. This may all seem complicated, but in fact the tools are really easy to use. You learned enough SQL in the previous chapter to write a SelectCommand, and there are tools called command builders that you can use to automatically create the other commands based on this.

Take a look at the SelectCommand property, and then look at how you can create commands for updating, deleting, and inserting records.

The SelectCommand Property

The SqlDataAdapter class’s SelectCommand property is used to fill a DataSet with data from a SQL Server database, as shown in Figure 16-1.

SqlDataAdapter

DataSet

Select Command

A collection of tables, relationships and constraints, consistent with the data read from the data store.

SQL Server

Figure 16-1

500

Database Programming with SQL Server and ADO.NET

When you want to read data from the data store, you must set the SelectCommand property of the SqlDataAdapter class first. This property is a SqlCommand object and is used to specify what data to select and how to select that data. Therefore the SelectCommand property has properties of its own, and you need to set them just as you would set properties on a normal command. You’ve already seen the following properties of the SqlCommand object:

Connection: Sets the SqlConnection object to be used to access the data store.

CommandText: Sets the SQL statements or stored procedure name to be used to select the data.

In the previous examples of SqlCommand objects, you used straight SQL statements. If you want to use stored procedures, you need to be aware of an additional property, CommandType, which sets a value that determines how the CommandText property is interpreted.

In this chapter, you are going to concentrate on SQL statements, but stored procedures are often useful too, particularly if they already exist in the database. If you want to use one, set the CommandText property to the name of the stored procedure (remember to enclose it in quote marks because the compiler treats this as a string), and set the CommandType property to CommandType.StoredProcedure.

Setting SelectCommand to a SQL String

Take a look at how you set these properties in code. The code fragment that follows shows the typical settings for these properties when executing a SQL string:

‘ Declare a SqlDataAdapter object...

Dim objDataAdapter As New SqlDataAdapter()

‘Assign a new SqlCommand to the SelectCommand property objDataAdapter.SelectCommand = New SqlCommand()

‘Set the SelectCommand properties...

objDataAdapter.SelectCommand.Connection = objConnection objDataAdapter.SelectCommand.CommandText = _

“SELECT au_lname, au_fname FROM authors “ & _ “ORDER BY au_lname, au_fname”

The first thing that this code fragment does is declare a SqlDataAdapter object. This object has a SelectCommand property set to a SqlCommand; you just need to set that command’s properties. You set the properties by first setting the Connection property to a valid connection object, one that will already have been created before the code that you see here. Next, you set the CommandText property to your SQL SELECT statement.

Setting SelectCommand to a Stored Procedure

This next code fragment shows how you could set these properties when you want to execute a stored procedure. A stored procedure is a group of SQL statements that are stored in the database under a unique name and are executed as a unit. The stored procedure in this example (usp_select_author_ titles) uses the same SQL statement that you used in the previous code fragment:

‘ Declare a SqlDataAdapter object...

Dim objDataAdapter As New SqlDataAdapter()

‘ Assign a new SqlCommand to the SelectCommand property

501

Chapter 16

objDataAdapter.SelectCommand = New SqlCommand()

‘ Set the SelectCommand properties...

objDataAdapter.SelectCommand.Connection = objConnection objDataAdapter.SelectCommand.CommandText = “usp_select_author_titles” objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

The CommandText property now specifies the name of the stored procedure that you want to execute instead of the SQL string that was specified in the last example. Also notice the CommandType property. In the first example, you did not change this property, because its default value is CommandType.Text, which is what you need to execute SQL statements. In this example, it is set to a value of CommandType. StoredProcedure, which indicates that the CommandText property contains the name of a stored procedure to be executed.

Using Command Builders to Create the Other Commands

The SelectCommand is all you need to transfer data from the database into your DataSet. After you let your users make changes to the DataSet, though, you will want to write the changes back to the database. You can do this by setting up command objects with the SQL for inserting, deleting, and updating. Alternatively, you can use stored procedures. Both of these solutions require knowledge of SQL outside the scope of this book. Fortunately, there is an easier way; you can use command builders to create these commands. It takes only one more line:

‘ Declare a SqlDataAdapter object...

Dim objDataAdapter As New SqlDataAdapter()

‘Assign a new SqlCommand to the SelectCommand property objDataAdapter.SelectCommand = New SqlCommand()

‘Set the SelectCommand properties...

objDataAdapter.SelectCommand.Connection = objConnection objDataAdapter.SelectCommand.CommandText = “usp_select_author_titles” objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

‘automatically create update/delete/insert commands

Dim objCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(objDataAdapter)

Now you can use this SqlDataAdapter to write changes back to a database. You will look more at this later in the chapter. For know, look at the method that gets data from the database to the DataSet in the first place: the Fill method.

The Fill Method

You use the Fill method to populate a DataSet object with the data that the SqlDataAdapter object retrieves from the data store using its SelectCommand. However, before you do this you must first initialize a DataSet object. To use the DataSet object in your project, you must add a reference to

System.Xml.

‘ Declare a SqlDataAdapter object...

Dim objDataAdapter As New SqlDataAdapter()

‘ Assign a new SqlCommand to the SelectCommand property objDataAdapter.SelectCommand = New SqlCommand()

502

Database Programming with SQL Server and ADO.NET

‘ Set the SelectCommand properties...

objDataAdapter.SelectCommand.Connection = objConnection objDataAdapter.SelectCommand.CommandText = “usp_select_author_titles” objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure Dim objDataSet as DataSet = New DataSet()

Now that you have method has several syntax for the Fill

a DataSet and a SqlDataAdapter, you can fill your DataSet with data. The Fill overloaded versions, but you will be discussing the one most commonly used. The method is shown here:

SqlDataAdapter.Fill(DataSet, string)

The DataSet argument specifies a valid DataSet object that will be populated with data. The string argument gives the name you want the table to have in the DataSet. Remember that one DataSet can contain many tables. You can use any name you like, but usually it’s best to use the name of the table from which the data in the database has come. This helps you self-document your code and makes the code easier to maintain.

The following code fragment shows how you invoke the Fill method. The string “authors” is specified as the string argument. This is the name you want to use when manipulating the in-memory version of the table; it is also the name of the table in the data source.

‘ Declare a SqlDataAdapter object...

Dim objDataAdapter As New SqlDataAdapter()

‘Create an instance of a new select command object objDataAdapter.SelectCommand = New SqlCommand

‘Set the SelectCommand properties...

objDataAdapter.SelectCommand.Connection = objConnection objDataAdapter.SelectCommand.CommandText = “usp_select_author_titles” objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure Dim objDataSet as DataSet = New DataSet()

‘Fill the DataSet object with data...

objDataAdapter.Fill(objDataSet, “authors”)

The Fill method uses the SelectCommand.Connection property to connect to the database. If the connection is already open, the data adapter will use it to execute the SelectCommand and leave it open after it’s finished. If the connection is closed, then the data adapter will open it, execute the SelectCommand, and then close it again.

You now have data in memory and can start manipulating it independently of the data source. Notice that the DataSet class does not have Sql at the start of its class name. This is because DataSet is not in the System.Data.SqlClient namespace, it is in the parent System.Data namespace. The classes in this namespace are primarily concerned with manipulating data in memory, rather than obtaining data from any particular data source. Once you have the data loaded into a DataSet, it no longer matters what data source it came from (unless you need to write it back). Let’s have a look at two of the classes in this namespace: the DataSet and the DataView.

503

Chapter 16

The DataSet Class

The DataSet class is used to store data retrieved from a data store and stores that data in memory on the client. The DataSet object contains a collection of tables, relationships, and constraints that are consistent with the data read from the data store. It acts as a lightweight database engine all by itself, enabling you to store tables, edit data, and run queries against it using a DataView object.

The data in a DataSet is disconnected from the data store, and you can operate on the data independently from the data store. You can manipulate the data in a DataSet object by adding, updating, and deleting the records. You can apply these changes back to the original data store afterwards using a data adapter.

The data in a DataSet object is maintained in Extensible Markup Language (XML, which is discussed in detail in Chapter 18), meaning that you can save a DataSet as a file or easily pass it over a network. The XML is shielded from you as a developer, and you should never need to edit the XML directly. All editing of the XML is done through the properties and methods of the DataSet class. Many developers like using XML and will sometimes choose to manipulate the XML representation of a DataSet directly, but this is not essential.

Like any XML document, a DataSet can have a schema (a file that describes the structure of the data in one or more XML files). When you generated a typed dataset in the previous chapter, an XML Schema Definition (XSD) file was added to the Solution Explorer, as shown in Figure 16-2.

Figure 16-2

This file is an XML schema for the data that the CustomerDataSet would hold. From this, Visual Studio .NET was able to create a class that inherited from the DataSet and that used this particular schema. A DataSet schema contains information about the tables, relationships, and constraints stored in the DataSet. Again, this is shielded from you, and you do not need to know XML to work with a

DataSet.

Since the DataSet contains the actual data retrieved from a data store, you can bind the DataSet to a control or controls to have them display (and allow editing of) the data in the DataSet. You did this a bit in Chapter 15, and you will see more later in this chapter.

DataView

The DataView class is typically used for sorting, filtering, searching, editing, and navigating the data from a DataSet. A DataView is bindable, meaning that it can be bound to controls in the same way that the DataSet can be bound to controls. Again, you learn more about data binding in code later in this chapter.

504

Database Programming with SQL Server and ADO.NET

A DataSet can contain a number of DataTable objects; when you use the SqlDataAdapter class’s Fill method to add data to a DataSet, you are actually creating a DataTable object inside the DataSet. The DataView provides a custom view of a DataTable; you can sort or filter the rows, for example, as you can in a SQL query.

You can create a DataView from the data contained in a DataTable that contains only the data that you want to display. For example, if the data in a DataTable contains all authors sorted by last name and first name, you can create a DataView that contains all authors sorted by first name and then last name. Or, if you wanted, you could create a DataView that contained only last names or certain names.

Although you can view the data in a DataView in ways different from the underlying DataTable, it is still the same data. Changes made to a DataView affect the underlying DataTable automatically, and changes made to the underlying DataTable automatically affect any DataView objects that are viewing that DataTable.

The constructor for the DataView class initializes a new instance of the DataView class and accepts the DataTable as an argument. The following code fragment declares a DataView object and initializes it using the authors table from the DataSet named objDataSet. Notice that the code accesses the Tables collection of the DataSet object, by specifying the Tables property and the table name:

‘ Set the DataView object to the DataSet object...

Dim objDataView = New DataView(objDataSet.Tables(“authors”))

The Sort Property

Once a DataView has been initialized and is displaying data, you can alter the view of that data. For example, suppose you want to sort the data in a different order than in the DataSet. To sort the data in a DataView, you set the Sort property and specify the column or columns that you want sorted.

The following code fragment sorts the data in a DataView by author’s first name and then last name:

objDataView.Sort = “au_fname, au_lname”

Notice that this is the same syntax as the ORDER BY clause in a SQL SELECT statement. As in the SQL ORDER BY clause, sorting operations on a DataView are always performed in an ascending order by default. If you wanted to perform the sort in descending order, you would need to specify the DESC keyword, as shown in the next code fragment:

objDataView.Sort = “au_fname, au_lname DESC”

The RowFilter Property

When you have an initialized DataView, you can filter the rows of data that it will contain. This is similar to specifying a WHERE clause in an SQL SELECT statement; only rows that match the criteria will remain in the view. The underlying data is not affected, though. The RowFilter property specifies the criteria that should be applied on the DataView. The syntax is similar to the SQL WHERE clause. It contains at least a column name followed by an operator and the value. If the value is a string, it must be enclosed in single quote marks as shown in the following code fragment, which retrieves only the authors whose last names are Green:

‘ Set the DataView object to the DataSet object...

objDataView = New DataView(objDataSet.Tables(“authors”)) objDataView.RowFilter = “au_lname = ‘Green’”

505