How to convert DataSet to DataTable
Solution 1
A DataSet
already contains DataTables
. You can just use:
DataTable firstTable = dataSet.Tables[0];
or by name:
DataTable customerTable = dataSet.Tables["Customer"];
Note that you should have using
statements for your SQL code, to ensure the connection is disposed properly:
using (SqlConnection conn = ...)
{
// Code here...
}
Solution 2
DataSet is collection of DataTables.... you can get the datatable from DataSet as below.
//here ds is dataset
DatTable dt = ds.Table[0]; /// table of dataset
AlwaysANovice
Updated on July 23, 2022Comments
-
AlwaysANovice almost 2 years
I am retrieving data from a SQL table so I can display the result on the page as a HTML table. Later I need to be able to save that table as a CSV file.
So far I have figured out how to retrieve the data and fill them in a dataset for display purpose (which is working perfectly)...
string selectQuery = "SELECT Name, ProductNumber, ListPrice FROM Poduction.Product"; // Establish the connection to the SQL database SqlConnection conn = ConnectionManager.GetConnection(); conn.Open(); // Connect to the SQL database using the above query to get all the data from table. SqlDataAdapter myCommand = new SqlDataAdapter(selectQuery, conn); // Create and fill a DataSet. DataSet ds = new DataSet(); myCommand.Fill(ds);
and how to save them in a CSV file with the help of following code from: http://www.evontech.com/login/topic/1983.html
private void exportDataTableToCsv(DataTable formattedDataTable, string filename) { DataTable toExcel = formattedDataTable.Copy(); HttpContext context = HttpContext.Current; context.Response.Clear(); foreach (DataColumn column in toExcel.Columns) { context.Response.Write(column.ColumnName + ","); } context.Response.Write(Environment.NewLine); foreach (DataRow row in toExcel.Rows) { for (int i = 0; i < toExcel.Columns.Count; i++) { context.Response.Write(row.ToString().Replace(",", string.Empty) + ","); } context.Response.Write(Environment.NewLine); } context.Response.ContentType = "text/csv"; context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv"); context.Response.End(); }
Now my problem is how do I convert this
DataSet
toDataTable
? I have tried the way described here with NO luck: http://www.ezineasp.net/post/ASP-Net-C-sharp-Convert-DataSet-to-DataTable.aspxCan anyone help me?
-
AlwaysANovice over 12 yearsThanks for the quick responses! I have already tried this: mySqlAdapter.Fill(myDataSet); DataTable myDataTable = myDataSet.Tables[0]; but the CSV file does not look correct as the values are missing and replaced with "System.Data.DataRow".... Name,ProductNumber,ListPrice, System.Data.DataRow,System.Data.DataRow,System.Data.DataRow, System.Data.DataRow,System.Data.DataRow,System.Data.DataRow, System.Data.DataRow,System.Data.DataRow,System.Data.DataRow, System.Data.DataRow,System.Data.DataRow,System.Data.DataRow, how to fix this?
-
Jon Skeet over 12 years@Walahh: Well your CSV code is calling
ToString()
onDataRow
, which looks like a bad idea to me. I suggest you ask about that as a separate question, as it really isn't the same as the question you asked. -
Steam over 10 years@JonSkeet - I am getting a DataSet from SSIS. I execute a SQL server query with ADO.NET connection. The result set is stored in a DataSet. Is there a way I could name my result set such that DataTable for it also gets the same name ? Otherwise, I can use the index.
-
Steam over 10 yearsI was wondering why could one need a collection of DataTables, ie DataSet to store the result sets of a query. A result set is always one table. So, what are the advantages of storing it in a DataSet instead ?
-
Jon Skeet over 10 years@blasto: You can issue a multi-result query, although I think it's relatively rare. To be honest I've never liked the "old-school" ADO.NET...
-
Steam over 10 years@JonSkeet - Oh yes. I now realized what you mean - the multiple select statements in Management Studio that show multiple result sets in the output/execution window. Makes sense now. I wonder why someone would want to do that.
-
Steam over 10 years@JonSkeet - Please tell me why you "never liked the "old-school" ADO.NET". I don't think I can use any other connection now. I dont want to use ADO or OLEDB.
-
Jon Skeet over 10 years@blasto: I've always preferred getting things into a strongly-typed form as quickly as possible. Strongly-typed datasets are better than nothing, but I still prefer a proper ORM. (I realize that comes with its own problems, of course...)
-
JeffO almost 10 years@Steam - Inside the dataset, you can have join logic similar to a database without having to send the data to the database after each incremental change (batch). This could be used to handle referential integrity like in a parent-child relationship.