Dynamically Extracting SQL Fields in LinqToSQL Data Object

Problem:

I had a situation in which I get a SQL record using LinqToSQL then dynamically extract a value continued in the returned record by the field name.  For example, suppose you have a SQL Table that contains columns like "Description" or "Indication" and the caller would want to know what's contained in these field individually, also dynamically (so that in the future if a column is added, the code do not need to change.)

If you look at these database record objects that LinqToSQL generates, there is no "by-name" functions. 

Solution: 

As it turns out you can essentially "parse" break-down a C# object descriptions into arrays called MethodInfo and FieldInfo using System.Reflection. Once we know this the rest of of the idea is easy; just find the matching method or field names the user requests and dynamically access either the fields or methods.

In LinqToSQL database "row" class objects, the column values are implemented as {get; set; } methods, so instead of extracting the FieldInfo, you need to extract MethodInfo for the column field, and you get two methods get_ and set_. So if my Study table contains Description then the method to get the Description value in the column is _get_Description.

My code example below returns the value contained in the field. In order to make it easier for the user, I make the field name matching to be non-case sensitive by ToLower() the names. 

 private string FindStudyField(string Field, Guid? StudyGuidKey)
        {
            if (StudyGuidKey == null) return "";

            using (var ctx = MyLINQtoSQL.ContextFactory.NewMyDataConext())
            {

                var studies = from s in ctx.Studies
                              orderby s.StudyDateTime descending
                              where s.StudyGuidKey == StudyGuidKey
                              select s;

                if (studies.Count() == 0) return "";

                /*
                 * The following piece of code uses Reflection to get values "dynamically" 
                 * out of a LINQ data row object. In this case a Study record from a study table.
                 * LINQ values are implemented as {get; set;} methods, so we will need to 
                 * get them out as methods. This means that the field name needs to be 
                 * appended with a 'get_' to derive the proper method name that corresponds
                 * with the SQL field name.
                 * 
                 */

                Field = "get_" + Field.Trim().ToLower();

                try
                {
                    Study study = studies.First();
                    Type studyType = typeof(Study);
                    MethodInfo[] studyFields = studyType.GetMethods(BindingFlags.Public
                        | BindingFlags.Instance);

                    for (int i = 0; i < studyFields.Length; i++)
                    {
                        var fn = studyFields[i].Name.ToLower();
                        if (fn != Field) continue;
                        var v = studyFields[i].Invoke(study, null).ToString();
                        return "";
                    }
                }
                catch (Exception ex)
                {
                    return "";
                }
            }
            
            return "";
        }

ASP.NET GridView and LinqDataSource Stuff...

The DataGrid is one of the components I use quite a bit in my line of work, and they are convenient.

I have also been using LINQ quite a bit. It is a significant time saver as well as making my code more easier to understand. But until today I never bothered to use LinqDataSource just because I did not spend time to learn a bit about it. Instead I always built the DataSets using the designer. The problem with the Datasets for me is that it does not show me exactly what I am querying easily.

Before I get into that, I have to tell you also that if your requirement is very simple, then you can feed the result of the Linq query directly to the GridView (or other data binding controls.) This might be a great way to bind the pull down menu or a list view with small number of selections.

For example,

var customers = from c in context.customers select c;
gridview1.DataSource = c;

And that really all it takes to feed a GridView. For more information on this, learn from this MSDN page.

But this won't allow you to sort or page the grid. For that purpose you need to attach a LinqDataSource. You can easily do that by:

• Defining the LinqToSQL object in your project, or pull one in from other assembly
• Dragging and dropping the LinqDataSource component on to of the GridView. The wizard will show you which of the LinqToSQL database context object to use.

Well that's great, but I really do not want to use the connection string that's built into Web.config. In my case, I pull the connection string common to all my applications from my own company's registry set. But at any rate, you would assign a newly created data context to e.ObjectInstance of the event argument.

So here is another trick.

• Define an event processing method for ContextCreating event.
• Re-instantiate the LinqDataContext class object with your connection string. Yes, just the Context is all you need here. The rest will be handled with the LinqDataSource you've created with the designer. (Yes, it is the Data Context object, not the Linq Select result.)

       protected void LinqDataSource1_ContextCreating(object sender,
           LinqDataSourceContextEventArgs e)
        {
            string myConnectionString="your connection string";
            e.ObjectInstance = new MyDataConext(myConnectionString);
        }

Here is another bonus thing you can do. If you do this type of connection string swapping, it is best to create a new context factory class of your own and then call that. This factory class can do the connection string swapping for you. This will centralize the connection string swapping.

In my case, the connection information is stored locally in a registry and many of my own applications pull that information from there at run-time. This way, all I need to do is to set the connection strings once and then all other applications I write will not need separate connection configurations. There are of course many other ways of doing it.