The DataView in Connected Environments
In Chapter 3, you learned how a DataView is useful to sort, filter, and view data based on different selection criteria. You also saw how to add, edit, and delete data from a DataView. In Chapter 3, you saw all of this in disconnected data. Also, you created DataView objects from DataTable objects. In the following sections, you'll see how to work with DataView objects in a connected environment. You'll also learn how to search and sort data based on criteria and how to create different views of the same data.
You simply create a DataView from a DataTable, which can be retrieved using the Tables property of a DataSet. The following code creates a DataView from the first table of a DataSet and binds a DataView to the DataGrid control:
Dim dtView1 As DataView = New DataView(ds.Tables(0)) DataGrid1.DataSource = dtView1
Creating Multiple Views
The main features of a DataView is that it's easily bound to controls, and it provides filter and sort properties, which allow you to create different views of the same data. To see how to use DataViews, let's create a Windows application. After creating a Windows application, you'll add two DataGrid controls, four TextBox controls, two CheckBox controls, and a Button control and arrange them as shown in Figure 4-7. The Load Data button loads data from the Northwind SQL Server database's Orders table.
Figure 4-7: Default multiple views application
Now, if you enter the filter and sort criteria from both DataGrid controls, the data is filtered and sorted based on the criteria. For example, Figure 4-8 shows the criteria and the result of clicking the Load Data button.
Figure 4-8: Multiple views with different filter and sorting criteria
Now let's examine the code. Listing 4-41 shows the private members.
Listing 4-41: Private Members
 |
Private ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Private conn As SqlConnection Private SQL As String = _ "SELECT EmployeeID, CustomerID, ShipCity, ShipName FROM Orders" Private adapter As SqlDataAdapter Dim ds As DataSet = New DataSet()
|
Listing 4-42 shows the code of the Load Data button's click event handler. As you can see, the code creates two different DataView objects from the DataSet and sets their RowFilter and Sort properties based on the data entered on the form. In the end, you bind each DataView object to a separate DataGrid control.
Listing 4-42: The Load Data Button's Click Event Handler
|
Private Sub LoadDataBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles LoadDataBtn.Click conn = New SqlConnection(ConnectionString) Try conn.Open() adapter = New SqlDataAdapter(SQL, conn) adapter.Fill(ds) Catch exp As SqlException exp.Message.ToString() End Try ' Create two DataView objects Dim dtView1 As DataView = New DataView(ds.Tables(0)) Dim dtView2 As DataView = New DataView(ds.Tables(0)) ' Set RowFilter and Sort proeprties of DataView1 If (TextBox1.Text <> String.Empty) Then dtView1.RowFilter = TextBox1.Text End If Dim sortStr1 As String = TextBox2.Text If (sortStr1 <> String.Empty) Then If CheckBox1.Checked Then sortStr1 = sortStr1 + " DESC" Else sortStr1 = sortStr1 + " ASC" End If dtView1.Sort = sortStr1 End If ' Set RowFilter and Sort proeprties of DataView2 If (TextBox3.Text <> String.Empty) Then dtView2.RowFilter = TextBox3.Text End If Dim sortStr2 As String = TextBox4.Text If (sortStr2 <> String.Empty) Then If CheckBox2.Checked Then sortStr2 = sortStr2 + " DESC" Else sortStr2 = sortStr2 + " ASC" End If dtView2.Sort = sortStr2 End If ' Bind both DataViews to two different DataGrids DataGrid1.DataSource = dtView1 DataGrid2.DataSource = dtView2 ' release objects conn.Close() conn.Dispose() End Sub
|
|
Transactions are groups of database commands that execute as a package and provide an ability to commit or roll back (abort) all changes made during the transaction processing. Transaction changes will be committed if there wasn't an error during the transaction processing. If an error occurs during the transaction processing, all changes will be aborted and data will be same as it was before any transactions started. To start transaction processing, you call BeginTransaction. At the end you can call CommitTransaction or Rollback Transaction based on the status of the transactions. CommitTransaction reflects all changes to the database, and Rollback aborts all changes.
For example, say you have an application with two tables: Inventory and Orders. When a customer places an order, the Inventory table needs to be reduced. Now imagine that updating the Orders table was successful, but updating the Inventory table failed. This scenario leads to data inconsistency. To maintain the integrity of data, you could package both commands into a single transaction. If one table updated successfully and the other table didn't, the transaction could be rolled back; otherwise, the transaction could be committed.
Nested transactions are transactions within the scope an existing transaction. The changes made within the nested transactions are invisible to the top-level transactions until the nested transactions are committed. To create nested transactions, you call BeginTransaction with CommitTransaction and Rollback-Transaction within the existing transactions. For example:
Begin Transaction A Begin Transaction B Do something Commit Transaction B Commit Transaction A
Savepoints are useful when you're working with nested transactions. There are occasions when you want to abort a portion of transaction, but not all of it. For example, say you're processing four commands as nested transactions, but you want to commit only two commands and abort two of them. A savepoint is a temporary point in the transaction that you want to save (or back up) without aborting the entire transaction. In transaction processing, you set the savepoint call and come back later when you think it's safe to process the transaction. A unique number represents the savepoint. For example:
Begin Transaction A Do something SetSavePoint Do something Commit or Rollback Transaction A
Managing and writing reliable and scalable multitier distributed applications is one of the most challenging jobs for database developers. Think about multiple clients accessing same server database simultaneously—some of them are accessing data, some of them are updating the same data, and some of them are trying to delete the same data that other clients are using in their operations.
To prevent data inconsistency, it's important to provide some kind of mechanism so other users can't update data when a user is already using that data. The mechanism to deal with this situation is called concurrency control. Concurrency is the method by which multiple clients can access and update the same data simultaneously without being concerned that they're forcing data redundancy and inconsistency.
There are three common ways to manage concurrency:
-
Pessimistic concurrency control: In pessimistic concurrency, the data is unavailable to other users from the time the record is fetched by a user until it's updated in the database. This is useful when users accessing a row are going to play a major role based on the data they're accessing. Another advantage of pessimistic concurrency is less locking overhead. Once a row is locked, it's locked until the first user is done. The main drawback of this type of concurrency is that data is not available to other users. For example, if a user accessing data left his terminal, other users have to wait for him to release the connection.
-
Optimistic concurrency control: In optimistic concurrency, data is available all the time except when a user is updating the data. The update examines the row in the database and determines whether any changes have been made. In this type of concurrency, the locks are set and held only while the database is being accessed. The locks prevent other users from attempting to update records at the same instant. If other users try to update the data that is locked by first user, the update fails.
-
Last in wins concurrency control: In this case of concurrency control, a row is unavailable to other users only while the data is being updated. This is only useful when the last user's update counts. For example, it's useful if you're keeping track of the last winner of a race. In other words, many users are updating the same data, and the person who updates it last is the latest data. The data updated by other users will be lost. In this case, data could easily lead to inconsistencies because of network slowness when data previously posted arrives last.
|
| Note | See Appendix A for more on locking, cursors, and isolation levels. |
Using Transactions in ADO.NET
ADO.NET provides a Transaction class that represents a transaction. All data providers provide their own version of the Transaction class. In the provider classes, a transaction is represented as an object returned after BeginTransaction is called on a Connection. You can commit (make permanent) or roll back (cancel and return to the original state) the transactions. Table 4-15 describes the methods for the OleDb provider's Transaction class.
| METHOD | DESCRIPTION |
|---|---|
| Commit | Commits the transaction to the database |
| Rollback | Rolls back a transaction to the previous database state |
| Begin(IsolationLevel) | Begins a nested database transaction passing the isolation level |
The IsolationLevel allows you to lock your transaction in various ways. The default isolation level is ReadCommitted, which allows you to alter data during a transaction. If you use an isolation level of RepeatableRead, locks are placed on all the data, so you can't alter the data in this transaction. If you lock at the Serializable level, locks are placed on the entire DataSet, preventing changes to all the data in the DataSet. Table 4-16 describes different isolation levels.
| ISOLATION LEVEL | DESCRIPTION |
|---|---|
| ReadCommitted (default) | Locks are shared to prevent inconsistent reads between multiple users. Data can be altered during the transaction. |
| ReadUncommitted | Locks are not placed on the data, so a dirty read is possible. A dirty read occurs when a transaction reads some data that has been added to the table but not committed yet. |
| RepeatableRead | Locks are placed on all the data of the database query, so the data can't be altered during a read. |
| Chaos | The changes made on transactions awaiting commitment can't be altered. |
| Serializable | A range lock is placed on an entire DataSet, preventing changes from being made to the DataSet |
| Unspecified | The IsolationLevel can't be determined. |
Using Concurrency in ADO.NET
The ADO.NET model assumes that optimistic concurrency is the default concurrency model because of the disconnected nature of data in ADO.NET. A user reads data in a DataSet through a DataAdapter, and data is available to a user as a local copy of the data. The server database is available to all other users.
Even though the ADO.NET model supports optimistic concurrency by default, that doesn't mean you can't implement pessimistic concurrency in ADO.NET. The following two examples show you how to handle both cases.
Listing 4-43 shows you how to implement optimistic concurrency. You can handle the optimistic concurrency by creating an Update command that checks the database to make sure the original data of the database row hasn't changed when an immediate Update is about to be performed. It does this by creating two sets of parameters for the Update command: a current set of parameters and an original set of parameters. The original parameters maintain the data that was originally read in from the DataSet. If the data has changed in the data source, when you run the Update command with the WHERE clause filter, the filter won't find the row and an Update won't occur. If the data has not changed in the data source, then the WHERE clause will find the original row you're updating, and the row will be updated with the new data. Listing 4-43 has a built-in WHERE clause on the original data. The IDE generates parameters such as @ShipCity and @ShipDate. The framework even generates the Update command (which has been shortened in the example).
Listing 4-43: Optimistic Concurrency
|
Private Sub TestOptimisticConcurrency() Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) conn.Open() Try Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Orders", conn) Dim ds As DataSet = New DataSet("test") Dim updateCmd As SqlCommand = New SqlCommand() updateCmd.CommandText = "UPDATE Orders SET CustomerID = @CustomerID," & _ "OrderDate = @OrderDate, ShippedDate = @ShippedDate WHERE " & _ "(OrderID = @Original_OrderID) AND (CustomerID = @Original_CustomerID " & _ "OR @Original_CustomerID IS NULL AND CustomerID IS NULL) AND " & _ "(OrderDate = @Original_OrderDate OR @Original_OrderDate " & _ "IS NULL AND OrderDate IS NULL) AND (ShippedDate = " & _ "@Original_ShippedDate OR @Original_ShippedDate IS NULL AND " & _ "ShippedDate IS NULL); SELECT CustomerID, OrderDate, ShippedDate, " & _ "OrderID FROM Orders WHERE (OrderID = @OrderID)" updateCmd.Connection = conn ' CustomerID parameter updateCmd.Parameters.Add(New SqlParameter _ ("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID")) ' OrderDate parameter updateCmd.Parameters.Add(New SqlParameter _ ("@OrderDate", SqlDbType.DateTime, 8, "OrderDate")) ' ShippedDate parameter updateCmd.Parameters.Add(New SqlParameter _ ("@ShippedDate", SqlDbType.DateTime, 8, "ShippedDate")) updateCmd.Parameters.Add(New SqlParameter _ ("@Original_OrderID", SqlDbType.Int, 4, _ ParameterDirection.Input, False, _ (CType((0), System.Byte)), (CType((0), System.Byte)), _ "OrderID", DataRowVersion.Original, Nothing)) updateCmd.Parameters.Add(New SqlParameter _ ("@Original_CustomerID", SqlDbType.NVarChar, _ 5, ParameterDirection.Input, False, (CType((0), System.Byte)), _ (CType((0), System.Byte)), "CustomerID", _ DataRowVersion.Original, Nothing)) updateCmd.Parameters.Add(New SqlParameter _ ("@Original_OrderDate", SqlDbType.DateTime, _ 8, ParameterDirection.Input, False, (CType((0), System.Byte)), _ (CType((0), System.Byte)), "OrderDate", DataRowVersion.Original, Nothing)) updateCmd.Parameters.Add(New SqlParameter _ ("@Original_ShippedDate", SqlDbType.DateTime, _ 8, ParameterDirection.Input, False, (CType((0), System.Byte)), _ (CType((0), System.Byte)), "ShippedDate", _ DataRowVersion.Original, Nothing)) updateCmd.Parameters.Add(New SqlParameter("@OrderID", _ SqlDbType.Int, 4, "OrderID")) da.UpdateCommand = updateCmd da.Fill(ds, "Orders") ' update the row in the dataset ds.Tables("Orders").Rows(0).BeginEdit() ds.Tables("Orders").Rows(0)("OrderDate") = DateTime.Now ds.Tables("Orders").Rows(0)("ShipCity") = "Leone" ds.Tables("Orders").Rows(0).EndEdit() ' update the row in the data Source (Orders Table) da.Update(ds, "Orders") MessageBox.Show("Finished updating first row.") ' close connection conn.Close() conn.Dispose() Catch ex As SqlException ' close connection conn.Close() conn.Dispose() MessageBox.Show(ex.Message.ToString()) End Try End Sub |
| Note | To test these samples, create a Windows application, add references to the System.Data and System.Data.SqlClient namespaces, add two Button controls to the form, write code on the button event handlers, and make sure SQL Server is up and running. |
Another way of handling optimistic concurrency that you may be familiar with is by checking to see if a timestamp on the data source row has changed or the row version number has changed on the row being updated.
The data providers don't really support pessimistic locking on the database because the connection to the database is not kept open, so you must perform all locking with business logic on the DataSet.
You can do a form of pessimistic concurrency, however, using ADO.NET on the data source through transactions. The way to do this is to keep the connection open on the database and create a transaction that has a certain isolation level on a row. Listing 4-44 opens a connection and creates a transaction that locks out the rows used in the Update of the Orders table in the Northwind database.
Listing 4-44: Pessimistic Concurrency
|
Private Sub TestPessimisticConcurrency() Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) conn.Open() Try ' Create a transaction that locks the records of the query Dim tr As SqlTransaction = conn.BeginTransaction _ (IsolationLevel.RepeatableRead, "test") ' Create a command that updates the order of ' the database using the transaction\ Dim cmd As SqlCommand = New SqlCommand("UPDATE Orders SET " & _ "ShippedDate = '5/10/01', ShipCity = 'Columbus' WHERE " & _ "OrderID = 10248", conn, tr) ' Execute the update cmd.ExecuteNonQuery() ' Generate message MessageBox.Show("Wait for keypress...") ' transaction is committed As tr.Commit() conn.Close() Catch ex As SqlException MessageBox.Show(ex.Message.ToString()) End Try End Sub |
Understanding Rollback, Commit, and Savepoints
The Sql data provider provides some additional methods for dealing with transactions involving savepoints (which may not be available in all data providers). Savepoints allow you to roll back to a "bookmarked" point in the transaction. Table 4-17 describes these methods.
| METHOD | DESCRIPTION |
|---|---|
| Rollback(SavePoint) | Performs a rollback on a transaction to the previous database state. |
| Begin(IsolationLevel) | Begins a nested database transaction passing the isolation level. |
| Save(SavePointName) | Equivalent to the Transact-SQL SAVE TRANSACTION in the SQL Server database. Allows you to create a savepoint so that you can roll back to a particular saved state of the database. |
Listing 4-45 shows an example of how you use savepoints in SQL Server. As you can see, first you establish a connection with the Northwind database and open the connection. After that, by calling BeginTransaction on the connection, you can return a SqlTransaction object, which you can use with your Command object. To establish the relationship with the Command object, you then pass the Transaction object in the constructor of Command.
Listing 4-45: Using Savepoints in the Sql Data Provider
|
Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) Dim tran As SqlTransaction = Nothing Try conn.Open() tran = conn.BeginTransaction("Transaction1") Dim cmd As SqlCommand = New SqlCommand(_ "INSERT INTO Customers (CustomerID, ContactName, CompanyName)" & _ "VALUES (516, 'Tim Howard', 'FireCon')", conn, tran) ' Call Save method tran.Save("save1") cmd.ExecuteNonQuery() MessageBox.Show("Tim is in the Database") cmd.CommandText = _ "INSERT INTO Customers (CustomerID, ContactName, CompanyName)" & _ "VALUES (517, 'Bob Hope', 'Hollywood')" ' Call Save again tran.Save("save2") cmd.ExecuteNonQuery() MessageBox.Show("Bob is in the Database") cmd.CommandText = _ "INSERT INTO Customers(CustomerID, ContactName, CompanyName)" & _ "VALUES (518, 'Fred Astaire', 'Hollywood')" MessageBox.Show("Fred is in the Database") ' Save tran.Save("save3") cmd.ExecuteNonQuery() ' rollback tran.Rollback("save2") ' Commit the transaction tran.Commit() MessageBox.Show("Transaction Rolledback, only Tim Made it.") conn.Close() conn.Dispose() Catch exp As Exception If tran Is Nothing Then tran.Rollback() End If MessageBox.Show(exp.Message.ToString() & _ "Transaction Rolledback, Tim didn't make it.") Finally conn.Close() conn.Dispose() End Try |
Now that the transaction is tied to the Command object, you'll save the initial savepoint to the transaction and then execute the first insertion into the database. After that you assign a new SQL Insert to the Command text and save the current transaction savepoint before executing the query. This Insert puts Bob Hope into the database.
Finally, you assign a new SQL Insert to the Command text and save the current transaction savepoint before executing the query. This Insert puts Fred into the database.
By rolling back to the second savepoint, it's as if the second and third ExecuteNonQuery calls never happened, so the first ExecuteNonQuery that puts Tim in the database is the only one that actually gets committed. If there's an exception, then you can roll back the whole transaction.
Executing Batches
Another common concern for developers is the question of how to execute multiple INSERT statements in a batch. Again, using transactions, you can execute multiple queries in a batch and save or roll back changes based on some condition. For example, Listing 4-46 shows you how to execute multiple SQL queries.
Listing 4-46: Executing Multiple SQL Queries Using SqlTransaction
|
Dim ConnectionString As String = "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) ' Open the connection conn.Open() ' Create SqlCommand and SqlTransaction objects Dim myCommand As New SqlCommand() Dim trans As SqlTransaction = Nothing ' Create a local transaction using BeginTransaction ' All changes inside this transaction will be saves or rollback ' after a call of Commit or Rollback trans = conn.BeginTransaction() myCommand.Connection = conn myCommand.Transaction = trans Try myCommand.CommandText = _ "INSERT INTO Employees (FirstName, LastName, Address, City) " & _ " VALUES ('Sundar', 'Lal','234 Lane, Rose Blvd', 'Chester')" myCommand.ExecuteNonQuery() myCommand.CommandText = _ "INSERT INTO Employees (FirstName, LastName, Address, City) " & _ " VALUES ('Dinesh', 'Chand', '23rd Streed, Old Road', 'Delhi')" myCommand.ExecuteNonQuery() myCommand.CommandText = _ "INSERT INTO Employees (FirstName, LastName, Address, City) " & _ " VALUES ('Rajesh', 'Kumar', '278 Meadowlake Drive', 'Downingtown')" myCommand.ExecuteNonQuery() If MessageBox.Show("Save Changes?", "SqlTransaction Commit Rollback", _ MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) _ = MessageBoxDefaultButton.Button1 Then ' Commit transaction and save changes trans.Commit() Else ' Rollback transaction trans.Rollback() End If Catch exp As Exception Dim str As String = "Message :" + exp.Message str = str + "Source :" + exp.Source MessageBox.Show(str) Finally conn.Close() conn.Dispose() End Try |
EAN: 2147483647
Pages: 214