Thursday, December 27, 2007

Linq To Sql, Attach, Using a DateTime as Timestamp and large bottle of ibuprofren!!!

Scenario:

You use LINQ to SQL (L2S) as your ORM, you create a business layer that supplies a list of customer entities to the presentation layer. The UI presents the data in a gridview where the user can update the info.

When the user clicks update, it passes a new instance of the entity to your BL method. Now you might be tempted to think that you simply use the Attach method of the Customers table and everythings fine and dandy. Oh that it were true. Be ready for:

"System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."

The entity being passed to your BL here is *not* the same one that went out. It is a new one created by the GridView as part of the update. Thus, it has no idea of the original context and your new context has no idea of it's original state.

If you have add a timestamp column that can be used by L2S to determine if the row has changed, you can make things work fine. This has some advantages in that it reduces the amount of T-Sql generated to perform the update.

// Business Layer code to get the list and perform the update.
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)] 
public static List ListCustomers()
{
    using (SandboxDataContext dataContext = new SandboxDataContext())
    {
        var custs = from c in dataContext.Customers orderby c.CustomerName select c ;
        return custs.ToList();
    }
}

[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)]
public static void UpdateCustomer(Customer c)
{
    using (SandboxDataContext dataContext = new SandboxDataContext())
    {
        dataContext.Customers.Attach(c, true);
        dataContext.SubmitChanges();
    }
}

Note: You must use the Attach(entity, true) overload to actually save the changes.
Note: Make sure you configure the DataKeys property of your grid to include the primary key and the timestamp. Otherwise, you get a ChangeConflictException -- Row not found or changed.

-- T-Sql generated by L2S

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [CustomerName] = @p2
WHERE ([CustomerKey] = @p0) AND ([TimeStamp] = @p1)

SELECT [t1].[TimeStamp]
FROM [dbo].[Customers] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerKey] = @p3)',

N'@p0 int,
@p1 timestamp,
@p2 varchar(40),
@p3 int',
@p0=18,
@p1='0x00000000000007D1',
@p2='Acme Widgets, LLC',
@p3=18

Notice how only the CustomerName column is updated instead of every column. This is one of the largest advantages over stored procs. Ordinarily, you'd probably write one stored proc to update the table and it would update every column. You definitely wouldn't want to write an SP for *every* column or permutation of columns.
Instead, what's produced is a nice tight query that only changes those columns that need to be changed. IMHO, this outweighs any perceived performance issues versus stored procs.

Technorati Tags: ,

Wednesday, December 26, 2007

Fixing Association Lines in the LINQ to SQL Designer

There is a simple fix for when the Linq to Sql designer goes crazy with the number of points on an association line. Simply close the designer, click "Show All Files" in your solution. You should now be able to expand the underlying files used to store info for the designer. Open the dataContextName.dbml.layout file with the XML Editor. Associations are stored in an associationConnector element. Find the correct association and change the following attributes to appear as follows: manuallyRouted="false" fixedFrom="NotFixed" fixedTo="NotFixed" If manuallyRouted doesn't appear, don't worry about it. Save everything and close the file. Reopen the designer and your line still looks weird but as soon as you move one of the objects around, it should fix itself.

Friday, December 21, 2007

Steelers Game!!

Went to STL for the Rams-Steelers game. As we we're driving to the hotel, you'd have thought it was a home game. There were more Steeler nation than Rams fans by far. If you ever want to see a site, Casey Hampton was stretching in front of us. At one point he was doing the exercise where you bring your knee across your body and try to touch it to the ground. You must see it for yourself! Alan Fanaca was also stretching and didn't seem to want to talk. Wish him the best next year. He's given a lot to the team over the years and deserves to get a great contract. Forget the hometown discount!!! The game was excellent! Didn't see Willie get hurt although I remember the play. The opening pass from Ben to Holmes was amazing. Need to work on our coverage. Is it just me, or does it seem that you can throw under DeShea pretty easily?!

Tuesday, March 20, 2007

Get Asp.Net Profile properties from Sql

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository.

First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString.

Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.

You might be thinking you can simply use Right(PropertyValuesString, 4) to get all of the first names. Unfortunately, no. First, there are a lot of names longer than 4 so we know that length is an issue. Second, neither PropertyNames or PropertyValuesString are consistently ordered. You might see the FirstName as the start for one while the next one has the Phone and the next LastName.

The following Sql function finds the property we’re looking for, gets the starting position and length and then returns the value as a varchar.

-- =============================================
-- Create date: 09/01/2006
-- Description: Gets the property value from
--              the Asp.Net profile.
--              @PropertyName – The property to
--              be found.
--              @PropertyNamesString – The
--              property names information.
--              @PropertyValuesString – The
--              property values information
-- =============================================
CREATE FUNCTION [dbo].[GetProfilePropertyValue] (  
    @PropertyName as varchar(max)
    , @PropertyNamesString as varchar(max)
    , @PropertyValuesString as varchar(max)) 
RETURNS varchar(max)
AS
BEGIN
    DECLARE @StartIndex int
    DECLARE @EndIndex int
    DECLARE @StartPos int
    DECLARE @Length int
    
    -- First we find the starting position
    Set @StartIndex = PatIndex('%' + @PropertyName + ':%', @PropertyNamesString) + LEN(RTRIM(@PropertyName)) + 3
    Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
    Set @StartPos = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As Int)
    
    -- Now we need to know how long it is
    Set @StartIndex = @StartIndex + @EndIndex + 1
    Set @EndIndex = PatIndex('%:%', Right(@PropertyNamesString, LEN(@PropertyNamesString) - @StartIndex))
    Set @Length = Cast(Substring(@PropertyNamesString, @StartIndex, @EndIndex) As int)
    
    -- Now we get the value we want
    RETURN SUBSTRING(@PropertyValuesString, @StartPos + 1, @Length)
