Dynamically Build LINQ to SQL Classes at Runtime

• Download example - 53.5 KB
• Download source - 2.4 KB

Introduction

The attached .cs file looks at a SQL table, generates a LINQ to SQL Object at runtime that can be used to insert, update, and query the database. It is useful for working with your own interactive C# interpreter. The problem it solves; you don't need to design an EDMX file and compile before using.

Using the Code

//make an IDbConnection
IDbConnection oconn = new System.Data.SqlClient.SqlConnection(
  @"Data Source=192.168.0.1;Initial Catalog=db;user id=usr;password=pwd;");

//pass the connection and the table you want
object o = DynamicL2S.L2SClassObject(oconn, "table");
Type itemType = o.GetType();

//create an instance and insert into db
using (DataContext dc = new DataContext(oconn))
{
    var item = (dynamic)Activator.CreateInstance(itemType, new Object[] { });

    item.oid = Guid.NewGuid();
    item.x = 9;
    item.y = 9;
    item.z = 9;
    item.color = Color.Blue.ToArgb();

    dc.GetTable(itemType).InsertOnSubmit(item);

    dc.SubmitChanges();
}

//retrieve all items
using (DataContext dc = new DataContext(oconn))
{
    var res = from r in dc.GetTable(itemType).Cast<dynamic>()
              select r;

    dataGridView1.DataSource = res.ToList();
}

//get properties
System.Reflection.PropertyInfo[] method = o.GetType().GetProperties();
foreach (System.Reflection.PropertyInfo oInf in method)
{
    ret += oInf.Name + " - " + oInf.PropertyType.Name + "\n";
}

Points of Interest

It uses the C# compiler to compile C# classes at run time. It uses object extension methods to force LINQ to allow use of dynamic type when casting.

Credit to Sources

• Evaluate C Code Eval Function - kim.david.hauser
• LINQ Abuse with the C# 4 dynamic type - Jay R. Wren