Accessing and Updating Data in ASP.NET 2.0: Inserting Data
By Scott Mitchell
A Multipart Series on ASP.NET 2.0's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET 2.0's new data
source controls.
Data Source Control Basics - explores the concepts
and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
Accessing Database Data - shows how to use the
SqlDataSource and AccessDataSource controls to query data from a relational database.
Filtering Database Data with Parameters - learn how to
retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page,
session variables, and so on.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
Inserting Data - learn how to insert data using
a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
Deleting Data - see how to delete data using
a SqlDataSource control. Also looks at how to programmatically cancel a delete.
Updating Basics - learn the basics of updating database data using a
SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
Customizing the Editing Interface - see how to
customize the GridView's columns to provide a customized editing interface that includes input validation and alternative
user interface elements.
Handling Database NULL Valuese - explore how to
extend the GridView's customized editing interface to handle database NULL values.
Introduction
ASP.NET 2.0's data source and data Web controls make working with data much easier than in previous versions. These controls
allow for a declarative approach to working with data, without needing to write any cumbersome data access code. The
articles we have explored in the series thus far have focused on understanding the core concepts of the data source
controls and how to retrieve data from the database. In addition to retrieving data, the data source controls can also
insert, update, and delete data.
In this article we will focus on using the SqlDataSource control to insert records into a database. We will look at three examples:
using a SqlDataSource to insert a record using values entered by the user through various TextBoxes, DropDownLists, and
other Web controls on an ASP.NET page; inserting using a SqlDataSource and a DetailsView
control; and determining the value of the just-inserted record's IDENTITY column. Read on to learn more!
An Overview of Inserting Data Using the Data Source Controls
The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities.
In a nutshell, all three have an Insert method that, when invoked, kicks off the following inserting workflow:
The data source's Inserting event is fired
The inserting "action" occurs
The data source's Inserted event is fired
The inserting "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action
involves connecting to the specified database and executing the INSERT statement specified by the control's
InsertCommand
property. For the ObjectDataSource, the inserting "action" involves creating an instance of the data source's underlying
object and invoking the specified InsertMethod. This article focuses on the SqlDataSource control; for a detailed
look at inserting with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my
Working with Data in ASP.NET 2.0 tutorial series.
Let's explore the inserting "action" for the AccessDataSource and SqlDataSource controls in more detail. The
INSERT statement or stored procedure specified by the control's InsertCommand uses a
parameterized query. That is, if you use an ad-hoc SQL statement for the InsertCommand, that INSERT
statement will use parameters like so:
In the Filtering Database Data with Parameters article,
we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products
WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified
via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded
value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user
to enter a price bound in a TextBox; from the querystring; from session state; and so on.
Likewise, the parameter values in the INSERT statement are assigned based on the parameters in the data source
control's InsertParameters collection, and these parameters can use the same sources as the SelectParameters.
The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform
their data access. That is, they connect to the database using a SqlConnection or OleDbConnection
object and specify the command text and parameters via a SqlCommand or OleDbCommand object.
Given this information, the inserting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:
The data source's Inserting event is fired
SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
The command object's CommandText property is assigned the data source control's InsertCommand property
The parameters in the data source control's InsertParameters collection are added the command object's Parameters collection
A connection to the database is established and the command is executed, thereby inserting the data
The data source's Inserted event is fired
The remainder of this article examines three inserting scenarios using the SqlDataSource control: inserting data through a
manually-created Web form; inserting data using the DetailsView control; and retrieving the value of the just-inserted record's
IDENTITY column. The full code for these demos is available in the download at the end of this article.
Inserting Data Using a Manually-Created Web Form
The demos available at the end of this article illustrate different techniques for inserting data into the Products
table of the Northwind database. The Products table contains a number of columns. Each product record is
uniquely identified by its ProductID, which is an AutoNumber/IDENTITY column. When inserting a record into
this table, the only two columns that are required are ProductName and Discontinued; all other
columns can accept a value of NULL.
Imagine that we were tasked with creating a page that allowed users to add new records to the Products table
by specifying the new item's name, category, price, and discontinued status. We could create a simple Web Form that included
TextBoxes, a DropDownList, and a CheckBox control to collect these input fields, along with an "Add Product" Button control
that, when clicked, would insert the new product into the database.
In addition to these user input controls we could also add a SqlDataSource control to handle the actual insertion. We could
set this control's InsertCommand to the following INSERT statement:
Note the use of the parameters following the VALUES keyword. These sources for these parameters' values would
simply be the user input Web controls on the page. Map these parameters' values in the InsertParameters
collection using ControlParameters that point to the appropriate Web controls on that page. There are a
variety of ways to accomplish this. From the Designer, click on the SqlDataSource and go to the Properties window. There
you will see an InsertQuery option that, if clicked, displays the Command and Parameter Editor shown below. Here you can
specify the InsertCommand, the parameters, and their source. Note that each of the four parameters uses a Control
as its Parameter source, with the ControlID drop-down list set to the appropriate Web control on the page.
Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:
Once the Web controls have been added to the page and the SqlDataSource's InsertCommand and
InsertParameters properties have been correctly configured, inserting a new record is as simple as calling
the data source control's Insert() method. That is, the only code you need to write is the following line
of code (which would be placed in the "Add Product" Button's Click event handler):
Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click
'Add the new product! AddProductDataSource.Insert()
End Sub
Inserting Data Using a DetailsView Control
A number of new data Web controls were introduced with ASP.NET 2.0. These
include the GridView, DetailsView, and FormView, among others. The DetailsView and FormView controls display information about
one record at a time (unlike the GridView, which displays information about a set of records). The DetailsView and FormView
controls can also be configured to display an inserting interface. In short, you can use the DetailsView or FormView controls
to create an interface for inserting data into the database without having to write a line of code!
The DetailsView vs. the FormView Control
The DetailsView and FormView controls have a lot in common - they both display one record at a time and can display interfaces
for inserting and updating data. The difference between the two is that the DetailsView control is composed of
DataFields (BoundFields, CheckBoxFields, TemplateFields, and so on), just like the GridView. This results in a very boxy appearance.
The FormView, on the other hand, uses templates instead of DataFields; consequently, it allows for a much more flexible layout
of its display, inserting, and updating interfaces.
Start by adding a SqlDataSource control to the page and use the same InsertCommand as in the previous example:
Next, add the parameters to the control's InsertParameters collection. Rather than using ControlParameters, use the default
Parameter object. Also, the DetailsView we will create for this demo will not include an interface for the
user to specify the category. Therefore, set the corresponding Parameter object's DefaultValue to
"1". This will assign every product added through this page to the Beverages category.
Next, add a DetailsView to the page and set its DataSourceID property to the ID of the SqlDataSource
control (AddProductDataSource). From the DetailsView's smart tag, check the "Enable Inserting" checkbox. This adds
a CommandField to the DetailsView and sets its ShowInsertButton property to True. A CommandField with this
configuration displays a "New" Button when the DetailsView control is in its ReadOnly
mode. When the "New" Button is clicked, a postback ensues and the DetailsView is shifted into its Insert mode, causing
the CommandField to display "Insert" and "Cancel" Buttons.
The SqlDataSource control does not contain a value for its SelectCommand, so no data will be displayed in the DetailsView.
In fact, for this example we want the DetailsView to always be in an insertable mode (that is, we don't want to make the user
have to click "New" to start adding a new record). Set the DetailsView's DefaultMode property to Insert
to indicate that the DetailsView control should display just its inserting interface.
Next, add two BoundFields and a CheckBoxField to the DetailsView, setting the HeaderText and DataField
properties so that they are bound to the ProductName, UnitPrice, and Discontinued columns
used by the SqlDataSource. Finally, set the AutoGenerateRows property to False.
You can accomplish these two tasks from the Fields dialog box or by manually entering the control's declarative markup.
To use the Fields dialog box, click the Edit Fields link from the DetailsView's smart tag. Add the two BoundFields and CheckBoxField
and set their properties from the list on the right. To set the AutoGenerateRows property to False, simply uncheck
the "Auto-generate fields" checkbox in the lower left corner.
Alternatively, you can specify the DetailsView's fields and set the AutoGenerateRows property to False through
the declarative syntax:
That's all there is to it! When a user visits this page and enters the name, price, and discontinued status of a product
and clicks Insert, a postback occurs. The DetailsView automatically assigns the values assigned to its input controls
to the SqlDataSource's corresponding InsertParameters before starting the inserting workflow. The net result is that a new
record is inserted into the database without writing a lick of code and without having to manually map the SqlDataSource's
InsertParameters to their sources (since the DetailsView does this for us automatically when Insert is clicked).
This example illustrates only the simplest facilities of the DetailsView and omits important steps in a real-world application,
such as input validation and customizing the inserting interface. For example, since the ProductName column is
required, the insert will fail if the user leaves this textbox empty. Likewise, if the user puts in an invalid unit price value (like "expensive!"),
an error will occur when attempting to insert the illegal value into the database. The download at the end of this article
includes another DetailsView example that illusrated adding validation controls and customizing the inserting interface
to prompt the user for the new product's category via a drop-down list of available category choices. For more information
on customizing the DetailsView's inserting interface, see
Customizing the Data Modification Interface (VB Version)
(C# version).
Inserting and Returning the Value of the Just-Inserted Record's IDENTITY Column
Most database tables provide some mechanism to uniquely identify each record. There are a variety of approaches, but a very
common one is the use of an IDENTITY column, which is also referred to as an AutoNumber. An IDENTITY
column is one whose value is automatically assigned by the database system when a new record is added to the table. These values
start at some seed (usually 1) and increment by some specified amount with each new record (usually incremented by 1). Therefore,
if you add three new records to the table, the IDENTITY column values for those first three records will be
1, 2, and 3, respectively.
When using IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted
record's IDENTITY column. Perhaps after inserting a new record you want to automatically whisk the user to
a details page where you need to pass along the IDENTITY column value through the querystring, or maybe you
want to add additional records in a child table and need the just-inserted parent record's IDENTITY column value
to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the
SCOPE_IDENTITY() keyword to get the
IDENTITY column value of the just-insert record.
In order to pull back this information when using the SqlDataSource we need to do the following:
Create a stored procedure that returns the just-inserted record's IDENTITY column value using an
OUTPUT parameter. See Retrieving Scalar Data from a
Stored Procedure for more information on this topic.
Configure the SqlDataSource to use this stored procedure. This involves updating the InsertCommand to
the name of the stored procedure created from step 1, setting the data source control's InsertCommandType to
StoredProcedure, and adding an output parameter to the InsertParameters collection.
To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's Inserted
event. Recall that this event fires after the insert "action" has been performed. Once we have the IDENTITY
value of the just-inserted record we can use it as needed.
In the database included in the download at the end of this article you will find a stored procedure named AddProductAndReturnNewProductIDValue
that accepts four input parameters and has an OUTPUT parameter (@NewProductID). As the following
T-SQL syntax shows, this stored procedure inserts a new record into Products and then assigns the value returned
by SCOPE_IDENTITY() to @NewProductID:
ALTER PROCEDURE dbo.AddProductAndReturnNewProductIDValue
(
@ProductName nvarchar(40),
@CategoryID int,
@UnitPrice money,
@Discontinued bit, @NewProductID int OUTPUT
)
AS
-- Insert the record into the database
INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)
-- Read the just-inserted ProductID into @NewProductID
SET @NewProductID = SCOPE_IDENTITY()
Next, update the SqlDataSource to use the AddProductAndReturnNewProductIDValue as its InsertCommand
instead of an ad-hoc SQL statement. Also, add an ouput parameter to the InsertParameters collection. Note that the output
parameter in the InsertParameters collection is a Parameter object whose Direction property is
set to Output:
Adding an output parameter to the SqlDataSource's InsertParameters collection adds an output parameter to the Parameters
collection of the internal SqlCommand object used by the data source control during the inserting "action." The
value of this parameter can be examined in the Inserted event handler. As the following event handler code
shows, the internal SqlCommand object is accessible through the e.Command property in the
event handler. Here we can grab the specific parameter instance and insepct its Value property to determine the
IDENTITY column value of the just-inserted record:
Protected Sub AddProductDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles AddProductDataSource.Inserted
'Read the value of the @NewProductID OUTPUT parameter
Dim newProductID As Integer = Convert.ToInt32(e.Command.Parameters("@NewProductID").Value)
'Display confirmation message
NewProductAddedMsg.Text = String.Format("Product {0} has been added to the database... This new product's ProductID value is {1}...", ProductName.Text, newProductID)
End Sub
Conclusion
In this article we looked at how to use the SqlDataSource to insert data into a database. We worked through three examples:
inserting using a manually-created Web Form; inserting from a DetailsView control; and retrieving the IDENTITY
column value of the just-inserted record. In all three cases, the SqlDataSource control encapsulates many of the data access
tasks, such as connecting to the database, creating the command object, and executing the parameterized query. In future
installments of this article series we will take a similar in-depth look at updating and deleting.
A Multipart Series on ASP.NET 2.0's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data.
These controls allow page developers to declaratively access and modify data without writing any
code to perform the data access. This article is one in a series of articles on ASP.NET 2.0's new data
source controls.
Data Source Control Basics - explores the concepts
and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
Accessing Database Data - shows how to use the
SqlDataSource and AccessDataSource controls to query data from a relational database.
Filtering Database Data with Parameters - learn how to
retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page,
session variables, and so on.
Creating Custom Parameter Controls - learn how
to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
Inserting Data - learn how to insert data using
a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
Deleting Data - see how to delete data using
a SqlDataSource control. Also looks at how to programmatically cancel a delete.
Updating Basics - learn the basics of updating database data using a
SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
Customizing the Editing Interface - see how to
customize the GridView's columns to provide a customized editing interface that includes input validation and alternative
user interface elements.
Handling Database NULL Valuese - explore how to
extend the GridView's customized editing interface to handle database NULL values.
