Free Web Development Online Tutorials, Learn to Code
Ado.net fill dataset example, ado.net export dataset to excel, Dataset, Datatable, DataAdapter

Ado.net Dataset Tutorial with C# Example

This Ado.net Dataset Tutorial with C# for Beginners will guide you to learn Ado.net Dataset with some real-time examples. Please feel free to ask question, I will keep updating this tutorial with answer of your query

What is DataSet in ADO.Net

DataSet is a collection of DataTable in ADo.Net, works on disconnected architecture, which means that without having any connection with data source, in DataSet you can manipulate data and save data before finally you save data in database. DataSet use DataAdapter as bridge to fill data from data source.

Here is the code for creating ado.net empty dataset class

@using System.Data;
DataSet ds = new DataSet();

DataSet Methods

Before we show you few real-time Example of DataSet Implementation, we want you to read the few often used methods of DataSet.

  • Copy()

    Copy both the structure and data for this DataSet.

  • Clone()

    Copy the structure of the DataSet, but not the data

  • CreateDataReader()(DataTable[])

    It returns a DataTableReader with one result set per DataTable.

  • GetXml()

    It returns the XML representation of the data stored in the DataSet.

  • GetXmlSchema()

    Returns the XML Schema of the data stored in the DataSet.

  • Load(IDataReader, LoadOption, DataTable[])

    This method is used to fill a DataSet with values from a data source using the supplied IDataReader

  • Merge(DataSet)(DataTable)

    Merge any specified DataSet / DataTable and its schema into the current DataSet

  • ReadXml(XmlReader, XmlReadMode)

    Read XML schema and data into the DataSet using the specified XmlReader and XmlReadMode.

  • WriteXml(XmlWriter, XmlWriteMode)

    Write the current data and optionally the schema for the DataSet using XmlWriter and XmlWriteMode.

Ado.net Fill DataSet Example

Fill DataSet: Here we write a simple method called getDataSet() that will return a DataSet, pull data from tbSutdent table in Database.

using System.Data;
using System.Data.SqlClient;
            
DataSet getDataSet()
{
    DataSet ds = null;
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        SqlDataAdapter sde = new SqlDataAdapter("Select studentName, phone, adderss from tbStudent", con);
        ds = new DataSet();
        sde.Fill(ds);
    }
    return ds;
}

Note: While fetching data into DataSet, avoid writing “select * from tableName” , this will pull all the columns of your database table and may make the DataSet unnecessary heavy , instead specify the column names you need (like "Select studentName, phone, adderss from tbStudent"), you will have better readability while writing code.

Ado.net get dataset from stored procedure

We can get ado.net dataset from stored procedure with the help of SqlCommand object, we just need to set CommandType="StoredProcedure", see the example blow.

public DataSet getDataSet2()
{
    DataSet ds = null;
    using (SqlConnection con = new SqlConnection(Util.ConnectionStringSQL))
    {
        SqlCommand _sCommand = con.CreateCommand();
        _sCommand.CommandText = "usp_SpName";
        _sCommand.CommandType = CommandType.StoredProcedure;
        _sCommand.Connection = con;
             
        SqlDataAdapter sde = new SqlDataAdapter(_sCommand);             
        ds = new DataSet();
        sde.Fill(ds);
    }
    return ds;
}

Get multiple recordset from Ado.net dataset

We also can fill multiple tables using dataset , and to do that we need to get dataset from stored procedure, so we need to take the help of SqlCommand, and pass the SqlCommand object as a parameter when creating new instance of SqlDataAdapter class.

Look at the example below.

public DataSet getDataSet2()
{
    DataSet ds = null;
    using (SqlConnection con = new SqlConnection(Util.ConnectionStringSQL))
    {
        SqlCommand _sCommand = con.CreateCommand();
        _sCommand.CommandText = "usp_SpName";
        _sCommand.CommandType = CommandType.StoredProcedure;
        _sCommand.Connection = con;
             
        SqlDataAdapter sde = new SqlDataAdapter(_sCommand);             
        ds = new DataSet();
        sde.Fill(ds);
    }
    return ds;
}

Now if you wonder how to write SQL query inside the store procedure, look at the example below

CREATE PROCEDURE usp_SpName
AS
    SELECT * FROM tbStudent;
    SELECT * FROM tbTeacher;
GO;

ado.net read dataset

Now you need to read from dataset , You can simply retrieve tables from dataset object by index

DataTable dt1 = ds.Tables[0];
DataTable dt2 = ds.Tables[2];

Ado.Net DataSet Manipulation
You can manipulate DataSet even after fetching data from Database. Let's say you want to add another table into same DataSet, here is the example

DataSet _ds = getDataSet(); // the above method
_ds.Tables.Add(new DataTable());

Ado.net DataSet AcceptChanges, RejectChanges

After making some changes into DataSet you can either save the new data, AcceptChanges or you RejectChanges in dataset all new changes

DataSet _ds = getDataSet();
_ds.Tables.Add(new DataTable());

_ds.AcceptChanges(); // accept changes       
 
_ds.RejectChanges(); // reject new changes and remain same as earlier

Ado.net DataSet Copy and Clone Example

Copy DataSet into another DataSet object, See the difference between copy and clone method

// copy the dataset with data
DataSet _ds1 = getDataSet().Copy();
           
// copy the dataset structure  without data
DataSet _ds2 = getDataSet().Clone();

Ado.net export dataset to excel

You can easily create an Excel sheet from your DataSet object with just few lines of code, you have to specify the folder path where you want the excel to be saved .

using (StringWriter sw = new StringWriter("PathTosave"))
{
  using (HtmlTextWriter htw = new HtmlTextWriter(sw))
  {
    // get your dataset object with data
    DataSet _ds1 = getDataSet()   
    
    DataGrid dg =  new DataGrid();
    dg.DataSource = _ds1.Tables[0];
    dg.DataBind();

    dg.RenderControl(htw);
  }
}

You may also learn ADO.Net DataReader

Comment
Name
Email
Website
Subscribe
 
learn ado.net
Dataset in Ado.Net
Ado.net Interview Questions Answers

Ado.Net C# Examples | Join Asp.Net MVC Course