LINQ, Stored Procedures and Multiple Recordsets: How-To
By juliana | June 7, 2008
Okay, here’s my entire test-run of how to get LINQ to work with stored procedures that return multiple recordsets just a dummy project.
Topics: I Canz Code! | 2 Comments »
Maintenance Plans/Backup Jobs in SQL Server
By juliana | May 22, 2008
Nice little tutorial here:
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.
Topics: Uncategorized | No Comments »
LINQ, Stored Procedures and Multiple Recordsets
By juliana | May 15, 2008
Update: This post has a follow up here: LINQ, Stored Procedures and Multiple Recordsets: How-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
--second set
SELECT t1.value1, t2.value2
FROM table1 t1 LEFT JOIN
table2 t2 on t1.value1 = t2.value2
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.
Read the rest of this entry »
Topics: I Canz Code! | No Comments »
ScottGu - Da LINQ Man!
By juliana | May 15, 2008
For my own reference, the various parts of ScottGu’s LINQ series:
- 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
Topics: I Canz Code! | No Comments »
SQL Server 2005’s Pivot…PIVOT, PIVVOOOOT - Ross Gellar, Friends
By juliana | May 9, 2008
Every time I see SQL Server’s 2005 PIVOT, I think of that episode of Friends where Ross tries to guide his friends as they bring up the couch to his apartment.
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:
Topics: I Canz Code! | No Comments »
Vroom, vrooom…. aka Making Pages Load Faster
By juliana | May 7, 2008
Optimizing Page Load Time [die.net]
Best Practicies for Speeding up your Web Site [Yahoo]
Interesting stuff, some excerpts for own reference below the more tag.
Read the rest of this entry »
Topics: Uncategorized | No Comments »
ASP .NET Membership: Where’s the UserID?
By juliana | April 25, 2008
I couldn’t figure this out intuitively, but good news: there is a way to get it from the MembershipUser class.
They don’t call it UserID (duh) but ProviderUserKey
dim user as MembershipUser = Membership.GetUser() dim userGUID as GUID = user.ProviderUserKey dim userID as String = user.ProviderUserKey.toString
Topics: I Canz Code! | No Comments »
SQL Server: Data types text / ntext won’t play with sort or LIKE.
By juliana | April 22, 2008
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Pretty self-explanatory.
In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.
Edit:
COMPARISONS
When comparing (e.g. using LIKE or = ), convert the non text/ntext INTO text. One assumes it’d be something like this:
[...] WHERE TextDataTypeColumn like 'test%'
change to
[...] WHERE TextDataTypeColumn like cast('test%' as text)
Because you’re converting from a “smaller” data type to a larger data type, you never have to face the possibility that data may be trunctaed.
SORTING
I believe sorting will only work on varchar, so there’s no way around it.
When sorting, you’ll have to convert non text/ntext INTO varchar (and remember to give enough or as much space possible). One assumes it’d be something like this:
[...] ORDER BY TextDataTypeColumn
change to
[...] ORDER BY cast(TextDataTypeColumn as varchar(500))
Topics: I Canz Code! | No Comments »
LINQ here, LINQ there, LINQ everywhere
By juliana | April 22, 2008
According to DA GURU (that’s Scott Guthrie), on his article LINQ to SQL (Part 5 - Binding UI using the ASP:LinqDataSource Control)
We will also take advantage of the built-in paging/sorting support within LINQ to SQL to ensure that features like the product listing paging/sorting are performed not in the middle-tier, but rather in the database (meaning only 10 products are retrieved from the database at any given time - we are not retrieving thousands of rows and doing the sorting/paging within the web-server).
Emphasis mine.
Whoohoo! Automatic server side paging. Srsly? Must read more and find a way to implement. Never mind that I’ve already done my stored procedures for the old way of doing server-side paging; this is worth the investment. (Even if some people don’t think so.)
Read the rest of the article here.
ETA: After digging through the articles, I’ve come to the conclusion that this automatic paging only works if you’re LINQ’ing directly to the database tables (e.g. using LINQ expression/syntax). The interface takes care of the iteration so that it only draws out the necessary rows. However, I believe if you’re using LINQ to access a stored procedure, you lose this automatic “skip and take” feature. At least, that’s what it seems to me. Throwing a SP into the DBML only exposes the SP interface, but LINQ would be able to go into the stored procedure and optimize it. So, in order to do paging by LINQ , the stored procedure would have to retrieve all the rows (think: virtual table, except this one has to be constructed completely and wholly every time it’s called) and hand it off the LINQ. Yeouch.
Topics: I Canz Code! | No Comments »
ASP .Net says: Look, Ma, I’m a Javascript!
By juliana | April 19, 2008
Here’s was my problem yesterday, in a cool scripted form:
Mission> Your mission, should you choose to accept, is to allow third party sites to grab from this system you are developing and display our content. These undisclosed third party sites may be in ASP .Net, ASP, PHP, or even… [ominous pause] static HTML. You have no choice but to accept this mission. You may begin self-destructing in 5, 4, 3…
Me> No problem! I’ll use Javascript! I’ll use XMLHttpRequest object! Cool!
Javascript/XMLHttpRequest> Permission denied to call method xmlhttprequest.open.
Me>Um..
JS/XHR> Permission denied to call method xmlhttprequest.open. Bwhahahaha!
Me> Err..
JS/XHR>Permission denied to call method xmlhttprequest.open! Permission denied to call method xmlhttprequest.open! PERMISSION DENIED TO CALL METHOD XMLHTTPREQUEST.OPEN! NEENER NEENER!!
Me> ARGH! ::DIES::
Apparently, XMLHttpRequest does not allow cross-domain scripting for security reasons. I didn’t realize this because I’d always use the object for local connections, but I get it. Cross-domain scripting can lead to some pretty malicious results in the hands of an evil developer. But that doesn’t solve my problem.
Topics: I Canz Code! | No Comments »
« Previous Entries