DataTable with DataSet SQL Server C#

DataSet:

DataSet contains a rich collection of DataTableCollection and their DataRelationCollection . The DataTableCollection contains zero or more DataTable objects. The SqlDataAdapter object allows us to populate DataTables in a DataSet. We can use Fill method in the SqlDataAdapter Object for populating data in a Dataset.

ADO.NET enables you to create DataTable objects and add them to an existing DataSet. In a DataSet with multiple DataTable objects, you can use DataRelation objects to relate one table to another, to navigate through the tables, and to return child or parent rows from a related table. We can populate Dataset with more than one table at a time using SqlDataAdapter Object . The following C# source code shows how to a single SqlDataAdapter fill Dataset with multiple tables. DataTable has PrimaryKey feature which makes DataSet and DataTable combination more powerful.

Following code gives you a fair idea to implement DataTable with DataSet using ADO.NET:

public class PullDataTest
{
    // your data table
    private DataTable dtTable = new DataTable();

    public PullDataTest()
    {
    }

    // your method to pull data from database to datatable   
    public void PullData()
    {
        string connString = @"your connection string goes here";
        string query = "select * from table";

        SqlConnection conn = new SqlConnection(connString);        
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();

        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        da.Fill(dtTable);
        conn.Close();
        da.Dispose();
    }
}

Another Short hand method that you may like:

using System;
using System.Data;
using System.Data.SqlClient;
var table = new DataTable();    
using (var da = new SqlDataAdapter("SELECT * FROM mytable", "connection string"))
{      
    da.Fill(table);
}

Next>>Reading Filtered Cells from Excel in C#

Share on Facebook

Tweet