I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection
string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind
to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public
objClientConnection As
New ConnectionStringSettings
Public
Sub Page_Init(ByVal sender
As Object,
ByVal e As System.EventArgs)
If Session("ClientDBConnectionString") <>
"" Then
objClientConnection.ConnectionString = Session("ClientDBConnectionString")
objClientConnection.Name = "ClientDBConnection"
objClientConnection.ProviderName = "System.Data.SqlClient"
Else
objClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")
End If
End Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure
there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
If your connection string is created dynamically at run time. We cannot set the connection string at design time. Binding to the property might not work since this depends on when you call DataBind on your controls.
In this case, it's better to set this in code behind event handler for Page_Load like
Sincerely,
Kevin Yu
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Thanks for your response. I think what you are saying is that the only way to set the connection strings dynamically at run time is to use code behind? The problem with this is that the site I am working on has a lot of interrelated, databound controls many
of which are coded declaratively. I would have to recode every one of these (and there are hundreds!) in code behind and many are inside other controls (wizards, gridview, etc) and so are complicated to get at in code behind.
Given that every one of these databound controls uses:
Could you let me see the code that you're calling DataBind on the page? Because whether this is possible depends on when data is being pulled from the server.
Thanks!
Sincerely,
Kevin Yu
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Sorry but I don't have permissions to upload files so will have to cut and paste the code, see below. This is a typical example of one of the pages on this site. Some are more complex with cascading DropDownLists within Panels within Wizard controls, etc.
Cliff
Source code:
<%@ Page Language="VB" MasterPageFile="~/Client.master" Trace="False" Theme="DataLabClient1"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs)
Master.Page.Title = "Datalab - Manage Reports"
End Sub
Protected Sub cbReportType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbReportType.Checked Then
pnlReportType.Visible = True
Else
pnlReportType.Visible = False
End If
End Sub
Protected Sub rblReportSource_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
'If Not Page.IsPostBack Then
ddlReportSource.Items.Clear()
ddlReportSource.AppendDataBoundItems = True
ddlReportSource.Items.Add(New ListItem("Any", "Any"))
'End If
If rblReportSource.SelectedValue = "standard" Then
ddlReportSource.Visible = True
Else
ddlReportSource.Visible = False
End If
GridView1.DataBind()
End Sub
Protected Sub cbFirstFileDate_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbFirstFileDate.Checked Then
ddlFirstFileDate.Visible = True
Else
ddlFirstFileDate.Visible = False
End If
End Sub
Protected Sub cbProductOffer_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbProductOffer.Checked Then
pnlProductOffer.Visible = True
Else
pnlProductOffer.Visible = False
End If
End Sub
Protected Sub cbOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbOfferType.Checked Then
ddlOfferType.Visible = True
Else
ddlOfferType.Visible = False
End If
End Sub
Protected Sub cbRouteType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbRouteType.Checked Then
ddlRouteType.Visible = True
Else
ddlRouteType.Visible = False
End If
End Sub
Protected Sub cbProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbProductType.Checked Then
ddlProductType.Visible = True
Else
ddlProductType.Visible = False
End If
End Sub
Protected Sub cbEligibleFor_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbEligibleFor.Checked Then
pnlEligibleFor.Visible = True
Else
pnlEligibleFor.Visible = False
End If
End Sub
Protected Sub cbHolds_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbHolds.Checked Then
pnlHolds.Visible = True
Else
pnlHolds.Visible = False
End If
End Sub
Protected Sub cbEFProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbEFProductType.Checked Then
ddlEFProductType.Visible = True
Else
ddlEFProductType.Visible = False
End If
End Sub
Protected Sub cbEFProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbEFProductName.Checked Then
ddlEFProductName.Visible = True
Else
ddlEFProductName.Visible = False
End If
End Sub
Protected Sub cbEFOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbEFOfferType.Checked Then
ddlEFOfferType.Visible = True
Else
ddlEFOfferType.Visible = False
End If
End Sub
Protected Sub cbEFContactType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbEFContactType.Checked Then
ddlEFContactType.Visible = True
Else
ddlEFContactType.Visible = False
End If
End Sub
Protected Sub cbHProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbHProductType.Checked Then
ddlHProductType.Visible = True
Else
ddlHProductType.Visible = False
End If
End Sub
Protected Sub cbHProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbHProductName.Checked Then
ddlHProductName.Visible = True
Else
ddlHProductName.Visible = False
End If
End Sub
Protected Sub cbPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbPopulation.Checked Then
pnlPopulation.Visible = True
Else
pnlPopulation.Visible = False
End If
End Sub
Protected Sub cbCustomerType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbCustomerType.Checked Then
ddlCustomerType.Visible = True
Else
ddlCustomerType.Visible = False
End If
End Sub
Protected Sub cbSubPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbSubPopulation.Checked Then
pnlSubPopulation.Visible = True
Else
pnlSubPopulation.Visible = False
End If
End Sub
Protected Sub cbSelectionGroup_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbSelectionGroup.Checked Then
pnlSelectionGroup.Visible = True
Else
pnlSelectionGroup.Visible = False
End If
End Sub
Protected Sub cbTestPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbTestPack.Checked Then
pnlTestPack.Visible = True
Else
pnlTestPack.Visible = False
End If
End Sub
Protected Sub cbCreatedBy_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbCreatedBy.Checked Then
pnlCreatedBy.Visible = True
Else
pnlCreatedBy.Visible = False
End If
End Sub
Protected Sub cbTest_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbTest.Checked Then
pnlTest.Visible = True
Else
pnlTest.Visible = False
End If
End Sub
Protected Sub cbPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If cbPack.Checked Then
ddlPack.Visible = True
Else
ddlPack.Visible = False
End If
End Sub
Protected Sub ddlFileType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
ddlReportSource.Items.Clear()
ddlReportSource.AppendDataBoundItems = True
ddlReportSource.Items.Add(New ListItem("Any", "Any"))
GridView1.DataBind()
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Not Page.IsPostBack Then
'ddlReportSource.Items.Clear()
'ddlReportSource.AppendDataBoundItems = True
'ddlReportSource.Items.Add(New ListItem("Any", "Any"))
End If
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If (e.Row.RowType = DataControlRowType.DataRow) Then
Dim row As DataRowView = CType(e.Row.DataItem, DataRowView)
If (Not row Is Nothing) Then
'Dim reportType As String = LCase(Trim(CType(row("report_category"), String)))
'
Select Case LCase(Trim(rblReportSource.SelectedValue))
Case "adhoc"
' Display all command buttons
Dim convertButton As ButtonField = GridView1.Columns(3)
convertButton.Visible = True
Dim renameButton As CommandField = GridView1.Columns(4)
renameButton.Visible = True
Case "standard"
' Just display the View and Delete buttons
Dim convertButton As ButtonField = GridView1.Columns(3)
convertButton.Visible = False
Dim renameButton As CommandField = GridView1.Columns(4)
renameButton.Visible = False
Case "both"
' Display all command buttons
Dim convertButton As ButtonField = GridView1.Columns(3)
convertButton.Visible = False
Dim renameButton As CommandField = GridView1.Columns(4)
renameButton.Visible = False
Case Else
End Select
End If
End If
End Sub
Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
' Dim row As GridViewRow = GridView1.SelectedRow
'Response.Write("process gridview row = " & row.Cells(7).Text)
'Dim DropDownList1 As New DropDownList
'Dim ddl As DropDownList = row.FindControl("DropDownList1")
'Response.Write("<br />process gridview row = " & ddl.SelectedItem.ToString)
'Response.End()
End Sub
'Protected Sub dsReportList1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsReportList.Deleting
' For x As Integer = 0 To e.Command.Parameters.Count - 1
'Trace.Write(e.Command.Parameters(x).ParameterName)
'Trace.Write(e.Command.Parameters(x).Value)
'Next
'End Sub
Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs)
If (Not IsDBNull(e.Exception)) Then
Me.lblErrorMessage.Text = e.Exception.Message
e.ExceptionHandled = True
End If
End Sub
Protected Sub dsReportList_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
If (e.Exception IsNot Nothing) Then
Me.lblErrorMessage.Text = e.Exception.Message
e.ExceptionHandled = True
Else
Me.lblErrorMessage.Text = "Report successfully deleted."
End If
End Sub
Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
' The GridViewCommandEventArgs class does not contain a
' property that indicates which row's command button was
' clicked. To identify which row's button was clicked, use
' the button's CommandArgument property by setting it to the
' row's index.
If e.Row.RowType = DataControlRowType.DataRow Then
' Retrieve the Button control from the first column.
Dim delButton As Button = CType(e.Row.Cells(2).Controls(0), Button)
' Set the Button's CommandArgument property with the
' row's index.
delButton.CommandArgument = e.Row.RowIndex.ToString()
delButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}")
' Now repeat for the View button
Dim viewButton As Button = CType(e.Row.Cells(1).Controls(0), Button)
' Set the Button's CommandArgument property with the
' row's index.
viewButton.CommandArgument = e.Row.RowIndex.ToString()
'viewButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}")
' Repeat for the Convert adhoc to standard report button
Dim convertButton As Button = CType(e.Row.Cells(3).Controls(0), Button)
' Set the Button's CommandArgument property with the
' row's index.
convertButton.CommandArgument = e.Row.RowIndex.ToString()
End If
End Sub
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)
If e.CommandName = "Delete_Report" Then
Me.lblErrorMessage.Text = ""
Me.lblErrorMessage.Visible = False
' Convert the row index stored in the CommandArgument
' property to an Integer.
Dim index As Integer = Convert.ToInt32(e.CommandArgument)
' Retrieve the row that contains the button clicked
' by the user from the Rows collection.
Dim row As GridViewRow = GridView1.Rows(index)
Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)
Dim reportName As String = lblReportName.Text
Dim reportCategory As String = row.Cells(4).Text
' Delete the specified report
Dim clientDBConnection As New SqlConnection()
clientDBConnection.ConnectionString = Session("ClientDBConnectionString")
Try
Using reportDeleteCommand As SqlCommand = clientDBConnection.CreateCommand()
reportDeleteCommand.CommandText = "usp_DEL_Report"
reportDeleteCommand.CommandType = Data.CommandType.StoredProcedure
reportDeleteCommand.Parameters.AddWithValue("@report_name", reportName)
reportDeleteCommand.Parameters.AddWithValue("@report_category", reportCategory)
reportDeleteCommand.Connection.Open()
reportDeleteCommand.ExecuteNonQuery()
End Using
Me.lblErrorMessage.Text = "Report successfully deleted."
Me.lblErrorMessage.Visible = True
Catch ex As Exception
If (ex IsNot Nothing) Then
Me.lblErrorMessage.Text = ex.Message
Me.lblErrorMessage.Visible = True
ExceptionHandled = True
Else
Me.lblErrorMessage.Text = "Report successfully deleted."
Me.lblErrorMessage.Visible = True
End If
End Try
clientDBConnection.Dispose()
ElseIf e.CommandName = "View_Report" Then
' Convert the row index stored in the CommandArgument
' property to an Integer.
Dim index As Integer = Convert.ToInt32(e.CommandArgument)
' Retrieve the row that contains the button clicked
' by the user from the Rows collection.
Dim row As GridViewRow = GridView1.Rows(index)
Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)
Dim reportName As String = lblReportName.Text
Dim lblReportCategory As Label = CType(row.FindControl("reportCategory"), Label)
Dim reportCategory As String = LCase(lblReportCategory.Text)
Response.Redirect("~/Main/Table_Manager/Reports_View.aspx?rept=" & reportName & "&cat=" & reportCategory)
ElseIf e.CommandName = "Convert_Report" Then
' Convert the row index stored in the CommandArgument
' property to an Integer.
Dim index As Integer = Convert.ToInt32(e.CommandArgument)
' Retrieve the row that contains the button clicked
' by the user from the Rows collection.
Dim row As GridViewRow = GridView1.Rows(index)
' Get the report name ...
Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)
Dim reportName As String = lblReportName.Text
' ... and get the report title
Dim reportTitle As String = row.Cells(10).Text
'Response.Write("convert report!! " & reportName)
'Response.Write("<br />Reeport title = " & reportTitle)
'Response.Write("<br />DB Login = " & Session("client_DatabaseLogon"))
'Response.Write("<br /> User = " & Session("User_LongName"))
'Response.End()
' Now, convert the report from adhoc to standard
Dim clientDBConnection As New SqlConnection()
clientDBConnection.ConnectionString = Session("ClientDBConnectionString")
Try
Using reportConvertCommand As SqlCommand = clientDBConnection.CreateCommand()
reportConvertCommand.CommandText = "usp_UPD_AdHoc2Standard"
reportConvertCommand.CommandType = Data.CommandType.StoredProcedure
reportConvertCommand.Parameters.AddWithValue("@report_name", reportName)
reportConvertCommand.Parameters.AddWithValue("@report_title", reportTitle)
reportConvertCommand.Parameters.AddWithValue("@logonid ", Session("client_DatabaseLogon"))
reportConvertCommand.Parameters.AddWithValue("@user_LongName", Session("User_LongName"))
reportConvertCommand.Connection.Open()
reportConvertCommand.ExecuteNonQuery()
End Using
Me.lblErrorMessage.Text = "Report successfully converted to 'standard'."
Me.lblErrorMessage.Visible = True
Catch ex As Exception
If (ex IsNot Nothing) Then
Me.lblErrorMessage.Text = ex.Message
Me.lblErrorMessage.Visible = True
ExceptionHandled = True
Else
Me.lblErrorMessage.Text = "Report conversion status uncertain!"
Me.lblErrorMessage.Visible = True
End If
End Try
clientDBConnection.Dispose()
End If
GridView1.DataBind()
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="PageHeader" Runat="Server">
<h1 style="text-align: center">
Manage Reports</h1>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="Body" Runat="Server">
<br />
<h2>
</h2>
<table summary="Report filters table" frame="box">
<h1 style="text-align: left">
Report list filters</h1>
<tr>
<td colspan="2" style="width: 268px">
<asp:CheckBox ID="cbFile" runat="server" Checked="True" Enabled="False" Width="20px" Text="File" /></td>
<td colspan="2" style="width: 234px">
<asp:CheckBox ID="cbReportSource" runat="server" Checked="True" Enabled="False" Text="Report Source" /></td>
</tr>
<tr>
<td colspan="2" style="width: 268px">
<asp:Panel ID="pnlFileType" runat="server" Height="50px">
<table width="100%">
<tr>
<td >
<asp:CheckBox ID="cbFileType" runat="server" AutoPostBack="True" Text="File type" Checked="True" Enabled="False" /></td>
<td style="width: 100px; height: 55px;">
<asp:DropDownList ID="ddlFileType" runat="server" AppendDataBoundItems="True" AutoPostBack="True"
DataSourceID="dsFileType" DataTextField="file_type" DataValueField="file_type_extended" OnSelectedIndexChanged="ddlFileType_SelectedIndexChanged">
</asp:DropDownList><asp:SqlDataSource ID="dsFileType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_FileTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbFirstFileDate" runat="server" AutoPostBack="True" Text="First file date" OnCheckedChanged="cbFirstFileDate_CheckedChanged" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlFirstFileDate" runat="server" Visible="False" DataSourceID="dsFirstFileDate" DataTextField="first_file_date" DataValueField="first_file_date" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsFirstFileDate" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_FirstFileDate" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
<td colspan="2" style="width: 234px">
<asp:Panel ID="pnlReportSource" runat="server" Height="50px" Width="125px">
<table>
<tr>
<td style="width: 100px">
<asp:RadioButtonList ID="rblReportSource" runat="server" AutoPostBack="True" OnSelectedIndexChanged="rblReportSource_SelectedIndexChanged" >
<asp:ListItem Value="adhoc">Ad Hoc</asp:ListItem>
<asp:ListItem Value="standard" Selected="True">Standard</asp:ListItem>
<asp:ListItem Value="both">Both</asp:ListItem>
</asp:RadioButtonList></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlReportSource" runat="server" DataSourceID="dsStandardReports" DataTextField="standard_name" DataValueField="standard_name" AppendDataBoundItems="True" AutoPostBack="True" >
<asp:ListItem Selected="True">Any</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="dsStandardReports" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_StandardReports" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="cbReportType" DefaultValue="False" Name="report_type_checked"
PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="ddlReportType" DefaultValue="Null" Name="report_type"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="cbFileType" DefaultValue="False" Name="file_type_checked"
PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="ddlFileType" DefaultValue="Null" Name="file_type"
PropertyName="SelectedItem.Text" Type="String" />
<asp:Parameter DefaultValue="False" Name="route_type_checked" Type="Boolean" />
<asp:Parameter Name="route_type" Type="String" DefaultValue="Null" />
<asp:Parameter DefaultValue="False" Name="product_type_checked" Type="Boolean" />
<asp:Parameter Name="product_type" Type="String" DefaultValue="Null" />
<asp:Parameter DefaultValue="False" Name="offer_type_checked" Type="Boolean" />
<asp:Parameter Name="offer_type" Type="String" DefaultValue="Null" />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td colspan="2" style="width: 268px">
<br />
<asp:CheckBox ID="cbReportType" runat="server" OnCheckedChanged="cbReportType_CheckedChanged" AutoPostBack="True" Text="Report type" /><asp:Panel ID="pnlReportType" runat="server" Height="50px" Width="125px" Visible="False">
<asp:DropDownList ID="ddlReportType" runat="server" DataSourceID="dsReportType" DataTextField="report_type"
DataValueField="report_type" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsReportType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ReportTypes4FileType" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="ddlFileType" Name="file_type" PropertyName="SelectedItem.Text"
Type="String" DefaultValue="Any" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Panel>
</td>
<td colspan="2" style="width: 234px">
<asp:CheckBox ID="cbProductOffer" runat="server" AutoPostBack="True" OnCheckedChanged="cbProductOffer_CheckedChanged" Text="Product offer" /><br />
<asp:Panel ID="pnlProductOffer" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbOfferType" runat="server" AutoPostBack="True" OnCheckedChanged="cbOfferType_CheckedChanged" Text="Offer type" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlOfferType" runat="server" Visible="False" DataSourceID="dsOfferTypes" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsOfferTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbRouteType" runat="server" OnCheckedChanged="cbRouteType_CheckedChanged"
Text="Route type" AutoPostBack="True" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlRouteType" runat="server" Visible="False" DataSourceID="dsRouteTypes" DataTextField="route_type" DataValueField="route_type_short" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsRouteTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_RouteTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbProductType" runat="server" Text="Product type" OnCheckedChanged="cbProductType_CheckedChanged" AutoPostBack="True" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlProductType" runat="server" Visible="False" DataSourceID="dsProductTypes"
DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td colspan="2" style="width: 268px">
<asp:CheckBox ID="cbEligibleFor" runat="server" OnCheckedChanged="cbEligibleFor_CheckedChanged"
Text="Eligible for" AutoPostBack="True" /><br />
<asp:Panel ID="pnlEligibleFor" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px; height: 29px;">
<asp:CheckBox ID="cbEFProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbEFProductType_CheckedChanged" /></td>
<td style="width: 100px; height: 29px;">
<asp:DropDownList ID="ddlEFProductType" runat="server" Visible="False" DataSourceID="dsEFProductTypes" DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsEFProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbEFProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbEFProductName_CheckedChanged" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlEFProductName" runat="server" Visible="False" DataSourceID="dsEPProductNames" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsEPProductNames" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="up_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbEFOfferType" runat="server" AutoPostBack="True" Text="Offer type" OnCheckedChanged="cbEFOfferType_CheckedChanged" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlEFOfferType" runat="server" Visible="False" DataSourceID="dsEFOfferType" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsEFOfferType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px; height: 29px">
<asp:CheckBox ID="cbEFContactType" runat="server" AutoPostBack="True" Text="Contact type" OnCheckedChanged="cbEFContactType_CheckedChanged" /></td>
<td style="width: 100px; height: 29px">
<asp:DropDownList ID="ddlEFContactType" runat="server" Visible="False" DataSourceID="dsEFContactTypes" DataTextField="contact_type" DataValueField="contact_type" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsEFContactTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ContactTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
<td colspan="2" style="width: 234px">
<asp:CheckBox ID="cbHolds" runat="server" Text="Holds" OnCheckedChanged="cbHolds_CheckedChanged" AutoPostBack="True" /><br />
<asp:Panel ID="pnlHolds" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbHProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbHProductType_CheckedChanged" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlHProductType" runat="server" Visible="False" DataSourceID="dsHProductType" DataTextField="product_type" DataValueField="product_type" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsHProductType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbHProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbHProductName_CheckedChanged" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlHProductName" runat="server" Visible="False" DataSourceID="dsHProductName" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsHProductName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td colspan="4">
<asp:CheckBox ID="cbPopulation" runat="server" AutoPostBack="True"
Text="Population" OnCheckedChanged="cbPopulation_CheckedChanged" />
<asp:Panel ID="pnlPopulation" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbCustomerType" runat="server" OnCheckedChanged="cbCustomerType_CheckedChanged" AutoPostBack="True" Text="Customer type" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlCustomerType" runat="server" DataSourceID="dsPopCustomerTypes" DataTextField="customer_type" DataValueField="customer_type" Visible="False" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsPopCustomerTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_CustomerTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbSubPopulation" runat="server" OnCheckedChanged="cbSubPopulation_CheckedChanged" AutoPostBack="True" Text="Sub-population" /></td>
<td style="width: 100px">
<asp:Panel ID="pnlSubPopulation" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
Sub‑population field filter</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlsubPopulationFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationsSubPopulationfields" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True">
<asp:ListItem Selected="True">Not specified</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulationfields" runat="server"
ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulationFields"
SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
Sub-population</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlSubPopulation" runat="server" DataSourceID="dsPopulationsSubPopulation" DataTextField="subpopulation_name" DataValueField="subpopulation_name" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulation" runat="server"
ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations"
SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbSelectionGroup" runat="server" OnCheckedChanged="cbSelectionGroup_CheckedChanged" AutoPostBack="True" Text="Selection group" /></td>
<td style="width: 100px">
<asp:Panel ID="pnlSelectionGroup" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
Selection group field filter</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlSelectionGroupFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSelectionGroupsubPopulation" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True">
<asp:ListItem Selected="True">Not specified</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupsubPopulation"
runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_SelectionGroups" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
Selection group sub‑population filter</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlSelectionGroupsubPopulationFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSubPopulationNames" DataTextField="subpopulation_name" DataValueField="subpopulation_name" CausesValidation="True">
<asp:ListItem Selected="True">Not specified</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSubPopulationNames" runat="server"
ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations"
SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px; height: 10px">
Selection group</td>
<td style="width: 100px; height: 10px">
<asp:DropDownList ID="ddlSelectionGroup" runat="server" DataSourceID="dsPopulationSelectionGroupSelectionGroup" DataTextField="selection_group" DataValueField="selection_group" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupSelectionGroup"
runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_SelectionGroupsList" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td colspan="2" style="height: 16px; width: 268px;">
<asp:CheckBox ID="cbTestPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbTestPack_CheckedChanged" Text="Test/Pack" />
<asp:Panel ID="pnlTestPack" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbTest" runat="server" AutoPostBack="True" OnCheckedChanged="cbTest_CheckedChanged" Text="Test" /></td>
<td style="width: 100px">
<asp:Panel ID="pnlTest" runat="server" Height="50px" Visible="False">
<table>
<tr>
<td style="width: 100px">
Type</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlType" runat="server" AppendDataBoundItems="True" DataSourceID="dsTestTypes" DataTextField="test_type" DataValueField="test_type" AutoPostBack="True">
<asp:ListItem Selected="True" Value="Not specified">Any</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="dsTestTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_TestTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
<tr>
<td style="width: 100px">
Name</td>
<td style="width: 100px">
<asp:DropDownList ID="ddlName" runat="server" DataSourceID="dsTestPackTestName" DataTextField="test_name" DataValueField="test_name" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsTestPackTestName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_TestName" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="N" Name="test_order" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
</tr>
<tr>
<td style="width: 100px">
<asp:CheckBox ID="cbPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbPack_CheckedChanged"
Text="Pack" /></td>
<td style="width: 100px">
<asp:DropDownList ID="ddlPack" runat="server" Visible="False" DataSourceID="dsTestPackPacks" DataTextField="pack_description" DataValueField="pack_code" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsTestPackPacks" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_Packs" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</td>
</tr>
</table>
</asp:Panel>
</td>
<td colspan="2" style="width: 234px; height: 16px">
<asp:CheckBox ID="cbCreatedBy" runat="server" AutoPostBack="True" OnCheckedChanged="cbCreatedBy_CheckedChanged"
Text="Created by" />
<asp:Panel ID="pnlCreatedBy" runat="server" Height="50px" Visible="False">
<asp:DropDownList ID="ddlCreatedBy" runat="server" DataSourceID="dsUsers4Client" DataTextField="UserName" DataValueField="UserName" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="dsUsers4Client" runat="server" ConnectionString="<%$ ConnectionStrings:webtoolUserConnectionString %>"
SelectCommand="usp_SEL_Users4Client_Unique" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="pkClient_ID" SessionField="pkClient_ID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:Panel>
</td>
</tr>
</table>
<br />
<asp:Label ID="lblErrorMessage" runat="server" CssClass="warning" ></asp:Label><br />
<asp:GridView ID="GridView1" runat="server" EmptyDataText="No reports were found matching the above criteria."
DataSourceID="dsReportList" AllowSorting="True" AutoGenerateColumns="False"
onrowcreated="GridView1_RowCreated"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
DataKeyNames="report_name" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:TemplateField ShowHeader="False" Visible=False>
<ItemTemplate>
<asp:Label ID="reportname" Runat="Server"
Text='<%# Eval("report_name") %>' Visible="false" />
<asp:Label ID="reportCategory" Runat="Server"
Text='<%# Eval("report_category") %>' Visible="false" />
</ItemTemplate>
<HeaderStyle BackColor="White" />
</asp:TemplateField>
<asp:buttonfield commandname="View_Report" ButtonType="Button" text="View" >
<ControlStyle CssClass="button" />
<HeaderStyle BackColor="White" />
</asp:buttonfield>
<asp:buttonfield commandname="Delete_Report" ButtonType="Button" text="Delete" >
<ControlStyle CssClass="button" />
<HeaderStyle BackColor="White" />
</asp:buttonfield>
<asp:buttonfield commandname="Convert_Report" ButtonType="Button" text="Convert" >
<ControlStyle CssClass="button" />
<HeaderStyle BackColor="White" />
</asp:buttonfield>
<asp:CommandField ButtonType="Button" ShowEditButton="True" >
<HeaderStyle BackColor="White" />
<ControlStyle CssClass="button" />
</asp:CommandField>
<asp:BoundField DataField="report_category" HeaderText="Cat." InsertVisible="False" ReadOnly="True" SortExpression="report_category" />
<asp:BoundField DataField="offer_type" HeaderText="Offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type" />
<asp:BoundField DataField="route_type" HeaderText="Route type" InsertVisible="False" ReadOnly="True" SortExpression="route_type" />
<asp:BoundField DataField="product_type" HeaderText="Product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type" />
<asp:BoundField DataField="first_file_date" HeaderText="First file date" InsertVisible="False" ReadOnly="True" SortExpression="first_file_date" />
<asp:BoundField DataField="report_title" HeaderText="Report title" InsertVisible="False" SortExpression="report_title" />
<asp:BoundField DataField="start_date_formatted" HeaderText="Creation date" InsertVisible="False" ReadOnly="True" SortExpression="start_date_formatted" />
<asp:BoundField DataField="report_type" HeaderText="Report type" InsertVisible="False" ReadOnly="True" SortExpression="report_type" />
<asp:BoundField DataField="number_of_files" HeaderText="# files" InsertVisible="False" ReadOnly="True" SortExpression="number_of_files" />
<asp:BoundField DataField="customer_type" HeaderText="Population - Customer type" InsertVisible="False" ReadOnly="True" SortExpression="customer_type" />
<asp:BoundField DataField="subpopulation_name" HeaderText="Population - sub-population" InsertVisible="False" ReadOnly="True" SortExpression="subpopulation_name" />
<asp:BoundField DataField="selection_group" HeaderText="Population - Selection group" InsertVisible="False" ReadOnly="True" SortExpression="selection_group" />
<asp:BoundField DataField="product_type_eligibility" HeaderText="Eligibility - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_eligibility" />
<asp:BoundField DataField="product_name_eligibility" HeaderText="Eligibility - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_eligibility" />
<asp:BoundField DataField="offer_type_eligibility" HeaderText="Eligibility - offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type_eligibility" />
<asp:BoundField DataField="contact_type_eligibility" HeaderText="Eligibility - contact type" InsertVisible="False" ReadOnly="True" SortExpression="contact_type_eligibility" />
<asp:BoundField DataField="product_type_holding" HeaderText="Holds - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_holding" />
<asp:BoundField DataField="product_name_holding" HeaderText="Holds - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_holding" />
<asp:BoundField DataField="promotion_code" HeaderText="Promotion code" InsertVisible="False" ReadOnly="True" SortExpression="promotion_code" />
<asp:BoundField DataField="userlongname" HeaderText="Created by" InsertVisible="False" ReadOnly="True" SortExpression="userlongname" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="dsReportList" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"
SelectCommand="usp_SEL_ReportList" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UPD_AdHocReportTitle" UpdateCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="rblReportSource" Name="radioReportSource" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="ddlReportSource" Name="reportSource" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="cbProductType" Name="productTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbRouteType" Name="routeTypechecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbOfferType" Name="offerTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbReportType" Name="reportTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbPack" Name="packChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbFirstFileDate" Name="firstFileDateChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbCreatedBy" Name="createdByChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbEFProductType" Name="eFProductTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbEFProductName" Name="eFProductChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbEFOfferType" Name="eFOfferTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbEFContactType" Name="eFContactTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbHProductType" Name="hProductTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbHProductName" Name="hProductNameChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbCustomerType" Name="customerTypeChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbSubPopulation" Name="subPopulationChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbSelectionGroup" Name="selectionGroupChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="cbTest" Name="testChecked" PropertyName="Checked" Type="Boolean" />
<asp:ControlParameter ControlID="ddlProductType" Name="productType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlRouteType" Name="routeType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlOfferType" Name="offerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlReportType" Name="reportType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlPack" Name="packCode" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlFirstFileDate" Name="firstFileDate" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlCreatedBy" Name="createdBy" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlEFProductType" Name="eFProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlEFProductName" Name="eFProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlEFOfferType" Name="eFOfferType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlEFContactType" Name="eFContactType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlHProductType" Name="hProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlHProductName" Name="hProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlCustomerType" Name="customerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlSubPopulation" Name="subPopulation" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlSelectionGroup" Name="SelectionGroup" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlFileType" Name="fileType" PropertyName="SelectedItem.Text" DefaultValue=Null Type="String" />
<asp:ControlParameter ControlID="ddlName" Name="testName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="report_name" Type="String" />
<asp:Parameter Name="report_title" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
</asp:Content>
I've got a solution for you, but hold onto your hat Cliff, there's a fair bit to it!!! OK... let's go for it....
The real issue is that when you use markup, you can only "bind" the ConnectionString and ProviderName properties using expression builders (i.e. with <%$ notation)... which limits you to Resources, AppSettings and ConnectionStrings. Fortunately, this is
our saviour, as we can create our own ExpressionBuilder classes. What's more, with the power of Reflector, we can see what the existing ExpressionBuilder classes do, copy them and make our own modifications! So here's the plan...
We create a new ExpressionBuilder class, called SessionConnectionStringsExpressionBuilder
We tell ASP.NET about the new expression builder class in web.config
We replace all normal <%$ ConnectionStrings:ConnectionString %> and <%$ ConnectionStrings:ConnectionString.ProviderName %> markup with our own expression builder calls <%$
SessionConnectionStrings:ConnectionString %> and <%$
SessionConnectionStrings:ConnectionString.ProviderName %>
Sounds simple in theory, and in practise it's pretty straightforward too... there's just a fair bit of code to paste in here!! But before I do that, we need to discuss the
SessionConnectionStringsExpressionBuilder class. Here's how I designed things:
I create a variable in the user's Session to store all our ConnectionStringSettings of type
ConnectionStringSettingsCollection. The advantages of doing this are a) that it means we only use up 1 session variable and b) it can be easily substituted for
ConfigurationManager.ConnectionStrings
I define a default key by which to store this session variable ("SESSION_STATE_CONNECTION_STRINGS"), but this can be overridden in the appSettings in the web.config file if you happen to be using that already for something else
I expose methods to Add and Remove connection string settings from the Session making it simple for you to code things
When you add connection string settings to the session, there must be a setting of the same name in the web.config connectionStrings section.
My reasoning for this last point is that the fallback position is to use a standard connection string if it doesn't exist in session... we can only adopt this fallback position if the connectionString exists!!
So enough rambling and on with the instructions... take a deep breath and follow!!
SessionConnectionStringsExpressionBuilder class
Create a new class in your App_Code directory and paste this code in:
Imports System.Web
Imports System.Web.Compilation
Imports System.CodeDom
Imports System.ComponentModel
Imports System.Resources
Imports System.Threading
Imports System.Globalization
Imports System.Security.Permissions
<ExpressionPrefix("SessionConnectionStrings")> _
<ExpressionEditor("System.Web.UI.Design.ConnectionStringsExpressionEditor, System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"), AspNetHostingPermission(SecurityAction.LinkDemand, Level:=AspNetHostingPermissionLevel.Minimal), AspNetHostingPermission(SecurityAction.InheritanceDemand, Level:=AspNetHostingPermissionLevel.Minimal)> _
Public Class SessionConnectionStringsExpressionBuilder
Inherits ExpressionBuilder
#Region "Session Handling"
' we will store alternative connection strings in session state in a collection under a single key
' this is the default key we'll use
' otherwise use ConnectionStringSessionKey in AppSettings
Private Const DEFAULT_SESSION_KEY As String = "SESSION_STATE_CONNECTION_STRINGS"
' helper function to pull out the session key to use
Private Shared Function GetSessionKey() As String
' see if the user has overridden the session key
Dim SessionKey As String = ConfigurationManager.AppSettings.Get("ConnectionStringSessionKey")
If String.IsNullOrEmpty(SessionKey) Then
' no so use the default
SessionKey = DEFAULT_SESSION_KEY
End If
Return SessionKey
End Function
#End Region
#Region "AddConnectionStrings To Session"
Public Shared Sub AddConnectionStringSettings(ByVal settings As ConnectionStringSettings)
' see if we've got these connection string settings in the config file
Dim cs As ConnectionStringSettings = ConfigurationManager.ConnectionStrings.Item(settings.Name)
If cs Is Nothing Then
' no, so we've got no back up when the session times out... so throw an exception
Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {settings.Name}))
End If
' everything's OK, so add in the settings!
GetConnectionStringSettings().Add(settings)
End Sub
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String)
AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString))
End Sub
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String, ByVal providerName As String)
AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString, providerName))
End Sub
Public Shared Sub RemoveConnectionStringSettings(ByVal settings As ConnectionStringSettings)
GetConnectionStringSettings.Remove(settings)
End Sub
Public Shared Sub RemoveConnectionStringSettings(ByVal name As String)
GetConnectionStringSettings.Remove(name)
End Sub
#End Region
#Region "ConnectionStrings Helper Functions"
Private Shared Function GetConnectionStringSettings() As ConnectionStringSettingsCollection
Dim session As HttpSessionState = HttpContext.Current.Session
Dim sessionKey As String = GetSessionKey()
Dim connectionStrings As ConnectionStringSettingsCollection = session.Item(sessionKey)
If connectionStrings Is Nothing Then
connectionStrings = New ConnectionStringSettingsCollection()
session.Item(sessionKey) = connectionStrings
End If
Return connectionStrings
End Function
Private Shared Function GetConnectionStringSettings(ByVal connectionStringName As String) As ConnectionStringSettings
Dim css As ConnectionStringSettingsCollection = GetConnectionStringSettings()
Dim cs As ConnectionStringSettings = css.Item(connectionStringName)
If cs Is Nothing Then
cs = ConfigurationManager.ConnectionStrings.Item(connectionStringName)
End If
Return cs
End Function
' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection!
Public Shared Function GetConnectionString(ByVal connectionStringName As String) As String
Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName)
If (settings1 Is Nothing) Then
Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName}))
End If
Return settings1.ConnectionString
End Function
Public Shared Function GetConnectionStringProviderName(ByVal connectionStringName As String) As String
Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName)
If (settings1 Is Nothing) Then
Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName}))
End If
Return settings1.ProviderName
End Function
#End Region
#Region "Main ConnectionStringsExpressionBuilder Code"
' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection!
Public Overrides Function GetCodeExpression(ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As System.CodeDom.CodeExpression
Dim pair1 As Pair = DirectCast(parsedData, Pair)
Dim text1 As String = CStr(pair1.First)
If CBool(pair1.Second) Then
Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeExpression() {New CodePrimitiveExpression(text1)})
End If
Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeExpression() {New CodePrimitiveExpression(text1)})
End Function
Public Overrides Function EvaluateExpression(ByVal target As Object, ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As Object
Dim pair1 As Pair = DirectCast(parsedData, Pair)
Dim text1 As String = CStr(pair1.First)
Dim flag1 As Boolean = CBool(pair1.Second)
Dim settings1 As ConnectionStringSettings = SessionConnectionStringsExpressionBuilder.GetConnectionStringSettings(text1)
If flag1 Then
Return SessionConnectionStringsExpressionBuilder.GetConnectionString(text1)
End If
Return SessionConnectionStringsExpressionBuilder.GetConnectionStringProviderName(text1)
End Function
#Region "Simple Copies From ConnectionStringExpressionBuilder Class"
Public Overrides ReadOnly Property SupportsEvaluate() As Boolean
Get
Return True
End Get
End Property
Public Overrides Function ParseExpression(ByVal expression As String, ByVal propertyType As Type, ByVal context As ExpressionBuilderContext) As Object
Dim text1 As String = String.Empty
Dim flag1 As Boolean = True
If (Not expression Is Nothing) Then
If expression.EndsWith(".connectionstring", StringComparison.OrdinalIgnoreCase) Then
text1 = expression.Substring(0, (expression.Length - ".connectionstring".Length))
ElseIf expression.EndsWith(".providername", StringComparison.OrdinalIgnoreCase) Then
flag1 = False
text1 = expression.Substring(0, (expression.Length - ".providername".Length))
Else
text1 = expression
End If
End If
Return New Pair(text1, flag1)
End Function
#End Region
#End Region
End Class
#Region "Resource Helper Class"
' cut down version of System.Web.SR
' used to pull culture sensitive resources out of System.Web
' simply used Reflector to pull out what is required!
Friend NotInheritable Class SR
Private Shared loader As SR
Private Shared s_InternalSyncObject As Object
Private SRresources As ResourceManager
Public Shared ReadOnly Property Resources() As ResourceManager
Get
Return SR.GetLoader.SRresources
End Get
End Property
Private Shared ReadOnly Property InternalSyncObject() As Object
Get
If (SR.s_InternalSyncObject Is Nothing) Then
Dim obj1 As New Object
Interlocked.CompareExchange(SR.s_InternalSyncObject, obj1, Nothing)
End If
Return SR.s_InternalSyncObject
End Get
End Property
Private Shared ReadOnly Property Culture() As CultureInfo
Get
Return Nothing
End Get
End Property
Friend Sub New()
Me.SRresources = New ResourceManager("System.Web", MyBase.GetType.Assembly)
End Sub
Public Shared Function GetString(ByVal name As String) As String
Dim sr1 As SR = SR.GetLoader
If (sr1 Is Nothing) Then
Return Nothing
End If
Return sr1.SRresources.GetString(name, SR.Culture)
End Function
Public Shared Function GetString(ByVal name As String, ByVal ParamArray args As Object()) As String
Dim sr1 As SR = SR.GetLoader
If (sr1 Is Nothing) Then
Return Nothing
End If
Dim text1 As String = sr1.SRresources.GetString(name, SR.Culture)
If ((args Is Nothing) OrElse (args.Length <= 0)) Then
Return text1
End If
Dim num1 As Integer = 0
Do While (num1 < args.Length)
Dim text2 As String = TryCast(args(num1), String)
If ((Not text2 Is Nothing) AndAlso (text2.Length > 1024)) Then
args(num1) = (text2.Substring(0, 1021) & "...")
End If
num1 += 1
Loop
Return String.Format(CultureInfo.CurrentCulture, text1, args)
End Function
Public Shared Function GetObject(ByVal name As String) As Object
Dim sr1 As SR = SR.GetLoader
If (sr1 Is Nothing) Then
Return Nothing
End If
Return sr1.SRresources.GetObject(name, SR.Culture)
End Function
Private Shared Function GetLoader() As SR
If (SR.loader Is Nothing) Then
SyncLock SR.InternalSyncObject
If (SR.loader Is Nothing) Then
SR.loader = New SR
End If
End SyncLock
End If
Return SR.loader
End Function
End Class
#End Region
Set Up The web.config
We now need to get ASP.NET to recognise our new expression builder... this is done by adding these lines in under the <system.web><compilation> section:
Now all you should need to do is set the session connection string. I've added some shared methods to the class to assist with that:
Public Shared Sub AddConnectionStringSettings(ByVal settings As ConnectionStringSettings)
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String)
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String, ByVal providerName As String)
There's one minor caveat here... if you try and add the session connection string on Page_Init (etc)... the page has already evaluated the ExpressionBuilder for your SqlDataSource. So you will have to be a little bit careful about your coding logic, and
might have to force a page reload.
Good luck with this, let me know how you get on and if it's suitable for your situation/any problems you've got!
This looks like a great solution. You will have to allow me some time to understand it more fully and try and implement it - hopefully over the weekend - although I am already doing client work all weekend so I may struggle ;-)
I will get to it ASAP and let you know how I get on. A couple of people from other forums have asked me to keep them posted on possible solutions so you may have made several people happy in one go!
No worries Cliff... the best way to understand what I've done is probably to use Reflector on the original ConnectionStringsExpressionBuilder class and look at the differences between that and what I've done... to be honest there aren't too many changes,
just a conditional substitution between the ConfigurationManager and the Session!
Cheers,
James
Marked as answer by Kevin Yu - MSFT on Feb 12, 2007 07:22 AM
I'm getting a compliation error on the new ConnectionStringsExrpessionBuilder class that I don't understand:
Error 102 Type 'CodeBLOCKED' is not defined. C:\Documents and Settings\Cliff\My Documents\My Webs\DataLabDotNet\App_Code\SessionConnectionStringsExpressionBuilder.vb 111 127 C:\...\DataLabDotNet\
Just googled CodeBLOCKED and it only seems to come up with a couple of hits... relating to Beta 2!
Will have a look later for you... but in the meantime, can you confirm your environment for me please?
I'm also contemplating putting together a SessionAppSettingsExpresssionBuilder and a SessionResourcesExpressionBuilder to finish this off... this will probably be a few days, and I need to find somewhere to upload the code to!
CliffMitchel...
Member
147 Points
47 Posts
Dynamic connection strings
Feb 06, 2007 02:06 PM|LINK
Dynamic connection strings
I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public
objClientConnection As New ConnectionStringSettingsPublic
Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" Then
objClientConnection.ConnectionString = Session("ClientDBConnectionString")
objClientConnection.Name = "ClientDBConnection"
objClientConnection.ProviderName = "System.Data.SqlClient"
Else
objClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")
End If
End Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
<
asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
Many thanks,
Cliff
Kevin Yu - M...
All-Star
19021 Points
1467 Posts
Re: Dynamic connection strings
Feb 08, 2007 05:24 AM|LINK
Hi,
If your connection string is created dynamically at run time. We cannot set the connection string at design time. Binding to the property might not work since this depends on when you call DataBind on your controls.
In this case, it's better to set this in code behind event handler for Page_Load like
Me.dsTest.ConnectionString = objClientConnection.ConnectionString
Then you can call DataBind() manually.
HTH.
Kevin Yu
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
CliffMitchel...
Member
147 Points
47 Posts
Re: Dynamic connection strings
Feb 08, 2007 08:12 AM|LINK
Hi Kevin,
Thanks for your response. I think what you are saying is that the only way to set the connection strings dynamically at run time is to use code behind? The problem with this is that the site I am working on has a lot of interrelated, databound controls many of which are coded declaratively. I would have to recode every one of these (and there are hundreds!) in code behind and many are inside other controls (wizards, gridview, etc) and so are complicated to get at in code behind.
Given that every one of these databound controls uses:
ConnectionString=<%$ ConnectionStrings:WebTool1ConnectionString %>
I would have thought there would be a way to either:
1. dynamically change what connection string this declaration points to, or
2. change this to ConnectionString = <% some variable %> where 'some variable' is set to the required connection string in code behind.
Is this not possible?
Many thanks for you help,
Cliff
Kevin Yu - M...
All-Star
19021 Points
1467 Posts
Re: Dynamic connection strings
Feb 09, 2007 08:27 AM|LINK
Hi Cliff,
Could you let me see the code that you're calling DataBind on the page? Because whether this is possible depends on when data is being pulled from the server.
Thanks!
Kevin Yu
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
CliffMitchel...
Member
147 Points
47 Posts
Re: Dynamic connection strings
Feb 09, 2007 10:46 AM|LINK
Hi Kevin,
Sorry but I don't have permissions to upload files so will have to cut and paste the code, see below. This is a typical example of one of the pages on this site. Some are more complex with cascading DropDownLists within Panels within Wizard controls, etc.
Cliff
Source code:
<%@ Page Language="VB" MasterPageFile="~/Client.master" Trace="False" Theme="DataLabClient1"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Master.Page.Title = "Datalab - Manage Reports" End Sub Protected Sub cbReportType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbReportType.Checked Then pnlReportType.Visible = True Else pnlReportType.Visible = False End If End Sub Protected Sub rblReportSource_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) 'If Not Page.IsPostBack Then ddlReportSource.Items.Clear() ddlReportSource.AppendDataBoundItems = True ddlReportSource.Items.Add(New ListItem("Any", "Any")) 'End If If rblReportSource.SelectedValue = "standard" Then ddlReportSource.Visible = True Else ddlReportSource.Visible = False End If GridView1.DataBind() End Sub Protected Sub cbFirstFileDate_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbFirstFileDate.Checked Then ddlFirstFileDate.Visible = True Else ddlFirstFileDate.Visible = False End If End Sub Protected Sub cbProductOffer_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbProductOffer.Checked Then pnlProductOffer.Visible = True Else pnlProductOffer.Visible = False End If End Sub Protected Sub cbOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbOfferType.Checked Then ddlOfferType.Visible = True Else ddlOfferType.Visible = False End If End Sub Protected Sub cbRouteType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbRouteType.Checked Then ddlRouteType.Visible = True Else ddlRouteType.Visible = False End If End Sub Protected Sub cbProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbProductType.Checked Then ddlProductType.Visible = True Else ddlProductType.Visible = False End If End Sub Protected Sub cbEligibleFor_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbEligibleFor.Checked Then pnlEligibleFor.Visible = True Else pnlEligibleFor.Visible = False End If End Sub Protected Sub cbHolds_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbHolds.Checked Then pnlHolds.Visible = True Else pnlHolds.Visible = False End If End Sub Protected Sub cbEFProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbEFProductType.Checked Then ddlEFProductType.Visible = True Else ddlEFProductType.Visible = False End If End Sub Protected Sub cbEFProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbEFProductName.Checked Then ddlEFProductName.Visible = True Else ddlEFProductName.Visible = False End If End Sub Protected Sub cbEFOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbEFOfferType.Checked Then ddlEFOfferType.Visible = True Else ddlEFOfferType.Visible = False End If End Sub Protected Sub cbEFContactType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbEFContactType.Checked Then ddlEFContactType.Visible = True Else ddlEFContactType.Visible = False End If End Sub Protected Sub cbHProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbHProductType.Checked Then ddlHProductType.Visible = True Else ddlHProductType.Visible = False End If End Sub Protected Sub cbHProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbHProductName.Checked Then ddlHProductName.Visible = True Else ddlHProductName.Visible = False End If End Sub Protected Sub cbPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbPopulation.Checked Then pnlPopulation.Visible = True Else pnlPopulation.Visible = False End If End Sub Protected Sub cbCustomerType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbCustomerType.Checked Then ddlCustomerType.Visible = True Else ddlCustomerType.Visible = False End If End Sub Protected Sub cbSubPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbSubPopulation.Checked Then pnlSubPopulation.Visible = True Else pnlSubPopulation.Visible = False End If End Sub Protected Sub cbSelectionGroup_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbSelectionGroup.Checked Then pnlSelectionGroup.Visible = True Else pnlSelectionGroup.Visible = False End If End Sub Protected Sub cbTestPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbTestPack.Checked Then pnlTestPack.Visible = True Else pnlTestPack.Visible = False End If End Sub Protected Sub cbCreatedBy_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbCreatedBy.Checked Then pnlCreatedBy.Visible = True Else pnlCreatedBy.Visible = False End If End Sub Protected Sub cbTest_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbTest.Checked Then pnlTest.Visible = True Else pnlTest.Visible = False End If End Sub Protected Sub cbPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) If cbPack.Checked Then ddlPack.Visible = True Else ddlPack.Visible = False End If End Sub Protected Sub ddlFileType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) ddlReportSource.Items.Clear() ddlReportSource.AppendDataBoundItems = True ddlReportSource.Items.Add(New ListItem("Any", "Any")) GridView1.DataBind() End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If Not Page.IsPostBack Then 'ddlReportSource.Items.Clear() 'ddlReportSource.AppendDataBoundItems = True 'ddlReportSource.Items.Add(New ListItem("Any", "Any")) End If End Sub Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound If (e.Row.RowType = DataControlRowType.DataRow) Then Dim row As DataRowView = CType(e.Row.DataItem, DataRowView) If (Not row Is Nothing) Then 'Dim reportType As String = LCase(Trim(CType(row("report_category"), String))) ' Select Case LCase(Trim(rblReportSource.SelectedValue)) Case "adhoc" ' Display all command buttons Dim convertButton As ButtonField = GridView1.Columns(3) convertButton.Visible = True Dim renameButton As CommandField = GridView1.Columns(4) renameButton.Visible = True Case "standard" ' Just display the View and Delete buttons Dim convertButton As ButtonField = GridView1.Columns(3) convertButton.Visible = False Dim renameButton As CommandField = GridView1.Columns(4) renameButton.Visible = False Case "both" ' Display all command buttons Dim convertButton As ButtonField = GridView1.Columns(3) convertButton.Visible = False Dim renameButton As CommandField = GridView1.Columns(4) renameButton.Visible = False Case Else End Select End If End If End Sub Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) ' Dim row As GridViewRow = GridView1.SelectedRow 'Response.Write("process gridview row = " & row.Cells(7).Text) 'Dim DropDownList1 As New DropDownList 'Dim ddl As DropDownList = row.FindControl("DropDownList1") 'Response.Write("<br />process gridview row = " & ddl.SelectedItem.ToString) 'Response.End() End Sub 'Protected Sub dsReportList1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsReportList.Deleting ' For x As Integer = 0 To e.Command.Parameters.Count - 1 'Trace.Write(e.Command.Parameters(x).ParameterName) 'Trace.Write(e.Command.Parameters(x).Value) 'Next 'End Sub Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs) If (Not IsDBNull(e.Exception)) Then Me.lblErrorMessage.Text = e.Exception.Message e.ExceptionHandled = True End If End Sub Protected Sub dsReportList_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) If (e.Exception IsNot Nothing) Then Me.lblErrorMessage.Text = e.Exception.Message e.ExceptionHandled = True Else Me.lblErrorMessage.Text = "Report successfully deleted." End If End Sub Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs) ' The GridViewCommandEventArgs class does not contain a ' property that indicates which row's command button was ' clicked. To identify which row's button was clicked, use ' the button's CommandArgument property by setting it to the ' row's index. If e.Row.RowType = DataControlRowType.DataRow Then ' Retrieve the Button control from the first column. Dim delButton As Button = CType(e.Row.Cells(2).Controls(0), Button) ' Set the Button's CommandArgument property with the ' row's index. delButton.CommandArgument = e.Row.RowIndex.ToString() delButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}") ' Now repeat for the View button Dim viewButton As Button = CType(e.Row.Cells(1).Controls(0), Button) ' Set the Button's CommandArgument property with the ' row's index. viewButton.CommandArgument = e.Row.RowIndex.ToString() 'viewButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}") ' Repeat for the Convert adhoc to standard report button Dim convertButton As Button = CType(e.Row.Cells(3).Controls(0), Button) ' Set the Button's CommandArgument property with the ' row's index. convertButton.CommandArgument = e.Row.RowIndex.ToString() End If End Sub Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) If e.CommandName = "Delete_Report" Then Me.lblErrorMessage.Text = "" Me.lblErrorMessage.Visible = False ' Convert the row index stored in the CommandArgument ' property to an Integer. Dim index As Integer = Convert.ToInt32(e.CommandArgument) ' Retrieve the row that contains the button clicked ' by the user from the Rows collection. Dim row As GridViewRow = GridView1.Rows(index) Dim lblReportName As Label = CType(row.FindControl("reportname"), Label) Dim reportName As String = lblReportName.Text Dim reportCategory As String = row.Cells(4).Text ' Delete the specified report Dim clientDBConnection As New SqlConnection() clientDBConnection.ConnectionString = Session("ClientDBConnectionString") Try Using reportDeleteCommand As SqlCommand = clientDBConnection.CreateCommand() reportDeleteCommand.CommandText = "usp_DEL_Report" reportDeleteCommand.CommandType = Data.CommandType.StoredProcedure reportDeleteCommand.Parameters.AddWithValue("@report_name", reportName) reportDeleteCommand.Parameters.AddWithValue("@report_category", reportCategory) reportDeleteCommand.Connection.Open() reportDeleteCommand.ExecuteNonQuery() End Using Me.lblErrorMessage.Text = "Report successfully deleted." Me.lblErrorMessage.Visible = True Catch ex As Exception If (ex IsNot Nothing) Then Me.lblErrorMessage.Text = ex.Message Me.lblErrorMessage.Visible = True ExceptionHandled = True Else Me.lblErrorMessage.Text = "Report successfully deleted." Me.lblErrorMessage.Visible = True End If End Try clientDBConnection.Dispose() ElseIf e.CommandName = "View_Report" Then ' Convert the row index stored in the CommandArgument ' property to an Integer. Dim index As Integer = Convert.ToInt32(e.CommandArgument) ' Retrieve the row that contains the button clicked ' by the user from the Rows collection. Dim row As GridViewRow = GridView1.Rows(index) Dim lblReportName As Label = CType(row.FindControl("reportname"), Label) Dim reportName As String = lblReportName.Text Dim lblReportCategory As Label = CType(row.FindControl("reportCategory"), Label) Dim reportCategory As String = LCase(lblReportCategory.Text) Response.Redirect("~/Main/Table_Manager/Reports_View.aspx?rept=" & reportName & "&cat=" & reportCategory) ElseIf e.CommandName = "Convert_Report" Then ' Convert the row index stored in the CommandArgument ' property to an Integer. Dim index As Integer = Convert.ToInt32(e.CommandArgument) ' Retrieve the row that contains the button clicked ' by the user from the Rows collection. Dim row As GridViewRow = GridView1.Rows(index) ' Get the report name ... Dim lblReportName As Label = CType(row.FindControl("reportname"), Label) Dim reportName As String = lblReportName.Text ' ... and get the report title Dim reportTitle As String = row.Cells(10).Text 'Response.Write("convert report!! " & reportName) 'Response.Write("<br />Reeport title = " & reportTitle) 'Response.Write("<br />DB Login = " & Session("client_DatabaseLogon")) 'Response.Write("<br /> User = " & Session("User_LongName")) 'Response.End() ' Now, convert the report from adhoc to standard Dim clientDBConnection As New SqlConnection() clientDBConnection.ConnectionString = Session("ClientDBConnectionString") Try Using reportConvertCommand As SqlCommand = clientDBConnection.CreateCommand() reportConvertCommand.CommandText = "usp_UPD_AdHoc2Standard" reportConvertCommand.CommandType = Data.CommandType.StoredProcedure reportConvertCommand.Parameters.AddWithValue("@report_name", reportName) reportConvertCommand.Parameters.AddWithValue("@report_title", reportTitle) reportConvertCommand.Parameters.AddWithValue("@logonid ", Session("client_DatabaseLogon")) reportConvertCommand.Parameters.AddWithValue("@user_LongName", Session("User_LongName")) reportConvertCommand.Connection.Open() reportConvertCommand.ExecuteNonQuery() End Using Me.lblErrorMessage.Text = "Report successfully converted to 'standard'." Me.lblErrorMessage.Visible = True Catch ex As Exception If (ex IsNot Nothing) Then Me.lblErrorMessage.Text = ex.Message Me.lblErrorMessage.Visible = True ExceptionHandled = True Else Me.lblErrorMessage.Text = "Report conversion status uncertain!" Me.lblErrorMessage.Visible = True End If End Try clientDBConnection.Dispose() End If GridView1.DataBind() End Sub </script> <asp:Content ID="Content1" ContentPlaceHolderID="PageHeader" Runat="Server"> <h1 style="text-align: center"> Manage Reports</h1> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="Body" Runat="Server"> <br /> <h2> </h2> <table summary="Report filters table" frame="box"> <h1 style="text-align: left"> Report list filters</h1> <tr> <td colspan="2" style="width: 268px"> <asp:CheckBox ID="cbFile" runat="server" Checked="True" Enabled="False" Width="20px" Text="File" /></td> <td colspan="2" style="width: 234px"> <asp:CheckBox ID="cbReportSource" runat="server" Checked="True" Enabled="False" Text="Report Source" /></td> </tr> <tr> <td colspan="2" style="width: 268px"> <asp:Panel ID="pnlFileType" runat="server" Height="50px"> <table width="100%"> <tr> <td > <asp:CheckBox ID="cbFileType" runat="server" AutoPostBack="True" Text="File type" Checked="True" Enabled="False" /></td> <td style="width: 100px; height: 55px;"> <asp:DropDownList ID="ddlFileType" runat="server" AppendDataBoundItems="True" AutoPostBack="True" DataSourceID="dsFileType" DataTextField="file_type" DataValueField="file_type_extended" OnSelectedIndexChanged="ddlFileType_SelectedIndexChanged"> </asp:DropDownList><asp:SqlDataSource ID="dsFileType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_FileTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbFirstFileDate" runat="server" AutoPostBack="True" Text="First file date" OnCheckedChanged="cbFirstFileDate_CheckedChanged" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlFirstFileDate" runat="server" Visible="False" DataSourceID="dsFirstFileDate" DataTextField="first_file_date" DataValueField="first_file_date" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsFirstFileDate" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_FirstFileDate" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> <td colspan="2" style="width: 234px"> <asp:Panel ID="pnlReportSource" runat="server" Height="50px" Width="125px"> <table> <tr> <td style="width: 100px"> <asp:RadioButtonList ID="rblReportSource" runat="server" AutoPostBack="True" OnSelectedIndexChanged="rblReportSource_SelectedIndexChanged" > <asp:ListItem Value="adhoc">Ad Hoc</asp:ListItem> <asp:ListItem Value="standard" Selected="True">Standard</asp:ListItem> <asp:ListItem Value="both">Both</asp:ListItem> </asp:RadioButtonList></td> <td style="width: 100px"> <asp:DropDownList ID="ddlReportSource" runat="server" DataSourceID="dsStandardReports" DataTextField="standard_name" DataValueField="standard_name" AppendDataBoundItems="True" AutoPostBack="True" > <asp:ListItem Selected="True">Any</asp:ListItem> </asp:DropDownList><asp:SqlDataSource ID="dsStandardReports" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_StandardReports" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="cbReportType" DefaultValue="False" Name="report_type_checked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="ddlReportType" DefaultValue="Null" Name="report_type" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="cbFileType" DefaultValue="False" Name="file_type_checked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="ddlFileType" DefaultValue="Null" Name="file_type" PropertyName="SelectedItem.Text" Type="String" /> <asp:Parameter DefaultValue="False" Name="route_type_checked" Type="Boolean" /> <asp:Parameter Name="route_type" Type="String" DefaultValue="Null" /> <asp:Parameter DefaultValue="False" Name="product_type_checked" Type="Boolean" /> <asp:Parameter Name="product_type" Type="String" DefaultValue="Null" /> <asp:Parameter DefaultValue="False" Name="offer_type_checked" Type="Boolean" /> <asp:Parameter Name="offer_type" Type="String" DefaultValue="Null" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td colspan="2" style="width: 268px"> <br /> <asp:CheckBox ID="cbReportType" runat="server" OnCheckedChanged="cbReportType_CheckedChanged" AutoPostBack="True" Text="Report type" /><asp:Panel ID="pnlReportType" runat="server" Height="50px" Width="125px" Visible="False"> <asp:DropDownList ID="ddlReportType" runat="server" DataSourceID="dsReportType" DataTextField="report_type" DataValueField="report_type" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsReportType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ReportTypes4FileType" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="ddlFileType" Name="file_type" PropertyName="SelectedItem.Text" Type="String" DefaultValue="Any" /> </SelectParameters> </asp:SqlDataSource> </asp:Panel> </td> <td colspan="2" style="width: 234px"> <asp:CheckBox ID="cbProductOffer" runat="server" AutoPostBack="True" OnCheckedChanged="cbProductOffer_CheckedChanged" Text="Product offer" /><br /> <asp:Panel ID="pnlProductOffer" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbOfferType" runat="server" AutoPostBack="True" OnCheckedChanged="cbOfferType_CheckedChanged" Text="Offer type" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlOfferType" runat="server" Visible="False" DataSourceID="dsOfferTypes" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsOfferTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbRouteType" runat="server" OnCheckedChanged="cbRouteType_CheckedChanged" Text="Route type" AutoPostBack="True" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlRouteType" runat="server" Visible="False" DataSourceID="dsRouteTypes" DataTextField="route_type" DataValueField="route_type_short" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsRouteTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_RouteTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbProductType" runat="server" Text="Product type" OnCheckedChanged="cbProductType_CheckedChanged" AutoPostBack="True" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlProductType" runat="server" Visible="False" DataSourceID="dsProductTypes" DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td colspan="2" style="width: 268px"> <asp:CheckBox ID="cbEligibleFor" runat="server" OnCheckedChanged="cbEligibleFor_CheckedChanged" Text="Eligible for" AutoPostBack="True" /><br /> <asp:Panel ID="pnlEligibleFor" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px; height: 29px;"> <asp:CheckBox ID="cbEFProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbEFProductType_CheckedChanged" /></td> <td style="width: 100px; height: 29px;"> <asp:DropDownList ID="ddlEFProductType" runat="server" Visible="False" DataSourceID="dsEFProductTypes" DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsEFProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbEFProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbEFProductName_CheckedChanged" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlEFProductName" runat="server" Visible="False" DataSourceID="dsEPProductNames" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsEPProductNames" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="up_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbEFOfferType" runat="server" AutoPostBack="True" Text="Offer type" OnCheckedChanged="cbEFOfferType_CheckedChanged" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlEFOfferType" runat="server" Visible="False" DataSourceID="dsEFOfferType" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsEFOfferType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px; height: 29px"> <asp:CheckBox ID="cbEFContactType" runat="server" AutoPostBack="True" Text="Contact type" OnCheckedChanged="cbEFContactType_CheckedChanged" /></td> <td style="width: 100px; height: 29px"> <asp:DropDownList ID="ddlEFContactType" runat="server" Visible="False" DataSourceID="dsEFContactTypes" DataTextField="contact_type" DataValueField="contact_type" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsEFContactTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ContactTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> <td colspan="2" style="width: 234px"> <asp:CheckBox ID="cbHolds" runat="server" Text="Holds" OnCheckedChanged="cbHolds_CheckedChanged" AutoPostBack="True" /><br /> <asp:Panel ID="pnlHolds" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbHProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbHProductType_CheckedChanged" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlHProductType" runat="server" Visible="False" DataSourceID="dsHProductType" DataTextField="product_type" DataValueField="product_type" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsHProductType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbHProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbHProductName_CheckedChanged" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlHProductName" runat="server" Visible="False" DataSourceID="dsHProductName" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsHProductName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td colspan="4"> <asp:CheckBox ID="cbPopulation" runat="server" AutoPostBack="True" Text="Population" OnCheckedChanged="cbPopulation_CheckedChanged" /> <asp:Panel ID="pnlPopulation" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbCustomerType" runat="server" OnCheckedChanged="cbCustomerType_CheckedChanged" AutoPostBack="True" Text="Customer type" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlCustomerType" runat="server" DataSourceID="dsPopCustomerTypes" DataTextField="customer_type" DataValueField="customer_type" Visible="False" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsPopCustomerTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_CustomerTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbSubPopulation" runat="server" OnCheckedChanged="cbSubPopulation_CheckedChanged" AutoPostBack="True" Text="Sub-population" /></td> <td style="width: 100px"> <asp:Panel ID="pnlSubPopulation" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> Sub‑population field filter</td> <td style="width: 100px"> <asp:DropDownList ID="ddlsubPopulationFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationsSubPopulationfields" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True"> <asp:ListItem Selected="True">Not specified</asp:ListItem> </asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulationfields" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulationFields" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> Sub-population</td> <td style="width: 100px"> <asp:DropDownList ID="ddlSubPopulation" runat="server" DataSourceID="dsPopulationsSubPopulation" DataTextField="subpopulation_name" DataValueField="subpopulation_name" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulation" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbSelectionGroup" runat="server" OnCheckedChanged="cbSelectionGroup_CheckedChanged" AutoPostBack="True" Text="Selection group" /></td> <td style="width: 100px"> <asp:Panel ID="pnlSelectionGroup" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> Selection group field filter</td> <td style="width: 100px"> <asp:DropDownList ID="ddlSelectionGroupFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSelectionGroupsubPopulation" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True"> <asp:ListItem Selected="True">Not specified</asp:ListItem> </asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupsubPopulation" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SelectionGroups" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> Selection group sub‑population filter</td> <td style="width: 100px"> <asp:DropDownList ID="ddlSelectionGroupsubPopulationFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSubPopulationNames" DataTextField="subpopulation_name" DataValueField="subpopulation_name" CausesValidation="True"> <asp:ListItem Selected="True">Not specified</asp:ListItem> </asp:DropDownList><asp:SqlDataSource ID="dsPopulationSubPopulationNames" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px; height: 10px"> Selection group</td> <td style="width: 100px; height: 10px"> <asp:DropDownList ID="ddlSelectionGroup" runat="server" DataSourceID="dsPopulationSelectionGroupSelectionGroup" DataTextField="selection_group" DataValueField="selection_group" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupSelectionGroup" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SelectionGroupsList" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td colspan="2" style="height: 16px; width: 268px;"> <asp:CheckBox ID="cbTestPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbTestPack_CheckedChanged" Text="Test/Pack" /> <asp:Panel ID="pnlTestPack" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbTest" runat="server" AutoPostBack="True" OnCheckedChanged="cbTest_CheckedChanged" Text="Test" /></td> <td style="width: 100px"> <asp:Panel ID="pnlTest" runat="server" Height="50px" Visible="False"> <table> <tr> <td style="width: 100px"> Type</td> <td style="width: 100px"> <asp:DropDownList ID="ddlType" runat="server" AppendDataBoundItems="True" DataSourceID="dsTestTypes" DataTextField="test_type" DataValueField="test_type" AutoPostBack="True"> <asp:ListItem Selected="True" Value="Not specified">Any</asp:ListItem> </asp:DropDownList><asp:SqlDataSource ID="dsTestTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_TestTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> <tr> <td style="width: 100px"> Name</td> <td style="width: 100px"> <asp:DropDownList ID="ddlName" runat="server" DataSourceID="dsTestPackTestName" DataTextField="test_name" DataValueField="test_name" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsTestPackTestName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_TestName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="N" Name="test_order" Type="String" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> </tr> <tr> <td style="width: 100px"> <asp:CheckBox ID="cbPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbPack_CheckedChanged" Text="Pack" /></td> <td style="width: 100px"> <asp:DropDownList ID="ddlPack" runat="server" Visible="False" DataSourceID="dsTestPackPacks" DataTextField="pack_description" DataValueField="pack_code" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsTestPackPacks" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_Packs" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </td> </tr> </table> </asp:Panel> </td> <td colspan="2" style="width: 234px; height: 16px"> <asp:CheckBox ID="cbCreatedBy" runat="server" AutoPostBack="True" OnCheckedChanged="cbCreatedBy_CheckedChanged" Text="Created by" /> <asp:Panel ID="pnlCreatedBy" runat="server" Height="50px" Visible="False"> <asp:DropDownList ID="ddlCreatedBy" runat="server" DataSourceID="dsUsers4Client" DataTextField="UserName" DataValueField="UserName" AutoPostBack="True"> </asp:DropDownList><asp:SqlDataSource ID="dsUsers4Client" runat="server" ConnectionString="<%$ ConnectionStrings:webtoolUserConnectionString %>" SelectCommand="usp_SEL_Users4Client_Unique" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="pkClient_ID" SessionField="pkClient_ID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> </asp:Panel> </td> </tr> </table> <br /> <asp:Label ID="lblErrorMessage" runat="server" CssClass="warning" ></asp:Label><br /> <asp:GridView ID="GridView1" runat="server" EmptyDataText="No reports were found matching the above criteria." DataSourceID="dsReportList" AllowSorting="True" AutoGenerateColumns="False" onrowcreated="GridView1_RowCreated" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" DataKeyNames="report_name" OnRowCommand="GridView1_RowCommand"> <Columns> <asp:TemplateField ShowHeader="False" Visible=False> <ItemTemplate> <asp:Label ID="reportname" Runat="Server" Text='<%# Eval("report_name") %>' Visible="false" /> <asp:Label ID="reportCategory" Runat="Server" Text='<%# Eval("report_category") %>' Visible="false" /> </ItemTemplate> <HeaderStyle BackColor="White" /> </asp:TemplateField> <asp:buttonfield commandname="View_Report" ButtonType="Button" text="View" > <ControlStyle CssClass="button" /> <HeaderStyle BackColor="White" /> </asp:buttonfield> <asp:buttonfield commandname="Delete_Report" ButtonType="Button" text="Delete" > <ControlStyle CssClass="button" /> <HeaderStyle BackColor="White" /> </asp:buttonfield> <asp:buttonfield commandname="Convert_Report" ButtonType="Button" text="Convert" > <ControlStyle CssClass="button" /> <HeaderStyle BackColor="White" /> </asp:buttonfield> <asp:CommandField ButtonType="Button" ShowEditButton="True" > <HeaderStyle BackColor="White" /> <ControlStyle CssClass="button" /> </asp:CommandField> <asp:BoundField DataField="report_category" HeaderText="Cat." InsertVisible="False" ReadOnly="True" SortExpression="report_category" /> <asp:BoundField DataField="offer_type" HeaderText="Offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type" /> <asp:BoundField DataField="route_type" HeaderText="Route type" InsertVisible="False" ReadOnly="True" SortExpression="route_type" /> <asp:BoundField DataField="product_type" HeaderText="Product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type" /> <asp:BoundField DataField="first_file_date" HeaderText="First file date" InsertVisible="False" ReadOnly="True" SortExpression="first_file_date" /> <asp:BoundField DataField="report_title" HeaderText="Report title" InsertVisible="False" SortExpression="report_title" /> <asp:BoundField DataField="start_date_formatted" HeaderText="Creation date" InsertVisible="False" ReadOnly="True" SortExpression="start_date_formatted" /> <asp:BoundField DataField="report_type" HeaderText="Report type" InsertVisible="False" ReadOnly="True" SortExpression="report_type" /> <asp:BoundField DataField="number_of_files" HeaderText="# files" InsertVisible="False" ReadOnly="True" SortExpression="number_of_files" /> <asp:BoundField DataField="customer_type" HeaderText="Population - Customer type" InsertVisible="False" ReadOnly="True" SortExpression="customer_type" /> <asp:BoundField DataField="subpopulation_name" HeaderText="Population - sub-population" InsertVisible="False" ReadOnly="True" SortExpression="subpopulation_name" /> <asp:BoundField DataField="selection_group" HeaderText="Population - Selection group" InsertVisible="False" ReadOnly="True" SortExpression="selection_group" /> <asp:BoundField DataField="product_type_eligibility" HeaderText="Eligibility - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_eligibility" /> <asp:BoundField DataField="product_name_eligibility" HeaderText="Eligibility - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_eligibility" /> <asp:BoundField DataField="offer_type_eligibility" HeaderText="Eligibility - offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type_eligibility" /> <asp:BoundField DataField="contact_type_eligibility" HeaderText="Eligibility - contact type" InsertVisible="False" ReadOnly="True" SortExpression="contact_type_eligibility" /> <asp:BoundField DataField="product_type_holding" HeaderText="Holds - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_holding" /> <asp:BoundField DataField="product_name_holding" HeaderText="Holds - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_holding" /> <asp:BoundField DataField="promotion_code" HeaderText="Promotion code" InsertVisible="False" ReadOnly="True" SortExpression="promotion_code" /> <asp:BoundField DataField="userlongname" HeaderText="Created by" InsertVisible="False" ReadOnly="True" SortExpression="userlongname" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="dsReportList" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_ReportList" SelectCommandType="StoredProcedure" UpdateCommand="usp_UPD_AdHocReportTitle" UpdateCommandType="StoredProcedure" > <SelectParameters> <asp:ControlParameter ControlID="rblReportSource" Name="radioReportSource" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="ddlReportSource" Name="reportSource" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="cbProductType" Name="productTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbRouteType" Name="routeTypechecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbOfferType" Name="offerTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbReportType" Name="reportTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbPack" Name="packChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbFirstFileDate" Name="firstFileDateChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbCreatedBy" Name="createdByChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbEFProductType" Name="eFProductTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbEFProductName" Name="eFProductChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbEFOfferType" Name="eFOfferTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbEFContactType" Name="eFContactTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbHProductType" Name="hProductTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbHProductName" Name="hProductNameChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbCustomerType" Name="customerTypeChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbSubPopulation" Name="subPopulationChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbSelectionGroup" Name="selectionGroupChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="cbTest" Name="testChecked" PropertyName="Checked" Type="Boolean" /> <asp:ControlParameter ControlID="ddlProductType" Name="productType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlRouteType" Name="routeType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlOfferType" Name="offerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlReportType" Name="reportType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlPack" Name="packCode" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlFirstFileDate" Name="firstFileDate" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlCreatedBy" Name="createdBy" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlEFProductType" Name="eFProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlEFProductName" Name="eFProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlEFOfferType" Name="eFOfferType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlEFContactType" Name="eFContactType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlHProductType" Name="hProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlHProductName" Name="hProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlCustomerType" Name="customerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlSubPopulation" Name="subPopulation" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlSelectionGroup" Name="SelectionGroup" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlFileType" Name="fileType" PropertyName="SelectedItem.Text" DefaultValue=Null Type="String" /> <asp:ControlParameter ControlID="ddlName" Name="testName" PropertyName="SelectedValue" DefaultValue=Null Type="String" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="report_name" Type="String" /> <asp:Parameter Name="report_title" Type="String" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> </asp:Content>James_2JS
Participant
1526 Points
335 Posts
Re: Dynamic connection strings
Feb 09, 2007 03:46 PM|LINK
Hi,
I've got a solution for you, but hold onto your hat Cliff, there's a fair bit to it!!! OK... let's go for it....
The real issue is that when you use markup, you can only "bind" the ConnectionString and ProviderName properties using expression builders (i.e. with <%$ notation)... which limits you to Resources, AppSettings and ConnectionStrings. Fortunately, this is our saviour, as we can create our own ExpressionBuilder classes. What's more, with the power of Reflector, we can see what the existing ExpressionBuilder classes do, copy them and make our own modifications! So here's the plan...
Sounds simple in theory, and in practise it's pretty straightforward too... there's just a fair bit of code to paste in here!! But before I do that, we need to discuss the SessionConnectionStringsExpressionBuilder class. Here's how I designed things:
So enough rambling and on with the instructions... take a deep breath and follow!!
SessionConnectionStringsExpressionBuilder class
Create a new class in your App_Code directory and paste this code in:
Imports System.Web Imports System.Web.Compilation Imports System.CodeDom Imports System.ComponentModel Imports System.Resources Imports System.Threading Imports System.Globalization Imports System.Security.Permissions <ExpressionPrefix("SessionConnectionStrings")> _ <ExpressionEditor("System.Web.UI.Design.ConnectionStringsExpressionEditor, System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"), AspNetHostingPermission(SecurityAction.LinkDemand, Level:=AspNetHostingPermissionLevel.Minimal), AspNetHostingPermission(SecurityAction.InheritanceDemand, Level:=AspNetHostingPermissionLevel.Minimal)> _ Public Class SessionConnectionStringsExpressionBuilder Inherits ExpressionBuilder #Region "Session Handling" ' we will store alternative connection strings in session state in a collection under a single key ' this is the default key we'll use ' otherwise use ConnectionStringSessionKey in AppSettings Private Const DEFAULT_SESSION_KEY As String = "SESSION_STATE_CONNECTION_STRINGS" ' helper function to pull out the session key to use Private Shared Function GetSessionKey() As String ' see if the user has overridden the session key Dim SessionKey As String = ConfigurationManager.AppSettings.Get("ConnectionStringSessionKey") If String.IsNullOrEmpty(SessionKey) Then ' no so use the default SessionKey = DEFAULT_SESSION_KEY End If Return SessionKey End Function #End Region #Region "AddConnectionStrings To Session" Public Shared Sub AddConnectionStringSettings(ByVal settings As ConnectionStringSettings) ' see if we've got these connection string settings in the config file Dim cs As ConnectionStringSettings = ConfigurationManager.ConnectionStrings.Item(settings.Name) If cs Is Nothing Then ' no, so we've got no back up when the session times out... so throw an exception Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {settings.Name})) End If ' everything's OK, so add in the settings! GetConnectionStringSettings().Add(settings) End Sub Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String) AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString)) End Sub Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String, ByVal providerName As String) AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString, providerName)) End Sub Public Shared Sub RemoveConnectionStringSettings(ByVal settings As ConnectionStringSettings) GetConnectionStringSettings.Remove(settings) End Sub Public Shared Sub RemoveConnectionStringSettings(ByVal name As String) GetConnectionStringSettings.Remove(name) End Sub #End Region #Region "ConnectionStrings Helper Functions" Private Shared Function GetConnectionStringSettings() As ConnectionStringSettingsCollection Dim session As HttpSessionState = HttpContext.Current.Session Dim sessionKey As String = GetSessionKey() Dim connectionStrings As ConnectionStringSettingsCollection = session.Item(sessionKey) If connectionStrings Is Nothing Then connectionStrings = New ConnectionStringSettingsCollection() session.Item(sessionKey) = connectionStrings End If Return connectionStrings End Function Private Shared Function GetConnectionStringSettings(ByVal connectionStringName As String) As ConnectionStringSettings Dim css As ConnectionStringSettingsCollection = GetConnectionStringSettings() Dim cs As ConnectionStringSettings = css.Item(connectionStringName) If cs Is Nothing Then cs = ConfigurationManager.ConnectionStrings.Item(connectionStringName) End If Return cs End Function ' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection! Public Shared Function GetConnectionString(ByVal connectionStringName As String) As String Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName) If (settings1 Is Nothing) Then Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName})) End If Return settings1.ConnectionString End Function Public Shared Function GetConnectionStringProviderName(ByVal connectionStringName As String) As String Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName) If (settings1 Is Nothing) Then Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName})) End If Return settings1.ProviderName End Function #End Region #Region "Main ConnectionStringsExpressionBuilder Code" ' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection! Public Overrides Function GetCodeExpression(ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As System.CodeDom.CodeExpression Dim pair1 As Pair = DirectCast(parsedData, Pair) Dim text1 As String = CStr(pair1.First) If CBool(pair1.Second) Then Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeExpression() {New CodePrimitiveExpression(text1)}) End If Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeExpression() {New CodePrimitiveExpression(text1)}) End Function Public Overrides Function EvaluateExpression(ByVal target As Object, ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As Object Dim pair1 As Pair = DirectCast(parsedData, Pair) Dim text1 As String = CStr(pair1.First) Dim flag1 As Boolean = CBool(pair1.Second) Dim settings1 As ConnectionStringSettings = SessionConnectionStringsExpressionBuilder.GetConnectionStringSettings(text1) If flag1 Then Return SessionConnectionStringsExpressionBuilder.GetConnectionString(text1) End If Return SessionConnectionStringsExpressionBuilder.GetConnectionStringProviderName(text1) End Function #Region "Simple Copies From ConnectionStringExpressionBuilder Class" Public Overrides ReadOnly Property SupportsEvaluate() As Boolean Get Return True End Get End Property Public Overrides Function ParseExpression(ByVal expression As String, ByVal propertyType As Type, ByVal context As ExpressionBuilderContext) As Object Dim text1 As String = String.Empty Dim flag1 As Boolean = True If (Not expression Is Nothing) Then If expression.EndsWith(".connectionstring", StringComparison.OrdinalIgnoreCase) Then text1 = expression.Substring(0, (expression.Length - ".connectionstring".Length)) ElseIf expression.EndsWith(".providername", StringComparison.OrdinalIgnoreCase) Then flag1 = False text1 = expression.Substring(0, (expression.Length - ".providername".Length)) Else text1 = expression End If End If Return New Pair(text1, flag1) End Function #End Region #End Region End Class #Region "Resource Helper Class" ' cut down version of System.Web.SR ' used to pull culture sensitive resources out of System.Web ' simply used Reflector to pull out what is required! Friend NotInheritable Class SR Private Shared loader As SR Private Shared s_InternalSyncObject As Object Private SRresources As ResourceManager Public Shared ReadOnly Property Resources() As ResourceManager Get Return SR.GetLoader.SRresources End Get End Property Private Shared ReadOnly Property InternalSyncObject() As Object Get If (SR.s_InternalSyncObject Is Nothing) Then Dim obj1 As New Object Interlocked.CompareExchange(SR.s_InternalSyncObject, obj1, Nothing) End If Return SR.s_InternalSyncObject End Get End Property Private Shared ReadOnly Property Culture() As CultureInfo Get Return Nothing End Get End Property Friend Sub New() Me.SRresources = New ResourceManager("System.Web", MyBase.GetType.Assembly) End Sub Public Shared Function GetString(ByVal name As String) As String Dim sr1 As SR = SR.GetLoader If (sr1 Is Nothing) Then Return Nothing End If Return sr1.SRresources.GetString(name, SR.Culture) End Function Public Shared Function GetString(ByVal name As String, ByVal ParamArray args As Object()) As String Dim sr1 As SR = SR.GetLoader If (sr1 Is Nothing) Then Return Nothing End If Dim text1 As String = sr1.SRresources.GetString(name, SR.Culture) If ((args Is Nothing) OrElse (args.Length <= 0)) Then Return text1 End If Dim num1 As Integer = 0 Do While (num1 < args.Length) Dim text2 As String = TryCast(args(num1), String) If ((Not text2 Is Nothing) AndAlso (text2.Length > 1024)) Then args(num1) = (text2.Substring(0, 1021) & "...") End If num1 += 1 Loop Return String.Format(CultureInfo.CurrentCulture, text1, args) End Function Public Shared Function GetObject(ByVal name As String) As Object Dim sr1 As SR = SR.GetLoader If (sr1 Is Nothing) Then Return Nothing End If Return sr1.SRresources.GetObject(name, SR.Culture) End Function Private Shared Function GetLoader() As SR If (SR.loader Is Nothing) Then SyncLock SR.InternalSyncObject If (SR.loader Is Nothing) Then SR.loader = New SR End If End SyncLock End If Return SR.loader End Function End Class #End RegionSet Up The web.config
We now need to get ASP.NET to recognise our new expression builder... this is done by adding these lines in under the <system.web><compilation> section:
<compilation debug="false">
<expressionBuilders>
<add expressionPrefix="SessionConnectionStrings"
type="SessionConnectionStringsExpressionBuilder"/>
</expressionBuilders>
The expressionPrefix is what we will use in our markup.
Change Our Markup
becomes
Setting The Session Connection String
Now all you should need to do is set the session connection string. I've added some shared methods to the class to assist with that:
So you might call:
SessionConnectionStringsExpressionBuilder.AddConnectionStringSettings("MyConnectionString", "xxx Connection Details xxx"", "System.Data.SQLClient")
Caveat
There's one minor caveat here... if you try and add the session connection string on Page_Init (etc)... the page has already evaluated the ExpressionBuilder for your SqlDataSource. So you will have to be a little bit careful about your coding logic, and might have to force a page reload.
Good luck with this, let me know how you get on and if it's suitable for your situation/any problems you've got!
Cheers,
James
CliffMitchel...
Member
147 Points
47 Posts
Re: Dynamic connection strings
Feb 09, 2007 04:03 PM|LINK
WOW!
James, many, many thanks.
This looks like a great solution. You will have to allow me some time to understand it more fully and try and implement it - hopefully over the weekend - although I am already doing client work all weekend so I may struggle ;-)
I will get to it ASAP and let you know how I get on. A couple of people from other forums have asked me to keep them posted on possible solutions so you may have made several people happy in one go!
Thanks again and have a good weekend.
Cliff
James_2JS
Participant
1526 Points
335 Posts
Re: Dynamic connection strings
Feb 09, 2007 05:51 PM|LINK
No worries Cliff... the best way to understand what I've done is probably to use Reflector on the original ConnectionStringsExpressionBuilder class and look at the differences between that and what I've done... to be honest there aren't too many changes, just a conditional substitution between the ConfigurationManager and the Session!
Cheers,
James
CliffMitchel...
Member
147 Points
47 Posts
Re: Dynamic connection strings
Feb 12, 2007 10:02 AM|LINK
Hi James,
I'm getting a compliation error on the new ConnectionStringsExrpessionBuilder class that I don't understand:
Error 102 Type 'CodeBLOCKED' is not defined. C:\Documents and Settings\Cliff\My Documents\My Webs\DataLabDotNet\App_Code\SessionConnectionStringsExpressionBuilder.vb 111 127 C:\...\DataLabDotNet\
Any suggestions?
Thanks,
Cliff
James_2JS
Participant
1526 Points
335 Posts
Re: Dynamic connection strings
Feb 12, 2007 11:36 AM|LINK
Hmmm... I'm a bit confused with that too!!
Just googled CodeBLOCKED and it only seems to come up with a couple of hits... relating to Beta 2!
Will have a look later for you... but in the meantime, can you confirm your environment for me please?
I'm also contemplating putting together a SessionAppSettingsExpresssionBuilder and a SessionResourcesExpressionBuilder to finish this off... this will probably be a few days, and I need to find somewhere to upload the code to!
Thanks,
James