Delete duplicate records

delete from emp where id in(
select id from (
Select id, sal,dense_rank() over (partition by sal order by id) as den
from emp
) t where t.den >1
)

Validate multiple valid emails separated by semicolon

Validate multiple valid emails separated by semicolon
There are various ways to validate them
1. Using custom validator and calling the javascript

 <asp:CustomValidator ID="custValEmail" runat="server" ClientValidationFunction="validateMultipleEmailsCommaSeparated"  ErrorMessage="Invalid email(s)"></asp:CustomValidator>

 <script language="JavaScript" type="text/javascript">

 function validateEmail(field)
 {
     var regex=/^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;
     var matchArray=field.match(regex);
     if(matchArray==null)
         return false;
     else
         return true;
 }

 function validateMultipleEmailsCommaSeparated(source, value)
 {
     var result = document.getElementById("<%= txtEmail.ClientID %>").value.split(';');
     for(var i = 0;i < result.length;i++)
     {
         if(!validateEmail(result[i]))
         {
             value.IsValid = false;
             return false;
         }    
     }       
     value.IsValid = true;
 }
 </script>
 
2. Directly defining the regular expression in the RegularExpressionValidator control   
   
    <asp:RegularExpressionValidator ID="s" runat="server" ControlToValidate="txtEmail" ErrorMessage="Invalid email(s)" 
ValidationExpression="(([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)(\s*;\s*|\s*$))*"></asp:RegularExpressionValidator>

Close() Vs Dispose Method

The basic difference between Close() and Dispose() is, when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used. Where as Dispose() method permanently removes any resource ((un)managed) from memory for cleanup and the resource no longer exists for any further processing.
Example showing difference between Close() and Dispose() Method:

using System;
using System.Data;
using System.Data.SqlClient;
public class Test
{
    private string connString = "Data Source=COMP3;Initial Catalog=Northwind;User Id=sa;Password=pass";
    private SqlConnection connection;
    public Test()
    {
        connection = new SqlConnection(connString);
    }
    private static void Main()
    {
        Test t = new Test();
        t.ConnectionStatus();
        Console.ReadLine();
    }
    public void ConnectionStatus()
    {
        try
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                Console.WriteLine("Connection opened..");
            }

            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
                Console.WriteLine("Connection closed..");
            }
            // connection.Dispose();

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
                Console.WriteLine("Connection again opened..");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine(ex.Message + "\n" + ex.StackTrace);
        }
        catch (Exception ey)
        {
            Console.WriteLine(ey.Message + "\n" + ey.StackTrace);
        }
        finally
        {
            Console.WriteLine("Connection closed and disposed..");
            connection.Dispose();
        }
    }
}

In the above example if you uncomment the "connection.Dispose()" method and execute, you will get an exception as, "The ConnectionString property has not been initialized.".This is the difference between Close() and Dispose().

Make a strongly named assembly with delay sign

Creating Strong Named Assemblies:

Let's see how we can make a strongly named assembly so that we can add it to the GAC folder. The first thing that you need to do is to make the Strong Name this can be done by using the SN.EXE tool. Simply, type the following command on your Visual Studio.NET command prompt.

SN.exe -k MyProject.keys

This will create the MyProject.keys file which will contain the private and public keys. If you are curious and want to see the public key then you can use the SN.EXE tool with a -p switch. Take a look at the line below:

SN.exe -p MyProject.keys MyProject.PublicKey

Now, to view the PublicKey you can use the following line of code:

SN.exe -tp MyProject.PublicKey

Output:

C:\Program Files\Microsoft Visual Studio 9.0\VC>SN.exe -tp c:\MyProject.PublicKe

y

Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Public key is

0024000004800000940000000602000000240000525341310004000001000100f5e19964891cd7

7c1e9d1c4c600882d1c170bef6360c61654941b43a1fc605b6eca75816f142a6ff3addb760948d

d3b09fc747f32dd2b1ee8d279735f34060e1e00c0e8cb2d1d11ba5ea79257e54bc72229d35b973

a8455b0eb5f8b78189e88da760a26f6694205fafb28d74587133575255d6dee5dfb58c65592544

b34c98d4

Public key token is 11865b32dc3a44cf

You cannot use the SN.exe tool to view the private key.

For delay signing use

[assembly: AssemblyKeyFileAttribute("c:\\MyProject.PublicKey")]

[assembly: AssemblyDelaySignAttribute(true)]

Now after compling the project and if you try to put it in GAC using command below you will find this error:

C:\Program Files\Microsoft Visual Studio 9.0\VC>GACUtil.exe -i c:\StrongClassLib

rary1.dll

Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Failure adding assembly to the cache: Strong name signature could not be verifie

d.  Was the assembly built delay-signed?

So first of all ignore validation of the assembly using command:

C:\Program Files\Microsoft Visual Studio 9.0\VC>SN.exe -Vr C:\StrongClassLibrary

1.dll

Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Verification entry added for assembly 'StrongClassLibrary1,11865B32DC3A44CF'

This will delay sign theassembly with public key and also ignore validation, so this assembaly can be put in GAC for use using command:

C:\Program Files\Microsoft Visual Studio 9.0\VC>GACUtil.exe -i c:\StrongClassLib

rary1.dll

Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

Now we will sign the Assembly with the strong Key names using –R option and deplay it it GAC as below:

C:\Program Files\Microsoft Visual Studio 9.0\VC>SN.exe -R C:\StrongClassLibrary1

.dll c:\MyProject.keys

Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly 'C:\StrongClassLibrary1.dll' successfully re-signed

