I want to store the records of my sql query which i retieve using an Slq data reader in some object so that i can loop through them later on to export them to excel.....Can any kind soul give me samples of how to do..them ...Thank you in advance...
Instead of popluating a datareader popluate a dataset. Once the data is in a dataset you can loop through it.
You can fill your dataset like this:
Dim cnn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("YourString").ConnectionString)
cnn.Open()
Dim sqlAdp As New Data.SqlClient.SqlDataAdapter
Dim cmd As New Data.SqlClient.SqlCommand
Dim ds As New Data.DataSet
cmd.Connection = cnn
cmd.CommandText = "Select * From SomeTable"
cmd.Parameters.Clear()
sqlAdp.SelectCommand = cmd
sqlAdp.Fill(ds)
cnn.Close()
Now you can loop through your dataset like this:
For Each r In ds.Tables(0).Rows
For Each c In ds.Tables(0).Columns
Response.Write(ds.Tables(0).Rows(r)(c).ToString & " ")
Next
Response.Write("<br />")
Next
My example above will simply write out all your records row by row.
You can manipluate the data any you need.
To call information from the dataset you need to reference the table and the the row and column you want..so:
ds.Tables(TableIndex).Rows(rowIndex)(columnIndex).ToString will return a string with the value for the index specified.
If you want the value stored in row 2 column 5 of Table 0 you would call:
ds.Tables(0).Rows(2)(5).ToString
Your dataset like a database can house multiple tables. So you could in theory return multiple record sets from a single stored procedure into the dataset then based on the order they were returned reference the index of that table then the row and column
you want.
By looping with a for each row and then for each column we will get all of the data from top to bottom.
Let me know if you need more info. SqlDataReader is not going to be as friendly for this. You could build up an array with all the values as you progress through your reader then call that array again later but it's much more code and not as easy to work
with.
Thank You Decker Dong for your response.. but i already have my excel export working in standard fromat which i cannot change.....all i need is to loop through the Data table and print each tab and then close the work book..which i think is similar to Loganix
code above...
but i already have my excel export working in standard fromat which i cannot change
Then I think you can do this trick：
1）In the Page_Load event handler，please dynamically generate a GridView：
if(!IsPostBack)｛GridView gv = new GridView(); gv.DataSource=yourDataTable;gv.DataBind();｝
2）And then use the codes I've told you above to export to a whole excel file：
Response.Clear();
Response.AddHeader("content-disposition", "attachment;
filename=FileName.xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
Export GridView to Excel in ASP.NET
June 11th, 2011 admin Web Forms 0 Comments
Push data from xml file to GridView:
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Products.xml"));
GridView1.DataSource = ds;
GridView1.DataBind();
Export data from GridView to Excel:
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
GridView1.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
Complete source code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Products.xml"));
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
GridView1.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
Example
GridView Data Sheet
ExportGridviewToExcel1 thumb Export GridView to Excel in ASP.NET
Exported Data
ExportGridviewToExcel2 thumb Export GridView to Excel in ASP.NET
Download Source Code
Tags: export gridview to excel in asp net, export gridview to excel, export gridview to excel asp net, how to export gridview to excel in asp net, asp net export gridview to excel, gridview to excel, export to excel in asp net, how to export data from gridview to excel in asp net using c#, code to export gridview to excel in asp net, GridView export to excel
Thank you!
Tanveer Ahmad
Please do not forget to mark as answer if my post help you!
akpaga22
Member
184 Points
253 Posts
Looping thorough a query and storing the row values in a Dictonary or someother object
May 04, 2012 09:15 PM|LINK
Hi friends,
I want to store the records of my sql query which i retieve using an Slq data reader in some object so that i can loop through them later on to export them to excel.....Can any kind soul give me samples of how to do..them ...Thank you in advance...
Loganix77
Participant
1351 Points
412 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 04, 2012 09:23 PM|LINK
Instead of popluating a datareader popluate a dataset. Once the data is in a dataset you can loop through it.
You can fill your dataset like this:
Dim cnn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("YourString").ConnectionString) cnn.Open() Dim sqlAdp As New Data.SqlClient.SqlDataAdapter Dim cmd As New Data.SqlClient.SqlCommand Dim ds As New Data.DataSet cmd.Connection = cnn cmd.CommandText = "Select * From SomeTable" cmd.Parameters.Clear() sqlAdp.SelectCommand = cmd sqlAdp.Fill(ds) cnn.Close()Now you can loop through your dataset like this:
For Each r In ds.Tables(0).Rows For Each c In ds.Tables(0).Columns Response.Write(ds.Tables(0).Rows(r)(c).ToString & " ") Next Response.Write("<br />") NextLoganix77
Participant
1351 Points
412 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 04, 2012 09:30 PM|LINK
My example above will simply write out all your records row by row.
You can manipluate the data any you need.
To call information from the dataset you need to reference the table and the the row and column you want..so:
ds.Tables(TableIndex).Rows(rowIndex)(columnIndex).ToString will return a string with the value for the index specified.
If you want the value stored in row 2 column 5 of Table 0 you would call:
ds.Tables(0).Rows(2)(5).ToString
Your dataset like a database can house multiple tables. So you could in theory return multiple record sets from a single stored procedure into the dataset then based on the order they were returned reference the index of that table then the row and column you want.
By looping with a for each row and then for each column we will get all of the data from top to bottom.
Let me know if you need more info. SqlDataReader is not going to be as friendly for this. You could build up an array with all the values as you progress through your reader then call that array again later but it's much more code and not as easy to work with.
Cheers!
akpaga22
Member
184 Points
253 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 04, 2012 09:44 PM|LINK
Thanks Loganix for your response.. I am using Datatble in the follwing way
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataTable dt = new DataTable("MyResults");
da.Fill(dt);
Is the above code correct
How can I use this datable to read the values because i print them on the excel...
So now i have to loop through the Datable to read as well as export to excel,is it not...How would i do that...Something like below...
foreach (DataRow dr in dt.Rows)
{
TotalPopulation = Convert.ToInt32(dt.Rows[0]["Population"]);
}
Thank you
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 06, 2012 02:21 AM|LINK
Hello akapaga22：）
Since you've read out all the records from Oracle into a DataTable，in fact I think in the button click you can：
1）First bind the DataTable to the GridView's DataSource。
2）And then use Response.Write to export it。
For more you can refer this excel：http://geekswithblogs.net/AzamSharp/archive/2005/12/21/63843.aspx
akpaga22
Member
184 Points
253 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 06, 2012 03:35 PM|LINK
Thank You Decker Dong for your response.. but i already have my excel export working in standard fromat which i cannot change.....all i need is to loop through the Data table and print each tab and then close the work book..which i think is similar to Loganix code above...
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 07, 2012 01:19 AM|LINK
Then I think you can do this trick：
1）In the Page_Load event handler，please dynamically generate a GridView：
2）And then use the codes I've told you above to export to a whole excel file：
Response.Clear(); Response.AddHeader("content-disposition", "attachment; filename=FileName.xls"); Response.Charset = ""; // If you want the option to open the Excel file without saving than // comment out the line below // Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/vnd.xls"; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); GridView1.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End();public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
specified ASP.NET server control at run time.
}
tanveer_uos
Member
448 Points
234 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 07, 2012 12:55 PM|LINK
Hi
Export GridView to Excel in ASP.NET June 11th, 2011 admin Web Forms 0 Comments Push data from xml file to GridView: DataSet ds = new DataSet(); ds.ReadXml(Server.MapPath("Products.xml")); GridView1.DataSource = ds; GridView1.DataBind(); Export data from GridView to Excel: Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; StringWriter StringWriter = new System.IO.StringWriter(); HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter); GridView1.RenderControl(HtmlTextWriter); Response.Write(StringWriter.ToString()); Response.End(); Complete source code using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Data; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataSet ds = new DataSet(); ds.ReadXml(Server.MapPath("Products.xml")); GridView1.DataSource = ds; GridView1.DataBind(); } } protected void Button1_Click(object sender, EventArgs e) { Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; StringWriter StringWriter = new System.IO.StringWriter(); HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter); GridView1.RenderControl(HtmlTextWriter); Response.Write(StringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } } Example GridView Data Sheet ExportGridviewToExcel1 thumb Export GridView to Excel in ASP.NET Exported Data ExportGridviewToExcel2 thumb Export GridView to Excel in ASP.NET Download Source Code Tags: export gridview to excel in asp net, export gridview to excel, export gridview to excel asp net, how to export gridview to excel in asp net, asp net export gridview to excel, gridview to excel, export to excel in asp net, how to export data from gridview to excel in asp net using c#, code to export gridview to excel in asp net, GridView export to excelTanveer Ahmad
Please do not forget to mark as answer if my post help you!
tanveer_uos
Member
448 Points
234 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 07, 2012 12:56 PM|LINK
Tanveer Ahmad
Please do not forget to mark as answer if my post help you!
sandy060583
Star
8728 Points
1626 Posts
Re: Looping thorough a query and storing the row values in a Dictonary or someother object
May 07, 2012 01:36 PM|LINK
Hi , To loop thru the Datatable, here is the sample code which can help:
DataTable table = GetTable(); // Get the data table. foreach (DataRow row in table.Rows) // Loop over the rows. { Console.WriteLine("--- Row ---"); // Print separator. foreach (var item in row.ItemArray) // Loop over the items. { Console.Write("Item: "); // Print label. Console.WriteLine(item); // Invokes ToString abstract method. } }Hope this will help !!!
Ramani Sandeep (My Blog)
(MCTS, MCC-2011)