none
How to nest the enumeration of a table by iterating through an array of generic table references RRS feed

  • Question

  • I'm using SQL Server Express 2008 & VS Express C# 2008

    I have tables in the database each having an ID column
    I want an array that "points" to each table (eg the array size would equal 3 if there were 3 tables in the database - this would be manually coded)
    I then need to iterate through the array and then iterate through the ID records in each table.
    In psuedo code: 

    foreach( table in array)
       foreach(record in table)
     
    I've got as far as creating the table partial classes using SQLMetal:

    namespace ns{   [Table(Name = "dbo.Table_1")]
       public partial class Table_1
       {
          private int _ID;

          public Table_1()
          {
          }

          [Column(Storage = "_ID", DbType = "Int NOT NULL")]
          public int ID
          {
             get
             {
                return this._ID;
             }
             set
             {
                if ((this._ID != value))
                {
                   this._ID = value;
                }
             }
         }
    }

    Do I now create an interface and inherit from the interface?

    namespace ns
    {
       public interface IIDProvider
       {
          int IID { get; }
       }

       public partial class Table_1 : IIDProvider
       {
          public int IID { get { return this.ID; } }
       }
    }

    But then what class do I instantiate the array with and how do I enumerate the tables?
    Is it possible to enumerate a generic reference to a table?
    How do I decouple the enumeration of the table from the concrete table?
    Can this be achieved with LINQ to SQL or is it better handled in LINQ to Entities?

    Thanks in advance.

    Monday, August 3, 2009 8:52 PM

Answers

  • Hi Mr Foo,

    LINQ to SQL and LINQ to Entities help us to build object oriented data model where the data tables with columns are converted to certain entity classes with properties.  In LINQ to SQL, all the data classes are contained in DataContext class.  Once the model is created, we can retrieve certain table values by some LINQ codes like:

    ========================================
    MyDataContext db = new MyDataContext();
    var ids = from t in db.Table_1s
                    select t.ID;

    ========================================

    For more code example for LINQ to SQL, I recommend you the CSLinqToSQL project in All-In-One Code Framework (an open source project
    by MSDN forum support engineers).  We have code examples, detailed comments and documentations in each projects. 

    Since you are using Visual C# Express 2008, for function limitation, the Express version cannot connect to the SQL Server directly.  But it can link to mdf database files.  So you can use the mdf file to create the LINQ to SQL dbml file.  Or SQLMedal is also a good option.  
     

    Besides, as Damien suggested, your scenario can be realized easier by ADO.NET 2.0 technologies.  We can retrieve the data tables into one DataSet by SqlDataAdapter, and then get the table by indexer.   For code example, we have CSUseADONET project in All-In-One Code Framework.    Once the data is retrieved into DataSet, we can still use LINQ to query the data.  For detail, please refer to the CSLinqToDataSets project in All-In-One Code Framework. 

     

    Have a nice day!

     

    Best Regards,
    Lingzhi


    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 6, 2009 9:35 AM
    Moderator

All replies

  • While you might be able to do this with a series of metadata and table calls it really isn't the sort of thing LINQ to SQL was designed for.

    You might be better off with plain ADO.NET that would let you do this in a few lines of code.

    [)amien
    Tuesday, August 4, 2009 3:20 AM
    Moderator
  • Thanks but ... I guess that means this belongs in another forum and ... can you give me some keywords to search for? Thanks again
    Tuesday, August 4, 2009 7:42 AM
  • Hi Mr Foo,

    LINQ to SQL and LINQ to Entities help us to build object oriented data model where the data tables with columns are converted to certain entity classes with properties.  In LINQ to SQL, all the data classes are contained in DataContext class.  Once the model is created, we can retrieve certain table values by some LINQ codes like:

    ========================================
    MyDataContext db = new MyDataContext();
    var ids = from t in db.Table_1s
                    select t.ID;

    ========================================

    For more code example for LINQ to SQL, I recommend you the CSLinqToSQL project in All-In-One Code Framework (an open source project
    by MSDN forum support engineers).  We have code examples, detailed comments and documentations in each projects. 

    Since you are using Visual C# Express 2008, for function limitation, the Express version cannot connect to the SQL Server directly.  But it can link to mdf database files.  So you can use the mdf file to create the LINQ to SQL dbml file.  Or SQLMedal is also a good option.  
     

    Besides, as Damien suggested, your scenario can be realized easier by ADO.NET 2.0 technologies.  We can retrieve the data tables into one DataSet by SqlDataAdapter, and then get the table by indexer.   For code example, we have CSUseADONET project in All-In-One Code Framework.    Once the data is retrieved into DataSet, we can still use LINQ to query the data.  For detail, please refer to the CSLinqToDataSets project in All-In-One Code Framework. 

     

    Have a nice day!

     

    Best Regards,
    Lingzhi


    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 6, 2009 9:35 AM
    Moderator