Category Archives: LINQ - Page 2

LINQ to SQL (Part 5 – Binding UI using the ASP:LinqDataSource Control)

Posted by on July 18, 2007 No comments

Hi all, I have been quite busy lately so didnt got much time to post here, but now will try to catch it up. Here is a post from Scott Guthrie which is PART 5 of his LINQ to SQL series:

Over the last few weeks I’ve been writing a series of blog posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first four parts of my LINQ to SQL series:

In these previous LINQ to SQL blog posts I focused on how you can programmatically use LINQ to SQL to easily query and update data within a database.

In today’s blog post I’ll cover the new control that is shipping as part of ASP.NET in the upcoming .NET 3.5 release. This control is a new datasource control for ASP.NET (like the ObjectDataSource and SQLDataSource controls that shipped with ASP.NET 2.0) which makes declaratively binding ASP.NET UI controls to LINQ to SQL data models super easy.


Sample Application We’ll be Building

The simple data editing web application I’ll walkthrough building in this tutorial is a basic data entry/manipulation front-end for products within a database:

step03 LINQ to SQL (Part 5 Binding UI using the ASP:LinqDataSource Control)

The application will support the following end-user features:

  1. Allow users to filter the products by category
  2. Allow users to sort the product listing by clicking on a column header (Name, Price, Units In Stock, etc)
  3. Allow users to skip/page over multiple product listings (10 products per page)
  4. Allow users to edit and update any of the product details in-line on the page
  5. Allow users to delete products from the list

The web application will be implemented with a clean object-oriented data model built using the LINQ to SQL ORM.

All of the business rules and business validation logic will be implemented in our data model tier – and not within the UI tier or in any of the UI pages. This will ensure that: 1) a consistent set of business rules are used everywhere within the application, 2) we write less code and don’t repeat ourselves, and 3) we can easily modify/adapt our business rules at a later date and not have to update them in dozens of different places across our application.

We will also take advantage of the built-in paging/sorting support within LINQ to SQL to ensure that features like the product listing paging/sorting are performed not in the middle-tier, but rather in the database (meaning only 10 products are retrieved from the database at any given time – we are not retrieving thousands of rows and doing the sorting/paging within the web-server).

read original

LINQ to SQL (Part 3 – Querying our Database)

Posted by on July 4, 2007 No comments

The third part from Scott Guthrie’s series LINQ to SQL:

Last month I started a blog post series covering LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapping) framework that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can then use LINQ expressions to query the database with them, as well as update/insert/delete data from it.

Below are the first two parts of my LINQ to SQL series:

In today’s blog post I’ll be going into more detail on how to use the data model we created in the Part 2 post, and show how to use it to query data within an ASP.NET project.


Northwind Database Modeled using LINQ to SQL

In Part 2 of this series I walked through how to create a LINQ to SQL class model using the LINQ to SQL designer that is built-into VS 2008. Below is the class model that we created for the Northwind sample database:

step1 LINQ to SQL (Part 3 Querying our Database)


Retrieving Products

Once we have defined our data model classes above, we can easily query and retrieve data from our database. LINQ to SQL enables you to do this by writing LINQ syntax queries against the NorthwindDataContext class that we created using the LINQ to SQL designer above.

For example, to retrieve and iterate over a sequence of Product objects I could write code like below:

step1 LINQ to SQL (Part 3 Querying our Database)

In the query above I have used a “where” clause in my LINQ syntax query to only return those products within a specific category. I am using the CategoryID of the Product to perform the filter.

One of the nice things above LINQ to SQL is that I have a lot of flexibility in how I query my data, and I can take advantage of the associations I’ve setup when modeling my LINQ to SQL data classes to perform richer and more natural queries against the database. For example, I could modify the query to filter by the product’s CategoryName instead of its CategoryID by writing my LINQ query like so:

step2 LINQ to SQL (Part 3 Querying our Database)

Notice above how I’m using the “Category” property that is on each of the Product objects to filter by the CategoryName of the Category that the Product belongs to. This property was automatically created for us by LINQ to SQL because we modeled the Category and Product classes as having a many to one relationship with each other in the database.

read original

Introducing LINQ – Part 5

Posted by on June 7, 2007 No comments

Introduction

Whenever someone asks me about the DataContext I always refer to it as the “brains” of Linq to SQL, why? Because it is. It is responsible for the translation of Linq to T-SQL, and the mapping of the results (rows) of that query to objects, and so much more.

If you run any codegen tool (SqlMetal, or Linq to SQL file (VS Orcas designer)) you always get a class that derives from the DataContext class. The DataContext can be equated to a database, in that it contains a series of tables (normally this is implemented as a property that returns a Table), and sprocs (methods calls that invoke underlying sprocs at the DB layer).

Note: The DataContext can also handle other SQL Server features like views, and table functions etc.

What’s so special about the DataContext class?

Most notably the DataContext takes some Linq code and generates the appropriate T-SQL statement for that query; if that query returns rows then the DataContext object will take care of mapping those rows to objects.

Figure 5-1: DataContext core functionality

DataContextCorefunctionality Introducing LINQ – Part 5

I want to see the T-SQL generated by my LINQ

