Inserting Records Using a DataSet Object

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

All this messing around with INSERT statements is all very well, and in some circumstances it can be a powerful technique, but there's another way of doing things. We saw in the last chapter that DataSet objects are very powerful, enabling us to represent part of a database in memory. In order to get information from the database into our DataSets, we had to use methods of a data adapter object - and we can use the same communication path to make information travel in the opposite direction. We can add records to a DataSet object, and then call the data adapter's Update() method to have them inserted into a database.

We can summarize the steps involved in inserting a new record using a DataSet object as follows:

  • Create a connection object.

  • Create a data adapter object.

  • Inform the data adapter about the connection we want to use.

  • Create a DataSet object.

  • Use the data adapter's Fill() method to execute the SELECT command and fill the dataset.

  • Create a new row using the NewRow() method exposed by the DataTable object.

  • Set the data row's fields with the values we wish to insert.

  • Add the data row to the data table with the Add() method of the DataRowCollection class.

  • Set the data adapter class's InsertCommand property with the INSERT statement that we want to use to insert the record. (We will see later that we can use a command builder object to create SQL commands automatically.)

  • Use the Update() method exposed by the data adapter to insert the new record into the database. Optionally, we can use the DataSet class's GetChanges() method to retrieve just the changes that occurred to the object after the filling process.

  • Use the AcceptChanges() method provided by the DataSet class in order to align the in-memory data with the physical data within the database.

As we saw in Chapter 5, DataSet objects are adept at dealing with XML, and it's also possible to insert new records into a database after reading them straight from an XML file. We would do this in the following way:

  • Create a connection object.

  • Create a data adapter object.

  • Inform the data adapter about the connection we want to use.

  • Create a DataSet object.

  • Use the ReadXml() method exposed by the DataSet object to bring data into the application from an XML file.

  • Set the data adapter class's InsertCommand property with the INSERT statement that we want to use to insert the record.

  • Use the Update() method exposed by the data adapter to insert the new record into the database.

We'll demonstrate this second way of using a DataSet object to insert information into a database in the second of the two examples that conclude this chapter.

Command Builder Classes

The ADO.NET library provides some classes that are capable of creating SQL INSERT, UPDATE, and DELETE statements automatically, based on the SELECT statement that was specified during the creation of the data adapter object. Command builder classes can be very useful when you have a simple SELECT statement, but sadly you can't use them when your data adapter object was created from a SELECT command that retrieves records from two or more tables joined together. In the following demonstration, you'll see how they work.

Try It Out - Inserting a New Record Using the DataSet Object

start example

