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.. |
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. | |
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()
elementDS.Tables.Add(dtElements);
elementDS.Tables.Add(dtIsotopes);
Method 2 � Tables.AddRange()
ElementDS.Tables.AddRange(new DataTable()
{dtElements, dtIsotopes});
A DataSet tables collection can also be filled with linked tables containing
data directly from a database recordset, which is considered bound data.
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.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);
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")))
{
}
else
{
}
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);
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.
DataSet elementDS = new DataSet("Periodic");
DataTable dtElements = new DataTable("Elements");
dtElements.Columns.Add("Atomic Number", typeof(int));
dtElements.Columns.Add("Element", typeof(string));
dtElements.Columns.Add("Symbol", typeof(string));
dtElements.PrimaryKey = new DataColumn[]
{dtElements.Columns["Atomic Number"]};
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));
ElementDS.Tables.AddRange(new DataTable(){dtElements, dtIsotopes});
Dataset . Relations.Add()
elementDS.Relations.Add("Isotopes",
elementDS.Tables["Elements"].Columns["Atomic Number"],
elementDS.Tables["Isotopes"].Columns["Atomic Number"] );
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.
DataTable dt = ds.Tables[0];
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"])
{
prevAtomicNbr = (int)dr["AtomicNbr"];
drElement = dtElements.NewRow();
drElement["Atomic Number"] = dr["AtomicNbr"];
drElement["Element"] = dr["Element"];
drElement["Symbol"] = dr["Symbol"];
dtElements.Rows.Add(drElement);
}
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);
}
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)
{
ds.Relations.Clear();
for (int i=ds.Tables.Count -1; i >=0; i--)
{
ds.Tables[i].Constraints.Clear();
ds.Tables.RemoveAt(i);
}
}
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);
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);
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)
{
MessageBox.Show("DataSet has Errors");
}
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.
DataSet dsChanges;
dsChanges = ds.GetChanges(DataRowState.Modified);
if(!dsChanges.HasErrors)
{
da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
int res = da.Update(dsChanges);
da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);
}
Next
Data Grids