softlogger Latest Articles ::DatabaseSQL

A future world without the SQL Server Image/Text/NText data types. Now's the time to start planning for that future...

Gregs Cool [Insert Clever Name] of the Day — 2008-03-23T00:00:00

Jose Barreto's Blog - SQL Server 2008 and Unstructured Data

"SQL Server 2008 introduces two new options to store unstructured data, in addition to the current BLOB support we have in SQL Server 2005.

IMAGE

Back in SQL Server 2000 and SQL Server 7 days, we used the IMAGE data type to store binary large objects (a.k.a. BLOBs). Large, by the way, is defined as anything that would not fit a SQL Server data page (around 8,000 bytes). This data type is still present in SQL Server 2005 today, but it should be going away soon. The current recommendation is to “avoid using these data types in new development work, and plan to modify applications that currently use them.” This applies also to the similar TEXT and NTEXT data types. [GD: Emphasis added]

This is described in more detail at http://msdn2.microsoft.com/en-us/library/ms187993.aspx.

VARBINARY

To replace those, SQL Server 2005 introduced the concept of using the VARBINARY(MAX) data type....

FILESTREAM

A new option in SQL Server 2008 is to use a FILESTREAM attribute in a VARBINARY column. This allows you to store the actual data on the server’s file system...

RBS

SQL Server 2008 also introduces a new client-side API that allows for storing BLOBs on a Remote BLOB store (RBS).

..."

SQL Server 2005 Books Online (September 2007) - ntext, text, and image (Transact-SQL)

"Important:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

..."

We've been warned. SQL Server 2011 without the Image/Text/NText data types? Could be...

Introducing SQL Server Data Services ( SSDS ) by Nigel Ellis - Mix08 Video Download of Presentation

David Hayden - Florida .NET Developer - C# and SQL Server — 2008-03-12T00:00:00

The speakers for the Microsoft Sync Framework, ADO.NET Data Services, and SQL Server Data Services mentioned the one-two punch you get from leveraging these services together. SQL Server Data Services is a web facing data storage and processing utility to be launched by Microsoft during the first-half of 2009...

Let Live Search know when you update your website

.NET slave — 2008-03-12T00:00:00

UPDATE: Google also has a ping service that works in the exact same way. You can find it at http://www.google.com/ping?sitemap=[your sitemap URL]

Microsoft’s Live Search along with Yahoo and Ask decided some time ago to support Google’s XML sitemap format. So if you already have added support for a Google sitemap on your website, it now also works on the other major search engines.

The new thing is that Live Search have added a ping service that let’s you notify it when your sitemap has been updated. Then Live Search will index the changes and make them appear faster in the search results (or at least so I think, otherwise it wouldn’t make much sense to have a ping service).

It’s very simple to ping Live Search. Simply make a request to http://webmaster.live.com/ping.aspx?siteMap=[your sitemap URL] in any way that suits you best. Do it in a browser manually or let your CMS or blogging engine do it for you.

For BlogEngine.NET users

If you use BlogEngine.NET then you already have a compatible sitemap. It’s located at example.com/sitemap.axd. See my sitemap here. I’ve written an extension that sends the ping automatically whenever a post or page is created or edited and when a comment is added.

Download the extension below, unzip it and place the LiveSearchPinger.cs file in the App_Code/Extensions folder of your BlogEngine.NET website.

LiveSearchPinger.zip (727 bytes)

Decompiling Silverlight 2 Apps – Enter the XAP

The Moth — 2008-03-09T00:00:00

When you build a Silverlight 2 application, you compile your source files (inc. XAML and embedded resources) into assemblies (dlls). Those assemblies (plus any 3rd party ones plus MS assemblies that are not part of Silverlight) are then collected together along with a manifest into a packaged archive – or in other words, a ZIP file. Except, the extension is changed to be a .XAP (pronounced "ZAP"). The benefits of packaging everything into a XAP should be obvious but in case they are not, Pete has discussed that on his blog (inc. screenshots). Note that if your web server is on IIS (prior to version 7) you need to register that MIME type. As an aside, my web server happens to be Apache (shocking I know!) and I had to configure nothing – it just worked.

So, if I had given you a Winforms EXE file you wouldn't ask me for the source code, you would probably just pass it through your favourite decompiler and you've got it all. Same with Silverlight apps: if I point you to my web page that is running a Silverlight app (e.g. the one from this post) all you have to do is:

1. View HTML source, e.g. for this: http://www.danielmoth.com/SL/HtmlBridge/index.html

2. Locate the tag that points to a XAP file and copy the full path, e.g. ClientBin/DemoIntegration.xap

3. Use that path to "Save As" the xap file locally.

4. Rename the XAP file to ZIP and explode it to get the DLLs inside.

5. Party away by tossing the DLL(s) to the decompiler, e.g. Reflector.

Obviously authors of serious SL apps may not condone that behaviour, but feel free to do it with all of mine, for example the DeepZoom/MultiScaleImage that I have now updated to also use mouse wheel.

Building Great AJAX Applications from Scratch Using ASP.NET 3.5 and Visual Studio 2008 - MIX 08 Presentation

David Hayden - Florida .NET Developer - C# and SQL Server — 2008-03-07T00:00:00

Building Great AJAX Applications from Scratch Using ASP.NET 3.5 and Visual Studio 2008 - MIX 08 Presentation

by David Hayden, Florida ASP.NET Web Developer

