Better use for datasets

One of the great features introduced by microsoft in the .net technology is the dataset . The dataset is the object corresponding the the traditional ADO recordset . However the dataset has many significant differences

1)The dataSet can hold the results of many SQL queries .

2)You can use the dataset while the connection is closed .

3)You can create a dataset from an XML file .

4)You can write XML directly from a dataset .

While some may say that the recordset can offer some of these features , but sure will not be flexible and powerful as ADO.NET datasets .We will discuss now very nice feature in the dataset to save your database server resources . Most people use the dataset for returning a query result from the database . We will use the dataset now to return multiple result sets that is very helpful when you are filling drop down controls with some thing from lookup tables . Assuming that we have a connection ready to SQL server , using the pubs sample database , you can create a sqldataadapter like that :

    sqlDataAdapter1.SelectCommand.CommandText="SELECT * FROM titles;SELECT * FROM authors" ;

you can see that we separate the SQL statment that returns each result set with a semi colon , a space would work but this is more readable .We can then fill a dataset with this code

DataSet ds= new DataSet();

sqlDataAdapter1.Fill( ds ) ;

now we have a dataset with 2 tables if you bind the dataset to a grid you will have the 2 tables availiable to choose from You may want to use the data of each table to bind a user control you can say

listBox1.DisplayMember="title" ;

listBox1.ValueMember="title_id" ;

listBox1.DataSource=ds.Tables[0] ;

as you see the tables collection contain a table for each returned result set , so there is no limit on the number of result sets that you can return from the database just a final note this code will not work with MySQL databases , it doesnt support returning multiple databsets As for oracle , you will have to use reference Cursors to achieve the same functionality .

 But what if we do not wish to use a database to keep our data in an application . is there a way for us to load from files ? Yes you can create XML files containing the data you need . DataSets even provide a very easy method for wrting XML files containing the data . if you call

    ds.write( filename ) ;

you can save an entire dataset to an XML file . It has 8 overloads , the simplest one just takes a file name to save to . If you then want to read this data again into a dataset you just create a dataset and use the ReadXml method on the file name you wish and its all done . You have a woking dataset that you can bind to controls or use how ever you wish .