END

That was easy, now all we need to do is run a query that gets the info.

SELECT
    dbo.GetProfilePropertyValue('LastName', PropertyNames, PropertyValuesString)
    , dbo.GetProfilePropertyValue('FirstName', PropertyNames, PropertyValuesString)
    , dbo.GetProfilePropertyValue('Phone', PropertyNames, PropertyValuesString)
FROM aspnet_Profile

Joining with the aspnet_Users on UserID will give you the user name and email.
Enjoy.

Technorati Tags: ,,

Subscribe in a reader

Thursday, March 15, 2007

Generate Custom Business Object Properties from SQL Server Tables

In the never ending debate between using datasets and using custom business objects (CBOs), one of the main knocks on CBOs is that they take more time to code. You have to write the properties and create getters and setters for each, code the DB calls, etc.

Of course, you could always get yourself a code generator but those cost money. On the other hand, Sql Server 2005 has made this a little easier with some System Views. Look at the Information_Schema views and you’ll find a whole bunch of really nice info. The one we’re going to use today is the Columns view.

The first steps we need is to be able to convert from Sql datatypes to .Net types. This handy little Sql function will do our conversion for us. You can simply delete the function when our done generating.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 03/15/2007
-- Description: Returns the correct Net datatype
-- from Sql datatype
-- =============================================
CREATE FUNCTION GetNetDataType(
    @SqlDataType nvarchar(128)
)
RETURNS nvarchar(128)
AS
BEGIN
-- Declare the return variable here
    DECLARE @NetDataType nvarchar(128)
    SELECT @NetDataType = CASE
        WHEN @SqlDataType = 'varbinary' THEN 'Byte[]'
        WHEN @SqlDataType = 'binary' THEN 'Byte[]'
        WHEN @SqlDataType = 'varchar' THEN 'String'
        WHEN @SqlDataType = 'char' THEN 'String'
        WHEN @SqlDataType = 'nvarchar' THEN 'String'
        WHEN @SqlDataType = 'nchar' THEN 'String'
        WHEN @SqlDataType = 'text' THEN 'String'
        WHEN @SqlDataType = 'ntext' THEN 'String'
        WHEN @SqlDataType = 'uniqueidentifier' THEN 'Guid'
        WHEN @SqlDataType = 'rowversion' THEN 'Byte[]'
        WHEN @SqlDataType = 'bit' THEN 'Boolean'
        WHEN @SqlDataType = 'tinyint' THEN 'Byte'
        WHEN @SqlDataType = 'smallint' THEN 'Int16'
        WHEN @SqlDataType = 'int' THEN 'Integer'
        WHEN @SqlDataType = 'bigint' THEN 'Int64'
        WHEN @SqlDataType = 'smallmoney' THEN 'Decimal'
        WHEN @SqlDataType = 'money' THEN 'Decimal'
        WHEN @SqlDataType = 'numeric' THEN 'Decimal'
        WHEN @SqlDataType = 'decimal' THEN 'Decimal'
        WHEN @SqlDataType = 'real' THEN 'Single'
        WHEN @SqlDataType = 'float' THEN 'Double'
        WHEN @SqlDataType = 'smalldatetime' THEN 'DateTime'
        WHEN @SqlDataType = 'datetime ' THEN 'DateTime'
        END
        
    RETURN @NetDataType
END
GO

Now we can run a query against Information_Schema.Columns to get the columns of our table and gen the code for the properties. I’ve included VB and C# codes. It may be easier to get the code from the Text (Ctrl-T) of the result window rather than the grid view (Ctrl-D).

DECLARE @CRLF as varchar(2)
Set @CRLF = CHAR(13) + CHAR(10)
DECLARE @TAB as varchar(2)
Set @TAB = CHAR(9)
-- This produces VB code
SELECT 'private m' + Column_Name + ' as ' + dbo.GetNetDataType(data_type) + @CRLF
    + 'Public Property ' + Column_Name + ' as ' + + dbo.GetNetDataType(data_type) + @CRLF
    + 'Get' + @CRLF
    + 'return m' + Column_Name + @CRLF
    + 'End Get' + @CRLF
    + 'Set(value as ' + dbo.GetNetDataType(data_type) + ')' + @CRLF
    + 'm' + Column_Name + ' = value ' + @CRLF
    + 'End Set' + @CRLF
    + 'End Property' + @CRLF + @CRLF
FROM information_schema.columns
WHERE Table_Name = 'MyTable'
ORDER BY Column_Name

-- This produces C# code
SELECT @TAB + 'private ' + dbo.GetNetDataType(data_type) + ' m' + Column_Name + ';' + @CRLF
    + @TAB + 'public ' + dbo.GetNetDataType(data_type) + ' ' + Column_Name + ' {' + @CRLF
    + @TAB + @TAB + 'get {' + @CRLF
    + @TAB + @TAB + @TAB + 'return m' + Column_Name + ';' + @CRLF
    + @TAB + @TAB +  '}' + @CRLF
    + @TAB + @TAB + 'set { ' + @CRLF
    + @TAB + @TAB + @TAB + 'm' + Column_Name + ' = value;' + @CRLF
    + @TAB + @TAB + '}' + @CRLF
    + @TAB + '}' + @CRLF  + @CRLF
FROM information_schema.columns
WHERE Table_Name = 'MyTable'
ORDER BY Column_Name
Next time I’ll look at how to generate the parameters for your stored procedures.

Friday, March 9, 2007

Start it off Right!

Wow, my first blog post. Need to start it off right!! The Steelers Rule!!