How to select a connection string dynamically from the config file from a session variable?

Question

What am trying to create is a single login screen that connects the users to different databases depending on a certain code that each user has.

i have created some keys in my config file which corresponds to the user codes as follows

<appSettings>
<add key="ch001" value="h001"/>
<add key="ch002" value="h002"/>
</appSettings>

Then i have created connections string as follows

<connectionStrings>
<add name="Dbconn_h001" connectionString="XXX" providerName="XXX"/>
<add name="Dbconn_h002" connectionString="XXX" providerName="XXX"/>
</connectionStrings>

Then i have created a class to get the key value corresponding to the connection string as follows

Imports System.Web.Compilation

Imports System.CodeDom Imports System.ComponentModel

Public Class ConnStringExpressionBuilder Inherits ExpressionBuilder

Public Shared Function GetEvalData(ByVal expression As String, ByVal target As Type, ByVal entry As String) As Object

    Return System.Configuration.ConfigurationManager.ConnectionStrings("Dbconn_" & System.Configuration.ConfigurationManager.AppSettings(HttpContext.Current.Session("code").ToString))
End Function
Public Overrides Function GetCodeExpression(ByVal entry As BoundPropertyEntry, ByVal parsedData As Object, ByVal context As ExpressionBuilderContext) As CodeExpression
    Dim type1 As Type = entry.DeclaringType
    Dim descriptor1 As PropertyDescriptor = TypeDescriptor.GetProperties(type1)(entry.PropertyInfo.Name)
    Dim expressionArray1(2) As CodeExpression
    expressionArray1(0) = New CodePrimitiveExpression(entry.Expression.Trim())
    expressionArray1(1) = New CodeTypeOfExpression(type1)
    expressionArray1(2) = New CodePrimitiveExpression(entry.Name)
    Return New CodeCastExpression(descriptor1.PropertyType, New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType()), "GetEvalData", expressionArray1))
End Function

End Class

The issue is

System.Configuration.ConfigurationManager.AppSettings(HttpContext.Current.Session("code").ToString)

returns a null reference

Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — VDWWD, Jan 15 '18 at 07:52
Your `Session("code")` probably is NULL or not `ch001` or `ch002`. Debug your code and check the value of the Session. — VDWWD, Jan 15 '18 at 07:55

score 0 · Answer 1 · answered Jan 15 '18 at 13:26

0

using(SqlConnection conn = new SqlConnection()) 
{
 var connString=ConfigurationManager.AppSetting["keyname"];
 conn.ConnectionString = connString;
// using the code here...
}

and in config file save like <add key="ch001" value="YourConnectionString" />

answered Jan 15 '18 at 13:26

Uttam Suthar

71
6

keyname will come from your session – Uttam Suthar Jan 15 '18 at 13:27
if the SqlDataSource control is embedded inside the EditTemplate then it will not be accessible from code behind. – peter muthike Jan 15 '18 at 17:34

score 0 · Answer 2 · answered Jan 23 '18 at 11:33

After a long hustle i figured it out i created this expression builder class

Public Class ConnStringExpressionBuilder
Inherits ExpressionBuilder

Public Shared Function GetEvalData(ByVal expression As String, ByVal target As Type, ByVal entry As String) As Object

    Return System.Configuration.ConfigurationManager.ConnectionStrings(System.Configuration.ConfigurationManager.AppSettings(HttpContext.Current.Session("code").ToString())).ToString()

End Function
Public Overrides Function GetCodeExpression(ByVal entry As BoundPropertyEntry, ByVal parsedData As Object, ByVal context As ExpressionBuilderContext) As CodeExpression
    Dim type1 As Type = entry.DeclaringType
    Dim descriptor1 As PropertyDescriptor = TypeDescriptor.GetProperties(type1)(entry.PropertyInfo.Name)
    Dim expressionArray1(2) As CodeExpression
    expressionArray1(0) = New CodePrimitiveExpression(entry.Expression.Trim())
    expressionArray1(1) = New CodeTypeOfExpression(type1)
    expressionArray1(2) = New CodePrimitiveExpression(entry.Name)
    Return New CodeCastExpression(descriptor1.PropertyType, New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType()), "GetEvalData", expressionArray1))
End Function

End Class

Then in my markup i call the class like this

<asp:SqlDataSource ID="Ds" runat="server" ProviderName="Mysql.Data.MysqlClient"
    ConnectionString="<%$ ConnStringExpression:Dbconn %>" SelectCommand="XXX"></asp:SqlDataSource>

Then from the code behind

Using conn = getConnect(System.Configuration.ConfigurationManager.AppSettings(Session("code").ToString()))
        conn.Open()

        Try
           //logic
        Catch ex As Exception

        End Try
        conn.Close()
    End Using

How to select a connection string dynamically from the config file from a session variable?

2 Answers2