Loading


ADO.NET

  • Home
  • ADO.NET
  • How to using ADO.NET DataSet with SQL Server?. The Complete ADO.NET Developer Course 2024.
ADO.NET

How to using ADO.NET DataSet with SQL Server?. The Complete ADO.NET Developer Course 2023 [Videos].

By admin / 11-Feb-2022 / in Blog, ADO.NET / 425 Comments / 18 Likes / Share This Post

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

DataSet using SQL Server

Please use the 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


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

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. " + 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. " + 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. " + 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. " + e);
}
Console.ReadKey();
}
}
}

Output:

ADO.NET DataSet using SQL Server Database

Read more
See All

Comments (425 Comments)

Submit Your Comment

See All Posts

Related Posts

ADO.NET / Blog

What is ADO.NET?

In this article, I am going to give you a brief introduction to ADO.NET. As a .NET developer, you should be aware of ADO.NET. At the end of this article, you will understand the following pointers in detail.
11-Feb-2022 /18 /425

ADO.NET / Blog

How to using ADO.NET with SQL Server?

In this article, I am going to discuss ADO.NET using SQL Server Database with Examples. Please read our previous article where we discussed the Architecture of ADO.NET. At the end of this article, you will understand how to connect with SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using SQL Server Management Studio (SSMS) Tool to interact with SQL Server.
11-Feb-2022 /18 /425

ADO.NET / Blog

What is ADO.NET SqlConnection Class in C#?

In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.
11-Feb-2022 /18 /425