Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Populating a DataSet from a DataAdapter


The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the CUBRIDDataAdapter.

The SelectCommand property of the CUBRIDDataAdapter is a CUBRIDCommand object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the CUBRIDDataAdapter are CUBRIDCommand objects that manage updates to the data in the data source according to modifications made to the data in the DataSet.

The Fill method of the CUBRIDDataAdapter is used to populate a DataSet with the results of the SelectCommand of the CUBRIDDataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

Note Using the CUBRIDDataAdapter to fetch an entire table takes time, especially if there are many rows in the table. This is because accessing the database, locating and processing the data, and then transferring the data over the wire to your client is time consuming. Pulling the entire table to the client also locks all of the rows on the server. To improve performance, you can use the WHERE clause can to greatly reduce the number of rows returned to the client. You can also reduce the amount of data returned to the client by only explicitly listing required columns in the SELECT statement. Another good workaround is to retrieve the rows in batches—such as a few hundred rows at a time—and only retrieve the next batch when the client is done with the current batch.

The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the tables in Mapping .NET Data Provider Data Types to .NET Framework Data Types. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in theDataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSetFill uses any mappings that may exist when populating the DataSet (see Setting Up DataTable and DataColumn Mappings).

NoteNote

If the SelectCommand returns the results of an OUTER JOIN, the CUBRIDDataAdapter does not set a PrimaryKey value for the resulting DataTable. You will need to define thePrimaryKey yourself to ensure that duplicate rows are resolved correctly. For more information, see Defining a Primary Key for a Table.

The following code example creates an instance of a SqlDataAdapter that uses a SqlConnection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and SqlConnection arguments passed to the SqlDataAdapter constructor are used to create the SelectCommand property of the SqlDataAdapter.For example, in CUBRID you can do:

string queryString = "select * from nation order by `code` asc";
CUBRIDDataAdapter adapter = new CUBRIDDataAdapter();
adapter.SelectCommand = new CUBRIDCommand(queryString , connection);
DataSet nation = new DataSet("nation");
adapter.Fill(nation);

Here is a complete example:

using CUBRID.Data.CUBRIDClient;
 
namespace DataSetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000", true);
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();

                string queryString = "select * from nation order by `code` asc";
                CUBRIDDataAdapter adapter = new CUBRIDDataAdapter();
                adapter.SelectCommand = new CUBRIDCommand(queryString , conn);
                DataSet nation = new DataSet("nation");
                adapter.Fill(nation);

                conn.Close();
            }
        }
    }
}

 

NoteNote

The code shown in this example does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it also closes the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Open method of the Connection, performing the operations against the data source, and then calling the Close method of theConnection. You should try to keep connections to the data source open as briefly as possible to free up resources for use by other client applications.

comments powered by Disqus
Page info
viewed 2122 times
translations en
Author
posted last year by
kevinyang
Contributors
updated last year by
View revisions
Share this article