Machine Key for Load Balancing

In my project there is requirement to change the field length eg CompanyCode. Problem is with this field name there are many tables who not created the foriegn keys and this field is used in many procedures and functions. To find the text company code I found this simple code:

/// <summary>
/// Version arguments to MachineKey.Generate() method.
/// </summary>
public enum MachineKeyVersion
{
    /// <summary>
    /// .NET version 1.1.
    /// </summary>
    Net1,

    /// <summary>
    /// .NET version 2.0 and up.
    /// </summary>
    Net2,
}

public class MachineKey
{
    /// <summary>
    /// Generates the contents of a machineKey element suitable for use in
    /// an ASP.NET web.config file.
    /// </summary>
    /// <param name="version">Indicates if keys should be generated for
    /// ASP.NET 1.1 or 2.0 and later.</param>
    public static string Generate(MachineKeyVersion version)
    {
        // Generate keys
        string validationKey = GenerateKey(64);
        string decryptionKey;
        if (version == MachineKeyVersion.Net1)
            decryptionKey = GenerateKey(24);
        else
            decryptionKey = GenerateKey(32);

        // Construct <machineKey> tag
        StringBuilder builder = new StringBuilder();
        builder.Append("<machineKey");
        builder.AppendFormat(" validationKey=\"{0}\"", validationKey);
        builder.AppendFormat(" decryptionKey=\"{0}\"", decryptionKey);
        builder.Append(" validation=\"SHA1\"");
        if (version == MachineKeyVersion.Net2)
            builder.Append(" decryption=\"AES\"");
        builder.Append(" />");
        return builder.ToString();
    }

    /// <summary>
    /// Generates a string of random hex digits of the specified
    /// number of bytes.
    /// </summary>
    /// <param name="length">Number of bytes to generate</param>
    protected static string GenerateKey(int length)
    {
        RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
        byte[] buff = new byte[length];
        rngCsp.GetBytes(buff);
        StringBuilder sb = new StringBuilder(buff.Length * 2);
        for (int i = 0; i < buff.Length; i++)
            sb.Append(string.Format("{0:X2}", buff[i]));
        return sb.ToString();
    }
}

Now just call the MachineKey.Generate method

private void btnGenerate_Click(object sender, EventArgs e)
{
    MachineKeyVersion version;

    if (radNet1.Checked)
        version = MachineKeyVersion.Net1;
    else
        version = MachineKeyVersion.Net2;

    txtMachineKey.Text = MachineKey.Generate(version);
}

Reference: http://www.blackbeltcoder.com/Articles/asp/generating-a-machinekey-element 

Searching Text in Procedures, Tables and Functions

In my project there is requirement to change the field length eg CompanyCode. Problem is with this field name there are many tables who not created the foriegn keys and this field is used in many procedures and functions. To find the text company code I found this simple code:

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
 -- tables
 SELECT
  TABLE_NAME
 FROM
  INFORMATION_SCHEMA.TABLES T
 WHERE
  charindex(@text, T.TABLE_NAME)>0
  
 -- columns
 SELECT 
  C.TABLE_NAME, C.COLUMN_NAME
 FROM
  INFORMATION_SCHEMA.COLUMNS C
 WHERE
  charindex(@text, C.COLUMN_NAME)>0

 -- views
 SELECT 
  V.TABLE_NAME AS VIEW_NAME
 FROM 
  information_schema.VIEWS V
 WHERE 
  charindex(@text, V.VIEW_DEFINITION)>0
  
 -- stored procs
 SELECT 
  R.ROUTINE_NAME 
 FROM 
  information_schema.routines r 
 WHERE 
  charindex(@text, r.ROUTINE_DEFINITION)>0
END

Hope this helps you!!!!!

Password in C# Console Application

Today my friend ask me how to make password field in C# Console Application.
I found the simple solution:

class PasswordExample

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Pls key in your Login ID");

            var
loginid = Console.ReadLine();

            Console.WriteLine("Pls key in your Password");

            var
password = ReadPassword();



            Console.Write("Your Password is:" + password);

            Console.ReadLine();
        }




      

     public static string
ReadPassword()

        {

            string
password = "";

            ConsoleKeyInfo
info = Console.ReadKey(true);

            while
(info.Key != ConsoleKey.Enter)

            {

                if
(info.Key != ConsoleKey.Backspace)

                {

                    Console.Write("*");

                    password += info.KeyChar;

                }

                else
if (info.Key == ConsoleKey.Backspace)

                {

                    if
(!string.IsNullOrEmpty(password))

                    {

                        // remove one character from the list of password characters

                        password =
password.Substring(0, password.Length - 1);

                        // get the location of the cursor

                        int pos = Console.CursorLeft;

                        // move the cursor to the left by one character

                        Console.SetCursorPosition(pos - 1, Console.CursorTop);

                        // replace it with space

                        Console.Write(" ");

                        // move the cursor to the left by one character again

                        Console.SetCursorPosition(pos - 1, Console.CursorTop);

                    }

                }

                info = Console.ReadKey(true);

            }





            // add a
new line because user pressed enter at the end of their password

            Console.WriteLine();

            return
password;

        } 

    }

That's it.

Bulk Insert From XML To Table

If you want to insert large number of data in table with just one shot then you can do it by this article. While doing my project I came to scenario where I want to send the large number to data to SQL and then insert into tables. I found this very useful. What I did is I passed all records in form of XML in SQL Procedure.

Below is the example: Open Query analyzer and paste the below code

1) Declared the XML Variable first and set the value to it.

DECLARE @idoc int




DECLARE @doc varchar(8000)

SET @doc ='<ROOT><student>

<id>1</id>

<name>Prashant</name>

<age>32</age>

</student>

<student>

<id>2</id>

<name>Swami</name>

<age>42</age>

</student>

<student>

<id>3</id>

<name>Ash</name>

<age>23</age>

</student>

<student>

<id>4</id>

<name>Kris</name>

<age>12</age>

</student>

<student>

<id>5</id>

<name>Derek</name>

<age>75</age>

</student>

</ROOT>'

2) Now Include this:

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc

OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

Insert into Students SELECT id, name, age from 

OPENXML (@idoc, '/ROOT/student',2) 

WITH
(id  int, name varchar(50), age int)




Select * from
@Students

Enjoy!!!!!!

Retrieving Last Inserted Identity of Record

While writing procedure in SQL somewhere I want the Identity value of inserted record but confused which way is the best to use and what's the difference between them. I found very useful article on this which I am sharing.

There are 3 ways to get the Identity value of last inserted record in SQL Server procedures:
@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Enjoy!!!!!!

Thanks to Pinal Dave where I found this useful topic.
Reference: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Finding Nth row in SQL Server

Finding nth row in SQL Server. Here is the simple query using ROW_NUMBER() function. In below example I am finding the 2 row:

SELECT * FROM


    (SELECT ROW_NUMBER()

        OVER (ORDER BY id) AS Row,

        id

    FROM Table) AS EMP

WHERE Row = 2

Enjoy!!!!!!

Deleting duplicate records in SQL Server

Deleting duplicate records in SQL Server:

WITH emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY id, firstname ORDER BY id ) AS RNUM FROMtable1 )

--select * from emp

DELETE FROM Emp WHERE RNUM > 1

Enjoy!!!!!!