As you should know already, before using a data set, you
should create an object of type DataSet. You can create it
manually or visually.

To manually create a data set, you have two
alternatives. You can click the DataSet object from the
Data section of the Toolbox and click the form. You would specify it as an
Untyped Dataset and click OK. An alternative is to manually type the code
used to create the data set, which is done by declaring a variable of type
DataSet. After manually creating the data set, you must
fill it with records to make it useful.

As you should know already, before using a data
set, you should create an object of type DataSet.
You can create it manually or visually.

To manually create a data set, you have two
alternatives. You can click the DataSet object from
the Data section of the Toolbox and click the form. You would
specify it as an Untyped Dataset and click OK. An alternative is to
manually type the code used to create the data set, which is done by
declaring a variable of type DataSet. After
manually creating the data set, you must fill it with records to
make it useful.

To visually create a data set, you can add a new
data source from either the main menu under Data or the Data Sources
window. Then use the Data Source Configuration Wizard. When the
wizard ends, a class is generated. The class is derived from the
DataSet class and holds the name you had specified
for the data set in the last page of the wizard. The class may start
as follows:

public partial class dsExercise : global::System.Data.DataSet
{
}

After creating the data set, you can use it, along
with its table(s) (DataTable objects), its (their) columns
(DataColumn objects), and its (their) records (DataRow
objects and DataRowCollection lists).

Because a data set is tied to the database, it provides
all of its services. This means that a data set can be used for any
necessary maintenance assignment. There are many ways you can perform
maintenance on a data set, a table, a column, or a record. Each one of these
items is represented by one or more classes and those classes support
various types of maintenance operations.

Besides the means provided by the data set, the tables,
their columns, and their records, the table adapter that was generated by
the wizard is equipped with various methods.

Practical
Learning: Introducing Data Sets

Start Microsoft Visual Studio and open the FunDepartmentStore1
from the previous lesson

Saving the Records of a Data Set

Although the records of a database belong to a table, if
you want to use them in an external application, you can save them in an XML
file. To support this, the DataSet class is equipped with
the WriteXml() method. Here is an example of calling it:

Some of the operations you can perform on a data set
include copying a table or the entire data set by calling the appropriate
Copy() method (DataTable.Copy() or
DataSet.Copy() respectively).

To get the number of records in a table, access the
desired table (using its name or its index) from the data set that was
generated, access its Rows property, and access its
Count property.

Filling a Data Set

We saw that, when a table adapater has been created, its
class is equipped with a method named Fill that is used to
fill a data set. You too, at times, will want to fill or refill a table with
records from its corresponding data table. To do this, access your table
adapter, call its Fill() method, and pass the table as
argument. Here is an example:

Editing a record consists of changing one or more of its
values. To programmatically do this, you must first locate and open the
record. Then change the necessary value(s). After doing this, if you want to
apply the change(s) to the table, you must update it. To assist you with
this, the generated table adapter is equipped with the Update()
method. This method is overloaded with four versions: one for a data set,
one for a data table, one for a record (a data row), and one for an array of
records (a DataRow[] array). Therefore, after making the
changes on either a record, some records, or a table, call the appropriate
version of the method to apply the changes.

Creating a Record

One of the most fundamental operations you can
perform on a data set consists of creating a new record. To assist
you with tables, their columns and records, the data set class that
the wizard generates inherits the properties and methods of the
DataSet class. This includes the Tables
property. You can use this property to access a table, based on its
name or its index. Once you have obtained the table, you can perform
any normal operation you want.

To support record creation, we
already know
that the DataTable class is equipped with the
NewRow() method. To use this method, you can access
the data set object that was generated for you, access the desired
table, and call this method.

After calling the DataTable.NewRow()
method, you can access each column by its name or its index and
assign the desired value to it. You can access the columns in any
order of your choice. You can choose what columns to provide values
for and which ones to ignore. When doing this, you must observe the
rules established in the table's structure:

Specify a value only for an existing column

Don't assign a value to a column whose records must be
automatically specified. This is the case for a primary key
column with an identity property

Don't assign a value to a column whose records are specified
by an expression