If you are a control freak/perfectionist like me then you probably want to know exactly what T-SQL is being generated for you at all times.

Before I show you some code please remember that the T-SQL generation by the DataContext object is not perfect, if you know of a very efficient T-SQL solution then implement that in a sproc. Saying that I have found the T-SQL generation of the DataContext object to be efficient in some advanced queries. Like so many things in programming one way, and one way alone is often not the best approach – experiment to see what offers the best performance for your query.

There are a few ways in which we can monitor the T-SQL being generated by our DataContext object, these include:

  • Visual Studio Orcas
  • ToString() override of query
  • DataContext Log property
  • SQL Server 2005 profiler (not included with Express edition)
Note: As the majority of people reading this article will be using the Express editions of the developer tools I will skip the SQL Server 2005 profiler demo. Those who have used the profiler before will know that it is a real time T-SQL dump of everything that a particular SQL Server 2005 instance is doing so seeing what T-SQL the DataContext is generating is a trivial task.

Visual Studio Orcas

As Linq is a 1st class citizen within the C# and VB.NET languages it should be no surprise to you whatsoever to see rich debugging support within the IDE.

Figure 5-2: Seeing the T-SQL generated by the DataContext object in VS Orcas

DataContextObjectInVSOrcas Introducing LINQ – Part 5

ToString() override

This is a really nice feature – if you call the ToString() method on any variable whose value is a query you can see the associated T-SQL for that query.

Figure 5-3: Calling ToString()

  1. using System;
  2. using System.Linq;
  3. using System.Collections.Generic;
  5. namespace ConsoleApplication4
  6. {
  7. public class Program
  8. {
  9. public static void Main(string[] args)
  10. {
  11. using (BookShopDataContext db = new BookShopDataContext())
  12. {
  13. IEnumerable query = from b in db.Books select b;
  14. Console.WriteLine(query);
  15. }
  16. }
  17. }
  18. }


Figure 5-4: Result of running the code in Figure 5-3

5 4 Introducing LINQ – Part 5

Concurrency

Concurrency is always an issue – Linq to SQL by default uses optimistic concurrency. Take an example of where we retrieve a particular piece of data and then update that data, we then try to submit the updated data to the server but we get an exception as Linq to SQL knows that we have violated a concurrency check as we were acting upon “old” data.

Just to show this I did a pretty simple operation by adding a breakpoint on the line where I call the SubmitChanges(...) method and then executed some T-SQL code to alter that record and then continued the execution of my application so I would get an exception.

Figure 5-6: ChangeConflictException

ChangeConflictException Introducing LINQ – Part 5

The SubmitChanges(...) method is overrided to take a ConflictMode enum where we can change the default behaviour when data is submitted to the database, these are:

  • ContinueOnConflict
  • FailOnFirstConflict

The above can be used when within a TransactionScope. There are a few attribute values we can apply to our generated code to specifically say “we don’t care if this particular bit of data is changed – just overwrite it”. We do this using the UpdateCheck parameter to the Column attribute.

Figure 5-7: We don’t care about this data – just overwrite the value with what we have!

  1. [global::System.Data.Linq.Column(Storage="_Title", Name="Title",
  2. DBType="NVarChar(50) NOT NULL", CanBeNull=false,
  3. UpdateCheck=System.Data.Linq.UpdateCheck.Never)]

The UpdateCheck enum values:

  • Always
  • Never
  • WhenChanged

I’m pretty sure I’ve not covered everything in Linq to SQL with regards to concurrency – remember though that you can use the TransactionScope type in the .NET framework to assist you.

Using VS Orcas to look at data

Remember – queries only execute when we call the GetEnumerator() of the variable that holds the query! In previous CTP’s of VS Orcas it was hard to see what data we pulled back from the database as the debugger didn’t call the GetEnumerator() method when you wanted to look at that particular variable – the March CTP does however which is really great!

Let’s go ahead and look at using VS Orcas to look at data returned from our database using the Locals window when debugging. First place a breakpoint after the line you define a query, you don’t need a foreach to call the GetEnumerator() – the IDE will do that for us. When you get to your breakpoint you will see a screen similar to that in Figure 5-8 where the variable holding our query has a message saying something like “expand me to enumerate this IEnumerable”.

Figure 5-8: Thinking about enumerating the IEnumerable

EnumeratingIEnumerable Introducing LINQ – Part 5

Expand the results! You know you want to.

Figure 5-9: Viewing the results

5 9 Introducing LINQ – Part 5

You can see that in Figure 5-9 I have two objects returned from my query, I can drill down into the values of their properties and so forth.

On the face of it this is a pretty basic enhancement to the IDE, and you would be right in thinking so – but this is a great visual tool which I’m sure you will love when using Linq to SQL!

LINQ to SQL FAQ

Will LINQ to SQL support Oracle?

This is a common question, the answer to which is no. Linq to SQL is a lightweight O/R mapper targeted for use with SQL Server 2000/2005. For a more powerful O/R mapper look at the Entity Data Model (EDM) – EDM will support Oracle and other 3rd party databases.

I hate codegen tools, how much code does it really take to hook up to my DB using LINQ to SQL?

