Now that we've seen how to view an entry from a database in its own page it makes sense to start looking at how we add new entries and edit existing rows.

Adding New Entries

First thing we need is a Stored Procedure (SP) in our database to insert a new row. We could just add the SQL directly to the Table Adapter, like we did before, but I thought I'd cover how to use an SP (which lives in the actual database rather than your .NET project) too.

You could work on the database directly from within SQL Server Management Studio. Or you could use the Database Explorer in VS, which is probably a little quicker, assuming you have it running, so we'll cover that approach.

In VS - at the bottom of the Solution Explorer pane - there's a Database Explorer pane. Open it up and, assuming you've got the "connection string" in you Web.Config file you should see the Zoo database listed.

Expand the Zoo DB like so:

Now, right click on Stored Procedures and choose to add a new one. In the window that appears type the following:

This will create an SP that we pass four arguments to. The third one (date of birth) is optional. Using these four values we INSERT a new row and then return the "ID" of the row we just added.

Save the new SP and call it AddNewAnimal.

Now we need to add the new SP to our TableAdapter in the project's DataSet, so that the ASP.NET application is aware of its existence.

Back in the Solution Explorer pane open the DataSet (Zoo.xsd) and right-click on the AnimalsTableAdapter section, like so:

Choose Add Query and in the first screen of the wizard that appears choose "Use Existing Stored Procedure".

On the next page you should get a dropdown list of SPs from the database. Choose "AddNewAnimal", like so:

Notice how it knows about the four parameters we need to pass it and the value it will return.

On the next screen be sure to choose "A single value". This means we get a simple way to convert the ID returned in to an integer value for use in C# (see later).

In the next screen give the method a simple name, like "Add":

Click Finish and you're done!

Our TableAdapter now has a new method:

To use this in C# would be as simple as this:

AnimalsTableAdapter adapter = new AnimalsTableAdapter();
adapter.Add("Snappy", DateTime.Now, null, "Crocodile");

Simple, no?

In practice, you'd never do this though. What we want to do is put this interaction inside the Animal class, inside a method called Save().

So, here, it's passing the properties of the current animal object to the adapter. We could use this in code, thus:

Animal newAnimal = new Animal();
newAnimal.name = "Skippy";
newAnimal.dateOfBirth = DateTime.Now.AddYears(-10);
newAnimal.type = "Kangaroo";
newAnimal.Save();

Cool. Not only are we creating our own objects we're adding methods to them. Any .aspx pages which modify Animals now only need to call Save. They don't need to know about the adapter and we can change how the save method works from one place.

Now, look at this code:

Animal oldAnimal = AnimalFactory.GetAnimal(1);
oldAnimal.name = "Changed";
oldAnimal.Save();

Notice anything wrong with it based on what we've done so far?

You'd expect this code to load the animal whose ID is 1, change its name and then save it back to the database. It won't work though as our SP only INSERTs new entries. What the code above will do is add a new row with a new incremented ID, the name "Changed" but the same values in the other columns as already exist for animal with ID of 1.

What we need is an UPDATE Stored Procedure.

Updating Existing Entries

Following the same steps as before add a new SP with the SQL you see below:

We're passing the same data as before, along with the animal's ID, so that we know which row to UPDATE.

Back in the TableAdapter, following the same steps as before, add a new method that points to this new SP and call it "Change" or something like that.

Make sure you choose to have "no value" returned though:

Your TableAdapter should now look like this:

Back in the Animal class (Animal.cs file) update the Save method to look like this:

Which SP we call now depends on whether the Animal has an ID greater than zero. In effect this is like testing whether it's a new Animal. There's probably a more graceful way of doing this, but this works for now.

Now both of the code snippets mentioned earlier should work as expected.

Using With Forms

I was hoping to cover the creation of ASPX pages that contain forms to take user input and add or update Animals, but it's taken long enough to get this far, so that will have to wait for another day.

For now, if you feel like it, have a go at creating animals in your own pages. Perhaps add a new animal in the Page_Load event of Default.aspx and watch the table of animals grow each time you refresh the page. What fun.

I'll cover using Forms in the next post in the "series".