Using Visual Data Components

At this time, ADO.NET has three base data providers and multiple add-on data providers to work with various kinds of data sources, including SQL Server, OLEDB data sources, and ODBC data sources. What do we mean by base data providers? Well, initially Microsoft released only three data providers; now, based on additional requirements, Microsoft and third parties are adding new data providers to the library. For example, Microsoft, Oracle, and Core Lab Software Development provide three different data providers for the Oracle database. (We discuss these new data providers in later chapters.) The main class hierarchy model of these data providers remains the same, so programmers will have no problem switching between data providers. Some of these data providers are OleDb, Sql, Odbc, and Oracle. In this chapter, we concentrate on the OleDb and Sql data providers. Later chapters discuss the ODBC, Oracle, and other data providers.


You can even write your own custom data provider. ADO.NET provides a set of interfaces, which you must implement to write your own data provider. Chapter 13 of this book covers writing custom data providers.

You can use the data provider components at design-time in VS .NET. The data provider components are available at design-time via the Toolbox. The Toolbox's Data tab shows you the available data controls in VS .NET. Some of the components you may see are DataSet, DataView, SqlConnection, SqlCommand, SqlDataAdapter, OleDbConnection, OleDbCommand, OleDbDataAdapter, OdbcCommand, OdbcCommandBuilder, OdbcConnection, and OdbcDataAdapter. These components are available only when you're in a design view.


You can open Toolbox by using the View Toolbox menu item.

As mentioned briefly in Chapter 1, the .NET Framework Library contains many ADO.NET data providers, including OleDb, Sql, and Odbc. The OleDb data provider wraps up the native OLE-DB COM Application Programming Interface (API) for working with OLE-DB data sources. To access an OLE-DB data source, you need to install an OLE-DB data provider for that database. Sql data providers work with SQL Server 7 or later databases. Odbc data providers wrap up the ODBC API to work with ODBC data sources (with the help of the ODBC Administration and ODBC drivers). Chapter 4 discusses these data providers in more detail. Microsoft and other vendors might introduce more data providers, which you can add to the library later.

In the .NET Framework, each of these data providers has its own namespaces. For instance, the System.Data.OleDb namespace consists of classes belonging to the OleDb data providers. All of these namespace classes start with OleDb. The Microsoft.Data.ODBC and System.Data.SqlClient namespaces consist of classes belonging to the Odbc and Sql data providers, respectively. Similar to OleDb, classes in Odbc start with Odbc, and classes in SqlClient start with Sql.

In VS .NET, some of these classes (or objects) are available from the Toolbox; you can drag them onto a form like any other Windows control in the Toolbox. These controls are called data components.

VS .NET also provides a set of data-bound controls. The DataGrid, ListBox, and DataList are good examples of some of these data-bound controls. It's fairly easy to work with these controls. You just set a few properties, and they're ready to display your data. For example, setting a DataGrid control's DataSource property displays data from a DataSet object. We use these data-bound controls in the examples throughout this book. In the next section, we discuss how you can add these components to your Windows Forms applications and set their properties and methods at design-time with the help of the .NET wizards.

Understanding Data Connections

To connect to a data source, the first thing you need to learn about is a data connection.

Each data provider has a Connection class, and if you're using the VS .NET IDE, you can see these class objects as components in the Data tab of the Toolbox. For example, the SqlConnection, OdbcConnection, and OleDbConnection class objects represent a connection for the Sql, Odbc, and OleDb data providers, respectively:

  • SqlConnection creates and manages SQL Server database connections.

  • OdbcConnection creates and manages connections to ODBC data sources.

  • OleDbConnection creates and manages connections to OLE-DB data sources.

In the VS .NET IDE, you can create a Connection in many ways. You can use the IDE to add a Connection object to a project, create it programmatically, or use DataAdapters that automatically create a Connection object for you. In this chapter, we concentrate on adding a connection through the VS .NET IDE.

start sidebar
IDE vs. Manual Coding

In some cases, you may not know if you should use the IDE or write code manually. Actually, creating a connection and other objects such as a Command manually is usually much better. Those types of objects only require a few lines of code, and creating them manually allows you to know where the code is and what it means. If you use the VS .NET IDE, it adds a connection and other data components but sometimes also writes lots of code that is difficult to understand. For example, adding a DataAdapter adds a great deal of code that you may not need. If you want to modify it, you'll almost certainly get lost if you don't have a lot of experience.

