In this Video, I am going to discuss ADO.NET DataSet using SQL Server. Please read our previous Video where we discussed the basics of ADO.NET DataSet and the different constructors, properties, and methods of DataSet Class. At the end of this Video, you will understand how to use ADO.NET DataSet with the SQL Server database. We will see how to store one as well as multiple tables into the dataset. We will also see how to set explicitly the table names for the dataset tables.
Example to understand DataSet using SQL Server:
We are going to use the following Customers and Orders tables to understand the ADO.NET DataSet object using the SQL Server data table.
Please use the below SQL Script to create a database and tables and populate the Customers and Orders tables with the required test data.
Our business requirement is to fetch all the data from the Customers table and then need to display it on the console. The following example exactly does the same using DataSet. In the below example, we created an instance of the DataSet and then fill the dataset using the Fill method data adapter object.
By default, the dataset assigns a name to the table as Table, Table1, Table2. So, the above example can be rewritten as shown below and it should give the same output as the previous example. As you can see, here, we are fetching the table using the name (Table).
DataSet with Multiple Database Tables using SQL Server:
It is also possible that your SQL Query may return multiple tables. Let us understand this with an example. Now our business requirement is to fetch the Customers as well as Orders table data and needs to display on the Console. Here, you can access the first table from the dataset using an integral index 0 or string Table name. On the other hand, you can access the second table using the integral index 1 or the string name Table1.
Using Integral Index Position:
Using Table Name:
It will also give the same output as the previous example.
Setting the table name explicitly in DataSet:
If your dataset going to contain multiple tables data, then it is very difficult for you to identify using the integral index position or using the default name. In such a scenario, it is always recommended to provide an explicit name for the table.
Let us understand this with an example. Now, we need to set the first table as Customers and the second table as Orders and then we will see how to use these custom table names to fetch the actual table data. You can set the table name using the TableName property as shown below.
The following is the complete example that uses the tableName property of the dataset object to set and get the table name.