Observe all check constraints

If a column has a UNIQUE characteristic,
you must not give it a value that exists already in the table

After specifying the value(s) of column(s), to
apply them to the table, call the Add() method of
the Rows property of the table.

After calling the DataRowCollection.Rows.Add()
method, you must update the table adapter. Here is an example:

In the same way, you can use these steps to add as many
records as you want.

Instead of adding one record at a time, you can store
the records in an array and add them at once, as a block. This is possible
because the DataTable.Rows property, which is of type
DataRowCollection, is equipped with the ItemArray
property.

After adding the record(s) to the table, you must update
the data set. To assist you with this, the generated table adapter is
equipped with a method named Update.

After the new record has been added, it is marked with
the RowState.Added value.

Practical
Learning: Creating a Record

To create a new form, on the main menu, click Project -> Add Windows
Form...

Set the Name to CreateStoreItem and click Add

In the top section of the Toolbox, click dsFunDS and click the form

In the top section of the Toolbox, click StoreItemsTableAdapter and
click the form

Although you can use Transact-SQL to find a record, the
data set provides its own mechanism through the DataRowCollection
class that is represented in a table with the Rows
property. You can first use the DataRowCollection[]
(actually DataRowCollection.Item[]) property to locate a
record. Once you have the record, you can use the DataRow[]
(actually DataRow[]) to identify a column and inquire about
its value. If the result is not null, a record is found. If a value is not
found, the compiler may throw an IndexOutOfRangeException
exception. You can use this exception to find out whether a record was found
or not.

Because the DataRow.Item property is
overloaded, you can access a column by its index inside the table or using
its actual name.

Practical
Learning: Locating a Record

If necessary, display the Create Store Item form and double-click
the Reset button

Change its code as follows:

private void btnReset_Click(object sender, EventArgs e)
{
// We will use this variable to create a new item number
int newRecordNumber = 1;
// Check each record
for (int i = 0; i < taStoreItems.GetData().Rows.Count; i++)
{
// Get the current record
DataRow rcdCurrentStoreItem = taStoreItems.GetData().Rows[i];
// Get the next record
DataRow rcdNextStoreItem = taStoreItems.GetData().Rows[i + 1];
// If the item number + 1 of the current record is different from the next
if( (int.Parse(rcdCurrentStoreItem["ItemNumber"].ToString()) + 1) !=
int.Parse(rcdNextStoreItem["ItemNumber"].ToString()) )
{
// Then use the current item number + 1 as the new item number
newRecordNumber = int.Parse(rcdCurrentStoreItem["ItemNumber"].ToString()) + 1;
break;
}
}
txtItemNumber.Text = newRecordNumber.ToString();
txtManufacturer.Text = "";
cbxCategories.Text = "Women";
txtItemName.Text = "";
txtItemSize.Text = "";
txtUnitPrice.Text = "0.00";
txtManufacturer.Focus();
}

Deleting Records

After locating a record, you can perform an
action on it. One of the things you can do is to delete a record. To
support this operation, the DataRow class is
equipped with the Delete() method. Therefore, to
delete a record, first find it.

To assist you with this, the
DataRowCollection class, which is represented in a table by
the Rows property, is equipped with the
Find() method. After finding the record, call its
DataRow.Delete() method. After deleting the record, you
must update the table by calling the Update()
method of the generated table adapter.

The string class provides tremendous opportunities for
data analysis through its built-in methods. It gives the ability to get a
list of records that start, or end, with a certain character or a
combination of characters, to get the records that contain a certain word,
etc.

Display the Store Inventory form and click the Show Whole Store
Items Inventory button

Data Analysis With a Binding Source

In the Data section of the Toolbox, Microsoft Visual
Studio provides a component you can use to analyze, filter, or sort records.
To use it, click the BindingSource object and click the
form. You should then specify the DataSource as the data
set object you had added to your form. You should also specify its
DataMember as the table on which you will operate.

To perform data analysis using a binding source, you use
the Filter property of the BindingSource class. You can
enter an expression in the Properties window or type one when you are ready
to apply the filter. Here is an example: