nth Highest Salary in SQL

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

 --3rd Highest Salary

select e.sal from emp e 
where 2=(select count(distinct sal) from emp 
where sal>e.sal); 

Custom paging in asp.net using stored procedure in gridview

Custom paging in asp.net using stored procedure in gridview

 Why to avoid default paging and use custom paging reason -

     In default paging we are fetching all rows in database that takes more execution time.
      In default paging all rows (records of table) load in memory so memory consumption
      In default paging all rows fetch form back end to front end but we are displaying only some of the rows i.e page-size.


How to do custom paging -

    Need to customize you store procedure
    No Change in binding Gridview or Datalist or Repeater whatever you are using
    Need to populate ( Generate ) Pager (Pagination ) manually ( Code also given below).

 Store Procedure Code :

CREATE PROCEDURE getDeals
      @StartIndex int,
      @PageSize int,
      @TotalCount int OutPut
as

select @TotalCount=count(1) from mstrDeals;
WITH CTE AS
(
   select top(@startIndex+@PageSize-1) ROW_NUMBER() OVER(ORDER BY creationdate) RowNumber,dealid,dealTitle
   from mstrDeals
)
select * from CTE where RowNumber between @startIndex and (@startIndex+@PageSize-1)


Binding Grid Control Code :

public void bindGrid(int currentPage)
{
 int pageSize = 10;
 int _TotalRowCount = 0;

 string _ConStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
 using (SqlConnection con = new SqlConnection(_ConStr))
 {

   SqlCommand cmd = new SqlCommand("getDeals", con);
   cmd.CommandType = CommandType.StoredProcedure;

   int startRowNumber = ((currentPage - 1) * pageSize) + 1;
     
   cmd.Parameters.AddWithValue("@StartIndex", startRowNumber);
   cmd.Parameters.AddWithValue("@PageSize", pageSize);

   SqlParameter parTotalCount = new SqlParameter("@TotalCount", SqlDbType.Int);
   parTotalCount.Direction = ParameterDirection.Output;
   cmd.Parameters.Add(parTotalCount);

   SqlDataAdapter da = new SqlDataAdapter(cmd);
   DataSet ds = new DataSet();
   da.Fill(ds);

   _TotalRowCount = Convert.ToInt32(parTotalCount.Value);

   grdCustomPagging.DataSource = ds;
   grdCustomPagging.DataBind();

   generatePager(_TotalRowCount, pageSize, currentPage);

 }
}


<asp:GridView Width="500" runat="server" ID="grdCustomPagging">
</asp:GridView>


Then we need to generate pager with total no of rows, page size and current page, below is the sample code that populating pager here total number of pager links is 5 that can be customize further by resetting local variable named totalLinkInPage and have first and last button also which automatic enable or disable when current page is first or last accordingly.
Generate Pager Code :

public void generatePager(int totalRowCount, int pageSize, int currentPage)
{
  int totalLinkInPage = 5;
  int totalPageCount = (int)Math.Ceiling((decimal)totalRowCount / pageSize);

  int startPageLink = Math.Max(currentPage - (int)Math.Floor((decimal)totalLinkInPage / 2), 1);
  int lastPageLink = Math.Min(startPageLink + totalLinkInPage - 1, totalPageCount);

  if ((startPageLink + totalLinkInPage - 1) > totalPageCount)
  {
      lastPageLink = Math.Min(currentPage + (int)Math.Floor((decimal)totalLinkInPage / 2), totalPageCount);
      startPageLink = Math.Max(lastPageLink - totalLinkInPage + 1, 1);
  }

  List<ListItem> pageLinkContainer = new List<ListItem>();

  if (startPageLink != 1)
      pageLinkContainer.Add(new ListItem("First", "1", currentPage != 1));
  for (int i = startPageLink; i <= lastPageLink; i++)
  {
      pageLinkContainer.Add(new ListItem(i.ToString(), i.ToString(), currentPage != i));
  }
  if (lastPageLink != totalPageCount)
      pageLinkContainer.Add(new ListItem("Last", totalPageCount.ToString(), currentPage != totalPageCount));

  dlPager.DataSource = pageLinkContainer;
  dlPager.DataBind();
}

protected void dlPager_ItemCommand(object source, DataListCommandEventArgs e)
{
   if (e.CommandName == "PageNo")
   {
       bindGrid(Convert.ToInt32(e.CommandArgument));
   }
}


