Microsoft Study Bible

November 3, 2009

Linq to SQL: Achieve Link Search and Criteria Queries

Filed under: Developer tools and applications — Tags: , , , , , — Jackson @ 9:39 pm

There is a need to display all the information of the members (MemberInfo) , Sorting  by SortNumber column of which the Member type is zero in Member Sort table .

 Table structure and the relevant test data scripts are as follows:

create table MemberInfo
(
    MemberID int,
    MemberName nvarchar(50)
)
create table MemberSort
(
    MemberId int,
    SortNumber int,
    MemberType int
)
insert into MemberInfo values (1,‘A’)
insert into MemberInfo values (2,‘B’)
insert into MemberInfo values (3,‘C’)
insert into MemberInfo values (4,‘D’)
insert into MemberInfo values (5,‘E’)

insert into MemberSort values (1,5,0)
insert into MemberSort values (2,4,0)
insert into MemberSort values (3,3,1)

–Drop table MemberInfo
–Drop table MemberSort

Using the traditional SQL mode query code

SELECT [t0].[MemberID], [t0].[MemberName]
FROM [dbo].[MemberInfo] AS [t0]
LEFT OUTER JOIN [dbo].[MemberSort] AS [t1] ON ([t1].[MemberType] =0) AND ([t0].[MemberID] = [t1].[MemberID])
ORDER BY
    (CASE
        WHEN [t1].[SortNumber] IS NOT NULL THEN [t1].[SortNumber]
        ELSE 9999
     END)

Hope that the same support can be achieved in Linq to SQL .

Firstly, try out the failure code:

var data = from m in db.MemberInfo
                           join s in db.MemberSort on m.MemberID equals s.MemberID && s.MemberType == 0
                           into x
                           from cx in x.DefaultIfEmpty()
                           orderby cx.SortNumber.HasValue ? cx.SortNumber.Value : 9999
                           select m;

The above code revised is as follows:

var data = from m in db.MemberInfo
                           join s in db.MemberSort on m.MemberID equals s.MemberID
                           into x
                           from cx in x.Where(d => d.MemberType == 0).DefaultIfEmpty()
                           orderby cx.SortNumber.HasValue ? cx.SortNumber.Value : 9999
                           select m;

The key statement is “from cx in x.where (d=>d.MemberType==0).DefaultEmpty();”,which means that it will return the data collection   of which  the MemberType==0 or the value of  MemberType does not exist in Connection Portfolio.

Share This

October 28, 2009

LINQ to SQL common BaseClass

Filed under: Developer tools and applications, Server technologies — Tags: , , , , , , — Jackson @ 9:46 pm

LINQ is a series of new features provided by Visual Studio 2008, which will expand C# and Visual Basic and can provide powerful query capabilities. As part of LINQ, LINQ to SQL can provide a framework on which the relational data is used as object to run. In one way, it is equal to NHibernate and Castle provided by Microsoft. When we need to access the database, LINQ to SQL will become our first choice.

In LINQ to SQL，all varies in relational database model are strongly typed ,which can provide verification and intelisense when they are being complied .And we can use Query expression including query syntax and method syntax, to access data from the database.

 

1. Achieve where method

 

 However，the strongly-typed is not beneficial to do abstract operation on the data .So, the developer had to define some particular class for each entity object, which will cause to a lot of duplicate code .If we can achieve a common BaseClass and encapsulate Public Data operation such as Select ,Where ,Add ,Update and Delete ,it can be useful for us to develop the N-tier application development.

Fortunately, Generics Types can help us make it .The way is to call GetTable<T>() method of  DataContext .For example ,we can achieve where method by passing a Lambda expression to find the result we want to get.

 public IList<TEntity> Where(Func<TEntity, bool> predicate)
{
    InitDataContext();
    return m_context.GetTable<TEntity>().Where(predicate).ToList<TEntity>();
}

 

And，we can even use Dynamic Query to expose some methods to receive conditional expression：

public static class DynamicQueryable
{
    public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values)
    {
        return (IQueryable<T>)Where((IQueryable)source, predicate, values);
    }
public static IQueryable Where(this IQueryable source, string predicate, params object[] values)
    {
        if (source == null) throw new ArgumentNullException(“source”);
        if (predicate == null) throw new ArgumentNullException(“predicate”);
        LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(bool), predicate, values);
        return source.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable), “Where”,              
                new Type[] { source.ElementType },
                source.Expression, Expression.Quote(lambda)));
    }
}
public IList<TEntity> Where(string predicate, params object[] values)
{
    InitDataContext();
    return m_context.GetTable<TEntity>().Where(predicate, values).ToList<TEntity>();
}

 

Data Entity operation

Of course, for an AbstractBaseClass, there is no problem to query .Because there is no need to care the properties of the entity or the composition of the expression Lambda when we call these methods. (more…)

Share This

October 26, 2009

What does ASP.NET 3.5 Extensions bring to us?

Filed under: Developer tools and applications — Tags: , , , , — Jackson @ 9:32 pm

1. Overview

