Querying a database using Linq and Reflection


Hi,

With Insert, Update and Delete methods but without a Select method, LinqDataSource control looks just like a chair with only three legs! Such a powerful and flexible control is used to get connected to a DataBound control like GridView, while it could excel in many scenarios if it had a Select method! For example, it was great if we could write such a code:

LinqDataSource1.TableName=’students’;

LinqDatasource1.Where = ‘Grade<5’;

var x= LinqDataSource1.Select();

Anyway, in order to develop a special component, that I am gonna describe it in a separate post later, I needed to query a Table using Linq to Sql and the table’s string name. In ADO.NET territory, we may create a dynamic string query and execute it over the database:

const string TableName = ‘students’;

string Query = String.Format(‘Select * from {0} ‘, TableName);

But, how to query the Student table with Linq? Better to say, how does LinqDataSource probably work? The question is fairly easy, but before explaining it, I invite you to be informed that Linq has come to let us query in a strongly typed fashion! Thus, only in rare situations you might need to query a database in an untyped manner.

Anyway, first we have to get to know which Assembly is already running. In a Console or Windows application we may simply find this by calling Assembly.GetExecutingAssembly() method. But, in a Web application, things are different. In an ASP.NET application classes are compiled into App_config.dll assembly. ASP.NET uses shadow copy mechanism to let an ASP.NET application get updated while it is executing. In fact, ASP.NET copies every .DLL file in the bin directory to ” Temporary ASP.NET Files “ folder and name them with strange , long and unreadable (!) names. For example App_Code.Dll file might be named “App_Code.7vkubplh.dll” ! Therefore, we have to search all loaded assemblies for the required type.

As a result, we need the following code to get all assemblies loaded inside the current Application Domain:

List<Assembly> asmList =  AppDomain.CurrentDomain.GetAssemblies().ToList();

Since DataContext class provides required methods, we actually don’t need to find concrete DataContext classes. But if for any reason you need to find them, the best way is to find for classes that have been decorated with DatabaseAttribute attribute:

var AllDataContext = from C in asmList

from P in C.GetTypes()

from J in P.GetCustomAttributes(true)

where J.GetType().Name == “DatabaseAttribute”

select P;

Well, In order to query a table, you have to Add a DataContext to your application and drag your desired table on to  it. Luckily, since each Linq To Sql entity is a class, we can not have two different tables with the same name. Therefore,  the only job to do is to query a type with your table name:

Type table = (from C in asmList

from P in C.GetTypes()

where P.Name == “user”

select P).Single();

This code looks up all loaded assemblies and checks to see which one is named “user”. “user” is actually the table name. Since we are sure only one “user” class exists, we have used Single extension method.

Well, now that we have found the class, we should create an instance of DataContext class and call it’s non-generic GetTable method:

DataContext dc = new DataContext(” a connection string comes here”);

List<object> Records = dc.GetTable(table).Cast<object>().ToList();

The above code converts the resulting table to List<object> but it is not necessary indeed.

Well, the records are ready. You can assign “Records” variable to a GridView to see the results :

GridView1.DataSource = Records;

GridView1.DataBind();

If you would like to get the field values individually, you should obtain all public property names, loop over them and call GetValue on each one:

Table tbl = new Table();

// Loop over records

foreach (var s in dc.GetTable(table))

{

TableRow row = new TableRow();

// Retrieve all instance (non-static) and public properties.

PropertyInfo[] pList = s.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

// Loop over properties

foreach (PropertyInfo p in pList)

{

TableCell cell = new TableCell();

object temp = p.GetValue(s, null);

cell.Text = temp != null ? p.GetValue(s, null).ToString() : “-“;

row.Cells.Add(cell);

}

tbl.Rows.Add(row);

}

this.Controls.Add(tbl);

Well, in the above code I have converted all the values to String but be reminded that this method won’t work for Boolean fields.

Hope this article be a bit of help 🙂

Aspguy

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s