CODEWITHSUNDEEP
c#.netsqldatabaselinq
evanmcdonnal
evanmcdonnal

Reputation: 48076

LINQ selecting a table to query at runtime

I'm new to LINQ and trying to write a query which chooses the table to query based on a variable entered at runtime. The basic idea is that I have a Contacts table for each person in a Persons table. I can get all the data I want from the entities but I can't find the other table when I query. Here's the code;

    public void GetFriendsList(string username, Person[] people)
    {     
        string table = "FL." + username;

        DataTable friends = new DataTable(table);

            var filePaths =
            from row in friends.AsEnumerable()
            select row.Field<string>("Friend's Name");

        var friendsArray = filePaths.ToArray();

        for (int i = 0; i < people.Length; i++)
        {
            people[i] = GetPerson(friendsArray[i]);
        }     
    }

I've also tried doing this with something like the following at the top but VS isn't recognizing the FillDataSet function.

DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables[table];

Any advice appreciated, sorry for the vagueness. I'm assuming I'm missing a connection string or something. I've stepped through this the and code clearly isn't connecting to the table.

Upvotes: 2

Views: 683

Answers (2)

McGarnagle
McGarnagle

Reputation: 102743

Euphoric is correct in the comments, if a bit rude. There's no way you should have a separate contacts table for each person. The normal way would be to have a single Contacts table and put a foreign key linking back to the Persons table. That way you could accomplish everything in a single query and wouldn't have to worry about querying tables unknown until runtime.

As far as why the code you listed isn't working, your suspicion is basically correct: it's not connected to any database. You're creating a new empty table and attempting to query it. To get this to work you would need to use SqlConnection to connect to the database, then use SqlDataAdapter and its Fill method to populate your DataTable. (But again, please don't do this if possible, read up on SQL foreign keys.)

Upvotes: 4

Jakub Konecki
Jakub Konecki

Reputation: 46008

First of all, you're not really using LINQ to query your database.

But most importantly, you should have only 1 Contacts table in your database with a relationship to Persons table.

Get Microsoft sample Northwind database (http://www.microsoft.com/en-us/download/details.aspx?id=23654) and learn more about database schema design.

Upvotes: 3

Related Questions