none
Generic function to retrieve Entity from different DB RRS feed

  • Question

  • I have several web-app (eg, WA1, WA2, ...; unknown numbers in design time), each have its own identical table (eg, tbSYS_m_CodeSetup in DB1, tbSYS_m_CodeSetup in DB2, ...) in its respective DB.

    I found I have to write this in BLL level:

    DB1_DBEntities d = new DB1_DBEntities();
    brAccess.test(d, brAccess.getKey(d.tbSYS_m_CodeSetup));
    DB2_DBEntities e = new DB2_DBEntities();
    brAccess.test(e, brAccess.getKey(e.tbSYS_m_CodeSetup));

    and this in DAL level (brAccess):

    public string getKey(ObjectSet<DB1.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
            public string getKey(ObjectSet<DB2.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
            public void test(ObjectContext context, string entitySetName)
            {
                IEnumerable<KeyValuePair<string, object>> entityKeyValues =
                    new KeyValuePair<string, object>[] { new KeyValuePair<string, object>("PK_CodeUNID", 64.01M) };
    
                EntityKey key = new EntityKey(entitySetName, entityKeyValues);
                var o = context.GetObjectByKey(key);
                System.Reflection.PropertyInfo[] PropInfo = o.GetType().GetProperties();
    
                string s = "";
                for (int j = PropInfo.GetLowerBound(0); j <= PropInfo.GetUpperBound(0); j++)
                {
                    if (PropInfo[j].Name.ToString() == "CodeValue1")
                    {
                        s= (string)PropInfo[j].GetValue(o, null);
                    }
                } 
            }

    Question:

    1) Any way to write a generic method, without repeating this class in BLL level

    public string getKey(ObjectSet<DB1.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
    though i know i can simple construct it in a higher level with this, thru' skipping that getKey():
    daCLM_MainDBEntities d = new daCLM_MainDBEntities();
                test2(d, d.tbSYS_m_CodeSetup.Context.DefaultContainerName + "." + d.tbSYS_m_CodeSetup.EntitySet.Name);
                //brAccess.test(d, d.tbSYS_m_CodeSetup.Context.DefaultContainerName + "." + d.tbSYS_m_CodeSetup.EntitySet.Name);
                daSYS_MainDBEntities e = new daSYS_MainDBEntities();
                test2(e, e.tbSYS_m_CodeSetup.Context.DefaultContainerName + "." + e.tbSYS_m_CodeSetup.EntitySet.Name);
    

    2) this method i think only allow to get by primary key, how to get the value by other column?

    var o = context.GetObjectByKey(key);

    3) what if I want to get the whole List of entity <tbSYS_m_CodeSetup>, instead of individual value for a particular column in Q2?


    gan

    Thursday, April 5, 2012 5:43 AM

Answers

  • Hi,

    1) Unclear. As the table structure is the same accross all databases you should be able to use the same classes by just passing the appropriate connection string when creating the context (you show some code but I'm not sure which problem it solves). Or you want to expose all those databases through a single context rather than each database separately through its dedicated context ? It seems rather than each web app deals only wiht a single database ?

    2) See http://msdn.microsoft.com/en-us/library/bb397678.aspx for ways to express queries (not the where clause or method)

    3) Then you just enumerate the list without using the where clause or method


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Thursday, April 5, 2012 12:16 PM

All replies

  • I have several web-app (eg, WA1, WA2, ...; unknown numbers in design time), each have its own identical table (eg, tbSYS_m_CodeSetup in DB1, tbSYS_m_CodeSetup in DB2, ...) in its respective DB.

    I found I have to write this in BLL level:

    DB1_DBEntities d = new DB1_DBEntities();
    brAccess.test(d, brAccess.getKey(d.tbSYS_m_CodeSetup));
    DB2_DBEntities e = new DB2_DBEntities();
    brAccess.test(e, brAccess.getKey(e.tbSYS_m_CodeSetup));

    and this in DAL level (brAccess):

    public string getKey(ObjectSet<DB1.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
            public string getKey(ObjectSet<DB2.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
            public void test(ObjectContext context, string entitySetName)
            {
                IEnumerable<KeyValuePair<string, object>> entityKeyValues =
                    new KeyValuePair<string, object>[] { new KeyValuePair<string, object>("PK_CodeUNID", 64.01M) };
    
                EntityKey key = new EntityKey(entitySetName, entityKeyValues);
                var o = context.GetObjectByKey(key);
                System.Reflection.PropertyInfo[] PropInfo = o.GetType().GetProperties();
    
                string s = "";
                for (int j = PropInfo.GetLowerBound(0); j <= PropInfo.GetUpperBound(0); j++)
                {
                    if (PropInfo[j].Name.ToString() == "CodeValue1")
                    {
                        s= (string)PropInfo[j].GetValue(o, null);
                    }
                } 
            }

    Question:

    1) Any way to write a generic method, without repeating this class in BLL level

    public string getKey(ObjectSet<DB1.tbSYS_m_CodeSetup> objectSet)
            {
                var entitySetName = objectSet.Context.DefaultContainerName + "." + objectSet.EntitySet.Name;
                return entitySetName;
            }
    
    though i know i can simple construct it in a higher level with this, thru' skipping that getKey():
    DB1_MainDBEntities d = new DB1_MainDBEntities();
    test2(d, d.tbSYS_m_CodeSetup.Context.DefaultContainerName + "." + d.tbSYS_m_CodeSetup.EntitySet.Name);
        
    DB2_MainDBEntities e = new DB2_MainDBEntities();
    test2(e, e.tbSYS_m_CodeSetup.Context.DefaultContainerName + "." + e.tbSYS_m_CodeSetup.EntitySet.Name);
    

    2) this method i think only allow to get by primary key ("PK_CodeUNID"), how to get the value by other column?

    var o = context.GetObjectByKey(key);

    3) what if I want to get the whole List of entity <tbSYS_m_CodeSetup>, instead of individual value for a particular column in Q2?

    this is at most I've tried:

    foreach (var name in context.ExecuteStoreQuery<DB1.tbSYS_t_AccessControl>
                                                                                    ("Select * from tbSYS_t_AccessControl where FK_ModuleUNID = {0}", 64.01))
                    {
                        System.Reflection.PropertyInfo[] PropInfo = name.GetType().GetProperties();
                        string s = "";
                        for (int j = PropInfo.GetLowerBound(0); j <= PropInfo.GetUpperBound(0); j++)
                        {
                            if (PropInfo[j].Name.ToString() == "CodeValue1")
                            {
                                s = (string)PropInfo[j].GetValue(name, null);
                            }
                        }
                    }

    but it's still strong-typed (DB1.tbSYS_t_AccessControl, DB2.tbSYS_t_AccessControl, ...)


    gan

    Thursday, April 5, 2012 7:18 AM
  • Hi,

    1) Unclear. As the table structure is the same accross all databases you should be able to use the same classes by just passing the appropriate connection string when creating the context (you show some code but I'm not sure which problem it solves). Or you want to expose all those databases through a single context rather than each database separately through its dedicated context ? It seems rather than each web app deals only wiht a single database ?

    2) See http://msdn.microsoft.com/en-us/library/bb397678.aspx for ways to express queries (not the where clause or method)

    3) Then you just enumerate the list without using the where clause or method


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Thursday, April 5, 2012 12:16 PM
  • The first question, As @Patrice said, you can write the connecting string in configuration file and pass the appropriate one to your app.

    Fighting like Allen Iverson. Neve give up!

    Monday, April 9, 2012 2:43 AM