How to get data dynamically from you LINQ to SQL data context

Lately I have been playing around with LINQ to SQL and let me tell you this. It is super cool! :D So today I wanted to check how I can grab data from all tables that are mapped in my LINQ to SQL mapping file. I discovered that it is really easy… In fact it is just a few lines of code. Have a look …

NWDataContext context = new NWDataContext();
var model = context.Mapping;
//get all tables 
foreach (var mt in model.GetTables())
    Console.WriteLine("Getting data " + mt.TableName);
    //generate a sql statment for each table - just grab the first 20
    string sql = String.Format("Select Top 20 * from {0} ", mt.TableName);
    var data = context.ExecuteQuery(mt.RowType.Type, sql);
    //data is here now. Lets print it on the console
    foreach (var item in data)

So all you need to do is to get the list of table meta from the data context and then generate a sql that grabs all the data from the specific table…

You can get loads of other information from the table meta for more details click here.

To get info about the DataMembers of a specific table you can use the RowType property of the TableMeta that you get from the GetTables().


kick it on

One thought on “How to get data dynamically from you LINQ to SQL data context

  1. Here’s some vb code that might help others… You could drop the tables into a data gridview and perform an update. ;)

    Private Sub Form_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    ClientDB.Log = Console.Out

    MyDatatablesBindingSource.DataSource = ClientDB.Mapping.GetTables
    DataGridView1.DataSource = MyDatatablesBindingSource
    DataGridView1.Columns(0).Visible = False
    DataGridView1.Columns(2).Visible = False
    DataGridView1.Columns(3).Visible = False
    DataGridView1.Columns(4).Visible = False
    DataGridView1.Columns(5).Visible = False
    DataGridView1.Columns(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

    End Sub

    Private Sub MyDatatablesBindingSource_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyDatatablesBindingSource.PositionChanged

    MyDataInTableBindingSource.DataSource = ClientDB.ExecuteQuery(MyDatatablesBindingSource.CurrencyManager.Current.RowType.Type, “select * from ” & MyDatatablesBindingSource.CurrencyManager.Current.TableName.ToString)
    DataGridView2.DataSource = MyDataInTableBindingSource
    End Sub

    The only problem that I’ve had is that I can’t add/delete rows. I can only update/change existing information. :( Any ideas?


Leave a Reply

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

You are commenting using your 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