The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

slindee (28) [Avatar] Offline
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
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
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.

slindee (28) [Avatar] Offline
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)
End Sub

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

From the calling UI application, with the connection string, I would like to do something like this:
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
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
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
Re: Passing LINQ a Dynamic Connection String
i am a begginer in
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
how should i pass dis into

public LinqChatDataContext() :

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

for connection into database