Reputation: 21
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
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