Surprisingly little, essentially you need to create a property with the correct access (get, set, or both?) with the appropriate Column attribute. The class with your properties should be pluralized and use the Table attribute to map it to the corresponding table in the database.

Finally you will need to create a class that derives from DataContext. In this class you will want to expose some properties that return a generic Table.

Below is the code for the TodoListDataContext and Task types:

Task.cs (type that is associated with records in Tasks):

  1. using System;
  2. using System.Data.Linq;
  4. namespace ConsoleApplication5
  5. {
  6. [Table(Name="Tasks")]
  7. public class Task
  8. {
  9. private int _taskId;
  11. [Column(Name = "TaskID", Storage = "_taskId",
  12. IsDBGenerated = true, IsPrimaryKey = true,
  13. CanBeNull = false, DBType = "int not null identity")]
  14. public int TaskID
  15. {
  16. get { return _taskId; }
  17. }
  19. [Column(Name = "Title", CanBeNull = false,
  20. DBType = "nvarchar(50) not null")]
  21. public string Title
  22. {
  23. get;
  24. set;
  25. }
  27. [Column(Name = "Complete", CanBeNull = false)]
  28. public bool Complete
  29. {
  30. get;
  31. set;
  32. }
  33. }
  34. }


TodoListDataContext.cs (type that exposes a Table of type Task, and defines connection string)

  1. using System;
  2. using System.Data.Linq;
  4. namespace ConsoleApplication5
  5. {
  6. public class TodoListDataContext : DataContext
  7. {
  9. public TodoListDataContext(string conn) : base(conn)
  10. {
  11. }
  13. public Table Tasks
  14. {
  15. get { return GetTable(); }
  16. }
  18. }
  19. }


Why are the changes to my data in SQL Server not being persisted?

Remember, in order to persist changes to SQL Server you need to call the SubmitChanges(...) method on your DataContext object after you have made changes to any data.

Can I use XML to define the O/R mapping?

Yes. You can use SqlMetal to generate an XML mapping file, when creating a DataContext you need to specify that your mapping is defined in an XML file by using the XmlMappingSource type.

I’m hitting the database every time for two identical queries, can I prevent this?

To prevent hitting the database every time you can load the results into memory (e.g. Append ToList() to a query) any subsequent calls to that query will use the in-memory collection rather than going off and querying the database again.

Is it possible to create code from XML?

Yes. You can create a .dbml file using SqlMetal and then generate a codefile (either VB.NET or CS) based on the XML defined in the .dbml.

What’s this deferred loading stuff?

Linq to SQL uses deferred loading when accessing properties of a related entity. Given a simple select query returning all Books, if I then want the publisher for each book then I can do so, however, behind the scenes the DataContext is doing a select where query to get the publisher for the current book. This is deferred loading.

Note: You can disable deferred loading using the EnableDeferredLoading property of a DataContext object.

Summary

There is a ton of stuff that’s special about the DataContext that I haven’t covered – I encourage everyone to go check out the DataContext class and experiment. I hope that I have provided you with the knowledge to get started with Linq, and Linq to SQL – if you have any questions about a particular feature of Linq to SQL I have/haven’t covered then please feel free to email me via the address on http://gbarnett.org/about.

read original

LINQ to SQL (Part 2 – Defining our Data Model Classes)

Posted by on May 29, 2007 1 comment

Another great article from Scott! Must read for all of us! Chech it out here:

In Part 1 of my LINQ to SQL blog post series I discussed “What is LINQ to SQL” and provided a basic overview of some of the data scenarios it enables.

In my first post I provided code samples that demonstrated how to perform common data scenarios using LINQ to SQL including:

  • How to query a database
  • How to update rows in a database
  • How to insert and relate multiple rows in a database
  • How to delete rows in a database
  • How to call a stored procedure
  • How to retrieve data with server-side paging

I performed all of these data scenarios using a LINQ to SQL class model that looked like the one below:

step1 LINQ to SQL (Part 2 Defining our Data Model Classes)

In this second blog post in the series I’m going to go into more detail on how to create the above LINQ to SQL data model.

LINQ to SQL, the LINQ to SQL Designer, and all of the features that I’m covering in this blog post series will ship as part of the .NET 3.5 and Visual Studio “Orcas” release.

You can follow all of the steps below by downloading either Visual Studio “Orcas” Beta 1 or Visual Web Developer Express “Orcas” Beta1. Both can be installed and used side-by-side with VS 2005.


Create a New LINQ to SQL Data Model

You can add a LINQ to SQL data model to an ASP.NET, Class Library or Windows client project by using the “Add New Item” option within Visual Studio and selecting the “LINQ to SQL” item within it:

step1 LINQ to SQL (Part 2 Defining our Data Model Classes)

Selecting the “LINQ to SQL” item will launch the LINQ to SQL designer, and allow you to model classes that represent a relational database. It will also create a strongly-typed “DataContext” class that will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we modeled. As I described in Part 1 of this blog post series, the DataContext class is the main conduit by which we’ll query entities from the database as well as apply changes back to it.

Below is a screen-shot of an empty LINQ to SQL ORM designer surface, and is what you’ll see immediately after creating a new LINQ to SQL data model:

