Ajaxus place on the net

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:

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:

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#:

VB:

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#:

VB:

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#

VB:

4) Delete Products from the Database

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

C#:

VB:

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#:

VB:

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#:

VB:

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

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

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

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

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

Figure 4-4: Defining the columns

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

Figure 4-5: Saving the table

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

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

Figure 4-7: AddTask

create procedure AddTask @Title nvarchar(50), @Complete bit as insert into Tasks (Title, Complete) values (@Title, @Complete)

Figure 4-8: GetTasksByState

create procedure GetTasksByState @Complete bit as select TaskID, Title, Complete from Tasks where Complete = @Complete order by Title

Figure 4-9: UpdateTaskState

create procedure UpdateTaskState @TaskID int, @Complete bit as update Tasks set Complete = @Complete 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.

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

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

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

using System; using System.Collections.Generic; using System.Linq; namespace Org.GBarnett.Dns { public static class TasksBll { /// /// Gets all tasks of a particular state /// public static List GetTasksByState() { using (TodoListDataContext db = new TodoListDataContext()) { return (from t in db.GetTasksByState() select t).ToList(); } } /// /// Adds a new task /// public static void AddTask(string title, bool complete) { using (TodoListDataContext db = new TodoListDataContext()) { db.AddTask(title, complete); } } /// /// Updates a tasks state /// public static void UpdateTaskState(int taskId, bool complete) { using (TodoListDataContext db = new TodoListDataContext()) { db.UpdateTaskState(taskId, complete); } } } }

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

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

Using a GridView to view the tasks

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

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

<asp:checkbox id=”cbI” runat=”server” checked=”” Enabled=”false” /> <asp:checkbox id=”cbE” runat=”server” checked=”” />

Using the FormView to add more tasks

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

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

<p><strong>Title:</strong> <asp:textbox id=”txtTitle” runat=”server” text=”” /></p> <p><strong>Complete:</strong> <asp:checkbox id=”cbC” runat=”server” checked=”” /></p> </p> <p>

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

<asp:checkbox id=”cbI” runat=”server” checked=”” Enabled=”false” /> <asp:checkbox id=”cbE” runat=”server” checked=”” />

Figure 4-18: The update FormView

<p><strong>Title:</strong> <asp:textbox id=”txtTitle” runat=”server” text=”” /></p> <p><strong>Complete:</strong> <asp:checkbox id=”cbC” runat=”server” checked=”” /></p> </p> <p>

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

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

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

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!

Introducing LINQ is the first part of a series of articles on Language Integrated Query (LINQ). This series will cover the core essentials of LINQ and its use with other technologies like ASP.NET, Win Forms and WPF.

This and the forthcoming articles are designed to be a comprehensive tutorial on LINQ, having completed the series you will have a thorough understanding of LINQ and C# 3.0.

All code in the series will use the latest publicly available CTP at the time of writing.
view source

Interesting subject which Daniel Moth explains:
At a recent event where I was presenting on LINQ, I showed a query with a join, similar to the following:

      var results =      from p in Process.GetProcesses()      join p2 in MyProccess.GetMyProcList()      on p.ProcessName equals p2.MyProcName      where p.Threads.Count > 14      orderby p.ProcessName descending      select new { p2.MyProcDescription, ThreadCount = p.Threads.Count, p.Id };

After the session one of the delegates asked me: “Why do we have to use the equals keyword and not just ==“. In other words he would have preferred to type:
on p.ProcessName == p2.MyProcName
I didn’t have a good answer but promised to look into it.

view original