How to Retrieve data using dataset in c#

The System.Data.DataSet class holds data that is retrieved from the database. The DataSet class allows you to hold disconnected data. It means, you can work with the contents of the database while disconnected from the actual database. This is because the contents that you will be working on already exist inside the DataSet. You can consider the DataSet class as a mini database management system inside the memory.

The DataSet Class

The DataSet class has a Tables property which is a collection of systems.Data.DataTable objects. A typical database can contain multiple tables. Just like a database, a DataSet can contain multiple tables as well. Records retrieved from the actual database are stored as a DataTable and added to the Tables property of the DataSet. The DataTable also has properties named Columns and Rows which are collections of DataColumn and DataRow respectively. These properties contain the individual columns and rows of the table from the database. The good thing about the DataTable and DataColumn is that they can be assigned with names. This is done by using the TableName and ColumnName properties or using their constructors.

Let's create a simple DataTable and add it to the DataSet. Create a new Windows Forms Application and name it DataSetform. Add a DataGridView from the toolbar and set its Dock property to Fill.

Double click the title bar of the form to generate an event handler for the form's Load event. First, be sure to import the System. Data namespace. Use the following code for the Load event handler.

Code for Retrieve data using dataset in c#

C#

private void Form1_Load(object sender, EventArgs e) {     //Initialize components     DataSet personsDataSet = new DataSet();     DataTable personsTable = new DataTable("Persons");     DataColumn firstNameColumn = new DataColumn("FirstName");     DataColumn lastNameColumn = new DataColumn("LastName");     DataColumn ageColumn = new DataColumn("Age");     DataRow firstPerson;     DataRow secondPerson;     DataRow thirdPerson;       //Construct the table's column     personsTable.Columns.AddRange(new DataColumn[] {         firstNameColumn,         lastNameColumn,         ageColumn });       //Create three rows/records     firstPerson = personsTable.NewRow();     secondPerson = personsTable.NewRow();     thirdPerson = personsTable.NewRow();       //Assign values to rows     firstPerson["FirstName"] = "Mark";     firstPerson["LastName"] = "Davidson";     firstPerson["Age"] = 18;       secondPerson["FirstName"] = "John";     secondPerson["LastName"] = "Fredrich";     secondPerson["Age"] = 22;       thirdPerson["FirstName"] = "Chelsey";     thirdPerson["LastName"] = "Bells";     thirdPerson["Age"] = 19;       //Add the rows to the table     personsTable.Rows.Add(firstPerson);     personsTable.Rows.Add(secondPerson);     personsTable.Rows.Add(thirdPerson);       //Add the table to the DataSet     personsDataSet.Tables.Add(personsTable);       dataGridView1.DataSource = personsDataSet.Tables["Persons"]; }

Lines 4-11 declare the different classes for creating a complete DataTable and DataSet.

Notice the passed names inside the constructor for the DataTable and each DataColumn. This will make them easier to reference when we call them using the DataSet.

 

Lines 14-17 add the created DataColumn object to the Columns property of the DataTable. It is important that you do this first before specifying the rows. Lines 20-22 initializes each DataRow object using the DataTable.NewRow() method which returns a DataRow object that already has the proper number of values it can have. Lines 25-35 assigns values to each field of the DataRow objects. Note that each field was accessed via indexers that accept the name of the corresponding DataColumn. 

To complete the DataTable lines 38-40 adds all the rows to the DataTable.Rows property. Finally, we added the DataTable object to the Tables property of the DataSet. To access the table that we have added, we can use an indexer for the Tables property and pass the name of the table.

We assign the table to the DataSource property of the DataGridView so it will show the contents of the DataTable that we have created. Run the program and you will see the records of the table inside the DataGridView control.

Do you know:-

• The Command Class | C# | TrickCode
• C# DataReader ADO.NET |Trickcode
• What is disconnected data access?
• how to Adding Parameters to Commands
• SQL Basics?
• What is Database Preparations?
• SQL vs NoSQL or MySQL vs MongoDB
• What is data provider in C#?
• What is connection string C#?
• [SQL] SELECT Statement | Example
• how to get return value from stored procedure in sql server
• how to prevent sql injection attacks
• What is Database? What is SQL?
• C#.Net How To: Send email in asp.net using c#

If you like the tutorial, then please share this tutorial with your friends on social media.