ASP.NET gridview에 데이터 입력하기

detailsview에는 추가 하는 옵션이 있는데, gridview에는 그 기능이 없었다.

구글 검색을 했는데, 기막힌 방법이 있어서 잊어버리기 전에 포스팅한다.

이 한줄이 시간을 세이브 시켰다.

Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click    'Add the new product!    AddProductDataSource.Insert() End Sub

출처: http://www.4guysfromrolla.com/articles/050207-1.aspx

Accessing and Updating Data in ASP.NET: Inserting Data

By Scott Mitchell

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:

1. The data source's Inserting event is fired
2. The inserting "action" occurs
3. 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:

INSERT INTO TableName (ColumnName1, ColumnName2, ..., ColumnNameN) VALUES (@Parameter1, @Parameter2, ..., @ParameterN)

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:

1. The data source's Inserting event is fired
2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
3. The command object's CommandText property is assigned the data source control's InsertCommand property
4. The parameters in the data source control's InsertParameters collection are added the command object's Parameters collection
5. A connection to the database is established and the command is executed, thereby inserting the data
6. 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:

INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

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:

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."       InsertCommand="INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"       ProviderName="...">    <InsertParameters>       <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />       <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />       <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />       <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />    </InsertParameters> </asp:SqlDataSource>

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:

INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

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.

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."       InsertCommand="INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"       ProviderName="...">    <InsertParameters>       <asp:Parameter Name="ProductName" Type="String" />       <asp:Parameter Name="CategoryID" DefaultValue="1" />       <asp:Parameter Name="UnitPrice" Type="Decimal" />       <asp:Parameter Name="Discontinued" Type="Boolean" />    </InsertParameters> </asp:SqlDataSource>

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:

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="AddProductDataSource"       DefaultMode="Insert" CellPadding="4" ForeColor="#333333" GridLines="None">    <Fields>       <asp:BoundField DataField="ProductName" HeaderText="Product Name:" />       <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price:" />       <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued:" />       <asp:CommandField ShowInsertButton="True" />    </Fields> </asp:DetailsView>

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:

1. 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.
2. 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.
3. 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:

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."       InsertCommand="AddProductAndReturnNewProductIDValue"       ProviderName="..." InsertCommandType="StoredProcedure">    <InsertParameters>       <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />       <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />       <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />       <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />        <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />    </InsertParameters> </asp:SqlDataSource>

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.

Until then... Happy Programming!

By Scott Mitchell