HOW TO: Populate a DataSet Object from a Database by Using Visual J# .NET (320625)

SUMMARY

DataSet objects, a key part of data access in the Microsoft .NET Framework, are in-memory objects that can hold tables, views, and relationships. This article shows how to fill a DataSet object with the results of one or more database queries, and how to access that data after it is loaded into the DataSet object.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or Microsoft Data Engine with the Pubs sample database installed
• Microsoft Visual Studio .NET
• Microsoft Visual J# .NET

This article assumes that you are familiar with the following topics:

• Database terminology
• Structured Query Language (SQL)

back to the top

Filling a DataSet

Using a variety of objects from within the System.Data.* namespace, you can connect to a database server, run a query, and have the results placed into a DataSet object. The DataSet is a disconnected object. Therefore, after the data is loaded, the connection to the database is no longer used until you want to load more data or update the server with the changes you have made to your in-memory copy of the information.

To load data from a database into a DataSet, follow these steps:

1. Start Internet Information Services (IIS) and SQL Server.
2. Open Visual Studio .NET.
3. Create a new Console Application project in Visual J# .NET. Visual Studio .NET creates a Static Class for you, together with an empty Main procedure.
4. Make sure that the project references the System.* and System.Data.* namespaces.
5. Use the import statement on the System.*, System.Data.*, and System.Data.SqlClient.* namespaces so that you do not have to qualify declarations from these namespaces later in your code. You must use these statements before any other declarations.

   import System.*;
   import System.Data.*;
   import System.Data.SqlClient.*;
   					

6. The first step to get data from the database to the DataSet is to establish a database connection, which requires a System.Data.SqlClient.SqlCommand object and a connection string. The connection string in the code to follow connects a SQL Server server that is located on the local computer (the computer where the code is running). You must modify this connection string as appropriate for your environment. After the SqlConnection object is created, call the Open method of that object to establish the actual database link.

   String stConnection = server=(local);integrated security=sspi;database=Northwind";
   SqlConnection cnNorthwind = new SqlConnection(stConnection);
   cnNorthwind.Open();
   					

7. Create a DataAdapter object, which represents the link between the database and your DataSet object. You can specify SQL or another type of command that is used to retrieve data as part of the constructor object of the DataAdapter. This sample uses a SQL statement that retrieves records from the Authors table in the Pubs database.

   String stSelect  = "select * from Customers";
   SqlDataAdapter daNorthwind = new SqlDataAdapter(stSelect,cnNorthwind);
   					

8. You must declare and create an instance of a DataSet object, at which time you can supply a name for the whole DataSet before you can start to load any data. The name may contain several distinct tables.

   DataSet dsNorthwind = new DataSet("Northwind");
   					

9. The SqlDataAdapter class provides two methods, Fill and FillSchema, that are crucial to loading this data. Both of these methods load information into a DataSet. Fill loads the data itself, and FillSchema loads all of the available meta data about a particular table (such as column names, primary keys, and constraints). A good way to handle the data loading is to run FillSchema followed by Fill. For example:

   daNorthwind.FillSchema(dsNorthwind,SchemaType.Source,"Customers");
   daNorthwind.Fill(dsNorthwind,"Customers");
   						

   If you only use Fill, you can only load the basic meta data that is required to describe the column names and data types. The Fill method does not load primary key information. To change this default behavior, you can set the set_MissingSchemaAction property of the DataAdapter object to MissingSchemaAction.AddWithKey, which loads the primary key meta data and the default information. For example:

   daNorthwind.set_MissingSchemaAction(MissingSchemaAction.AddWithKey);
   daAuthors.Fill(dsPubs,"Authors");
   					

10. You can use a For Next loop to loop through all of the DataRow objects in the Rows collection of a DataTable. This gives you access to each row of the table. You can access columns by name or by positional index (with '0' being the first column position). For example:

    for ( int i =0;i< dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Count() -1; i++)
    
      {
      System.Console.WriteLine("{0} {1}",
    				dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Item(i).get_Item("CustomerID").ToString(),
    				dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Item(i).get_Item("CompanyName").ToString() );		
      }
      System.Console.ReadLine();
    					

11. Save your project. On the Debug menu, click Start to run your project and make sure that it works.

back to the top

Complete Code Listing

package PopulatedatSet;

import System.*;
import System.Data.*;
import System.Data.SqlClient.*;

	/**
	 * Summary description for Class1.
	 */ 
public class Class1
{

	public Class1()
	{
		// 
		// TODO: Add constructor logic here.
		// 
	}

	/** @attribute System.STAThread() */ 
	public static void main(String[] args)
	{

		String stConnection = "server=(local);integrated security=sspi;database=Northwind";
		SqlConnection cnNorthwind = new SqlConnection(stConnection);
		String stSelect  = "select * from Customers";
		SqlCommand cmNorthwind = new SqlCommand(stSelect,cnNorthwind);
		SqlDataAdapter daNorthwind = new SqlDataAdapter(cmNorthwind);
		DataSet dsNorthwind = new DataSet("Northwind");
		cnNorthwind.Open();

		daNorthwind.FillSchema(dsNorthwind,SchemaType.Source,"customers");
		daNorthwind.Fill(dsNorthwind,"Customers");

		for ( int i =0;i< dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Count() -1; i++)
		{

			System.Console.WriteLine("{0} {1}",
				dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Item(i).get_Item("CustomerID").ToString(),
				dsNorthwind.get_Tables().get_Item(0).get_Rows().get_Item(i).get_Item("CompanyName").ToString() );        
		}
		System.Console.ReadLine();

	}
}
				

back to the top