Generate All Database Backup From Sql Server Using Query

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

DECLARE @filedate1 varchar(100)

SET @path = 'D:\BrijeshDatabase\'

SELECT @fileDate = CONVERT(varchar(50),GETDATE(),103)

SELECT @filedate1= REPLACE(@fileDate,'/','-')

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

       SET @fileName = @path + @name + '_' + @filedate1 + '.BAK'

       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name 

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

Find Nth Highest Salary of Employee order by Desc

SELECT TOP 3 EmpSal

FROM (

SELECT DISTINCT TOP 3 EmpSal

FROM tblEmp1

ORDER BY EmpSal DESC) a

ORDER BY EmpSal

How to use OUTPUT option in an UPDATE statement

DECLARE @update_table TABLE (emp_no INT, project_no CHAR(20),old_job CHAR(20),new_job CHAR(20));

UPDATE works_on

SET job = NULL

OUTPUT DELETED.emp_no, DELETED.project_no,

DELETED.job, INSERTED.job INTO @update_table

WHERE job = 'Clerk';

SELECT * FROM @update_table

How the OUTPUT statement works with a DELETE statement

DECLARE @del_table TABLE (emp_no INT, emp_lname CHAR(20));

DELETE employee

OUTPUT DELETED.emp_no, DELETED.emp_lname INTO @del_table

WHERE emp_no > 15000;

SELECT * FROM @del_table

How to register WCF with IIS and ASP.NET

If we have installed .NET framework before installing IIS on our machine and we wanted to deploy a WCF service. Then we need to do some additional steps by registering Windows Communication Foundation with IIS and ASP.NET.

Firstly, register ASP.NET with IIS by executing following:
C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -i


Secondly, register WCF by executing following:
C:\Windows\Microsoft.NET\Framework\3.0\Windows Communication Foundation\ServiceModelReg -i

Find total week no and dates between two dates in asp.net c#

//============  Week No & Date Handling for Week No Dropdownbox ==================//

       

DateTime start = DateTime.Today;// Adjust to your start date

//------ Total week no of Current Year

int w = (DateTime.IsLeapYear(System.DateTime.Now.Year) ? 366 : 365) / 7;

//------ Week No of Current Date

CultureInfo ciCurr = CultureInfo.CurrentCulture;

int weekNum = ciCurr.Calendar.GetWeekOfYear(start, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

// Get date of first day of a current week.

DateTime dt = GetFirstDayOfWeek(start, ciCurr);

for (int x = weekNum; x <= w; x++)

{

dropWeekNo.Items.Add(string.Format("Week: {0} ({1} - {2})", weekNum, dt.ToString("dd/MM/yyyy").Replace('-', '/'), dt.AddDays(6).ToString("dd/MM/yyyy").Replace('-', '/')));

dt = dt.AddDays(7);

weekNum++;

}

 public DateTime GetFirstDayOfWeek(DateTime dayInWeek, CultureInfo cultureInfo)

    {

        DayOfWeek firstDay = cultureInfo.DateTimeFormat.FirstDayOfWeek;

        DateTime firstDayInWeek = dayInWeek.Date;

        while (firstDayInWeek.DayOfWeek != firstDay)

            firstDayInWeek = firstDayInWeek.AddDays(-1);

        return firstDayInWeek;

    }

Capture line number with exception message in asp.net C#

Sometime, i am writing a long code for implement for some logic in my program and inside my code, multiple try-catch block is exist, then i don't know proper error exception for capture message type. So i have capture all information for generated error.

Add below code in your function:

// Get stack trace for the exception with source file information

var st = new StackTrace(ex, true);

// Get the top stack frame

var frame = st.GetFrame(0);

// Get the line number from the stack frame

var line = frame.GetFileLineNumber();

Read excel’s multiple sheet in asp.net with C#

I have create a program which need a concept for read multiple sheet at a time. but this is complex.  I have try to solved this problem. I have implement this code. This function is useful for you.

    private DataTable ReadExcelToTable(string path)

    {

        //Connection String (Excel)

        string connstring_Excel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

        using (OleDbConnection conn = new OleDbConnection(connstring_Excel))

        {

            conn.Open();

            //Get All Sheets Name

            DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

            //Get the First Sheet Name

            string firstSheetName = sheetsName.Rows[0][2].ToString();

            //Query String

            string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);

            OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring_Excel);

            DataSet set = new DataSet();

            ada.Fill(set);

            conn.Close();

            DataTable tempdt = new DataTable();

            tempdt = set.Tables[0];

            return tempdt;

        }

    }

Convert amount in word format in c#

Use This function for convert any number, amount or any digit to word format:--    

  

public static string NumbersToWords(Int32 inputNumber)

      {

          int inputNo = inputNumber;

   

          if (inputNo == 0)

              return "Zero";

   

          int[] numbers = new int[4];

          int first = 0;

         int u, h, t;

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

  

         if (inputNo < 0)

         {

             sb.Append("Minus ");

             inputNo = -inputNo;

         }

  

         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 " };

  

         numbers[0] = inputNo % 1000; // units

         numbers[1] = inputNo / 1000;

         numbers[2] = inputNo / 100000;

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

         numbers[3] = inputNo / 10000000; // crores

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

  

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

         {

             if (numbers[i] != 0)

             {

                 first = i;

                 break;

             }

         }

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

         {

             if (numbers[i] == 0) continue;

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

             t = numbers[i] / 10;

             h = numbers[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 "Rupees " + sb.ToString().TrimEnd();