I have a search engine that uses a SqlDataSource control in ASP.NET which gets data from a stored procedure with parameters. I get the parameters from default values and controls on the page, so the user can get what he wants. That data is then shown in a GridView control.
Now, I have to add an export button, which will export the results in an excel document to be downloaded by the user. I'm not sure if it is the best way, but I used the algorithm from microsoft (http://support.microsoft.com/default.aspx?scid=kb;en-us;308247) to do this. The problem arose when I tried getting the values of the search fields. You see, we'll use this as a template, since we have about a dozen search engines to make, and we'd like to have something that works dynamically.
Here's an exemple of the SqlDataSource control on the aspx page :
<asp:SqlDataSource ID="SearchDataSource" runat="server"
ConnectionString="CONNECTIONSTRING"
ProviderName="System.Data.SqlClient"
SelectCommand="sp_SearchEngine_BASE"
SelectCommandType="StoredProcedure" onselected="SearchDataSource_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="ctlID1" DbType="SomeType" DefaultValue=""
Name="spParamA" PropertyName="aProperty" />
<asp:ControlParameter ControlID="ctlID2" DbType="SomeType" DefaultValue=""
Name="spParamB" PropertyName="aProperty" />
</SelectParameters>
</asp:SqlDataSource>
And here's the exportation code :
protected void exportExcel()
{
int i;
String strLine = "", filePath, fileName, fileExcel;
FileStream objFileStream;
StreamWriter objStreamWriter;
Random nRandom = new Random(DateTime.Now.Millisecond);
//Dim fs As Object, myFile As Object
SqlConnection cnn = new SqlConnection(SearchDataSource.ConnectionString);
//Create a pseudo-random file name.
fileExcel = "t" + nRandom.Next().ToString() + ".xls";
//Set a virtual folder to save the file.
//Make sure that you change the application name to match your folder.
filePath = Server.MapPath("\\ExcelTest");
fileName = filePath + "\\" + fileExcel;
//Use FileStream to create the .xls file.
objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream);
//Use a DataReader to connect to the Pubs database.
cnn.Open();
String sql = SearchDataSource.SelectCommand;
SqlCommand cmd = new SqlCommand(sql, cnn);
//cmd.Parameters.Add(SearchDataSource.SelectParameters[0].);
for (i = 0; i <= SearchDataSource.SelectParameters.Count - 1; i++)
{
// ---------------------------------------
// ----- Here is where I am stuck... -----
// ---------------------------------------
//cmd.Parameters.AddWithValue(SearchDataSource.SelectParameters[i].Name, WhatDoIPutHere);
}
SqlDataReader dr;
dr = cmd.ExecuteReader();
//Enumerate the field names and records that are used to build the file.
for(i = 0; i <= dr.FieldCount - 1; i++) {
strLine = strLine + dr.GetName(i).ToString() + "\t";
}
//Write the field name information to file.
objStreamWriter.WriteLine(strLine);
//Reinitialize the string for data.
strLine = "";
//Enumerate the database that is used to populate the file.
while (dr.Read()) {
for(i = 0; i<= dr.FieldCount - 1; i++) {
strLine = strLine + dr.GetValue(i) + "\t";
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}
//Clean up.
dr.Close();
cnn.Close();
objStreamWriter.Close();
objFileStream.Close();
/*
//Show a link to the Excel file.
HyperLink1.Text = "Open Excel";
HyperLink1.NavigateUrl = fileExcel;
*/
}
Since it needs to be dynamic, the exportation code needs to works whatever is used in the SqlDataSource control. So if it has more parameters, a different connection string or anything like that, it still needs to work. We shouldn't have to change to code for a different engine.
So here's the question : How can I get the values of the controls used by the SqlDataSource SearchDataSource as parameters for the SqlDataReader dr in the C# user-defined method void exportExcel() linked to my aspx page without hard-coding the controls' IDs?