Now, your next question probably is, "Why do I need to use the IDE at all?" Well, the IDE may help you if you don't know how to create connection strings and other objects because it writes code for you. Basically, the IDE is useful for part-time ADO.NET developers—those who write non-database-related code but occasionally use ADO.NET. It's also useful for beginners, who want to have their application ready and running in no time. But after completing this book, I bet you'll hate using IDE.

end sidebar

The easiest way to add a connection to a project in VS .NET is to drag a connection component (SqlConnection, OleDbConnection, or OdbcConnection) from the Toolbox's Data tab to a form. After that, you can set the connection's properties using the Properties window. For this demonstration, drop a SqlConnection from the Toolbox onto the form. You can set the SqlConnection properties from the Properties window by right-clicking the SqlConnection and selecting the Properties menu item.

Note that the default connection name is the class name with a unique name appended to it. Because this is the first connection, the name is sqlConnection1.

As you can see in the Properties window, a connection's properties include Database, ConnectionTimeout, DataSource, PacketSize, WorkstationId, Name, and ConnectionString.


The connection properties depend on the data provider. Some properties may not be available for some data providers. For example, the WorkstationId property is available in Sql data providers but not in OleDb or ODBC data providers.

Understanding Connection Strings

The ConnectionString property is the main property of a connection. By clicking the drop-down list of the ConnectionString property, you can see all the available data connections. If you don't have a data connection, you can use its New Connection option, which launches the Data Link Properties Wizard. (Refer to the earlier "Using the Server Explorer" section for more information.)

For this example, we use the database server name MCB instead of localhost. The SQLConnection string for server MCB looks like following:

 data source=MCB;initial catalog=Northwind;integrated security=SSPI;" & _ "persist security info=False;workstation id=MCB;packet size=4096 


In Chapter 4, we discuss a connection and its properties in more detail and show how to set them programmatically. You can also use a user ID and password in a connection string.

Working with SQL DataAdapters

A DataAdapter is another important component of a data provider. Similar to a connection, each data provider has a corresponding DataAdapter class. All DataAdapters in ADO.NET work in same way, which means if you know how to work with Sql DataAdapters, you can use OleDb, ODBC, and other DataAdapters easily. The SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdaper classes represent DataAdapter components in Sql, OleDb, and Odbc data providers, respectively.

Besides creating a DataAdapter programmatically (see Chapter 4 for more details), VS .NET provides various ways to create DataAdapters. Two common ways are by using the Server Explorer and by using the Data Adapter Configuration Wizard.

Creating DataAdapters with the Server Explorer

It's easy to create a DataAdapter using the Server Explorer. You just drag and drop database objects to a form and the IDE takes care of everything for you. The IDE writes code that you can use programmatically or bind data controls at design-time. To add a new connection to a project, expand your database in the Server Explorer, and drag a table from the Server Explorer to your form. For this example, expand the Northwind database in the Server Explorer and drop the Employees table to the form.

This action adds a SqlConnection and SqlDataAdapter object to the project. You can even drag selected columns or stored procedures onto the form. The VS .NET IDE takes care of the rest. Right-click the form and choose View Code to examine the code generated by the wizard; in this example, you'll see one SqlConnection component and one SqlDataAdapter component along with a set of SqlCommand components:

 Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection   Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter 

You'll also see that the IDE writes SQL statements and sets the CommandText property of SqlCommand objects. Once you have a DataAdapter, you can use it to populate DataSets and work with its properties. (We discuss DataSet basics and how to construct them manually in Chapter 4.) With the VS .NET IDE, you can even generate DataSets using the visual representation of the DataAdapter. (We discuss how to populate a DataSet using VS .NET IDE wizards in the "Generating Typed DataSets Using a DataAdapter" section of this chapter.)

Creating DataAdapters with the Data Adapter Configuration Wizard

The Data Adapter Configuration Wizard is a powerful tool to develop database applications. To see how you can create DataAdapters using this wizard, you'll create a new Windows Forms-based project in the following step-by-step sections.

In this first project, you'll learn how to create SQL DataAdapters, read data from a SQL Server data source, and display the data from a DataAdapter to a DataGrid control. After completing these steps, you'll see how easy it is to develop database applications using the Data Adapter Configuration Wizard.

Step 1: Selecting a Project Template

