DataSet using SQL Server

ADO.NET DataSet using SQL Server

In this article, I am going to discuss ADO.NET DataSet using SQL Server. Please read our previous article where we discussed the basics of ADO.NET DataSet and the different constructors, properties, and methods of DataSet Class. At the end of this article, 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 table name 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.

DataSet using SQL Server

Please use below SQL Script to create a database and tables and populate the Customers and Orders tables with the required test data.

CREATE DATABASE ShoppingCartDB;
GO

USE ShoppingCartDB;
GO

CREATE TABLE Customers(
 ID INT PRIMARY KEY,
 Name VARCHAR(100),
 Mobile VARCHAR(50)
)
GO

INSERT INTO Customers VALUES (101, 'Anurag', '1234567890')
INSERT INTO Customers VALUES (102, 'Priyanka', '2233445566')
INSERT INTO Customers VALUES (103, 'Preety', '6655443322')
GO

CREATE TABLE Orders(
 ID INT PRIMARY KEY,
 CustomerId INT,
 Amount INT
)
GO

INSERT INTO Orders VALUES (10011, 103, 20000)
INSERT INTO Orders VALUES (10012, 101, 30000)
INSERT INTO Orders VALUES (10013, 102, 25000)
GO
Example:

Our business requirement is to fetch all the data from the Customers table and then need to display 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.

using System;
using System.Data;
using System.Data.SqlClient;

namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=ShoppingCartDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from customers", connection);

                    //Creating DataSet Object
                    DataSet dataSet = new DataSet();

                    //Filling the DataSet
                    dataAdapter.Fill(dataSet);

                    //Iterating through the DataSet 
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Mobile"]);
                    }
                }

                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

Example to understand DataSet using SQL Server:

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).

Fetching DataSet tables using the tableName

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 System;
using System.Data;
using System.Data.SqlClient;

namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=ShoppingCartDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    //Sql Command return data from customers and orders table
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from customers; select * from orders", connection);
                    DataSet dataSet = new DataSet();                    
                    dataAdapter.Fill(dataSet);

                    // First Table
                    Console.WriteLine("Table 1 Data");
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Mobile"]);
                    }
                    Console.WriteLine();

                    // Second Table
                    Console.WriteLine("Table 2 Data");
                    foreach (DataRow row in dataSet.Tables[1].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["CustomerId"] + ",  " + row["Amount"]);
                    }

                }
                
                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

DataSet with Multiple Databse Tables using SQL Server

Using Table Name:
using System;
using System.Data;
using System.Data.SqlClient;

namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=ShoppingCartDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    //Sql Command return data from customers and orders table
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from customers; select * from orders", connection);
                    DataSet dataSet = new DataSet();                    
                    dataAdapter.Fill(dataSet);

                    // First Table
                    Console.WriteLine("Table 1 Data");
                    foreach (DataRow row in dataSet.Tables["Table"].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Mobile"]);
                    }
                    Console.WriteLine();

                    // Second Table
                    Console.WriteLine("Table 2 Data");
                    foreach (DataRow row in dataSet.Tables["Table1"].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["CustomerId"] + ",  " + row["Amount"]);
                    }
                }
                
                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

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.

Setting the table name explicitly in DataSet

The following is the complete example that uses the tableName property of the dataset object to set and get the table name.

using System;
using System.Data;
using System.Data.SqlClient;

namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConnectionString = "data source=.; database=ShoppingCartDB; integrated security=SSPI";
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    //Sql Command return data from customers and orders table
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from customers; select * from orders", connection);
                    DataSet dataSet = new DataSet();                    
                    dataAdapter.Fill(dataSet);

                    //Setting the table name explicitly
                    dataSet.Tables[0].TableName = "Customers";
                    dataSet.Tables[1].TableName = "Orders";
                   
                    Console.WriteLine("Customers Table Data");
                    //Fetching the table using the custom table name
                    foreach (DataRow row in dataSet.Tables["Customers"].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Mobile"]);
                    }
                    Console.WriteLine();

                    // Second Table
                    Console.WriteLine("Orders Table Data");
                    //Fetching the table using the custom table name
                    foreach (DataRow row in dataSet.Tables["Orders"].Rows)
                    {
                        Console.WriteLine(row["Id"] + ",  " + row["CustomerId"] + ",  " + row["Amount"]);
                    }
                }
                
                Console.ReadKey();
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

ADO.NET DataSet using SQL Server Database

In the next article, I am going to discuss the Architecture of DataSet in C#. Here, in this article, I try to explain how to use ADO.NET DataSet using the SQL Server database with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this ADO.NET DataSet using the SQL Server article.

Leave a Reply

Your email address will not be published. Required fields are marked *