CODEWITHSUNDEEP
c#linq-to-sql
vwdewaal
vwdewaal

Reputation: 1005

c# Linq to Sql dynamic Data Context assignment

`Hi,

Can somebody please give me a pointer on this? I have 8 servers each with 8 databases which look identical exept server/database name. We are talking thousands of tables.

I create my data contexts with sqlmetal.exe

After creating my data contexts, I import them into the application and then I run comparison scripts over the databases to compare results.

My problem is dynamically switching between data contexts.

Datacontext.DAL.DUK1 duk1sdi = new     Datacontext.DAL.DUK1(connectionString);
Datacontext.DAL.DUK3 duk3sdi = new     Datacontext.DAL.DUK3(connectionString);

string fromOne = runQuery(duk1sdi);
string fromThree = runQuery(duk3sdi);

public static string runQuery(DataContext duk)

{
var query =
from result in duk.TableA
select result.Total;

string returnString = query;
return returnString;
}

I have no problem with the query running when the duk is predefined, however how do I define and pass the datacontext to the function?

The error I get is:

Error 1 'System.Data.Linq.DataContext' does not contain a definition for 'TableA' and no extension method 'TableA' accepting a first argument of type 'System.Data.Linq.DataContext' could be found (are you missing a using directive or an assembly reference?)

Upvotes: 0

Views: 2518

Answers (4)

vwdewaal
vwdewaal

Reputation: 1005

Thanks, guys, I think I found the simplist solution for me based a bit of both your answers and by RTFM (Programming Microsoft Linq in Microsoft .NET Framework 4 by Paulo Pialorsi and Marco Russo)

In this way I don't have to use the large DBML files. It is a shame because I'm going to have to create hundreds of tables in this way, but I can now switch between connection strings on the fly.

First I create the table structure. (outside the program code block)

[Table(Name = "TableA")]
public class TableA
{
[Column] public int result;
}

Then I define the table for use:

Table<TableA> TableA = dc.GetTable<TableA>();

And then I can query from it:

var query =
from result in TableA
select TableA.result;

Upvotes: 0

Jim Wooley
Jim Wooley

Reputation: 10408

If your schema is identical between databases, why script the dbml for all of them? Just create one context with it's associated classes and dynamically switch out the connection string when instantiating the context.

var duk1sdi = new     Datacontext.DAL.DUK1(connectionString1);
var duk3sdi = new     Datacontext.DAL.DUK1(connectionString2);

Upvotes: 0

Maarten
Maarten

Reputation: 22955

You can use interfaces. Check this answer, but be sure to script the interfaces using a .tt file with the amount of tables you have.

Edit:

If you have generated contexts which you want to use interchangeably in a reusable method, you have the problem that the generated TableA classes are not reusable, since they are different types (even though the names may match, but that doesn't make them equal). Therefore you need to abstract the actual types, and one way to do this, is to use interfaces. You build your reusable method around an interface which abstracts the specific context-type and table-type. The downside is that you have to implement the interfaces on the generated contexts and tabletypes. This though is something you can solve using a .tt script.

Pseudo code:

// Define interface for table
public interface ITableA {
    // ... properties
}

// Define interface for context
public interface IMyContext {
    IQueryable<ITableA> TableA { get; }
}

// Extend TableA from DUK1
public partial class TableA: ITableA {  
}

// Extend DUK1
public partial class Datacontext.DAL.DUK1: IMyContext {
    IQueryable<ITableA> IMyContext.TableA { 
        get { return TableA; }
    }
}

// Same for DUK3 and TableA FROM DUK3


// Finally, your code
Datacontext.DAL.DUK1 duk1sdi = new     Datacontext.DAL.DUK1(connectionString);
Datacontext.DAL.DUK3 duk3sdi = new     Datacontext.DAL.DUK3(connectionString);

string fromOne = runQuery(duk1sdi);
string fromThree = runQuery(duk3sdi);

public static string runQuery(IMyContext duk) { 
    // Note: method accepts interface, not specific context type

    var query = from result in duk.TableA
                select result.Total;

    string returnString = query;
    return returnString;
}

Upvotes: 1

Grant Thomas
Grant Thomas

Reputation: 45068

You could use the GetTable<T> method, where T is the type of the table, e.g. TableA.

public static string runQuery(DataContext duk) {
  var table = duk.GetTable<TableA>();
  var query = from result in table select result.Total;
  ...
}

However, all types of TableA will need to be the same type, strictly (I'm pretty sure).

Otherwise you would need to literally branch the logic for the handling of each context. Since you can extend your DataContext instances (in general, maybe not in your specific case) then you could have them share an interface that exposes a collection property of TableA, but you would need a higher level context wrapper to pass around then - unless you pass around the collection by altering the method signature.

Upvotes: 1

Related Questions