LINQ to Sql

I am going to demonstrate LINQ to Sql by writing code.

Support you have a table called Categories in Shalvin database you need to first create a Entity. An Entity is a class that represents a Table or a view in the database.

Create a class as follows:
Set a reference to Data.Linq.

using System.Data.Linq.Mapping;

[Table]
public class Categories
{
[Column(IsPrimaryKey=true, IsDbGenerated=true)]
public int CategoryId { get; set; }

[Column]
public string CategoryName { get; set; }

[Column]
public string Description { get; set; }
}

Each property should have Column Atribute. I am also setting values of IsPrimaryKey and IsDbGenerated for CategoryId column. Editing is possible only if IsPrimaryKey is set to true. IsDbGenerated Indicated that it is an Identity column.


In the asp .net page :

using System.Data.Linq;
protected void Page_Load(object sender, EventArgs e)
{
string ConString = "Integrated Security=sspi;Initial Catalog=Shalvin";
DataContext db = new DataContext(ConString);
var tMovies = db.GetTable<categories>();
GridView1.DataSource = tMovies;
DataBind();
}Now suppose you are interested only in viewing the Categories table with CategoryName 'Confections'.

GridView1.DataSource = tMovies.Where(m => m.CategoryName.Contains("Beverages"));The DataContext is the source of all entities mapped over a database connection. It tracks changes that you made to all retrieved entities .

Linq to Sql with Visual Studio Tools

Having seen the internals lets turn our attention to the Visual Studio Support of Linq to Sql.
Here I have started an Empty Visual Studio Project and Added a Linq to Sql Classes from the Add New Item Dialog. I have renamed it AirLines.dbml

On to the design surface it is possible to drag the table from Database Explorer.

That's all I have to do and I am ready to do CRUD operation with the selected tables.

AirLinesDataContext context = new AirLinesDataContext();
protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    GridView1.DataSource = context.Locations;

    DropDownList1.DataSource = context.Locations;
    DropDownList1.DataTextField = "LocationName";
    DropDownList1.DataValueField = "LocationId";

    DataBind();
  }
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
  Response.Write(DropDownList1.SelectedValue.ToString());
}

protected void btnInsertLocation_Click(object sender, EventArgs e)
{
  var loc = new Location { LocationName = "London", Latitude =67 };
  context.Locations.InsertOnSubmit(loc);
  context.SubmitChanges();
}

Related Blogs
Linq to Objects
Linq to Xml (Code Snippets)