step2 LINQ to SQL (Part 2 Defining our Data Model Classes)


Entity Classes

LINQ to SQL enables you to model classes that map to/from a database. These classes are typically referred to as “Entity Classes” and instances of them are called “Entities”. Entity classes map to tables within a database. The properties of entity classes typically map to the table’s columns. Each instance of an entity class then represents a row within the database table.

Entity classes defined with LINQ to SQL do not have to derive from a specific base class, which means that you can have them inherit from any object you want. All classes created using the LINQ to SQL designer are defined as “partial classes” – which means that you can optionally drop into code and add additional properties, methods and events to them.

Unlike the DataSet/TableAdapter feature provided in VS 2005, when using the LINQ to SQL designer you do not have to specify the SQL queries to use when creating your data model and access layer.

Instead, you focus on defining your entity classes, how they map to/from the database, and the relationships between them. The LINQ to SQL OR/M implementation will then take care of generating the appropriate SQL execution logic for you at runtime when you interact and use the data entities. You can use LINQ query syntax to expressively indicate how to query your data model in a strongly typed way.


Creating Entity Classes From a Database

If you already have a database schema defined, you can use it to quickly create LINQ to SQL entity classes modeled off of it.

The easiest way to accomplish this is to open up a database in the Server Explorer within Visual Studio, select the Tables and Views you want to model in it, and drag/drop them onto the LINQ to SQL designer surface:

step5 LINQ to SQL (Part 2 Defining our Data Model Classes)

When you add the above 2 tables (Categories and Products) and 1 view (Invoices) from the “Northwind” database onto the LINQ to SQL designer surface, you’ll automatically have the following three entity classes created for you based on the database schema:

step6 LINQ to SQL (Part 2 Defining our Data Model Classes)

Using the data model classes defined above, I can now run all of the code samples (expect the SPROC one) described in Part 1 of this LINQ to SQL series. I don’t need to add any additional code or configuration in order to enable these query, insert, update, delete, and server-side paging scenarios.


Naming and Pluralization

One of the things you’ll notice when using the LINQ to SQL designer is that it automatically “pluralizes” the various table and column names when it creates entity classes based on your database schema. For example: the “Products” table in our example above resulted in a “Product” class, and the “Categories” table resulted in a “Category” class. This class naming helps make your models consistent with the .NET naming conventions, and I usually find having the designer fix these up for me really convenient (especially when adding lots of tables to your model).

If you don’t like the name of a class or property that the designer generates, though, you can always override it and change it to any name you want. You can do this either by editing the entity/property name in-line within the designer or by modifying it via the property grid:

step7 LINQ to SQL (Part 2 Defining our Data Model Classes)

The ability to have entity/property/association names be different from your database schema ends up being very useful in a number of cases. In particular:

1) When your backend database table/column schema names change. Because your entity models can have different names from the backend schema, you can decide to just update your mapping rules and not update your application or query code to use the new table/column name.

2) When you have database schema names that aren’t very “clean”. For example, rather than use “au_lname” and “au_fname” for the property names on an entity class, you can just name them to “LastName” and “FirstName” on your entity class and develop against that instead (without having to rename the column names in the database).


Relationship Associations

When you drag objects from the server explorer onto the LINQ to SQL designer, Visual Studio will inspect the primary key/foreign key relationships of the objects, and based on them automatically create default “relationship associations” between the different entity classes it creates. For example, when I added both the Products and Categories tables from Northwind onto my LINQ to SQL designer you can see that a one to many relationship between the two is inferred (this is denoted by the arrow in the designer):

step13 LINQ to SQL (Part 2 Defining our Data Model Classes)

The above association will cause cause the Product entity class to have a “Category” property that developers can use to access the Category entity for a given Product. It will also cause the Category class to have a “Products” collection that enables developers to retrieve all products within that Category.

step9 LINQ to SQL (Part 2 Defining our Data Model Classes)

If you don’t like how the designer has modeled or named an association, you can always override it. Just click on the association arrow within the designer and access its properties via the property grid to rename, delete or modify it.


Delay/Lazy Loading

LINQ to SQL enables developers to specify whether the properties on entities should be prefetched or delay/lazy-loaded on first access. You can customize the default pre-fetch/delay-load rules for entity properties by selecting any entity property or association in the designer, and then within the property-grid set the “Delay Loaded” property to true or false.

For a simple example of when I’d want to-do this, consider the “Category” entity class we modeled above. The categories table inside “Northwind” has a “Picture” column which stores a (potentially large) binary image of each category, and I only want to retrieve the binary image from the database when I’m actually using it (and not when doing a simply query just to list the category names in a list).

I could configure the Picture property to be delay loaded by selecting it within the LINQ to SQL designer and by settings its Delay Loaded value in the property grid:

step8 LINQ to SQL (Part 2 Defining our Data Model Classes)

Note: In addition to configuring the default pre-fetch/delay load semantics on entities, you can also override them via code when you perform LINQ queries on the entity class (I’ll show how to-do this in the next blog post in this series).


Using Stored Procedures

LINQ to SQL allows you to optionally model stored procedures as methods on your DataContext class. For example, assume we’ve defined the simple SPROC below to retrieve product information based on a categoryID:

