Inserting Data with ASP.NET and ADO.NET

Chapter 6 - Creating and Inserting Records
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

When we use SQL statements to insert records into one or more database tables, we have to respect some rules. In this section, we'll look at some of the basic theory of inserting data with ASP.NET and ADO.NET:

• Understanding primary keys and foreign keys

• Providing mandatory fields

• Obeying the syntax of SQL's INSERT keyword

It All Looks so Simple

You may recall that we took a very brief look at creating and inserting records back when we first started talking about relational databases and the SQL language in Chapter 2. We even went as far as to show the syntax for a straightforward INSERT operation:

    INSERT INTO Shippers    (CompanyName, Phone)    VALUES ('Wrox Deliveries', '[phone]') 

Essentially, all we have to do in this chapter is to look at the different ways of assembling and then executing statements like this using ADO.NET code in our ASP.NET applications. Whenever you're adding new records to a database, though, there are some important considerations to bear in mind. The operation isn't quite as straightforward as it first appears.

Primary Keys

The first potential obstacle that we need to overcome is the rules concerning primary keys. As you know, a primary key uniquely identifies a record within a table, and any attempt to insert a duplicate will always cause an error. So when we insert a new record, we have to assure ourselves that we have provided that record with a valid and unique primary key.

Auto-Increment Primary Keys

Often, you'll encounter primary keys that have been defined as auto-increment fields. This can be very useful when the primary key doesn't contain significant data (an ISBN is an example of a primary key that does contain significant data), but each record still needs to have a unique identifier. If you were to take a look at the Northwind database through MS Access, you'd see that the Customers table is defined like this:

While the Categories table looks like this:

In this second table, the checkmark in the Identity column instructs the database to increment the primary key field automatically, by the value specified in the Identity Increment column. The Identity Seed represents the initial value that will be assigned to this field in the first record to be inserted.

When we're dealing with this kind of field from SQL, we can omit its name from the INSERT statement, safe in the knowledge that a value will be generated on our behalf:

    INSERT INTO Categories    (CategoryName, Description)    VALUES ('Books/Magazines', 'Some books, some magazines') 

If we actually tried to enter a value into this field, we'd get an error. If a table that you're dealing with has an auto-increment primary key, steer well clear of that field, and let the database do the work.

Foreign Keys

The second stumbling block to deal with comes when we have foreign keys to consider. As you may recall, a foreign key in one table is related to the primary key of another table, in such a way that it's only possible to create records containing the foreign key for which the value of the key field is equal to one of the values already present in the table containing the primary key. In other words, an operation that attempts to add a record to a table containing a foreign key will fail, unless the value of the key field matches one of the primary key values in the related table.

Relating two or more tables in this fashion can be quite advantageous. Imagine, for example, a database in which one table holds details about books: their ISBNs, titles, prices, and so on. Another table holds details of authors: their names, contact information, and the ISBNs of books they've written. By making the ISBN a primary key in the book table, and a foreign key in the author table, we introduce a useful constraint: no author can be added to the author table until a book they've written is added to the book table. To put that into SQL code, it's illegal to execute a statement like this:

    INSERT INTO tabAuthor    (ISBN, Author)    VALUES ('1-861005-92-X', 'Fabio Claudio Ferracchiati') 

Until you've first executed a statement like this:

    INSERT INTO tabBook    (ISBN, Title, Price)    VALUES ('1-861005-92-X', 'Data-Centric .NET Programming with C#', '$59.99') 

If you violate the rules of a primary key-foreign key relationship during an operation in your application, that operation will fail.

Mandatory Fields

The third and final thing to worry about when inserting new records regards mandatory fields - database columns for which we must always provide a value. Primary keys are always mandatory, but other fields can be mandatory too. Going back to our table of books in the last section, the ISBN is the primary key, but it seems quite reasonable to insist that all books should have a title, too.

In the Categories table of the Northwind database, two of the fields are mandatory, while the other two are not. In the Access screenshot below, this situation is reflected by the absence or presence of checkmarks in the Allow Nulls column. A field that may not be null is a mandatory field.

Clearly, when you're adding records to a table programmatically, you must be sure to provide values for all mandatory fields - unless, of course, you're dealing with a field whose values are generated automatically.

Formalizing the Syntax

The discussion so far has included a few snippets of SQL code, but just before we attempt our first proper example, we should take a look at the syntax of INSERT statements in a fairly formal way. The generic form of an INSERT statement may be represented as follows:

    INSERT [INTO]    { table_name }    {      [(column_list) ]      {        VALUES ( {DEFAULT | NULL | expression} [ ,...n] )      }    } 

Where:

• INSERT is the SQL command

• [INTO] is an optional keyword that we can include before the table name

• table_name is the name of the table in which we want to insert our record

• column_list represents a comma-separated list of column names that we'll provide values for. This list can be omitted if we provide values for every column, in the same order as they appear in the table specification. When we have an auto-increment primary key, we don't specify its value - it is inserted automatically.

• VALUES introduces the list of data values to be inserted. There must be one data value for each column in column_list or, when the column list is not provided, in the table. The comma-separated VALUES list must be enclosed in parentheses.

With all of these pieces in place, we can at last begin to attack the problem of creating records in a database using ADO.NET.