Sunday, November 30, 2008

Paging in SQL Server 2005 using OVER

Full detail of "OVER" can be found on following link:

Following is the code snippet to show how quick and simply pagination can be done using OVER:



USE northwind
DECLARE  @startIndex INT,
         @pageLength INT
SET @startIndex = 6
SET @pageLength = 5

SELECT *
FROM   (SELECT o.shipname,
               o.shipaddress,
               Row_number()
                 OVER(ORDER BY o.customerid DESC) AS rownumber
        FROM   orders AS o
               JOIN [order details] AS od
                 ON od.orderid = o.orderid
        WHERE  o.customerid = 'VINET') AS shipmentdetail
WHERE  rownumber BETWEEN @startIndex AND (@startIndex + @pageLength - 1)


Sunday, November 23, 2008

Creating a user and giving permissions in database

Sometimes we need to create special users for our database. For example, in one of my applications, I need to provide appropriate permissions for remote server window service (which is running as a local system account on remote server), so that it can connect to my database and can perform read-write operations. I found following links helpful for understanding basics of SQL server security:

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

http://msdn.microsoft.com/en-us/library/ms187750.aspx

To cut long story short, following is SQL command to create and give read-write permissions to remote machine. Suppose remore server name is 'TESTDOMAIN\testserver'.


Go
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'testdomain\testserver$')
CREATE LOGIN [testdomain\testserver$] FROM WINDOWS
GO

IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'testdomain\testserver$')
CREATE USER [testdomain\testserver$] FOR LOGIN [testdomain\testserver$] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [testdomain\testserver$]
EXEC sp_addrolemember 'db_datareader', 'testdomain\testserver$'
EXEC sp_addrolemember 'db_datawriter', 'testdomain\testserver$'
GO
GO

Saturday, November 15, 2008

Mime Types for Office 2007 documents

Sometimes you need to correctly set the mime type of file for uploading or downloading purpose. I found this link to help you in this situation, for you convienence, content types are below for docx file.

+---------------------------------------------------------------------------+-------+
| application/vnd.openxmlformats-officedocument.wordprocessingml.document | .docx |
| application/vnd.openxmlformats-officedocument.wordprocessingml.template | .dotx |
| application/vnd.openxmlformats-officedocument.presentationml.presentation | .pptx |
| application/vnd.openxmlformats-officedocument.presentationml.slideshow | .ppsx |
| application/vnd.openxmlformats-officedocument.presentationml.template | .potx |
| application/vnd.openxmlformats-officedocument.spreadsheetml.sheet | .xlsx |
| application/vnd.openxmlformats-officedocument.spreadsheetml.template | .xltx |
+---------------------------------------------------------------------------+-------+

Example of IEnumerable and Yield

Following is very basic code snippet to create a simple IEnumerable class. (Question (1): what's the difference between IEnumerable and IEnumerator? Question (2): does anyone in this world still  implement IEnumerator?) 
using System;
using System.Collections;

public class Person
{
    public Person(string fName, string lName)
    {
        this.firstName = fName;
        this.lastName = lName;
    }

    public string firstName;
    public string lastName;
}

public class People : IEnumerable
{
    private Person[] _people;
    public People(Person[] pArray)
    {
        _people = new Person[pArray.Length];

        for (int i = 0; i <>
        {
            _people[i] = pArray[i];
        }
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        foreach (Person p in _people) 
yield return p;
    }
}

public class MyClass
{
public static void RunSnippet()
{
Person[] peopleArray = new Person[3]
        {
            new Person("John", "Smith"),
            new Person("Jim", "Johnson"),
            new Person("Sue", "Rabon"),
        };

        People peopleList = new People(peopleArray);
        foreach (Person p in peopleList)
            Console.WriteLine(p.firstName + " " + p.lastName);
}
#region Helper methods
public static void Main()
{
try
{
RunSnippet();
}
catch (Exception e)
{
string error = string.Format("---\nThe following error occurred while executing the snippet:\n{0}\n---", e.ToString());
Console.WriteLine(error);
}
finally
{
Console.Write("Press any key to continue...");
Console.ReadKey();
}
}
#endregion
}