Brad Abrams hit a homerun with his MIX 08 Presentation on Building Great AJAX Applications from Scratch Using ASP.NET 3.5 and Visual Studio 2008.

I watched the video and was really, really impressed with his coverage of:

Visual Studio 2008 CSS and Style Sheet Management
LINQ To SQL Basics
Introduction to the new ListView and DataPager Controls in .NET 3.5 Framework
Using the ASP.NET AJAX Server-Side UpdatePanel and UpdateProgress Controls
Client-Side AJAX Calls using JavaScript to WCF Services
Use of the AJAX Control Toolkit for a Richer User Exprerience
Switching Between Internet Explorer and Firefox / FireBug
Demonstrating a ScriptCombining Feature that will be released in a .NET 3.5 SP1 it seems

A nice and realistic list of AJAX Website Features demonstrated on-the-fly that one would want to use in a real-world application.

He even posted a great summary of his talk as well as provided the sample code for download on his blog. Still, I would watch the video because he does a great job of walking you through building the ASP.NET AJAX Web Application piece-by-piece.

MIX 08 Sessions Viewed

Developing Data-Driven Web Applications Using ASP.NET Dynamic Data

ASP.NET AJAX Tutorials

News Feed: http://www.davidhayden.com/

SQL Server 2008 FILESTREAM - Writing a file to a FILESTREAM column

Gregs Cool [Insert Clever Name] of the Day — 2008-03-04T00:00:00

Zach Skyles Owens - SQL Server 2008 FILESTREAM and WPF MediaElement - Part 2 (Writing FILESTREAM Data)

Wow... It's been a long time coming. I promised that I would explain in more detail how to write FILESTREAM data to SQL Server 2008. This is the second article in a series and uses the sample published on the SQL Server Community Samples site on CodePlex.
Writing data to a varbinary(max) FILESTREAM column in SQL is a bit more involved then just opening a file on the filesystem. SQL Server needs to manage this operation within a transaction which adds a bit of complexity. Here are the basic steps... These steps apply both to reading and writing.
Start a SQL Server transaction
Insert a row into the table containing metadata
Select the PathName from SQL Server which will be used to get a handle
Open a handle for writing using sqlncli10.dll
Use that handle within System.IO classes
Commit the transaction
Now that the basic steps are laid out, let's take a closer look
...
So... Here's where I think things get interesting. You can see that the SELECT statement above calls the PathName() method of the FILESTREAM column. Here's an example of the results from the query above. Note the use of UDTs and CLR!
\\ZOWENS-NB3\MSSQLSERVER\v1\FilestreamWpfHttp\dbo\media\file\4C3C9C2D-8268-43FF-8317-D507319FE21C
This is a "virtual" path managed by SQL Server. It consists of \\COMPUTER_NAME, followed by a configurable handler \MSSQLSERVER...
...
The code above uses a simple C# class "SqlNativeClient" that wraps the sqlncli10.dll I mentioned above. This C# class is key to working with FILESTREAM in managed code. You can see that we passed in the sqlFilePath variable from the PathName() query.
// Open up a new stream to write the file to the blob.
System.IO.FileStream destBlob = new System.IO.FileStream(handle, FileAccess.Write);
"Old school" System.IO file manipulation using the handle obtained from the SqlNativeClient class above.
// Commit transaction       
txn.Commit();
There you have it... It's not rocket science but there are a few tricks."

This article shows the magic behind the new FILESTREAM column attribute. It shows how it's not just some kind of uber-BLOB, but the delegating of responsibility based on capability. Showing how the SQL does what it is good at, while delegating to the file system to do what it is good it (i.e. file operations, reading, writing, etc)

Interesting... But I'm a little afraid of getting too much into this. What if it doesn't work out and MS kills it in a release or two? (Think Exchange M: drive, Notification Services, etc)

Still it's pretty darn cool and helps solve a number of problems we have today...

T-SQL Update or Insert Logic -

Gregs Cool [Insert Clever Name] of the Day — 2008-02-20T00:00:00

Jeremiah Clark's Blog - SQL: If Exists Update Else Insert

"This is a pretty common situation that comes up when performing database operations. A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not. If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach. ...

...

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

..."

Interesting... This is an approach I would not have thought of, and it's not something that I don't know I would have ever thought of. It just doesn't "fit" in my developer mind set.

But now that I see it, it makes sense. It's something I'm going to have to think about and test anyway. I've got a few databases/tables with 80+ million rows, so a small improvement could lead to big results.

Building an N-Layer ASP.NET Application with LINQ, Lambdas and Stored Procedures

Dan Wahlins WebLog — 2008-02-19T00:00:00

.NET 3.5 has a lot of great new features that can significantly enhance developer productivity. I've been spending some time lately working on a little sample application that demonstrates how an N-Layer ASP.NET 3.5 application can be built using LINQ, lambdas and LINQ with stored procedures. The application is for a talk I'll be giving at DevConnections in April discussing how LINQ technologies can be used in an N-Layer architecture. In a previous post comparing different LINQ options I mentioned that I'd be posting the code download as soon as it was ready. You can now download it here if you're interested.

The application provides a presentation layer, business layer, data layer and model layer through separate projects as shown next:

It also demonstrates how the new ListView control can be used to display data, perform insert, update and delete operations and nest other controls such as the GridView. Databinding on the presentation layer is mainly done using the ObjectDataSource control.

All of the queries performed in the application go against an object model created using the Visual Studio 2008 LINQ to SQL Designer.

