How to fetch the nth highest salary from a table without using TOP and sub-query?

WITH Salaries AS
(
    SELECT Basic_Salary, ROW_NUMBER() OVER(ORDER BY Basic_Salary DESC) AS 'RowNum'
    FROM dbo.EMPLOYEE
)
SELECT
  Basic_Salary
FROM
  Salaries
WHERE
   RowNum <= 5

New Code "CONVERT NUMERIC TO WORD"

using System;

/// <summary>

/// Summary description for NumberToWordsConvertor

/// </summary>

public class NumberToWordsConvertor

{

public NumberToWordsConvertor()

{

// TODO: Add constructor logic here

}

public string NumberToText(int number)

{

if (number == 0) return "Zero";

if (number == -2147483648) return "Minus Two Hundred and Fourteen Crore Seventy Four Lakh Eighty Three Thousand Six Hundred and Forty Eight";

int[] num = new int[4];

int first = 0;

int u, h, t;

System.Text.StringBuilder sb = new System.Text.StringBuilder();

if (number < 0) { sb.Append("Minus "); number = -number; }

string[] words0 = { "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine " };

string[] words1 = { "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen " };

string[] words2 = { "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety " };

string[] words3 = { "Thousand ", "Lakh ", "Crore " };

num[0] = number % 1000; // units

num[1] = number / 1000;

num[2] = number / 100000;

num[1] = num[1] - 100 * num[2]; // thousands

num[3] = number / 10000000; // crores

num[2] = num[2] - 100 * num[3]; // lakhs

for (int i = 3; i > 0; i--)

{ if (num[i] != 0)

{ first = i; break; } }

for(int i = first ; i >= 0 ; i--)

{ if (num[i] == 0) continue;



u = num[i] % 10; // ones

t = num[i] / 10;

h = num[i] / 100; // hundreds

t = t - 10 * h; // tens

t = num[i] / 10;

h = num[i] / 100; // hundreds

t = t - 10 * h; // tens

if (h > 0) sb.Append(words0[h] + "Hundred ");

if (u > 0 || t > 0)

{

if (h > 0 || i == 0) sb.Append("and ");

if (t == 0) sb.Append(words0[u]);

else if (t == 1) sb.Append(words1[u]);

else sb.Append(words2[t-2] + words0[u]);

}

           if (i != 0) sb.Append(words3[i - 1]);

}
return sb.ToString().TrimEnd();

}

}

Conver Numeric value to Words

using System;

/// <summary>

/// Summary description for NumberToWordsConvertor

/// </summary>

public class NumberToWordsConvertor

{

public NumberToWordsConvertor()

{

// TODO: Add constructor logic here

}

// Single-digit and small number names

private string[] _smallNumbers = new string[] { "Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen" };

// Tens number names from twenty upwards

private string[] _tens = new string[] { "", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

// Scale number names for use during recombination

private string[] _scaleNumbers = new string[] { "", "Thousand", "Million", "Billion" };

// Converts an integer value into English words

public string NumberToWords(int number)

{

// Zero rule

if (number == 0)

return _smallNumbers[0];

// Array to hold four three-digit groups

int[] digitGroups = new int[4];

// Ensure a positive number to extract from

int positive = Math.Abs(number);

// Extract the three-digit groups

for (int i = 0; i < 4; i++)

{

digitGroups[i] = positive % 1000;

positive /= 1000;

}

// Convert each three-digit group to words

string[] groupText = new string[4];

for (int i = 0; i < 4; i++)

groupText[i] = ThreeDigitGroupToWords(digitGroups[i]);

// Recombine the three-digit groups

string combined = groupText[0];

bool appendAnd;

// Determine whether an 'and' is needed

appendAnd = (digitGroups[0] > 0) && (digitGroups[0] < 100);

// Process the remaining groups in turn, smallest to largest

for (int i = 1; i < 4; i++)

{

// Only add non-zero items

if (digitGroups[i] != 0)

{

// Build the string to add as a prefix

string prefix = groupText[i] + " " + _scaleNumbers[i];

if (combined.Length != 0)

prefix += appendAnd ? "" : ", ";

// Opportunity to add 'and' is ended

appendAnd = false;

// Add the three-digit group to the combined string

combined = prefix + combined;

}

}

// Negative rule

if (number < 0)

combined = "Negative " + combined;

return combined;

}

// Converts a three-digit group into English words

private string ThreeDigitGroupToWords(int threeDigits)

{

// Initialise the return text

string groupText = "";

// Determine the hundreds and the remainder

int hundreds = threeDigits / 100;

int tensUnits = threeDigits % 100;

// Hundreds rules

if (hundreds != 0)

{

groupText += _smallNumbers[hundreds] + " Hundred";

if (tensUnits != 0)

groupText += "";

}

// Determine the tens and units

int tens = tensUnits / 10;

int units = tensUnits % 10;

// Tens rules

if (tens >= 2)

{

groupText += _tens[tens];

if (units != 0)

groupText += "" + _smallNumbers[units];

}

else if (tensUnits != 0)

groupText += _smallNumbers[tensUnits];

return groupText;

}

}

Generate five digit Random Number

public string GetRandomString(int seed)
    {
        //use the following string to control your set of alphabetic characters to choose from
        //for example, you could include uppercase too
        const string alphabet = "abcdefghijklmnopqrstuvwxyz";

        // Random is not truly random,
        // so we try to encourage better randomness by always changing the seed value
        Random rnd = new Random((seed + DateTime.Now.Millisecond));

        // basic 5 digit random number
        string result = rnd.Next(10000, 99999).ToString();

        // single random character in ascii range a-z
        string alphaChar = alphabet.Substring(rnd.Next(0, alphabet.Length-1),1);

        // random position to put the alpha character
        int replacementIndex = rnd.Next(0, (result.Length - 1));
        result = result.Remove(replacementIndex, 1).Insert(replacementIndex, alphaChar);

        return result;
    }

Import Excel Data Into An ASP.NET GridView using OLEDB

using System.Data.OleDb;
using System.Data;
public partial class UploadD : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e)
{
string cnstr = “Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\a.xls;”+ “Extended Properties=Excel 8.0″;
OleDbConnection oledbConn = new OleDbConnection(cnstr);
string strSQL = “SELECT * FROM [Sheet$]“;
OleDbCommand cmd = new OleDbCommand(strSQL, oledbConn);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}