To create a new Windows application using VS .NET, select File New Project Visual Basic Projects Windows Application. Next, select an appropriate directory and type your project name.

Step 2: Adding a DataGrid Control to the Form

Now add a DataGrid control to the form. You do this by just dragging a DataGrid control from the Toolbox's Windows Forms category to the form and resizing it.

Step 3: Adding a DataAdapter Component

Next, drag a SqlDataAdapter control from the Toolbox's Data category to the form. As you drop the SqlDataAdapter the Data Adapter Configuration Wizard pops up. The following sections walk you through this wizard.

Welcome Page

The first page of this wizard is just a welcome screen. Click the Next button to move to the next screen.

Choose Your Data Connection Page

The second page of the wizard lets you create a new connection or pick from a list of available connections on your machine. All available connections are listed in the drop-down list. If you don't have any connections listed in the list, you can click the New Connection button, which launches the Data Link Properties Wizard (discussed previously in the "Adding a New Connection" section).

Choose a Query Type

The next page of the wizard is for selecting a SQL query type. If you see this page, three options are available for the SQL Server connection:

  • The Use SQL Statement option allows you to create a new SQL query.

  • The Create New Stored Procedure option allows you to create a new stored procedure.

  • The Use Existing Stored Procedure option allows you to select a stored procedure from the existing stored procedures.


The last two options aren't available for databases that don't support stored procedures.

Generate the SQL Statement

The next page of the Data Adapter Configuration Wizard lets you build a SQL statement or a stored procedure. You can type a SQL statement directly into the text box, or you can click the Query Builder button to use the Query Builder to build your SQL statement.

Query Builder

The Query Builder option lets you pick tables from your data source. First, select the Employees table to read in the Employee data. You actually have the option of selecting as many as tables you want, but for now select only one table and click the Add button. After clicking the Add button, the Query Builder provides you with an option to select columns of the table. You can check *(All Columns) to select all the columns of a table, or you can check only the columns you want to participate in the SQL statement.

If you've ever used the New Query Wizard in Microsoft Access, you'll find that the Query Builder is similar to it. In Access, you can create queries by dragging tables and their columns to the grid (or checking the columns), and the Query Builder builds a SQL query for your action.

In this sample, select EmployeeID, FirstName, and LastName from the Employees table to build your SQL statements. The SQL statement after selecting these three columns looks like this:

 SELECT EmployeeID, LastName, FirstName FROM Employees 

You can even type this SQL statement directly into the text box.


You can even write your own SQL statement if you don't want to use the Query Builder. For performance reasons, if you only want few columns, then use column names instead of using SELECT * statements.

One more button is available on this page: Advanced Options. By clicking the Advanced Options button, you launch the Advanced SQL Generation Options page. On this page, there are three options: Generate Insert, Update, and Delete Statements; Use Optimistic Concurrency; and Refresh the DataSet. By default all three options are checked. If you don't want to generate INSERT, UPDATE, or DELETE SQL statements, you should uncheck the first check box.

By default, ADO.NET uses optimistic concurrency. If you don't want your program to implement optimistic concurrency, you should uncheck the second check box.

View Wizard Results

The View Wizard Results page shows you the action being taken by the wizard. If everything went well, the wizard generates a message saying, "The data adapter was configured successfully." For this example, the Details section shows that the wizard has generated SQL SELECT, INSERT, UPDATE, and DELETE statements and mappings.

Now you can click the Finish button to complete the process.

Currently, if you examine the design surface at the bottom of the Form window, you'll see two components: sqlConnection1 and sqlDataAdapter1. The wizard sets the properties of these components for you. Now you can use the DataAdapter to populate your DataSets. Don't forget to resize the DataGrid you added to the project.

Step 4: Setting and Reviewing DataAdapter Properties

Now that you have a DataAdapter on your form, let's look at the SqlDataAdapter component properties. You can see its properties by right-clicking the adapter and selecting the Properties menu item. The wizard also shows the available command properties, including InsertCommand, DeleteCommand, SelectCommand, and UpdateCommand.

You can set DataAdapter properties by clicking these properties. . A DataAdapter has four SqlCommand properties—SelectCommand, DeleteCommand, InsertCommand, and UpdateCommand—that all execute SQL commands on the data source. For example, if you look at the SelectCommand property, you'll see the SQL SELECT statement.


