Category list

Applied ADO.NET: Building Data-Driven Solutions - page 41

Summary

This chapter introduced the basics of ADO.NET and its components . You learned why ADO.NET is a much better data access technology than previous technologies. ADO.NET provides data providers such as OleDb, Sql, and Odbc to work with OLE-DB, SQL Server, and ODBC data sources, respectively. After that, you learned about various ADO.NET objects such as Connection , Command , DataAdapter , DataReader , DataSet , and DataView as well as the relationships between them. For example, DataSet is a collection of DataTable objects. A DataTable can attach to a DataView , which can later be bound with data-bound controls. Further, using a DataView 's filter and sort properties, you can provide different views of a DataSet and a DataView .

At the end of this chapter, you learned how to write a simple application to read data from Microsoft Access and SQL Server databases using ADO.NET.

Chapter 2 is heavily based on the VS .NET IDE. It covers how to take advantage of VS .NET IDE wizards and utilities to develop database applications without writing much code. You'll probably find this chapter exciting because you'll learn to build fully functioning database applications by simply writing a couple of lines of code.

Chapter 2: Data Components in Visual Studio .NET

In the previous chapter, you saw the basics of the ADO.NET model and its components and how they are connected to each other in the ADO.NET architecture. The Visual Studio .NET (VS .NET) Integrated Development Environment (IDE) provides design-time support to work with data components. In this chapter, you'll learn how to use these data components in the VS .NET IDE at design-time to create database applications. Using ADO.NET components (also referred to as data components ) is similar to using any Windows control. You just drag the component from the Toolbox to a form, set its properties and methods , and you're up and running.

We start this chapter with the Server Explorer, a useful tool for creating database applications. We focus on developing database applications quickly using data components in VS .NET without writing a lot of code. We also provide a step-by-step tutorial to help you develop and run a project. After that, we discuss the Connection , DataAdapter , Command , DataSet , and DataView components in more detail. After finishing this chapter, you'll have a good understanding of data components and how to work with them in VS .NET.

Creating an ADO.NET Project

Begin your project by launching the VS .NET IDE and choosing New Ø Project from the Project menu. Choose Visual Basic Projects from Project Types and then pick the Windows Application template. If you like, type an appropriate name into the Name field for your first ADO.NET application and click OK. Our project name is MyFirstADONetApp.

Using the Server Explorer

The Server Explorer is new to VS .NET; it enables you to manage your database servers and connections. You can use the Server Explorer to add a new server or a data connection to your list. The Server Explorer allows you to view, add, edit, and delete data in a tabular grid. The Server Explorer also allows you to create and delete database objects such as tables, stored procedures, views, and triggers.

Note

If you've ever used Open Database Connectivity (ODBC) data source names (DSNs) in your applications, then you might be familiar with the traditional Windows ODBC Administration. With ODBC Administration, you created DSNs using ODBC drivers for a data source and then connected your application using this DSN. Now, you'll hardly use ODBC Administration at all because the Server Explorer provides connection management services.

OK, let's see how to use the Server Explorer. You can open the Server Explorer by clicking the View Ø Server Explorer menu item.

Tip

The use of the Server Explorer isn't limited to SQL Server databases. You can add any type of database, including MySQL and Access.

When you open the Server Explorer, you'll see two root nodes: Data Connections and Servers. Initially, the Data Connections node has no items listed under it, but the Servers node displays all the available servers. By expanding the Servers node, you can see all the servers and services running on your machine. The SQL Servers node of the Server Explorer lists all the available SQL Servers running on that machine.

If you don't have any servers listed in your Servers list, you can right-click the Servers node and select the Add Server menu option. This option allows you to add new servers to the list. You can also unregister a server by right-clicking it and selecting the "Unregister Server" menu option.

You can also manage services running on your server. If you expand the Services node, you'll see a list of services running on your system. By right-clicking a service, you can even start and stop a service using the Server Explorer.

To view and managed database objects such as tables, views, or stored procedures, you can simply expand the database server name listed under the Data Connections node. After that you can expand Tables, Views, and Stored Procedures nodes. To view, delete, and update a database table, you right-click the table and use the available options.

Adding a New Connection

Adding a new connection is the next step after adding a server (if you're using a server) to the Server Explorer. You add a new connection to your list by right-clicking the Data Connections node and choosing the Add Connection option. This brings up a Data Link Properties Wizard. By default, the Connection tab is active, but you may want to click the Provider tab. The first tab of this wizard, Provider, displays all the data source providers installed on your machine; this is where you select your database provider. The list could contain any OLE-DB provider, Jet OLE-DB, or other data driver available on your computer.

The second tab of this wizard, Connection, lets you pick your server and corresponding data source. The drop-down list displays all the available servers. Our server is a SQL Server with the default name localhost. After selecting a server, the Database drop-down list displays all the available databases on the server. Select the Northwind database in this example. You can also pick what security mode you want to use for your connection on this tab. You can use the user ID and password of a SQL Server or use the Windows NT Integrated security option. By clicking the Test Connection button, you can make sure your database connection is working. If you've provided an incorrect user ID or password, the test will throw an error.

The third tab, Advanced, is for setting connection timeout and access permissions. You can give this connection read, write, or other permissions using the Advanced tab. The last tab, All, shows you all properties of the connection in a list, such as server name, connection timeout, data source, initial catalog (database name), security, and other network options. You can also edit this data from this tab by just double-clicking a particular property and editing the value of it.

After adding a data connection, you'll see that the wizard adds a node ( Servername.DatabaseName.dbo ) to the Data Connections root node. If you expand this node, you can see the database objects such as tables, views, stored procedures, and functions.

Managing and Viewing Data

As mentioned earlier, the Server Explorer not only lets you add server and database connections, but it also lets you manage and view data. You can add, update, and delete data from a database. Of course, you must have read and write permissions on the server to read and write data. The Server Explorer also provides options for creating new databases and objects, including tables, views, stored procedures, and so on. You can do this from both root nodes (Data Connections or Servers).

The Server Explorer shows database objects in a tree structure. Each database is a tree node of the server. As you expand the Northwind database node, for example, you can see its children listed as tables, stored procedures, and views.

If you expand this connection by double-clicking it, you'll notice it shows tables, views, and stored procedures. You can further expand these items to see them in more detail.

Note

We discuss stored procedures and views in more detail in Chapter 11.

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

Besides showing a list of database objects such as tables, views, stored procedures, and functions, the Server Explorer also lets you retrieve, add, edit, and delete data from a data source. You can always double-click to view the data of a table. Alternatively, right-clicking a database object (such as a table) provides you menu options based on the selection. For example, you can right-click and then select the Retrieve Data from Table menu option to retrieve data from that table.

Now, if you retrieve data from the Employees table by double-clicking or using the Retrieve Data from Table option, data will be displayed in an editable grid. You can edit this data at any time—again, assuming you have the correct permissions. For example, to delete a row or a collection of rows, select the rows and hit Delete or right-click the selected rows and then hit the Delete option. The right-click option of the grid also provides you options to move to the grid's first, next, previous, and last records.

Flylib.com