Payment gateway implementation C# in asp.Net

Copy paste following code to implement payment gateway using c#.net:
String post_url = “https://test.authorize.net/gateway/transact.dll“;
Hashtable post_values = new Hashtable();
//the API Login ID and Transaction Key must be replaced with valid values
post_values.Add(“x_login”, “6zz6m5N4Et”);
post_values.Add(“x_tran_key”, “9V9wUv6Yd92t27t5″);
post_values.Add(“x_delim_data”, “TRUE”);
post_values.Add(“x_delim_char”, ‘|’);
post_values.Add(“x_relay_response”, “FALSE”);
post_values.Add(“x_type”, “AUTH_CAPTURE”);
post_values.Add(“x_method”, “CC”);
post_values.Add(“x_card_num”, “378282246310005″);
post_values.Add(“x_exp_date”, “0809″);
post_values.Add(“x_amount”, “99999.00″);
post_values.Add(“x_description”, “Sample Transaction”);
post_values.Add(“x_first_name”, “John”);
post_values.Add(“x_last_name”, “Doe”);
post_values.Add(“x_address”, “1234 Street”);
post_values.Add(“x_state”, “WA”);
post_values.Add(“x_zip”, “98004″);
// Additional fields can be added here as outlined in the AIM integration
// guide at: http://developer.authorize.net
// This section takes the input fields and converts them to the proper format
// for an http post. For example: “x_login=username&x_tran_key=a1B2c3D4″
String post_string = “”;
foreach(DictionaryEntry field in post_values)
{
post_string += field.Key + “=” + field.Value + “&”;
}
post_string = post_string.TrimEnd(‘&’);

// create an HttpWebRequest object to communicate with Authorize.net
HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(post_url);
objRequest.Method = “POST”;
objRequest.ContentLength = post_string.Length;
objRequest.ContentType = “application/x-www-form-urlencoded”;
// post data is sent as a stream
StreamWriter myWriter = null;
myWriter = new StreamWriter(objRequest.GetRequestStream());
myWriter.Write(post_string);
myWriter.Close();
// returned values are returned as a stream, then read into a string
String post_response;
HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse();
using (StreamReader responseStream = new StreamReader(objResponse.GetResponseStream()) )
{
post_response = responseStream.ReadToEnd();
responseStream.Close();
}
// the response string is broken into an array
// The split character specified here must match the delimiting character specified above
Array response_array = post_response.Split(‘|’);
// the results are output to the screen in the form of an html numbered list.
resultSpan.InnerHtml += “<OL> \n”;
foreach (string value in response_array)
{
resultSpan.InnerHtml += “<LI>” + value + “&nbsp;</LI> \n”;
}
resultSpan.InnerHtml += “</OL> \n”;
// individual elements of the array could be accessed to read certain response
// fields. For example, response_array[0] would return the Response Code,
// response_array[2] would return the Response Reason Code.
// for a list of response fields, please review the AIM Implementation Guide

Stored Procedure (Create autogenerate column in table & Fetch data)By Cursor--Best Use of cursor

CREATE  PROC SP_FINAL_REPORT_NS_KG  --SP_FINAL_REPORT_NS_KG 2,13,'A',6,'satyam',1   
(@SessionId int,@ClassId int,@Section varchar(2),     
 @TestTypeId int,@UserId varchar(40),@Criteria int     
)      
AS BEGIN       
IF(@Criteria=1)     
BEGIN     
------------------------------------       
CREATE TABLE #TMP       
(       
ID INT NULL,SNAME VARCHAR(50) NULL       
)       
INSERT INTO #TMP EXEC sp_getacademicsubject @UserId,@SessionId,@ClassId ---'Devender',2,13       
--SELECT * FROM #TMP       
------------------------------------       
CREATE TABLE #TMP1       
(ROLLNO INT NULL,STUDENT_ID INT NULL,       
 STUDENT_NAME VARCHAR(50) NULL)       
       
