Display Number with Commas in SQL

-- Test this function : 
-- SELECT dbo.NumericToCurrency (1116548238,'US') AS RetValue
-- SELECT dbo.NumericToCurrency (10000,'IND') AS RetValue
-- For Indian Format - 'IND', FOR US Format - 'US'

CREATE FUNCTION [dbo].[NumericToCurrency]
( 
   @InNumericValue MONEY
  ,@InFormatType  VARCHAR(10)
)

RETURNS VARCHAR(50)

AS
BEGIN

 DECLARE   @RetVal  VARCHAR(50)
    ,@StrRight  VARCHAR(5) 
    ,@StrFinal  VARCHAR(50) 
    ,@StrLength  INT
    
 SET   @RetVal = ''
 
 SET  @RetVal = @InNumericValue 
 SET  @RetVal = SUBSTRING(@RetVal,1,CASE WHEN CHARINDEX('.', @RetVal)= 0 THEN LEN(@RetVal) 
            ELSE CHARINDEX('.', @RetVal)-1 END) 
 
 IF(@InFormatType = 'US')
 BEGIN
  SET  @StrFinal = CONVERT(VARCHAR(50), CONVERT(money, @RetVal) , 1)
  SET  @StrFinal = SUBSTRING(@StrFinal,0,CHARINDEX('.', @StrFinal))
 END
 
 ELSE
 IF(@InFormatType = 'IND')
 BEGIN
  SET  @StrLength = LEN(@RetVal)
  IF(@StrLength > 3)
  BEGIN
   SET  @StrFinal = RIGHT(@RetVal,3)  
   SET  @RetVal  = SUBSTRING(@RetVal,-2,@StrLength)
   SET  @StrLength  = LEN(@RetVal)
   IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
     BEGIN
      SET  @StrFinal = @RetVal + ',' + @StrFinal
     END
   WHILE LEN(@RetVal) > 2
     BEGIN
      SET  @StrRight = RIGHT(@RetVal,2)   
      SET  @StrFinal = @StrRight + ',' + @StrFinal
      SET  @RetVal  = SUBSTRING(@RetVal,-1,@StrLength)
      SET  @StrLength = LEN(@RetVal)
      IF(LEN(@RetVal) > 2) 
      CONTINUE
      ELSE
      SET  @StrFinal = @RetVal + ',' + @StrFinal
      BREAK
     END
  END
  ELSE
  BEGIN
   SET @StrFinal = @RetVal
  END

 END
 
 SELECT @StrFinal = ISNULL(@StrFinal,00)
  
 RETURN @StrFinal
END

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Read More

upload file to ftp server using c#

Import below Namespaces

using System.IO;
using System.Net;

Create a Procedure as shown below

private static void UploadFileToFTP(string source)
    {
        string _ftpurl = "some ftp url"; // like "ftp://10.0.0.10/folder1/folder2";
        string _ftpusername = "your ftp username"; //like "admin";
        string _ftppassword = "your ftp password"; //like "pass@123";
        try
        {
            string filename = Path.GetFileName(source);
            string ftpfullpath = _ftpurl + @"/" + filename;
            FtpWebRequest ftp = (FtpWebRequest)FtpWebRequest.Create(ftpfullpath);
            ftp.Credentials = new NetworkCredential(_ftpusername, _ftppassword);

            ftp.KeepAlive = true;
            ftp.UseBinary = true;
            ftp.Method = WebRequestMethods.Ftp.UploadFile;
            FileStream fs = File.OpenRead(source);
            byte[] buffer = new byte[fs.Length];
            fs.Read(buffer, 0, buffer.Length);
            fs.Close();
            Stream ftpstream = ftp.GetRequestStream();
            ftpstream.Write(buffer, 0, buffer.Length);
            ftpstream.Close();
        }
        catch (Exception ex)
        {
            throw;
        }
    }

Now just call the procedure on whatever event you want as below

string _path = @"d:\text.txt";
 UploadFileToFTP(_path);

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

The given ColumnMapping does not match up with any column in the source or destination

Once I was working with SqlBulkCopy I was getting below error:
"The given ColumnMapping does not match up with any column in the source or destination."
If you are also facing the same issue, you need to look into below points.

1. Do you have TimeStamp columns in your table? Or do you have identity columns? If you have identity columns, you need to specify SqlBulkCopyOptions.KeepIdentity as shown below

SqlBulkCopy copy = new SqlBulkCopy("Your Connection String", SqlBulkCopyOptions.KeepIdentity)

2. If your Column name is "Address" and you are using it as "ADDRESS" then it is wrong, as columns on SQL Server 2005 are case sensitive.


Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Read More

Rotate Columns to Rows (or vice versa)

Sometime you must be in a need of rotating or converting your columns to rows like below or vice versa

Moving all the data one by one would be a tedious and time consuming work. To save you time and headache you can use special paste command to quickly transpose a column of data to row of data, or vice versa.

Move data between rows and columns 

1. Copy the data in one or more columns or rows.
2. Right Click on a cell where you want to paste your data, and then click Paste Special.
3. In the Paste Special dialog box, select Transpose, and then click OK.

You'll find the Transpose check box in the lower-right corner of the dialog box:

Note:
If you don't see the Paste Special command, make sure you right-click the first destination cell. You'll also find the command on the Edit menu. If you still don't see the command, make sure you're using Excel 2000 or later.

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in), Microsoft Office

Read More

executenonquery connection property has not been initialized.

This has bitten me twice now.
Code written below is Microsoft Patterns & Practices Enterprise Library 2.0 Data Access Layer.

Database database = DatabaseFactory.CreateDatabase("Your Connection String Name in Web Config");
string sqlcommand = "Your Stored Procedure Name";
DbCommand databasecommand = database.GetStoredProcCommand(sqlcommand);
database.AddInParameter(databasecommand, "@Status", DbType.String, "Complete");
int i = databasecommand.ExecuteNonQuery();

