slindee (28) [Avatar] Offline
#1
VS2008, .NetFramework 3.5 SP1:

I have built a LINQ data access layer project. When the LINQ data context was built over an existing SQL2005 database, the connection string for that database was correctly, automatically, placed into the Settings for the LINQ project. It works, but it’s a “static” connection string.

I would like to be able to use this one LINQ data access layer against both test and production databases without having to manually change and recompile the project’s connection string each time. Since this is a data access layer project, the functions within it are called by the User application project. The User application project has the test or production connection string within it’s settings.

Is there a way to pass the User connection string to the LINQ data access layer? I would even consider passing the connection string on each function call to the LINQ project, but my first attempt was unsuccessful, because the LINQ connection string is ReadOnly.

Public Shared Function GetModuleRowsActiveBoot(ByVal strConnectionString As String) As DataTable
Dim dt As New DataTable
Try
Dim dc As New dcSecurityDataContext
Global.SecurityLinq.My.MySettings.Default.SecurityConnectionString = strConnectionString

The last statement above is in error because Global….SecurityConnectionString is ReadOnly.

Anyone tried doing this?
jwooley (123) [Avatar] Offline
#2
Re: Passing LINQ a Dynamic Connection String
I would recommend using the context's overloaded constructor which allows you to pass in the connection string directly. Alternatively, you may want to create a static/shared factory method in the partial class for your context which pulls the connection string from the config file and returns the constructed context.

The issue you are running into is probably an issue with the settings file in a class libray project which retains the value in the attribute in the settings.designer.vb file rather than using the one from a .settings file in the client project.

Jim
slindee (28) [Avatar] Offline
#3
Re: Passing LINQ a Dynamic Connection String
I understand your suggestion, and your words in paragraph two are correct.

Here is the code you refer to inside my Linq data access layer (named AdminLinq):
Partial Public Class dcAdminDataContext
Inherits System.Data.Linq.DataContext

Public Sub New()
MyBase.New(Global.AdminLinq.My.MySettings.Default.AdminConnectionString, mappingSource)
OnCreated()
End Sub

Public Sub New(ByVal connection As String)
MyBase.New(connection, mappingSource)
OnCreated()
End Sub

From the calling UI application, with the connection string, I would like to do something like this:
AdminLinq.dcAdminDataContext(strAdminConnectionString)
However, I get this syntax error:
'dcAdminDataContext' is a type in 'AdminLinq' and cannot be used as a expression.

Or, if I instantiate a new AdminLinq data access layer and try to provide the connection string like thus:
Dim dalAdmin As New AdminLinq.linqAdmin(strAdminConnectionString)
I get a syntax error: "Too many arguments for Public Sub New()"

Were you thinking of something other than the above?
fabrice.marguerie (224) [Avatar] Offline
#4
Re: Passing LINQ a Dynamic Connection String
I think that you want to instanciate a new DataContext, so the statement should be: New AdminLinq.dcAdminDataContext(strAdminConnectionString)
Is there something incorrect here?
slindee (28) [Avatar] Offline
#5
Re: Passing LINQ a Dynamic Connection String
OK, I figured it out:
In my Linq data access layer there is a Public (connection) string variable that is set by the calling UI project:
Public Shared strAdminConnectionString As String

Then, within each call to Linq, instantiate the data context, like this:
Public Shared Function GetAdminActivityRows() As DataTable
Dim dt As New DataTable
Dim dc As New dcAdminDataContext(strAdminConnectionString)
Dim query = From tblAdminActivity In dc.tblAdminActivities _
Order By tblAdminActivity.ActivityID
dt = query.CopyToDataTable()
Return dt
End Function

Note: this approach effectively ignores the ConnectionString setting variable that is automatically set when using the designer to contruct/modify the data context. Nice, no special extra work to remember to do in the future.

Final note: On the Data Context designer, there is a property named Application Settings that I also set to False. However, I think the above trumps that value.
kiransolkar (1) [Avatar] Offline
#6
Re: Passing LINQ a Dynamic Connection String
i am a begginer in Asp.net
as i have got a code from net to create a application using Linq
and d connection into it was Static i have to make it Dynamic
so in web config i have declare
<add name="LinqChatConnectionString" connectionString="Server={0};Database={1};User ID={2};Pwd={3}; Max Pool Size=200"/>

and fetching it into a Class name as FetchConnection
and decleare a method into it
public static string Fetchconnectionstring()
{
string clientConnString = string.Empty;
clientConnString = ConfigurationManager.ConnectionStrings["LinqChatConnectionString"].ConnectionString;
clientConnString = string.Format(clientConnString, ".", "locationmaster", "sa", "su");
return clientConnString;
}
now m calling dis method in Datacontext class
and getting d connection string into a Variable

static string connectionString = FetchConnection.Fetchconnectionstring().ToString();

and m getting connectionstring in above decleared variable
connectionString
how should i pass dis into

public LinqChatDataContext() :

base(global::System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString, mappingSource)
{
OnCreated();
}

for connection into database