So far inthis chapter, you have seen how to update a database and how to add transactions to ensure data integrity. All of that is fine as far as it goes, but nothing you’ve done so far to update the database takes advantage of the DataSet object, which you will remember is the keystone of ADO.NET.
If you are using the DataSet object to retrieve data and pass it from tier to tier within your application, you would also like to manipulate that data within the dataset and push the changes back to the database. To make this more sophisticated model of data updating work, you will need to take advantage of the advanced capabilities of the DataSet and the DataAdapter classes, and you’ll need to understand how they in turn use the Command and Connection objects to mediate between the dataset and the database itself.
As explained in Chapter 11, the DataSet object interacts with the database through a DataAdapter object. Until now, you’ve created the data adapter by passing in a command string and a connection string to the DataAdapter object’s constructor and then calling the Fill method. It turns out that Fill interacts with the database by creating a command object on your behalf and assigning that command object to the SelectCommand property of the DataAdapter object.
Each SqlDataAdapter object has four command properties (SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand), each of which takes an object of type SqlCommand. Thus far, you’ve been using the Fill method to create a SelectCommand object (that is, a command object assigned to the SelectCommand property) by employing the command string parameter you’ve passed in to the DataAdapter object’s constructor. For example, in Example 11-2 you wrote:
// get records from the Bugs table string commandString = "Select BugID, Description from Bugs"; // create the dataset command object // and the DataSet object SqlDataAdapter dataAdapter = new SqlDataAdapter( commandString, connectionString); DataSet dataSet = new DataSet( ); // fill the dataset object dataAdapter.Fill(dataSet,"Bugs");
You could just as easily have explicitly set the SelectCommand property of the DataAdapter by writing:
// define the SQL Select command string commandString = "Select BugID, Description from Bugs"; // create the data adapter; do not pass in the select statement SqlDataAdapter dataAdapter = new SqlDataAdapter(connectionString); // explicitly create a command object with the select statement SqlCommand cmd = new SqlCommand(commandString); // assign the new command object to the SelectCommand property dataAdapter.SelectCommand = cmd;
To update the database with the changes you’ll make to your dataset, you’ll need to explicitly set the other three properties: UpdateCommand, DeleteCommand, and InsertCommand. You will fill these three properties with either SQL statements, or, more commonly, the names of stored procedures. When the data adapter is told to update the database, it will examine the changes to the dataset and call the appropriate command objects to update, delete, or insert records. Often, a single request to a dataset to update the database will cause each of these commands to be called repeatedly, once for each modified row.
The steps to updating a database using a dataset are:
Create a dataset by retrieving data from the database and display it.
Persist the dataset.
Update the records in the dataset. This might include adding new records, deleting records, and updating existing records. You may choose to rebind the changed dataset to display widgets on your page to show the user what has changed, and optionally to give the user an opportunity to make further changes before the database is updated.
Create stored procedures in the database to manage the update, insert, and delete commands.
Create command objects to invoke the stored procedures. Add parameters to the command objects as needed.
Add transaction support to ensure all updates are done or none is done.
Call the Update method on the data adapter. The data adapter will examine the changes in the dataset and call the appropriate command objects, which will update the database on your behalf.
The example program described in the next section will walk you through each of these steps and examine their implementation and implications in some detail.
As you have done in many previous examples, you start by retrieving data from the database using astored procedure, and displaying that data in a grid, as shown in Figure 12-5.
This data grid is created once again by calling the CreateBugDataSet method:
private DataSet CreateBugDataSet( ) { // connection string to connect to the Bugs Database string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); // Create a SqlCommand object and assign the connection System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( ); command.Connection=connection; command.CommandText="spBugsWithIDs"; command.CommandType=CommandType.StoredProcedure; // create a data adapter and assign the command object // and add the table mapping for bugs SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand=command; dataAdapter.TableMappings.Add("Table","BugInfo"); // Create the dataset and use the data adapter to fill it DataSet dataSet = new DataSet( ); dataAdapter.Fill(dataSet); return dataSet; }
The VB.NET equivalent is:
Private Function CreateBugDataSet( ) As DataSet ' connection string to connect to the Bugs Database Dim connectionString As String = _ "server=YourDB; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs" ' Create connection object, initialize with ' connection string. Open it. Dim myConnection As _ New System.Data.SqlClient.SqlConnection(connectionString) myConnection.Open( ) ' Create a SqlCommand object and assign the connection Dim myCommand As New System.Data.SqlClient.SqlCommand( ) myCommand.Connection = myConnection myCommand.CommandText = "spBugsWithIDs" myCommand.CommandType = CommandType.StoredProcedure ' create a data adapter and assign the command object ' and add the table mapping for bugs Dim dataAdapter As New SqlDataAdapter( ) dataAdapter.SelectCommand = myCommand dataAdapter.TableMappings.Add("Table", "BugInfo") ' Create the dataset and use the data adapter to fill it Dim myDataSet As New DataSet( ) dataAdapter.Fill(myDataSet) Return myDataSet End Function
The only change to the previous example is that this time
CreateBugDataSet calls a new stored procedure,
spBugsWithIDs
.
The source code for the spBugsWithIDs stored procedure itself is shown in Example 12-7. There are two important things to note in this stored procedure. The first is that the data displayed in the grid is once again drawn from a number of different tables. The Description field is from the Bugs table. The Response field (used to populate the Most Recent Action column on the grid) is taken from the last BugHistory record for each Bug. The Owner is drawn from the People table based on the Owner value in the latest BugHistory record (described in the sidebar Finding the Last BugHistory).
Example 12-7. The spBugsWithIDs stored procedure
CREATE PROCEDURE spBugsWithIDs AS select b.BugID, h.BugHistoryID, b.Description, b.Version, h.Response, o.FullName as owner, h.owner as ownerID, b.Product as ProductID, p.ProductDescription, b.Reporter as ReporterID, r.FullName as reporter, h.status as statusID, s.StatusDescription, h.severity as severityID, sev.SeverityDescription, h.DateStamp from (select bugID, max(bugHistoryID) as maxHistoryID from BugHistory group by bugID) t join bugs b on b.bugid = t.bugid join BugHistory h on h.bugHistoryID = t.maxHistoryID join lkProduct p on b.Product = p.ProductID join People r on b.Reporter = r.PersonID join People o on h.Owner = o.PersonID join lkStatus s on s.statusid = h.status join lkSeverity sev on sev.SeverityID = h.severity GO
The second important thing to note about this stored procedure is that it not only retrieves the values to be displayed, it also carefully retrieves the IDs of the fields as they appear in Bugs and BugHistory. That is, not only do you retrieve the severity description (High, Medium, Low) to display in the grid, but you also retrieve the corresponding severity ID values (5, 4, 3) as they are stored in the underlying records. This is important because in this example you will update these records, and you’ll need the IDs to appear in the table you have created in the dataset. If the user indicates he wants to change the severity from High to Medium, your update will actually change the value from 5 to 4.
Once a command object that can invoke the new stored procedure is created, as shown in the previous C# code fragment, a new data adapter is created and the SelectCommand property is manually set to that command object, as shown in the following code fragment:
SqlDataAdapter dataAdapter = new SqlDataAdapter( ); dataAdapter.SelectCommand=command;
You then add a new TableMapping object to the TableMappings collection to map the results of the stored procedure to a table within the BugInfo dataset named BugInfo:
dataAdapter.TableMappings.Add("Table","BugInfo");
It is critical to understand that to the dataset BugInfo appears as a single table, consisting of the fields and values returned by the stored procedure. The dataset, in this example, is oblivious to the underlying data structure of multiple interrelated tables.
Finally, a new dataset is created and filled using the data adapteryou’ve crafted:
DataSet dataSet = new Data dataAdapter.Fill(dataSet);
Later in this example, you will modify this dataset in response to the user pressing a button. When the user presses the button, however, there is a round trip to the server, and web pages are stateless. Thus, just as you are ready to update the dataset, it is gone (poof!), disappearing in a puff of stateless smoke.
You have a number of options for dealing with this problem. First, you can recreate the dataset by reissuing the query. This is not a great solution, not least because, after you update the dataset, you’ll want to write it back to the database -- and you must hold on to the dataset at that point in order to know what changes to write. In short, you can’t recreate the dataset; you need to persist it.
For this example, you’ll persist the dataset to
session state. Session state is
covered in detail in Chapter 6, but writing and
retrieving is dead simple. As soon as you get back the DataSet object
from the CreateBugDataSet method, you save it in a session variable
named BugsDataSet
. In order to do this, you
include the following code in the Page_Load method:
DataSet ds = CreateBugDataSet( ); Session["BugsDataSet"] = ds;
The VB.NET equivalent is:
Dim ds As DataSet = CreateBugDataSet Session("BugsDataSet") = ds
In the handler for the Update DataSet button, you’ll retrieve the dataset from session state. Remember that session state stores objects, and so you will have to cast the dataset back to its correct type:
DataSet ds = (DataSet) Session["BugsDataSet"]; DataTable bugTable = ds.Tables["BugInfo"];
In VB.NET, use:
ds = CType(Session("BugsDataSet"), DataSet) Dim bugTable As DataTable = ds.Tables("BugInfo")
Hey! Presto! You have the same dataset after your round trip. Even better, because Session objects are stored on the server, the dataset did not make the round trip.
There are many ways to allow the user to indicate how he wants to modify the data. This example ignores all user interface issues (which are covered in Chapter 10 and Chapter 13) and focuses on interacting with the data. To keep things simple, you’ll have only two buttons: Update DataSet and Update Database.
The event handler for the first button, Update
DataSet,
implements hard-coded changes to the data
in the dataset and then draws a second grid showing the changes. This
will have no effect on the underlying database. If you close the web
page after updating and displaying these changes, the database tables
will be unaffected. The second button, Update Database, writes the
changes to the dataset back to the database.
The user interface is bare bones. As shown in Figure 12-5, the web page opens by displaying data from the Bugs and BugHistory tables with two rather ugly buttons above the data grid.
In a real application, the user may indicate changes to the dataset in any number of ways. You might provide buttons and links to allow the user to interact with the data directly in the data grid. Or you might provide a form for adding and changing the data. For this application, as indicated earlier, you’ll just hardwire a number of changes that will mimic the changes that might be requested by a user. You provide only a single button, Update DataSet, as described in the following section.
When a user clicks on the Update DataSet button, the btnUpdateDataSet_Click event handler is called. In the previous step, you stashed the dataset away in session state; you retrieve it now, using the code:
DataSet ds = (DataSet) Session["BugsDataSet"];
which in VB.NET is:
Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet)
With the dataset in hand, you can extract the table you created earlier named BugInfo.
DataTable bugTable = ds.Tables["BugInfo"];
which in VB.NET is:
Dim bugTable As DataTable = ds.Tables("BugInfo")
You are now ready to edit, insert, and delete values. The
DataRow class has an
Item property that returns the data
stored in a specified column. Because this is implemented as the
indexer
in C#, you can access the value for a
particular field in a given row by providing the row offset and the
field name. For example, the following line of code will change the
Response
value in the first row (remember that in
C# and VB.NET arrays are zero-indexed) to the value This is a test
:
bugTable.Rows[0]["Response"] = "This is a test";
In VB.NET, Item is the default property of the DataRow class. Hence, the VB.NET code is similar to the C# code:
bugTable.Rows(0)("Response") = "This is a test"
You can delete a row by calling the Delete method on the row itself:
bugTable.Rows[1].Delete( );
You add a new row using exactly the same syntax you saw for creating new data rows by hand in Chapter 11:
DataRow newRow = bugTable.NewRow( ); newRow["Description"] = "New bug test"; newRow["Response"] = "Created new bug"; newRow["Owner"] = "Jesse Liberty"; newRow["OwnerID"] = 1; newRow["ProductID"] = 2; newRow["ProductDescription"] = "PIM - My Personal Infomation Manager"; newRow["Version"] = "0.01"; newRow["ReporterID"] = 3; newRow["Reporter"] = "John Galt"; newRow["StatusID"] = 1; newRow["StatusDescription"] = "open"; newRow["SeverityID"] = 2; newRow["SeverityDescription"] = "High"; newRow["DateStamp"] = "07-27-2005"; bugTable.Rows.Add(newRow);
In VB.NET, the code looks like:
Dim newRow As DataRow = bugTable.NewRow( ) newRow("Description") = "New bug test" newRow("Response") = "Created new bug" newRow("Owner") = "Jesse Liberty" newRow("OwnerID") = 1 newRow("ProductID") = 2 newRow("ProductDescription") = "PIM - My Personal Infomation Manager" newRow("Version") = "0.01" newRow("ReporterID") = 3 newRow("Reporter") = "John Galt" newRow("StatusID") = 1 newRow("StatusDescription") = "open" newRow("SeverityID") = 2 newRow("SeverityDescription") = "High" newRow("DateStamp") = "07-27-2005" bugTable.Rows.Add(newRow)
Keep in mind that you’re filling the BugInfo table
in the dataset that was created by calling the
spBugsWithIDs
stored procedure. You must add a
field for every field in the resulting set returned by that sproc.
It is up to you, as the programmer, to ensure the data integrity of the hand-created rows. For example, nothing stops you from adding a SeverityID of 4 (normally Low) with a SeverityDescription of High, except that if you do you will display a value to the user that will not correspond to the value with which you’ll update the database!
Once you’ve made all the changes to the dataset, you will bind a second grid (DataGrid2) to the BugInfo table and make that grid visible, so that the user can see the new values, as shown in Figure 12-6.
Notice in Figure 12-6 that the first record has been
updated with a new Most Recent Action
value. This
new value reflects the change to the Response
field:
bugTable.Rows[0]["Response"] = "This is a test";
or in VB.NET:
bugTable.Rows(0)("Response") = "This is a test"
The Most Recent Action
value is highlighted in the
image of Figure 12-6 to make it easier for you to
locate the change.
BugID 2, which was the second record
(bugTable.Rows[1]
), appears to have been deleted.
In fact, it has only been marked for deletion, but the data grid is
smart enough not to display records marked for deletion.
A new record has been added, as shown on the final line in the grid. Notice that there is no BugID. (Looking back at the example, you will note that you did not provide a BugID.) The BugID field is an identity column, which will be provided by the database when you write this data back to the database.
The absence of a BugID illustrates quite clearly that while you’ve updated the dataset, you have not yet written these changes back to the database. You can prove this to yourself by examining the tables in the database directly, as shown in Figure 12-7.
When the user clicks on the second button, Update Database, the btnUpdateDataBase_Click event handler is invoked. Your goal in this method is to update the database with the changes in the dataset.
The dataset keeps track of the changes to its data. You can update the database with all the changes just by calling the Update method on the DataAdapter, passing in a reference to the DataSet object and the name of the table you want to update.
That said, there is a bit of preparation work. For the update to work, you first need to provide command objects to the InsertCommand, UpdateCommand, and DeleteCommand properties of the data adapter. We’ll examine each of these preparatory steps in the following sections.
As indicated earlier, you must begin by creating the
appropriate stored procedures. Example 12-8 shows the
spDeleteBugFromDataSet
stored procedure for
deleting bug records. If the user deletes a record from the grid, he
intends to delete all record of that bug. Because of referential
integrity, you must first remove all records
from that bug within BugHistory, and then you may remove the record
from the Bugs table.
Notice that you will pass in two parameters that will be used to identify the record to delete. With this stored procedure, you are ready to create the Command object you will assign to the DataAdapters DeleteCommand property.
You begin by creating a new SqlCommand object:
SqlCommand deleteCmd = new SqlCommand("spDeleteBugFromDataSet",connection); deleteCmd.CommandType=CommandType.StoredProcedure;
or in VB.NET:
Dim deleteCmd As New SqlCommand("spDeleteBugFromDataSet", myConnection) deleteCmd.CommandType = CommandType.StoredProcedure
This SqlCommand object is just like every command object you’ve created to date. You will name it deleteCmd to make it easy to identify, but it is just a garden-variety SqlCommand object, just like all the others you’ve used so far to invoke stored procedures.
You’ll add two parameters, BugID and BugHistoryID.
These are input parameters, but this time
rather than assigning a value to them, you must set two new
properties of the Parameter object, SourceColumn andSourceVersion. The SourceColumn property
identifies the column within the table in the dataset that this
parameter will get its value from. That is, when you invoke the
stored procedure, the parameter (@BugID
)
will draw its value from this column in the record to be deleted. The
column you want, of course, is BugID:
param.SourceColumn="bugID";
The second property of the parameter is the
SourceVersion,
which must be set to one of the
DataRowVersion
enumerated values
(Current, Default, Original
, or
Proposed).
The Default
value is used only when you wish
to use a default value, which does not apply to this example.
The Original
value is the value
the field had when the dataset was created. The original value is
compared to the value in the database when the update is performed to
see if the database has been changed by another process. This is
covered later in the Section 12.4
section.
The Current
value holds the
changes to the column you’ve made since the dataset
was created. That is, as you update columns, the
Current
value holds the changes
you’ve made, while the Original
value has the value as you originally obtained it from the database.
In the case of the BugID, you’ll tell the Param to
use the Original
value (though of course since
you’ve not changed the value, you can use the
Current
value as well):
param.SourceVersion=DataRowVersion.Original;
You create a Parameter object for the BugHistory in exactly the same way:
param = deleteCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="BugHistoryID"; param.SourceVersion=DataRowVersion.Original;
You are now ready to assign the command object to the DeleteCommand property of the data adapter:
dataAdapter.DeleteCommand=deleteCmd;
The stored procedure for updating the database is somewhat more complicated than the procedure for deleting records. This time, you want to pass in parameters for each of the fields that may be changed. You will also pass in the BugID and BugHistory ID to uniquely identify the bug you wish to alter. The complete stored procedure is shown in Example 12-9.
Example 12-9. The stored procedure for updating a bug
CREATE PROCEDURE spUpdateBugFromDataSet @ProductID int, @Description varChar(8000), @Response varChar(8000), @Reporter int, @Owner int, @Status int, @Severity int, @bugID int, @BugHistoryID int as Update Bugs set Product = @productID, [Description] = @Description, Reporter = @Reporter where bugID = @BugID Update BugHistory Set bugID = @BugID, status = @Status, severity = @Severity, response = @Response, owner = @Owner where BugHistoryID = @bugHistoryID and bugID = @bugID GO
Once again you create a command object, this time to hold the Update command stored procedure:
SqlCommand updateCmd = new SqlCommand("spUpdateBugFromDataSet",connection); updateCmd.CommandType=CommandType.StoredProcedure;
or in VB.NET:
Dim updateCmd As New SqlCommand("spUpdateBugFromDataSet", myConnection) updateCmd.CommandType = CommandType.StoredProcedure
You’ll add a SqlParameter object for each parameter to the stored procedure:
param = updateCmd.Parameters.Add("@ProductID",SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.SourceColumn="ProductID";
param.SourceVersion=DataRowVersion.Current;
The ProductID parameter is like the BugID parameter, except that now
you use the enumerated value
DataRowVersion.Current
for the SourceVersion
property. You will use Current
for any value that
may have been changed in the dataset; this instructs the data adapter
to update the dataset with the value current in the dataset, rather
than with the value that may reside back in the database.
When you create the parameters for the Reporter, Owner, Status, and
Severity fields, you must be careful to use the ReporterID, OwnerID,
StatusID, and SeverityID SourceColumns,
respectively. Remember that while you are displaying the full names
of the reporter and owner, and the text value of the status and
severity, the records you are updating in the Bugs and BugHistory
tables use the ID.
The final command you’ll need to implement is the insert command. You start, once again, by creating the necessary stored procedure, as shown in Example 12-10.
Example 12-10. The stored procedure spInsertBugFromDataSet
CREATE PROCEDURE spInsertBugFromDataSet
@ProductID int,
@Version varChar(50),
@Description varChar(8000),
@Response varChar(8000),
@Reporter int,
@Owner int,
@Status int,
@Severity int
as
declare @bugID int
Insert into Bugs values (@ProductID, @Version, @Description, @Reporter)
select @bugID = @@identity
Insert into BugHistory
(bugID, status, severity, response, owner)
values
( @bugID,
@status, -- status
@Severity,
@response,
@owner
)
GO
You must remember to insert into the Bugs table before inserting into the BugHistory table because referential integrity constraints require that the BugID must exist in Bugs before it can be inserted into BugHistory.
Note that you do not pass in either the BugID nor the BugHistoryID as
parameters; these are created by the database itself. The BugHistory
table requires the BugID generated by adding a record to Bugs; you
obtain this value from
@@identity
.
It is this stored procedure that will be called to insert the record you created by hand in the btnUpdateDataSet_Click event procedure. Once again, you must create a command object, this time for the InsertCommand property of the DataAdapter object:
param = insertCmd.Parameters.Add("@ProductID",SqlDbType.Int);
Once again, you create all the parameters and set their values. You then assign the command object to the DataAdapter object’s InsertCommand property:
dataAdapter.InsertCommand=insertCmd;
It is possible for one or another of the updates to fail, and if they do not all fail, it can be difficult to return the database to a valid state. You will therefore add transaction support. You start, as last time, by obtaining a reference to a SqlTransaction object by calling BeginTransaction on the connection object:
SqlTransaction transaction; connection.Open( ); transaction = connection.BeginTransaction( );
or in VB.NET:
Dim transaction As SqlTransaction myConnection.Open( ) transaction = myConnection.BeginTransaction( )
With all three Command properties set, you can add the transaction to each command’s Transaction property:
dataAdapter.UpdateCommand.Transaction = transaction; dataAdapter.DeleteCommand.Transaction = transaction; dataAdapter.InsertCommand.Transaction = transaction;
You are now ready to call the Update method of the
SqlDataAdapter
object,
which you will do from within a
try
block. The Update method will return the
number of rows that are updated, which you will use to fill in the
text of a label at the bottom of the data grid. The code is as
follows:
try { int rowsUpdated = dataAdapter.Update(ds,"BugInfo"); transaction.Commit( ); CountUpdatedRows.Visible=true; CountUpdatedRows.Text = rowsUpdated.ToString( ) + " rows Updated."; } catch { transaction.Rollback( ); }
or in VB.NET:
Try Dim rowsUpdated As Int32 rowsUpdated = CType(dataAdapter.Update(ds, "BugInfo"), Int32) transaction.Commit( ) ' transaction.Rollback( ) CountUpdatedRows.Visible = True CountUpdatedRows.Text = rowsUpdated.ToString( ) + " rows Updated." Catch transaction.Rollback( ) End Try
If no exception is thrown, you commit the transactions; otherwise, you roll them back.
You can then rebind to the data grid, which will remain unchanged. The label is now visible, however, showing the number of rows that were updated, as shown in Figure 12-8. (The label is highlighted in the figure to make it easy to find.)
If you examine the Bugs and BugHistory tables, you should now see that the data has been updated, as shown in Figure 12-9.
Most of the methods in this example are unchanged from earlier listings. The important changes are in declaring the user interface elements and three methods: Page_Load, btnUpdateDataSet_Click, and btnUpdateDataBase_Click. These changes are shown in Example 12-11 (C#) and Example 12-12 (VB.NET).
Example 12-11. Updating the database from a dataset
protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.DataGrid DataGrid2; protected System.Web.UI.WebControls.DataGrid HistoryGrid; protected System.Web.UI.WebControls.Panel BugHistoryPanel; protected System.Web.UI.WebControls.Panel DataGrid2Panel; protected System.Web.UI.WebControls.Button btnUpdateDataSet; protected System.Web.UI.WebControls.Button btnUpdateDataBase; protected System.Web.UI.WebControls.Label CountUpdatedRows; private void Page_Load( object sender, System.EventArgs e) { if (!IsPostBack) { // hide the history panel UpdateBugHistory( ); DataGrid2Panel.Visible=false; // set the data source for the // grid to the first table DataSet ds = CreateBugDataSet( ); Session["BugsDataSet"] = ds; DataGrid1.DataSource=ds.Tables[0]; DataGrid1.DataBind( ); } } // respond to the request to update // the dataset. This would normally be // replaced by a complete user interface to allow // the user to specify what changes to make private void btnUpdateDataSet_Click(object sender, System.EventArgs e) { // retrieve the dataset from session state DataSet ds = (DataSet) Session["BugsDataSet"]; // extract the table of Bug and BugHistory information DataTable bugTable = ds.Tables["BugInfo"]; // change one field in row 0 bugTable.Rows[0]["Response"] = "This is a test"; // delete row 1 bugTable.Rows[1].Delete( ); // append a new row DataRow newRow = bugTable.NewRow( ); newRow["Description"] = "New bug test"; newRow["Response"] = "Created new bug"; newRow["Owner"] = "Jesse Liberty"; newRow["OwnerID"] = 1; newRow["ProductID"] = 2; newRow["ProductDescription"] = "PIM - My Personal Infomation Manager"; newRow["Version"] = "0.01"; newRow["ReporterID"] = 3; newRow["Reporter"] = "John Galt"; newRow["StatusID"] = 1; newRow["StatusDescription"] = "open"; newRow["SeverityID"] = 2; newRow["SeverityDescription"] = "High"; newRow["DateStamp"] = "07-27-2005"; bugTable.Rows.Add(newRow); // update two fields in row 2 - note we update the id // for writing back to the db. We are responsible // for ensuring that the id matches the description bugTable.Rows[2]["SeverityID"] = 5; bugTable.Rows[2]["SeverityDescription"] = "Trivial"; // bind the DataSet to the second data grid // and make it visible DataGrid2.DataSource = ds.Tables["BugInfo"]; DataGrid2.DataBind( ); DataGrid2Panel.Visible=true; Session["BugsDataSet"] = ds; } private void btnUpdateDataBase_Click(object sender, System.EventArgs e) { DataSet ds = (DataSet) Session["BugsDataSet"]; SqlDataAdapter dataAdapter = new SqlDataAdapter( ); string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs"; // Create connection object, initialize with // connection string. Open it. System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); SqlTransaction transaction; connection.Open( ); transaction = connection.BeginTransaction( ); // *** create the update command object SqlCommand updateCmd = new SqlCommand("spUpdateBugFromDataSet",connection); updateCmd.CommandType=CommandType.StoredProcedure; // declare the parameter object System.Data.SqlClient.SqlParameter param; // Add new parameters, get back a reference // set the parameters' direction and value param = updateCmd.Parameters.Add("@ProductID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="ProductID"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Description",SqlDbType.Text,8000); param.Direction = ParameterDirection.Input; param.SourceColumn="Description"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Response",SqlDbType.Text,8000); param.Direction = ParameterDirection.Input; param.SourceColumn="Response"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Reporter",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="ReporterID"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Owner",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="OwnerID"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Status",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="StatusID"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@Severity",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="SeverityID"; param.SourceVersion=DataRowVersion.Current; param = updateCmd.Parameters.Add("@bugID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="bugID"; param.SourceVersion=DataRowVersion.Original; // note Original param = updateCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="BugHistoryID"; param.SourceVersion=DataRowVersion.Original; // note Original dataAdapter.UpdateCommand=updateCmd; // *** the delete command SqlCommand deleteCmd = new SqlCommand("spDeleteBugFromDataSet",connection); deleteCmd.CommandType=CommandType.StoredProcedure; param = deleteCmd.Parameters.Add("@bugID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="bugID"; param.SourceVersion=DataRowVersion.Original; // note Original param = deleteCmd.Parameters.Add("@BugHistoryID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="BugHistoryID"; param.SourceVersion=DataRowVersion.Original; // note Original dataAdapter.DeleteCommand=deleteCmd; // *** insert command SqlCommand insertCmd = new SqlCommand("spInsertBugFromDataSet",connection); insertCmd.CommandType=CommandType.StoredProcedure; // Add new parameters, get back a reference // set the parameters' direction and value param = insertCmd.Parameters.Add("@ProductID",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="ProductID"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Version",SqlDbType.Text,50); param.Direction = ParameterDirection.Input; param.SourceColumn="Version"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Description",SqlDbType.Text,8000); param.Direction = ParameterDirection.Input; param.SourceColumn="Description"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Response",SqlDbType.Text,8000); param.Direction = ParameterDirection.Input; param.SourceColumn="Response"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Reporter",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="ReporterID"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Owner",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="OwnerID"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Status",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="StatusID"; param.SourceVersion=DataRowVersion.Current; param = insertCmd.Parameters.Add("@Severity",SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn="SeverityID"; param.SourceVersion=DataRowVersion.Current; dataAdapter.InsertCommand=insertCmd; // add transaction support for each command dataAdapter.UpdateCommand.Transaction = transaction; dataAdapter.DeleteCommand.Transaction = transaction; dataAdapter.InsertCommand.Transaction = transaction; // try to update, if all succeed commit // otherwise roll back try { int rowsUpdated = dataAdapter.Update(ds,"BugInfo"); transaction.Commit( ); CountUpdatedRows.Visible=true; CountUpdatedRows.Text = rowsUpdated.ToString( ) + " rows Updated."; } catch { transaction.Rollback( ); } // rebind the grid to show the results // grid should be unchanged DataGrid2.DataSource = ds.Tables["BugInfo"]; DataGrid2.DataBind( ); }
Example 12-12. Updating the database from a dataset
Private Sub Page_Load( _ ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load If Not IsPostBack Then ' hide the history panel UpdateBugHistory( ) DataGrid2Panel.Visible = False ' set the data source for the ' grid to the first table Dim ds As DataSet = CreateBugDataSet Session("BugsDataSet") = ds DataGrid1.DataSource = ds.Tables(0) DataGrid1.DataBind( ) End If Private Sub btnUpdateDataSet_Click( _ ByVal sender As Object, ByVal e As System.EventArgs) ' retrieve the dataset from session state Dim ds As DataSet ds = CType(Session("BugsDataSet"), DataSet) ' extract the table of Bug and BugHistory information Dim bugTable As DataTable = ds.Tables("BugInfo") ' change one field in row 0 bugTable.Rows(0)("Response") = "This is a test" ' delete row 1 bugTable.Rows(1).Delete( ) ' append a new row Dim newRow As DataRow = bugTable.NewRow( ) newRow("Description") = "New bug test" newRow("Response") = "Created new bug" newRow("Owner") = "Jesse Liberty" newRow("OwnerID") = 1 newRow("ProductID") = 2 newRow("ProductDescription") = _ "PIM - My Personal Infomation Manager" newRow("Version") = "0.01" newRow("ReporterID") = 3 newRow("Reporter") = "John Galt" newRow("StatusID") = 1 newRow("StatusDescription") = "open" newRow("SeverityID") = 2 newRow("SeverityDescription") = "High" newRow("DateStamp") = "07-27-2005" bugTable.Rows.Add(newRow) ' update two fields in row 2 - note we update the id ' for writing back to the db. We are responsible ' for ensuring that the id matches the description bugTable.Rows(2)("SeverityID") = 5 bugTable.Rows(2)("SeverityDescription") = "Trivial" ' bind the dataset to the second data grid ' and make it visible DataGrid2.DataSource = ds.Tables("BugInfo") DataGrid2.DataBind( ) DataGrid2Panel.Visible = True Session("BugsDataSet") = ds End Sub Private Sub btnUpdateDataBase_Click( _ ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As DataSet = CType(Session("BugsDataSet"), DataSet) Dim dataAdapter As New SqlDataAdapter( ) Dim connectionString As String = _ "server=YourDB; uid=sa; pwd=YourPW; database=ProgASPDotNetBugs" ' Create connection object, initialize with ' connection string. Open it. Dim myConnection As New System.Data.SqlClient.SqlConnection(connectionString) Dim transaction As SqlTransaction myConnection.Open( ) transaction = myConnection.BeginTransaction( ) ' *** create the update command object Dim updateCmd As New SqlCommand("spUpdateBugFromDataSet", myConnection) updateCmd.CommandType = CommandType.StoredProcedure ' declare the parameter object Dim param As System.Data.SqlClient.SqlParameter ' Add new parameters, get back a reference ' set the parameters' direction and value param = updateCmd.Parameters.Add("@ProductID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "ProductID" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Description", SqlDbType.Text, 8000) param.Direction = ParameterDirection.Input param.SourceColumn = "Description" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Response", SqlDbType.Text, 8000) param.Direction = ParameterDirection.Input param.SourceColumn = "Response" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Reporter", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "ReporterID" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Owner", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "OwnerID" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Status", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "StatusID" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@Severity", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "SeverityID" param.SourceVersion = DataRowVersion.Current param = updateCmd.Parameters.Add("@bugID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "bugID" param.SourceVersion = DataRowVersion.Original ' note Original param = updateCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "BugHistoryID" param.SourceVersion = DataRowVersion.Original ' note Original dataAdapter.UpdateCommand = updateCmd ' *** the delete command Dim deleteCmd As New SqlCommand("spDeleteBugFromDataSet", myConnection) deleteCmd.CommandType = CommandType.StoredProcedure param = deleteCmd.Parameters.Add("@bugID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "bugID" param.SourceVersion = DataRowVersion.Original ' note Original param = deleteCmd.Parameters.Add("@BugHistoryID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "BugHistoryID" param.SourceVersion = DataRowVersion.Original ' note Original dataAdapter.DeleteCommand = deleteCmd ' *** insert command Dim insertCmd As New SqlCommand("spInsertBugFromDataSet", myConnection) insertCmd.CommandType = CommandType.StoredProcedure ' Add new parameters, get back a reference ' set the parameters' direction and value param = insertCmd.Parameters.Add("@ProductID", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "ProductID" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Version", SqlDbType.Text, 50) param.Direction = ParameterDirection.Input param.SourceColumn = "Version" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Description", SqlDbType.Text, 8000) param.Direction = ParameterDirection.Input param.SourceColumn = "Description" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Response", SqlDbType.Text, 8000) param.Direction = ParameterDirection.Input param.SourceColumn = "Response" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Reporter", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "ReporterID" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Owner", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "OwnerID" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Status", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "StatusID" param.SourceVersion = DataRowVersion.Current param = insertCmd.Parameters.Add("@Severity", SqlDbType.Int) param.Direction = ParameterDirection.Input param.SourceColumn = "SeverityID" param.SourceVersion = DataRowVersion.Current dataAdapter.InsertCommand = insertCmd ' add transaction support for each command dataAdapter.UpdateCommand.Transaction = transaction dataAdapter.DeleteCommand.Transaction = transaction dataAdapter.InsertCommand.Transaction = transaction ' try to update, if all succeed commit ' otherwise roll back Try Dim rowsUpdated As Int32 rowsUpdated = CType(dataAdapter.Update(ds, "BugInfo"), Int32) transaction.Commit( ) ' transaction.Rollback( ) CountUpdatedRows.Visible = True CountUpdatedRows.Text = rowsUpdated.ToString( ) + " rows Updated." Catch transaction.Rollback( ) End Try ' rebind the grid to show the results ' grid should be unchanged DataGrid2.DataSource = ds.Tables("BugInfo") DataGrid2.DataBind( ) End Sub
18.191.168.203