<asp:DataList CellPadding="5" RepeatDirection="Horizontal" runat="server" ID="dlPager"
    onitemcommand="dlPager_ItemCommand">
    <ItemTemplate>
       <asp:LinkButton Enabled='<%#Eval("Enabled") %>' runat="server" ID="lnkPageNo" Text='<%#Eval("Text") %>' CommandArgument='<%#Eval("Value") %>' CommandName="PageNo"></asp:LinkButton>
    </ItemTemplate>
</asp:DataList>

disable space key press event in a textbox

disable space key press event in a textbox 

 <asp:TextBox ID="TextBox1" runat="server" onkeypress="return (event.keyCode != 32&&event.which!=32)"></asp:TextBox>

 

Send Mail by hotmail

Send Mail by hotmail

protected void SendMail()
    {

        SmtpClient SmtpServer = new SmtpClient("smtp.live.com");
        var mail = new MailMessage();
        mail.From = new MailAddress("xxx@hotmail.com");
        mail.To.Add("xxx@gmail.com");
        mail.Subject = "Your Sub";
        mail.IsBodyHtml = true;
        string htmlBody;
        htmlBody = "HTML code";
        mail.Body = htmlBody;
        SmtpServer.Port = 587;
        SmtpServer.UseDefaultCredentials = false;
        SmtpServer.Credentials = new System.Net.NetworkCredential("xxx@hotmail.com", "xxxxxx");
        SmtpServer.EnableSsl = true;
        SmtpServer.Send(mail);
    }

Query to find Number of Parameters in a Stored Procedure or Function in Sql Server?

SELECT 
    p.name AS Parameter,        
    t.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters p 
    ON sp.object_id = p.object_id
JOIN sys.types t
    ON p.system_type_id = t.system_type_id
WHERE sp.name = '<name>'

How to get list of all stored procs/functions/view which refer a table?

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%table1%'

How do I find a stored procedure containing a text?

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%property_doc%'
    AND ROUTINE_TYPE='PROCEDURE'

SELECT OBJECT_NAME(id)
    FROM SYSCOMMENTS
    WHERE [text] LIKE '%property_doc%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%property_doc%'

Create table runtime

<%@ Page Language="VB" %>
<script runat="server">
Sub Button1_Click(sender As Object, e As EventArgs)
Dim numrows As Integer
Dim numcells As Integer
Dim i As Integer
Dim j As Integer
Dim r As TableRow
Dim c As TableCell
numrows = CInt(DropDown1.SelectedItem.Value)
numcells = CInt(DropDown2.SelectedItem.Value)
For j = 0 To numrows-1
r = new TableRow()
For i = 0  To numcells-1
c = new TableCell()
c.Controls.Add(new LiteralControl("row " & j & ", cell " & i))
r.Cells.Add(c)
Next i
Table1.Rows.Add(r)
Next j
End Sub
</script>
<html>
<head>
<title>vbdotnetheaven.Com Article</title>
</head>
<body>
<form id="Form1" runat="server">
Table rows:
<asp:DropDownList id="DropDown1" runat="server">
<asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
</asp:DropDownList>
Table cells:
<asp:DropDownList id="DropDown2" runat="server">
<asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
</asp:DropDownList>
<asp:button id="Button1" onclick="Button1_Click" runat="server" Text="Generate Table"></asp:button>
<hr>
<asp:Table id="Table1" runat="server" Gridlines="Both" BorderWidth="1" BorderColor="black" CellSpacing="0" CellPadding="5" Font
Size="8pt" Font-Names="Verdana"></asp:Table>
</form>
</body>
</html>

DataTable as Parameter in sql

Create a User-Defined TableType in your database:

CREATE TYPE [dbo].[TableType] AS TABLE(
[UserId] int NOT NULL,
[UserName] [nvarchar](128) NULL,
[Password] [varchar](30)

) 

define a parameter in your Stored Procedure:

CREATE PROCEDURE [dbo].[InsertTable]
@myTableType TableType readonly
AS
BEGIN
insert into [dbo].Users select * from @myTableType 
END

 send your DataTable directly to sql server:

 SqlCommand command = new SqlCommand("InsertTable");
 command.CommandType = CommandType.StoredProcedure;
 var dt = new DataTable(); //create your own data table
 command.Parameters.Add(new SqlParameter("@myTableType", dt));
 command.ExecuteNonQuery();