Inserting, Updating, and Deleting Data
Another part of dealing with data in ASP.NET applications is being able to insert new records, update existing records, and delete records from your database tables. With these three types of functions that you can execute against your database, you will be able to manipulate the contents of your database to do just about anything you want.
ADO.NET provides a bunch of ways for you to perform these functions, from simple to automated, to providing you with the tools to be very detailed and specific about how your database and ASP.NET pages interact. Let's move along to the following sections and look at some of the ways you can use ADO.NET to affect your data.
Command
The SQLCommand object has a method called ExecuteNonQuery(), which provides a means by which you can run Insert, Update, and Delete statements against your database by writing the appropriate T-SQL statement as the SQLCommand's CommandText property. Let's have look.
Visual Basic .NET ado_sqlcommand_iud_vb.aspx
<%@ page language="vb" runat="server" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=server> Sub Page_Load() If (Not IsPostBack) then LoadData() End If End Sub Sub LoadData() dim OurConnection as SqlConnection dim OurCommand as SqlCommand dim OurDataAdapter as SQLDataAdapter dim OurDataSet as New DataSet() OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; 
database=Northwind") OurConnection.Open() OurCommand = New SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from Employees" ,OurConnection) OurDataAdapter = New SQLDataAdapter(OurCommand) OurDataAdapter.Fill(OurDataSet, "Employees") OurDataGrid.DataSource=OurDataSet.Tables("Employees") UpdateEmployeeID.DataSource=OurDataSet.Tables("Employees") DeleteEmployeeID.DataSource=OurDataSet.Tables("Employees") DataBind() End Sub Sub UpdateSetup(sender As Object, e As System.EventArgs) UpdateSelect.Visible="False" UpdateTextBoxes.Visible="True" dim SelectedID as String = UpdateEmployeeID.SelectedItem.Value UpdateButton.CommandArgument = SelectedID dim OurConnection as SqlConnection OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurConnection.Open() dim OurCommand as SqlCommand dim OurDataReader2 as SqlDataReader OurCommand = New SqlCommand("Select FirstName, LastName From Employees Where EmployeeID = " + SelectedID ,OurConnection) OurDataReader2 = OurCommand.ExecuteReader() OurDataReader2.Read() UpdateFirstName.Visible="True" UpdateLastName.Visible="True" UpdateFirstName.Text = OurDataReader2("FirstName") UpdateLastName.Text = OurDataReader2("LastName") OurConnection.Close() OurDataReader2.Close() LoadData() End Sub Sub UpdateReset(sender As Object, e As System.EventArgs) UpdateSelect.Visible="True" UpdateTextBoxes.Visible="False" LoadData() End Sub Sub InsertCommand(sender As Object, e As System.EventArgs) dim OurConnection as SqlConnection OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") dim OurCommand as SqlCommand OurCommand = New SqlCommand("Insert Into Employees (FirstName, LastName) Values (@FirstName, @LastName)" ,OurConnection) OurCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 10).Value = InsertFirstName.Text OurCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 20).Value = InsertLastName. Text OurConnection.Open() OurCommand.ExecuteNonQuery() OurConnection.Close() LoadData() End Sub Sub UpdateCommand(sender As Object, e As System.Web.UI.WebControls.CommandEventArgs) dim OurConnection as SqlConnection dim OurCommand as SqlCommand dim EmployeeID as String = e.CommandArgument OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurCommand = New SqlCommand("Update Employees Set Firstname = @FirstName,LastName = @LastName Where EmployeeID = " + EmployeeID, OurConnection) OurCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 10).Value =UpdateFirstName. Text OurCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 20).Value =UpdateLastName. Text OurConnection.Open() OurCommand.ExecuteNonQuery() OurConnection.Close() LoadData() End Sub Sub DeleteCommand(sender As Object, e As System.EventArgs) dim OurConnection as SqlConnection dim SelectedID as String = DeleteEmployeeID.SelectedItem.Value dim OurCommand as SqlCommand OurConnection = New SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurCommand = New SqlCommand("Delete Employees Where EmployeeID = " + SelectedID, OurConnection) OurConnection.Open() OurCommand.ExecuteNonQuery() OurConnection.Close() LoadData() End Sub </script> <html> <head> <title>ADO SQLCommand -Insert,Update,Delete</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <table border="0" cellpadding="0" cellspacing="20"> <tr><td> <ASP:DataGrid EnableViewState="false" BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" runat="server" /> </td><td> <h4>Insert</h4> First Name: <asp:TextBox runat="server" /><br> Last Name: <asp:TextBox runat="server" /> <asp:button runat="server" OnClick="InsertCommand" text="Submit" /><br><br> <hr style="height:1px"> <h4>Update</h4> <asp:Panel runat="server" > EmployeeID: <asp:DropDownList DataTextField="EmployeeID" DataValueField="EmployeeID" runat="server" /> <asp:button runat="server" text="Select" OnClick="UpdateSetup" /> </asp:Panel> <asp:Panel Visible="false" runat="server" > First Name: <asp:TextBox runat="server" /> <asp:button onCommand="UpdateCommand" runat="server" text="Update" /><br> Last Name: <asp:TextBox runat="server" /> <asp:button runat="server" onClick="UpdateReset" text="Select Other" /> </asp:Panel><br> <hr style="height:1px"> <h4>Delete</h4> EmployeeID: <asp:DropDownList DataTextField="EmployeeID" DataValueField="EmployeeID" runat="server" /> <asp:button runat="server" text="Delete" onClick="DeleteCommand" /> </td></tr> </table> </form> </body> </html> C# ado_sqlcommand_iud_cs.asxp
<%@ page language="c#" runat="server" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat=server> void Page_Load() { if (!IsPostBack) { LoadData(); } } void LoadData() { SqlConnection OurConnection; SqlCommand OurCommand; SqlDataAdapter OurDataAdapter; DataSet OurDataSet = new DataSet(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurConnection.Open(); OurCommand = new SqlCommand("Select Top 15 EmployeeID,FirstName,LastName from Employees" ,OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter.Fill(OurDataSet, "Employees"); OurDataGrid.DataSource=OurDataSet.Tables["Employees"]; UpdateEmployeeID.DataSource=OurDataSet.Tables["Employees"]; DeleteEmployeeID.DataSource=OurDataSet.Tables["Employees"]; DataBind(); } void UpdateSetup(Object sender, System.EventArgs e) { UpdateSelect.Visible=false; UpdateTextBoxes.Visible=true; String SelectedID = UpdateEmployeeID.SelectedItem.Value.ToString(); UpdateButton.CommandArgument = SelectedID; SqlConnection OurConnection; OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurConnection.Open(); SqlCommand OurCommand; SqlDataReader OurDataReader2; OurCommand = new SqlCommand("Select FirstName, LastName From Employees Where EmployeeID = " + SelectedID ,OurConnection); OurDataReader2 = OurCommand.ExecuteReader(); OurDataReader2.Read(); UpdateFirstName.Visible=true; UpdateLastName.Visible=true; UpdateFirstName.Text = OurDataReader2["FirstName"].ToString(); UpdateLastName.Text = OurDataReader2["LastName"].ToString(); OurConnection.Close(); OurDataReader2.Close(); LoadData(); } void UpdateReset(Object sender, System.EventArgs e) { UpdateSelect.Visible=true; UpdateTextBoxes.Visible=false; LoadData(); } void InsertCommand(Object sender, System.EventArgs e) { SqlConnection OurConnection; OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); SqlCommand OurCommand; OurCommand = new SqlCommand("Insert Into Employees (FirstName, LastName) Values (@FirstName, @LastName)" ,OurConnection); OurCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = InsertFirstName.Text; OurCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = InsertLastName. Text; OurConnection.Open(); OurCommand.ExecuteNonQuery(); OurConnection.Close(); LoadData(); } void UpdateCommand(Object sender, System.Web.UI.WebControls.CommandEventArgs e) { SqlConnection OurConnection; SqlCommand OurCommand; String EmployeeID = e.CommandArgument.ToString(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password;database=Northwind"); OurCommand = new SqlCommand("Update Employees Set Firstname = @FirstName, LastName = @LastName Where EmployeeID = " + EmployeeID, OurConnection); OurCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = UpdateFirstName.Text; OurCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = UpdateLastName. Text; OurConnection.Open(); OurCommand.ExecuteNonQuery(); OurConnection.Close(); LoadData(); } void DeleteCommand(Object sender, System.EventArgs e) { SqlConnection OurConnection; String SelectedID = DeleteEmployeeID.SelectedItem.Value; SqlCommand OurCommand; OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password;database=Northwind"); OurCommand = new SqlCommand("Delete Employees Where EmployeeID = " + SelectedID, OurConnection); OurConnection.Open(); OurCommand.ExecuteNonQuery(); OurConnection.Close(); LoadData(); } </script> <html> <head> <title>ADO SqlCommand -Insert,Update,Delete</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <table border="0" cellpadding="0" cellspacing="20"> <tr><td> <ASP:DataGrid EnableViewState="false" BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" runat="server" /> </td><td> <h4>Insert</h4> First Name: <asp:TextBox runat="server" /><br> Last Name: <asp:TextBox runat="server" /> <asp:button runat="server" OnClick="InsertCommand" text="voidmit" /><br><br> <hr style="height:1px"> <h4>Update</h4> <asp:Panel runat="server" > EmployeeID: <asp:DropDownList DataTextField="EmployeeID" DataValueField="EmployeeID" runat="server" /> <asp:button runat="server" text="Select" OnClick="UpdateSetup" /> </asp:Panel> <asp:Panel Visible="false" runat="server" > First Name: <asp:TextBox runat="server" /> <asp:button onCommand="UpdateCommand" runat="server" text="Update" /><br> Last Name: <asp:TextBox runat="server" /> <asp:button runat="server" onClick="UpdateReset" text="Select Other" /> </asp:Panel><br> <hr style="height:1px"> <h4>Delete</h4> EmployeeID: <asp:DropDownList DataTextField="EmployeeID" DataValueField="EmployeeID" runat="server" /> <asp:button runat="server" text="Delete" onClick="DeleteCommand" /> </td></tr> </table> </form> </body> </html> As you can see in the code examples, there are three different functions that address separately inserting, updating, and deleting. The three functions are pretty similar, with the difference being that I simply set the CommandText property of the SQLCommand to the appropriate T-SQL statement, depending on whether I want to insert, update, or delete data. This is a fairly complex system that allows inserting, updating, and deleting from the same page and also queries the database to populate the DataGrid and the two DropDownLists with the EmployeeID in them.
You can see the result of this page in Figure 10.6, and as you reflect on the code example, realize that each individual function is really a SQLCommand statement unto itself and can be used as an example of how each of these functions can be used individually.
Figure 10.6. The SQLCommand's ExecuteNonQuery() method enables you to use the SQLCommand to execute inserts, updates, and deletes.
If you look at the UpdateCommand function, you will notice the Command object's text has @FirstName and @LastName in the string. These are parameters of the Command object that act as placeholders for values that you intend to replace.
OurCommand = New SqlCommand("Update Employees Set Firstname = @FirstName, LastName
The Command.Parameters.Add().Value method is the way to replace a placeholder or parameter in Command.Text. The three parameters that you pass to the Add() method are the name of the parameter to replace or receive, the DataType of the Parameter, and the size of the data being contained in the Parameter. The SqlDBType namespace contains the DataTypes that are specific to SQL Server and information about these DataTypes can be found in the .NET Framework SDK at the following address:
ms-help://MS.NETFrameworkSDK/cpref/html/frlrfsystemdatasqltypes.htm
Inserts, updates, and deletes are performed in exactly the same manner as they are when you query the database: First you create a connection, and then you build the command. But rather than fill DataSets or DataReaders with data from the command, you use the ExecuteNonQuery() method, which then performs what the CommandText states, without expecting data to be returned from the T-SQL statement.
DataAdapter
The DataAdapter, as I've said earlier, is a set of commands and database connections that are used to fill the DataSet object and update the database. It provides several ways to communicate between the database and dataset, including an automated method to build and execute T-SQL statements against your database, based from data contained in a DataSet.
InsertCommand, UpdateCommand, DeleteCommand
These properties of the DataAdapter work almost exactly the same as writing the T-SQL statements into the Command object. As a matter of fact, these properties are instances of the SQLCommand or OledbCommand object, so you deal with them in exactly the same way. The difference is you use the Update() method of the DataAdapter, rather than the ExecuteNonQuery() method of the Command, to execute the change. You will use the Update() method, whether you are using the InsertCommand, UpdateCommand, or DeleteCommand.
This is what opens the doors for making edits to a DataTable in a DataSet and then having this affect the values of the database afterwards. In other words, you can insert, update, or delete records in a DataTable, then after you conclude editing the DataTable, call the DataAdapter's Update() method, and this method will insert, update, or delete records according to edits you've made to the DataTable.
What I show in the following code example is just the UpdateCommand to demonstrate a straightforward example of editing five rows in a DataTable and then back to the data source using the DataAdapter's Update() method.
Visual Basic .NET ado_dataadapter_iud_sql_vb.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() If IsPostBack Then Update() End If DataSub() End Sub Sub DataSub() dim OurConnection as SqlConnection dim OurCommand as SqlCommand dim OurDataAdapter as SqlDataAdapter dim OurDataSet as new DataSet() OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurCommand = new SqlCommand("Select Top 5 ProductID, ProductName, UnitPrice From Products",OurConnection) OurDataAdapter = new SqlDataAdapter(OurCommand) OurDataAdapter.Fill(OurDataSet, "Products") OurDataGrid.DataSource = OurDataSet.Tables("Products") DataBind() ProductName1.Text = OurDataSet.Tables("Products").Rows(0).Item("ProductName") UnitPrice1.Text = OurDataSet.Tables("Products").Rows(0).Item("UnitPrice") ProductName2.Text = OurDataSet.Tables("Products").Rows(1).Item("ProductName") UnitPrice2.Text = OurDataSet.Tables("Products").Rows(1).Item("UnitPrice") ProductName3.Text = OurDataSet.Tables("Products").Rows(2).Item("ProductName") UnitPrice3.Text = OurDataSet.Tables("Products").Rows(2).Item("UnitPrice") ProductName4.Text = OurDataSet.Tables("Products").Rows(3).Item("ProductName") UnitPrice4.Text = OurDataSet.Tables("Products").Rows(3).Item("UnitPrice") ProductName5.Text = OurDataSet.Tables("Products").Rows(4).Item("ProductName") UnitPrice5.Text = OurDataSet.Tables("Products").Rows(4).Item("UnitPrice") End Sub Sub Update() dim OurConnection as SqlConnection = new SqlConnection("Server=server;uid=newriders; pwd=password;database=Northwind") dim OurCommand as SqlCommand = new SqlCommand("Select Top 5 ProductID, ProductName, UnitPrice From Products",OurConnection) dim OurDataAdapter as SqlDataAdapter = new SqlDataAdapter(OurCommand) dim OurDataSet as new DataSet() dim OurTable as DataTable OurDataAdapter.Fill(OurDataSet, "Products") OurDataAdapter.UpdateCommand = new SqlCommand("Update Products Set ProductName = @ProductName, UnitPrice = @UnitPrice Where ProductID = @ProductID", OurConnection) OurDataAdapter.UpdateCommand.Parameters.Add("@ProductName", SqlDbType.VarChar, 40, "ProductName") OurDataAdapter.UpdateCommand.Parameters.Add("@UnitPrice", SqlDbType.Money, 8, "UnitPrice") OurDataAdapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4, "ProductID") OurTable = OurDataSet.Tables("Products") OurTable.Rows(0).Item("ProductName") = ProductName1.Text OurTable.Rows(0).Item("UnitPrice") = UnitPrice1.Text OurTable.Rows(1).Item("ProductName") = ProductName2.Text OurTable.Rows(1).Item("UnitPrice") = UnitPrice2.Text OurTable.Rows(2).Item("ProductName") = ProductName3.Text OurTable.Rows(2).Item("UnitPrice") = UnitPrice3.Text OurTable.Rows(3).Item("ProductName") = ProductName4.Text OurTable.Rows(3).Item("UnitPrice") = UnitPrice4.Text OurTable.Rows(4).Item("ProductName") = ProductName5.Text OurTable.Rows(4).Item("UnitPrice") = UnitPrice5.Text OurDataAdapter.Update(OurTable) End Sub </script> <html> <head> <title>ADO DataSet</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" /> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> <table> <tr> <td>Product Name</td> <td>Unit Price</td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> </table> <asp:Button text="Submit" runat="server" /> </form> </body> </html> C# ado_dataadapter_iud_sql_cs.aspx
<%@ page language="c#" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load() { if (IsPostBack) { Update(); } DataSub(); } void DataSub() { SqlConnection OurConnection; SqlCommand OurCommand; SqlDataAdapter OurDataAdapter; DataSet OurDataSet = new DataSet(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurCommand = new SqlCommand("Select Top 5 ProductID, ProductName, UnitPrice From Products",OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter.Fill(OurDataSet, "Products"); OurDataGrid.DataSource = OurDataSet.Tables["Products"]; DataBind(); ProductName1.Text = OurDataSet.Tables["Products"].Rows[0]["ProductName"].ToString(); UnitPrice1.Text = OurDataSet.Tables["Products"].Rows[0]["UnitPrice"].ToString(); ProductName2.Text = OurDataSet.Tables["Products"].Rows[1]["ProductName"].ToString(); UnitPrice2.Text = OurDataSet.Tables["Products"].Rows[1]["UnitPrice"].ToString(); ProductName3.Text = OurDataSet.Tables["Products"].Rows[2]["ProductName"].ToString(); UnitPrice3.Text = OurDataSet.Tables["Products"].Rows[2]["UnitPrice"].ToString(); ProductName4.Text = OurDataSet.Tables["Products"].Rows[3]["ProductName"].ToString(); UnitPrice4.Text = OurDataSet.Tables["Products"].Rows[3]["UnitPrice"].ToString(); ProductName5.Text = OurDataSet.Tables["Products"].Rows[4]["ProductName"].ToString(); UnitPrice5.Text = OurDataSet.Tables["Products"].Rows[4]["UnitPrice"].ToString(); } void Update() { SqlConnection OurConnection = new SqlConnection("Server=server;uid=newriders; pwd=password;database=Northwind"); SqlCommand OurCommand = new SqlCommand("Select Top 5 ProductID, ProductName,UnitPrice From Products",OurConnection); SqlDataAdapter OurDataAdapter = new SqlDataAdapter(OurCommand); DataSet OurDataSet = new DataSet(); DataTable OurTable; OurDataAdapter.Fill(OurDataSet, "Products"); OurDataAdapter.UpdateCommand = new SqlCommand("Update Products Set ProductName = @ProductName, UnitPrice = @UnitPrice Where ProductID = @ProductID", OurConnection); OurDataAdapter.UpdateCommand.Parameters.Add("@ProductName", SqlDbType.VarChar, 40, "ProductName"); OurDataAdapter.UpdateCommand.Parameters.Add("@UnitPrice", SqlDbType.Money, 8, "UnitPrice"); OurDataAdapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4, "ProductID"); OurTable = OurDataSet.Tables["Products"]; OurTable.Rows[0]["ProductName"] = ProductName1.Text; OurTable.Rows[0]["UnitPrice"] = UnitPrice1.Text; OurTable.Rows[1]["ProductName"] = ProductName2.Text; OurTable.Rows[1]["UnitPrice"] = UnitPrice2.Text; OurTable.Rows[2]["ProductName"] = ProductName3.Text; OurTable.Rows[2]["UnitPrice"] = UnitPrice3.Text; OurTable.Rows[3]["ProductName"] = ProductName4.Text; OurTable.Rows[3]["UnitPrice"] = UnitPrice4.Text; OurTable.Rows[4]["ProductName"] = ProductName5.Text; OurTable.Rows[4]["UnitPrice"] = UnitPrice5.Text; OurDataAdapter.Update(OurTable); } </script> <html> <head> <title>ADO DataSet</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" /> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> </Columns> </asp:DataGrid> <table> <tr> <td>Product Name</td> <td>Unit Price</td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> <tr> <td><asp:TextBox runat="server" /></td> <td><asp:TextBox runat="server" /></td> </tr> </table> <asp:Button text="Submit" runat="server" /> </form> </body> </html> If you look at the results in Figure 10.7, you can see that I first affected the rows in the DataTable that I wanted to edit, then called the Update() method to mirror those changes back to the database.
Figure 10.7. The InsertCommand, UpdateCommand, and DeleteCommand property, plus the Update() method of the DataAdapter, give powerful flexibility to control how data is manipulated in your database.
There are many ways to take advantage of these properties of the DataAdapter and suspend connection to the database until all changes have been made to the DataTable.
CommandBuilder
The DataAdapter's InsertCommand, UpdateCommand, and DeleteCommand provide a great way to pass data back and forth between the DataSet object and your database. But wouldn't it be great if the DataAdapter could automatically build the InsertCommand, UpdateCommand, and DeleteCommand for you? The CommandBuilder object does just that. It provides a way to automatically generate commands to a single table and reconcile changes made to a DataSet with your database.
This is pretty cool when you think about inserting, updating, and deleting rows from a DataSet and then being able to create an instance of the CommandBuilder object and have it dynamically handle reconciling the changes back to the database.
Visual Basic .NET ado_dataadapter_iud_cb_sql_vb.aspx
<%@ page language="vb" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> Sub Page_Load() If Not IsPostBack Then DataSub() End If End Sub Sub DataSub() dim OurConnection as SqlConnection dim OurCommand as SqlCommand dim OurDataAdapter as SqlDataAdapter dim OurDataSet as new DataSet() OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind") OurCommand = new SqlCommand("Select ProductID, ProductName, UnitPrice From Products", OurConnection) OurDataAdapter = new SqlDataAdapter(OurCommand) OurDataAdapter.Fill(OurDataSet, "Products") OurDataGrid.DataSource = OurDataSet.Tables("Products") DataBind() End Sub Sub Edit(sender As Object, e As DataGridCommandEventArgs) OurDataGrid.EditItemIndex = e.Item.ItemIndex DataSub End Sub Sub Cancel(sender As Object, e As DataGridCommandEventArgs) OurDataGrid.EditItemIndex = -1 DataSub End Sub Sub Update(sender As Object, e As DataGridCommandEventArgs) dim OurConnection as SqlConnection = new SqlConnection("Server=server;uid=newriders; pwd=password;database=Northwind") dim OurCommand as SqlCommand = new SqlCommand("Select ProductID, ProductName, UnitPrice From Products",OurConnection) dim OurDataAdapter as SqlDataAdapter = new SqlDataAdapter(OurCommand) dim OurCommandBuilder as SqlCommandBuilder = new SqlCommandBuilder(OurDataAdapter) dim OurDataSet as new DataSet() dim OurDataTable as DataTable dim OurDataRow as DataRow dim PrimaryKeys(1) as DataColumn OurDataAdapter.Fill(OurDataSet, "Products") OurDataTable = OurDataSet.Tables("Products") PrimaryKeys(0) = OurDataTable.Columns("ProductID") OurDataTable.PrimaryKey = PrimaryKeys OurDataRow = OurDataTable.Rows.Find(e.Item.Cells(0).Text) OurDataRow(1) = CType(e.Item.Cells(1).Controls(0), TextBox).Text OurDataRow(2) = CType(CType(e.Item.Cells(2).Controls(0), TextBox).Text, Decimal) OurDataGrid.EditItemIndex = -1 OurDataAdapter.Update(OurDataTable) OurDataGrid.DataSource = OurDataTable OurDataGrid.DataBind() End Sub </script> <html> <head> <title>ADO DataSet</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" OnEditCommand="Edit" OnCancelCommand="Cancel" OnUpdateCommand="Update" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" /> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> <asp:EditCommandColumn HeaderText="Edit" EditText="Edit" CancelText="Cancel" UpdateText="Update"/> </Columns> </asp:DataGrid> </form> </body> </html> C# ado_dataadapter_iud_cb_sql_cs.aspx
<%@ page language="c#" runat="server"%> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SqlClient"%> <script runat=server> void Page_Load() { if (!IsPostBack) { DataSub(); } } void DataSub() { SqlConnection OurConnection; SqlCommand OurCommand; SqlDataAdapter OurDataAdapter; DataSet OurDataSet = new DataSet(); OurConnection = new SqlConnection("Server=server;uid=newriders;pwd=password; database=Northwind"); OurCommand = new SqlCommand("Select ProductID, ProductName, UnitPrice From Products", OurConnection); OurDataAdapter = new SqlDataAdapter(OurCommand); OurDataAdapter.Fill(OurDataSet, "Products"); OurDataGrid.DataSource = OurDataSet.Tables["Products"]; DataBind(); } void Edit(Object sender, DataGridCommandEventArgs e) { OurDataGrid.EditItemIndex = e.Item.ItemIndex; DataSub(); } void Cancel(Object sender, DataGridCommandEventArgs e) { OurDataGrid.EditItemIndex = -1; DataSub(); } void Update(Object sender, DataGridCommandEventArgs e) { SqlConnection OurConnection = new SqlConnection("Server=server;uid=newriders; pwd=password;database=Northwind"); SqlCommand OurCommand = new SqlCommand("Select ProductID, ProductName, UnitPrice From Products",OurConnection); SqlDataAdapter OurDataAdapter = new SqlDataAdapter(OurCommand); DataSet OurDataSet = new DataSet(); SqlCommandBuilder OurCommandBuilder = new SqlCommandBuilder(OurDataAdapter); DataTable OurDataTable; DataRow OurDataRow; DataColumn[] PrimaryKeys = new DataColumn[1]; OurDataAdapter.Fill(OurDataSet, "Products"); OurDataTable = OurDataSet.Tables["Products"]; PrimaryKeys[0] = OurDataTable.Columns["ProductID"]; OurDataTable.PrimaryKey = PrimaryKeys; OurDataRow = OurDataTable.Rows.Find(e.Item.Cells[0].Text); OurDataRow[1] = (e.Item.Cells[1].Controls[0] as TextBox).Text; OurDataRow[2] = Decimal.Parse((e.Item.Cells[2].Controls[0] as TextBox).Text.Replace( "$", "")); OurDataGrid.EditItemIndex = -1; OurDataAdapter.Update(OurDataTable); OurDataGrid.DataSource = OurDataTable; OurDataGrid.DataBind(); } </script> <html> <head> <title>ADO DataSet</title> </head> <body bgcolor="#FFFFFF" text="#000000"> <form runat="server"> <ASP:DataGrid BorderWidth="1" BorderColor="#000000" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="12px" OnEditCommand="Edit" OnCancelCommand="Cancel" OnUpdateCommand="Update" HeaderStyle-BackColor="#AAAAAA" ItemStyle-BackColor="#EEEEEE" AutoGenerateColumns="false" runat="server"> <Columns> <asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" /> <asp:BoundColumn HeaderText="Product Name" DataField="ProductName" /> <asp:BoundColumn HeaderText="Unit Price" DataField="UnitPrice" DataFormatString="{0:c}"/> <asp:EditCommandColumn HeaderText="Edit" EditText="Edit" CancelText="Cancel" UpdateText="Update"/> </Columns> </asp:DataGrid> </form> </body> </html> As you can see in the code example, the DataAdapter was sent as a parameter to the CommandBuilder object so it knows which DataAdapter to build the commands for. Then the DataAdapter's Update() method was called, with the DataSet and DataTable passed as parameters. The rest is truly magic. The CommandBuilder watches the rows of the DataSet to determine whether they've changed; if they have, it creates the proper T-SQL statement to properly reconcile the data back to the database.
| Top |
- Enterprise Application Integration: New Solutions for a Solved Problem or a Challenging Research Field?
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Distributed Data Warehouse for Geo-spatial Services
- Data Mining for Business Process Reengineering
- Healthcare Information: From Administrative to Practice Databases