In this example, we're going to create an ASP.NET page that will be capable of adding records to the Categories table contained in the Northwind database. We'll use a DataGrid control to show the records in the table, and a DataSet object to add a new record. Thanks to the DataGrid control's DataBind() method, we'll see the new record appear in the grid after the insertion process.

  1. Create a new text file in the webroot\ch06 folder, and call it DG_Insert.aspx.

  2. This is quite a long listing, so we'll take it in stages. First, the HTML code, which places the DataGrid along with a couple of text boxes, a button, and a RequiredFieldValidator on the page:

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head>     <title>DataGrid - Insert</title>   </head>   <body leftMargin="0" topMargin="0">     <form  method="post" runat="server">       <table               style="Z-INDEX: 110; LEFT: 5px; POSITION: absolute; TOP: 5px"              cellSpacing="0" cellPadding="0" width="300" border="0">         <tr>           <td colSpan="3">             <asp:DataGrid  runat="server"                     Width="728" Height="234" BorderColor="#CC9966"                     BorderStyle="None" BorderWidth="1"                     BackColor="White" CellPadding="4" EnableViewState="False">               <ItemStyle ForeColor="#330099" BackColor="White" />               <HeaderStyle Font-Bold="True"                            ForeColor="#FFFFCC" BackColor="#990000" />               <FooterStyle ForeColor="#330099" BackColor="#FFFFCC" />               <PagerStyle HorizontalAlign="Center"                            ForeColor="#330099" BackColor="#FFFFCC" />             </asp:DataGrid>           </td>         </tr>         <tr>           <td colSpan="3"></td>         </tr>         <tr>           <td colSpan="3">             <p>               <asp:Label  runat="server"                          Width="317" BackColor="Firebrick" ForeColor="White">                 Insert a new record...               </asp:Label>             </p>           </td>         </tr>         <tr>           <td colSpan="3"></td>         </tr>         <tr>           <td colSpan="3">             <asp:Label  runat="server" Width="85px">               Category</asp:Label>             <asp:TextBox  runat="server" Width="220px" />             <asp:RequiredFieldValidator  runat="server"                   ErrorMessage="Please insert the category name..."                   ControlToValidate="txtCategory" />           </td>         </tr>         <tr>           <td colSpan="3">             <asp:Label  runat="server" Width="85">               Description</asp:Label>             <asp:TextBox  runat="server" Width="220" />           </td>         </tr>         <tr>           <td colSpan="3">             <asp:Button  runat="server" OnClick="btnInsert_Click"                         Width="317" Height="22"                         BorderColor="#FFFFCO" BorderStyle="Solid"                         BackColor="Firebrick" ForeColor="White" Text="Insert" />           </td>         </tr>       </table>     </form>   </body> </html> 

  3. Next, we have the declaration of global variables for the connections, data adapter, and dataset objects, followed by the Page_Load() event handler:

     <script language="VB" runat="server"> Dim objConnection As SqlConnection Dim daNorthwind As SqlDataAdapter Dim dsNorthwind As DataSet Sub Page_Load(Source As Object, E As EventArgs)    Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")    objConnection = New SqlConnection(strConnection)    Dim strSQL As String = "SELECT CategoryID, CategoryName, Description " & _                           "FROM Categories"    daNorthwind = New SqlDataAdapter(strSQL, objConnection)    ' Create a command builder object in order to create    ' INSERT, UPDATE, and DELETE SQL statements automatically    Dim cb As New SqlCommandBuilder(daNorthwind)    ' Is the page being loaded for the first time?    If Not Page.IsPostBack Then      FillDataGrid()    End If End Sub 

  4. Following hard on the heels of Page_Load() is FillDataGrid(), which is called by the former and performs the function its name suggests. It's not dissimilar from the procedures with similar names that you saw in the previous chapter.

     Sub FillDataGrid()   ' Create a new dataset to contain categories' records   dsNorthwind = New DataSet()   ' Fill the dataset retrieving data from the database   daNorthwind.Fill(dsNorthwind)   ' Set the DataSource property of the DataGrid   dgNorthwind.DataSource = dsNorthwind.Tables(0).DefaultView   ' Bind the dataset data to the DataGrid   dgNorthwind.DataBind() End Sub 

  5. Finally, we have the handler for the button being clicked, which performs validation and then enters the new category into the database.

     Sub btnInsert_Click(Sender As Object, E As EventArgs)   ' If user has filled every text box correctly...   If Page.IsValid Then     ' Create a temporary dataset to contain the new record     Dim dsTemp As New DataSet()     ' Fill the temporary dataset     daNorthwind.Fill(dsTemp)     ' Create a new row     Dim r As DataRow = dsTemp.Tables(0).NewRow()     ' Add the category name, reading its value from the text box     r("CategoryName") = txtCategory.Text     ' Add the category description, reading its value from the text box     r("Description") = txtDescription.Text     ' Add the new row into the dataset's rows collection     dsTemp.Tables(0).Rows.Add(r)     ' Update the database using the temporary dataset     daNorthwind.Update(dsTemp)     ' Usually, you have to call the AcceptChanges() method in order to align the     ' dataset with records in the database. Because this is a temporary dataset,     ' we can omit this instruction.     ' dsTemp.AcceptChanges()     ' Refresh the data grid to display the new record     FillDataGrid()   End If End Sub </script> 

  6. With all of this code in place, you should be able to launch the page and see this:

    click to expand

  7. And after inserting a new category name and description (and pressing the Insert button), you'll see the new record appear at the bottom of the list:

    click to expand

    Don't worry if the CategoryID is different from the one you can see here. The value allocated by the database will vary, depending on how many times you've added and deleted categories from this table.

end example

How It Works

In the next chapter, we'll see how the DataGrid control provides some properties and methods that enable users to edit the rows it contains directly - you just select the row you want to modify, and the DataGrid will show text box fields to receive the new data. Sadly, however, the DataGrid control doesn't provide an automatic way to insert a new record, so we have to add input controls for each value to be added into the database. This is the reason for those two text box controls in the HTML code:

              <tr>                <td colSpan="3">                  <asp:Label  runat="server" Width="85px">                    Category</asp:Label>                  <asp:TextBox  runat="server" Width="220px" />                  <asp:RequiredFieldValidator  runat="server"                        ErrorMessage="Please insert the category name..."                        ControlToValidate="txtCategory" />                </td>              </tr>              <tr>                <td colSpan="3">                  <asp:Label  runat="server" Width="85">                    Description</asp:Label>                  <asp:TextBox  runat="server" Width="220" />                </td>              </tr> 

