Retrieving Data

With a good idea of the new object model, you're ready to take a look at the tools that Visual Studio .NET provides and learn how to use them. First stop, the Server Explorer. If you haven't seen it yet, it has a lot of functionality and use in Visual Studio. This chapter focuses on using it to get some data. Figure 5.3 shows you what the Server Explorer looks like.

The two sections you'll see are Data Connections and Servers. The Servers section allows you to connect to one or more Windows machines. Typically you will use it to connect to a server and get easy access to its services, SQL Server instances or maybe its event logs, but it is not limited to servers. With a default installation of Visual Studio, your local machine will show up in the list of servers to play with. This section focuses on the Data Connections section. It helps you more with the task at hand of building data applications.

Data Connections

First things first, before you can access any of your data, you need to create a connection to your database. To create a data connection in Visual Studio .NET, simply right-click the Data Connections node in the Server Explorer window and click Add Connection or click the Connect to Database button in the toolbar across the top. This will bring up the Data Link Properties dialog box, which will more than likely be pretty familiar because it has existed in most of Microsoft applications that work with data. You can add any type of connection you'd like to any data source, but for these examples, you're going to add a connection to the Northwind SQL Server database. To do so, enter the name of a SQL Server instance name, which by default is the name of the server. Then set up the appropriate authentication to connect to it. The type of authentication to use depends on how your SQL Server is configured, so check with your SQL administrator if you are unsure of what type of authentication to use. Finally, enter in the name of the database to connect to, in this case, “Northwind,” or select it from the drop-down list and click the OK button. Assuming everything was set up properly, you will now see a new item under Data Connections in Server Explorer. By default, the name is in the form <SQLInstanceName>.<DatabaseName>.<DatabaseOwner>. You can easily change this to anything you'd like.

If you expand this new connection, you'll see most of the things you'd expect to see in SQL Server Enterprise Manager, including tables, views, and stored procedures. You can also edit all these items and perform queries, which can come in handy at times.

Now that you have a connection, let's get started. Create a new Windows Forms project. For this example, expand the Tables node in the Data Connection and select the Customers table. Make sure that the default Form1 is open and in design view. Now click and drag the table from the Server Explorer to the design surface of your form. You'll notice two new objects are created: a SqlConnection and a SqlDataAdapter.

Establishing a Database Connection

The Connection (SqlConnection for SQL Server, or OleDBConnection for OLE DB data sources) class isn't very far off from the ADODB.Connection. It has a ConnectionString property and Open and Close methods that should be pretty familiar. It also has built-in support for dealing with transactions. When you create a Transaction through your Connection object, it creates a real database transaction, but it exists across more than a single database call, as opposed to handling transactions within your SQL calls and Stored Procedures. Having a transaction that you can control outside of your SQL statements allows you to commit or roll it back based on the results of non-database activities.

If you wanted to add a record to your table and at the same time add a file to the file system that was somehow related, you could wrap this pair of actions in a transaction. By placing a Try Catch statement around both statements, you can rollback the database action if the other activity fails. It is worth noting though, that in the reverse situation (if database action fails, but the non-database action succeeds) you will have to handle “rolling back” the non-database change in your own code. (There are exceptions to this rule though, check out the System.EnterpriseServices namespace for more information on COM+ and the concept of “distributed transactions”.)

When wrapping both database and non-database actions together into a single transaction it is often easier to place the database work first, because it can be easily rolled back. If the database activity is first and either it or the non-database activity fails, then you only have to concern yourself with rolling back the database action. For more information on transactions, refer to Chapter 6.

DataAdapter