Note: The included Northwind SQL Express database has been modified slightly to add a TimeStamp field into the Customer and Orders tables and contains several custom stored procedures (sprocs aren't required unless using that portion of the application). Adding TimeStamp fields simplifies updates so be aware that if you change the connection string to point to a standard Northwind database you'll get an error since the TimeStamp fields will be missing.

3 Options for Data Access

Rather than focusing solely on LINQ, I wanted to show different options for data access that .NET 3.5 offers so that developers can get a feel for what's available in addition to standard LINQ queries that seem to get most of the attention these days. I ended up creating six main data layer classes as shown next:

Customer Query Classes:

CustomerDBLINQ - Executes customer related queries using inline LINQ
CustomerDBLambda - Executes customer related queries using lambda expressions
CustomerDBSprocs - Executes customer related queries using stored procedures and LINQ

Order Query Classes:

OrderDBLINQ - Executes order related queries using inline LINQ
OrderDBLambda - Executes order related queries using lambda expressions
OrderDBSprocs - Executes order related queries using stored procedures and LINQ

I still lean toward using stored procedures due to the security and maintenance benefits they offer in more enterprise environments, but for small queries I actually prefer lambda expressions over LINQ (not sure why...just feels more object oriented I guess). If you currently use stored procedures in your applications and haven't checked out the new LINQ to SQL Designer you'll be impressed with how easy it is to call stored procedures and pass parameters. You never have to see or create another SqlCommand or SqlParameter object again (well...in many cases anyway).

Switching Between Data Access Classes

By changing a value in web.config you can switch between the different data layer classes and see which option you prefer (LINQ, lambdas or LINQ with sprocs). All of the data access classes perform the same overall tasks, they just use different techniques to do it.

<appSettings>
  <!-- 
    Used to define which DB layer class should be loaded and used. 
    Valid customer values include:  Data.CustomerDBSprocs, Data.CustomerLINQ, Data.CustomerLambda
    Valid order values include: Data.OrderDBSprocs, Data.OrderLINQ, Data.OrderLambda
  -->
  <add key="CustomerDBType" value="Data.CustomerDBLINQ" />
  <add key="OrderDBType" value="Data.OrderDBLINQ" />
  <!-- When the following key is set to "true" ensure that 
       EnablePartialRendering is set to false on the Default.aspx ScriptManager control -->
  <add key="EnableDataContextLogging" value="false" />
</appSettings>

Over the next few weeks I'm hoping to make some time to walk through the application pieces. I may create some video tutorials about it as well....we'll see how time goes.

del.icio.us Tags: N-Layer,LINQ,Lambdas,Stored Procedures,ListView,ASP.NET

High Performance LINQ To SQL - Compiled Queries - O/R Mappers - Ecommerce Websites

David Hayden - Florida .NET Developer - C# and SQL Server — 2008-02-19T00:00:00

High Performance LINQ To SQL - Compiled Queries - O/R Mappers - Ecommerce Websites

by David Hayden, Florida ASP.NET Developer, Filed: LINQ To SQL

It should be of no surprise that an O/R Mapper will add overhead to performance as compared to the performance one would receive from a DataReader in ADO.NET. LINQ To SQL is no different. It adds a lot of services on top of ADO.NET, such as:

Connection Management
Query Translation and Execution
Object Identity
Change Tracking

This, of course, all comes at a little bit of a performance overhead. For most applications and user stories the slower performance is irrelevant. The value of the O/R Mapping services greatly outweigh any degradation in performance.

Sometimes, however, there may be a couple of database activities that are very expensive and we may want to achieve as optimal performance as possible even at the expense of maintainability. LINQ To SQL has a solution for these times - Compiled Queries.

LINQ To SQL Compiled Queries

Let's pretend we have an e-commerce website and getting Products By Category is an expensive operation. We have optimized the database as much as we can using proper indexing and covering indexes and it just isn't enough. We are using LINQ To SQL and we decide it is time for Compiled Queries. LINQ To SQL Compiled Queries allow the translation of the query to happen once at compile time and the query to be consumed many times. The lack of translation at each call will greatly improve performance for our e-commerce store.

Using Northwind, I create a partial class of the NorthwindDataContext and add my compiled query:

public partial class NorthwindDataContext
{
    public static Func<NorthwindDataContext, int, IQueryable<Product>>
        ProductsByCategoryID = CompiledQuery.Compile
            ((NorthwindDataContext context, int categoryID) =>
            context.Products.Where(p => p.CategoryID == categoryID));

    public IQueryable<Product> GetProductsByCategoryIDFast(int
                                                        categoryID)
    {
        return ProductsByCategoryID(this, categoryID);
    }
}

Everything above is compiled except for the category I am searching on. Note we are using Expression Trees, which are new to C# 3.0. This will greatly improve the performance of getting the products by category in my e-commerce store.

Now in my e-commerce website I will use the higher performance method of accessing the products in my product catalog by category:

NorthwindDataContext db = new NorthwindDataContext();

List<Product> products = db.GetProductsByCategoryIDFast(1).ToList();

Here I am getting all the products in my product catalog in the CategoryID = 1. The improvement in performance is much better and very close to the performance of the speed of the DataReader in ADO.NET. This should make my e-commerce website perform much quicker than when not using a LINQ To SQL Compiled Query.

For more information on the performance one achieves by using LINQ To SQL Compiled Queries, I recommend reading Rico Mariana's blog:

http://blogs.msdn.com/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

There are actually several parts to his LINQ To SQL performance testing that you can read on his blog.

Conclusion

LINQ To SQL offers the option of Compiled Queries when you need to achieve DataReader like performances in ADO.NET. This comes at a bit of a price of maintenance, because you will need to create compiled queries for those operations you feel are expensive.

LINQ To SQL Tutorials

Author: David Hayden, Florida Website Developer

Site: http://www.davidhayden.com/

Debugging Custom Actions: Leaked Handles

Heath Stewarts Blog — 2008-02-12T00:00:00

Debugging custom actions isn't exactly the easiest action to do; Windows Installer spawns separate processes - remoting servers - to run in-process custom actions. Any opportunity to diagnose issues without debugging can be helpful.

For example, an issue that comes up from time to time is leaked handles. A Windows Installer debug log might show something similar to the following:

MSI (s) (CC:1C) [17:40:29:055]: Doing action: MyCustomAction MSI (s) (CC:58) [17:40:29:165]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI1B.tmp, Entrypoint: MyCustomAction Action start 17:40:29: MyCustomAction. MSI (s) (CC:58) [17:40:29:906]: Leaked MSIHANDLE (219) of type 790531 for thread 3676 MSI (s) (CC:58) [17:40:29:906]: Leaked MSIHANDLE (190) of type 790540 for thread 3676 MSI (s) (CC:58) [17:40:29:906]: Leaked MSIHANDLE (189) of type 790540 for thread 3676 MSI (s) (CC:58) [17:40:29:906]: Leaked MSIHANDLE (188) of type 790540 for thread 3676 MSI (s) (CC:58) [17:40:29:906]: Leaked MSIHANDLE (179) of type 790541 for thread 3676 MSI (s) (CC:58) [17:40:29:906]: Note: 1: 2769 2: MyCustomAction 3: 5 DEBUG: Error 2769: Custom Action MyCustomAction did not close 5 Action ended 17:40:30: MyCustomAction. Return value 3.

Windows Installer provides a clue in the log that can help with a code review. The Windows Installer team has provided the following information:

MSIHANDLE type 790531 identifies a record
MSIHANDLE type 790540 identifies a view
MSIHANDLE type 790541 identifies a database

With this knowledge, you should be able to review your code and spot cause of the issue.

Being IN in LINQ to SQL - Or How I learned to Love Contains (aka Getting Linq to SQL to generate a T-SQL WHERE clause that includes an IN)

Gregs Cool [Insert Clever Name] of the Day — 2008-01-30T00:00:00

So today was my first time really playing with Linq to SQL (or any flavor of Linq for that matter). I'm prototyping a new feature and wanted to see how Linq could help...

The biggest road block? Trying to figure out how to do a IN in Linq to SQL, i.e. SELECT * FROM table WHERE column IN ('value','another','etc')

Dynamically building OR's wasn't going to cut it. (Linq: ... WHERE C.Column = "Value" or C.Column = "another" or C.Column = "etc" )

And using a Lambda expression in the Linq Where was making my brain hurt.

There had to be a simple and easy way to do this...

Luckily through the power of the search I found a hint for a truly workable and clean solution. One that results in final SQL that includes an IN. The Blomsma Code - LINQ to SQL: Joining database data with non-database data

The magic is with the Contains and to turn the Where on its head.

Here's an example (see the screenshots below). The code pretty much speaks for itself.

The key is the order in the Where clause. I kept looking for an another method, another way, a more T-SQL kind of thing. Some way to say emp.Country IN bla bla or emp.Country.Contains(bla, bla), etc. But nothing would work or result in an IN based SQL Query... That is until I found the hint I needed in the above post.

Now the below code works exactly as I wanted. It builds a T-SQL statement using IN in the WHERE clause, is clean, scalable and easy to understand...

The source code and project is also available here, http://code.msdn.microsoft.com/LinqtoSQLJoinExample (but it's all below too... Yep, that's it, that's all of it... )

Source

Imports System.Data.Linq
Imports System.Data.SqlClient

Public Class Form1




  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim db As New Northwind("Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf")

    Dim Countries() As String = Me.TextBox1.Text.Split(","c)

    Dim query = From emp In db.Employees _
                Where Countries.Contains(emp.Country) _
                Select emp

    db.Log = Console.Out

    Me.DataGridView1.DataSource = query

  End Sub

End Class

Screenshots

T-SQL Generated by Linq to SQL

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [France]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1, @p2)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [France]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- @p2: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

