none
Create a function that can return different query results RRS feed

  • Question

  • Hi,

    I'm trying to create a function that can return a list of a generic type based on the Table's name. The type is based on the return fields of functions in my SQL DB. I started it but I'm missing something.... How do I declare the function ? I also know that I cannot use several times in the same scope var results..... Any helped will be greatly appreciated !

    CJ

     Here's my function....

    in C#:

    public

     

     

    List<what ?> GetRecords(string tableName)

    {

     

     

     

    DataContext db = new DataContext();

    object x = new object();

     

     

    switch (tableName)

    {

     

     

    case "dbo.TestTable1":

     

     

    var results = db.TestTable_function().ToList();

     

     

    break;

    case "dbo.TestTable2":

     

     

    var results = db.TestTable2_function().ToList();

     

     

     

     

     

    case "dbo.TestTable3":

     

     

    var results = db.TestTable3_function().ToList();

     

     

     

     

     

    :

     

     

     

     

    break;

    }

     

    //end switch statement

     

     

    return results;

    }

    default

    Sunday, August 22, 2010 4:00 PM

Answers

  • This seems a strange requirement. What are you trying to do with the data once this method returns whichever list it is to return? By virtue of using ToList(), it looks like you are wanting a local copy (or read-only) of the table data; i.e. a copy which, if you update, does not cause the table to be updated.

    Here are some things you could do:

    1. set the method's return type to be 'object'. When you get this back, you would have to cast it back to the right type of List<> based on the table name.
    2. select into a new class (or structure) which contains all the fields that you want and return a List<MyNewClass>
    3. Write an overloaded GetRecords method for each table which returns the specific List<{tablename}>. To make the method signatures distinct to that table type, you would probably have to pass the List<{tablename}> as an out parameter.

     

    Monday, August 23, 2010 7:36 AM
  • Hi Cj,

    I think you can return a Object type instead of List<?>, and judge the specific type of the Object and continue to do what you want based this specific type. For example:

    public Object GetRecords(string tableName) 
    {
      NorthwindDataContext db = new NorthwindDataContext();
    
      Object results;
      switch (tableName) 
      {
        case "Orders": 
        results = (from p in db.Orders
              select p ).ToList();
        break;
    
        case "Products":
        results = (from p in db.Products 
              select p).ToList();
        break;
    
        case "Order_Details":
        results = (from p in db.Order_Details 
              select p).ToList();
        break;
    
        default:
        results = null;
        break;
      }
      //end switch statement
      return results; 
     }
    
    
    Object test = GetRecords("Orders");      
    
    if (test != null)
    {
      if (test.GetType() == typeof(List<Order>))
      {
        List<Order> temp = (List<Order>)test;
        // Do what you want here....
      }
    }      
    
    


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 7:57 AM
    Moderator
  • It seems like a bridge pattern is desired.  The problems is that your shouldn't edit your Dynamic Types that Linq creates.  So I would do an adapter.

    public class TestTable
    {
      public TestTable(TestTable1 record) 
      { 
      //...
      }
    
      public TestTable(TestTable2 record) 
      { 
      //...
      }
    
      public TestTable(TestTable3 record) 
      { 
      //...
      }
    
      //Then add your properties and such.
    }
    
    
    //Your generic method could be...
    
    public List<TestTable> Method()
    {
      List<TestTable> results;
      DataContext dbo = new DataContext();
      switch ( ... )
      {
       case ... :
       results = (from record in dbo.TestTable1
         //...
         select new TestTable(record)).ToList();
       break;
    
       case ... :
       results = (from record in dbo.TestTable2
         //...
         select new TestTable(record)).ToList();
       break;
    
       case ... :
       results = (from record in dbo.TestTable3
         //...
         select new TestTable(record)).ToList();
       break;
    
    
      }
    
    
      return results
    }
    
    Thursday, August 26, 2010 10:31 PM

All replies

  • This seems a strange requirement. What are you trying to do with the data once this method returns whichever list it is to return? By virtue of using ToList(), it looks like you are wanting a local copy (or read-only) of the table data; i.e. a copy which, if you update, does not cause the table to be updated.

    Here are some things you could do:

    1. set the method's return type to be 'object'. When you get this back, you would have to cast it back to the right type of List<> based on the table name.
    2. select into a new class (or structure) which contains all the fields that you want and return a List<MyNewClass>
    3. Write an overloaded GetRecords method for each table which returns the specific List<{tablename}>. To make the method signatures distinct to that table type, you would probably have to pass the List<{tablename}> as an out parameter.

     

    Monday, August 23, 2010 7:36 AM
  • Hi Cj,

    I think you can return a Object type instead of List<?>, and judge the specific type of the Object and continue to do what you want based this specific type. For example:

    public Object GetRecords(string tableName) 
    {
      NorthwindDataContext db = new NorthwindDataContext();
    
      Object results;
      switch (tableName) 
      {
        case "Orders": 
        results = (from p in db.Orders
              select p ).ToList();
        break;
    
        case "Products":
        results = (from p in db.Products 
              select p).ToList();
        break;
    
        case "Order_Details":
        results = (from p in db.Order_Details 
              select p).ToList();
        break;
    
        default:
        results = null;
        break;
      }
      //end switch statement
      return results; 
     }
    
    
    Object test = GetRecords("Orders");      
    
    if (test != null)
    {
      if (test.GetType() == typeof(List<Order>))
      {
        List<Order> temp = (List<Order>)test;
        // Do what you want here....
      }
    }      
    
    


    Best regards,
    Alex Liang
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 23, 2010 7:57 AM
    Moderator
  • Hi Cj, 

    I'm writing to follow up the post. Does the above suggestion work ?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 26, 2010 3:35 AM
    Moderator
  • It seems like a bridge pattern is desired.  The problems is that your shouldn't edit your Dynamic Types that Linq creates.  So I would do an adapter.

    public class TestTable
    {
      public TestTable(TestTable1 record) 
      { 
      //...
      }
    
      public TestTable(TestTable2 record) 
      { 
      //...
      }
    
      public TestTable(TestTable3 record) 
      { 
      //...
      }
    
      //Then add your properties and such.
    }
    
    
    //Your generic method could be...
    
    public List<TestTable> Method()
    {
      List<TestTable> results;
      DataContext dbo = new DataContext();
      switch ( ... )
      {
       case ... :
       results = (from record in dbo.TestTable1
         //...
         select new TestTable(record)).ToList();
       break;
    
       case ... :
       results = (from record in dbo.TestTable2
         //...
         select new TestTable(record)).ToList();
       break;
    
       case ... :
       results = (from record in dbo.TestTable3
         //...
         select new TestTable(record)).ToList();
       break;
    
    
      }
    
    
      return results
    }
    
    Thursday, August 26, 2010 10:31 PM
  • Hi Cj,

    I am marking the replies as answer. If you have questions, please feel free to let me know.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, August 30, 2010 1:19 PM
    Moderator
  • Hi Alex,

        Sorry for the late response, I was away for one work.

    I'll try this option tonight and give you a feedback later.

     

    Cj

     

    Wednesday, September 1, 2010 4:06 PM
  • Thanks Alex! It worked!
    Thursday, September 2, 2010 3:12 AM