Expertrating - ASP.Net Tutorial, Working with DataSets

Home > Courses, Tutorials & eBooks > ASP.NET Tutorial > Working with DataSets

ASP.NET Tutorial - Working with DataSets

Page 2 of 5

Adding DataTable to a DataSet

The contents of existing database can be added to the tables of a DataSet by using the DataAdapter. To add a database table to a DataSet, use the following steps:

Create a DataSet.
Create a database connection.
Create a DataAdapter with a SQL Select statement and the database connection.
Call the Fill method of the Da±aAdapter, passing the name of the DataSet and the name of the new DataTable.

The following example, illustrates how the Employees can be added from the table from the Northwind database to a DataSet.

Example 51 ExpertSqlDataAdapter.aspx

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

Dim dstEmployees As DataSet

Dim conNorthwind As SqlConnection

Dim dadEmployees As SqlDataAdapter

dstEmployees = New DataSet()

conNorthwind = New SqlConnection( "Server=localhost;Database=Northwind;UID=sa;PWD=secret" )

dadEmployees = New SqlDataAdapter( "Select * From Employees", conNorthwind )

dadEmployees.Fill( dstEmployees, "Employees" )

<b>Employees table has been added to DataSet dstEmployees!</b>

The output of above example is shown below:

In the above example when the Fill method is called , a new DataTable is added to the DataSet, named Employees. Therefore the connection to a database is never open. The Fill method by itself opens a database connection and then automatically closes the connection that it opens.

The page in above example works only with a Microsoft SQL Server (version 7.0 and higher) database. It uses the SqlConnection and SqlDataAdapter classes from the System. Data. SqlClient namespace. If another type of database, is to be used such as Microsoft Access or Oracle, the classes must be used from the System. Data. OleDbnamespace.

The following example illustrates how a DataTable can be added from a Microsoft Access database to a DataSet.

Example 51 ExpertOledbDataAdapter.aspx

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

Dim dstEmployees As DataSet

Dim conEmployees As OleDbConnection

Dim dadEmployees As OleDbDataAdapter

dstEmployees = New DataSet()

conEmployees=New OleDbConnection( "PROVIDER= Microsoft. Jet.OLEDB.4.0;DATA Source=c:\Employees.mdb" )

dadEmployees = New OleDbDataAdapter( "Select * From Employees", conEmployees )

dadEmployees.Fill( dstEmployees, "Employees" )

<b>Employees table has been added to DataSet dstEmployees! </b>

The output of above example is shown below:

In this example the classes from the System.Data.OleDb namespace are used. Instances of the oleDbConnection and OleDbAdapter classes are created.

ExpertRating ASP.NET Certification - Get Certified Now!!