I don’t claim to have come up with this little code snippet* but I’m putting it up here so I don’t have to waste time looking for it again. And if it helps you, feel free to snag and use.

UPDATE T
SET
T.fieldOne= S.oneValue,
T.fieldTwo= S.twoValue
FROM [TargetTable] T
JOIN [SourceTable] S ON T.id = S.id

* I believe I first saw it on SQLTeam.com but I can’t find the original article now.

Click on pen to

Anyway, one of the commonly unnecessarily used controls, I think, is the LinkButton. When I had to try and make our pages a little leaner, that’s one of the first things I check:

<asp:LinkButton ID="LinkButton1" runat="server" PostBackUrl="~/somefolder/somepage.aspx">LinkButton</asp:LinkButton>

In our company, we had an (unrelated) viewstate problem where all our linkbuttons kept failing, but our A HREF tags were fine. Now, this made no sense to me ’cause, basically, the above linkbutton was just doing the same job as an A HREF. There was no behind the code event subroutine, it was just a redirect.

I asked the programmer who did this why he didn’t use normal static A HREFs instead. He said that he wanted to be able to use the virtual path “~/” because the pages would be deployed in different plages at different stages. I introduced him to this nifty little library called VirtualPathUtility.

You can get the same function by using this code:

<a href="<%=VirtualPathUtility.ToAbsolute("~/somefolder/somepage.aspx")%>">No Longer a LinkButton</a>

Because of the unrelated issue — and I might be wrong — but it seems like using a LinkButton even with a different PostBackURL specified, a postback to the same page DID occur, processed on the server, PostBackURL value determined, and then a redirection issue. To me, that’s like a lot more stuff going on that just a simple A HREF where the user clicks and the browser knows straight away which page it needs to go.

Plus, I think not having a webcontrol just means less for the server to keep track of since it’s all HTML that comes out. I could be wrong, but in our team, we’ve decided to apply the following:

1. Unless your LinkButton needs to do behind-the-code processing, convert all LinkButtons to <a href> tags.

2. If you need to resolve virtual paths, use the nifty VirtualPathUtility.

I’ve also used it to replace ImageButtons with <a href> and <img> tags. I’m not sure if it’s made a huge difference in the grand scheme of things, but hopefully it doesn’t hurt and helps just a little.

Click on pen to

Today, though, I decided to move off some of my Virtual Machine files to the My Book and clear up some of my hard disk. So I switched it on, plugged it in and tried to copy and paste it into the disk…

Windows tells me the file is too large for the destination file system. Bu-wha-? My virtual hard disks is over 4GB…but I checked! My Book had over 350GB free space! I thought it was the network problem (so I plugged it in direct), I thought it was read-only thing (resetting it did nothing), I was scratchin’ my head until I Goggled and figured it out….

The darn thing was formatted in FAT32. Did you know FAT32 doesn’t accept files larger than 4GB? Now WHY a 500GB storage space is formatted in FAT32 is beyond me!

But now, I’ve got a dilemma.

1) if this was a new disk, I’d be able to reformat it (Right clicking the Drive in My Computer and Format)

2) but I got data, … luckily, on a Windows, you can convert your disk on the fly!

a) Run the command prompt as Administrator (Start->Command Prompt->Right-click->Run as Administrator)

b) At the command prompt, type

C:\Users\J>convert f: /fs:ntfs /nosecurity

where f: is the drive of your external hard disk.

And …tada! Two hours of trying to figure out what the heck was wrong, and fixed in 2 minutes.

Sooooo typical of a computer problem for ya.

UPDATE: When prompted to “Enter the current volume label for drive [Drive Letter Here]“, if your drive has a label, you need to enter it (e.g. “My Passport” without the quotations). If your drive doesn’t have a label, just press the enter key for blank.

To find the volume label (if there is one specified), go to ‘My Computer’, right-click the drive you’re trying to convert, select Properties. In the General tab, the volume label is the name in the first field.

Click on pen to

Here’s a multiple recordset-returning stored procedure. My first recordset returns a three columns, single row of overview data, and my second recordset returns the list/breakdown.

CREATE PROCEDURE spGetBookList
AS
BEGIN
-- first recordset - overview of the list
SELECT count(*) as totalbooks,
sum(pages) as totalpages,
sum(price) as totalprice
from book_list

