005 ADO.Net DataSet

ADO.Net DataSet

DataSet is a class under the. Net Framework. DataSet is in memory representation , can hold large data , copied from database. DataSet contain mainly two object

1) DataTableCollection
2) DataRelationCollection  

DataTableCollection is a collection of tables , maybe or may not be with data . DataRelationCollection  is  a collection of Schema of the tables and relationship between them. Foreign key, Primary key information of tables are store in the data collection relations. DataSet hold data in XML format. DataAdopter execute one or more select command , fetch data from database and fill it to the DataSet . DataSet hold a copy of this data with its schema. DataSet never connected with database, it is disconnected in nature. DataSet are Programmable also, tables and tables collections with or without data can be added to the data set.

Some commonly used of data set features are

Constructor 

• DataSet : Initialise new instance of a DataSet . 

 Properties

• DataSetName : Get or set name of the DataSet .  
• HasErrors : Indicator , Get or Set at any tables in the data set has error or not. 
• Tables: Get the collection of tables in a DataSet . 

Method 

• AcceptChanges : Commit all changes in a DataSet when last loaded. 
• Clear: Removes all tables and rows from a DataSet . 
• Copy: Copy DataSet with tables and schema and with and without data. 
• Dispose: Dispose a DataSet and release all resources used by the DataSet.
• GetXml : Return XML of the data from the DataSet . 
• HasChanges : Indicate any changes in data after the Lord has made or not. 
• Merge: Add datatable in the DataSet . 
• ReadXml : Read XML form a file and load it to DataSet . 
• RejectChanges : Changes after the latest it has been loaded. 
• WriteXml : DataSet inner XML is written in a file.

Here are some widely used functionality of DataSet

1)Add table with data to DataSet 

DataSet ds = new DataSet();

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable();

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

 2)DataSet Tables Loop

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

//Loop throught DataSet Tables

for (int i = 0; i < ds.Tables.Count; i++)

{

Console.WriteLine(ds.Tables[i].TableName.ToString());

}

OutPut :
MyFirstTable
MySecondTable 

3)Load XML  to DataSet

string xml = @"John150";

DataSet ds = new DataSet();

ds.ReadXml(System.Xml.XmlReader.Create(new System.IO.StringReader(xml)));

for (int i = 0; i < ds.Tables.Count; i++)

{

Console.WriteLine(ds.Tables[i].Rows[0]["Name"].ToString());

Console.WriteLine(ds.Tables[i].Rows[0]["RollNumber"].ToString());

}

Output : 
John
150

4)Write XML from DataSet

string xml = @"John150";

DataSet ds = new DataSet();

ds.ReadXml(System.Xml.XmlReader.Create(new System.IO.StringReader(xml)));

System.IO.StringWriter sw = new System.IO.StringWriter();

ds.WriteXml(sw);

string result = sw.ToString();

Console.Write(result);

Output : 

 
    John
    150
 

5)AcceptChanges Example

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

ds.AcceptChanges();

//Loop to get data

for (int i = 0; i < ds.Tables.Count; i++)

{

    for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

    {

     Console.WriteLine(ds.Tables[i].Rows[l][0].ToString());

   }

}

Output :
John1
John2
John3
3,Kolkata4,
Mumbai5,
Bombay 



 6)RejectChanges Example

 

DataSet ds = new DataSet();

DataTable dt = new DataTable("MyFirstTable");

dt.Columns.Add("Name");

dt.Columns.Add("RollNumber");

DataRow nr1 = dt.NewRow();

nr1["Name"] = "John1";

nr1["RollNumber"] = 500;

dt.Rows.Add(nr1);

DataRow nr2 = dt.NewRow();

nr2["Name"] = "John2";

nr2["RollNumber"] = 600;

dt.Rows.Add(nr2);

DataRow nr3 = dt.NewRow();

nr3["Name"] = "John3";

nr3["RollNumber"] = 700;

dt.Rows.Add(nr3);

ds.Merge(dt);

DataTable dt2 = new DataTable("MySecondTable");

dt2.Columns.Add("Address");

dt2.Columns.Add("ZIP");

DataRow nr4 = dt2.NewRow();

nr4["Address"] = "3,Kolkata";

nr4["ZIP"] = 500;

dt2.Rows.Add(nr4);

DataRow nr5 = dt2.NewRow();

nr5["Address"] = "4,Mumbai";

nr5["ZIP"] = 600;

dt2.Rows.Add(nr5);

DataRow nr6 = dt2.NewRow();

nr6["Address"] = "5,Bombay";

nr6["ZIP"] = 700;

dt2.Rows.Add(nr6);

ds.Merge(dt2);

ds.RejectChanges();

//Loop to get data

for (int i = 0; i < ds.Tables.Count; i++)

{

           for (int l = 0; l < ds.Tables[i].Rows.Count; l++)

            {

                 Response.Write(ds.Tables[i].Rows[l][0].ToString());

            }

}

 7)Populate DataSet with SqlDataAdopter

DataSet ds = new DataSet();

string connection = "Data Source= Your Server; initial catalog=Your Database Name;Connect Timeout=6000; User ID= Your User id;Password=Your Password;";

string qry = "SELECT top 100 * from tbl_my_Table";

SqlDataAdapter da = new SqlDataAdapter(qry, connection);

da.Fill(ds);