C:\Program Files\Microsoft Visual Studio 9.0\VC>GACUtil.exe -i c:\StrongClassLib

rary1.dll

Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.21022.8

Copyright (c) Microsoft Corporation.  All rights reserved.

Assembly successfully added to the cache

Or you can use compiler to do this as below:

Now, to sign the assembly with the strong name you use the following line of code:

csc /keyfile:MyProject.keys Program.cs

CSV file Parser

using System;
using System.Data;
using System.IO; //not used by default
using System.Data.OleDb; //not used by default
namespace CSVParserExample
{
  class CSVParser
  {
    public static DataTable ParseCSV(string path)
    {
      if (!File.Exists(path))
        return null;
      string full = Path.GetFullPath(path);
      string file = Path.GetFileName(full);
      string dir = Path.GetDirectoryName(full);
      //create the "database" connection string
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
        + "Data Source=\"" + dir + "\\\";"
        + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
      //create the database query
      string query = "SELECT * FROM " + file;
      //create a DataTable to hold the query results
      DataTable dTable = new DataTable();
      //create an OleDbDataAdapter to execute the query
      OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
      try
      {
        //fill the DataTable
        dAdapter.Fill(dTable);
      }
      catch (InvalidOperationException /*e*/)
      { }
      dAdapter.Dispose();
      return dTable;
    }
  }
}
The weird thing about all of this is that the CSV file gets treated as a database table. We need to create a connection string with the Jet OLEDB provider, and you set the Data Source to be the directory that contains the CSV file. Under extended properties, 'text' means that we are parsing a text file (as opposed to, say, an Excel file), the HDR property can be set to 'Yes' (the first row in the CSV files is header information) or 'No' (there is no header row), and setting the FMT property set to 'Delimited' essentially says that we will be working with a comma separated value file. You can also set FMT to 'FixedLength', for files where the fields are fixed length - but that wouldn't be a CSV file anymore, would it?
Now we are into normal OLEDB territory - we create a DataTable that we will be filling with results, and we create a OleDbDataAdapter to actually execute the query. Then (inside of a try block, because it can throw an exception) we fill the data table with the results of the query. Afterwords, we clean up after ourselves by disposing the OleDbDataAdapter, and we return the now filled data table.

Using the FileSystemWatcher Class

The FileSystemWatcher class does exactly what the name implies - it watches the file system. Basically you just give it a path and it fires events when certain things happen to that path. The FileSystemWatcher can listen for rename, delete, change, and create. Let's hook it up and listen for renamed files.

FileSystemWatcher watcher = new FileSystemWatcher();
watcher.Path = @"C:\MyDirectory";
watcher.Renamed += new RenamedEventHandler(watcher_Renamed)
watcher.EnableRaisingEvents = true;

Once you hook the events you want, in this case Renamed, you have to set EnableRaisingEvents to true to begin watching. In this example, the method watcher_Renamed will be called whenever any file in the folder C:\MyDirectory is renamed. The RenamedEventHandler contains lots of good information about the renamed file - like the old file name and the new file name.

void watcher_Renamed(object sender, RenamedEventArgs e)
{
  Console.WriteLine("File Renamed: Old Name: " + e.OldName +
    " New Name: " + e.Name);
}
All of the other events in the FileSystemWatcher class will pass a FileSystemEventArgs object to the event handler.

watcher.Deleted += new FileSystemEventHandler(watcher_Deleted);
watcher.Changed += new FileSystemEventHandler(watcher_Changed);
watcher.Created += new FileSystemEventHandler(watcher_Created);

FileSystemEventArgs contain the filename, the full path, and what action caused the event.
So what if you want to watch a single file or a single type of file (like .txt files)? The FileSystemWatcher contains a Filter property that can be used to do that.

//watches only myFile.txt
watcher.Filter = "myFile.txt";
//watches all files with a .txt extension
watcher.Filter = "*.txt";

Like the second example in the above snippet shows, the Filter property can accept wildcards. In this case, events will only be fired when a file with a .txt extension changes.
The last important piece of the FileSystemWatcher is the property IncludeSubdirectories. The property tells the class to watch the folder you gave and all folders contained in it - and folders contained within those, etc.

How to Get an Enum from a Number

Tutorial on how to convert an integer to an enum. This is useful when you've got a number, either from a file or transferred over a socket, and you want to convert that number to an enum.

What we're going to create is a generic method that can convert a number to any type of enum. I like using a generic method for this because now I can convert numbers to any type of enum using the same function and without having to cast the return value for each enum separately.
Here's the function that will be doing all the work:
public T NumToEnum<T>(int number)
{
   return (T)Enum.ToObject(typeof(T), number);
}
This is a generic function that can convert a number to any type of enum. Let's see some sample code that makes use of this function. First, let's create some enums.
public enum DaysOfWeek
{
   Monday,
   Tuesday,
   Wednesday,
   Thursday,
   Friday,
   Saturday,
   Sunday
}
public enum MonthsInYear
{
   January,
   February,
   March,
   April,
   May,
   June,
   July,
   August,
   September,
   October,
   November,
   December
}
Now, let's see some code that uses NumToEnum.
int day = 3;
int month = 10;
DaysOfWeek d  = NumToEnum<DaysOfWeek>(day);
//d is now DaysOfWeek.Thursday
MonthsInYear m = NumToEnum<MonthsInYear>(month);
//m is now MonthsInYear.November
I know this is something I've needed on several occasions, so hopefully this will help you out as well.