Tutorial 2: Connecting to database and submitting changes: the DataContext class

Description
Table of Contents
Summary

The DataContext is the object used to connect to a database, retrieve objects from it, and submit changes back to it.

The purpose of the DataContext is to translate requests for objects into SQL queries to be made against the database, and then to assemble objects out of the results.

It enables Language-Integrated Query (LINQ) to obtain data from database.

Description

The DataContext object represents a database table as a ITable collection: Each table collection is obtained through the GetTable method.

It can be used in two different ways:

  • Directly. In this case it will be instantiated through a IDbConnection or connection string. A mapping source file can be supplied.
  • Declare a strongly-typed DataContext class; In this case all table collections are declared as properties in order to avoid the use of the GetTable method on the Linq query code.
Basic or strongly-typed ?

A strongly-typed DataContext class must always be implemented for advanced scenarios since some methods are protected (e,g, ExecuteMethodCall)

Object Tracking

The DataContext manages object identity. Whenever a new row is retrieved from database, the row is logged iin a identity table by its primary key and new object is ceated.

If the same row is loaded then the original object instance is retrieved without a new access to the database:

Customer cust1 =
   (from cust in db.Customers
    where cust.CustomerID == "BONAP"
    select cust).First();

Customer cust2 =
   (from cust in db.Customers
    where cust.CustomerID == "BONAP"
    select cust).First();

If the same row is loaded througn different queries then the original object instance is also retrieved:

Customer cust1 =
   (from cust in db.Customers
    where cust.CustomerID == "BONAP"
    select cust).First();

Customer cust2 =
   (from ord in db.Orders
    where ord.Customer.CustomerID == "BONAP"
    select ord).First().Customer;

How to use it ?

Global DataContext – dangerous in multi-threaded environments (including web apps). since instance members are not guaranteed to be thread-safe

DataContext per thread – complicated. If the DataContext is tracking changes we must be sure to flush them at the appropriate time.

DataContext per atomic action – The ability to track changes is lost since one DataContext creates an object while another updates or deletes it.

DataContext per data object – It is not an elegant because  the DataContext must be used on instantiation (create and attach) and update/delete (pull it off the data object and use it).

Object tracking


State Description
Untracked An object not tracked by LINQ to SQL. Examples include the following:

  • An object not queried through the current DataContext (such as a newly created object).
  • An object created through deserialization
  • An object queried through a different DataContext.
Unchanged An object retrieved by using the current DataContext and not known to have been modified since it was created.
PossiblyModified An object which is attached to a DataContext. For more information, see Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL).
ToBeInserted An object not retrieved by using the current DataContext. This causes a database INSERT during SubmitChanges.
ToBeUpdated An object known to have been modified since it was retrieved. This causes a database UPDATE during SubmitChanges.
ToBeDeleted An object marked for deletion, causing a database DELETE during SubmitChanges.
Deleted An object that has been deleted in the database. This state is final and does not allow for additional transitions.

Operations
Use basic DataContext to connect to database and retrieve table rows
How to do it
//Connect to database
DataContext db = new DataContext(@"Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa"); // new DataContext(@"c:\databasefile.mdf")

// Get table collection to run queries
Table<Customer> Customers = db.GetTable<Customer>();

// Query customers
var query =
    from cust in Customers
    where cust.City == "London"
    select cust;
Use basic DataContext to connect to database and retrieve table rows (2)
How to do it
//Connect to database
DataContext db = new DataContext(@"Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa"); // new DataContext(@"c:\databasefile.mdf")

// Get table collection to run queries
Table<Customer> Customers = db.GetTable<Customer>();

// Query customers
var query =
    from cust in db.GetTable<Customer>()
    where cust.City == "London"
    select cust;
Use strongly-typed DataContext to connect to database and retrieve table rows
How to do it
// Declare data context
public partial class TrainingDataContext : DataContext
{
        public Table<Customer> Customers
        {
	  get
          {
            return this.GetTable<Customer>();
          }
        }

    public TrainingDataContext (string pConnection) : basepConnection) { }
}

//Connect to database
TrainingDataContext db = new TrainingDataContext (@"Data Source=VEGETA\VEGETA$SQL2008;Initial Catalog=Training;Integrated Security=True; User Id=sa"); // new DataContext(@"c:\databasefile.mdf")

// Query customers
var query =
    from cust in db.Customers
    where cust.City == "London"
    select cust;
Initiate transaction and commit all changes
Remarks

For multiple datacontext(s) within the same transaction the MSDTC service on remote SQL Server machine must be active.

