Where and How to use DataSet
How to use DataSet?
For using DataSet to create the in-memory structure of data retrieved from the SQL Server Database you can use SqlCommand and SqlDataAdapter objects.
You need a SqlConnection object also to initialize and open the database connection to allow the execution of sql command. SqlCommand object accepts the parameters as Sql Query or stored procedure name you have created at back end in sql server programmability section.
SqlDataAdapter object takes SqlCommand and SqlConnection object and enables you to perform the data access without explicitly open or close the sql data connection. SqlDataAdapter Fill method performs automatically, it opens the database connection fills the DataSet passed and closed the database connection. If Sql Data connection is in open state before calling the Fill method then SqlDataAdapter leaves it open after performing the task.
C# Code to Access Data Using DataSet
// string variable to store the connection string // defined in appsettings section of web.config file. string connStr = ConfigurationManager.AppSettings["ConnectionString"].ToString(); // object created for SqlConnection Class. SqlConnection mySQLconnection = new SqlConnection(connStr); SqlCommand mySqlCommand = new SqlCommand( "SQL QUERY OR STORED PROC GOES HERE", mySQLconnection); SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand); DataSet myDataSet = new DataSet(); mySqlAdapter.Fill(myDataSet);
Where to use DataSet?
You can use the DataSet Filled by using SqlDataAdapter when your requirements are as follows:
- You need a disconnected in-memory cached data that can be passed to the components of the web application.
- You have data bound control that supports only IList generic collection to the bind the data with it.
- You want to update the multiple rows in a single batch.
- You want to save the data retrieved from the database in the form of XML along with its schema. Also you want the functionality to manipulate it.
- You need a relational view of data retrieved.
Continue to next tutorial: Where and How to use DataReader? to learn where to use connected data access method.