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
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();
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.
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.
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; }
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;
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());
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
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();
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