none
How to modify LinqToSql project for use several DBs

    Question

  • Hello. I have a little problem, for example i have a DB with 1 table that has Name and Password. I'm connecting with it using LinqToSql and all is nice. So i have another DB, it has the same table, but also a field Country. I don't want to create another LinqToSql project, so i'd like to do something like this

    SecondDbTable : FirstDbTable
    {
       private string _country;
    
    		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_country", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    		public string Country
    		{
    			get
    			{
    				return this._country;
    			}
    			set
    			{
    				if ((this._country != value))
    				{
    					this.OnCheckIDChanging(value);
    					this.SendPropertyChanging();
    					this._country = value;
    					this.SendPropertyChanged("Country");
    					this.OnCheckIDChanged();
    				}
    			}
    		}
    }
    
    
    SecondDbDataContext : FirstDbDataContext
    {
               public override Table<FirstDbTable> FirstDbTables
            {
                get
                {
                    return GetTable<SecondDbTable>();
                }
            }
    }

    so i'd like to use come covariance and replace FirstDbTable by SecondDbTable and do it virtual, for i can use an Abstract Factory and use only interface|base abstact class object for all Dbs.

    It's clear that this code will never works, but is there some alternatives? I have at least 5 DBs with a very little differences.



    Wednesday, July 31, 2013 2:07 PM

Answers

  • Hello PsilonRus,

    Thank you for posting in MSDN Forum.

    From your description, I notice the issue you are experiencing is that you want to use one LinqToSql project to map two tables or more that are similar and tables are not in the same db.

    If I have misunderstood anything, please feel free to let me know.

    As you put forward an idea with the code provide by you, I followed your ideas to do an experiment.Wo,It works.

    Following is my whole step:

    1.Create two DB:

    DB1,DB2:

    DB1 is like:

    CREATE TABLE [dbo].[LoginTable] ( [Name] NVARCHAR (50) NOT NULL, [Password] NVARCHAR (50) NOT NULL );

    DB2 is like:

    CREATE TABLE [dbo].[LoginTable] (
        [Name]     NVARCHAR (50) NOT NULL,
        [Password] NVARCHAR (50) NOT NULL,
        [Country]  NVARCHAR (50) NOT NULL
    );

    They both have the table named “LoginTable” and the table in db2 have a filed “Country”.

    2.Create The LinqToSql Project named DB1LinqToSql.dbml.

    3.Bulid Relationship

    Now,we can do some operations on table “LoginTable” in DB1 like :

                DB1LinqToSqlDataContext db1 = new DB1LinqToSqlDataContext();
                FirstTable firstTable = new FirstTable();
                firstTable.Name = "Jjq";
                firstTable.Password = "123456";
                db1.FirstTables.InsertOnSubmit(firstTable);

    after running the app,it inserts a record into table “LoginTable” in DB1:

    Name Password

    Jjq       123456

    4.But this is not what we want.

    So I created class name “SecondTable” which inherits “FirstTable”:

    [global::System.Data.Linq.Mapping.TableAttribute(Name = "dbo.LoginTable")]
        public partial class SecondTable : FirstTable, INotifyPropertyChanging, INotifyPropertyChanged
        {
            private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
            private string _Country;
            #region Extensibility Method Definitions
            partial void OnCountryChanging(string value);
            partial void OnCountryChanged();
            #endregion
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Country", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]
            public string Country
            {
                get
                {
                    return this._Country;
                }
                set
                {
                    if ((this._Country != value))
                    {
                        this.OnCountryChanging(value);
                        this.SendPropertyChanging();
                        this._Country = value;
                        this.SendPropertyChanged("Country");
                        this.OnCountryChanged();
                    }
                }
            }
        }

    The calss has one property “Country”.

    And I add a method in class “DB1LinqToSqlDataContext””:

    public System.Data.Linq.Table<SecondTable> SecondTables
            {
                get
                {
                    return this.GetTable<SecondTable>();
                }
            }

    Now I am doing the operating to insert a record into “LoginTable” in DB2:

    DB1LinqToSqlDataContext db1 = new DB1LinqToSqlDataContext("Data Source=(localdb)\\v11.0;Initial Catalog=DB2;Integrated Security=True");
                SecondTable secondTable = new SecondTable();
                secondTable.Name = "Jjq";
                secondTable.Password = "123456";
                secondTable.Country = "China";
                db1.SecondTables.InsertOnSubmit(secondTable);

    Attention,I used anther constructed function that needs a parameter because we need to connect to DB2.

    Before it runs:

    Name Password County

    null     null           null

    After it runs:

    Name Password County

    Jiq      123456     China




    So it works and I do not add another LinqToSql Project.

    There is a complete sample:

    http://sdrv.ms/13p5lE7.

    You can download it and see the whole code.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Thursday, August 01, 2013 10:55 AM

