Click here to Skip to main content
Click here to Skip to main content

A Practical Guide to .NET DataTables, DataSets and DataGrids - Part 3

, 23 Feb 2004
Rate this:
Please Sign up or sign in to vote.
The purpose of this document is to provide a practical guide to using Microsoft’s .NET DataTables, DataSets and DataGrid

4 Data Sets

4.1 DataSet Methods

DataSet Method

Description

AcceptChanges()

Accepts all changes to the DataSet

Clear()

Removes all rows from all tables in the DataSet – that is, removes all data.

Clone()

Creates a new DataSet with all tables having the same Table structure including any constraints and relationships. No Data is copied.

Copy()

Same as for the DataSet Clone() but it includes all Data.

GetChanges()

Creates a DataSet that contains all changes made to the dataset. If AcceptChanges was called then only changes made since the last call are returned.

HasChanges()

Returns true if any changes were made to the DataSet including adding tables and modifying rows.

WriteXml()

Outputs an XML file containing schema with all Tables, Data, Constraints and relationships.

ReadXml()

Inputs an XML file containing schema, Tables, Data, Constraints and relationships..

4.2 DataSet Properties

DataSet Property

Description

CaseSensitive

If set to true then string compares in DataSet tables are case sensitive otherwise they are not.

DataSetName

Name of the DataSet

HasErrors

Returns true if there are any errors within any tables in the DataSet

Relationss

Collection of Relations

Method/Property

Description

Add()

AddRange()

If two or more relations are to be added to the collection they can be added using this method. They are appended to the existing collection in the order specified in the range.

CanRemove

Returns true if the relation can be removed from the collectionn

Clear()

Removes all relations from the relations collection

Contains()

Returns a true if the collection contains the named relation.

Count

Returns the number of relations in the collection

IndexOf()

Returns the index of a relation in the collection equal to name.

Remove()

Removes a relation by Name from the collection

RemoveAt()

Removes a relation by index from the collection.

Tables

Collection of Tables

Method/Property

Description

Add()

Adds a table to the collection

AddRange()

If two or more tables are to be added to the collection they can be added using this method. They are appended to the existing collection in the order specified in the range.

Clear()

Removes all tables from the collection

Contains()

Returns a true if the collection contains a table with TableName equal to name.

Count

Returns the number of tables in the collection

IndexOf()

Returns the index of a table in the collection with a TableName equal to name.

Remove()

Removes a table by TableName from the collection

RemoveAt()

Removes a table by index from the collection.

4.3 Loading A DataSet

4.3.1 From a Table

Tables created and filled with data as discussed in the Tables section can be added to the Tables collection by using the Add() method or they can be added at once using the AddRange() method.

Example of Two equivalent methods used to add tables dtElements and dtIsotopes to the DataSet ElementDS using Add() and AddRange()

Method 1 – Tables.Add()

// Add the Elements table to the DataSet
elementDS.Tables.Add(dtElements);
// Add the Isotopes table to the DataSet
elementDS.Tables.Add(dtIsotopes);

Method 2 – Tables.AddRange()

ElementDS.Tables.AddRange(new DataTable()
{dtElements, dtIsotopes});

4.3.2 From a Database

A DataSet tables collection can also be filled with linked tables containing data directly from a database recordset, which is considered bound data.

4.3.2.1 Method 1 – sqlDataAdapter

The following code illustrates how to directly load or bind a database recordset using the sqlDataAdapter’s Fill() method where the select query string was used to create the recordset. After the Fill() method is called, ds will contain a table in its collection with column headers that match the field names in the select query string and column datatypes will match those specified in the database table elements. Each row will contain data corresponding to each field.

System.Data.SqlClient.SqlConnection sqlConnection1;
System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
System.Data.SqlClient.SqlCommand sqlSelectCommand1;
sqlConnection1 = new System.Data.SqlClient.SqlConnection();
sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
sqlSelectCommand1.CommandText = "SELECT ElementsID, AtomicNbr,”
+ “Symbol, AtomicMass, Element FROM [Elements]";
sqlDataAdapter1.SelectCommand = sqlSelectCommand1;
//
// sqlConnection1
// <replace xxxxx, nnnnn and dbName with appropriate values>
//
sqlConnection1.ConnectionString = “workstation id=xxxxx;packet “
+ “ size=4096;user id=nnnnn; pwd=yyyyy;data “
+ “ source=xxxxx; persist”
+ “ security info=False; initial catalog=dbName”;
sqlSelectCommand1.Connection = sqlConnection1;
DataSet ds = new DataSet();
sqlDataAdapter1.Fill(ds);