step10 LINQ to SQL (Part 2 Defining our Data Model Classes)

I can use the server explorer within Visual Studio to drag/drop the SPROC onto the LINQ to SQL designer surface in order to add a strongly-typed method that will invoke the SPROC. If I drop the SPROC on top of the “Product” entity in the designer, the LINQ to SQL designer will declare the SPROC to return an IEnumerable result:

step11 LINQ to SQL (Part 2 Defining our Data Model Classes)

I can then use either LINQ Query Syntax (which will generate an adhoc SQL query) or alternatively invoke the SPROC method added above to retrieve product entities from the database:

step12 LINQ to SQL (Part 2 Defining our Data Model Classes)

Using SPROCs to Update/Delete/Insert Data

By default LINQ to SQL will automatically create the appropriate SQL expressions for you when you insert/update/delete entities. For example, if you wrote the LINQ to SQL code below to update some values on a “Product” entity instance:

step5 LINQ to SQL (Part 2 Defining our Data Model Classes)

By default LINQ to SQL would create and execute the appropriate “UPDATE” statement for you when you submitted the changes (I’ll cover this more in a later blog post on updates).

You can also optionally define and use custom INSERT, UPDATE, DELETE sprocs instead. To configure these, just click on an entity class in the LINQ to SQL designer and within its property-grid click the “…” button on the Delete/Insert/Update values, and pick a particular SPROC you’ve defined instead:

step14 LINQ to SQL (Part 2 Defining our Data Model Classes)

What is nice about changing the above setting is that it is done purely at the mapping layer of LINQ to SQL – which means the update code I showed earlier continues to work with no modifications required. This avoids developers using a LINQ to SQL data model from having to change code even if they later decide to put in a custom SPROC optimization later.


Summary

LINQ to SQL provides a nice, clean way to model the data layer of your application. Once you’ve defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it.

Using the built-in LINQ to SQL designer within Visual Studio and Visual Web Developer Express you can create and manage your data models for LINQ to SQL extremely fast. The LINQ to SQL designer also provides a lot of flexibility that enables you to customize the default behavior and override/extend the system to meet your specific needs.

In upcoming posts I’ll be using the data model we created above to drill into querying, inserts, updates and deletes further. In the update, insert and delete posts I’ll also discuss how to add custom business/data validation logic to the entities we designed above to perform additional validation logic.

Mike Taulty also has a number of great LINQ to SQL videos that I recommend checking out here. These provide a great way to learn by watching someone walkthrough using LINQ to SQL in action.

read original

Using LINQ to SQL (Part 1)

Posted by on May 19, 2007 No comments

It’s all about eductaion, thats what my mom used to say to me. Its all about education – thats what Scott Guthrie is doing. Again he posted great article describing how to use LING to SQL. Thats the first part of this series, here is the whole of it:

Developers can use LINQ with any data source. They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results. LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources. The “Orcas” version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

What Is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework “Orcas” release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.


Modeling Databases Using LINQ to SQL:

Visual Studio “Orcas” ships with a LINQ to SQL designer that provides an easy way to model and visualize a database as a LINQ to SQL object model. My next blog post will cover in more depth how to use this designer (you can also watch this video I made in January to see me build a LINQ to SQL model from scratch using it).

Using the LINQ to SQL designer I can easily create a representation of the sample “Northwind” database like below:

step1 Using LINQ to SQL (Part 1)

My LINQ to SQL design-surface above defines four entity classes: Product, Category, Order and OrderDetail. The properties of each class map to the columns of a corresponding table in the database. Each instance of a class entity represents a row within the database table.

The arrows between the four entity classes above represent associations/relationships between the different entities. These are typically modeled using primary-key/foreign-key relationships in the database. The direction of the arrows on the design-surface indicate whether the association is a one-to-one or one-to-many relationship. Strongly-typed properties will be added to the entity classes based on this. For example, the Category class above has a one-to-many relationship with the Product class. This means it will have a “Categories” property which is a collection of Product objects within that category. The Product class then has a “Category” property that points to a Category class instance that represents the Category to which the Product belongs.

The right-hand method pane within the LINQ to SQL design surface above contains a list of stored procedures that interact with our database model. In the sample above I added a single “GetProductsByCategory” SPROC. It takes a categoryID as an input argument, and returns a sequence of Product entities as a result. We’ll look at how to call this SPROC in a code sample below.

Understanding the DataContext Class

When you press the “save” button within the LINQ to SQL designer surface, Visual Studio will persist out .NET classes that represent the entities and database relationships that we modeled. For each LINQ to SQL designer file added to our solution, a custom DataContext class will also be generated. This DataContext class is the main conduit by which we’ll query entities from the database as well as apply changes. The DataContext class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.

For example, below is the NorthwindDataContext class that is persisted based on the model we designed above:

step2 Using LINQ to SQL (Part 1)


LINQ to SQL Code Examples

Once we’ve modeled our database using the LINQ to SQL designer, we can then easily write code to work against it. Below are a few code examples that show off common data tasks:


1) Query Products From the Database

The code below uses LINQ query syntax to retrieve an IEnumerable sequence of Product objects. Note how the code is querying across the Product/Category relationship to only retrieve those products in the “Beverages” category:

