Using Stored Procedures and SmartMarkers in .NET

Aspose.Cells Product Family
1

Hello,

I’m trying to use a stored procedure for my data source, instead of an inline SQL query. However, Excel.Express doesn’t return any data to the spreadsheet. I think the problem is the name of the datasource in the SmartMarker. According to the help file, you should use DataSource.Field name as the syntax.

For example, the name of my stored procedure is “sprocFlightData”, so my SmartMarkers are named like this: &=sprocFlightData.FlightNumber. However, this only returns an empty spreadsheet with no data. I’ve also tried using the name of the SqlCommand object like this: &=sqlFlightData.FlightNumber. This also doesnt work.

If I try my query using the table name directly (as in =tblFlightData.FlightNumber), it works fine. Unfortunately, I have to use a stored procedure in order to make use of several parameters.

I guess my question is this: what should the name of the DataSource be when you’re using a stored procedure to populate a data set?

Any suggestions as to what I can try?

Thanks for your help!
Victor

2

Dear Victor,

When you use a stored procedure to populate a DataSet, you can use DataTable.TableName property to get or set the name of a DataTable. Then you can make the data source name same as your DataTable name.

3

Hello,

Thanks Laurence! Here’s the code that solved the problem.

This is my data access class:

public DataTable GetFlightData()

{

string connection = …

SqlConnection sqlConnect = new SqlConnection(connection);

// create command object and assign it the stored procedure
SqlCommand sqlGetFlightData = new SqlCommand(“sprocFlightData”, sqlConnect);

sqlGetFlightData.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(sqlGetFlightData);

DataTable dt = new DataTable(“flightdata”);

sqlConnect.Open();

da.Fill( dt );

return dt;

}

And this is the code that calls the data and invokes Excel.Express:

{

DataTable dt = new DataTable(“flightdata”);

dt = GetData.GetFlightData();

ExcelDesigner designer = new ExcelDesigner();

string path = MapPath(".");

path = path.Substring(0, path.LastIndexOf("\"));

string designerFile = path + “\Designer\SmartMarkerDesigner2b.xls”;

designer.Open(designerFile);

designer.SetDataSource(dt);

designer.Process();

designer.Save(“CSRTest.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

}

Hope this helps anyone who has encountered a similiar problem.