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)
    {
        Console.WriteLine(item.ToString());
    }
}
Console.ReadLine();

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().

LINQ to SQL ROCKS !!!!!

kick it on DotNetKicks.com

About these ads

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
    DataGridView1.Update()

    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
    DataGridView2.Update()
    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?

    Thanks,
    John

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