none
how can I map unknown table names in VS 2008? RRS feed

  • Question

  • I have a database which is always called "FredDB" (for the sake of this question).

    FredDB can exist on different servers. FredDB ALWAYS has just ONE type of table on it... We'll call it FREDTable.

    The structure of FredTable is ALWAYS the same. (It has a  PK, some columns, and no attached records or FKs)

    BUT... there can be multiple instances of FredTable on each FredDB and they will have different names...

    So, on one server, FredDB may have Tables called Abe, Bert, Charlie and Dan, and on another server we might find tables called Fritz, Alphonse, and KarlHeinz. But ALL of them are identical in structure (they are FREDTables).

    I used the VS 2008 Tools to drop one of these tables on the design surface and I renamed it "FREDTable". It generated a mapping for me as expected. However the Table Name property is the name of the example I chose (say, Alphonse...). 

    I need to be able to specify a server and a database at run time (Not too hard to build a connection string for a data context) and then get ALL the Table Names on the FredDB. I can then let the user select a name and I want to be able to access THAT table using LINQ, and map it to FREDTable so I can access/update the columns in it.

    I am not highly experienced at using LINQ and I am around intermediate with C#, so any code samples would be very gratefully received.

    I really can't see how to get round the Table Name problem... It just isn't possible for me to map EVERY table of EVERY FredDB in advance, and it would be horribly inefficient to have exactly the same definitions in the maps for every table, even if I COULD do it.

    Any Ideas?

    (Please let me know if this is impossible and I'll just have to use SQL; I REALLY like what I have done with LINQ so far (all mapping known tables, and everything works very well indeed) but this one has me beaten.)


    I used to write COBOL... now I can do anything.

    Sunday, August 24, 2014 2:38 PM

Answers

  • Didn't have time to look at it since your reply but I think I have the solution to your problem.

    1.  Again create the Linq table description by dragging the table onto the DBML workspace.  

    2.  No need to create SP so that is all on the data side.

    3.  Now the programming (not showing how to get a list of tables).  All you need to do is the following:

            Dim dc As New DataClasses1DataContext
    
            Dim anotherret = dc.ExecuteQuery(Of TableLL1)("select * from dbo.TableLL1", "").ToList
            Dim stillanotherret = dc.ExecuteQuery(Of TableLL1)("select * from dbo.TableLL2", "").ToList
    
     

    As you can see you are getting a list of TableLL1 objects but they are coming from two seperate queries that get their data from two separate tables with the same structure.

    Hope this helps (no SP) and BTW I coded in Cobol for about 10 years at the start of my career. 


    Lloyd Sheen

    • Marked as answer by petedashwood Wednesday, August 27, 2014 3:45 AM
    Tuesday, August 26, 2014 7:00 PM

All replies

  • There is a way but it requires some trickery.

    First create a stored procedure that simply has a select of the table that you want (any one of the tables since this is just to get the structure).  Once you have created it drag it to the DBML surface.

    Ok now you have the first step.  Now change the sp code to the following (or close to it).

    USE [MusicInfoRationialized]
    GO
    /****** Object:  StoredProcedure [dbo].[AdHocQuery]    Script Date: 08/24/2014 15:47:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[AdHocQuery]
    	-- Add the parameters for the stored procedure here
    	@TableName sysname
    AS
    BEGIN
    	SET NOCOUNT ON;
    
        DECLARE @cmd AS NVARCHAR(max)
    	SET @cmd = N'SELECT * FROM ' + @Tablename 
    	EXEC sp_executesql @cmd 
    
    END
    

    As you can see this procedure is taking the name of the table and return a select of the contents of that table.

    To use it you would do the following (VB.NET).

            Dim ret = dc.AdHocQuery("dbo.RSongInfo").ToList
    

    Now you have a method of inputting the table name to get the info as you required.


    Lloyd Sheen

    Sunday, August 24, 2014 8:05 PM
  • Thank you Lloyd for the very prompt and interesting reply.

    Maybe this is a little over my head as I am not familiar with using SPs. I can certainly do this though and will try it if there are no other ideas.

    I take it the stored procedure is only used once to get the mapping generated for LINQ? Obviously, I can't insist that all users put an SP on their databases...Although I haven't used VB very much I can see the way it works and I really like the way it returns the data in the datacontext.

    This is an imaginative solution.

    Thanks.


    I used to write COBOL... now I can do anything.

    Monday, August 25, 2014 1:30 AM
  • Didn't have time to look at it since your reply but I think I have the solution to your problem.

    1.  Again create the Linq table description by dragging the table onto the DBML workspace.  

    2.  No need to create SP so that is all on the data side.

    3.  Now the programming (not showing how to get a list of tables).  All you need to do is the following:

            Dim dc As New DataClasses1DataContext
    
            Dim anotherret = dc.ExecuteQuery(Of TableLL1)("select * from dbo.TableLL1", "").ToList
            Dim stillanotherret = dc.ExecuteQuery(Of TableLL1)("select * from dbo.TableLL2", "").ToList
    
     

    As you can see you are getting a list of TableLL1 objects but they are coming from two seperate queries that get their data from two separate tables with the same structure.

    Hope this helps (no SP) and BTW I coded in Cobol for about 10 years at the start of my career. 


    Lloyd Sheen

    • Marked as answer by petedashwood Wednesday, August 27, 2014 3:45 AM
    Tuesday, August 26, 2014 7:00 PM
  • Thanks again, Lloyd.

    I solved the "Get me all the tables on FredDB" query using the usual select from sys.tables via ExecuteCommand, but I didn't make the connection between the TResult in ExecuteQuery (I think this is your "Of TableLL1"? It wasn't until I saw your VB that I realized I can specify "FREDTable" here and then select whatever table name I actually need by constructing the command SQL dynamically and passing it to ExecuteQuery.

    dc.ExecuteQuery<FREDtable>("Select * from " + desiredTableName)

    I haven't tried it yet but I think it will work :-)

    This is the actual solution to what I was trying to do, so thank you very much, again.

    Cheers,

    Pete.

    (BTW, I have a soft spot for COBOL, but these days I code mostly in C#...)


    I used to write COBOL... now I can do anything.

    Wednesday, August 27, 2014 3:43 AM