The DataGrid used in the example creates columns by reading their names from the SELECT statement we specified during creation of the data adapter object. Moreover, the DataGrid uses ItemStyle, HeaderStyle, FooterStyle and PagerStyle elements in order to give a more agreeable aspect to the grid.

                  <asp:DataGrid  runat="server"                          Width="728" Height="234" BorderColor="#CC9966"                          BorderStyle="None" BorderWidth="1"                          BackColor="White" CellPadding="4" EnableViewState="False">                    <ItemStyle ForeColor="#330099" BackColor="White" />                    <HeaderStyle Font-Bold="True"                                 ForeColor="#FFFFCC" BackColor="#990000" />                    <FooterStyle ForeColor="#330099" BackColor="#FFFFCC" />                    <PagerStyle HorizontalAlign="Center"                                 ForeColor="#330099" BackColor="#FFFFCC" />                  </asp:DataGrid> 

Moving on, the most interesting part of the Page_Load() event handler is the creation of the SqlCommandBuilder object. Rather unusually, having created the cb variable, we never use it again - but this line of code is essential, nonetheless. Without it, the call to the data adapter object's Update() method that takes place in the button-click handler would simply fail. The creation of this object, which takes our data adapter object as a parameter to its constructor, is key to the automatic creation of UPDATE, INSERT, and DELETE SQL statements that will result in any changes made to the dataset being reflected in the database:

     Sub Page_Load(Source As Object, E As EventArgs)       Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")       objConnection = New SqlConnection(strConnection)       Dim strSQL As String = "SELECT CategoryID, CategoryName, Description " & _                              "FROM Categories"       daNorthwind = New SqlDataAdapter(strSQL, objConnection)       ' Create a command builder object in order to create       ' INSERT, UPDATE, and DELETE SQL statements automatically       Dim cb As New SqlCommandBuilder(daNorthwind)       ' Is the page being loaded for the first time?       If Not Page.IsPostBack Then         FillDataGrid()       End If     End Sub 

In FillDataGrid(), we use the DataBind() method that's exposed by the DataGrid control in order to refresh the records in the grid. Also, thanks to the DataSource property of the DataGrid object, we can inform the grid about which part of the in-memory database we want to display.

     Sub FillDataGrid()       ' Create a new dataset to contain categories' records       dsNorthwind = New DataSet()       ' Fill the dataset retrieving data from the database       daNorthwind.Fill(dsNorthwind)       ' Set the DataSource property of the DataGrid       dgNorthwind.DataSource = dsNorthwind.Tables(0).DefaultView       ' Bind the dataset data to the DataGrid       dgNorthwind.DataBind()     End Sub 

Finally, when the user presses the Insert button, our first check is to verify that the rules of the RequiredFieldValidator control have been met. After that, we create a temporary DataSet that will eventually contain the record we want to insert in the database, and fill it with the records from the Categories table that we earlier read into the data adapter object.

     Sub btnInsert_Click(Sender As Object, E As EventArgs)       ' If user has filled every text box correctly...       If Page.IsValid Then         ' Create a temporary dataset to contain the new record         Dim dsTemp As New DataSet()         ' Fill the temporary dataset         daNorthwind.Fill(dsTemp) 

Last of all, we have to add a new row to the temporary DataSet, specifying the category name and the description provided by the user on the ASP.NET page. With that done, calling the Update() method (now) provided by the SqlDataAdapter object will insert the record in the database. We don't have to open the connection and close it after the record insertion, because the SqlDataAdapter object manages everything automatically.

         ' Create a new row         Dim r As DataRow = dsTemp.Tables(0).NewRow()         ' Add the category name, reading its value from the text box         r("CategoryName") = txtCategory.Text         ' Add the category description, reading its value from the text box         r("Description") = txtDescription.Text         ' Add the new row into the dataset's rows collection         dsTemp.Tables(0).Rows.Add(r)             ' Update the database using the temporary dataset             daNorthwind.Update(dsTemp) 

And now we can call the FillDataGrid() method in order to refresh the data in the grid.

             ' Refresh the data grid to display the new record             FillDataGrid()           End If 

By producing the necessary INSERT statement on our behalf, the ADO.NET SqlCommandBuilder object has enabled us to update the database with our new category without the need to construct another SQL query - something that you'll surely agree to be a good thing!

Try It Out - Using a Calendar, a DataSet, and XML to Insert Records

start example

