LINQ to SQL DataContext Example

Having the need to query some SQL server tables using LINQ, I have to first connect to the database.  But, connecting to a database on a remote server is a little bit different than a local database.

Before we can discuss the things like tables, connection strings, queries, anonymous types, we need to discuss about an object called a DataContext.  The DataConext object is the pipe that connects your .Net application to the target database.  This is very similar to the ADO .Net database connection object.

You can think of the database as the entire entity – tables, stored procedures, columns – all that composes a database.  Well, the DataContext object is LINQ’s equivalent object since it is bound to the target  database. 

For the required binding, a DataContext object can be instantiated three ways:

1. A string that represents the location of the SQL Server database
2. A connection string
3. Another DataContext object

All three can be passed into the DataContext constructor. 

I personally like to encapsulate the database’s DataContext object and its Table objects in its own class which then can be used as one unit:

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Text;
namespace TestLinqSql
{
    public partial class clsL2db : DataContext
    {
        public Table<Tracking> Trackings;
        public clsL2db(string connection) : base(connection) { }
    }
}

The used of this class is shown below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace TestLinqSql
{
    public partial class frmLingSql : Form
    {
        clsL2db db;
        public frmLingSql()
        {
            InitializeComponent();
        }
        private void frmLinqSql_Load(object sender, EventArgs e)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                db = new clsL2db("Server=BhServer;initial catalog=chtl_l2_db;user=sa;Password=");
            }
            catch (Exception ex)
            {
                sb.Length = 0;
                sb.Append("Cannot connect to CHTL DB: ").Append(ex.Message);
                MessageBox.Show(sb.ToString());
            }
            try
            {
                var query =
                    from item in db.Trackings
                    where item.HeadWeldZone < 80
                    select item;
                lstTrack.BeginUpdate();
                lstTrack.Items.Clear();
                foreach (var item in query)
                {
                    ListViewItem row = new ListViewItem(item.ChargeKey.ToString());
                    row.SubItems.Add(item.ChgCoilNbr.ToString());
                    row.SubItems.Add(item.HeadWeldZone.ToString());
                    row.SubItems.Add(item.TailWeldZone.ToString());                    lstTrack.Items.Add(row);<br /><br />
                }
                lstTrack.EndUpdate();
                lstTrack.Refresh();
            }
            catch (Exception ex)
            {
                sb.Length = 0;
                sb.Append(&quot;Cannot query DB: &quot;).Append(ex.Message);
                MessageBox.Show(sb.ToString());
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}