logo

ASP.NET: Adding and Editing Database Entries

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:

image

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

image

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:

image

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:

image

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).

image

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

image

Click Finish and you're done!

Our TableAdapter now has a new method:

image

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().

image

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:

image

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:

image

Your TableAdapter should now look like this:

image

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

image

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".

Comments

    • avatar
    • Emily
    • Wed 9 Feb 2011 03:59 AM

    Hi Jake - been reading your site for years and thought I'd comment for once instead of just lurking. Just wanted to say thanks for a great series of articles on ASP.NET. I'm a long time Domino dev but looking like I'll be getting into ASP.NET and Sharepoint as a means of keeping the money coming in. I enjoy the challenge of learning new technologies but it's always difficult to figure out the ASP.NET way of doing things when you're so used to using the Domino way of doing things, so it's great to see things explained by someone coming from a similar background. I think the only downside of all this is not being able to stop comparing the LS editor in Designer to the Visual Studio IDE. I think Designer is heading in the right direction, but it's got quite a long way to go yet!

    Emily.

      • avatar
      • Jake Howlett
      • Wed 9 Feb 2011 04:05 AM

      Hi Emily,

      Thank you for letting me know. It's messages like yours that have kept me going over the years.

      You're right. VS's IDE is so, so much better than anything else out there. Makes you never want to go back.

    • avatar
    • Stephen Hood
    • Fri 11 Feb 2011 08:18 AM

    Jake you may be interested in this video about CouchDb for .Net developers.

    http://oreillynet.com/pub/e/1790

    Especially once you tire of the whole schema management treadmill of relational db's and are pining for the days of a flexible storage container like NSF :)

    Actually with your javascript/html/css/json knowledge you can probably bypass .Net and store everything inside CouchDb with nothing but all "web" technologies.

    With recent Couch/Membase merger I think they are going to explode as a key infrastructure of many many up and coming web companies in the near future.

    If you can put up with the background noise there are some very good technical insights on this video..

    An Intro to CouchDB: What caught Ubuntu's eye

    http://www.youtube.com/watch?v=2R1qf5_6Zt4&feature=related

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:


About This Page

Written by Jake Howlett on Mon 7 Feb 2011

Share This Page

# ( ) '

Comments

The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content