CODEWITHSUNDEEP
sqllinq-to-sqldynamicienumerable
Ayo
Ayo

Reputation: 1228

Dynamic Table Names in Linq to SQL

Hi all I have a horrid database I gotta work with and linq to sql is the option im taking to retrieve data from. anywho im trying to reuse a function by throwing in a different table name based on a user selection and there is no way to my knowledge to modify the TEntity or Table<> in a DataContext Query.

This is my current code.

public void GetRecordsByTableName(string table_name){

string sql = "Select * from " + table_name;
var records = dataContext.ExecuteQuery</*Suppossed Table Name*/>(sql);

ViewData["recordsByTableName"] = records.ToList();
}

I want to populate my ViewData with Enumerable records.

Upvotes: 3

Views: 6332

Answers (2)

AdrianoRR
AdrianoRR

Reputation: 1131

As casperOne already answered, you can use ExecuteQuery method first overload (the one that asks for a Type parameter). Since i had a similar issue and you asked an example, here is one:

public IEnumerable<YourType> RetrieveData(string tableName, string name)
        {
            string sql = string.Format("Select * FROM {0} where Name = '{1}'", tableName, name);

            var result = YourDataContext.ExecuteQuery(typeof(YourType), sql);

            return result;
        }

Pay attention to YourType since you will have to define a type that has a constructor (it can't be abstract or interface). I'd suggest you create a custom type that has exactly the same attributes that your SQL Table. If you do that, the ExecuteQuery method will automatically 'inject' the values from your table to your custom type. Like that:

//This is a hypothetical table mapped from LINQ DBML

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.ClientData")]
    public partial class ClientData : INotifyPropertyChanging, INotifyPropertyChanged
    {
private int _ID;

        private string _NAME;

        private string _AGE;
}

//This would be your custom type that emulates your ClientData table

public class ClientDataCustomType 
    {

        private int _ID;

        private string _NAME;

        private string _AGE;
}

So, on the former example, the ExecuteQuery method would be:

var result = YourDataContext.ExecuteQuery(typeof(ClientDataCustomType), sql);

Upvotes: 0

casperOne
casperOne

Reputation: 74530

You can call the ExecuteQuery method on the DataContext instance. You will want to call the overload that takes a Type instance, outlined here:

http://msdn.microsoft.com/en-us/library/bb534292.aspx

Assuming that you have a type that is attributed correctly for the table, passing that Type instance for that type and the SQL will give you what you want.

Upvotes: 3

Related Questions