This requires a new connection for each page load, which will hurt scalability as the number of users increase.
One way to relieve this is to use a Data Set that is updated one time and stored in cache.
The Sql Data Adapter opens a connection only when required and closes it as soon as it has performed its task.
For example, the Sql Data Adapter performs the following tasks when filling a Data Set with data: In between the Fill and Update operations, data source connections are closed and you are free to read and write data with the Data Set as you need.
These are the mechanics of working with disconnected data.
Because the applications holds on to connections only when necessary, the application becomes more scalable.
The Sql Data Adapter holds the SQL commands and connection object for reading and writing data.
You initialize it with a SQL select statement and connection object: , should have already been instantiated, but not opened.Since that isn’t a requirement for this example, I left it out.Right now, the Data Set is empty and you need a Sql Data Adapter to load it.In following example first we will make a dataset object from Sql Server table and after that we will add a new record and edit an existing record and then update the database according to updated dataset object.Suppose we have a sql server table named ‘tbl_employee.It is the Sql Data Adapter’s responsibility to open and close the connection during Fill and Update method calls.