ADO.Net Questions - Part 2

  1. How can we save all data from DataSet?

    Dataset has AcceptChanges method which commits all the changes since last time AcceptChanges has been executed.

  2. How to check that some changes have been made to dataset since it was loaded?

    For tracking down changes DataSet has two methods which comes as rescue GetChanges and HasChanges.

    • GetChanges
      Returns DataSet which are changed since it was loaded or since AcceptChanges was executed.
    • HasChanges
      This property indicates that has any changes been made since the dataset was loaded or AcceptChanges method was executed. If we want to revert or abandon all changes since the dataset was loaded use RejectChanges.

    Note: One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database. As Dataset is disconnected and do not know anything about the changes happening in actual database.

  3. How can we add/remove row’s in “DataTable” object of “DataSet” ?

    “Datatable” provides “NewRow” method to add new row to “DataTable”. “DataTable” has “DataRowCollection” object which has all rows in a “DataTable” object. Following are the methods provided by “DataRowCollection” object :

    • Add

      Adds a new row in DataTable

    • Remove

      It removes a “DataRow” object from “DataTable”

    • RemoveAt

      It removes a “DataRow” object from “DataTable” depending on index position of the “DataTable”.

  4. What is basic use of “DataView” ?

    “DataView” represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data with in “Datatable”. Dataview has the following method’s :

    • Find

      It takes a array of values and returns the index of the row.

    • FindRow

      This also takes array of values but returns a collection of “DataRow”.

      If we want to manipulate data of “DataTable” object create “DataView” (Using the “DefaultView” we can create “DataView” object) of the “DataTable” object and use the following functionalities :

    • AddNew

      Adds a new row to the “DataView” object.

    • Delete

      Deletes the specified row from “DataView” object.

  5. What is the difference between “DataSet” and “DataReader” ?

    Following are the major differences between “DataSet” and “DataReader” :

    • “DataSet” is a disconnected architecture, while “DataReader” has live connection while reading data. If we want to cache data and pass to a different tier “DataSet” forms the best choice and it has decent XML support.
    • When application needs to access data from more than one table “DataSet” forms the best choice.
    • If we need to move back while reading records, “datareader” does not support this functionality.
    • But one of the biggest drawbacks of DataSet is speed. As “DataSet” carry considerable overhead because of relations, multiple tables etc speed is slower than “DataReader”. Always try to use “DataReader” wherever possible, as it’s meant specially for speed performance.

  6. How can we load multiple tables in a DataSet?

    objCommand.CommandText = "Table1"
    objDataAdapter.Fill(objDataSet, "Table1")
    objCommand.CommandText = "Table2"
    objDataAdapter.Fill(objDataSet, "Table2")

    Above is a sample code which shows how to load multiple “DataTable” objects in one “DataSet” object. Sample code shows two tables “Table1” and “Table2” in object ObjDataSet.


    lstdata.DataSource = objDataSet.Tables("Table1").DefaultView

    In order to refer “Table1” DataTable, use Tables collection of DataSet and the Defaultview object will give you the necessary output.

  7. How to add relations between tables in a DataSet?

    Dim objRelation As DataRelation
    objRelation=New DataRelation("CustomerAddresses", _
    objDataSet.Tables("Customer").Columns("Custid"),objDataSet.Tables("Addresses").Columns("Custid_fk"))
    objDataSet.Relations.Add(objRelation)

    Relations can be added between “DataTable” objects using the “DataRelation” object. Above sample code is trying to build a relationship between “Customer” and “Addresses” “Datatable” using “CustomerAddresses” “DataRelation” object.

  8. What is the use of CommandBuilder?

    CommandBuilder builds “Parameter” objects automatically. Below is a simple code which uses commandbuilder to load its parameter objects.

    Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
    pobjCommandBuilder.DeriveParameters(pobjCommand)

    Be careful while using “DeriveParameters” method as it needs an extra trip to the Datastore which can be very inefficient.

  9. What’s difference between “Optimistic” and “Pessimistic” locking?

    In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking. In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days browser based application is very common and having pessimistic locking is not a practical solution.

  10. How many ways are there to implement locking in ADO.NET ?

    Following are the ways to implement locking using ADO.NET :

    • When we call “Update” method of DataAdapter it handles locking internally. If the DataSet values are not matching with current data in Database it raises concurrency exception error. We can easily trap this error using Try..Catch
      block and raise appropriate error message to the user.
    • Define a Datetime stamp field in the table.When actually you are firing the UPDATE SQL statements compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This is the best practice used by industries for locking.
      Update table1 set field1=@test where LastTimeStamp=@CurrentTimeStamp
    • Check for original values stored in SQL SERVER and actual changed values. In stored procedure check before updating that the old data is same as the current. Example in the below shown SQL before updating field1 we check that is the old field1 value same. If not then some one else has updated and necessary action has to be taken.
      Update table1 set field1=@test where field1 = @oldfield1value

      Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures.

  11. How can we perform transactions in .NET?

    The most common sequence of steps that would be performed while developing a transactional application is as follows:

    • Open a database connection using the Open method of the connection object.
    • Begin a transaction using the Begin Transaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the Begin Transaction method will be committed to the database immediately after they execute. Set the Transaction property of the command object to the above mentioned transaction object.
    • Execute the SQL commands using the command object. We may use one or more command objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object.
    • Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
    • Close the database connection.

  12. What is difference between Dataset.Clone and Dataset.Copy?

    Clone: It only copies structure, does not copy data.

    Copy: Copies both structure and data.

  13. Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?

    There two main basic differences between recordset and dataset :

    • With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with recordset this is not possible
    • All representation of Dataset is using XML while recordset uses COM.
    • Recordset can not be transmitted on HTTP while Dataset can be.

  14. Explain in detail the fundamental of connection pooling?

    When a connection is opened first time a connection pool is created and is based on the exact match of the connection string given to create the connection object. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened, and connection pooling won’t be used

  15. What is Maximum Pool Size in ADO.NET Connection String?

    The maximum number of connections allowed in the pool. By default, the max pool size is 100. If we try to obtain connection more than max pool size, then ADO.NET waits for Connection Timeout for the connection from the pool. If even after that connection is not available, we get the exception:

    Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

January 26, 2008 | Posted Under ADO.Net Questions 

Comments

Leave a Reply

You must be logged in to post a comment.