ADO.NET tutorial for beginners

Part 15 Disconnected data access in asp net

Link for code samples used in the demo
http://csharp-video-tutorials.blogspot.com/2013/10/part-15-disconnected-data-access-in.html

Link for csharp, asp.net, ado.net, dotnet basics, mvc and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists

ADO.NET offers two data access modes
1. Connection oriented data access
2. Disconnected data access

In this video, we will discuss disconnected data access. SqlDataAdapter and DataSet objects together provide disconnected data access.

A DataSet is an in-memory data store that can hold one or more tables. DataSets only hold data and do not interact with the underlying database table. The DataSet object has no knowledge of the underlying Data Source. It is the SqlDataAdapter object that retrieves data from the datasource. This is how it works.
1. You create an instance of SqlDataAdapter by specifying a select command and a connection object
string connectionString = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
string selectQuery = “Select * from tblStudents”;
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectQuery, connection);
2. When SqlDataAdapter.Fill() method is invoked, SqlDataAdapter opens the connection to the database, executes the select command, and the DataSet is populated with the data that is retrieved. The SqlDataAdapter automatically closes the connection.
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, “Students”);
3. You now have data in the DataSet and there is no active connection to the database. At this point you can make any changes(insert, update, delete) to the data in the DataSet. Only the data in the DataSet is changed, the underlying database table data is not changed.
4. To update the underlying database table, invoke SqlDataAdapter.Update() method. Make sure there is an UPDATE, DELETE and INSERT command are associated with SqlDataAdapter object when Update() method is called, otherwise there would be a runtime exception.
dataAdapter.Update(DataSetObject, “Students”);