In the last example of this chapter, we'll create an ASP.NET page that displays a DataGrid control containing records from the Employees table of the Northwind database. Also on the page is a Calendar control that we'll use to specify the hiring date of a new employee. When a date has been selected, we'll use a DataSet to add the new employee to the database, along with two more new employees whose details will be read from an XML file.

  1. The name of the ASPX file for this example will be Cal_Insert.aspx, so generate a new text file with that name and place it in the ch06 folder.

  2. Once again, we'll start the code listing with the HTML section, which this time has a form containing information for formatting a DataGrid control and a Calendar control. The DataGrid contains BoundColumn controls like the ones you saw in the previous chapter.

     <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.SqlClient" %> <html>   <head>     <title>Inserting Calendar and XML Data</title>   </head>   <body>     <form  runat="server" method="post">       <table               style="Z-INDEX: 101; LEFT: 7px; POSITION: absolute; TOP: 7px"              cellSpacing="0" cellPadding="0" width="300" border="0">         <tr>           <td style="WIDTH: 681" colSpan="2">             <asp:DataGrid  runat="server" Width="479"                     Height="191" BorderColor="#CC9966" BorderWidth="1"                     BorderStyle="None" BackColor="White" CellPadding="4"                     DataKeyField="EmployeeID" AutoGenerateColumns="False">              <SelectedItemStyle Font-Bold="True"                                 ForeColor="#663399" BackColor="#FFCC66" />               <ItemStyle ForeColor="#330099" BackColor="White" />               <HeaderStyle Font-Bold="True"                            ForeColor="#FFFFCC" BackColor="#990000" />               <FooterStyle ForeColor="#330099" BackColor="#FFFFCC" />               <Columns>                <asp:BoundColumn DataField="FirstName" HeaderText="First Name" />                <asp:BoundColumn DataField="LastName" HeaderText="Last Name" />                <asp:BoundColumn DataField="HireDate" HeaderText="Hire Date" />               </Columns>               <PagerStyle HorizontalAlign="Center"                           ForeColor="#330099" BackColor="#FFFFCC" />             </asp:DataGrid>           </td>         </tr>         <tr>           <td style="WIDTH: 681; HEIGHT: 49" colSpan="2">             <asp:Label  runat="server" Height="45" Width="480"                        BackColor="Maroon" Font-Bold="True" ForeColor="#FFE0C0">               By selecting a date from the calendar, you               will insert a new record in the database.             </asp:Label>           </td>         </tr>         <tr>           <td style="WIDTH: 681" colSpan="2">             <asp:Calendar  runat="server"                   OnSelectionChanged="calHire_SelectionChanged"                   BorderColor="#FFCC66" Height="153" Width="479"                   BackColor="#FFFFCC" BorderWidth="1" ForeColor="#663399"                   EnableViewState="False" ShowGridLines="True"                   Font-Names="Verdana" Font-Size="8pt">               <TodayDayStyle ForeColor="White" BackColor="#FFCC66" />               <SelectorStyle BackColor="#FFCC66" />               <NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />               <DayHeaderStyle Height="l" BackColor="#FFCC66" />               <SelectedDayStyle Font-Bold="True" BackColor="#CCCCFF" />               <TitleStyle Font-Size="9pt" Font-Bold="True"                           ForeColor="#FFFFCC" BackColor="#990000" />               <OtherMonthDayStyle ForeColor="#CC9966" />             </asp:Calendar>           </td>         </tr>         <tr>           <td style="WIDTH: 681" colSpan="2">             <asp:Label  runat="server" ForeColor="Red" />           </td>         </tr>       </table>     </form>   </body> </html> 

  3. The Page_Load() event handler is little changed from the one in the last example, save for the content of the initial SQL query, which retrieves employee details rather than category information.

     <script language="VB" runat="server"> Dim objConnection As SqlConnection Dim daNorthwind As SqlDataAdapter Dim dsNorthwind As DataSet Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   objConnection = New SqlConnection(strConnection)   Dim strSQL As String = "SELECT EmployeeID, LastName, FirstName, HireDate " & _                          "FROM Employees"   daNorthwind = New SqlDataAdapter(strSQL, objConnection)   Dim cb As New SqlCommandBuilder(daNorthwind)   If Not Page.IsPostBack Then     FillDataGrid()   End If End Sub 

  4. Because we've used the same variable names, the FillDataGrid() method for this example is exactly the same as the one we had last time, so we won't reproduce it here. Instead, we can move on to the calHire_SelectionChanged() event handler, where all the action takes place:

     Sub calHire_SelectionChanged(Source As Object, E As EventArgs)   ' If the user chooses a date in the future, an error message is displayed   If calHire.SelectedDate <= Today() Then     ' Create a temporary dataset to contain the new record     Dim dsTemp As New DataSet()     dsTemp.ReadXml("C:\BegAspNetDb\datastores\Employees.xml")     ' Create a new row     Dim r As DataRow = dsTemp.Tables(0).NewRow()     r("LastName") = "Ferracchiati"     r("FirstName") = "Fabio C."     r("HireDate") = Convert.ToDateTime(calHire.SelectedDate)     ' Add the new row into the dataset's rows collection     dsTemp.Tables(0).Rows.Add(r)     ' Update the database using the temporary dataset     daNorthwind.Update(dsTemp, "EMPLOYEE")     ' Refresh the data grid to display the new record     FillDataGrid()   Else     Response.Write("Hire date can't be in the future!")     Response.End()   End If End Sub </script> 

  5. In keeping with the details of the above listing, we also need to create an XML file in our datastores folder, called Employees.xml. It takes the following form:

     <?xml version="1.0" standalone="yes"?> <EMPLOYEES>   <EMPLOYEE>     <LastName>Griffiths</LastName>     <FirstName>Derek</FirstName>     <HireDate>1996-08-13TOO:00:00.0000000+01:00</HireDate>   </EMPLOYEE>   <EMPLOYEE>     <LastName>Holmes</LastName>     <FirstName>Eamonn</FirstName>     <HireDate>1995-11-22TOO:00:00.0000000+01:00</HireDate>   </EMPLOYEE> </EMPLOYEES> 

  6. Now, when we load our page, we should be presented with the following:

    click to expand

  7. And when we select a date (which should be prior to the current date), three new records will be added to the database and displayed in the grid:

    click to expand

