Add a Record to the Dataset and Update the Database

When you add a new record, you'll want to add it to the Dataset and the underlying database. Let's see how.

Add three new buttons to the form. Set the following properties for your buttons:

Name: btnAddNew
Text: Add New

Name: btnSave
Text: Save
Enabled: False

Name: btnCancel
Text: Cancel
Enabled: False

The Add New button won't actually add a new record. We'll use this button to clear the textboxes, ready for a new record to be added. We can also disable this button after it has been clicked, and enable the Save and Cancel buttons. The Save button is where we'll add the record to the Dataset and to the Database.

Double click your Add New button, and add code to clear the text boxes:

textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();

If the user clicks the Add New button then we want to disable it, and then switch on the Save and Cancel button. To do that, here's the code:

btnAddNew.Enabled = false;
btnSave.Enabled = true;
btnCancel.Enabled = true;

That's all we need to do here. You can test it out, if you want. When the form loads it will look like this (we've put our buttons on a GroupBox):

C# form showing Add New Record button

When you click the Add New button, the form will look like this:

C' form - saving a new record to the database

But all the code does is to clear the four textboxes of text. The user can then enter a new record.

Return to Design Time and double click your Cancel button. What we need to do here is to call NavigateRecords. We need to do this because the textboxes are all blank. Calling NavigateRecords will put the record that was erased back into the textboxes.

The Cancel button also needs to reset all three buttons. Here's the code to add, then:

NavigateRecords( );

btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

You can test it out. Click your Add New button when the form loads. The textboxes will go blank, and the first record will disappear. Now click Cancel. You should find that the record reappears.

 

Saving a New Record

After a new record has been entered into the text boxes, we can save it. Double click your Save button to get at the code.

To save a record, you need to do two things: save it to the Dataset, and save it to the underlying database. You need to do it this way because the Dataset with its copy of the records is disconnected from the database. Saving to the Dataset is NOT the same as saving to the database.

To add a record to the Dataset, you need to create a new Row:

DataRow row = ds.Tables[0].NewRow( );

This creates a new DataRow object that we've called row. The DataRow object is used to add new rows to a Dataset. But the new row will not have any data. To add data to the row, the format is this:

row[1] = textFirstName.Text;

So after your DataRow variable (row) you need a pair of square brackets. In between the square brackets type its position in the Row. This is the Column number. So row[1] refers to the first_name column, for us. After an equals sign, you type whatever it is you want to add to that Column - the text from textFirstName, in our case.

Finally, you issue the Add command:

ds.Tables[0] Rows.Add( row );

After Add, and in between a pair of round brackets, you type the name of the row you want to add, which was row in our example. The new row will then get added to the end of the Dataset.

So add this code to your Save button:

DataRow row = ds.Tables[0].NewRow( );
row[1] = txtFirstName.Text;
row[2] = txtSurname.Text;
row[3] = txtJobTitle.Text;
row[4] = txtDepartment.Text;

ds.Tables[0].Rows.Add(row);.Rows.Add(row);

To actually update the database, we need to add a new method to our class. This method will use the DataAdapter we set up. The DataAdapter is linked to the database itself, and can issue Update, Insert and Delete commands. It's quite easy to do and only takes a couple of lines.

If your DatabaseConnections class is not open, double click it in the Solution Explorer to get at the code. Now add the following method:

public void UpdateDatabase( System.Data.DataSet ds )
{

}

So this is a public method that doesn't return a value (void). We called it UpdateDatabase. In between the round brackets of UpdateDatabase have a look at what the code is:

System.Data.DataSet ds

This is a DataSet being passed to the method. The name of the DataSet is ds. Our DataSet, remember, is where all the records are kept, even the ones that have changed. We'll be updating the database with these new changes.

In order to do an automatic update, you need something called a CommandBuilder. This is initialised with the name of a DataAdapter, the DataAdapter we set up earlier in the Class.

Add this rather long line to your method (it should be on one line in your code):

System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder( da_1 );

We've called our CommandBuilder cb. At the end, in between round brackets, we have the name of our DataAdapter, which was da_1.

Now that we have a CommandBuilder, we can do something with it. Add this line to your method:

cb.DataAdapter.Update( ds.Tables[0] );

The CommandBuilder has a property called DataAdapter. This property has a method of its own called Update. The Update method takes the name of a DataSet and a table. The DataSet for us is ds, which is the one we passed in to our UpdateDatabase method when we set it up. After a dot, you type the name of a table in your dataset.

Here's what your method should look like:

C# code for a CommandBuilder object

And that's it. That's enough to update the underlying database with your new record. Of course, we have yet to call our new method into action.

Go back to the code for your Save button. Add a try … catch statement:

try
{

}
catch (Exception err)
{

MessageBox.Show(err.Message);

}

For the try part, add the following:

try
{

objConnect.UpdateDatabase(ds);

MaxRows + 1;
inc = MaxRows - 1;

MessageBox.Show("Database updated");

}

As the first line of the try part, we have this:

objConnect.UpdateDatabase(ds);

Here, we access our DatabaseConnections object (objConnect), and call our new method UpdateDatabase. In between the round brackets of our UpdateDatabase method we have the name of our DataSet, which is ds. This contains all the records we pulled from the database, and any amendments we've made. We're handing the whole thing over to the new method, where the CommandBuilder and DataAdapter will take care of the updates for us.

Notice the next two lines:

MaxRows = MaxRows + 1;
inc = MaxRows - 1;

Because we have added a new Row to the Dataset, we also need to add 1 to the MaxRows variable. The inc variable can be set to the last record in the Dataset, which is MaxRows - 1.

The final three lines of our Save code are these:

btnCancel.Enabled = false;
btnSave.Enabled = false;
btnAddNew.Enabled = true;

These three lines just reset the buttons to their original state.

The whole of the code for your Save button should look like this:

C# code - saving a new record to a database

Try it out. When you start your programme, click the Add New button to clear the textboxes. Enter a new record in the blank textboxes and then click your Save button. Click your Previous and Next buttons. You'll see that the new record appears. When you close down your project and open it up again, the new record should still be there.

(If the new record doesn't appear when you restart, go back to Design Time. In the Solution Explorer, click on your Database under Resources to select it. Now have a look at the Properties window below the Solution Explorer. Locate a property called Copy to Output Directory. It has three settings: Do not copy, Copy always, and Copy if newer. If your database is not updating, try either Copy if newer or Copy always.)

In the next lesson, you'll learn how to update a record, and how to delete a record.