C# DataSet And DataTable: Save Query Results To DataSet Or DataTable

In this tutorial, we'll demonstrate how to use ADO.NET DataSet and DataTable to save query results from a SQL Server database. We'll use a simple example of an Employee table in a database.

Set up the database

Create a new database and a table called 'Employees':

CREATE DATABASE EmployeeDB;

USE EmployeeDB;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

Insert some sample data into the 'Employees' table:

INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe');
INSERT INTO Employees (FirstName, LastName) VALUES ('Jane', 'Smith');

Create a C# Console Application

Create a new C# Console Application project in Visual Studio and add the following namespaces:

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

Set up the connection string

Create a connection string with the necessary credentials to connect to your SQL Server instance:

string connectionString = "Server=localhost;Database=EmployeeDB;User Id=myUser;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);

Save query results to a DataTable

Create a method called LoadEmployeesToDataTable to load the data from the 'Employees' table into a DataTable:

static DataTable LoadEmployeesToDataTable(SqlConnection connection)
{
    string selectQuery = "SELECT * FROM Employees";
    SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
    DataTable table = new DataTable("Employees");
    
    connection.Open();
    adapter.Fill(table);
    connection.Close();
    
    return table;
}

Save query results to a DataSet

Create a method called LoadEmployeesToDataSet to load the data from the 'Employees' table into a DataSet:

static DataSet LoadEmployeesToDataSet(SqlConnection connection)
{
    string selectQuery = "SELECT * FROM Employees";
    SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection);
    DataSet dataSet = new DataSet();
    
    connection.Open();
    adapter.Fill(dataSet, "Employees");
    connection.Close();
    
    return dataSet;
}

Display the contents of a DataTable or a DataSet

Create a method called DisplayEmployees to display the contents of a DataTable or a DataSet:

static void DisplayEmployees(DataTable table)
{
    Console.WriteLine("EmployeeID\tFirstName\tLastName");
    Console.WriteLine("---------------------------------------");

    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine($"{row["EmployeeID"]}\t\t{row["FirstName"]}\t\t{row["LastName"]}");
    }
}

Test loading query results into a DataTable and a DataSet

In the Main method, call the methods to load query results into a DataTable and a DataSet, and then display the contents:

static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(connectionString);

    Console.WriteLine("Loading data into DataTable:");
    DataTable table = LoadEmployeesToDataTable(connection);
    DisplayEmployees(table);

    Console.WriteLine("\nLoading data into DataSet:");
    DataSet dataSet = LoadEmployeesToDataSet(connection);
    DisplayEmployees(dataSet.Tables["Employees"]);

    Console.ReadLine();
}

When you run the Console Application, you should see the contents of the 'Employees' table displayed in the console for both the DataTable and the DataSet.

In this tutorial, we've shown you how to use ADO.NET DataSet and DataTable to save query results from a SQL Server database in C#.

C# save query results to DataSet example:

Description: This example demonstrates how to execute a SQL query in C# and save the results to a DataSet for further manipulation and use.

Code:

// Create a DataSet
DataSet dataSet = new DataSet();

// Execute a SQL query and fill the DataSet
using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    string queryString = "SELECT * FROM YourTable";
    SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, connection);
    dataAdapter.Fill(dataSet, "YourTable");
}

Saving query results to DataTable in C#:

Description: Focusing on DataTable, this example illustrates how to save the results of a SQL query directly to a DataTable within a DataSet.

Code:

// Create a DataSet with a DataTable
DataSet dataSet = new DataSet();

// Execute a SQL query and fill the DataTable in the DataSet
using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    string queryString = "SELECT * FROM YourTable";
    SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, connection);
    dataAdapter.Fill(dataSet, "YourTable");
}

DataTable dataTable = dataSet.Tables["YourTable"];