How to get nth highest value from a table

SELECT t.*
FROM (
SELECT *,row_number() over (ORDER BY AccountNo desc) as _Rank
FROM AccountInformation
)as t
where t._Rank=2

Accessing Web service and delete files

localhost.Service1 nn = new ReadTextFiles.localhost.Service1();

nn.SynchroinzeData(dtFileData);
string temp = nn.HelloWorld();

foreach (DataRow drt in dtFileNames.Rows)
{
File.Delete(drt[0].ToString());
}

Accessing files

string foldername = this.folderBrowserDialog1.SelectedPath;

DirectoryInfo ourDir = new DirectoryInfo(foldername);

foreach(FileInfo file in ourDir.GetFiles())
{
DataRow dr = dtFileNames.NewRow();
dr[0] = file.FullName.ToString();//To get the file name with path
dr[1] = file.Name.ToString();// To get only the file name
dtFileNames.Rows.Add(dr);
this.listBox1.Items.Add(file.Name.ToString());
}

Connection String

private string conString = "Data Source=iroshanlap;Initial Catalog=test;User Id=sa;Password=p@ssw0rd;";



SqlConnection con = new SqlConnection(conString);
con.Open();
try
{
SqlCommand command = new SqlCommand("InsertFileData", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FileName", SqlDbType.NVarChar, 200, "FileName"));
command.Parameters.Add(new SqlParameter("@FileData", SqlDbType.NVarChar, 4000, "FileData"));

foreach (DataRow dr in dt.Rows)
{
command.Parameters[0].Value = dr[0].ToString();
command.Parameters[1].Value = dr[1].ToString();
int i = command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
con.Close();
}

Writing SP

CREATE PROCEDURE InsertFileData
(

@FileName nvarchar(200),
@FileData nvarchar(4000)
)


AS
BEGIN

BEGIN
INSERT INTO fileData ([FileName], FileData)
VALUES (@FileName, @FileData)
END

END

Read Text Files

dtFileData.Columns.Add("FilePath");
dtFileData.Columns.Add("FileData");
foreach (DataRow dr in dtFileNames.Rows)
{
// create reader & open file
StreamReader fileStream = File.OpenText(dr[0].ToString());
try
{
// read a line of text
string fileDada = string.Empty;
fileDada = fileStream.ReadToEnd();
Console.WriteLine(dr[0].ToString());
Console.WriteLine(fileDada);

DataRow drr = dtFileData.NewRow();
drr[0] = dr[0];
drr[1] = fileDada;

dtFileData.Rows.Add(drr);
}
finally
{
// close the stream
fileStream.Close();
}

Working with background worker

private void ViewData()
{
try
{
if (validateBeforeView())
{
grdPendingLeads.DataSource = new DataTable();

if (!(backgroundWorker1.IsBusy))
{
backgroundWorker1.RunWorkerAsync();
pictureBox1.Visible = true;
}
}
}
catch(Exception ex)
{
pictureBox1.Visible = false;
MessageBox.Show(ex.Message.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}


private void GetData()
{
try {
PendingLeadsCls obj = new PendingLeadsCls();
string fromDate = calCmbFrom.Value.ToShortDateString();
string toDate = calCmbTo.Value.AddDays(1).ToShortDateString();
dtResult = obj.GetLeads(mcmbCity.Text, fromDate, toDate);
}
catch (Exception ex)
{
pictureBox1.Visible = false;
MessageBox.Show(ex.Message.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}


private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
try {
GetData();
}
catch (Exception ex)
{
pictureBox1.Visible = false;
MessageBox.Show(ex.Message.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}


private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
try {
pictureBox1.Visible = false;
if (dtResult.Rows.Count == 0)
{
MessageBox.Show("There is no record for your search criteria.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
grdPendingLeads.Tables[0].Caption = " -Pending Leads for the period of " + calCmbFrom.Text + " to " + calCmbTo.Text + " -";
grdPendingLeads.DataSource = dtResult;
}
}
catch (Exception ex)
{
pictureBox1.Visible = false;
MessageBox.Show(ex.Message.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

Example for FolderBrowserDialog

this.folderBrowserDialog1.ShowNewFolderButton = false;

this.folderBrowserDialog1.RootFolder = System.Environment.SpecialFolder.MyComputer;


DialogResult result = this.folderBrowserDialog1.ShowDialog();

if (result == DialogResult.OK)
{
string foldername = this.folderBrowserDialog1.SelectedPath;
textBox1.Text = foldername;

foreach (string f in Directory.GetFiles(foldername))
{
this.listBox1.Items.Add(f);
}

}