SqlDataSource reuse on different pages

Question

I am using ASP.NET. I have a ReportPage1 and ReportOutputPage1. These are different aspx files and has different MasterPages. However, I need the same SqlDataSource object to use on both pages. On ReportPage I need SqlDataSource to call StoredProcedure and import data to CSV file, but on ReportOutputPage I need to use SqlDataSource to call the same StoredProcedure and populate GridView.

ReportPage1 is "main" page - a button click from this page opens ReportOutputPage1 and displays it in new window. ReportPage is PreviousPage for ReportOutputPage1.

Above is example for Report1. The same idea is for Report2 (with SqlDataSource2) and for Report3 (SqlDataSource3) etc. - 10 different reports.

How to reuse SqlDataSource for every two pages (ReportPage & ReportOutputPage)?

1. First suggestion I found in web - using masterpage for both ReportPage and ReportOutputPage. This doesn't work, as I have already have different masterpages for ReportPage and ReportOutputPage, as well as then I need to create 10 different MasterPages for each Report.

2. Second suggestion was to define SqlDataSource on ReportPage and then reuse it using PrevousePage on ReportOutputPage, but this doesn't work for my special case (I am using Ajax staff and Partial page postbacks and I am loosing PreviousPage, also SqlDataSource could not be serialized to save it in ViewState or similar).

3. Create UserControl. Probably this could work, but it is time consuming to create UserControl every time for new Report (10 reports - 10 usercontrols?).

4. Simply Copy & Paste SqlDataSource (I did it for one Report) could work, but I would like something more like code reuse. Someone could simply forget to modify SqlDataSource in two different places if necessary.

Can you, please, give me some suggestions how to reuse code (particularly, SqlDataSource) for Report & ReportOutput pages?

Answer 1

Could you define the need for using the same SqlDataSource? If they are two different pages and it sounds like two different uses why not use two different SqlDataSource? The pages are separate anyhow, your not going to be able to share an object on one with the other.

I would suggest you look at adding a data layer to your application for database interaction and binding your data to the datagrid at request time. That way you build your database interaction once and reuse that over different pages.

The other option is you simply use two SqlDataSources and copy/paste them to both the pages. If your trying to make a selection or some sort of criteria apply to your second page then consider using query strings and QueryStringParameters in your SqlDataSource.

Hope this helps.

Edit: Pop this in App_Code somewhere, pass in your specific usage requirements.

Public Shared Function GetData(connString As String, command As String, Optional params As SqlParameter() = Nothing) As DataTable
    Dim conn As New SqlConnection(connString)
    Dim da As New SqlDataAdapter(command, conn)
    Dim dt As New DataTable
    da.SelectCommand.CommandType = CommandType.StoredProcedure
    da.SelectCommand.CommandTimeout = 6000 '6 seconds.
    If Not IsNothing(params) Then
        For Each p As SqlParameter In params
            da.SelectCommand.Parameters.Add(p)
        Next
    End If
    Try
        conn.Open()
        da.Fill(dt)
        Return dt
    Catch ex As Exception
        Throw ex
    Finally
        conn.Close()
    End Try
End Function

Then bind the datatable to your gridview, not sure how your outputing to file.

Private Sub BindData(gridview As GridView, data As DataTable)
    gridview.DataSource = data
End Sub

You can now re-use the database interaction from the code behind:

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
   BindData(MyGridView,GetData(ConnectionStrings(connName).ConnectionString, _
                 "dbo.SomeSprocOrOther", _
                 New SqlParameter(){New SqlParameter("paramName","paramValue")})
End Sub