– second recordset – the book list
select id, title, author, pages, price, published_date
from book_list
END
GO

I want to use this through LINQ, so first, I’ve got to add it to the LINQ dbml file.

1. Drag and drop in the designer as usual.
The stored procedure will appear on the stored procedure list.

The designer generates two things automatically whenever you drag a stored procedure. The function that you can use (in your coding) to call this stored procedure , and then the class that represents the data.

You can see this if you take a look at the .vb bile of the dbml file (expand the + sign on the dbml and double-click on the .vb file.).

And scroll down ’til you see a function that’s similar in name to the stored procedure you added.

Note:

1. Public Function spGetBookList()
2. Class spGetBookListResult

As far as I can tell, this is what always happens when we drag a stored procedure to the designer. A function with the same name as the stored procedure will be created, and a class representing our recordset. Even though our stored procedure here returns two recordsets, the designer is only capable of detecting and designing one recordset result.

Here’s where I guess we have to go in and tweak it so that this function returns TWO recordset.

2. Create a new class.
This class is going to contain the data classes that’ll represent the multiple recordsets in our stored procedure.

At first I created these classes INSIDE the DBML’s .vb file, but I discovered I would lose all my tweaks/changes whenever I refreshed the DBML designer (e.g. adding a new table/stored procedure), since it generates that file automatically.

This is a pain here, but an important one: everytime the DBML is changed, it re-generates. That means, that some of the steps here will have to be re-done every time. More on that later. For now, though, this class will help reduce the number of steps that’ll have to be re-done.

3. Move the partial class to the new class

3a. Insert Imports System.Data.Linq.Mapping to class

3b. Cut and paste the partial public class spGetBookListResult from the .vb and paste into this new class.

4. Create another partial class for the second recordset
Basically this class has all the properties that’ll represent all the columns in the second recordset. Since my second statement was

select id, title, author, pages, price, published_date
from book_list


My class had to have properties id (integer), title (string), author (string), pages (integer), price (decimal), published_date (datetime). Basically, you can pretty much copied the same format as the first recordset’s partial class and do something like this:

CHEAT: To be honest, I don’t create most of my second-onwards recordset classes manually. What I do is I create a dummy stored procedure and put the single select statement into the stored procedure, drag it to the DBML, open the .vb, and tada, “steal” the auto-generated class for that select statement and put it in my own data class. And then I repeat as necessary, and when I’m done, I delete my dummy stored procedure from the DBML.

Remember the name of this class!

5. Go back to the DBML .vb, find the function, and tweak it so that it returns the multiple recordset instead of one.
To do this, you need to change this

to this

Basically, the changes are:

1. Replace the As ISingleResult(Of <whateverclass) with IMultipleResults
2. Replace the function code with the one above Dim result …. Return Ctype(result.Returnvalue,IMultipleResults)
3. Add the ResultType attributes in order, with the GetType value referring to the class you just created to represent each recordset.

6. Copy this entire function, paste into the data class, and comment out. JUST FOR REFERENCE.

ANNNNNND, we’re done!

Oh, except on how to actually CALL this thing. Which would be something like this:

The results variable basically holds all the multiple recordsets. To access each one, you need to call the GetResult (in order), which is sort of like NextRecordset in good ol’ ASP. The Of <your recordset class> allows you to do all those nice .<fieldname> intellisense and such.

I had to convert the results to ToList in order to iterate back and forth, but this may or may not be necessary in your case. Try with and without, YMMV.

Oh, one more thing…
Remember I said whenever you made a change to the DBML designer, the whole designer’s vb file gets regenerated? The IMultiple result function will ALSO disappear to be replaced by a single result one (and its data class). That’s where the copied Imultiple function in your own data class comes in handy. Just copy and replace and all will be well again.

AND FIIIIINALLY…
I get the feeling this is a bit hack-ish, especially the part where we’re going into the designer –that’s auto-generated– and then tweaking. Not sure if that’s the official proper way to do it… I look forward to the day when the designer will be a little bit more intuitive, generating the IMultiple result function and necessary classes itself. I would also love it if the designer could automatically REFRESH. (Ever notice that when you change a stored procedure after dragging it into the designer, you’ve got to remove and re-add?)

The End.

