101 LINQ Samples

Visual Basic

http://msdn2.microsoft.com/en-us/vbasic/bb688088.aspx

 

C#

http://msdn2.microsoft.com/en-us/vcsharp/aa336746.aspx

Is LINQ the future of database development?

The LINQ (.NET Language Integrated Query) Project is an initiative to standardize data access across data sources and development.

  

This is an interesting article by Arthur Fuller that explains why he's not convinced that LINQ will revolutionize database application development.

 

http://articles.techrepublic.com.com/5100-9592-5897968.html

 

I can't say I completely agree or disagree with all of the comments he makes in the article; nonetheless, I found it an interesting read. Be sure to read the "comments on this article" to see how certain people have reacted to both sides of the argument.

 

Like Arthur Fuller I've always been a strong proponent of Whatever the back end can do, the back end should do. This certainly has to be looked at on a project by project basis, but typically the projects I'm involved in and architect have benefited from a separate data layer with stored procedures for data access. So naturally I had skepticism as I began reading, researching, and testing LINQ.

 

With that said - I don't necessarily look at LINQ as having to move all back end code to the front end. I think it ends up being strictly up to how the solution is architected and how the developer wants to utilize it. Unfortunately, with LINQ in its infancy almost every code-snippet, documentation or article has some misleading examples that make it appear that all the data access code must now be moved to the front end of an application. I have to admit with my first several examples it was simple enough for me to drop a DBML file in the web project and write some quick code to test it. But of course after I saw it worked I immediately created a "Data" layer and moved the DBML file there and continued testing. That way the data access layer did just that, access data, and the front end could use the data accordingly.

 

This comment seemed to sum up exactly what I was thinking as I waded through all the pros and cons of how LINQ will affect the future of database development:

 

LINQ is a tier neutral technology. On the front end one can use LINQ to query returned datasets, XML files etc and on the back end to query a database. IMHO the back end (data access tier - DAC) is the only tier that is allowed to access data storage like SQL Server. The front end can manipulate returned data from the back end but not retrieve or update it without using the DAC. LINQ is merely a uniform way of accessing different data sources. New functionality like LINQ does not force bad coding style, that is left up to the creativity of the developer.

 

So although no conclusive agreement has been reached in the development community regarding the future of LINQ, I am optimistic and look forward to learning more about it over the next several weeks and months...

System.Data.Linq.Table Add and Remove Method Name Changes

If you tested BETA releases of Visual Studio 2008 and the .Net 3.5 Framework you'll find that some minor changes were made for the final release.

 

In particular one issue I ran into this evening was testing and writing sample code that I found in the MSDN documentation that used the Add and Remove methods of the System.Data.Linq.Table class.

 

The Add method was changed to InsertOnSubmit and the Remove method was changed to DeleteOnSubmit.

 

While more wordy, the change was made to make them more explicitly describe their behavior.

Visual Studio 2008 & LINQ

I started using Visual Studio 2008 today. So far I haven't ran into any problems. In fact, I've tried it on Vista and XP and both installations went very smooth.

 

I've tested a few different scenarios, inluding working with one of my Visual Studio 2005 .Net 2.0 applications without upgrading to .Net 3.5. I didn't have any noticeable problems in doing so and was able to work with the application as it were 2.0 as expected. And I had all the benefits of Visual Studio 2008. The intellisense for JavaScript was very slick. I don't typically have to write a ton of JavaScript code, but the fact that it's going to be easier to work with and debug is a very nice feature.

 

I also tested converting an existing 2.0 application to 3.5. Again, I saw no noticeable problems. And once converted to 3.5 I had all the additional benefits of the 3.5 framework at my disposable.

 

And lastly I created a .Net 3.5 web application from scratch and had no problems. I did some very basic testing initially, and even got involved in writing my first lines of LINQ code. Quite remarkable!

 

Here's a great link for Visual Studio 2008 samples that was helpful for me to get started, particularly with LINQ.

 

http://msdn2.microsoft.com/en-us/bb330936.aspx

 

The "Hands On Labs" are perfect if you are just getting started with the LINQ syntax and LINQ to SQL syntax. The documents are available for VB and C#.

 

