Monday, March 17, 2008

LINQ to SQL

Language-Integrated Query (LINQ) is a set of features in .NET Framework 3.5 (Visual Studio 2008) that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio 2008 includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.

In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable<(Of <(T>)>) interface. LINQ support for the ADO.NET Entity Framework is also planned, and LINQ providers are being written by third parties for many Web services and other database implementations.
You can use LINQ queries in new projects, or alongside non-LINQ queries in existing projects. The only requirement is that the project target version 3.5 of the .NET Framework.

There are different variants of LINQ as listed below:
LINQ to Objects
LINQ to XML
LINQ to ADO.NET (Dataset)
LINQ to SQL
LINQ to SQL

LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.

In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.

See also http://msdn2.microsoft.com/hi-in/library/bb425822(en-us).aspx

The DataContext
The DataContext is the main conduit by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select.

Implementing LINQ to SQL
LINQ to SQL is best implemented in a project using the built-in Object Relational Designer (O/R Designer) in Visual Studio 2008. O/R Designer auto generates the DataContext class and entity classes for all the tables in a specified database.

To launch it,
Visual Studio 2008 > Projects > Add New Item > Visual C# (Category) > LINQ to SQL Classes (Template)
(Eg Name: Northwind)

This will open a blank O/R Designer. Now open Server explorer and add a new data connection to the required database. Drag and drop the required tables to the O/R Designer and it will automatically generate all needed classes.

The DataContext class will be named as NorthwindDataContext and this can be used to do operations against the database.

Selecting Data


//Create the DataContext object
NorthwindDataContext db = new NorthwindDataContext();

//Write the Select Query
var prods = from p in db.Products
select p;

//Use the output in various ways
foreach (Product prod in prods)
{
MessageBox.Show(prod.ProductName);
}

//assigning as data source to a grid view
gvMain.DataSource = prod;


Updating Data


//Getting one row from the Products table
Product prod = db.Products.Single(p => p.ProductName == "First Prod");

//Updating two of its columns
prod.UnitPrice = 25;
prod.UnitsInStock = 50;

//Submitting the changes to database
db.SubmitChanges();


Inserting Data


//Creating a new category
Category cat = new Category();
cat.CategoryName = "New Test Category";
cat.Description = "This is a new type of category";

//Creating two new products
Product p1 = new Product();
p1.ProductName = "First Prod";

Product p2 = new Product();
p2.ProductName = "Second Prod";

//Add the products to new category
cat.Products.Add(p1);
cat.Products.Add(p2);

//Add category to database and save changes
db.Categories.InsertOnSubmit(cat);
db.SubmitChanges();


Deleting Data


//Get the required data to delete
var toyProds = from p in db.Products
where p.ProductName.Contains("toy")
select p;

//Delete it from database
db.Products.DeleteAllOnSubmit(toyProds);
db.SubmitChanges();


So start using LINQ and refer the 101 samples at http://msdn2.microsoft.com/en-us/vcsharp/aa336746.aspx to help you with any syntax issues.

1 comment:

  1. Thanks. Appreciate if you could get me somethig for a stored procedure which returns some record.
    Abish

    ReplyDelete