August « 2008 « IBlog by Himanshu Trivedi

August 6, 2008

OpenID, myOpenID Rocks!

Filed under: announcements,open id — Himanshu @ 6:37 am

I have been using openID from last 5-6 months as and when I got chance to use it. It’s getting popular more and more now a days. I see more and more sites supporting it. When first time I learned about it, first thing that came to my mind was to support openID login for my site. And second thing that came to my mind was, MS passport got competition ;). I haven’t seen passport authentication much popular apart from Microsoft sites.

As I didn’t had anything to secure on my site. So far I haven’t actively started working on to support openID login. It also make sense to have user ids for features like customization, but my site is not supporting that either. I would like to support openID authentication as soon as I finish coding with site-spaces that supports customization and security. Mainly when I need user authentication.

On root (http://root.objectpattern.com) page, that is like dashboard, I’m planning to have UI that supports customization/personalization. And once I finish coding Media Gallery, I will need authorization their for different spaces/albums of it, yeah some media will be private to my family and friends. I have done some initial tests using DotNetOpenID (http://code.google.com/p/dotnetopenid/). But that library didn’t worked well on GoDaddy hosting, because of trust level that GoDaddy Deluxe hosting gets. Haven’t done much research on that to fix it some how.

Also did some initial tests with myOpenId (http://myopenid.com) to setup such that I/someone can have openId as http://openid.objectpattern.com/. Actually, it wasn’t that difficult to do. I have my id ready, as http://openid.objectpattern.com/himanshu I liked myOpenID, it allows to have certificate sign on.

Anyway, I’m posting this to let you know a) openID Rocks 🙂 b) I’m planning to be openID consumer, c) setup with help of myOpenId is ready to have openID as openid.objectpattern.com/*.

By the way, did I told you what is openID? Oh, never mind just take a look at it’s official site (http://www.openid.net) if you don’t know what is openID and after that if you have any further queries about it, send me email or comment your query against this post, and I will try answering them.

Comments Off

August 5, 2008

Paging of SQL Server records

Filed under: .net,sql server — Himanshu @ 6:29 am

In one of my ASP.NET, SQL Server project, we were expected to provide paging in the ASP.NET GridView. Microsoft’s very common sample will do this using Dataset filled with all records. If someone is doing GridView paging, s/he is doing it not waste server’s or client’s precious resources, Isn’t it?

To me, it make more sense to do paging of records being fetched in server memory along with view paging. What if table in question has more then 100,000 (1 lack) records?

Digging a bit further, I found a resource which explains how to do paging of records being sent from stored procedure. But it was expecting to have auto identity as primary key in the table. And we were so fortunate that we couldn’t have db design such that we can afford to have auto identity primary key, and so that solution to the paging problem. On doing some more finding, we all agreed to a solution that is described below. It’s not fully optimized, as it do not have any optimization for SQL Server to prepare result sets. But worked for our need in specific project as even in 100,000 records it was working very fast.

In our solution, stored procedure that will fetch records from table and return to caller will needs to have two addition parameters, @RowIndexFrom and @RowIndexTo

Getting customers by page will look like following,

CREATE PROCEDURE [dbo].[GetCustomersPage]
(
    @RowIndexFrom int,
    @RowIndexTo int
)
AS
BEGIN
    SET NOCOUNT ON;

    WITH IndexedCustomers AS
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY Id) AS rowIndex,
            Customers.*
        FROM
            Customers
    )
    SELECT * FROM IndexedCustomers WHERE rowIndex BETWEEN @RowIndexFrom AND @RowIndexTo

    SELECT COUNT(*)
    FROM
        Customers
END
GO

The stored procedure returns two result set, first will be records for given page (defined by different between @RowIndexFrom and @RowIndexTo parameters). And the second result set will give total number of records for give query. Second result set will help preparing pages list. So, if first page needs to be retrieved of page size 10, parameter values should be 1 and 10 respectively.

Comments Off