C#:

step3 Using LINQ to SQL (Part 1)

VB:

step9 Using LINQ to SQL (Part 1)


2) Update a Product in the Database

The code below demonstrates how to retrieve a single product from the database, update its price, and then save the changes back to the database:

C#:

step5 Using LINQ to SQL (Part 1)

VB:

step11 Using LINQ to SQL (Part 1)

Note: VB in “Orcas” Beta1 doesn’t support Lambdas yet. It will, though, in Beta2 – at which point the above query can be rewritten to be more concise.


3) Insert a New Category and Two New Products into the Database

The code below demonstrates how to create a new category, and then create two new products and associate them with the category. All three are then saved into the database.

Note below how I don’t need to manually manage the primary key/foreign key relationships. Instead, just by adding the Product objects into the category’s “Products” collection, and then by adding the Category object into the DataContext’s “Categories” collection, LINQ to SQL will know to automatically persist the appropriate PK/FK relationships for me.

C#

step4 Using LINQ to SQL (Part 1)

VB:

step12 Using LINQ to SQL (Part 1)


4) Delete Products from the Database

The code below demonstrates how to delete all Toy products from the database:

C#:

step6 Using LINQ to SQL (Part 1)

VB:

step13 Using LINQ to SQL (Part 1)


5) Call a Stored Procedure

The code below demonstrates how to retrieve Product entities not using LINQ query syntax, but rather by calling the “GetProductsByCategory” stored procedure we added to our data model above. Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

C#:

step7 Using LINQ to SQL (Part 1)

VB:

step14 Using LINQ to SQL (Part 1)


6) Retrieve Products with Server Side Paging

The code below demonstrates how to implement efficient server-side database paging as part of a LINQ query. By using the Skip() and Take() operators below, we’ll only return 10 rows from the database – starting with row 200.

C#:

step8 Using LINQ to SQL (Part 1)

VB:

step10 Using LINQ to SQL (Part 1)


Summary

LINQ to SQL provides a nice, clean way to model the data layer of your application. Once you’ve defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it.

Hopefully the above introduction and code samples have helped whet your appetite to learn more. Over the next few weeks I’ll be continuing this series to explore LINQ to SQL in more detail.

read original

Introducing LINQ – Part 4

Posted by on May 16, 2007 No comments

Part four of Granville Barnett’s series of Introducing LINQ is online. Read it here:

Introduction

The ASP.NET application that we will create we be a 3 tier application, consisting of a data access layer (DAL), a business logic layer (BLL) and a presentation layer (PL).

We will be using Visual Studio Orcas Beta 1 for this tutorial.

Defining our database schema

Before we jump into coding some C# or ASP.NET we need to setup our database, more importantly we first need a database! If you haven’t already go and grab yourself a copy of SQL Express 2005. I will be using SQL Server Management Studio 2005 to create and define my database; however, you can do all of this using Visual Studio 2005 if you want.

Creating a database

Let’s go ahead and create a database for this tutorial. I’m going to create a database called TodoList. To create a database right click the Databases folder in the Object Explorer window and click New Database (Figure 4-1).

Figure 4-1: Creating a database

CreatingADatabase Xlinq4 Introducing LINQ – Part 4

When the New Database window is visible enter TodoList (or any other name you want to use) as the Database name (Figure 4-2).

Figure 4-2: Specifying the database name

Specifyingthedatabasename XLinq4 Introducing LINQ – Part 4

Adding the Tasks table

With our database created we will now add a Tasks table. In SQL Server Management Studio right click the Tables folder and select New Table (Figure 4-3).

Figure 4-3: Creating the Tasks table

CreatingTheTasksTable Xlinq4 Introducing LINQ – Part 4

In the table designer replicate the settings from Figure 4-4.

Figure 4-4: Defining the columns

DefiningTheColumns Xlinq4 Introducing LINQ – Part 4

Note: In Figure 4-4 TaskID is a primary key and identity.

When you save the table, save it as Tasks (Figure 4-5).

Figure 4-5: Saving the table

SavingTheTable Xlinq4 Introducing LINQ – Part 4

Creating the stored procedures

We need to define three stored procedures, one for adding a new task, one for updating the state of a task, and finally one for retrieving all tasks of a specific state (either active, or closed).

Creating a stored procedure is easy, simply right click on the Stored Procedures folder and click New Stored Procedure (Figure 4-6).

Figure 4-6: Creating a stored procedure

CreatingStoredProcedure Xlinq4 Introducing LINQ – Part 4

Below are the three code listings for the stored procedures we will use.

Figure 4-7: AddTask

  1. create procedure AddTask
  2. @Title nvarchar(50),
  3. @Complete bit
  4. as
  5. insert into Tasks (Title, Complete)
  6. values (@Title, @Complete)


Figure 4-8: GetTasksByState

  1. create procedure GetTasksByState
  2. @Complete bit
  3. as
  4. select TaskID, Title, Complete
  5. from Tasks
  6. where Complete = @Complete
  7. order by Title


Figure 4-9: UpdateTaskState

  1. create procedure UpdateTaskState
  2. @TaskID int,
  3. @Complete bit
  4. as
  5. update Tasks
  6. set Complete = @Complete
  7. where TaskID = @TaskID


