Linq to SQL DataContext: how to load data?

Question

(I'm completely new to Linq to SQL) I am creating a web app that works very closely with a database, I'm looking for the quickest and connection time efficient model and believing Linq to SQL to be this. I'm using C#/.Net4/Visual Studio 2010

For simplicity sake, I have a web .aspx page containing a number of asp Text Boxes. I want to give their Text values from SQL data via Linq to SQL object. I also have a file called DataClasses.dbml with a a table added in the design view. The code I have so far in my web page code-behind is:

DataClassesDataContext db = new DataClassesDataContext(getConnectionString);

var table = from t in db.MyTable
            where t.PK == 2
            select new { t.col1, t.col2, t.col3};

db.Connection.Open();
db.  // What's the best way of loading the object?
db.Connection.Close();

How do I then access the column values? Or do I databind it to a datatable? If so, how?

myTextBox1.Text = table.???col1.value;

Answer 1

You don't need to open or close the connection. LinqToSql abstracts that away for you.

Once you created the query, you can execute it and retrieve the row as an object using SingleOrDefault().

using (var db = new DataClassesDataContext(getConnectionString))
{
    var query = from t in db.MyTable
                where t.PK == 2
                select new { t.col1, t.col2, t.col3};
    var myObject = query.SingleOrDefault();
}

You can also simplify this operation by using the lambda notation:

using (var db = new DataClassesDataContext(getConnectionString))
{
    var myObject = db.MyTable.SingleOrDefault(t => t.PK == 2 )
}

To access the object you can directly access the columns since they have been mapped to the corresponding properties:

myTextBox1.Text = myObject.col1;

Answer 2

Use

myTextBox1.Text = table.FirstOrDefault().col1.ToString();

Answer 3

Common way is to call method that will execute query (ToArray, ToList, First, Single, etc..) or enumerate it in foreach.

For example:

var query = from t in db.MyTable
            where t.PK == 2
            select new { t.col1, t.col2, t.col3};

var result = query.ToArray(); // now it contains array of resulting objects

// or enumerate
foreach (var obj in query)
{
    // do stuff with obj
}