nigs
nigs

Reputation: 21

Using Variables to call a table and columns in Linq to Sql

I have a problem which seems simple in a logical way but not in a sql way

I have a program which allows the user to select a single radio button, the checkChange event sends the text string of the button to a function that compares it to a database which returns the name of the column heading and the name of the table the column comes from as a keyValuePair. simple so far.

There are 8 tables and each table has between 10 and 21 possible choices and each choice is a separate column in one of the tables.

What I am trying to do is call a table using a string variable as the table name, named in the keyValuePair.value and then select all the data from the column named by the keyValuePair.key. if that makes sense.

Edited.

There is a class of static methods which initialises the data context and gets the tables, each method name is composed of "get" + TableName + "Table()", so we have for example getDescriptionsTable(), this class is called Initialise. getTableValues() returns a KeyValuePair.

    KeyValuePair<string, string> names = new getTableValues();
    string colHead = names.Key; // contains the column heading
    string tabName = name.Value; // contains the Table name

    string tableName = "get" + tabName + "Table()" // produce a getTable name

    MyDataContext mdc = Initialise.tableName; // Initialise is a class for getting tables.
                                              // table name is the concat variable above
    var p = from x in mdc.tableName  // here I want to use the variable tableName
            where !x.colHead.equals( null) &&  // here I want to use the variable colHead
                  !x.colHead.contains("")      //  "   "  "    "  "   "    "         "

            select new { Code = x.code,  Property = Convert.ToDecimal(x.colHead) };
    var y = from output in p
            orderby y.Code descending
            select new { Code = y.Code, Property = y.Property};

    DataGridView1.DataSource = y;

    class Initialise
    {
      #region Full Table

      public static System.Data.Linq.Table<Login> GetLoginDetails()
      {
          DataClasses2DataContext log = new DataClasses2DataContext();
          return log.GetTable<Login>();
      }

      public static System.Data.Linq.Table<Descriptions> GetDescriptionsTable()
      {
          DataClasses1DataContext dc = new DataClasses1DataContext();
          return dc.GetTable<Descriptions>();
      }

      public static System.Data.Linq.Table<Singles> GetSingleTable()
      {
          DataClasses1DataContext dc = new DataClasses1DataContext();
          return dc.GetTable<Singles>();
      }

      public static System.Data.Linq.Table<Doubles> GetDoubleTable()
      {
          DataClasses1DataContext dc = new DataClasses1DataContext();
          return dc.GetTable<Doubles>();
      }
      #endregion
    }

at this point I have not implemented the join on the second table. That can come later when I understand this problem more fully.

I suppose the SQL equivalent would be select @colName from @tableName where @colName == NotNull and @colName != "" order by @colName desc

I am new to Linq to SQL and there may be a simpler way of doing this. But it may not be possible at all. It would mean a lot of if else statements.

Upvotes: 0

Views: 2215

Answers (1)

Grundy
Grundy

Reputation: 13382

I'm tried several ways and simplest, as i think, will be this

1) add in project reference to System.Linq.Dynamic, available from NuGet

2) Change your code like this

KeyValuePair<string, string> names = new getTableValues();
string colHead = names.Key; // contains the column heading
string tabName = name.Value; // contains the Table name

string tableName = "get" + tabName + "Table()" // produce a getTable name

var table = typeof(Initialise).GetMethod(tableName).Invoke(null, null); // Initialise is a class for getting tables.

var data = ((IQueryable)table).Where(string.Format("!{0}.Equals(@0) && !{0}.Equals(@1)", colHead), null, "")
                              .OrderBy("code")
                              .Select(string.Format("new(code as Code,Convert.ToDecimal({0}) as Property )",colHead));

DataGridView1.DataSource = data;

Upvotes: 1

Related Questions