| | 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 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. -
Create a new text file in the webroot\ch06 folder, and call it DG_Insert.aspx. -
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> -
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 -
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 -
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> -
With all of this code in place, you should be able to launch the page and see this: -
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: 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. | 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 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. -
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. -
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> -
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 -
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> -
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> -
Now, when we load our page, we should be presented with the following: -
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: | 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. |