This looks fairly straight forward. However, if you run it you will get the error, "executenonquery connection property has not been initialized."
The proper way to call ExecuteNonQuery above is

int i = database.ExecuteNonQuery(databasecommand);

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

Export DataTable to CSV / Excel

DataTable objDt = _dt;// where _dt is your DataTable with values to export

        if (objDt.Rows.Count <= 0)
        {
            lblMessage.Text = "No data to Download";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            return;
        }

        HttpContext context = HttpContext.Current;

        context.Response.Clear();

        foreach (DataColumn column in objDt.Columns)
            context.Response.Write(column.ColumnName + ",");

        context.Response.Write(Environment.NewLine);

        foreach (DataRow row in objDt.Rows)
        {
            for (int i = 0; i < objDt.Columns.Count; i++)
                context.Response.Write(row[i].ToString() + ",");
            context.Response.Write(Environment.NewLine);
        }

        context.Response.ContentType = "text/csv";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
        context.Response.End();

OR

DataTable objDt = _dt;// where _dt is your DataTable with values to export

        if (objDt.Rows.Count <= 0)
        {
            lblMessage.Text = "No data to Download";
            lblMessage.ForeColor = System.Drawing.Color.Red;
            return;
        }

StreamWriter sw = new StreamWriter(@"D:\Test\test.csv", false);

        for (int i = 0; i < objDt.Columns.Count; i++)
        {
            sw.Write(objDt.Columns[i]);
            if (i < iColCount - 1)
                sw.Write(",");
        }
        sw.Write(sw.NewLine);

        foreach (DataRow dr in objDt.Rows)
        {
            for (int i = 0; i < objDt.Columns.Count; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                    sw.Write(dr[i].ToString());

                if (i < iColCount - 1)
                    sw.Write(",");
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

telnet is not recognized as an internal or external command

One day I was checking if the port is open or not using telnet command, it shown me below message.
"telnet is not recognized as an internal or external command operable program or batch file"
Solution for Windows 7, Vista, Server 2008 (NOT R2):
Select Control Panel > Programs > Turn Windows Features on or off > "Select Telnet Client" > OK

Solution for Windows Server 2008 (R2):
Select Start > Administrative Tools > Server Manager > Features >Add Features > Locate and select "Telnet Client" > Next > Install > Close


Reference: Muthukumar (http://nadarmuthukumar.blogspot.in), Petenetlive

Read More

Undo Delete Command

While working with live data it is important that you work with full concentration, else it may cost you like anything.
Here I am sharing one of my colleagues expirence when he was working on live data.
While deleting some record with WHERE clause, he forgot to select the whole WHERE clause on executing the query... and what!!! all of the data from live data is gone..

How to rollback that data now???

What I did on my case is,
1. Find the log file path of that database by right clicking on the database > properties then Select Files from the left pane and noted the log file path (both .mdf and .ldf).
2. Downloaded a tool to read sql log file from internet which you can get from here and install the same.
3. Now to read the file, you need to first make your database offline. For that right click on database > task > Take Offline.
4. Open Kernal for SQL Database and open your log file (.mdf) and then click on recover.
5. That is you will get all your data. Now you can do what ever you like to do with the data.

Special Thanks to Pinal Dave

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

Call Javascript from Code Behind

There are time when you need to call your Javascript function from code behind.
You can achieve the same using below code,

Javascript function to call

function ConfirmBox(objMsg)
    {
        if(confirm(objMsg))
        {
            alert('test');
            return true;
        }
        else
        {
            return false;
        }
    }

C# Code Behind

this.ClientScript.RegisterStartupScript(typeof(Page), "Popup", "ConfirmBox('" + str + "');", true);

Or

this.ClientScript.RegisterStartupScript(typeof(Page), "Popup", "alert('" + str + "');", true);

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

ASP.NET Membership: Get UserID by UserName

MembershipUser myObject = Membership.GetUser();
string UserID = myObject.ProviderUserKey.ToString();

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More

Make website down

There any many time when you want your website to be in Under Construction mode or Mantainance Mode and so on.
This can be achieved by following step below:

1. Create .htm or .html page with name "App_Offline"
2. Deploy this page on your root directory of Website.

To make the site work just delete or rename the page "App_Offline" from you  root directory of Website, and you are done.

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Read More

Check Value is Integer

function ValidateInteger(Obj)
    {
        if(Obj.toString().indexOf(".") > -1)
        {alert('Kindly enter amount in multiples of 1.'); return false;}

        if(parseInt(Obj)==NaN)
        {alert('Invalid Amount.'); return false;}
        
        return true;
    }

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in/)

Read More

printdialog always using default printer

I have the following code to show printer dialog box but no matter what printer I choose, it always prints to the default printer.

 

PrintDialog pdlg = new PrintDialog();

// Show the PrintDialog
if (pdlg.ShowDialog() == DialogResult.OK)
{
   PrintDocument pd = new PrintDocument();

   // Associate PrintDocument object with the PrintDialog
   pdlg.Document = pd;
   pd.PrinterSettings = pdlg.PrinterSettings;
   
   pd.Print();
}

Solution:

Just Swapped Document and Printer Settings as below, and it worked for me.

PrintDialog pdlg = new PrintDialog();

// Show the PrintDialog
if (pdlg.ShowDialog() == DialogResult.OK)
{
   PrintDocument pd = new PrintDocument();
   
   pd.PrinterSettings = pdlg.PrinterSettings;

   // Associate PrintDocument object with the PrintDialog
   pdlg.Document = pd;
   pd.Print();
}

Reference: Muthukumar (http://nadarmuthukumar.blogspot.in)

Read More