Get Top N Rows For Each Group

Consider a table which store the Total Marks of each student in each standard

Create Table TblMarksheet
(
  StudentId varchar(40),
  Standard varchar(max),
  TotalMarks int
)

Insert Some Values into it

Insert into TblMarksheet
Select 1,1,10 union all
Select 2,1,50 union all
Select 3,1,5 union all
Select 4,1,30 union all
Select 5,2,0 union all
Select 6,2,9 union all
Select 7,2,8 union all
Select 8,2,10 union all
Select 9,3,1 union all
Select 10,3,3 union all
Select 11,3,6 union all
Select 12,3,2 union all
Select 13,4,10 union all
Select 15,4,9 union all
Select 15,4,8 union all

---

Now what we want is we want to find the Top 3 Students from Each Standard.
Query:-

With MyTempView
(
  StudentId varchar(40),
  Standard varchar(max),
  TotalMarks int,
  RowIndex int
)
as
(
  Select *,row_number () over (partition by Standard order by TotalMarks desc)
)

Select StudentId,Standard,TotalMarks From MyTEmpView where RowIndex<=3

Insert value into Identity Column

• Create Table

  CREATE TABLE [dbo].[TblMyFriends]
  (
              [FriendId] [int] IDENTITY(1,1) NOT NULL,
              [FriendName] [varchar](50) NOT NULL
  )
  

• Insert Values

  Insert into [TblMyFriends](FriendName) values('Dipal')
  Insert into [TblMyFriends](FriendName) values('Sai')
  Insert into [TblMyFriends](FriendName) values('Harshad')
  Insert into [TblMyFriends](FriendName) values('Pankaj')

• Insert Values with Explicity Identity Value

  Insert into [TblMyFriends](FriendId,FriendName) values(44,'New Friend')
  --Throw Error
  --   Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT --is set to OFF.
  
  SET IDENTITY_INSERT [TblMyFriends] ON
  INSERT INTO dbo.[TblMyFriends](FriendId,FriendName) VALUES(44, 'New Friend')
  SET IDENTITY_INSERT dbo.[TblMyFriends] OFF
  

Hope This will help you,Expecting Comments....

Guid.Parse in .Net 4.0

In .net Frameword  4.0 a new Guid.Parse method is introduced which works just like any normal parse method.

class GuidTest
{
   static void Main(string[] args)
   {
       string StrId= "d668900g-467j-2357-8699-2hkiot456hj7";
       Guid ObjPriId= Guid.Parse(StrId);
       Console.WriteLine(ObjPriId.ToString());
       Console.ReadLine();
 
 
   }
}

SqlDataReader To DataTable

class ClsDataReaderToDataTable
{
    static void Main(string[] args)
    {
        string StrQuery = "SELECT * from MyTable";
        DataTable DtCustomers = new DataTable();
        using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionstring"].ConnectionString))
        {
            oConn.Open();
            SqlCommand SCmd = new SqlCommand(StrQuery, oConn);
            SqlDataReader SDr = SCmd.ExecuteReader();
            DtCustomers.Load(SDr);
        }
        Console.ReadKey();
    }
}

var vs dynamic

• 
  
• In case of var type is decided at compile time by compiler=>Complie time Type Interference.
• As per MSDN,-
           var is a strongly implicitly typed local variable for which the compiler is able to determine the type from the initialization expression.
• Introduced in 3.5
• 
  
• In case of dynamic type is decided at runtime=>Run time Type Interference.
• As per MSDN,-         dynamic lookup allows you to write method, operator and indexer calls, property and field accesses, and even object invocations which bypass the normal static binding of C# and instead gets resolved dynamically.
• Introduced in 4.0

Example:-

string MyString="Sukesh Marla";
var MyVarVariable=MyString;
Response.Write(MyVarVariable.MyMethod());

when we run the above code,we get a error  'string does not contain a definition for 'MyMethod' and no extension method 'MyMethod' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?
means var knows that what is assgned to it.

string MyString="Sukesh Marla";
dynamic MyVarVariable=MyString;
Response.Write(MyVarVariable.MeMethod());

this will get complied.

and throws exception at runtime,if you wont define any extension method MyMethod for string class.

int.Parse vs Convert.ToInt32 vs int.TryParse

All int.Parse and Convert.ToInt32 and int.TryParse are used to convert string into the integer.

• Convert.ToInt32 handle null and returns ‘0’ as output.
• int.parse is not going to handle NULL and will give a Argument Null Exception. 
• int.TryParse also take a second integer parameter which will be an output parameter.This method hanles all kind of exception and returns result as output parameter.

      
      string ValidInteger = "45";
      string nullString = null;
      string InvalidString="45.1";

      int Result;
      
      #region int.Parse
         // It will perfectly convert interger
         Result= int.Parse(ValidInteger);
  
        // It will raise Argument Null Exception
        Result= int.Parse(nullString);

       //It will raise Format Exception
       int.Parse(InvalidString);

     #end region

     #region Convert.ToInt32
       //It will perfectly convert integer
       Result= Convert.ToInt32(ValidInteger);

       //It will ouput as 0 if Null string is there
       Result= Convert.ToInt32(nullString);

       //It will raise Format Exception
       Result= Convert.ToInt32(InvalidString);
     #end region

    #region int.TryParse
      
       Result=-1; 
      
       //Value of Result will be 45
       int.TryParse(ValidInteger,out Result);

       //Value of Result will be -1
       int.TryParse(nullString,out Result);

       //Value of Result will be -1
       int.TryParse(InvalidString,out Result);
     #end region

SQL Server Foreign Key Update and Delete Rules

• Foreign key constraints are used to maintain integrity among related data in different tables. 
• It may be possible that foreign key value is NULL which indicates a particular record has no parent record. But if a value exists, then it is bound to have an associated value in a parent table.
• When applying update or delete operations on parent tables there may be different requirements about the effect on associated values in child tables. There are four available options in SQL Server 2005 and 2008 as follows:


• No Action
• Cascade
• SET NULL
• SET Default

	Update operation on parent table	Delete operation on parent table
No Action	Not allowed. Error message would be generated.	Not allowed. Error message would be generated.
Cascade	Associated values in child table would also be updated.	Associated records in child table would also be deleted.
Set NULL	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.	Associated values in child table would be set to NULL. Foreign key column should allow NULL values to specify this rule.
Set Default	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and update operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.	Associated values in child table would be set to default value specified in column definition. Also default value should be present in primary key column. Otherwise basic requirement of FK relation would fail and delete operation would not be successful. If no default value is provided in foreign key column this rule could not be implemented.

Steps for applying the rule

1. In Object Explorer, right-click the table with the constraint, and click Design.
   The table opens in Table Designer.
2. From the Table Designer menu, click Relationships.
3. In the Foreign Key Relationships dialog box, select the relationship in the Selected Relationship list.
4. In the grid, click Delete Rule or Update Rule and choose an action from the drop-down list box to the left of the property.

Hope its helpful.


For any queries you can put comments.

Bind Enum to a DropDown

So, you have an enum defined as follows:

public enum Gender
{
        Male= 0,
        Female= 1
}

Code to Bind the Dropdown

string[] names = Enum.GetNames(typeof(Gender));
var values = (Gender[])Enum.GetValues(typeof(Gender));
for (int i = 0; i < names.Length; i++)
{
      DdlGender.Items.Add(new ListItem(names[i], values[i].ToString()));
}

Export Gridview to Excel

Steps:-
1.Populate the Gridview ->MyGridview with the Data.
2.Override the VerifyRenderingInServerForm method. as

public override void VerifyRenderingInServerForm(Control control)
{

}

3.Use the following Code snippet (In button click or somewhere) - It will just export the grid into excel.

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvUsers.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

---

Points to remember:-


-->When you try to export the GridView which contains LinkButtons and Paging enabled then you might see the error "RegisterForEventValidation can only be called during Render"
     You can easily handle this error by going to the page source and turning the EnableEventValidation = "false".

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

We will speak about the Enable EnableEventValidation and VerifyRenderingInServerForm
in subsequent posts.

Asp.net Directives

The ASP.NETsupports the following directives.

@ Page	Defines page-specific attributes used by the ASP.NET page parser and compiler. Can be included only in .aspxfiles.
@ Control	Defines control-specific attributes used by the ASP.NET page parser and compiler. Can be included only in .ascxfiles (user controls).
@ Import	Explicitly imports a namespace into a page or user control.
@ Implements	Declaratively indicates that a page or user control implements a specified .NET Framework interface.
@ Register	Associates aliases with namespaces and class names, thereby allowing user controls and custom server controls to be rendered when included in a requested page or user control.
@ Assembly	Links an assembly to the current page during compilation, making all the assembly's classes and interfaces available for use on the page.
@ Master	Identifies an ASP.NET master page.
@ WebHandler	Identifies an ASP.NET IHttpHandler page.
@PreviousPageType	Provides the means to get strong typing against the previous page as accessed through the PreviousPageproperty.
@ MasterType	Assigns a class name to the Master property of an ASP.NET page, so that the page can get strongly typed references to members of the master page.
@ OutputCache	Declaratively controls the output caching policies of a page or user control.
@ Reference	Declaratively links a page or user control to the current page or user control.

Original Post by http://blog-mstechnology.blogspot.com/2009/06/aspnet-page-directives.html

Insert Into Vs Select Into

INSERT INTO SELECT

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table or From any other sources like views,Derived Tabes,TempTables etc.

USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

SELECT INTO 
This method is used when table is not created earlier and needs to be created when data from one table Or any other sources is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). 

Reference : - http://blog.sqlauthority.com/