Chapter 4 covers SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand in more detail.

From the Properties window, you can also set CommandText, CommandType, Connection, and other properties. If you double-click CommandText, it pops up the Query Builder where you can rebuild your query.

The TableMapping class represents a mapping of DataColumns in the data source to DataColumns in the DataSet. We discuss DataTables and table mappings in more detail in Chapters 3 and 4. If you click the TableMappings property (which is a collection of TableMapping objects), it brings up the Table Mappings dialog box.

start sidebar
Question and Answer


Why do I need table mappings? I've also heard of column mapping. What is it?

sometimes, you won't want to display your actual table names and their columns as defined in a database. for instance, say you have a mytable table with two fields, a0 and a1. the table stores a customer's information, and the fields a0 and a1 store customer names and addresses. if you display your table in data components such as a datagrid , it will display the table name as mytable and the column names as a0 and a1, which makes no sense to user. using table mappings, you can change the table name to customers table. using column mapping, you can change the columns' names to customer name and customer address. after this mapping, datagrid will display the table name as customers table and the columns as customer name and customer address, which make more sense to a user. we discuss table and column mappings in chapter 3 in more detail.



Sometimes, you won't want to display your actual table names and their columns as defined in a database. For instance, say you have a MyTable table with two fields, a0 and a1. The table stores a customer's information, and the fields a0 and a1 store customer names and addresses. If you display your table in data components such as a DataGrid, it will display the table name as MyTable and the column names as a0 and a1, which makes no sense to user.

Using table mappings, you can change the table name to Customers Table. Using column mapping, you can change the columns' names to Customer Name and Customer Address. After this mapping, DataGrid will display the table name as Customers Table and the columns as Customer Name and Customer Address, which make more sense to a user. We discuss table and column mappings in Chapter 3 in more detail.

end sidebar

If you click the TableMappings property, it opens the Table Mappings dialog box. The Table Mappings dialog box has two columns: Source Table and Dataset Table (see Figure 2-1). If you have more than one table in a DataAdapter, you'll see all the tables listed in the Source Table list box. By default the source table Table is listed in the list box. The Source Table column is a list of actual columns, and the Dataset Table column is a list of the column names used in the DataSet. By default, DataSet columns names are the same as the source table. This is useful when you want to use different names in a program. You can change DataSet columns by editing the column itself. Of course, you can't change source columns, but you can reorder them by using the Column Mappings drop-down list.

click to expand
Figure 2-1: Table Mappings dialog box

By using this dialog box, you can even delete columns from your mapping using the Delete button.

Step 5: Reviewing Other Options

If you look closely at the DataAdapter's Properties window, you'll see three links: Configure Data Adapter, Generate Dataset, and Preview Data.

The Configure Data Adapter option calls the Data Adapter Configuration Wizard, discussed earlier in this chapter. If you want to reset the wizard to change your options, you can use this link.

The Generate Dataset option lets you generate a DataSet for this DataAdapter. The Generate DataSet option provides you with options that allow you to select whether you want to use an existing DataSet or create a new DataSet. The Existing option is available only when a typed DataSet is available in your project.

Create a new DataSet with the default name DataSet1. (You can change the DataSet name if you want.) Clicking the OK button adds a typed DataSet (DataSet1.xsd) to the project.

start sidebar
Question and Answer


What's a typed DataSet? Are there other types of DataSets?

there are two types of dataset s: typed and untyped. a typed dataset is represented by an extensible markup language (xml) schema. see the ` typed and untyped datasets ` section of chapter 3 for more information.



There are two types of DataSets: typed and untyped. A typed DataSet is represented by an Extensible Markup Language (XML) schema. See the "Typed and Untyped DataSets" section of Chapter 3 for more information.

end sidebar

Actually, the Generate DataSet option adds a class DataSet1 to your project, which is inherited from DataSet. If you go to the Class View, you can see the DataSet1 class and its members.

The Preview Data option enables you to view the DataSet schema. You can even preview the data in the DataSet by pressing the Fill button, which opens the Data Adapter Preview dialog box (see Figure 2-2).

click to expand
Figure 2-2: Previewing data for the DataAdapter

The Fill Dataset button in Figure 2-2 fills data into a grid based upon the current state of the SelectCommand in the DataAdapter. The Parameters grid displays available parameters if a DataAdapter has any. The Clear Results button clears the records.