DECLARE @SQL NVARCHAR(1000)       
DECLARE @COLS NVARCHAR(1000)        
DECLARE @SNAME VARCHAR(25)       
       
DECLARE CUR_SUB CURSOR FAST_FORWARD FOR       
SELECT SNAME FROM #TMP       
OPEN CUR_SUB       
FETCH NEXT FROM CUR_SUB INTO @SNAME       
 WHILE(@@FETCH_STATUS=0)       
 BEGIN       
  SET @COLS=@SNAME + ' ' + 'VARCHAR(5)'        
  SELECT  @SQL='ALTER TABLE #TMP1 ADD ' + @COLS       
  EXEC(@SQL)       
FETCH NEXT FROM CUR_SUB INTO @SNAME       
END       
 PRINT @sql     
 CLOSE CUR_SUB                                             
 DEALLOCATE CUR_SUB         
-------------------------------------       
DECLARE @SID INT       
DECLARE @LEN INT       
DECLARE @OBT_MARKS VARCHAR(8)       
DECLARE @GRADE VARCHAR(5)       
DECLARE @EVL_TYPE_ID VARCHAR(2)       
DECLARE @STUDENTID VARCHAR(10)       
DECLARE @ROLLNO VARCHAR(5)        
DECLARE @STUDENT_NAME VARCHAR(50)       
SET @COLS=NULL       
SET @SQL=NULL       
       
DECLARE MAIN_CURSOR CURSOR FAST_FORWARD FOR       
  select distinct STM.Current_RollNo,SM._studentID,SM._studentname                         
  from tbl_StudentMaster SM INNER JOIN                          
  student_table_sessionwise STM on SM._studentID=STM.Student_Id                         
  where STM.SessionId=@SessionId and STM.Class=@ClassId  and STM.section=@Section                       
         
 OPEN MAIN_CURSOR        
 FETCH NEXT FROM MAIN_CURSOR INTO @ROLLNO,@STUDENTID,@STUDENT_NAME       
 WHILE (@@FETCH_STATUS=0)       
 BEGIN     
      
      ---------------------------------------------------------------------       
      DECLARE @COLL VARCHAR(1000)     
      SET @COLL=''    SET @SQL=''     
      DECLARE EX_CURSOR CURSOR FAST_FORWARD FOR       
      SELECT ID FROM #TMP        
      OPEN EX_CURSOR        
      FETCH NEXT FROM EX_CURSOR INTO @SID       
      WHILE (@@FETCH_STATUS=0)       
      BEGIN       
                  
           SELECT @OBT_MARKS=_OBT_MARKS,@GRADE=_GRADE,       
           @EVL_TYPE_ID=_EVL_TYPE_ID FROM        
           tbl_add_Marks_NS_KG WHERE  _academicId=@SessionId AND        
           _test_typeID=@TestTypeId AND _classID=@ClassId  AND        
           _studentId=@STUDENTID AND _subjectId=@SID       
           
            SET @OBT_MARKS=ISNULL(@OBT_MARKS,'0')   
            IF(@OBT_MARKS='0')   
            BEGIN   
            SET @OBT_MARKS=ISNULL(@GRADE,'')   
            END   
      
      
            SET @COLL=@COLL + ''''+@OBT_MARKS+''''+','    
   
            SET @OBT_MARKS=NULL       SET @EVL_TYPE_ID=NULL     
            SET @GRADE=NULL           SET @SID=NULL    
          
      FETCH NEXT FROM EX_CURSOR INTO @SID       
      END            
           
      SET @LEN=LEN(@COLL)       
      SET @COLL=SUBSTRING(@COLL,1,LEN(@COLL)-1)   
      set @ROLLNO=ISNULL(@ROLLNO,'')     
      SET @SQL='insert INTO #TMP1 VALUES('''+@ROLLNO+''','''+@STUDENTID+''','''+@STUDENT_NAME+''','+@COLL+')'       
      EXEC(@SQL)     
           
      CLOSE EX_CURSOR                                             
      DEALLOCATE EX_CURSOR        
    -----------------------------------------------------------------------------       
   SET @ROLLNO=NULL     SET @STUDENTID=NULL             SET @STUDENT_NAME=NULL         
   FETCH NEXT FROM MAIN_CURSOR INTO @ROLLNO,@STUDENTID,@STUDENT_NAME         
   END       
   CLOSE MAIN_CURSOR                                             
   DEALLOCATE MAIN_CURSOR        
        
 SELECT * FROM #TMP1 ORDER BY STUDENT_NAME ASC    
END       
END