Accessing and Updating Data in ASP.NET: Inserting DataBy Scott Mitchell
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
Insertmethod that, when invoked, kicks off the following inserting workflow:
- The data source's
Insertingevent is fired
- The inserting "action" occurs
- The data source's
Insertedevent is fired
INSERTstatement specified by the control's
InsertCommandproperty. 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
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
object and specify the command text and parameters via a
Given this information, the inserting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:
- The data source's
Insertingevent is fired
OleDbCommand) objects are created
- The command object's
CommandTextproperty is assigned the data source control's
- The parameters in the data source control's InsertParameters collection are added the command object's
- A connection to the database is established and the command is executed, thereby inserting the data
- The data source's
Insertedevent is fired
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
Productstable of the Northwind database. The
Productstable 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
Discontinued; all other columns can accept a value of
Imagine that we were tasked with creating a page that allowed users to add new records to the
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
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
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
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
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):
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
"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
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
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
to indicate that the DetailsView control should display just its inserting interface.
Next, add two BoundFields and a CheckBoxField to the DetailsView, setting the
properties so that they are bound to the
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)
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
IDENTITYcolumn, which is also referred to as an AutoNumber. An
IDENTITYcolumn 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
IDENTITYcolumn values for those first three records will be 1, 2, and 3, respectively.
IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted
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
IDENTITYcolumn value using an
OUTPUTparameter. 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
InsertCommandto the name of the stored procedure created from step 1, setting the data source control's
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
Insertedevent. Recall that this event fires after the insert "action" has been performed. Once we have the
IDENTITYvalue of the just-inserted record we can use it as needed.
AddProductAndReturnNewProductIDValuethat accepts four input parameters and has an
@NewProductID). As the following T-SQL syntax shows, this stored procedure inserts a new record into
Productsand then assigns the value returned by
Next, update the SqlDataSource to use the
AddProductAndReturnNewProductIDValue as its
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
Adding an output parameter to the SqlDataSource's InsertParameters collection adds an output parameter to the
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:
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
IDENTITYcolumn 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.
Until then... Happy Programming!