In a matter of minutes I had a connection to a database and fully generated classes by dragging and dropping tables to my "LINQ to SQL Classes" file.

 

The only code I had to write was to query the data using LINQ and do some basic operations on it.

 

Definitely looking forward to getting more involved with Visual Studio 2008 and LINQ!

SCOPE_IDENTITY() vs. @@IDENTITY - Retrieving IDENTITY value for most recently added row in a table

This one is one of the basics that all SQL programmers learn at some point... Sometimes the hard way.

 

@@IDENTITY returns the most recently created identity for your current connection, not necessarily the identity for the recently added row in a table. You could have a situation where there is a trigger that inserts a new record in a Logs Table, for example, when your Stored Procedure or INSERT SQL Statement inserts a record in the Orders Table. If you use @@IDENTITY to retrieve the identity of the new order, you will actually get the identity of the record added into the Log Table and not the Orders Table, which will create a nasty, nasty bug in your data access layer.

 

To avoid the potential problems associated with someone adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your INSERT SQL Statement or Stored Procedure.

 

http://davidhayden.com/blog/dave/archive/2006/01/17/2736.aspx

 

Luckily today when some strange behavior occurred during our QA testing this was the first thing that I thought of, and sure enough, the original developer used @@IDENTITY to get the newly inserted identity value, which at the time was perfectly correct to do; however, we've recently added some trigger functionality, which caused this behavior to produce incorrect results. Switching to SCOPE_IDENTITY()  fixed the problem. But this one is definitely one you want to remember if you are using triggers in your code.

Setting a variable from dynamic SQL

I don't have to do this very often, but it definitely comes in handy in certain situations.

 

-- setting a variable from dynamic sql

DECLARE @MyValue INT

EXEC sp_executesql N'SELECT @MyValue = 999', N'@MyValue INT OUTPUT', @MyValue OUTPUT

SELECT @MyValue

 

-- setting output parameter from dynamic stored procedure call

DECLARE @OutputParameter VARCHAR(100)

DECLARE @Error INT

DECLARE @SPName VARCHAR(128)

DECLARE @SPCall NVARCHAR(128)

DECLARE @RC INT

 

SELECT @SPCall = 'EXEC ' + @SPName + ' @OutputParameter OUTPUT'

EXEC @RC = sp_executesql @SPCall, N'@OutputParameter VARCHAR(100) OUTPUT', @OutputParameter OUTPUT

SELECT @Error = @@Error

 

One example where this was useful for me was for converting a demormalized set of horizontal data to a normalized set of vertical data.

 

The denormalized data contained a series of column names such as "200701", "200702", "200703", etc. for each month of the year. The file changes month to month and to minimize re-writing code each time a new file comes in I am able to import the data generically, determine the columns that are in the file, and get the value by setting a variable using dynamic SQL.

 

-- declare variables

DECLARE @Total FLOAT

DECLARE @SqlStatement NVARCHAR(1000)

 

-- set default values

SET @Total = 0

SET @SqlStatement = 'SELECT @Total = [' + @ColumnName + '] FROM RawData WHERE RecordID = ' + CONVERT(VARCHAR, @RecordID)

 

-- get the specified column value for the current record

EXEC sp_executesql @SqlStatement, N'@Total FLOAT OUTPUT', @Total OUTPUT

 

Enjoy!

SQL Server GetAge Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetAge] (@DOB DATETIME, @Today DATETIME)
RETURNS INT
AS
BEGIN
     -- declare variables
     DECLARE @Age INT

     -- set defaults
     SET @Age = Year(@Today) - Year(@DOB)

     -- if it is a previous month subtract one off the age
     IF Month(@Today) < Month(@DOB)
     BEGIN
          SET @Age = @Age -1
     END

     -- if it is the current month but a previous day subtract one off the age
     IF Month(@Today) = Month(@DOB) AND Day(@Today) < Day(@DOB)
     BEGIN
          SET @Age = @Age - 1
     END 

     -- return value
     RETURN @Age
END

Usage

-- declare variables
DECLARE @Today AS DATETIME

-- set default values
SET @Today = GetDate()

-- get data
SELECT   ID,
             DOB,
             'Age' = dbo.GetAge(DOB, @Today)
FROM     MyTable