end example

How It Works

Let's start by examining the ASP.NET Calendar control. We want to insert a new record when the user selects a date, so we specify a handler for the OnSelectionChanged event. Moreover, we've specified some attributes that change the default appearance of the calendar:

                    <asp:Calendar  runat="server"                          OnSelectionChanged="calHire_SelectionChanged"                          BorderColor="#FFCC66" Height="153" Width="479"                          BackColor="#FFFFCC" BorderWidth="1" ForeColor="#663399"                          EnableViewState="False" ShowGridLines="True"                          Font-Names="Verdana" Font-Size="8pt">                      <TodayDayStyle ForeColor="White" BackColor="#FFCC66" />                      <SelectorStyle BackColor="#FFCC66" />                      <NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />                      <DayHeaderStyle Height="1" BackColor="#FFCC66" />                      <SelectedDayStyle Font-Bold="True" BackColor="#CCCCFF" />                      <TitleStyle Font-Size="9pt" Font-Bold="True"                                  ForeColor="#FFFFCC" BackColor="#990000" />                      <OtherMonthDayStyle ForeColor="#CC9966" />                    </asp:Calendar> 

When the user selects a date in the calendar control, the calHire_SelectionChanged() event handler is called. The first operation performed is to check that the date is less than or equal to the current date; if it's not, an error will be displayed.

    Sub calHire_SelectionChanged(Source As Object, E As EventArgs)      ' If the user chooses a date in the future, an error message is displayed      If calHire.SelectedDate <= Today() Then      ' Data manipulation code, discussed in a moment.      Else        Response.Write("Hire date can't be in the future!")        Response.End()      End If    End Sub 

In the main body of the event handler, we first fill a new DataSet object with the contents of the Employees.xml file, using the ReadXml() method:

        ' Create a temporary dataset to contain the new record        Dim dsTemp As New DataSet()        dsTemp.ReadXml("C:\BegAspNetDb\datastores\Employees.xml") 

And now we can treat this DataSet in the same way as we treated the one in the previous example. We have to add a new row to the Rows collection, specify the fields we want to insert in the database, and finally call the Update() method specifying the name of the table contained in the XML file.

        ' Create a new row        Dim r As DataRow = dsTemp.Tables(0).NewRow()        r("LastName") = "Ferracchiati"        r("FirstName") = "Fabio C."        r("HireDate") = Convert.ToDateTime(calHire.SelectedDate)        ' Add the new row into the dataset's rows collection        dsTemp.Tables(0).Rows.Add(r)        ' Update the database using the temporary dataset        daNorthwind.Update(dsTemp, "EMPLOYEE") 

In the interests of brevity, we've cut down the complexity of this demonstration by writing some of the new data into the code of the application. Hopefully, though, you can begin to see how you might assemble the techniques we've examined so far to produce something rather more complicated. For example, the hard-wired data that was entered into the database in this example could certainly be replaced by values from text boxes or drop-down list controls, if that's what you needed to do. Do experiment with the things you've seen so far, and see what you can come up with.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net