Connection string for LINQ to SQL

In my article about connecting to external databases with the help of LINQ to SQL, I already mentioned how to parametrize which database to use, because you’ll likely use different databases for development, test and live environments.

I wrote: In real implementations, I usually store DB server and DB name in AX database, create a connection string from them (with the help of a connection string builder) and pass the connection string to data context’s constructor.

Let’s look at what it means in practice.

First of all, let me remind you the .NET class:

public class Data
{
    public void Import()
    {
        TestDBDataContext db = new TestDBDataContext("); 
        …
    }
}

Here I create the data context without any parameters, therefore it connects to the database that I used when designing the library in Visual Studio. It’s also possible to change it in the designer.

A common way for specifying connection details is using connection strings like this:

Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True

This approach is supported even by LINQ to SQL and you could simply pass a connection string to the constructor of a data context class:

new TestDBDataContext("Data Source=.;Initial Catalog=TestDB;Integrated Security=True");

I could construct such a string by myself, but I’m not interested in details of the syntax, in concatenating strings and so on. A connection builder will deal with it for me. Therefore I’ll add a field to the Data class holding the connection builder instance:

SqlConnectionStringBuilder connStringBuilder;

I also have to add

using System.Data.SqlClient;

on the top of the file, because that’s the namespace containing SqlConnectionStringBuilder class.

I have to decide what I want to include in my connection string and usually a database server name and a database name are enough, therefore these two values will be my parameters. I add a constructor to the Data class that accepts these parameters, creates a connection string builder and set its properties:

public Data(string dbServer, string dbName)
{
    //TODO: parameter validation
 
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dbServer;
    connStringBuilder.InitialCatalog = dbName;
    connStringBuilder.IntegratedSecurity = true;
}

Now we can get the connection string at any time from ConnectionString property of the builder, therefore let’s pass it to the data context:

new TestDBDataContext(connStringBuilder.ConnectionString);

Put all together, the class now looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
 
namespace ExtSystem
{
    public class Data
    {
        SqlConnectionStringBuilder connStringBuilder;
 
        public Data(string dbServer, string dbName)
        {
            //TODO: parameter validation
 
            connStringBuilder = new SqlConnectionStringBuilder();
            connStringBuilder.DataSource = dbServer;
            connStringBuilder.InitialCatalog = dbName;
            connStringBuilder.IntegratedSecurity = true;
        }
 
        public void Import()
        {
            TestDBDataContext db = new TestDBDataContext(connStringBuilder.ConnectionString);}
    }
}

Rebuild the solution and open Dynamics AX (restart the client if it was running).

Our test job in AX contained the following code:

new ExtSystem.Data().Import();

It doesn’t compile anymore, because we have to specify the arguments (database server + database name). The following implementation connects to exactly the same database as before.

new ExtSystem.Data('.', 'TestDB').Import();

I can easily prove that the parameters are utilized by specifying an invalid database name such as WrongDB. I’ll get an error saying “Cannot open database “WrongDB” requested by the login”.

Now the only remaining thing is to create fields in AX database for the parameters and use them when creating the Data class.

Also note that if I wanted, I could select the data from AX database directly in the .NET class (by creating a proxy to the table etc.). But I think that it would be an unnecessary coupling between the library and the table. Using parameters is more flexible and it also make testing easier.

6 Comments

  1. Great post!, I was developing a process with ADO and I didn’t like it, I changed it with linq to sql and now the code it is much more clear

  2. Hi martin,
    I got this error when I try to use this class.

    ExtSystem.Data is not a class?
    I have already restarted the client.

    Here is part of my Data class.
    SqlConnectionStringBuilder connStringBuilder;

    public Data(string dbServer, string dbName)
    {
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dbServer;
    connStringBuilder.InitialCatalog = dbName;
    connStringBuilder.IntegratedSecurity = true;
    }

    public void Import()
    {

    AttendSync ax = new AttendSync();
    Linq2SqlDataContext db = new Linq2SqlDataContext(connStringBuilder.ConnectionString);

    • Do you also use namespace ExtSystem? If not, change the namespace to match yours. Also make sure that the class is public.

    • Then verify that the assembly has been deployed to the tier running your code and that it’s been built for the right processor architecture and .NET version.

      • It worked when I manually add the dll to the client and add it to AOT references.
        Now I am confused why it worked the first day and the next day It did not found the class.

        (don’t get mad at me. It was my first time working with C# Class Libraries.)

Comments are closed.