c# Linq to Sql dynamic Data Context assignment

Question

`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?)

Grant Thomas · Accepted Answer · 2013-07-22 14:35:58Z

1

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.

edited Jul 22, 2013 at 14:35

answered Jul 22, 2013 at 14:29

Grant Thomas

45.1k10 gold badges88 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:49:13Z

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;
}

Jim Wooley · Accepted Answer · 2013-07-22 17:31:42Z

0

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);

answered Jul 22, 2013 at 17:31

Jim Wooley

10.4k1 gold badge27 silver badges44 bronze badges

Comments

vwdewaal · Accepted Answer · 2013-07-23 09:04:34Z

0

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;

answered Jul 23, 2013 at 9:04

vwdewaal

1,0052 gold badges13 silver badges27 bronze badges

Collectives™ on Stack Overflow

c# Linq to Sql dynamic Data Context assignment

4 Answers 4

Comments

Edit:

Pseudo code:

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Edit:

Pseudo code:

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related