Dotnet Turkey

CustonnerlD

Company N a

Address

City

Region

PostalCode

►

ALFKI

Alfreds Futter

Obere Str. 57

Berlin

(null)

12209

ANATR

Ana Trujillo E

Avda. de la C

México D.F.

(null)

05021

ANTON

Antonio More

Mataderos 2

México D.F.

(null)

05023

ARGUT

Around the H

120 Hanover

London

(null)

WA1 1DP

BERGS

Berglunds sn

Berguvsváge

Luleá

(null)

S-958 22

BLAUS

Blauer See D

Forsterstr. 57

Mannheim

(null)

S830S

BLONP

Blondesddsl

24, place Klé

Strasbourg

(null)

S7000

BOLID

Bólido Comid

C/ AraquiL 67

Madrid

(null)

28023

BO NAP

Bon app'

12, rue des B

Marseille

(null)

A Brief Introduction to ADO.NET

Before we can begin creating our application we need to learn about some basic components of ADO.NET and wizards that our application is to use. We will go into greater detail on ADO.NET in the next chapter so, for now, we'll learn just enough to get us through this simple database project.

ADO.NET provides us with a way of gathering data and information and presenting it through a user interface. By using some components, we're able to connect to various data sources and can then build a user interface that accesses a database.

We need four pieces to build our ADO.NET project:

1. A data source - where the actual data is stored, our database.

2. A Connection object - for connecting us to our database.

3. A DataAdapter object - to provide a mechanism for reading and writing data to the database.

4. A DataSet object - this will contain the table(s) that we will use.

The following figure shows how all these pieces tie together. Firstly, we need a connection to the data source, provided by a Connection object. The Connection object requires certain information for it to connect to the data source. The Connection object is called by the DataAdapter object, which handles commands to select, update, insert, and delete data in the data source. Finally we have a DataSet that contains our tables and which uses the DataAdapter to populate itself and to update information in the data source.

Basic Data Components

We will now cover these components in a bit more detail, taking each of the data source, Connection, DataAdapter and DataSet in turn.

The Data Source

A data source is the term used to describe any collection of information that can provide data to us. It can take the form of a database, an XML document, a Microsoft Excel spreadsheet, or even a flat text or binary file. It only takes one or two lines of code for us to change the kind of data source that we connect to. The Windows environment provides us a shared set of classes for use in our programs to communicate with these different sources using similar code.

The Data Connection

The first thing we need to connect to a database is the data Connection object. This comes in two versions - either a SqlConnection or OleDbConnection object. As we are working with the SQL Server Desktop Engine, we will use the SqlConnection object.

When we create a connection using a SqlConnection object, we need to feed it the following connection parameters:

□ Data Source - the name of the server where your data source is located. The data source can be anywhere, be it on your network or somewhere over the Internet. Usually, you will be working on your local network and so you need to specify the name of the computer that holds the data source here. Alternatively, we can give the name localhost or (local) to signify that we want to use the computer that is actually running the application. This terminology is used by many Windows applications when it is necessary to identify the current, local computer.

□ User ID and Password - the authentication details required to communicate with the data source. The ID and password is set up by the database administrator and helps prevent people from viewing or modifying the database without permission.

□ Initial Catalog - this is the name of the database we want to work with - in this case, NorthwindSQL.

To create a new connection, we declare a new SqlConnection and set the ConnectionString property using these parameters as shown here:

Dim myConnection As New SqlClient.SqlConnection() myConnection.ConnectionString = "Data Source=localhost;" & _

"Initial Catalog=NorthwindSQL;User

Id=sa;Password=sa;"

Alternatively, we can pass the connection string as a parameter to the SqlConnection as follows:

