· Types Of Transactions
1) Database Transactions: Where database-specific statements control the transaction and it is carried out within the database itself. Usually the stored procedure within the database contains the transaction statements.
2) Connection-Based Transactions: Where the statements that control the transactions are outside the database. Usually this is a feature of the Connection object.
· To start a transaction, call the BeginTransaction method of the current Connection object. This returns a Transaction object that you must then assign to any Command object that you want to enroll into that transaction. To commit or rollback the transaction call the respective methods of the Transaction object (note that it's not a method of Connection object).
· You can enroll a Command object into a transaction only after setting its Connection property. If you want to change the Connection property afterwards you first have to un-enroll it by setting the Transaction property of the Command object to nothing. You can use same Command object in a transaction multiple times to execute various SQL statements.
· The DataAdapter only processes one command at a time.
· You can use a direct method to define a DataAdapter object without first creating a Connection or Command object as:
Dim ObjDataAdapter As New SqlDataAdapter ("SQL-Statement",
"Connection-String")
· Command Builder Object
Either you have to define InsertCommand, UpdateCommand, and DeleteCommand objects explicitly to push back changes from a DataSet to database or you can use the CommandBuilder object to automatically generate these objects. All you have to do is create a CommandBuilder object, specifying as the parameter to its constructor the DataAdapter object:
Dim ObjComBuilder As New CommandBuilder(ObjDataAdapter)
Then when you call the Update method of this DataAdapter, it will automatically use the CommandBuilder to create Insert, Update and Delete commands for the process, and assign them to the InsertCommand, UpdateCommand and DeleteCommand properties of the DataAdapter. You can use CommandBuilder's GetInsertCommand, GetUpdateCommand and GetDeleteCommand methods to retrieve the auto-generated commands before the Update method of the DataAdapter is called, because after that these properties (i.e. InsertCommand) will be set to Nothing.
The limitations of auto-generated commands are:
1) The rows in a table in the DataSet must have originally come from a single table.
2) The source table must have a primary key defined, or it must have at least one column that contains unique values. This column must be included in the Select statement.
One other useful feature that the CommandBuilder provides is the ability to automatically create appropriate Parameter objects. You can use this feature when you are using a stored procedure to update the data source. DeriveParameters method returns the Command object with its Parameter collection populated with the appropriate Parameter objects. All that's required then is to fill in the values. However, be aware that the DeriveParameters method requires an extra call to the data store to get information about the parameters, and so is generally inefficient.
ObjCommandBuilder.DeriveParameters(ObjCommand)
ObjCommand.Parameters("Param1-Name").Value = Value1
ObjCommand.Parameters("Param2-Name").Value = Value2
· If the connection to database has not been open yet and if you use DataAdapter to perform the operation on the database, DataAdapter automatically opens the connection to database and closes after the operations are performed.
· Updating With A DataAdapter
You have a DataSet object with tables filled with data. You make the changes in the rows of a table. Then you can call the DataAdapter's Update method to push back those changes back into database.
Dim ObjCommandBuilder As New SqlCommandBuilder
(ObjDataAdapter)
ObjConnection.Open()
ObjTransaction = ObjConnection.BeginTransaction()
ObjDataAdapter.InsertCommand.Transaction = ObjTransaction
ObjDataAdapter.UpdateCommand.Transaction = ObjTransaction
ObjDataAdapter.DeleteCommand.Transaction = ObjTransaction
Dim RowsAffected As Integer
RowAffected = ObjDataAdapter.Update(ObjDataSet, "Books")
ObjTransaction.Commit()
· Updating Multiple Tables Data
You can have multiple tables and relationship between them in a DataSet. If you modified all table and using the Update method of the DataAdapter, you have to maintain the order of table in the code while calling the Update method. For example if your DataSet contains two tables one is parent and other is child (same relationship is exist at database end also) and suppose you have inserted new rows in both tables. Then you cannot call the Update method for the child table before the parent, because in this case at database end there would be no matching rows in the parent table before the child insert the new rows. So it will cause a database exception as you are breaking the referential integrity. So the correct order is:
ObjDataAdapter.Update (ObjDataSet, "Parent-Table")
ObjDataAdapter.Update (ObjDataSet, "Child-Table")
And if you have deleted some rows in both tables you have to reverse the above mention order, as you cannot delete the rows from the parent table as far as the child tables contain the matching rows.
· If you have created a table mapping in the DataSet for the default table, you can execute the Update method without specifying the table name. An error occurs if this mapping does not exist when the Update method is called without specifying the name of the table.
· Updating Subsets Of Rows From A Table
The DataAdapter's Update method can also be used to push changes from a collection or array of DataRow objects into the data source. All the rows must come from the same source table, and there must be a default table mapping.
Dim ArrRows() As DataRow
ArrRows = ObjDataSet.Tables(0).Rows.All
ObjDataAdapter.Update(ArrRows)
· Updating From A DataSet Using Stored Procedures
When you create a Table in a DataSet using a complex select query (as using JOIN in the query, so that the data is coming from more than one table), you cannot use the CommandBuilder's auto-generated commands to update the data source. Instead you define the stored procedures for each tack (i.e. Insert, Update, Delete) and specify them to DataAdapter and then update the data from the DataSet. To implement the above strategy use the three stored procedures as the command text for the Command objects in the DataAdapter object's InsertCommand, UpdateCommand and DeleteCommand properties. You specify which columns will provide the values for the parameters when the Command is executed, rather than specifying actual values for the parameters. To specify a dynamic parameter, set the SourceColumn property of the Parameter object to the name of the column from which the value for the parameter will come. As each column can expose four different values (the DataRowVersion): Original, Current, Default and Proposed. You can specify which of these values you want the parameter to use by setting the SourceVersion property of the Parameter object as well. This means you can specify the Original value of the column as the parameter value which is going to be used in the WHERE clause in the query or the Current value of the column if you are updating that column in the table.
Dim ObjUpdateCommand As New SqlCommand ("Sp_Update",
ObjConnection)
ObjUpdateCommand.CommandType = CommandType.StoredProcedure
Dim ObjParam As SqlParameter
ObjParam = ObjUpdateCommand.Parameters.Add("ISBN")
ObjParam.SourceColumn = "ISBN"
ObjParam.SourceVersion = DataRowVersion.Original
ObjParam = ObjUpdateCommand.Parameters.Add("Price")
ObjParam.SourceColumn = "Price"
ObjParam.SourceVersion = DataRowVersion.Current
ObjDataAdapter.UpdateCommand = ObjUpdateCommand
ObjDataAdapter.Update(ObjDataSet, "Books")
· The DataAdapter decides the success or failure of an operation by looking at the number of row affected. If the number of row affected is zero, it assumes that the operation is failed. When there is more then one operation executed at ones (as in case of a stored procedure, where you can define an Insert and an Update operation in the same procedure), the row-affected value of the previous operation is add-up into the row-affected value of the next operation and the combined result is returned. Now suppose in the execution of multiple operations, even if any operation is failed, the overall combined result would not be zero and DataAdapter will assume that the Update operation was a success. To overcome this problem you can set the NOCOUNT value to "On" at the database level (usually in the stored procedure). When the NOCOUNT is ON, the number of rows affected is not added to the return value. (SET NOCOUNT ON)
· In case of InsertCommand execution the Original value of any column in the row would be empty. Likewise, there won't be any Current values if the row has been deleted.
· Update Events In The DataAdapter
The DataAdapter exposes two events: The RowUpdating event occurs before an attempt is made to update the row in the data source, and the RowUpdated event occurs after the row has been updated (or after an error has been detected). You can handle these events just declaring the suitable event handlers for them.
· AcceptChanges And The Update Process
Once the DataAdapter.Update process is complete (in other words all the updates for all the rows have been applied), the AcceptChanges method is called for those rows automatically. So, after an update, the Current values in all the rows moved to the Original values. However, during the update process, the Current and Original values are available in both the RowUpdating and the RowUpdated events.
· Pessimistic Record Locking
When a user wants to update a record, they open it with pessimistic locking, preventing any other user opening the same record in the update mode. Other users can only open the record in 'read' mode until the first user saves their copy and releases their lock on the record.
· Optimistic Record Locking
Within .Net, all data access is done through optimistic record locking, which allows multiple users to open the same record for updating. Locking is only done when one of the users is going to save his record.
· Updating Just The Changed Columns
One way to minimize the concurrency problem is to push only the modified column values (the one that have been changed by this user or process) into the original data store, rather than blindly updating all of the columns. Of course, this means that you can't use the Update method – you have to build and execute each SQL statement yourselves.
You can do this by creating custom SQL statements for each row (through iterating whole table) or you can use the events like RowUpdating and RowUpdated rises in the process of updating through DataAdapter. You can then put your code in the event handler for those events to check the changed columns.
· The general process of collecting together data and transferring it to another location is often referred to as Marshalling.
· Getting The Modified Rows Into A New DataSet
Dim ObjChangeDS As DataSet
ObjChangeDS = ObjDataSet.GetChanges(DataRowState.Modified)
· Locating Errors After An Update Is Complete
We can force the DataAdapter to continue processing the updates for each row even if it encounters an error (rather than terminating the Update process when the first concurrency or other error occurs). All we need to do is set the ContinueUpdateOnError property of the DataAdapter object that is performing the Update to True. Then, whenever an error is encountered, the DataAdapter will simply insert the error message that it receives into the RowError property of the relevant row, and continue with the next updated row. After it finishes up its updating process we can check the HasError property and if we found it true we can either check the individual row's RowError property or we can use GetErrors method of the DataTable to get an array of the rows with errors in them
· On a default Windows 2000 IIS 5.0 installation, the root directory of the default web site is http://localhost or http://<servername>, while the physical path of the root directory is c:\Inetub\wwwroot.
· A virtual directory is an alias that points to a physical directory on the server. It appears as a directory in the web site, but it exists in a separate path. Multiple virtual directories can point to a common set of files or live on the same file share.
· ASP .Net makes use of IIS web applications to identify distinct application domains. Application domains are a feature of the CLR. Each application domain is separate, secure and does not share memory (data such as Session or Application) with other domains. Many application domains may be hosted within a single process. You can think of an application domain as a logical process. When if fails, it does not take down the host process, so one failure won't crash all your ASP .Net applications.
· There can only one bin directory per ASP .Net application.
· The dynamic loading and unloading of the component in the bin directory works because ASP .Net specifically listens for file change notification events within the bin directory. When a change is detected, ASP .Net will create a new application domain to begin servicing the new requests using the new installed component. As soon as the original application domain has completed servicing any outstanding requests, it is removed.
· The Global.asax file is used as an implementation point for global events, objects and variables. There can only be one Global.asax file per web application, and it must be called Global.asax.
· Other than <Script runat="server"/> blocks in the global.asax for the code declaration there are two additional ways of declaring the code:
1) Server-Side Include: You can include a code file into the global.asax. The contents of the file included will be added to the global.asax file before it is compiled. If an include file is used within a global.asax, the application will automatically be restarted whenever the include file change.
<!--#Include [File | Virtual]="Path To File" -->
2) Declarative Object Tags: <Object> tags enable us to declare and instantiate Application and Session objects in global.asax.
<Object id="AppData" runat="server"
class="System.Data.DataSet" scope="Application"/>
We have declared as Application scoped variable named AppData that is of class type System.Data.DataSet. However, the object is not actually created until it is first used.
· ASP .Net provides four ways to manage state for the application:
1) Session: User state is controlled through the Session object.
2) Application: This is control by the Application object.
3) Transient Application State (Cache): Transient Application state is controlled through the Cache object. Cache is similar in functionality to Application, in that it is accessible for the entire web application.
Comments