The release of the new technology .NET 3.5 and Visual Studio 2008 makes milestone contribution to the .NET strategy of Microsoft. In the Web development, it includes a powerful HTML Web designer which provides split-view editing, and the perfect CSS. Meanwhile, it makes its support to JavaScript better, including Intelligent hints and debug.

 

Microsoft issued the first preview version of ASP.NET 3.5 Extensions only 20 days after it released .NET 3.5. The final version is released in the first half of the year 2008, which contains more features of ASP.NET:

1. ASP.NET MVC Framework

2. Improvement of ASP.NET AJAX

3. ASP.NET Dynamic Data support 

4. ASP.NET Silverlight support

5. ADO.NET Data services

 

In this article, I will display some of the new features of ASP.NET 3.5 Extensions though several simple examples.

2. ASP.NET MVC framework

The concept of MVC has been raised for many years. It divides the application implementation into three parts: Model to maintain the data status, View to display the user interface, Controller to handle the user interaction, operate Model and select View to display the data. The first concern of ASP.NET MVC Framework is separation for testing more conveniently. Meanwhile, it uses aspx page, template page and user control as View and provides powerful URL Routing Engine. I will show these features by examples.

 

Step1: set up Model, use LINQ to SQL to establish a Product data model

 

 

 

Step2： set up View, display the detailed information of Pdoduct by using an aspx page which inherited from a Generic ViewPage：

 

public partial class Product_Product : ViewPage<ProductInfo>
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
 

 

 

 

   show the data in aspx page, use <%=%> directly to label output 

<div>
<h2>ProductDetail:</h2>
ProductId

：<%= ViewData.ProductId %><br /><br />
Name：<%= ViewData.Name %><br /><br />
Descn：<%= ViewData.Descn %>
</div>
 
 

 

 

Step3：set up Controller，inherit from Controller base class，and add Action，at the same time, use product view to realize the data function and input data object that needs to be displayed.

public class ProductController : Controller
{
[ControllerAction]
public void Index()
{
MSPetShopDataContext db = new MSPetShopDataContext();
ProductInfo productinfo = db.ProductInfos.Single(p => p.ProductId == “BD-03″);
RenderView(”Product”, productinfo);
}
}
 

 

 

 

Step4：select Path for Configuration，make configurations Application_Start：

 

void Application_Start(object sender, EventArgs e)
{
RouteTable.Routes.Add(
new Route
{
Url = “[controller]/[action].mvc”,
Defaults = new { action = “Index”},
RouteHandler = typeof(MvcRouteHandler)
}
);
}
 
 

 

basicly, the proceedings are the four steps for setting up a application based on ASP.NET MVC Framework.

   


3. ASP.NET AJAX improvement
 
 

 

In the ASP.NET 3.5 Extensions, the most important improvement to ASP.NET AJAX is a better support to browser history that we can control the Back and Forward button more conveniently. It provides two ways for us to choose, using server-side control or Client-script. In the following, I will illustrate how to use server-side control to control the browser history.


       add ScriptManager Control， and set its EnableHistory attribute as true，permit the history management of browser and OnNavigate handled as OnNavigateHistory function for dealing with navigation event. At the same time, set the EnableStateHash attribute as false to make modifications easier to perform. In real situations, we can decide whether to perform the Hash encryption as required:

 

<asp:ScriptManager runat=”server” ID=”ScriptManager1″
OnNavigate=”OnNavigateHistory”
EnableHistory=”true”
EnableStateHash=”false” />

 

Create Browser history point at the time of clicking button with the way of AddHistoryPoint：

 

 

public void ButtonClick(object sender, EventArgs e)
{
LabelHistoryData.Text = ((Button)sender).Text;
ScriptManager.GetCurrent(this).AddHistoryPoint(key, LabelHistoryData.Text);
} 


 

Navigation processing：

public void OnNavigateHistory(object sender, HistoryEventArgs e)
{
LabelHistoryData.Text = Server.HtmlEncode(e.State[key]);
} 


 

For demonstration，three button should be added on the page：

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="Button1" EventName="Click" />
<asp:AsyncPostBackTrigger ControlID="Button2" EventName="Click" />
<asp:AsyncPostBackTrigger ControlID="Button3" EventName="Click" />
</Triggers>
<ContentTemplate>
<asp:Panel runat="server" CssClass="box" ID="Content" Height="40px">
Date and Time:
<%= DateTime.Now.ToLongTimeString() %>
<br />
Page's refresh state:
<asp:Label runat="server" ID="LabelHistoryData" />
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>
<p />
<asp:Button runat="server" ID="Button1" Text="Key 1" OnClick="ButtonClick" />
<asp:Button runat="server" ID="Button2" Text="Key 2" OnClick="ButtonClick" />
<asp:Button runat="server" ID="Button3" Text="Key 3" OnClick="ButtonClick" /> 


 

  

In the preceding, I mainly use the server-side approach to realize the support to browser history in ASP.NET AJAX, you can also use client script to manage browser history.

 

 

 

 

 

 

Share This

Powered by WordPress

Close
E-mail It