开发者

Can I use the same DBML to access different tables?

I ha开发者_开发知识库ve many tables with identical schemas but different names. Can I create a single representative DBML/DataContext and re-use it for all the tables, or do I have to drag every table into the dbml designer?


It is possible to do this without dragging every table from the designer, BUT it's a PITA because you have to manually define all the table mappings for each subsequent object (which is what the DBML designer is doing for you, in essence). Here's an example:

Define a pair of tables:

create table Person (
    Id int identity not null,
    Name varchar(50),
    Email varchar(50),
    constraint PK_Person primary key (Id)
)

create table OtherPerson (
    Id int identity not null,
    Name varchar(50),
    Email varchar(50),
    constraint PK_OtherPerson primary key (Id)
)

Add some data to them:

Person
Id  Name    Email
1   Fred    fred@yabbadabba.com
2   Wilma   wilma@yabbadabba.com

OtherPerson
Id  Name    Email
1   Barney  barney@rubble.com
2   Betty   betty@rubble.com

Create a DBML file called TestsDb, and drag the Person table onto the surface. Now create the following partial class:

partial class TestsDbDataContext
{
    public System.Data.Linq.Table<OtherPerson> OtherPersons
    {
        get { return this.GetTable<OtherPerson>(); }
    }
}

For convenience, I used ReSharper to extract an interface from Person, like so:

public interface IPerson
{
    int Id { get; set; }
    string Name { get; set; }
    string Email { get; set; }
}

And then I created a new class called OtherPerson which implements that interface:

[Table(Name = "dbo.OtherPerson")]
public class OtherPerson : IPerson
{
    [Column(AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY",
            IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column(DbType = "VarChar(50)")]
    public string Name { get; set; }

    [Column(DbType = "VarChar(50)")]
    public string Email { get; set; }
}

And test it all in a main function like so:

class Program
{
    static void Main()
    {
        var dc = new TestsDbDataContext();

        var persons = dc.Persons.ToList().OfType<IPerson>();
        writePeople(persons);

        var otherPersons = dc.OtherPersons.ToList().OfType<IPerson>();
        writePeople(otherPersons);
    }

    private static void writePeople(IEnumerable<IPerson> people)
    {
        foreach (var person in people)
        {
            Console.WriteLine("{0}: {1} ({2})", person.GetType().Name,
                              person.Name, person.Id);
        }
    }
}

And this is the end result:

Person: Fred (1)
Person: Wilma (2)
OtherPerson: Barney (1)
OtherPerson: Betty (2)

As you can see, it's possible, but is it really worth it? I suppose it might be worth it if you had some sort of automatic code generation going on (as opposed to manual code generation triggered by editing the DBML file), but otherwise, probably not.

You could use the Entity framework though. I'm not too familiar with it, but I think it's supposed to handle this sort of thing quite nicely, and EF4 is out now too.


I don't think this is possible in the sense of "true" tables.

It can be accomplished through procedures.

But generally every table must represent a different type of information, hence a different class is needed to represent this mapping.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