How can we add/remove row is in “Data Table” object of “Dataset”?
“Data table” provides “NewRow” method to add new row to “Data Table”. “Data Table” has “DataRowCollection” object that has all rows in a “Data Table” object. Following are the methods provided by “DataRowCollection” object:-
Add
Adds a new row in Data Table
Remove
It removes a “Data Row” object from “Data Table”
Remove At
It removes a “Data Row” object from “Data Table” depending on index position of the “Data Table”.
What is basic use of “Data View”?
“Data View” 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 “data table”.
Data view has the following methods:-
Find
It takes an array of values and returns the index of the row.
Find Row
This also takes array of values but returns a collection of “Data Row”.
If we want to manipulate data of “Data Table” object create “Data View” (Using the “Default View” we can create “Data View” object) of the “Data Table” object and use the following functionalities:-
Add New
Adds a new row to the “Data View” object.
Delete
Deletes the specified row from “Data View” object.
What is the difference between “Dataset” and “Data Reader” ?
Twist: - Why is Dataset slower than Data Reader is?Fourth point is the answer to the twist.
Note:- This is my best question and we expect everyone to answer it. It is asked almost 99% in all companies....Basic very Basic cram it.
Following are the major differences between “Dataset” and “Data Reader”:-
• “Dataset” is a disconnected architecture, while “Data Reader” 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, “data reader” does not support this functionality.
• However, one of the biggest drawbacks of Dataset is speed. As “Dataset” carry considerable overhead because of relations, multiple table’s etc speed is slower than “Data Reader”. Always try to use “Data Reader” wherever possible, as it is meant especially for speed performance.
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 “Data Table” 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” Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.
How can we add relation 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 “Data Table” objects using the “Data Relation” object. Above sample, code is trying to build a relationship between “Customer” and “Addresses” “Data table” using “Customer Addresses” “Data Relation” object.
What is the use of Command Builder?
Command Builder builds “Parameter” objects automatically. Below is a simple code, which uses command builder to load its parameter objects.
Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter)
pobjCommandBuilder.DeriveParameters(pobjCommand)
Be careful while using “Derive Parameters” method as it needs an extra trip to the Data store, which can be very inefficient
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 in browser based application it is very common and having pessimistic locking is not a practical solution.
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 Data Adapter 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 Date time 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 I the best practice used by industries for locking.
Update table1 set field1=@test where Last Timestamp=@Current Timestamp
• 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. For more details of how to implementing locking in SQL SERVER read “What are different locks in SQL SERVER?” in SQL SERVER chapter.
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 oneormorecommand 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.
What is difference between Dataset? Clone and Dataset. Copy?
Clone: - It only copies structure, does not copy data.
Copy: - Copies both structure and data.
Can you explain the difference between an ADO.NET Dataset and an ADO Record set?
There two main basic differences between record set and dataset:-
• With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with record set this is not possible
• All representation of Dataset is using XML while record set uses COM.
• Record set cannot be transmitted on HTTP while Dataset can be.
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 will not be used.
What is Maximum Pool Size in ADO.NET Connection String?
Maximum pool size decides the maximum number of connection objects to be pooled. If the maximum pool size is reached and there is no usable connection available the request is queued until connections are released back in to pool. So it’s always a good habit to call the close or dispose method of the connection as soon as you have finished work with the connection object.
How to enable and disable connection pooling?
For .NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLE DB Services=-4 in the connection string.
What extra features does ADO.Net 2.0 have ?
Bulk Copy Operation
Bulk copying of data from a data source to another data source is a newly added feature in ADO.NET 2.0. ADO.NET inrtoduces bulk copy classes which provide fastest way to transfer\ data from once source to the other. Each ADO.NET data provider has bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which can read a DataSet, DataTable, DataReader, or XML objects.
Data Paging
A new method is introduced ExecutePageReader which takes three parameters - CommandBehavior, startIndex, and pageSize. So if you want to get rows ony from 10 - 20, you can simply call this method with start index as 10 and page size as 10.
Batch Update
If you want to update large number of data on set ADO.NET 2.0 provides UpdateBatchSize property, which allows you to set number of rows to be updated in a batch. This increases the performance dramatically as round trip to the server is minimized.
Load and Save Methods
In previous version of ADO.NET, only DataSet had Load and Save methods. The Load method can load data from objects such as XML into a DataSet object and Save method saves the data to a persistent media. Now DataTable also supports these two methods. You can also load a DataReader object into a DataTable by using the Load method.
New Data Controls
In toolbox you can see three new controls - DataGridView, DataConnector, and DataNavigator.
DataReader's New Execute Methods
Some new execute methods introduced are ExecutePageReader, ExecuteResultSet, and ExecuteRow.
No comments:
Post a Comment