|
Language Integrated Query (LINQ, pronounced "link") is a Microsoft .NET
Framework component that adds native data querying capabilities to .NET
languages using a syntax reminiscent of SQL. Many of the concepts that
LINQ has introduced were originally tested in Microsoft's C? research
project. LINQ was released as a part of .NET Framework 3.5 on November 19,
2007.
LINQ defines a set of query operators that can be used to query, project and filter data in arrays, enumerable classes, XML, relational database, and third party data sources. While it allows any data source to be queried, it requires that the data be encapsulated as objects. So, if the data source does not natively store data as objects, the data must be mapped to the object domain. Queries written using the query operators are executed either by the LINQ query processing engine or, via an extension mechanism, handed over to LINQ providers which either implement a separate query processing engine or translate to a different format to be executed on a separate data store (such as on a database server as SQL queries). The results of a query are returned as a collection of in-memory objects that can be enumerated.
In this tutorial, we will be
examining LINQ with SQL, and create a project step by step.
|
First we will need to create a new website for our project. Launch Visual Studio 2008 (If you are using the Express Version, this would be Visual Studio Web Developer 2008). Once the program has launched, go the File menu, and click on New Web Site.
Select the ASP.NET Web Site, Choose your desired programming language, and project title:
The Default.aspx will automatically launch. Here we will be adding a new item. Simply right click on the project, and select: "Add New Item"
Choose the "LINQ to SQL Classes", and enter your desired name. This example uses CategoriesClasses.dbml.
Here is some additional information for "LINQ to SQL" from Microsoft:
|
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.
|
You may get promted to store the information in the App_Code folder. Click Yes to accept this.
Automatically the CategoriesClasses.dbml will
launch. Here is where we will configure the database table we will work
with for this example. First we need to configure our database. Click
on the database explorer which should come up on the right side of your
screen:
Right Click on Data Connections, and click on "Add Connection".
This will let us configure our settings to our desired database. This
will be different based on the type of server you have, and on what
machine is resides on. Make sure you have the database credential
information before proceeding:
The wizard to add a new connection will now come up.
Select the data source: Microsoft SQL Server (SqlClient) Enter the server name: localhost (This may be different on your machine) Select "User SQL Server Authentication", and enter the log-on credentials.
Next select the Northwind Database from the Connect to Database section.
Make sure to click on the "Test Connection" to make sure that there is an established connection to the db.
Select the data source: Microsoft SQL Server (SqlClient) Enter the server name: localhost (This may be different on your machine) Select "User SQL Server Authentication", and enter the log-on credentials.
Next select the Northwind Database from the Connect to Database section.
Make sure to click on the "Test Connection" to make sure that there is an established connection to the db.
Now you should be able to see all the tables of the
Northwind database. Simply drag and drop the Categories table from the
Database Explorer, to your CategoriesClasses.dbml designer.
See below for an example:
See below for an example:
Now we are ready to have our Default.aspx page
communicate with our LINQ To SQL Classes. First we will drag the
GridView control onto our Default.aspx page. This can be done by simply
dragging the GridView control from the Data tab of the Toolbox onto a
section of the Default.aspx page. There should be a div tag by default
on your Default.aspx page, in which you can place the GridView.
Once the GridView is in place, drag the LinqDataSource onto the page as well. The LinqDataSource is going to be used to connect our GridView with our LINQToSQLClasses.
Here is some information about LinqDataSource from Microsoft: http://msdn.microsoft.com/en-us/library/bb547113.aspx
Once the GridView is in place, drag the LinqDataSource onto the page as well. The LinqDataSource is going to be used to connect our GridView with our LINQToSQLClasses.
Here is some information about LinqDataSource from Microsoft: http://msdn.microsoft.com/en-us/library/bb547113.aspx
|
The
LinqDataSource control exposes Language-Integrated Query (LINQ) to Web
developers through the ASP.NET data-source control architecture. LINQ
provides a unified programming model for querying and updating data from
different types of data sources, and extends data capabilities directly
into the C# and Visual Basic languages. LINQ simplifies the interaction
between object-oriented programming and relational data by applying the
principles of object-oriented programming to relational data. For more
information about LINQ, see Language-Integrated Query (LINQ).
|
|
By
using declarative markup, you can create a LinqDataSource control that
connects to data from either a database or an in-memory data collection
such as an array. In the declarative text, you can write all the
conditions that are required to retrieve, filter, order, and group the
data. When you retrieve data from a SQL database table, you can also
configure a LinqDataSource control to handle updating, inserting, and
deleting data. The control does this without requiring you to write the
SQL commands to perform these tasks. The LinqDataSource class also
provides an event model that enables you to handle customized scenarios.
|
Next click on the GridView, and expand to view the GridView Tasks. The "Choose Data Source:" option will appear. Make sure to select the LinqDataSource we had just added. The default name should be LinqDataSource1. Also click on the "Enable Paging", "Enable Sorting", and "Enable Selection" checkboxes. Notice the change in the GridView columns in the designer.
Next we will configure our LinqDataSource. Click on the LinqDataSource control, and expand to see the LinqDataSource Tasks. Here select the "Configure DataSource".
The Configure Data Source for LinqDataSource wizard will launch. First click on the checkbox which states "Show only DataContext Objects," and select the CategoriesClassesDataContext which we had created earlier. Click on Next once you have chosen thecontext object.
Here we will leave the Select option to *. Here is where you would configure the columns you want to display for selection, editing, or updating. For this tutorial we will keep it simply, and display all the columns for the Categories table.
Click on the Advanced option on the lower right side, before clicking on Finish.
We want to be able to automatically allow updates to the database when they are edited. Select the last option which will bring this feature automatically, saving the developer a lot of time from coding. Imagine how much time this could save you. Once you have selected this option, click on OK.
You may be prompted to Refresh the fields and keys for GridView1. Click on Yes for it to automatically perform mappings.
Modify the GridView Tasks once more, but notice that we now have the Enable Editing option. Select this option as well as the others (Paging, Sorting, Selection).
In order to give a better feel, click on the AutoFormat and choose one of the provided styles. This tutorial uses the Professional style.
Now we are ready to run the code. Build the solution, and run the application... Below you will find a few screen shots of how the page will look and act based on our actions. You can also sort the data automatically giving it a rich feel.
SCREEN SHOT 1: Default.aspx page containing GridView with LinqDataSource and LINQ To SQL Classes
SCREEN SHOT 2: Clicking on Select for an item of the GridView.
SCREEN SHOT 3: Clicking on Edit for an item of the GridView.
SCREEN SHOT 4: Modifying the Description of one of the rows of the GridView
SCREEN SHOT 5: Click on Update, and watch the results.
This tutorial is a step by step guide to see how powerful framework 3.5 is with LINQ. You can automatically achieve features such as Edit/Update/Insert/Select/Delete without almost any code.
No comments:
Post a Comment