Setting up our data access layer

With our database ready let’s go ahead and create our DAL. We will use the LINQ to SQL file designer in Visual Studio Orcas to do this.

Note: You will first need to add a connection to the TodoList database in the Server Explorer window.

Right click on your website project and add a new LINQ to SQL file called TodoList (Figure 4-10).

Figure 4-10: Adding a LINQ to SQL file to our project

AddingLINQToSQL Xlinq4 Introducing LINQ – Part 4

With the file created drag and drop the Tasks table and the three stored procedures we created on to the design canvas of the designer (Figure 4-11).

Figure 4-11: Adding our table and sprocs to our DAL

AddingTableTSprocs Xlinq4 Introducing LINQ – Part 4

That’s it! Our DAL is all ready to use.

Adding a business logic layer

In this tutorial there is not much point to adding a BLL, but we will add one nonetheless.

This layer in a real life application this layer would enforce any business rules (parameter checking etc…). Also in a real life application you would want to separate your DAL and BLL into their own components.

Right click the App_Code folder and add a new class called TasksBll.cs, then copy and paste the class definition given in Figure 4-12 into that file.

Figure 4-12: TasksBll.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  5. namespace Org.GBarnett.Dns
  6. {
  7. public static class TasksBll
  8. {
  9. ///
  10. /// Gets all tasks of a particular state
  11. ///
  12. public static List GetTasksByState()
  13. {
  14. using (TodoListDataContext db = new TodoListDataContext())
  15. {
  16. return (from t in db.GetTasksByState() select t).ToList();
  17. }
  18. }
  20. ///
  21. /// Adds a new task
  22. ///
  23. public static void AddTask(string title, bool complete)
  24. {
  25. using (TodoListDataContext db = new TodoListDataContext())
  26. {
  27. db.AddTask(title, complete);
  28. }
  29. }
  31. ///
  32. /// Updates a tasks state
  33. ///
  34. public static void UpdateTaskState(int taskId, bool complete)
  35. {
  36. using (TodoListDataContext db = new TodoListDataContext())
  37. {
  38. db.UpdateTaskState(taskId, complete);
  39. }
  40. }
  41. }
  42. }

Each method of the TasksBll.cs (Figure 4-12) performs the appropriate operation by accessing methods defined in our DAL.

Enter ASP.NET…

As previously mentioned we will use ASP.NET to define the UI.

The UI will consist of 3 controls:

  • DropDownList
  • GridView
  • FormView

Before we begin add a new AJAX Web Form to your project, the name you give it is irrelevant. Add all the following code snippets inside the form tag of the page.

We have two states

Either a task is active or complete. We will use a drop down list control to store these values (Figure 4-12).

Figure 4-13: DropDownList containing the tasks states

  1. <asp:DropDownList ID=“ddlState” runat=“server” AutoPostBack=“true”>
  2. <asp:ListItem Selected=“True” Text=“Active” Value=“false” />
  3. <asp:ListItem Text=“Complete” Value=“true” />
  4. </asp:DropDownList>


Adding an ObjectDataSource

Before we add any more controls to our web page we need to hook our presentation layer up with our BLL. To do this we will add an ObjectDataSource and wire it up to the appropriate methods defined in our BLL.

Figure 4-14: Adding an ObjectDataSource

  1. <asp:ObjectDataSource
  2. ID=“odsTasks”
  3. runat=“server”
  4. TypeName=“TasksBll”
  5. SelectMethod=“GetTasksByState”
  6. InsertMethod=“AddTask”
  7. UpdateMethod=“UpdateTaskState”>
  8. <SelectParameters>
  9. <asp:ControlParameter
  10. ControlID=“ddlState”
  11. Name=“complete”
  12. PropertyName=“SelectedValue”
  13. Type=“Boolean” />
  14. </SelectParameters>
  15. <UpdateParameters>
  16. <asp:Parameter Name=“taskId” Type=“int32″ />
  17. <asp:Parameter Name=“complete” Type=“boolean” />
  18. </UpdateParameters>
  19. <InsertParameters>
  20. <asp:Parameter Name=“title” Type=“string” />
  21. <asp:Parameter Name=“complete” Type=“boolean” />
  22. </InsertParameters>
  23. </asp:ObjectDataSource>


Using a GridView to view the tasks

We will now go ahead and add a GridView to our page (Figure 4-15).

Note: I have customized the Item and Edit templates for the checkbox control.

Figure 4-15: Adding a GridView to our page to display the tasks

  1. <asp:GridView
  2. ID=“gvTasks”
  3. runat=“server”
  4. DataSourceID=“odsTasks”
  5. AutoGenerateColumns=“false”
  6. DataKeyNames=“TaskID”
  7. AllowPaging=“true”
  8. GridLines=“None”
  9. AlternatingRowStyle-CssClass=“alternate”>
  10. <Columns>
  11. <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
  12. <asp:TemplateField HeaderText=“Complete”>
  13. <ItemTemplate>
  14. <asp:CheckBox ID=“cbI” runat=“server” Checked=”
  15. Enabled=“false” />
  16. </ItemTemplate>
  17. <EditItemTemplate>
  18. <asp:CheckBox ID=“cbE” runat=“server” Checked=” />
  19. </EditItemTemplate>
  20. </asp:TemplateField>
  21. <asp:CommandField ShowEditButton=“true” />
  22. </Columns>
  23. </asp:GridView>


Using the FormView to add more tasks

To add a new task we will use a FormView (Figure 4-16).

Note: I have not included any data input checking to keep the example as simple as possible.

Figure 4-16: Adding a FormView to allow the user to add new tasks

  1. <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
  2. <InsertItemTemplate>
  3. <p><strong>Title:</strong>
  4. <asp:TextBox ID=“txtTitle” runat=“server” Text=” /></p>
  5. <p><strong>Complete:</strong>
  6. <asp:CheckBox ID=“cbC” runat=“server” Checked=” /></p>
  7. <p><asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” /></p>
  8. </InsertItemTemplate>
  9. </asp:FormView>


Adding AJAX functionality to the GridView and the FormView controls

Just to smooth things up I’ve added a few UpdatePanel’s to the page, each wrapping the GridView and FormView respectively.

Figure 4-17: The updated GridView

  1. <asp:UpdatePanel ID=“upTasks” runat=“server”>
  2. <ContentTemplate>
  3. <asp:GridView
  4. ID=“gvTasks”
  5. runat=“server”
  6. DataSourceID=“odsTasks”
  7. AutoGenerateColumns=“false”
  8. DataKeyNames=“TaskID”
  9. AllowPaging=“true”
  10. GridLines=“None”
  11. AlternatingRowStyle-CssClass=“alternate”>
  12. <Columns>
  13. <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
  14. <asp:TemplateField HeaderText=“Complete”>
  15. <ItemTemplate>
  16. <asp:CheckBox ID=“cbI”
  17. runat=“server”
  18. Checked=”
  19. Enabled=“false” />
  20. </ItemTemplate>
  21. <EditItemTemplate>
  22. <asp:CheckBox ID=“cbE” runat=“server” Checked=” />
  23. </EditItemTemplate>
  24. </asp:TemplateField>
  25. <asp:CommandField ShowEditButton=“true” />
  26. </Columns>
  27. </asp:GridView>
  28. </ContentTemplate>
  29. <Triggers>
  30. <asp:AsyncPostBackTrigger ControlID=“ddlState” />
  31. </Triggers>
  32. </asp:UpdatePanel>


Figure 4-18: The update FormView

  1. <asp:UpdatePanel ID=“upAdd” runat=“server”>
  2. <ContentTemplate>
  3. <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
  4. <InsertItemTemplate>
  5. <p><strong>Title:</strong>
  6. <asp:TextBox ID=“txtTitle” runat=“server” Text=” /></p>
  7. <p><strong>Complete:</strong>
  8. <asp:CheckBox ID=“cbC” runat=“server” Checked=” /></p>
  9. <p>
  10. <asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” />
  11. </p>
  12. </InsertItemTemplate>
  13. </asp:FormView>
  14. </ContentTemplate>
  15. </asp:UpdatePanel>

And on that note we are finished! You can download the full source code at the end of this tutorial.

The experience

From personal experience coding the data access layer can be a very boring process – in this tutorial we have seen how effective LINQ to SQL can be when creating our DAL.

Visual Studio Orcas has introduced a few really cool tools to further assist with the creation of the DAL – we no longer need to spend hours on OR mapping. The great thing about LINQ is the ability to further query your DAL, and create some anonymous type.

There is no doubt in my mind that LINQ is set to revolutionize the way programmers interact with data, more so is the fact that we always use a familiar set of standard query operators!

If you download and run the source you will see the site shown in Figure 4-19.

Figure 4-19: Our to-do list web site

ToDoList Xlinq4 Introducing LINQ – Part 4

Summary

This tutorial showed you how to update the to-do list application Scott Guthrie did some time ago to use LINQ. I really hope that this tutorial has shown you how simply you can build an application using LINQ, and I really do encourage you to play around with the sample code and further extend the application.

view original

Introducing LINQ – Part 3

Posted by on April 27, 2007 No comments

Salute to Granville Barnett for his third one! Yes, I am talking about the third article about introducing LINQ.
Here is an example:

“In Part 2 we took a look at LINQ to SQL, how to generate an entity, and also how to query that entity. In this part we will look a little more at what entities are, as well as taking a closer look at the key types we can use and their application.“

view original

Introducing LINQ – Part 2

Posted by on April 11, 2007 No comments

Granville Barnett has provided us with his second part of his ‘Introducing LINQ’ articles. In the first one he explained LINQ basics and gave some examples. In that one he goes further by giving a specific example how do we use LINQ to SQL file.

“Conceptual data access layer?

Conceptual what?! If you are familiar with the concept of a database schema then you don’t have much to worry about! You can think of a conceptual data access layer as being your applications view of the database.

I don’t want to bog you down with entities yet! That will come in part 3 of this series. Just know that what we deal with from our applications point of view is a series of entities describing the tables and relationships in our database schema. I will let you look forward to the diagrams explaining all of this in the next part!“

Here is the original!