4.3.2.2 Method 2 – sqlDataReader

This method is more complex than using the sqlDataAdapter’s Fill() method, but it allows for preprocessing of data prior to populating table rows and the data is not bound directly to the database. In the example, instead of restricting the database record set to contain distinct Atomic Number rows through a SQL query, it is done programmatically for illustration purposes.

string SQL= "SELECT ElementsID, AtomicNbr, " +
"Symbol, AtomicMass, Element”
+ “ FROM [Elements] order by AtomicNbr ASC";
sqlConnection sqlConnection1=
new sqlConnection (
 "connection info to MSDE or SQL Server 2000+”;);
sqlCommand sqlCommand = new sqlCommand (SQL,sqlConnection1);
sqlConnection1.Open();
sqlDataReader elementReader = SqlCommand.ExecuteReader();
// Starting with the element table dt defined in
// the Tables section, an ElementID column
// is added that will be used as the primary key for the row.
DataColumn dc = new DataColumn(“ElementsID”,
 System.Type.GetType(“System.Guid”));
dt.Columns.Add(dc);
// Make ‘ElementsID’ a primary key:

dt.PrimaryKey = new DataColumn[]{dt.Columns["ElementsID"]};
// Fill table dt with data from the database table Elements:

Note: the sqlDataReader class has a method that can be used to determine whether a null or non-existent value was returned for a particular cell. For example:

If (!elementReader.IsDBNull(elementReader.GetOrdinal("AtomicNbr")))
{
   // fill cell with value
}
else
{
   // handle this condition
   // for example fill cell with a default value
}

This check has been omitted in the following code for clarity, but it is a good practice to use it.

DataRow dr;
int PrevAtomicNbr = 0;
try
{
  while(myReader.Read())
  {
  if (PrevAtomicNbr != elementReader.GetInt32(
    elementReader.GetOrdinal("AtomicNbr")))
  {
  PrevAtomicNbr = elementReader.GetInt32(
    elementReader.GetOrdinal("AtomicNbr"));
  dr = dt.NewRow();
  dr[“ElementsID”] =
  elementReader.GetGuid(elementReader.GetOrdinal("ElementsID"));
  dr[“AtomicNbr”] = elementReader.GetInt32(
    elementReader.GetOrdinal("AtomicNbr"));
  dr[“Symbol”] = elementReader.GetString(
    elementReader.GetOrdinal("Symbol));
  dr[“Element”] = elementReader.GetString(
    elementReader.GetOrdinal("Element"));
  dr[“AtomicMass”] =
    elementReader.GetDecimal(elementReader.GetOrdinal("AtomicMass"));
  dt.Rows.Add(dr);
  }
 }
}
finally
{
  elementReader.Close();
  sqlConnection1.Close();
}
dt.AcceptChanges();
// Add table dt to a new dataset ds and its tables collection
DataSet ds = new DataSet();
ds.Tables.Add(dt);

4.4 Linked Tables

This example shows how to link two tables together through a primary key. In this example a Table with TableName of Elements is created with a Primary key of ‘Atomic Number’. The second Table with TableName of Isotopes is linked through a relationship coupling its Atomic Number column to Elements primary key.

// create a new DataSet named Periodic that will
// hold two linked tables with
// TableNames of Elements and Isotopes respectively.
DataSet elementDS = new DataSet("Periodic");
// Create Elements Table
DataTable dtElements = new DataTable("Elements");
dtElements.Columns.Add("Atomic Number", typeof(int));
dtElements.Columns.Add("Element", typeof(string));
dtElements.Columns.Add("Symbol", typeof(string));
// Make ‘Atomic Number’ a primary key in Elements table
dtElements.PrimaryKey = new DataColumn[]
{dtElements.Columns["Atomic Number"]};
// Create Isotopes Table
DataTable dtIsotopes = new DataTable("Isotopes");
dtIsotopes.Columns.Add("Symbol", typeof(string));
dtIsotopes.Columns.Add("Atomic Number", typeof(int));
dtIsotopes.Columns.Add("Isotope Number",typeof(int));
dtIsotopes.Columns.Add("Percent Abundance",
typeof(System.Decimal))
dtIsotopes.Columns.Add("Atomic Mass", typeof(System.Decimal));
// Add tables to the Dataset
ElementDS.Tables.AddRange(new DataTable(){dtElements, dtIsotopes});

Dataset . Relations.Add()

// Add a relationship for Tables Elements and Isotopes
// through the primary key ‘Atomic Number’ in DataSet
// Periodic and name the relationship ‘Isotopes’. This name is
// used in the DataGrid to select table rows, in table Isotopes,
// that contain isotope values for the selected element.
elementDS.Relations.Add("Isotopes",
elementDS.Tables["Elements"].Columns["Atomic Number"],
elementDS.Tables["Isotopes"].Columns["Atomic Number"] );

4.5 Linked tables in a dataset

4.5.1 Filling

Assume that another DataSet ds exists that has a table with index 0 in its Tables collection that contains both Element and Isotope data that will be used to fill rows in the linked tables contained in the elementDS DataSet tables collection as defined in the previous section. Assume Table[0] in DataSet ds has the following columns:

AtomicNbr, Element, Symbol, IsotopeNbr, PctAbundance and AtomicMass

where rows are sorted by Atomic numbers ascending and then by IsotopeNbr numbers ascending.

// Assign the table containing both Elements and
// Isotopes to dt using index 0
DataTable dt = ds.Tables[0];
// Create two DataTable variables dtElements and dtIsotopes and
// assign tables contained in the DataSet elementDS Tables
// collection using TableNames as indexes.
DataTable dtElements = elementDS.Tables[“Elements”];
DataTable dtIsotopes = elementDS.Tables[“Isotopes”];
DataRow drElement;
DataRow drIsotope;
int prevAtomicNbr = 0;
foreach (DataRow dr in dt.Rows)
{
  if(prevAtomicNbr != (int)dr["AtomicNbr"])
    { //need only one row per AtomicNbr in Table[“Elements”]
      // Fill an element row with data from dt.
     prevAtomicNbr = (int)dr["AtomicNbr"];
     drElement = dtElements.NewRow();
     drElement["Atomic Number"] = dr["AtomicNbr"];
     drElement["Element"] = dr["Element"];
     drElement["Symbol"] = dr["Symbol"];
     dtElements.Rows.Add(drElement);
   }
  // Fill an isotope row with data from dt.
  drIsotope = dtIsotopes.NewRow();
  drIsotope["Isotope Number"] = dr["IsotopeNbr"];
  drIsotope["Symbol"] = dr["Symbol"];
  drIsotope["Atomic Number"] = dr["AtomicNbr"];
  drIsotope["Percent Abundance"] = dr["PctAbundance"];
  drIsotope["Atomic Mass"] = dr["AtomicMass"];
  dtIsotopes.Rows.Add(drIsotope);
}

4.5.2 Removing

To remove all linked tables or a selected table that is linked from the DataSet, it is first necessary to remove all relations, then constraints and then the table otherwise relationship/constraint table errors are generated.

The following code example provides a generic routine for removing all linked tables in a dataset.

public void RemoveAllTables(DataSet ds)
{
  // need to do it in reverse order due to constraints
  ds.Relations.Clear();
  for (int i=ds.Tables.Count -1; i >=0; i--)
  {
   ds.Tables[i].Constraints.Clear();
   ds.Tables.RemoveAt(i);
  }
} 

4.6 XML Export and Import DataSet Data

4.6.1 WriteXml

All tables with their schemas, relationships, constraints and data contained in a DataSet can be exported in XML by specifying a DataSet property Namespace and using the WriteXml method.

For example:

ds.Namespace = "http://www.mydomain.com/xmlfiles”
ds.WriteXml(FileName, XmlWriteMode.WriteSchema);

4.6.2 ReadXml

All tables with their schemas, relationships, constraints and data contained in an XML file are imported into a DataSet by specifying a DataSet property Namespace and using the ReadXml method. Once in the DataSet it is just like any other dataset.

For example:

DataSet ds = new DataSet();
ds.Namespace = "http://www.mydomain.com/xmlfiles";
ds.ReadXml(FileName, XmlReadMode.ReadSchema);

4.7 Handling DataSet Errors

Similar to the DataTable HasErrors property the DataSet HasErrors property returns true if any errors occurred in any of the tables being managed by the DataSet.

if(ds.HasErrors)
{ // One or more of the tables in the DataSet has errors.
  MessageBox.Show("DataSet has Errors");
  // Insert code to resolve errors.
  // Refer to ‘Handling DataTable Errors’ section for example of
  // handling errors within tables.
}

4.8 Updating Database with DataSet/DataTable changes

The following code shows how to create a DataSet containing all of the changes that have occurred to tables within a DataSet. The new DataSet can be used for updating the database.

// Create temporary DataSet variable.
DataSet dsChanges;
// GetChanges for modified rows only.
dsChanges = ds.GetChanges(DataRowState.Modified);
// Check the DataSet for errors.
if(!dsChanges.HasErrors)
{
  // No errors were found, update the DBMS with the SqlDataAdapter da
  // used to create the DataSet.
  da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
  da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
  int res = da.Update(dsChanges); // returns the number of rows updated
  da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
  da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);
}

Next

Data Grids

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Pete2004
President Bioxing
United States United States
Ph.D. in BioPhysics and over 20 years of experience in managing, architecting and hands-on developing software systems for biotechnology companies that produced cutting edge instrumentation and data systems. These include DNA and Peptide Synthesizers, cDNA, oligo and Protein microarrays and mass spectrometers used for protein sequencing.In 2001 founded BioXing (pronounced Bio-Crossing) which has architected and developed an extensible relational database repository and software system that includes Web Services, Client Workstation and Web Based applications. The system is used to track, manage, integrate and data mine disparate laboratory data, protocols and experiments and link to reference proteomic and genomic data. BioXing also does consulting and development for biotechnology companies.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberBolud13-Aug-10 13:14 
GeneralGood article but unnecessary complex Pinmemberpentel27-Aug-08 20:45 
General[Message Removed] Pinmemberimmetoz4-Oct-08 3:02 
GeneralRe: Good article but unnecessary complex PinmemberPete20046-Oct-08 7:04 
GeneralUpdating Dataset Problems PinmemberHinJinShah1-Aug-07 6:50 
GeneralRe: Updating Dataset Problems PinmemberPete20041-Aug-07 7:04 
GeneralRe: Updating Dataset Problems PinmemberHinJinShah3-Aug-07 10:38 
GeneralRe: Updating Dataset Problems [modified] PinmemberPete20044-Aug-07 15:56 
GeneralRe: Updating Dataset Problems PinmemberHinJinShah8-Aug-07 7:43 
Questionhow read a xml with dataset class Pinmemberrjorget2-Feb-06 9:13 
AnswerRe: how read a xml with dataset class PinmemberDharmarajNagarajan14-Nov-07 0:18 
GeneralInsertAt() causes errors PinmemberMr.Bill16-Aug-05 3:13 
GeneralRe: InsertAt() causes errors PinmemberPete200417-Aug-05 11:45 
Generalconversion excel file into txt file PinmemberMiroka8-Jun-05 8:06 
GeneralWriteXML and blob PinmemberHolger Flick31-Jul-04 14:21 
GeneralRe: WriteXML and blob PinmemberPete200417-Aug-05 11:48 
GeneralProgramatic DataGrid Scrolling PinsussAnonymous10-Jul-04 10:00 
GeneralRe: Programatic DataGrid Scrolling Pinmemberpartyganger10-Jul-04 14:09 
GeneralRe: Programatic DataGrid Scrolling PinsussAnonymous15-Jul-04 20:32 
GeneralRe: Programatic DataGrid Scrolling PinmemberPete200415-Jul-04 7:34 
GeneralReadXML and datagrid PinsussAnonymous28-May-04 12:34 
GeneralRe: ReadXML and datagrid PinmemberPete200428-May-04 13:07 
GeneralA problem with WriteXml Pinmembersjkdo17-May-04 7:27 
GeneralRe: A problem with WriteXml PinmemberPete200417-May-04 8:08 
GeneralRe: A problem with WriteXml Pinmembersjkdo17-May-04 12:18 
GeneralChanging Column Order PinmemberEstimatedProphet4-May-04 4:32 
GeneralRe: Changing Column Order PinmemberPete20045-May-04 5:33 
GeneralRe: Changing Column Order PinmemberEstimatedProphet5-May-04 5:40 
GeneralRe: Changing Column Order PinmemberPete20045-May-04 6:40 
GeneralRe: Changing Column Order PinmemberEstimatedProphet5-May-04 6:45 
GeneralRe: Changing Column Order PinmemberPete20045-May-04 6:55 
GeneralRe: Changing Column Order PinmemberMike Osbahr27-Jul-04 11:55 
GeneralRe: Changing Column Order PinsussAnonymous9-Aug-04 13:56 
AnswerRe: One Solution for Changing Column Order Pinmemberdothedew1-Nov-05 7:26 
GeneralVery useful article. PinmemberBoyan Botev29-Apr-04 3:15 
GeneralRe: Very useful article. PinmemberPete200429-Apr-04 9:03 
GeneralRe: Very useful article. PinmemberPete200430-Apr-04 16:03 
QuestionLink your articles together? PinmemberMikeBeard25-Feb-04 6:15 
AnswerRe: Link your articles together? PinmemberPete200429-Apr-04 13:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 24 Feb 2004
Article Copyright 2004 by Pete2004
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid