Dynamic Connection Strings

Hi,

I am looking to allow a user to select which database they need to connect to, and then for them to be able to use that connection string until they choose a different database. I have a separate database specifically to hold the list of databases with the respective connection strings, and various forms that are currently looking at the web.config for the connection string. Unfortunately, I can't just put all the connection strings into the web.config as the number of databases available to the user will increase on a weekly basis which will be handled by a database administrator, so I want a user to select from a list of databases (for which they have permissions), and for that connection string to remain for that user. Potentially there will be 20 or more users each connected to a different database (all SQL Express).

If anyone can provide any help on this I will be truly thankful as I've hunted on the internet for hours and have been unable to find anything that has helped.

Thanks in advance.

Paul

Try this class: SqlConnectionStringBuilder

There are versions for Oracle, OLEDB, etc.

|||

I have a very similar problem and I thought I had it cracked with the following:

Public

objClientConnectionAsNew ConnectionStringSettings

PublicSub Page_Init(ByVal senderAsObject,ByVal eAs System.EventArgs)
If Session("ClientDBConnectionString") <>""Then
objClientConnection.ConnectionString = Session("ClientDBConnectionString")
objClientConnection.Name ="ClientDBConnection"
objClientConnection.ProviderName ="System.Data.SqlClient"
Else
objClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")
EndIf
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:SqlDataSourceID="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

|||

Thanks guys. I'll have to do some reading onSqlConnectionStringBuilder as this isn't a class I've come across before. Any pointers as to its usage in the situation I described would be really helpful. I'm also grateful to Cliff for his contribution as I'm really pleased to see somebody else struggling with the same problem, I'm not alone after all! Cliff, if / when you find a solution for your code (and likewise if I find a way around this one), I'm sure it'll help lots of developers from spending days struggling as we have, if we post the solution here.

Thanks,

Paul

|||

Hi All,

I think that I have managed to crack this one and hope that by posting the solution here it helps one or two others. I was a little worried about sessions timing out (as with the suggestion from Cliff), so have instead opted to use a profile. I'm only scratching the surface here - this all just functional. I must thank Ryan Olshan within these forums, as the one bit I couldn't get to work was the sorting and paging of the gridview as without the usual sqlDataSource, these don't work at all (the full thread for this can be found at:http://forums.asp.net/thread/1240547.aspx), so that part of the code is entirely thanks to him.

Because I want to force the user to select which database to connect to (for the purposes here they are Project1, Project2 & Project3), I have a database that has a table that holds the database names and connection strings. I have added the connection string for this in the normal way in the web.config file. So to add the profile to the web.config file:

<

connectionStrings>

<

addname="ConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestCRM.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />

</

connectionStrings>

<

system.web>

<

anonymousIdentificationenabled="true"/>

<

profileenabled="true">

<

properties>

<

addname="ConnStr"allowAnonymous="true"/>

</

properties>

</

profile>

</

system.web>

Next, I have a gridview that connects to the connection string in the web.config. This allows the user to select which database to connect to (at the moment, I'm displaying the connection strings along with the other project details, and simply selecting the content of the particular selected database:

Protected

Sub GridView1_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs GridViewCommandEventArgs)' Get the currently selected row using the SelectedRow property.Dim rowAs GridViewRow = GridView1.SelectedRowIf e.CommandName ="Select"Then' Convert the row index stored in the CommandArgument' property to an Integer.Dim indexAsInteger = Convert.ToInt32(e.CommandArgument)' Get the connection string from the appropriate' cell in the GridView control.Dim selectedRowAs GridViewRow = GridView1.Rows(index)Dim ConStrCellAs TableCell = selectedRow.Cells(8)Dim ConStrAsString = ConStrCell.Text

' Pass the connection string to the profile...

Profile.ConnStr = ConStr

' ... and move to the page that should utilise this connection string

Response.Redirect(

"test.aspx")EndIfEndSub

<%

@.PageLanguage="VB"AutoEventWireup="true"CodeFile="Default.aspx.vb"Inherits="_Default" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.1//EN""http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Project Selector</title>

</

head>

<

body><formid="form1"runat="server"><asp:ScriptManagerID="ScriptManager1"runat="server"/><div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"AutoGenerateColumns="False"CellPadding="4"DataSourceID="SqlDataSource1"EmptyDataText="There are no data records to display."ForeColor="#333333"GridLines="None"OnRowCommand="GridView1_SelectedIndexChanged"><FooterStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/><Columns><asp:ButtonFieldCommandName="Select"Text="Select"/><asp:BoundFieldDataField="ProjName"HeaderText="Project Name"SortExpression="ProjName"><HeaderStyleHorizontalAlign="Center"VerticalAlign="Middle"/></asp:BoundField><asp:BoundFieldDataField="ProjDesc"HeaderText="Project Desc"SortExpression="ProjDesc"/><asp:BoundFieldDataField="DBName"HeaderText="Database Name"SortExpression="DBName"/><asp:BoundFieldDataField="TotalHD"HeaderText="Total Half Days"SortExpression="TotalHD"/><asp:CheckBoxFieldDataField="Active"HeaderText="Active"SortExpression="Active"/><asp:BoundFieldDataField="ProjMgr"HeaderText="Project Manager"SortExpression="ProjMgr"/><asp:BoundFieldDataField="AppointTarget"HeaderText="Appointment Target"SortExpression="AppointTarget"/><asp:BoundFieldDataField="ConnectionString"HeaderText="Connection String"SortExpression="ConnectionString"/></Columns><RowStyleBackColor="#F7F6F3"ForeColor="#333333"/><EditRowStyleBackColor="#999999"/><SelectedRowStyleBackColor="#E2DED6"Font-Bold="True"ForeColor="#333333"/><PagerStyleBackColor="#284775"ForeColor="White"HorizontalAlign="Center"/><HeaderStyleBackColor="#5D7B9D"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"ForeColor="#284775"/></asp:GridView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:logiCRMConnectionString1 %>"ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>"SelectCommand="SELECT [ProjectURN], [ProjName], [ProjDesc], [DBName], [TotalHD], [Active], [ProjMgr], [AppointTarget], [TMDocPath], [ExtUserName], [ExtPassword], [ConnectionString], [DateCreated], [CreatedBy], [DateAmended], [AmendedBy], [Deleted] FROM [Projects]"></asp:SqlDataSource><br/><asp:LabelID="lblMessage"runat="server"></asp:Label></div></form>

</

body>

</

html>

Notice in the code behind, the key part is where the connection string is passed to the profile in web.config... Profile.ConnStr = ConStr

This next page is able to connect to the connection string from the one held in the profile as it is added into the code behind. You should note that I have allowed sorting and paging, so the amount of code I'm showing is quite lengthy, but I've highlighted in bold text the appropriate bits:

<%

@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Test.aspx.vb"Inherits="Test" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Test connect without a DataSource control</title>

</

head>

<

body><formid="form1"runat="server"><asp:ScriptManagerID="ScriptManager1"runat="server"/><div><fieldsetid="Fieldset1"style="width: 97%;"><legendclass="legendnohelp"><span>Company List</span></legend><asp:gridviewid="GridView1"AllowSorting="true"AllowPaging="true"autogeneratecolumns="false"runat="server"PageSize="20"Width="100%"DataKeyNames="CoID"EmptyDataText="There are no Companies to display"OnPageIndexChanging="gridView_PageIndexChanging"OnSorting="gridView_Sorting"><Columns><asp:BoundFieldReadOnly="True"DataField="CoID"InsertVisible="False"Visible="False"SortExpression="CoID"HeaderText="CoID"></asp:BoundField><asp:HyperLinkFieldHeaderText="Company Name"SortExpression="CoName"Text="Detail"DataTextField="CoName"DataNavigateUrlFormatString="company.aspx?coid={0}"DataNavigateUrlFields="CoID"><ItemStyleWidth="35%"></ItemStyle></asp:HyperLinkField><asp:BoundFieldDataField="Town"SortExpression="Town"HeaderText="Town"><ItemStyleWidth="20%"></ItemStyle></asp:BoundField><asp:BoundFieldDataField="Postcode"SortExpression="Postcode"HeaderText="Postcode"><ItemStyleWidth="15%"></ItemStyle></asp:BoundField><asp:BoundFieldDataField="Telephone"SortExpression="Telephone"HeaderText="Telephone"><ItemStyleWidth="15%"Wrap="False"></ItemStyle></asp:BoundField><asp:BoundFieldDataField="Status"HeaderText="Status"SortExpression="Status"><ItemStyleWidth="15%"/></asp:BoundField></Columns></asp:gridview><asp:LabelID="lblMessage"runat="server"Width="202px"/></fieldset></div></form>

</

body>

</

html>

Imports

System.Data

Imports

System.Data.Sqlclient

Partial

Class TestInherits System.Web.UI.PageProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

PopulateGridView()

EndSubPrivateSub PopulateGridView()Dim connectionStringAsString = DbConnectionString()Dim SQLConnectionAs SqlConnection =New SqlConnection(connectionString)

' this is just a stored procedure to recover the records to populate the gridview...

Dim sqlQueryAsString ="GetCompanies"Dim sqlCommandAs SqlCommand =New SqlCommand(sqlQuery, SQLConnection)Dim coDataAdapterAs SqlDataAdapter =New SqlDataAdapter(sqlCommand)Dim coDataTableAs DataTable =New DataTable("Companies")

coDataAdapter.Fill(coDataTable)

Dim dataTableRowCountAsInteger = coDataTable.Rows.CountIf dataTableRowCount > 0Then

GridView1.DataSource = coDataTable

GridView1.DataBind()

EndIfEndSubPrivateFunction DbConnectionString()AsStringDim DbPathAsString ="Data Source=" & Profile.ConnStr

ReturnString.Format(DbPath)

EndFunction

ProtectedFunction SortDataTable(ByVal dataTableAs DataTable,ByVal isPageIndexChangingAsBoolean)As DataViewIfNot dataTableIsNothingThenDim dataViewAs DataView =New DataView(dataTable)If GridViewSortExpression <>String.EmptyThenIf isPageIndexChangingThen

dataView.Sort =

String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection)Else

dataView.Sort =

String.Format("{0} {1}", GridViewSortExpression, GetSortDirection())EndIfEndIfReturn dataViewElseReturnNew DataView()EndIfEndFunctionProtectedSub gridView_Sorting(ByVal senderAsObject,ByVal eAs GridViewSortEventArgs)

GridViewSortExpression = e.SortExpression

Dim pageIndexAsInteger = GridView1.PageIndex

GridView1.DataSource = SortDataTable(GridView1.DataSource,

False)

GridView1.DataBind()

GridView1.PageIndex = pageIndex

EndSubProtectedSub gridView_PageIndexChanging(ByVal senderAsObject,ByVal eAs GridViewPageEventArgs)

GridView1.DataSource = SortDataTable(GridView1.DataSource,

True)

GridView1.PageIndex = e.NewPageIndex

GridView1.DataBind()

EndSubPrivateFunction GetSortDirection()AsStringSelectCase GridViewSortDirectionCase"ASC"

GridViewSortDirection =

"DESC"' breakCase"DESC"

GridViewSortDirection =

"ASC"' breakEndSelectReturn GridViewSortDirectionEndFunctionPrivateProperty GridViewSortBLOCKED EXPRESSIONAsStringGetReturn IIf(ViewState("SortExpression") =Nothing,String.Empty, ViewState("SortExpression"))EndGetSet(ByVal valueAsString)

ViewState(

"SortExpression") = valueEndSetEndPropertyPrivateProperty GridViewSortDirection()AsStringGetReturn IIf(ViewState("SortDirection") =Nothing,"ASC", ViewState("SortDirection"))EndGetSet(ByVal valueAsString)

ViewState(

"SortDirection") = valueEndSetEndProperty

End

Class

And that is all there is to it! I hope this helps a few people.

Cheers,

Paul