Dim myConnection As New SqlClient.SqlConnection("Data Source=localhost;" & _

"Initial Catalog=NorthwindSQL;User

Id=sa;Password=sa;""

Creating a new OleDbConnection object is similar, except that we also need a Provider parameter to describe the type of data source that we are connecting to. So why don't we need that parameter with the SqlConnection object? You've got it - because the provider type will always be SQL and, in fact, if you do try to set the Provider parameter for an SqlConnection object, you will get an error.

Now we can look at the component that requires a data Connection object to be set up in order to function, namely the DataAdapter.

The DataAdapters

The DataAdapter is the mechanism that sits between the data source and the DataSet. We have two types of DataAdapters, the SqlDataAdapter, which is used exclusively for SQL Server databases, and the OleDbDataAdapter, which is used for all other data sources and goes through another layer called OLE DB. Consequently, by avoiding the need for this extra layer, the SqlDataAdapter provides much faster access to data. The OleDbDataAdapter can be used to access SQL Server but, as it then goes through the OLE DB layer, you are well advised to stick with the SqlDataAdapter for optimum performance if you don't anticipate using anything other than SQL Server. This applies to our simple database application in this chapter, and so we work strictly with SqlDataAdapter.

The DataAdapter allows selecting, updating, deleting, or inserting data in the data source. These methods are accomplished through the use of the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties to set to the database command string required for that particular operation. Each of these properties is an instance of a Command object, whose job it is to execute a SQL statement or stored procedure and return a result set. For a SQL database, the Command object will hold the actual SELECT, UPDATE, INSERT, and DELETE statement required for a given operation, such as "SELECT * FROM Products" or "DELETE FROM Orders WHERE CustomerID='ABC'". The Command object also stores connection information so it may connect to the database to execute the SQL statement that it contains. Note that, in our simplified case, we will only be working with a SelectCommand as we only need to select information from our database for viewing.

As we are dealing with a SQL database, we will be working with the SqlCommand object (as opposed to the OleDBCommand object). When we use the DataAdapter Wizards, for each table you work with you will have a corresponding DataAdapter. When we use the Wizards, the DataAdapters are configured specifically for the chosen table such that all of the methods for updating and retrieving information point to that specific table. To re-use the adapter for another table, we have to essentially rebuild the objects that make up the DataAdapter, which means all of the Command objects. The simpler solution is to assign one DataAdapter per table, and this helps keep your code nice and clean and easy to maintain. When you build a DataAdapter, you can specify more than one table if needed. For example, we could create a DataAdapter that links the Customers table and the Orders table - to enable us to view information from both tables using a single DataAdapter, without needing any code to link them. This method of linking multiple tables into a single view doesn't work really well when it comes to updating information, however, as the DataAdapter Wizard isn't able to properly link tables together to cascade updates or deletes, reinforcing the case for using one DataAdapter per table.

This diagram shows the basic structure of a DataAdapter:

The DataSet

Finally, a DataSet is a container or collection of tables; it can contain one or more tables and is maintained in memory. Relationships between tables are also stored here. The tables it holds contain information such as customer details or product information in the form of records, or rows. A table may consist of thousands of such rows.

**Please insert 5555_05_39.bmp

One useful illustration is to think of a DataSet as holding details of a book publisher. A technical publisher might publish books in several categories such as .NET, Java, ASP, and C++. Within each category are individual books - so that a .NET category could have books such as Professional VB.NET, Beginning VB.NET, Professional C#, and Introducing .NET. A table could represent each of these categories, and each book in a category would be represented by a row in the appropriate table. Each row holds details for each book - for example, title, price, ISBN number, publishing date, and the number of pages.

Each publisher can publish books for several categories

Wrox Press

.NET

Java

Publisher Book Catagories

Each category can contain several books

Book Details:

Title = Professional VB.NET Price = $59.99 ISBN = 1861004974 Published = August 2001 Pages = 950

There is no limit to the type of information you can store in a DataSet. Now that we have looked at the internals of a DataSet, let's take a look at how we can put one to use in an application. The components shown in the figure below will be demonstrated in our application:

Customer Table Components

For our program, we will need to create a SqlDataAdapter object to select customer records from the Northwind database via a SqlConnection object. This connection will be opened only long enough to complete the SQL SELECT operation. Our DataSet will be populated with data from our customer table using the SqlDataAdapter object. Linking, or data binding, to a visual component such as the DataGrid control will then display the DataSet's contents on a Windows Form.

Now we can begin implementing these components in our simple database application. Here's an overview of the tasks ahead:

□ Creating a Windows Application

□ Connecting to a data source

□ Adding a DataAdapter to our form

□ Generating a DataSet from the DataAdapter

□ Adding a DataGrid control to our form

□ Displaying the contents of a customer table in our DataGrid

We start by creating a new Visual Studio .NET application and then adding a SqlDataAdapter to the project. We'll also have to create a connection to the NorthwindSQL database for the DataAdapter. From the SqlDataAdapter we'll create a new DataSet. Once we have a DataSet, we will add a DataGrid to our form and bind the DataSet to it. Lastly, we'll add a button that fills the DataSet with customer records and displays it in the DataGrid.

Visual Studio .NET's configuration wizards provide us with an easy way of doing all this. We're just a point and click away from creating our database application!

Try It Out - Creating a DataAdapter

5. Create a new Visual Basic .NET Windows Application. You can create this project in any directory. Name the project CustomerApp and click OK.

6. A new form will automatically be generated called Forml. Add a SqlDataAdapter to the form by double-clicking the SqlDataAdapter component from the Data tab of the Toolbox, usually found to the left of the Visual Studio screen.

7. Click Next when the welcome page of the DataAdapter Configuration Wizard appears to bring up the window shown in the following screenshot, prompting for a connection to a database. If we had created other data connections already, they would be shown in the dropdown list. Since this is our first time connecting, however, it will be empty and we must create a new connection by clicking the New Connection... button.

8. Now we are presented with the Data Link Properties window. In the top drop-down list for selecting the server name, if you can find your computer's name there, then choose it -otherwise type in localhost or whatever is the server name where the SQL Server containing the NorthwindSQL database resides. In item two, you choose to either use your current Windows logon details to authenticate or to enter a different user name and password as used by SQL Server. You should try selecting the Windows NT Integrated security first as this often works; if it doesn't, then try a specific username and password as set by the database administrator. In this case we use a SQL Server ID of sa (for system administrator) with no password. In general, of course, you would not use a blank password because of security concerns. Item three requires you to choose an existing database on the server, so type in NorthwindSQL here. Click the Test Connection button to test your connection if you wish, although you can be fairly sure that the connection is valid if the correct list of databases appears in item three's drop-down.

S Data Link Properties

Provider Connection ] Advanced | All

Specify the following to connect to SQL Server data:

1. Select or enter a server name:

[iocaihost

2. Enter information to log on to the server:

Use Windows NT Integrated security (* Use a specific user name and password: User name:

Refresh

Password: [

I- Blank password I- Allow saving password 3. (* Select the database on the server: |NorthwindSQL|

Attach a database file as a database name:

Using the filename: