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

Question

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.

Hello, finding it slightly hard to work out what you want. Is the code above working for you? Do you need help with the next step of this once you have added the items to the listbox? — Jamie Hammond
– Jamie Hammond, Commented Feb 28, 2014 at 14:24
As above, I'm having difficulty visualizing this. Please post a few sample rows from one of the target tables to help out. — Eric Hauenstein
– Eric Hauenstein, Commented Feb 28, 2014 at 14:24
What I want is to call a table using a variable such as select * from aVariable. — nigs
– nigs, Commented Feb 28, 2014 at 23:38
Or better still select d.colHead, p.name, p.code from dbo.tableName d, join dbo.descriptions p on d.code == p.descCode where Convert.ToInt32(d.colHead) > 0; ColHead and tableName are both variables — nigs
– nigs, Commented Feb 28, 2014 at 23:51

Grundy · Accepted Answer · 2014-03-06 08:29:19Z

1

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;

edited Mar 6, 2014 at 8:29

answered Mar 6, 2014 at 6:53

Grundy

13.4k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related