Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to add two or more SQL Server database tables in DataSet in ASP.NET

Author: Rachel Nicole

The purpose of this article is to explain how we can add two or more SQL Server database tables in DataSet in ASP.NET.

 

A DataSet may contain multiple data tables. In this article, I will explain that how we can add multiple SQL Server database tables in a DataSet using ADO.NET code. I will use NORTHWIND database to get different tables and fill DataSet with these tables using different techniques.
 
Include “System.Data” and “System.Data.SqlClient” namespaces to use below code.
 
First technique to get database tables and add to DataSet.
 
You can create two instances of SqlDataAdapter class by providing two different command texts. Call the Fill() method for both instances to fill both tables.  Tables can be reached in a DataSet by using table name or index of the table. 
 
C#
 
public DataTable DataSetWithTwoTables1()
{
    string cmdText1 = "SELECT * FROM Products";
    string cmdText2 = "SELECT * FROM Customers";
    string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
    SqlConnection connection = new SqlConnection(myConnection);
    connection.Open();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter da1 = new SqlDataAdapter(cmdText1, connection);
    SqlDataAdapter da2 = new SqlDataAdapter(cmdText2, connection);
    DataSet ds = new DataSet();
    da1.Fill(ds, "Products");
    da2.Fill(ds, "Customers");
    connection.Close();
    DataTable dt = new DataTable();
    dt = ds.Tables[1];
    return dt;
}
 
VB.NET
 
Public Function DataSetWithTwoTables1() As DataTable
    Dim cmdText1 As String = "SELECT * FROM Products"
    Dim cmdText2 As String = "SELECT * FROM Customers"
    Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
    Dim connection As New SqlConnection(myConnection)
    connection.Open()
    Dim command As New SqlCommand()
    Dim da1 As New SqlDataAdapter(cmdText1, connection)
    Dim da2 As New SqlDataAdapter(cmdText2, connection)
    Dim ds As New DataSet()
    da1.Fill(ds, "Products")
    da2.Fill(ds, "Customers")
    connection.Close()
    Dim dt As New DataTable()
    dt = ds.Tables(1)
    Return dt
End Function
 
Second technique to get database tables and add to DataSet.
 
You can create only one instance for SqlDataAdapter class but you need to set CommandText property for SqlCommand instance to add each table to DataSet. 
 
C#
 
public DataTable DataSetWithTwoTables2()
{
    string cmdText1 = "SELECT * FROM Products";
    string cmdText2 = "SELECT * FROM Customers";
    string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
    SqlConnection connection = new SqlConnection(myConnection);
    connection.Open();
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = cmdText1;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = command;
    DataSet ds = new DataSet();
    da.Fill(ds, "Products");
    command.CommandText = cmdText2;
    da.SelectCommand = command;
    da.Fill(ds, "Customers");
    connection.Close();
    DataTable dt = new DataTable();
    dt = ds.Tables[0];
    return dt;
}
 
VB.NET
 
Public Function DataSetWithTwoTables2() As DataTable
    Dim cmdText1 As String = "SELECT * FROM Products"
    Dim cmdText2 As String = "SELECT * FROM Customers"
    Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
    Dim connection As New SqlConnection(myConnection)
    connection.Open()
    Dim command As New SqlCommand()
    command.Connection = connection
    command.CommandText = cmdText1
    Dim da As New SqlDataAdapter()
    da.SelectCommand = command
    Dim ds As New DataSet()
    da.Fill(ds, "Products")
    command.CommandText = cmdText2
    da.SelectCommand = command
    da.Fill(ds, "Customers")
    connection.Close()
    Dim dt As New DataTable()
    dt = ds.Tables(0)
    Return dt
End Function
 
Third technique to get database tables and add to DataSet.
 
You can also write your database queries in constructor of SqlDataAdapter class separating each query with semi colon (;). 
 
C#
 
public DataTable DataSetWithThreeTables()
{
    string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
    SqlConnection connection = new SqlConnection(myConnection);
    connection.Open();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Products; SELECT * FROM Customers; SELECT * FROM Orders", connection);
    DataSet ds = new DataSet();
    da.Fill(ds, "Products");
    da.Fill(ds, "Customers");
    da.Fill(ds, "Orders");
    connection.Close();
    DataTable dt = new DataTable();
    dt = ds.Tables[2];
    return dt;
}
 
VB.NET
 
Public Function DataSetWithThreeTables() As DataTable
    Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
    Dim connection As New SqlConnection(myConnection)
    connection.Open()
    Dim command As New SqlCommand()
    Dim da As New SqlDataAdapter("SELECT * FROM Products; SELECT * FROM Customers; SELECT * FROM Orders", connection)
    Dim ds As New DataSet()
    da.Fill(ds, "Products")
    da.Fill(ds, "Customers")
    da.Fill(ds, "Orders")
    connection.Close()
    Dim dt As New DataTable()
    dt = ds.Tables(2)
    Return dt
End Function
 
Fourth technique to get database tables and add to DataSet.
 
You can use TableMapping property to add each table to DataSet.
 
C#
 
public DataTable DataSetWithThreeTables2()
{
    string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
    SqlConnection connection = new SqlConnection(myConnection);
    connection.Open();
    SqlCommand command = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Products; SELECT * FROM Customers; SELECT * FROM Orders", connection);
    DataSet ds = new DataSet();
    da.TableMappings.Add("Customers", "Customers");
    da.TableMappings.Add("Products", "Products");
    da.TableMappings.Add("Orders", "Orders");
    da.Fill(ds);
    connection.Close();
    DataTable dt = new DataTable();
    dt = ds.Tables[2];
    return dt;
}
 
VB.NET
 
Public Function DataSetWithThreeTables2() As DataTable
    Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
    Dim connection As New SqlConnection(myConnection)
    connection.Open()
    Dim command As New SqlCommand()
    Dim da As New SqlDataAdapter("SELECT * FROM Products; SELECT * FROM Customers; SELECT * FROM Orders", connection)
    Dim ds As New DataSet()
    da.TableMappings.Add("Customers", "Customers")
    da.TableMappings.Add("Products", "Products")
    da.TableMappings.Add("Orders", "Orders")
    da.Fill(ds)
    connection.Close()
    Dim dt As New DataTable()
    dt = ds.Tables(2)
    Return dt
End Function

  

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to Encrypt and Decrypt Query String in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net