Filling a DataSet with the DataAdapter

for RuBoard

Filling a DataSet with the DataAdapter

The Command object executes a query against a database. Alone, it can't place the results into a DataSet . This is where the DataAdapter object comes into the picture. One of the DataAdapter 's jobs is to fill a DataSet with the results of a query. You can learn more about the DataAdapter in Hour 8, "Using the DataReader and DataAdapter."

The DataAdapter object is instantiated in a very similar fashion to the Command object:

 Dim myAdapter as New SqlDataAdapter( myCommand ) 

or in C#:

 SqlDataAdapter myAdapter = new SqlDataAdapter( myCommand ); 

Notice that you pass the existing Command object you're working with to the new DataAdapter object you're creating. This tells the DataAdapter which Command object will be used to query the database.

DataAdapter Fill() Method

As previously stated, the Fill() method of the DataAdapter takes the results of a database query and pushes them into a DataSet . Therefore, before you call the Fill() method, you must create a new DataSet to hold the results of the query. Additionally, you must open your connection to the database before calling the Fill() method. Listing 6.3 shows the complete code required to connect to the database and retrieve the results of the query into a DataSet .

Listing 6.3 Retrieving a DataSet Using the DataAdapters Fill Method
 SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;                                     Server=(local);UID=sa;PWD=;"); SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", conn); SqlDataAdapter adapt = new SqlDataAdapter(cmd); DataSet dsEmployees = new DataSet(); conn.Open(); adapt.Fill(dsEmployees,  "Employees"); conn.Close(); 
graphics/pencil.gif

The Fill() method of the DataAdapter object cannot be found in the class browser under either the SqlDataAdapter or OleDbDataAdapter entries. That is because it is defined in the System.Data.Common.DbDataAdapter class instead. You can find all of the overloaded Fill() method definitions there.


You have several options to verify that the code in Listing 6.3 is actually retrieving data. You could create a Windows form application that opens the Employee DataTable in the dsEmployees DataSet and loops through the rows of data and outputs the values of specific columns . However, the easiest way to test this code is to use a Web form, as you did in Hour 2, "Working with DataSets and DataTables."

Place the code in Listing 6.3 into a Web form named hour6.aspx using the techniques at the end of Hour 2. Then, to view the Web form, you can navigate your browser to http://localhost/ADO24Hours/hour6.aspx, if you followed the directions in Hour 2.

Listing 6.4 Viewing the Contents of a DataSet in C#
 <% @Page Language="C#" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>     <LINK rel="stylesheet" type="text/css" href="Main.css">     <!-- End Style Sheet -->     <script language="C#" runat="server" >       void Page_Load(Object Source, EventArgs E)       {          SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;                                              Server=(local);UID=sa;PWD=;");          SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", conn);          SqlDataAdapter adapt = new SqlDataAdapter(cmd);          DataSet dsEmployees = new DataSet();          conn.Open();          adapt.Fill(dsEmployees, "Employees");          conn.Close();          employees.DataSource = dsEmployees;          employees.DataBind();       }     </script> </HEAD> <BODY> <h1>Creating a DataSet</h1> <hr> <form runat="server" id=form1 name=form1>    <asp:DataGrid id="employees" runat="server"></asp:DataGrid> </form> <hr> </BODY> </HTML> 
Listing 6.5 Viewing the Contents of a DataSet in VB.NET
 <% @Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <HEAD>       <LINK rel="stylesheet" type="text/css" href="Main.css">       <!-- End Style Sheet -->       <script language="VB" runat="server" >       Sub Page_Load(Source as Object, E as EventArgs)          Dim conn as New SqlConnection("Initial Catalog=Northwind;                                         Server=(local);UID=sa;PWD=;")          Dim cmd as New SqlCommand("SELECT * FROM Employees", conn)          Dim adapt as New SqlDataAdapter(cmd)          Dim dsEmployees as New DataSet()          conn.Open()          adapt.Fill(dsEmployees, "Employees")          conn.Close()          employees.DataSource = dsEmployees          employees.DataBind()       End Sub       </script> </HEAD> <BODY> <h1>Creating a DataSet</h1> <hr> <form runat="server" id=form1 name=form1>    <asp:DataGrid id="employees" runat="server"></asp:DataGrid> </form> <hr> </BODY> </HTML> 
graphics/pencil.gif

The C# and VB.NET code in Listings 6.3 and 6.4 are very similar. In fact, minor language-specific nuances of syntax aside, the code is identical and performs exactly the same.


The Web form in Listing 6.4 uses a Web control known as the DataGrid to display the DataSet . The DataGrid object is instantiated in a tag inside the server-side form toward the end of the listing. The DataGrid generates an HTML table built from the data sent to it.

Your ADO.NET code is inserted into the Page_Load method, which runs every time the Web page is loaded. In order to "wire up" the data to the DataGrid , the DataSource property of the DataGrid is set to dsEmployees. The last step required to display the DataSet on the page is to call the DataBind() method of the DataGrid control. After the Web form in Listing 6.4 is loaded, you'll see results very similar to those in Figure 6.2.

Figure 6.2. Binding the results of a database query to a DataGrid Web control.

graphics/06fig02.jpg

The unformatted DataGrid results aren't very pretty. However, it is very easy to configure the DataGrid control to display data in a format more pleasing to the eye. For more information on formatting the DataGrid control, see Hour 12, "Formatting ASP.NET List Controls."

for RuBoard


Sams Teach Yourself ADO. NET in 24 Hours
Sams Teach Yourself ADO.NET in 24 Hours
ISBN: 0672323834
EAN: 2147483647
Year: 2002
Pages: 237

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net