Click on pen to

• Creating Backup Jobs in SQL Server 2005
• SQL Server Tips: Shrinking Log

Will need to try.

Have gone a little crazy over current project–will be back during the next possible commercial break to blog about multiple results stored procedures and LINQ. Honest.

Click on pen to

I wanted to use a LINQ with a stored procedure that would return multiple recordsets, like this:


CREATE PROCEDURE spReturnMultiple
AS
--first set
SELECT value1 FROM table1

GO


I found ScottGu’s LINQ to SQL (Part 6 – Retrieving Data Using Stored Procedures) article and Guy Burstein’s Linq to SQL Stored Procedures with Multiple Results – IMultipleResults article that pointed me into the right direction, but not QUITE all the way.

ScottGu’s article showed how you can use a SPROC to return different types of recordset (usually due to an IF statement within the SPROC) and how IMultipleResults were used; in the comment trail someone asks about multiple tables and ScottGu said it could be done in the same way, calling GetResult twice. Burstein’s article showed exactly how you did this. My problem was that the stored procedures returned TABLES. However, my results are based on my own SELECT statements, so there was a little bit of extra that had to be done.

HOW TO (overview):

1. Create the multiple recordset stored procedure
2. Drag the stored procedure into the DBML. Note, this will create the functions and classes for retrieving the first recordset.
3. For each recordset, create the partial class needed based on the columns returned. Use the first recordset’s class as a guide.
4. Now that you’ve got all your recordset classes, convert the function for the stored procedure into IMultipleResults
5. In the application layer, call the stored procedure and use GetResults (and the recordset class) to access the multiple results.Self-note: had to convert the results ToList() in order to use it/iterate later,etc.

I’ll come back here and illustrate each step later. Need to finish up that page now that I’ve got access to my multiple results!

Click on pen to

• Part 1: Introduction to LINQ to SQL
• Part 2: Defining our Data Model Classes
• Part 3: Querying our Database
• Part 4: Updating our Database
• Part 5: Binding UI using the ASP:LinqDataSource Control
• Part 6: Retrieving Data Using Stored Procedures
• Part 7: Updating our Database using Stored Procedures
• Part 8: Executing Custom SQL Expressions
• Part 9: Using a Custom LINQ Expression with the <asp:LinqDatasource> control

Click on pen to

But serious, PIVOT is one of those things that I’m still trying to wrap my brain around.

I’m currently digesting this: Pivots with Dynamic Columns in SQL Server 2005

and modified this examples into one script with temporary tables so I can study it better:

DECLARE @Table1 TABLE (ColId INT,ColName VARCHAR(10))
INSERT INTO @Table1 VALUES(1, ‘Country’)
INSERT INTO @Table1 VALUES(2, ‘Month’)
INSERT INTO @Table1 VALUES(3, ‘Day’)

DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ‘,[' + colName + ']‘,
‘[' + colName + ']‘)
FROM @Table1
ORDER BY colName

DECLARE @Table2 TABLE (tID INT,ColID INT,Txt VARCHAR(10))
INSERT INTO @Table2 VALUES (1,1, ‘US’)
INSERT INTO @Table2 VALUES (1,2, ‘July’)
INSERT INTO @Table2 VALUES (1,3, ‘4′)
INSERT INTO @Table2 VALUES (2,1, ‘US’)
INSERT INTO @Table2 VALUES (2,2, ‘Sep’)
INSERT INTO @Table2 VALUES (2,3, ‘11′)
INSERT INTO @Table2 VALUES (3,1, ‘US’)
INSERT INTO @Table2 VALUES (3,2, ‘Dec’)
INSERT INTO @Table2 VALUES (3,3, ‘25′)

SELECT t2.tID
, t1.ColName
, t2.Txt
FROM @Table1 AS t1
JOIN @Table2
AS t2 ON t1.ColId = t2.ColID

SELECT tID
, [Country]
, [Day]
, [Month]
FROM ( SELECT t2.tID
, t1.ColName
, t2.Txt
FROM @Table1 AS t1
JOIN @Table2
AS t2 ON t1.ColId = t2.ColID
) p PIVOT ( MAX([Txt])
FOR ColName IN ( [Country], [Day],
[Month] ) ) AS pvt
ORDER BY tID ;

Click on pen to