Step 6: Reviewing the Source Code

Now it's time to examine the code and see what the wizard has done for you automatically. You can see the source code by right-clicking the form and selecting the View Source option.


You should take the time to look at the code so you know what goes under the hood when you use the IDE to write your code. It'll help you in the long run.

All source code generated by the Windows form designer is defined in the InitializeComponent method of the file. Right-click your form and choose View Code. Upon examining the source code, you'll see where the wizard has added two components, sqlConnection1 and sqlDataAdapter1, to your source file as well as four SqlCommand components. Scroll down to the Windows Designer Generated Code option and expand it. This reveals the contents of the InitializeComponent routine (see Listing 2-1).

Listing 2-1: Added Sql Server Provider Components

start example
 Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid   Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter   Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand   Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection   Friend WithEvents DataSet11 As MyFirstADONetApp.DataSet1   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()     Me.DataGrid1 = New System.Windows.Forms.DataGrid()     Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()     Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()     Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()     Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand()     Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection() 
end example

Search for the ConnectionString by hitting Ctrl+F to bring up VS .NET's Find dialog box. If you examine the InitializeComponent() method, you'll see that the wizard sets SqlConnection's ConnectionString property to the following:

 Me.SqlConnection1.ConnectionString = _ "data source=localhost;initial catalog=Northwind;" & _ "integrated security=SSPI;persist " & _ "security info=False;workstation id=MCB;packet size=4096" 

It also sets the CommandText property of the SqlCommand with the corresponding SELECT, INSERT, UPDATE, and DELETE SQL statements. The Connection property of SqlCommand is set to SqlConnection:

 Me.SqlSelectCommand1.CommandText = "SELECT EmployeeID, LastName, " & _     "FirstName FROM Employees"     Me.SqlSelectCommand1.Connection = Me.SqlConnection1 

If you examine Listing 2-2, you'll see that DataAdapter is connected to a Connection through data commands, and the TableMapping property is responsible for mapping tables and their columns. Note that the table mappings between DataSet columns and DataSource columns generated by the wizard have exactly the same column names.

Listing 2-2: DataAdapter Connection through TableMapping

start example
   Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1     Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1     Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1     Me.SqlDataAdapter1.TableMappings.AddRange(_     New System.Data.Common.DataTableMapping() { _     New System.Data.Common.DataTableMapping("Table", "Employees", _     New System.Data.Common.DataColumnMapping() { _     New System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"), _     New System.Data.Common.DataColumnMapping("LastName", "LastName"), _     New System.Data.Common.DataColumnMapping("FirstName", "FirstName")})})     Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1 
end example

It looks like the wizard did a lot of the work for you!

Step 7: Filling the DataGrid Control with Data

Until now, you haven't had to write a single line of code. Now, though, you'll add few lines of code and then you'll be all set to see the data from your data source. Now you have two options. First, you can use the typed DataSet to read the data, and second, you can create a DataSet object programmatically and fill it using the SqlDataAdapter's Fill method.

You'll create a DataSet object programmatically and use it to fill data in a DataGrid. Add the code in Listing 2-3 to the form load event handler. As you can see, this code creates a DataSet object and fills it by calling SqlDataAdapter's Fill method. The Fill method of SqlDataAdapter fills data from a DataAdapter to the DataSet. You call the Fill method in the FillDBGrid method. Once you have a DataSet containing data, you can do anything with it, including creating views for that data. In this example, you set a DataGrid control's DataSource property to the DataSet.DefaultViewManager, which binds the DataSet object to the DataGrid control.

Listing 2-3: Form's Load Event Handler

start example
 Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load     Dim ds As DataSet = New DataSet()     SqlDataAdapter1.Fill(ds)     DataGrid1.DataSource = ds.DefaultViewManager   End Sub 
end example

Another option is to use the typed DataSet you generated using the Generate DataSet option. The wizard added an instance of DataSet1 called DataSet11 to the project. Now just call the Fill method of DataAdapter and bind the DataSet's default view to the DataGrid. Just write the following two lines on the form load event handler:

 SqlDataAdapter1.Fill(DataSet11) DataGrid1.DataSource = DataSet11.DefaultViewManager 

Finally, build and run the project using either of the two methods. The result looks like Figure 2-3. Easy, huh?

click to expand
Figure 2-3: Output of the Employee data to a DataGrid control

Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214 © 2008-2017.
If you may any questions please contact us: