Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Posted on 2007-07-27 16:46 sunrack 阅读(714) 评论(0) 编辑 收藏 举报ScottGu's Blog
Search
Tags
News
<RECOMMENDED>
My ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas Series
</RECOMMENDED>
Archives
- July 2007 (8)
- June 2007 (10)
- May 2007 (9)
- April 2007 (12)
- March 2007 (9)
- February 2007 (9)
- January 2007 (12)
- December 2006 (12)
- November 2006 (14)
- October 2006 (14)
- September 2006 (15)
- August 2006 (17)
- July 2006 (17)
- June 2006 (14)
- May 2006 (17)
- April 2006 (23)
- March 2006 (12)
- February 2006 (27)
- January 2006 (35)
- December 2005 (29)
- November 2005 (17)
- October 2005 (9)
- September 2005 (8)
- August 2005 (11)
- June 2005 (1)
- May 2005 (2)
- April 2005 (4)
- March 2005 (4)
- February 2005 (1)
- January 2005 (2)
- November 2004 (9)
- October 2004 (5)
- December 2003 (4)
- November 2003 (7)
- October 2003 (15)
- September 2003 (2)
- May 2003 (2)
- April 2003 (3)
- March 2003 (2)
- February 2003 (5)
Favorite Links
Building and using a LINQ for SQL Class Library with ASP.NET 2.0
In my previous posts on doing data access using LINQ and ASP.NET, I used the built-in SQLMetal command-line utility to automatically generate the data model classes for my LINQ data classes. Recently I've been using the LINQ for SQL (aka DLINQ) designer to define my data models instead, and have been really impressed with how easily it enables me to build a re-usable class library that nicely encapsulates my data and business logic.
The below walkthrough demonstrates how you can get started with it yourself. Simply install the LINQ May CTP download on top of VS 2005, and you can then follow along all of the steps below yourself. For the sample below I am using C#, but everything works equally well in VB as well.
Step 1: Create a New LINQ Enabled Class Library
Choose File->New Project within Visual Studio and navigate to the "LINQ Preview" node in the project-type tree-explorer. Then select the "LINQ Library" project icon and create a new LINQ enabled class library:
This will create a class library project whose MSBuild project file enables compilation support for LINQ. A default "Class1.cs" file is added to the project -- I recommend just deleting it to start with an empty project.
Step 2: Add a LINQ for SQL Data Model to the Project
Right-click on the project and choose the Add->New Item context menu item. Scroll down in the dialog and select the "DLINQ Object" icon, and give it an appropriate name (in this sample I'm naming it "Northwind" since I'm going to be modeling the Northwind database):
This will add a Northwind.dlinq file to your project, and bring up its designer:
You can now use the designer to map your data models and entities graphically, as well as setup associations to map relationships between them.
Step 3: Quickly creating a Northwind data model
One of the really easy ways to quickly define your data model classes is to open up the Server Explorer tab within VS 2005 and connect to your database. You can then drill into the Tables and Views within your database, and drag/drop them from the server explorer onto the DLINQ designer surface to automatically create data models for them. For example, if I dragged/dropped the Suppliers, Customers, Orders, Order Details, and Products tables onto the designer surface, I'd by default get a data model that looks like this:
Note how the DLINQ designer automatically sets up default associations between the different entities based on the Primary Key/Foreign Key relationships in the database. These will automatically cause properties to be created on the various data classes so that we can perform rich querying and/or easily traverse between them. For example, if I had a "Customer" instance above I could simply access the "Customer.Orders" property to get a collection of all of that Customer's orders in the database.
If you want, you can add, delete or alter these associations by simply clicking on the association links in the designer and then use the property grid to change its settings. For example, if I wanted to modify the "Order Details" associations above to instead be named "OrderDetails", I would click on the association line in the designer and change it either via the property grid:
Or because it supports inline editing for the name, just click on the name in the designer and rename it in place:
Additionally, I could use the designer to easily rename the entities and/or rename/remove/add properties to them. Once we are done defining your data models, we'll just save the file. The designer will then automatically generate a .cs or .vb file containing the LINQ data class definitions. This is saved in a nested file underneath the Northwind.dlinq item in the solution explorer:
The LINQ data model classes created are defined as "partial" classes -- which means we will be able to go in later and add entity and property validation rules that enforce our business logic, as well as add any additional properties or helper methods we want to the classes.
Step 4: Creating a LINQ Enabled Web-Site that References our LINQ enabled Class Library
We'll now want to go ahead and build a LINQ-enabled ASP.NET web-site that uses our LINQ class library. Choose File->Add->Web Site within Visual Studio to add a new project to the solution. Choose the LINQ web-site template to create an ASP.NET web-site that is enabled with the LINQ compilers:
Your VS solution will then look like this:
The last two steps you'll want to take are to:
1) Add a project reference from the web-site project to the LINQ class library. To-do this, right-click on the web-site and select "Add Reference", select the "Projects" tab and add the reference as normal.
2) Add a connection-string to your web.config file in the web-site project for the LINQ class library. This will enable you to easily configure and change the connection-string that is used at runtime for the application. Simply copy/paste the connection-string value in the LINQ class library's app.config file to the web.config file's <connectionString> section to configure this.
Once the above two steps are done, we are ready to build ASP.NET UI pages against our LINQ class library and associated data models.
Step 5: Build a simple Products Listing Page
To give a simple taste of using LINQ, we'll build a simple Product Listing page that lists products like the image below:
To implement this, I'll add a templated ASP.NET DataList control to the .aspx page using the markup below to define the UI for each product in our list:
<ItemTemplate>
<div class="productimage">
<img src="images/productimage.gif" />
</div>
<div class="productdetails">
<div class="ProductListHead">
<%#Eval("ProductName")%>
</div>
<span class="ProductListItem">
<strong>Price:</strong>
<%# Eval("UnitPrice", "{0:c}") %>
</span>
</div>
</ItemTemplate>
</asp:DataList>
I can then use the below code-behind class to execute a LINQ query against the class library data model we defined in our class library previously to retrieve all of the products in the database supplier by the "Exotic Liquids" supplier and sorted by the product name:
using System.Web;
using System.Query;
using LINQClassLibrary;
public partial class HelloWorld : System.Web.UI.Page {
protected void Page_Load() {
NorthwindDataContext db = new NorthwindDataContext();
DataList1.DataSource = from p in db.Products
where p.Supplier.CompanyName == "Exotic Liquids"
orderby p.ProductName
select p;
DataList1.DataBind();
}
}
Notice above how the LINQ query is strongly-typed, and can incorporate the Product/Supplier entity association we setup earlier (for example: note how the where statement is able to search by the product's Supplier.CompanyName property -- which is stored in the Suppliers table and is linked to the Products table via a primary key/foreign key relationship).
This strong typing model means I get compile-time checking of my LINQ queries today, and will get full intellisense support with the new release of Visual Studio. No more runtime SQL syntax errors!
And that is it. No additional code required.
Step 6: Using Debug Visualizers to inspect what SQL is executed against the database
One of the most common questions people wonder when using ORM data frameworks is "so what SQL code is it running under the covers?". LINQ makes it easy to figure this out. Just set a debugging breakpoint on a LINQ expression variable, and you can use a built-in LINQ debugging visualizer to inspect it while debugging.
For example, set a breakpoint on the DataList1.DataBind() method above and when it is hit hover your mouse over the DataList1.DataSource property and click the magnifying glass to launch its DLINQ query visualizer:
This will bring up a visualizer dialog that displays the SQL statement that LINQ will execute against the database when evaluating that LINQ expression:
If you click the "execute" button on the bottom-right you can even test out the query in the debugger and see the exact data that will be returned from it when the supplier name is "Exotic Liquids" is this:
This makes it really easy to see the exact SQL that is executed and closely watch what is going on as you modify and refine your LINQ queries.
Summary
Hopefully the above walkthrough provides a good introduction to some of the cool things you can do with LINQ and the LINQ May CTP version of it.
To learn more about LINQ with ASP.NET, please download and run the slides + samples from my recent "Building Web Applications Using LINQ" talk.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# Slides + Samples Posted from my TechEd LINQ Talk
One of the highlights for me of my recent trip to TechEd NZ and Australia was the opportunity I had to
# [LINQ] Utiliser LINQ for SQL pour développer un site Web en ASP.NET 2.0
Scott Guthrie continue la suite de ces tutoriels sur LINQ et nous livre cette fois-ci un excellent article...
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# Handling Multi-Application Model with LINQ
# Drag & Drop no-show
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Steve,
The nice thing about LINQ is that you don't need to write or define any custom SQL code. Instead you model your data model classes (Product, Customer, Order, etc) against your database schema or mapping.
You can then query and bind against your data model entities -- rather than against the database tables directly. This gives you nice flexibility while also preserving data model encapsulation.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Steve,
You can install the LINQ CTP on a VS 2005 system without worrying about it breaking things. One thing to-do to ensure you don't affect any existing VS 2005 projects is to not update the intellisense engine to use the new compiler. This ensures that when you use normal VS projects you are using the same binaries as of today. Only when you work on a LINQ enabled project will you use the newer compiler.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Brian,
In cases where you want two applications to go against the same database, I generally recommend using a class library approach like I described above. This provides encapsulation for you, and allows you to easily re-use it. Just have both web applications referene the class library and you are good to go.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Leming,
The current version of LINQ doesn't support medium trust - but the final version will fully support medium trust environments.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Martin,
The plan with DLINQ and ADO.NET vNext is to support a provider model -- which will allow users to integrate it with different database implementations. So as long as a Postgres provider is out there, you'll be able to use LINQ against it.
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# Understanding LINQ to SQL Query Translations
I posted two blog posts last weekend about using LINQ to easily perform data access against a database.
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Vikram,
If you want to send me email I can loop you in with the person who runs the blog aggregator.
Thanks,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Brian,
LINQ queries are strongly typed, so you don't have the same types of SQL Injection Attack worries that you need to watch out for with ad-hoc SQL queries today.
Having said that, you can also fully use stored procedures with LINQ as well. This blog post provides more details on how to-do this: http://weblogs.asp.net/scottgu/archive/2006/06/18/DLINQ-with-Stored-Procedures.aspx
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
Hi Maulik,
You'd want your method to have a signature like this:
public IEnumerable<Product> SomeMethod() {
}
Hope this helps,
Scott
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# Tips/Tricks and LINQ Slides+Demos from my Talk in Dallas
I had a great time presenting fora fewhours to the North Dallas .NET User Group this past Thursday.I
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
# ASP.NET Connections Slides + Demos
Earlier this week I presented at the ASP.NET Connections conference in Las Vegas. This is a great conference
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
我是中文VS2005，按网上方法安装了LINQ ，但是没发现“DLINQObjects”，why？
# re: Building and using a LINQ for SQL Class Library with ASP.NET 2.0
I have started testing a little on Orcas September CTP (October CTP doesn't support August CTP of Entities and hence no tools for generating code from data store). I have May CTP of Linq, August CTP of ADO.Net vNext and a graphical tool for Linq to Entities that suck.
So I went for the dlinq designer you describe here. And it lets me define inheritance etc and all seems good. Except for one thing; How do I save to database?
Morten
# DLINQ Designer and SQL Server 2005 schemas
Hi Scott,
Not sure if this the the right place to ask this, if not point me in the right direction if you can.
I'm trying to use SQL Server 2005 schemas to compartmentalize my database. If I try to drag one of the tables using the designer, I don;t see anything and the code is not generated. SQLMETAL will generate it easily enough, but I don;t want all the tables modelled. The only work around I have found is scripting the schema tables to a new database without the schemas (just using dbo), use the designer and then tweak the generated code to use schemas. Or use SQLMETAL and copy the bits I need. Is there an easier solution to this? I'm sure I'm not the first to run into this, but I can't find any forum subjects that look like it...
Thanks
Kev