All replies

  • Hi PsilonRus,

      Welcome to MSDN Forum Support.

      You are more likely to get more efficient responses to Linq to SQL issues at http://social.msdn.microsoft.com/Forums/en-US/home?forum=linqtosql where you can contact Linq to SQL experts.

      


    Jason Wang
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 01, 2013 2:48 AM
  • Hello PsilonRus,

    Thank you for posting in MSDN Forum.

    From your description, I notice the issue you are experiencing is that you want to use one LinqToSql project to map two tables or more that are similar and tables are not in the same db.

    If I have misunderstood anything, please feel free to let me know.

    As you put forward an idea with the code provide by you, I followed your ideas to do an experiment.Wo,It works.

    Following is my whole step:

    1.Create two DB:

    DB1,DB2:

    DB1 is like:

    CREATE TABLE [dbo].[LoginTable] ( [Name] NVARCHAR (50) NOT NULL, [Password] NVARCHAR (50) NOT NULL );

    DB2 is like:

    CREATE TABLE [dbo].[LoginTable] (
        [Name]     NVARCHAR (50) NOT NULL,
        [Password] NVARCHAR (50) NOT NULL,
        [Country]  NVARCHAR (50) NOT NULL
    );

    They both have the table named “LoginTable” and the table in db2 have a filed “Country”.

    2.Create The LinqToSql Project named DB1LinqToSql.dbml.

    3.Bulid Relationship

    Now,we can do some operations on table “LoginTable” in DB1 like :

                DB1LinqToSqlDataContext db1 = new DB1LinqToSqlDataContext();
                FirstTable firstTable = new FirstTable();
                firstTable.Name = "Jjq";
                firstTable.Password = "123456";
                db1.FirstTables.InsertOnSubmit(firstTable);

    after running the app,it inserts a record into table “LoginTable” in DB1:

    Name Password

    Jjq       123456

    4.But this is not what we want.

    So I created class name “SecondTable” which inherits “FirstTable”:

    [global::System.Data.Linq.Mapping.TableAttribute(Name = "dbo.LoginTable")]
        public partial class SecondTable : FirstTable, INotifyPropertyChanging, INotifyPropertyChanged
        {
            private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
            private string _Country;
            #region Extensibility Method Definitions
            partial void OnCountryChanging(string value);
            partial void OnCountryChanged();
            #endregion
            [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Country", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]
            public string Country
            {
                get
                {
                    return this._Country;
                }
                set
                {
                    if ((this._Country != value))
                    {
                        this.OnCountryChanging(value);
                        this.SendPropertyChanging();
                        this._Country = value;
                        this.SendPropertyChanged("Country");
                        this.OnCountryChanged();
                    }
                }
            }
        }

    The calss has one property “Country”.

    And I add a method in class “DB1LinqToSqlDataContext””:

    public System.Data.Linq.Table<SecondTable> SecondTables
            {
                get
                {
                    return this.GetTable<SecondTable>();
                }
            }

    Now I am doing the operating to insert a record into “LoginTable” in DB2:

    DB1LinqToSqlDataContext db1 = new DB1LinqToSqlDataContext("Data Source=(localdb)\\v11.0;Initial Catalog=DB2;Integrated Security=True");
                SecondTable secondTable = new SecondTable();
                secondTable.Name = "Jjq";
                secondTable.Password = "123456";
                secondTable.Country = "China";
                db1.SecondTables.InsertOnSubmit(secondTable);

    Attention,I used anther constructed function that needs a parameter because we need to connect to DB2.

    Before it runs:

    Name Password County

    null     null           null

    After it runs:

    Name Password County

    Jiq      123456     China




    So it works and I do not add another LinqToSql Project.

    There is a complete sample:

    http://sdrv.ms/13p5lE7.

    You can download it and see the whole code.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    Thursday, August 01, 2013 10:55 AM