Maintaining Unique Indexes with IGNORE_DUP_KEY

Craig Freedmans WebLog : Aggregation — 2008-01-30T00:00:00

A few months ago, I wrote a post describing how SQL Server maintains unique indexes while avoiding false uniqueness violations. In this post, I'm going to look at how SQL Server maintains unique indexes that were created with the WITH IGNORE_DUP_KEY clause. Normally, if we attempt to insert a duplicate key into a unique index, SQL Server fails the entire statement, rolls back any changes made by that statement, and returns an error. However, if we attempt to insert a duplicate key into a unique index that was created with the WITH IGNORE_DUP_KEY clause, SQL Server merely discards the "bad" row, generates a warning message, and continues executing the remainder of the statement. Note that SQL Server only generates the warning for actual insert statements. Update statements that try to create a duplicate key still fail with an error.

Let's begin with a trivial insert into a unique clustered index:

CREATE TABLE T_SRC (A INT, B INT)

CREATE TABLE T_DST (A INT, B INT)
CREATE UNIQUE CLUSTERED INDEX T_DST_A ON T_DST(A) WITH IGNORE_DUP_KEY

INSERT T_DST SELECT * FROM T_SRC

This insert yields the following rather mundane query plan:

|--Clustered Index Insert(OBJECT:([T_DST].[T_DST_A]), SET:([T_DST].[A] = [T_SRC].[A],[T_DST].[B] = [T_SRC].[B]))
|--Top(ROWCOUNT est 0)
|--Table Scan(OBJECT:([T_SRC]))

If you were expecting something more exciting, I'm sorry for the disappointment. So what's going on here? SQL Server simply tries to insert each row. If any insertion fails due to a duplicate key, SQL Server just converts the error into a warning, discards the failed row, and continues. Since we only have the one clustered index and no non-clustered indexes to maintain, SQL Server either inserts or does not insert each row into the single clustered index. Either way, the integrity of the table is preserved.

Now let's create a non-clustered index and see what happens:

CREATE UNIQUE INDEX T_DST_B ON T_DST(B)

INSERT T_DST SELECT * FROM T_SRC

As it turns out, aside from adding the extra index to the clustered index insert, nothing changes:

|--Clustered Index Insert(OBJECT:([T_DST].[T_DST_A]), OBJECT:([T_DST].[T_DST_B]), SET:([T_DST].[A] = [T_SRC].[A],[T_DST].[B] = [T_SRC].[B]))
|--Top(ROWCOUNT est 0)
|--Table Scan(OBJECT:([T_SRC]))

Even with two indexes (one clustered and one non-clustered) to maintain, SQL Server continues to use the same strategy of inserting rows and waiting to see whether any of the inserts fail due to duplicate keys. This strategy works because SQL Server always maintains the clustered index first. If it successfully inserts the row into the clustered index, it can safely proceed to insert the row into the non-clustered index. If it cannot insert the row into the clustered index, it can discard the row without any risk of corrupting the indexes. Once again, either way, the integrity of the table is preserved.

It turns out that this strategy works perfectly so long as only the clustered index was created with the WITH IGNORE_DUP_KEY clause. However, if we have even one non-clustered index that was created with the WITH IGNORE_DUP_KEY clause, this strategy no longer works. Imagine what would happen if SQL Server were to insert a row into the clustered index only to encounter a duplicate key violation when trying to insert the same row into the non-clustered index. At this point, SQL Server could not simply issue a warning and discard the row as this would leave the clustered and non-clustered indexes inconsistent. Let's create a non-clustered index with the WITH IGNORE_DUP_KEY clause and see how SQL Server handles this case:

DROP INDEX T_DST.T_DST_B
CREATE UNIQUE INDEX T_DST_B ON T_DST(B) WITH IGNORE_DUP_KEY

INSERT T_DST SELECT * FROM T_SRC

Finally, an interesting query plan:

|--Clustered Index Insert(OBJECT:([T_DST].[T_DST_A]), OBJECT:([T_DST].[T_DST_B]), SET:([T_DST].[A] = [T_SRC].[A],[T_DST].[B] = [T_SRC].[B]))
       |--Top(TOP EXPRESSION:((1)))
            |--Segment
                 |--Sort(ORDER BY:([T_SRC].[B] ASC))
                      |--Assert(WHERE:(CASE WHEN [Expr1012] IS NOT NULL THEN (0) ELSE NULL END))
                           |--Nested Loops(Left Semi Join, OUTER REFERENCES:([T_SRC].[B]), DEFINE:([Expr1012] = [PROBE VALUE]))
                                |--Top(ROWCOUNT est 0)
                                |    |--Table Scan(OBJECT:([T_SRC]))
                                |--Index Seek(OBJECT:([T_DST].[T_DST_B]), SEEK:([T_DST].[B]=[T_SRC].[B]) ORDERED FORWARD)

There are two parts of this plan that handle duplicate keys. The first part includes the left semi-join, index seek, and assert operators. The second part includes the sort, segment, and top operators.

For each row that we intend to insert, the left semi-join, index seek, and assert operators determine whether a matching row with the same key already exists in the non-clustered index. If they find a matching row, they issue the duplicate key warning, discard the row, and continue. Let's explore exactly how these operators accomplish this task. First, the table scan returns a row to insert. The semi-join uses the index seek on the non-clustered index to determine whether a row with the same key already exists in the index. Recall that a left semi-join ordinarily returns rows from the left (or first) input that join with at least one row from the right (or second) input. This semi-join is special. It returns all rows, whether or not they join, and outputs a PROBE column that indicates whether or not each row in fact joined. (For two other examples of a semi-join with a probe column, see my subquery posts here and here.) Finally, the assert operator checks the value of the PROBE column. If the index seek does not return a matching row, the new row is not a duplicate and the assert operator returns it normally. If the index seek does return a matching row, the assert operator issues the warning and discards the row.

The sort, segment, and top operators check for duplicates in the stream of input rows. If they find a duplicate input row, they issue the duplicate key warning, discard the duplicate row, and continue. Again, let's explore exactly how these operators execute this task. The sort simply ensures that any duplicate input rows are clustered one after the other in the input stream. The segment operator compares adjacent rows checking for duplicates. It performs two functions in this plan. First, if it finds a duplicate row, it issues the warning. Second, it "flags" groups of duplicate rows. Unlike the assert operator, the segment operator does not discard any rows. Instead, the top operator discards the duplicates. The top operator in this plan is a special type of top known as a segment top. A normal top returns the first N rows and then stops. A segment top returns the first N rows from each group of duplicate rows flagged by the segment operator. In this case, the segment top returns only the first row from each group and, thus, eliminates the duplicates.

