In my experience, datasets have always been annoying, because when you need to modify a property of the database you always have to update the tables and table adapters in your xds file and republish the new version of the software.
But creating connection and queries with Datasets is easier and faster than creating a Connection Manager class, (but not always the best way). If you are about to create an application involving a Data Grid View, and you need to insert, update or delete; a dataset is your best option.
Create your dataset and table adapter
1. Create your frame
2. Drop a Data Grid View control
3. Create a Dataset (xsd) File.
4. Open your xsd file.
5. Drag the table that you want to work with from the server explorer. Your table and table adapter will be created.
If you click the Table Adapter and check its properties you will see that the select, insert, update and delete command are automatically generated.
That depends on how your table is designed. When I was working with a table with no primary key, the updated command was not generated. You can add the command, but when it is time to update a record in the data grid view, it could generate some issues and you will not able to update.
If you do not want to deal with these problems, set a primary key field to your table when you are designing it; if you do not want to deal these problems, set a primary key field to your table when you are designing it in your data base.
Now you check the code that is created automatically in the load form event. This code will fill the data grid view with the current data in your table.
6. Enable the insert, update and delete properties in your data grid view control clicking the option control button .
7. In that same box, choose your data table as your data source
Now your data grid view columns will be generated automatically. Also as your table adapter object, binding source object and dataset object.
Also in the Load Form event this code will appear to fill with the table info every time this window form is loaded.
private void dsDemo001GUI_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'dsDemoDatabase.People' table. You can move, or remove it, as needed.
this.peopleTableAdapter.Fill(this.dsDemoDatabase.People);
}
8. Now you need to add a button that will deleted a selected record.
9. Now is time to use the event to update the records from the data grid view. Select the grid view and go to event properties section.
10. Create a CellEndEdit Event.
11. In the code go the event method and type the following code
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
this.peopleTableAdapter.Update(dsDemoDatabase);
}
Now set your grid view needs the Allow User to Add Row to TRUE.
Now create a click event button, and type the following code
private void button1_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
dataGridView1.Rows.Remove(row);
this.peopleTableAdapter.Update(dsDemoDatabase);
}
The button will delete the selected rows from the data grid view and then call the method Update from the Table Adapter object. Also the grid view selection mode property is change to full row selection to avoid exceptions while the row is deleting.
Depends of what you are doing the Table Adapter Object call the Update method, and update table in the Dataset and in the base. If you remove rows, calls the delete query; if you add a new row, calls the insert query; and if you are modifying an existing cell calls the update query.
Conclusion
To use a dataset in a table you must have a good table design in your database (With a Primary Key and an auto-increment column).
Dataset can save you a lot time in creating and connection string and queries.