This chapter introduced the basics of ADO.NET and its
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.
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
) is similar to using any Windows control. You just drag the component from the Toolbox to a form, set its properties and
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.
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
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.
If you've ever used
OK, let's see how to use the Server Explorer. You can open the Server Explorer by clicking the View Ø Server Explorer menu item.
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
Adding a new connection is the
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
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.
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.
We discuss stored procedures and views in more detail in Chapter 11.
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,