Any rows that are not discarded by the time they reach the insert operator must be unique and can safely be inserted without triggering a uniqueness violation.

If we create multiple non-clustered indexes with the WITH IGNORE_DUP_KEY clause, one set of these extra operators is inserted for each non-clustered index. Note also that the plan needs these additional operators only for the non-clustered index. The clustered index is handled as described at the beginning of this post.

Finally, I want to comment briefly on the performance implications of the WITH IGNORE_DUP_KEY clause. On a clustered index, there is no plan change and the performance implications are negligible. Unfortunately, on a non-clustered index, all of the additional operators discussed above are costly and do result in a measurable performance reduction.

ASP.NET MVC Example Application over Northwind with the Entity Framework

Brad Abrams — 2008-01-29T00:00:00

Over last month over the holidays, Lance Olson and I spent some time porting ScottGu's excellent MVC example over the Entity Framework. I thought I'd share the results here and get your thoughts\feedback.

For this example, you will need:

VS2008 (Free trial )
ASP.NET 3.5 Extensions
ADO.NET Entity Framework Tools Dec 07 Preview
Northwind sample database (just northwnd.mdf)

If you are the type that likes to eat your desert first, you can skip to the end and just download the full working solution.

Getting Started

File/New Project - Select ASP.NET MVC Web Application and Test

This creates a single solution with both a web application project as well as a project we can use for unit testing. Both are pre-populated with the basic stuff you need to get going.

Creating the Routes

One of the powerful new features that ASP.NET MVC brings to the table is the ability to customize the URLs that access your application. The URL routing feature explicitly breaks the connection between physical files on disk and the URL that is used to access a given bit of functionality. This is important for Search Engine Optimization as well as general usability of the website. For example, rather than access http://localhost/Products/ItemDetails.aspx?item=42 you can now very give a pretty URL such as http://localhost/Products/CodFishOil

This is done by creating a route table in the global.asax file in the MVC Application. Luckily for us, the defaults included in the template work perfectly this application.

RouteTable.Routes.Add(new Route
{
    Url = "[controller]/[action]/[id]",
    Defaults = new { action = "Index", id = (string)null },
    RouteHandler = typeof(MvcRouteHandler)
});

This code spells out the format of the URLs we want to use for our site. In particular, a URL of the format

http://localhost/Products/Details/CodFishOil

would translate to the ProductsController class (notice we add the "Controller" suffix to the class name to make these classes stand out in the object model at design time). Then the Action is a method on that class called Details and finally the argument to that details method is CodFishOil.

It is of course possible to have other formats, simply by changing he regular expression in the URL pattern string.

Creating the Model

The model represents the data you are going to use in the application. In our case, this is a pretty good place to start the core of the application.

In the App_Data direction of the MVCApplication copy the Northwind.mdf file. Northwind is likely the most common example database we have for SqlServer... You can download it from the official location, or for just the raw file, feel free to grab it from here.

Next, we need to create a LINQ model on top of northwind to make it easier to work with... You can do this with NHibernate, LinqToSql, Entity Framework , or any other .NET OR-Mapping technology. As long as it results in .NET Object, the ASP.NET MVC framework can work with it. In this case I am going to use the Entity Framework.

Right click on the Models directory and select add new item

In the dialog select ADO.NET Entity Data Model.

In the wizard, select "Generate from Database" and then go with the default "Northwnd" connection string.

For our demo, we are only going to use the Categories, Products and Suppliers tables, but you could of course extend this demo to include a richer feature set. But for now, unselect the Views and Stored Procedures and all the tables except those three..

When you click finished, VS will create a set of .NET classes that are custom built for accessing this database and we get a nice designer for visualizing the data relationships.

Notice, that the default names given to these classes still use the plural names from the database, but in our OR-Mapping they represent single instances, so to make the code easier to read, go in and change all the table names to their appropriate singular form: Category, Product and Supplier. Then Navigation properties on Product needs to be singular as there is only one Category and Suppler for each product.

Next we need to cleanup the namespace so things look pretty in our code... Right click on the design surface then set the properties such that namespace is "NorthwindModels" and the Entity Container name to "NorthWindEntities"

While we are not 100% done with the model, we have gotten the lion's share of it complete.. Let's switch over and look at the controller.

Creating the Controller

Right click on the Controller directory and select "Add new Item". In the dialog find MVC Controller and be sure to give it a name that ends in the Controller suffix. In our case we are writing the ProductsController.

OK, we are ready to start working in ProductsController.cs

The goal of the controller is to prepare the model for the view. We want to bring as much logical as possible out of the view because it is so hard to test in the view. So in the controller, we are going to be accessing the Model and getting it all set up so all the view has to do is spit out some data.

The first thing we need, is access to our database.

1. Add the right namespaces... Linq and a reference to our OR mapping.

using System.Linq;
using NorthwindModel;