You can think of the DataAdapter (SqlDataAdapter against SQL Server or OleDBDataAdapter against OLE DB data sources) as an excellent helper class for pulling data from a database to a DataSet or DataTable, but especially for updating data back to the database. The DataAdapter has two main methods: Fill and Update. It has a SelectCommand property that is of type SqlCommand (or OleDBCommand against an OleDB data source. The Command object (SqlCommand for SQL Server, OleDBCommand for OLE DB data sources) is another object that existed in ADO, but has been improved upon. In the SqlDataAdapter, the SelectCommand lets the Fill Method know how and what data it should be pulling from the database to put into the supplied DataSet. For now, focus on using the SqlDataAdapter for “filling” a DataSet, but first you need to understand the SqlCommand object. For a more in depth look at the SqlDataAdapter, refer to Chapter 6.

Using the Command Class

The Command (SqlCommand or OleDBCommand) class is at the core of working with data. It can be used to pull data as well as send data back to the database.

The key properties on the SqlCommand object are CommandType, CommandText, and Connection. The CommandType is an Enum that you can set to Text, StoredProcedure, or TableDirect. Setting the CommandType will tell the SqlCommand object how it should interpret the CommandText property, which is a string that either represents the SQL statement to execute, the stored procedure name to execute, or the name of the table to retrieve the result set from. In ADO, the precursor to ADO.NET (used in many Visual Basic 6.0 applications), if you didn't set the CommandType property correctly, then it would be determined for you, but in ADO.NET not setting the CommandType will result in an error.

One great advantage that you'll start to notice about ADO.NET over ADO is that the object model is much cleaner and objects better relate to each other and work together to get the job done.

The Parameter property is another important one with the Command object as it can hold parameters to be used with your queries that can be programmatically set. The power of this is that you won't have to build the queries as a string anymore to supply the values for the SQL where statement. Building the query as a string in the past has also had some security implications, such as SQL injection by hackers. The Parameters collection obviates this issue. For more information on using the Parameters collection, refer to Chapter 6.

Filling a DataSet

Now that you have a good idea how all these objects work with the existing data model, let's continue with this example and query the Northwind database for some information about a customer.

Earlier, you used some of the cool RAD (Rapid Application Development) features of Visual Studio .NET to help set up the objects you need to retrieve some data from the Customers table. At this point, you should see in the component tray of Form1 two objects—SqlConnection1 and SqlDataAdapter1. You can use the Property window to browse around and check out some of the things that VS .NET sets up for you. The connection has its ConnectionString property already set to the proper string. You can also dig around in the DataAdapter to see what it has already set up. In the Property window, you can expand the SelectCommand property to see that it was nice enough to set up a SQL Select statement that selects all the fields from the Customers table. It also sets a reference to SqlConnection1, so when it executes, it will know what source to execute it on.

With that, you can now go get some data from the Customers table. Listing 5.1 demonstrates how to do this in the load event of the form.

Listing 5.1. Retrieving data using the Fill method of the DataAdapter.

Private Sub Form1_Load( _
        ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles MyBase.Load
     Dim Customers As New DataTable
     SqlDataAdapter1.Fill(Customers)
End Sub

In the load event, you're declaring a new DataTable, which is appropriate because you're really only dealing with one table. If you were dealing with more than one table, using a DataSet would be appropriate. Because DataAdapter already knows what SQL statement to execute when you want to pull data out, because of the SelectCommand property of SqlDataAdapter1, all you have to do is call the Fill method and supply the DataTable as the parameter. The DataAdapter will then take care of everything. It will open the connection, execute the SQL statement on the connection, and place the results in the DataTable. Just to prove that it's really working, let's display a message that tells you the “CustomerName” value of the first record in our Customer table.

MessageBox.Show(Customers.Rows(0)("CustomerName").ToString)

Again, as discussed before, you're going to use the Rows collection to retrieve the row you're after, and then use the default indexer of the DataRow, supplying the name of the field to return the value. The returned value can be any object, so you'll need to make sure it's a string, because that's what you would like to display. If you run your Windows Forms application, you'll see that the name of the first customer is displayed.

Now that you've got a good handle on how to pull data from a table in a database and how simple it is, let's see how you can take advantage of data binding in the Windows Forms application.