About DataContext Class

In Linq to Sql, if we added a Linq to Sql Class, there will created a MyClassDataContext which is inherited from DataContext Class. It is included in System.Data.Linq namespace.

"The DataContext provides connection-like functionality for us and has many capabilities such as executing commands, providing access to collections of entities from the database and identity and tracking services. "


I. Use DataContext to Create a DataBase

We should first create a Table Class included in our DataContext instance, otherwise DB is not allowed to be created.

The Table Attribute comes from using System.Data.Linq.Mapping namespace.

We can use LinqtoSql drag in the desinger to see how a table class looks like, we can check msdn:
http://msdn.microsoft.com/en-us/library/Bb399420(v=VS.90).aspx
Notice, we can use mdf file to create db as well.

Let's take an example:

[Table(Name = "Project")]
public class Project
{
[Column(IsPrimaryKey = true)]
public int PID;
[Column]
public string PName;
[Column]
public string PPerson;
}

And we create a MyDataContext Class overriding the class Context.

public class MyDataContext : DataContext
{
public Table Projects;
public MyDataContext(string constr)
: base(constr)
{
}
}

In this case we can use DataContext to create a database:

MyDataContext mctx = new MyDataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");
mctx.CreateDatabase();

2. Use DataContext to Delete a DataBase.

DataContext ctx2 = new DataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");
ctx2.DeleteDatabase();

3.Use DataContext to Execute a Sql Command.

DataContext ctx = new DataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");

ctx.ExecuteCommand("Insert Project(PID,PName,PPerson) values(1,'Project1','Jack')");


ctx.ExecuteCommand("Insert Project values({0},{1},{2})", 2, "Project2","Amy");

4.Use Context GetTable Method

DataContext ctx = new DataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");
foreach (Project p in ctx.GetTable())
{
Console.WriteLine(p.PName);
}
5.Use Context to Translate DataReader
We can use DataContext to translate the DataReader to our objective type.

We create a static method:

static SqlDataReader GetProject(SqlConnection con)
{
con.Open();
SqlDataReader reader;
using (SqlCommand cmd = new SqlCommand("Select * from Project", con))
{
reader = cmd.ExecuteReader();
}
return reader;
}

In this case we can use:

MyDataContext mctx = new MyDataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");

foreach (Project p in mctx.Translate(GetProject(new SqlConnection("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345"))))
{
Console.WriteLine(p.PPerson);
}

6.Use DataContext ExcuteQuery method

IEnumerable ProjectCollection = ctx.ExecuteQuery("Select * from Project");

foreach (Project p in ProjectCollection)
{
Console.WriteLine(p.PID);
}

7. Use DataContext SubmitChanges to Update Table
MyDataContext mctx = new MyDataContext("server=myServer;Database=MyDB;User Id=Jie Zhu;password=12345");

Project p1 = mctx.Projects.Single(p => p.PID == 2);
p1.PPerson = "Erica";

mctx.SubmitChanges();

After we know DataContext clearly here, we know the internal mechanism of Linq to Sql.
For example, we create a Linq to Sql class called MyDataClasses, after database configuration and Table dragging to the designer. It created corresponding DataContext and DataTable classes. You can look at MyDataClasses.designer.cs file.