2. Next, we create an instance of the NorthwindEntities container class. Nearly all of our actions will access this class.

    public class ProductsController : Controller
    {
        NorthwindEntities Northwind = new NorthwindEntities();

OK, now we are ready to create our first action.. Listing all the categories. Remember the job of the controller is to prepare the model for the view.. When defining a new action, I like to start off by putting a comment that reminds me what the URL is that access this functionality.

The next thing we do is access the Categories table from the model. I snap the results to a generic collection class (you may have to add a reference to System.Collections.Generic) then we pass the results to the view named "Categories". This is a very simple example, later we will see more complex logic here.

//URL: http://localhost/Products/Categories
[ControllerAction]
public void Categories()
{
List<Category> categories = Northwind.Categories.ToList();
RenderView("Categories", categories);
}

Next step, we need to create the "Categories" view..

Creating the View

Right click on the Views folder, add new Directory "Products". This so we can organize all our views cleanly. The Right click on Views/Products folder and add new item MVC View Content Page. We are going to leverage the Master Page that comes with the default project to make this look a little more pretty.

Call it Categories.aspx... It is important that the view name matches the first argument to the RenderView method above.

The default project puts the master page in Views/Shared/Site.Master

In order to get the strongly typed access to the ViewData we are passing in from the controller, we need to tell the View Page, the type to expect. This is done by opening up the codebehind (Categories.aspx.cs) and changing the derived type from:

    public partial class Categories : ViewPage
    {
    }
to:

public partial class Categories : ViewPage< List<Category> > 
{
}

Then you simply write clean, simple, designable HTML. Notice here I am looping over all the items returned in ViewData and pushing them out as a link. I use the MVC helper method Html.ActionLink to create the URL for the List action with the appropriate product ID.

    <h2>Browse Products</h2>

    <ul class="categorylisting">
    
      <% foreach (var category in ViewData) { %>
    
        <li>
            <%= Html.ActionLink(category.CategoryName, new { action="List", id=category.CategoryName }) %>            
        </li>
    
      <% } %>
        
    </ul>

OK, we are finally ready to run it!

Hit F5, and navigate to the controller action we just wrote on http://localhost:64701/products/Categories

Clicking on any of the links give you an error, because we haven't written the List action yet.. we will do that next.

As an aside, if like me, you are in the habit of using "View in Browser" on aspx pages you develop, you are likely to see this error. To reproduce, right click on Categories.aspx and select View in browser.

You get an error. Why? Well, remember with the MVC model, all execution goes through the controller, the views themselves are not runnable. Future tooling will make this better, but in the mean time, use F5 or you can "run in browser" with default.aspx.. just be sure you have built the solution first!

The List Action, View

OK, let's now go back and add the List action that we are missing. What we need to do here is find all the products with a give Category. First I need to get all the products out of the model, then I need to ensure that the Category references are loaded. The entity framework offers an explicit loading model by default. As such you have to explicitly load any tables you need. And finally we render the view.

//example URL:http://localhost:64701/products/List/Confections
[ControllerAction]
public void List(string id)
{
    List<Product> products = Northwind.GetProductsByCategory(id);

    //prepare the view by explicitly loading the categories  
    products.FindAll(p => p.Category == null).ForEach(p => p.CategoryReference.Load());

    RenderView("ListingByCategory", products);

}

Notice, i am calling a custom method on the NorthwindDataContext class... Personally I like the idea of keeping all the data access logic encapsulated in that class. So to define this method, Right click on the Model, add new Item, select CodeFile and name it NorthwindDataContext.cs and give it the implementation.

using System;
using System.Collections.Generic;
using System.Linq;

namespace NorthwindModel
{
    public partial class NorthwindEntities
    {

    }
}

Now you can easily add data access methods to this class... such as the GetProductsByCategory() method we use above.

public List<Product> GetProductsByCategory(string category)
{
    return Products.Where(p => p.Category.CategoryName == category).ToList();
}

Next, we need to add the ListingByCategory view... We follow the same steps from above to add a ListingByCategory.aspx page in the Views/Products/ directory.

This time, we should make the ViewData be of type List<Products>

public partial class ListingByCategory : ViewPage< List<Product> > 
 {
 }

Next, to implement the view, we simply loop over the view data and spit it out in the right format.

  
<%--Print out the catagory name--%>  
  <% foreach (var product in ViewData) { %>
  <% if (product.Category.CategoryName != null) {  %> 
    <h2>  <%=product.Category.CategoryName  %></h2> 
    <% break; %>
 <%} //end if %>
 <%}//end foreach %>
        
    <ul class="productlist">
    
        <% foreach (var product in ViewData) { %>
        
            <li>
                <img src="/Content/Images/<%=product.ProductID%>.jpg" alt="<%=product.ProductName %>" /> 
                <br/> 
                <a href="/Products/Detail/<%=product.ProductID %>"> <%=product.ProductName %> </a>
                <br />
                Price: <%=String.Format("{0:C2}", product.UnitPrice)%> 
                  <span class="editlink" ">
                    (<%= Html.ActionLink("Edit", new { Action="Edit", ID=product.ProductID })%>)
                </span>
           
            </li>
    
        <% } %>
        
    </ul>

Once you add the /Content/Images directory from the sample project you get this:

Unit Testing

One of the primary reasons for using the MVC model is to enable Test Driven Development and more generically Unit Testing. Unit testing UIs is notoriously difficult. Automation is fragile and hiring an army of testers to click on buttons is not always cost effective. The model MVC is going for is to separate out as much code (and therefore potential bugs) as possible into the Model and the Controller where they are testable.

So, we are going to write Unit Tests for the two control methods we just created and the corresponding logic in the model.

First, we need to setup our testing environment. The default template already created a Test project we can start to customize.

1. In the MvcApplicationTest project, create a new class called ProductControllerTest.cs

2. Add a method for testing the Categories action we defined above.

namespace MvcApplicationTest.Controllers
{

    [TestClass]
    public class ProductsControllerTest
    {

        [TestMethod]
        public void Categories()
        {

        }
    }

3. We want to test the controller we wrote, but we don't want to load IIS and all of ASP.NET... we want to have the leanest, most simple system we can to avoid test breaks, etc. To that end, I created a test specific subclass of the ProductsController we defined above. In this subclass I override the RenderView method. Instead of calling out to ASP.NET, my test specific implement simply captures the arguments so they can be queried later in the test.

class TestProductsController : ProductsController
{
    public string ViewName { get; set; }
    public string MasterName { get; set; }
    public new object ViewData { get; set; }
    protected override void RenderView(string viewName, string masterName, object viewData)
    {
        ViewData = viewData;
        MasterName = masterName;
        ViewName = viewName;
    }
  }

4. Now we implement the test! First we create an instance of our test controller, then I create some dummy (or mock) data. The idea here is that we don't want to hit the production database (or any database for that mater) in our tests. We want exactly the data we need for the test, no more, no less. Then we set up the NorthwindDataContext to use our data rather than the data from the database. Then we call the controller action we are trying to test. Finally, we assert a few things.. that the type of the ViewData is right, that the number of items returned is right, etc. Notice, you will need to add a reference to the EntityFramework (System.Data.Entity.dll) in your test project to use these EF generated types like Product, Category, etc.

[TestMethod]
public void Categories()
{
    // Create Products Controller
    TestProductsController controller = new TestProductsController();

    List<Category> l = new List<Category>();
    l.Add(new Category() { CategoryName = "Drink" });
    l.Add(new Category() { CategoryName = "Food" });
    l.Add(new Category() { CategoryName = "Cheese" });

    controller.Northwind.TheCategories = l.AsQueryable();

    // Invoke Controller Action
    controller.Categories();

    // Verify Detail Action Behavior
    Assert.AreEqual(typeof(List<Category>),
                     controller.ViewData.GetType(),
                     "Product object Passed to View");

    Assert.AreEqual(3,
                     ((List<Category>)controller.ViewData).Count,
                     "Correct Number of Catagories returned");

    Assert.AreEqual("Food",
                     ((List<Category>)controller.ViewData)[1].CategoryName,
                     "Correct Product Object Passed to View");

    Assert.AreEqual("Categories",
                     controller.ViewName,
                     "Correct View Rendered");

}

5. Set the connection string in the test project so that it access the metadata from EF model, but does not open the database. In MVCApplicationTest add an app.config file to the root of the project with the this connection string.

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="NorthwindEntities"
         connectionString="metadata=res://*/;provider=System.Data.SqlClient;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Now, we need to go back to the EDM designer and set it to embed the metadata for the model as part of the assembly. This makes our connection string above much more simple. Double click on the edmx file, then click on the conceptual model in the model browser and in the property sheet you change the property Metadata Artifact Processing from “Copy to Output Directory” to “Embed in Output Assembly”

6. Now we need to do just a bit of setup in the MVCApplication to enable a great testing experience... There are a few tricks here and what I am showing is NOT the definitive model (there isn't a definitive model yet)... but I like it, so what the heck!

MVCApplication/Model/NorthwindDataContext.cs add the following encapsulation of the Categories property.

private IQueryable<Product> savedProducts = null;
public IQueryable<Product> TheProducts
{
    get
    {
        if (savedProducts == null) savedProducts = this.Products;
        return savedProducts;
    }
    set
    {
        savedProducts = value;
    }
}

Then, make sure that all access go through this abstraction. For example:

public List<Product> GetProductsByCategory(string category)
{
    return TheProducts.Where(p => p.Category.CategoryName == category).ToList();
}

Finally, we need to make sure the NorthwindEntities is accessible from the test classes, so in ProductsController.cs add the "public" modifier.

public NorthwindEntities Northwind = new NorthwindEntities();

Now, we are able to run our test. You can do this by setting the test project as being the "startup project" (right click on the test project) or you can select Test/Run menu option.

Well, that is the lap around MVC+EF... There is a lot more to the sample I posted. If there is interest and I get the time, I will walk through the rest in future blog posts, but all the source code is there, so you can have fun with it on your own!

Enjoy!

You can download the full working example here.

LINQ to SQL vs. LINQ to Entities Smackdown!

Kirk Allen Evans Blog — 2008-01-29T00:00:00

OK, maybe not a smackdown... but it sure sounded like a good title.

There's a great paper on MSDN that describes the similarities and differences in LINQ to SQL and LINQ to Entities from the Entity Framework.

I have done a bunch of LINQ demos recently, a lot of people are really fired up about the simplicity that LINQ brings to the table. What is confusing is how LINQ to SQL conflicts and overlaps with what LINQ to Entities offers. From the article referenced above, the key takeaways are summarized below.

LINQ to SQL is good when your solution is targeted classes are going to be targeted at SQL Server and you have a 1:1 relationship between the domain model and database schema.

Use LINQ to Entities as an ORM solution where your classes may be 1:1 with the database or may have a very different structure from the database schema and targets. LINQ to Entities will also good for 3rd party database solutions.