If an error occurs the transaction is rolledback automatically.

How to do it
TrainingContext db = new TrainingContext();
using (TransactionScope trans = new TransactionScope())
{
    ...
    db.SubmitChanges();
    trans.Complete();
}
Display generated SQL

The SQL generated by the Data Context object and sent for execution at the data base server can be obtained through one of the following ways:
1) Configure the DataContext::Log property to an ouptut channel;
2) Use the DataContext::GetCommand method to display the SELECT statements generated for queries

Remarks

In case of (2) only the first command is retrieved and therefore additional commands that are used for eager loading (LoadWith) are not included.

in case of (2) should be invoked before the DataContext::SumbitChanges() method

How to do it
TrainingDataContext dc = new TrainingDataContext();

 //1. Re-direct all SQL statement to the console output channel
dc.Log = Console.Out;

var query =
   from p in people.People
   from s in people.Salaries
   where p.ID == s.ID
   select new { p.LastName, p.FirstName, s.Year, s.SalaryYear };

//2. SELECT statements generated for this query
Console.WriteLine(dc.GetCommand(query).CommandText);
Verify changed objects

Use the DataContext::GetChangeSet to obtain a collection of entity objects for INSERT, UPDATE or DELETEed respectivelly.

Remarks

Should be invoked before the DataContext::SumbitChanges() method

Turn off deferred loading

By default deferred loading is activated and is manipulated through the DataContext::LoadWith() method.

Remarks

If object tracking is turned off then deferred loading is also turned off.

How to do it

To turn off the DataContext:DeferredLoadingEnabled must be set to TRUE

Retrieve information as read-only

If theres is no intention to change the data then object tracking is not required.

Remarks

When no object tracking exists then a performance is incresead.

How to do it

To turn off the DataContext:ObjectTrackingEnabled must be set to FALSE.

Stored and reused queries

If an application executes structurally similar queries many times, these queries can be compiled one time and executed several times with different parameters.

A compiled queriy is stored as a static Func<>. It is static so it can be reused across the application.
Since a Func<> has a limited number of parameters that can be passed to it, depending on the complexity of the query (i.e. number of parameters):
1) In the first parameter is passed the data context class;
2) In the second, third and fourth parameters are passed the parameters used in the query;
OR
2) In the second parameter is passed a helper class, that aggregates all the parameters of the query;
3) In the last parameter (the output) is passed the Linq Query

Remarks

If query reusing is used then a performance is incresead.

How to do it
// 1. Compiled query with an helper class

private class GetOrdersQueryParameters
{
    public string CustomerName { get; set; }
    public string ShipCountry { get; set; }
    public int Take { get; set; }
    public int Skip { get; set; }
}

private static Func<TrainingDataContext, GetOrdersQueryParameters, IQueryable<Order>> GetOrdersQuery =
    CompiledQuery.Compile((TrainingDataContext dc, GetOrdersQueryParameters pParams) =>
												(from order in dc.Orders
												 where order.Customer.CompanyName == pParams.CustomerName
															 && order.ShipCountry == pParams.ShipCountry
												 select order).Skip(pParams.Skip).Take(pParams.Take));

// 2. Compiled query without an helper class

private static Func<TrainingDataContext, string, string, IQueryable<Order>> GetOrdersQuery =
    CompiledQuery.Compile((TrainingDataContext dc, string pName, string pShipCountry) =>
												(from order in dc.Orders
												 where order.Customer.CompanyName == pCustomerName
															 && order.ShipCountry == pShipCountry
												 select order));
How to use it

1) Load up the helper class with parameters (if used)
2) Execute the compiled query by calling the method (the Linq query) pointed by the static Func<>.

public static List<Order> GetOrdersCompiled(string pCustomerName, string pShipCountry, int pSkip, int pTake)
{

    // load up the helper class with parameters
    GetOrdersQueryParameters params = new GetOrdersQueryParameters { CustomerName = pCustomerName, pShipCountry = pShipCountry, pSkip = pSkip, pTake = pTake }; 

    // pass in parameters to the query and execute it...
    using (TrainingDataContext dc = new TrainingDataContext())
    {
        // Execute the compiled query.
        List<Order> orders = GetOrdersQuery(dc, params).ToList(); 

        return orders;
    }
}
Attach an entitiy object to a data context

The attach of an entiity object to a data context should be done if:
1) The entity object is in a untracked state (every new obejct just instantiated and populated is in untracked state);
2) The entity object has been created in one DataContext, serialized to a client, and then deserialized back (with the intention to perform an update or delete operation).