How to: Use Visual C # .NET from Database Package DataSet Objects

This article references the following Microsoft .NET Framework Class Space: • System.Data.sqlclient

Content of this Task • Summary • Requirements • Fill DataSet • Full Code list • Reference

SUMMARY The DataSet object is a key section of data access in the Microsoft .NET framework, which is the object that can save tables, views, and relationships. This article describes how to populate DataSet objects using the results of one or more database queries and how to access these data after loading these data into the DataSet object.

The following list is required to list the recommended hardware, software, network infrastructure, and service packs required: • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server or Windows NT 4.0 Server 7.0, Microsoft SQL Server 2000 or Microsoft Data Engine for the Pubs Sample Database • Microsoft Visual Studio .NET This article assumes that you are familiar with the topic below: • Database terminology • Structured Query language (SQL)

Fill DataSet Use multiple objects from the System.Data namespace, you can connect to a database server, run the query, and then put the results in the DataSet object. DataSet is an object that is disconnected. Therefore, after loading the data, you will not use the connection to the database before you want to load more data or want to use the changes to the changes to the copy of these information.

To load data from the database into the DataSet, follow these steps: 1. Start Visual Studio .NET. 2. Create a "Console Application" item in Visual C # .NET. Visual Studio .NET creates a static class and an empty main process. 3. Make sure the item references the two namespaces of System and System.Data. 4. Using the USING statement for System, System.Data, and System.Data.sqlclient namespace, so that there is no need to define in these namespaces in the following code. These statements must be used before any other statement. Using system; using system.data.sqlclient; 5. The first step in getting data from the database to DataSet is to create a database connection, which requires a system.data.sqlclient.sqlCommand object and a connection string. The connection string in the code is connected to the SQL Server server on the local computer (computer running these code). You must modify the connection string accordingly based on the environment. Once you have created a SQLConnection object, call the OPEN method of the object to create a real database link. string sConnectionString; sConnectionString = "Password = myPassword; User ID = myUserID;" "Initial Catalog = pubs;" "Data Source = (local)"; SqlConnection objConn = new SqlConnection (sConnectionString); objConn.Open (); 6 Create a DataAdapter object that represents the link between the database and the DataSet object. You can specify a command for retrieving data as part of the DataAdapter's constructor object. The following example uses a SQL statement to retrieve records from the AUTHORS table of the PUBS database. SqlDataAdapter Daauthors = New SqldataAdapter ("Select * from authors", objconn; 7. An instance of a DataSet object must be declared, at which point you should provide a name for the entire DataSet before starting to load any data. This name can contain several separate tables. DataSet DSPUBS = New Dataset ("Pubs"); 8. SQLDataAdapter class provides two methods for Fill and Fillschema, which is critical to loading these data. Both methods can load information into the DataSet. Fill loads the data itself, while FillSchema loads all available metadata (such as column names, primary keys, and constraints) about a particular table. The correct way to handle data is to run FillSchema first, run Fill. For example: Daauthors.Fillschema (DSPUBS, Schematype.Source, "Authors"); Daauthors.Fill (DSPUBS, "Authors"); if you only use Fill, you can only load the basic metadata required to describe the column name and data type. The Fill method does not load primary key information.

To change this default behavior, you can set the MissingsChemaAction property of the DataAdapter object to MissingsChemaAction.AddWithKey, which loads the primary key metadata with the default information. For example: daauthors.MissingschemaAction = missingschemaAction.addwithKey; DAAAUTHORS.FILL (DSPUBS, "Authors"); 9. This data is available as a separate DataTable object in DataSet's tables collection. If you specify a table name in the call to Fillschema and Fill, you can use this name to access the specific table you need. DataTable TBLAUTHORS; TBLAUTHORS = DSPUBS.TABLES ["authors"]; 10. You can use the for Each loop to follow all DataRow objects in a DataTable ROWS collection. This will allow you to access each row of the table. You can access the column by name or by position index ("0" is the first column location). For example: Foreach (DATAROW DRCURRENT IN TBLAUTHORS.ROWS) {Console.writeline ("{0} {1}", DRCURRENT ["Au_FNAME"]. TOSTRING (), DRCURRENT ["AU_LNAME"]. Tostring ());} Console .Readline (); 11. Save the project. On the Debug menu, click Start to run your project and make sure it can run normally.

Complete code listing using System; using System.Data; using System.Data.SqlClient;. Namespace PopulateDataSet {///