How to get data in dataset and pass into Datatable in C# — Coding Queue

In this Article, we will learn how we can get the data from the database in a Dataset and then pass the data into the Datatable. Suppose in your SQL Stored procedure you have 2 query and you want both query result in single transaction you can achieve it will the help of Dataset. We will see how we can do this.

We will learn below topic in this Article:

Suppose we have a below 2 table structure in Database

Create a stored procedure with multiple select sql query

So first we will create stored procedure which basically contains 2 SQL Select query.

create procedure GetData 
AS 
BEGIN 
Select Admin_ID,Admin_Pass from [AdminTbl] 
Select UserName,Pass from [UserTbl] 
END

Getting data into dataset

Now we will create a connection to Database and get the data into the dataset. If you want to learn how we can create connection and perform CRUD operation in Database by using C# you can check this article.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConS"].ConnectionString);
con.Open(); 
SqlCommand cmd = new SqlCommand("GetData", con); 
cmd.CommandType = CommandType.StoredProcedure; 
DataSet ds = new DataSet(); 
SqlDataAdapter da = new SqlDataAdapter(cmd); 
da.Fill(ds);

Now dataset object will contain 2 tables. Please check below screenshot

Now we will see how we can access the data from both the table in C# and convert the dataset into the datatable.

var dataTable = new DataTable(); 
dataTable.Columns.Add("Admin_ID", typeof(string)); dataTable.Columns.Add("Admin_Pass", typeof(string)); 
for (int i=0;i< ds.Tables[0].Rows.Count; i++) 
{ 
DataRow dr = dataTable.Rows[i]; 
dr["Admin_ID"] = ds.Tables[1].Rows[i].Field<string>("Admin_ID"); dr["Admin_Pass"] = ds.Tables[1].Rows[i].Field<string>("Admin_Pass"); } 
//Similarly we will fetch data from the second dataset table var dataTable2 = new DataTable(); 
dataTable2.Columns.Add("UserName", typeof(string)); dataTable2.Columns.Add("Pass", typeof(string)); 
for (int i=0;i< ds.Tables[1].Rows.Count; i++) 
{ 
DataRow dr = dataTable.Rows[i]; 
dr["UserName"] = ds.Tables[1].Rows[i].Field<string>("UserName"); dr["Pass"] = ds.Tables[1].Rows[i].Field<string>("Pass"); 
}

In this way we can convert the dataset into the datatable.

Conclusion

In this Article we have seen how we can use Dataset in C# and how we can fetch the data from the multiple Select statement in the single Database transaction. And we have also seen how we have convert dataset into the Datatable.

Thank you for reading this article. If you have any query regarding this please let me know in the comment section or you can contact me at contact@codingqueue.com

Originally published at https://codingqueue.com on March 20, 2021.