Thursday, November 4, 2010

Security : Encryption and Decryption from and to a string

This is article is about two simple Encryption and Decryption methods which I wrote using .NET Class Libraries.
When you provide a string as a parameter the EncryptFromString method returns an encrypted string. Similarly, when you give the encrypted string to the DecryptFromString method it returns the decrypted string.


Before you start you must have,
- Visual Studio 2005/2008 Installed

Step 1 : Create a Console Application
Then open the Program.cs and just after the Main method paste the following code,

-------------------------------------------------------------------------------------------------------------

//Encryption Method

      private static string EncryptToString(string str)
        {
            List<uint> objList = new List<uint>();
            StringBuilder strBuilder = new StringBuilder();
            uint btVal;
            foreach (char ch in str)
            {
                btVal = (uint)ch;
                btVal = btVal << 1;
                objList.Add(btVal);
              
            }

            objList.Reverse();

            for (int i=0;i<objList.Count;i++)
            {
                if (i + 1 <= objList.Count - 1)
                {
                    strBuilder.AppendFormat("{0}|{1}|", objList[i], objList[i + 1]);
                    i++;
                }            
            }

            if (objList.Count % 2 == 1)
            {
                strBuilder.AppendFormat("{0}|",objList[objList.Count - 1]);
            }

            

            string retString = strBuilder.ToString();

            return retString.Remove(retString.LastIndexOf('|'));
        }

-------------------------------------------------------------------------------------------------------------

//Decryption Method

   private static string DecryptFromString(string str)
        {
            string[] strSplitArr = str.Split(new char[] { '|' });
            uint btVal;
            List<uint> objList = new List<uint>();
            StringBuilder strBuilder = new StringBuilder();
            foreach (string strElem in strSplitArr)
            {
                btVal = uint.Parse(strElem);
                btVal = btVal >> 1;
                objList.Add(btVal);
            }

            objList.Reverse();

            for (int i = 0; i < objList.Count; i++)
            {
                if (i + 1 <= objList.Count - 1)
                {
                    strBuilder.AppendFormat("{0}{1}", (char)objList[i], (char)objList[i + 1]);
                    i++;
                }
            }

             if (objList.Count % 2 == 1)
            {
                  strBuilder.AppendFormat("{0}", (char)objList[objList.Count - 1]);
             }


            return strBuilder.ToString();
        }



In future we will look at how to add these functions to SQL Server as a User Defined Function.

Monday, October 25, 2010

A Client - Server App using .NET Remoting

This is a basic client-server application where the client is able to view and insert records to the database using the server. Both applications have been created as Console applications, just to keep it simple ;-)

Before you start you must have,
- Visual Studio 2005/2008 Installed
- MS SQL Server 2005 Installed

Step 1 : Creating the Server
Before you start to do anything 
Open Visual Studio and create a Console Application in C# named ServeWithDL.
Create the following folder structure and the files.












Before you start to do anything, right click on References and add reference to
System.Runtime.Remoting
System.Runtime.Serialization
System.Runtime.Serialization.Formatters.Soap

Then open the Supplier.cs within the BusinessEntities folder and paste the following code;

====================================================================


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.IO;

namespace ServeWithDL.BusinessEntities
{
    #region Supplier
    /// <summary>
    /// Class which represents the Supplier object
    /// </summary>
    [Serializable]
    public class Supplier
    {
        public string SupID {get; set;}
        public string SName {get; set;}
        public string UserName {get; set;}
        public string UPassword {get; set;}
        public string SAddress {get; set;}
        public string SMail {get; set;}
        public string SMobile {get; set;}
        public string SFax { get; set; }

        public override string ToString()
        {
            return "Id : " + this.SupID + "\nName : " + this.SName + "\nUser Name : " + this.UserName + "\nPassword : " + this.UPassword
                + "\nAddress : " + this.SAddress + "\nMail Address : " + this.SMail + "\nMobile : " + this.SMobile + "\nFax : " + this.SFax+"\n\n";
        }

        public override bool Equals(object obj)
        {
            if (obj.GetType() != typeof(Supplier))
                throw new InvalidOperationException();
            else
            {
                Supplier objSupplier = (Supplier)obj;
                return this.SupID.Equals(objSupplier.SupID);
              
            }
        }
    }
#endregion

    #region Suppliers
    /// <summary>
    /// Class to represent the Suppliers object
    /// </summary>
    [Serializable]
    public class Suppliers : ICollection<Supplier>
    {

        public Suppliers()
        {
            SupplierList = new List<Supplier>();
        }

        private List<Supplier> SupplierList;

        #region ICollection<Supplier> Members

        public void Add(Supplier item)
        {
            this.SupplierList.Add(item);
        }

        public void Clear()
        {
            for (int x = 0; x < this.SupplierList.Count; x++)
            {
                this.SupplierList.RemoveAt(x);
            }
        }

        public bool Contains(Supplier item)
        {
            return SupplierList.Contains(item);
        }

        public void CopyTo(Supplier[] array, int arrayIndex)
        {
            foreach (Supplier objSupplier in SupplierList)
            {
                array.SetValue(objSupplier, arrayIndex);
                arrayIndex++;
            }

        }

        public int Count
        {
            get { return this.SupplierList.Count; }
        }

        public bool IsReadOnly
        {
            get { return false; }
        }

        public bool Remove(Supplier item)
        {
            if (SupplierList.Contains(item))
            {
                return SupplierList.Remove(item);
            }
            return false;
        }

        #endregion

        #region IEnumerable<Supplier> Members

        public IEnumerator<Supplier> GetEnumerator()
        {
            return SupplierList.GetEnumerator();
        }

        #endregion

        #region IEnumerable Members

        IEnumerator IEnumerable.GetEnumerator()
        {
            return new Enumerator(SupplierList.ToArray());
        }

        #endregion
    }
  
    #endregion

    #region Enumerator
    /// <summary>
    /// Class to make the Supplier class, Enumerable
    /// </summary>
    public class Enumerator : IEnumerator
    {
        private Supplier[] suppliers;
        private int Cursor;

        public Enumerator(Supplier[] supplierArr)
        {
            this.suppliers = supplierArr;
            Cursor = -1;
        }

        void IEnumerator.Reset()
        {
            Cursor = -1;
        }

        bool IEnumerator.MoveNext()
        {
            if (Cursor < suppliers.Length)
                Cursor++;

            return (!(Cursor == suppliers.Length));
        }

        object IEnumerator.Current
        {
            get
            {
                if ((Cursor < 0) || (Cursor == suppliers.Length))
                    throw new InvalidOperationException();
                return suppliers[Cursor];
            }
        }
    }
  
    #endregion
}

====================================================================

Then open the ServeWithDL.cs within the BusinessServiceLayer folder and paste following code;

====================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServeWithDL.BusinessEntities;
using ServeWithDL.DataAccess;

namespace ServeWithDL.BusinessServiceLayer
{
    #region ServerWithDL
    /// <summary>
    /// This is the Class which is used by the Client directly
    /// </summary>
    public class ServerWithDL : MarshalByRefObject
    {
        public Suppliers GetSuppliers()
        {
            SupplierDAC objSupplierDAC = new SupplierDAC();
            return objSupplierDAC.GetAll();
        }

        public bool AddSupplier(Supplier objSupplier)
        {
            SupplierDAC objSupplierDAC = new SupplierDAC();
            return objSupplierDAC.Insert(objSupplier);
        }
    }
    #endregion

====================================================================

Then open the SupplierDAC.cs within the DataAccess folder and paste the following code;

====================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServeWithDL.BusinessEntities;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace ServeWithDL.DataAccess
{
    #region SupplierDAC
    /// <summary>
    /// Class which does the talking with the Database
    /// </summary>
    public class SupplierDAC
    {
        #region Public Methods
        #region GetAll
        public Suppliers GetAll()
        {
            Suppliers objSuppliers = new Suppliers();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM Supplier", con))
                {
                    con.Open();
                    Supplier objSupplier;
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (true)
                        {
                            objSupplier = FillSupplier(rdr);
                            if (objSupplier != null)
                                objSuppliers.Add(objSupplier);
                            else
                                break;
                        }
                    }
                }
            }
            return objSuppliers;
        }

        #endregion

        #region Insert
        public bool Insert(Supplier objSupplier)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Supplier VALUES (@ID,@Name,@UserName,@Password,@Address,@Mail,@Mobile,@Fax)", con))
                {
                    con.Open();
                    SetCommonParams(cmd, objSupplier);
                    return cmd.ExecuteNonQuery() > 0;

                }
            }
        }
        #endregion 
        #endregion

        #region Private Methods
        #region FillSupplier
        private Supplier FillSupplier(SqlDataReader rdr)
        {
            if (rdr.Read())
            {
                Supplier objSupplier = new Supplier();
                objSupplier.SupID = rdr.GetString(0);
                objSupplier.SName = rdr.GetString(1);
                objSupplier.UserName = rdr.GetString(2);
                objSupplier.UPassword = rdr.GetString(3);
                objSupplier.SAddress = rdr.GetString(4);
                objSupplier.SMail = rdr.GetString(5);
                objSupplier.SMobile = rdr.GetString(6);
                objSupplier.SFax = rdr.GetString(7);
                return objSupplier;
            }
            else
                return null;
        }
        #endregion

        #region SetCommonParams
        private void SetCommonParams(SqlCommand cmd, Supplier objSupplier)
        {
            cmd.Parameters.Add(new SqlParameter("@Id", objSupplier.SupID));
            cmd.Parameters.Add(new SqlParameter("@Name", objSupplier.SName));
            cmd.Parameters.Add(new SqlParameter("@UserName", objSupplier.UserName));
            cmd.Parameters.Add(new SqlParameter("@Password", objSupplier.UPassword));
            cmd.Parameters.Add(new SqlParameter("@Address", objSupplier.SAddress));
            cmd.Parameters.Add(new SqlParameter("@Mail", objSupplier.SMail));
            cmd.Parameters.Add(new SqlParameter("@Mobile", objSupplier.SMobile));
            cmd.Parameters.Add(new SqlParameter("@Fax", objSupplier.SFax));
        }

        #endregion 
        #endregion
    } 
    #endregion

    
}

====================================================================

Then open the App.config and change the ConnectionString named "ServerConnectionString" accordingly;
The database, I am connecting to is called UserLogin.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ServerConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=UserLogin;Integrated Security=true"/>
  </connectionStrings>
</configuration>

====================================================================

Then open Program.cs file and paste the following code;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;

namespace ServeWithDL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of a channel
            TcpChannel channel = new TcpChannel(8070);
            ChannelServices.RegisterChannel(channel);

            // Register as an available service with the name Server
            RemotingConfiguration.RegisterWellKnownServiceType(
                typeof(BusinessServiceLayer.ServerWithDL),
                "Server",
                WellKnownObjectMode.Singleton);

            System.Console.WriteLine("Press the enter key to exit...");
            System.Console.ReadLine();
           
        }
    }
}


Well, that is it for the Server, just build the application now we will move on to the Client.

Step 2 : Creating the Client
Open Visual Studio and create a Console Application in C# named Client.

Before you start to do anything, right click on References ->Add Reference->Click on Browse tab and navigate to the Debug folder of the ServeWithDL application, then select ServeWithDL.exe.
















Then open the Program.cs and paste the following code;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;
using ServeWithDL;
using ServeWithDL.BusinessEntities;
using ServeWithDL.BusinessServiceLayer;

namespace Client
{
    class Program
    {
        static void Main(string[] args)
        {

            
            ServerWithDL obj = (ServerWithDL)Activator.GetObject(
                typeof(ServeWithDL.BusinessServiceLayer.ServerWithDL),
                "tcp://localhost:8070/Server");

            if (obj.Equals(null))
            {
                System.Console.WriteLine("Error: unable to locate server");
            }
            else
            {

                Console.WriteLine(":::::::: Welcome to the Client ::::::::::");
                Console.WriteLine("Press 1 to Insert a New Supplier.");
                Console.WriteLine("Press 2 to View All Suppliers.");
                Console.WriteLine("Press Q to Quit.");
                
                string response = Console.ReadLine();

                while (!response.Contains("Q"))
                {
                    if (response == "1")
                    {
                        Supplier objSupplier = new Supplier();
                        Console.Write("Enter Supplier Id : ");
                        objSupplier.SupID = Console.ReadLine();
                        Console.Write("Enter Supplier Name : ");
                        objSupplier.SName = Console.ReadLine();
                        Console.Write("Enter Supplier User Name : ");
                        objSupplier.UserName= Console.ReadLine();
                        Console.Write("Enter Supplier Password : ");
                        objSupplier.UPassword = Console.ReadLine();
                        Console.Write("Enter Supplier Address : ");
                        objSupplier.SAddress = Console.ReadLine();
                        Console.Write("Enter Supplier Mail : ");
                        objSupplier.SMail = Console.ReadLine();
                        Console.Write("Enter Supplier Mobile : ");
                        objSupplier.SMobile = Console.ReadLine();
                        Console.Write("Enter Supplier Fax : ");
                        objSupplier.SFax = Console.ReadLine();

                        if (obj.AddSupplier(objSupplier))
                        {
                            Console.WriteLine("Supplier Added Successfully!");
                        }
                        else
                        {
                            Console.WriteLine("Supplier Didn't Added Successfully!");
                        }

                    }
                    else if (response == "2")
                    {
                        foreach (Supplier objSupplier in obj.GetSuppliers())
                        {
                            Console.WriteLine(objSupplier.ToString());
                        }
                    }
                    else
                    {
                        Console.WriteLine("Invalid Choice. Press Q to Quit.");
                        response = Console.ReadLine();
                    }

                    Console.WriteLine("Enter a Choice. Press Q to Quit.");
                    response = Console.ReadLine();
                }
                
            }
          
        }
    }
}

Step 3 : Creating Database and Supplier Table
Open SQL Server Management Studio and Create a New Database named UserLogin. Then execute the following script;

USE [UserLogin]
GO
/****** Object:  Table [dbo].[Supplier]    Script Date: 10/26/2010 11:28:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Supplier](
[SupID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UPassword] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAddress] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SMail] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SMobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SFax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [pk_Supplier] PRIMARY KEY CLUSTERED 
(
[SupID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Now we are ready to go,

1. Start the Server application first. (Double click on ServeWithDL.exe)










2. Start the Client application.










3. First We will add a new Supplier.

 

As you can see a New row with the values we provided has been created. In the table Supplier.




4. Now we will view the Record from the Client application










We have come to the end of this post, feel free to drop your suggestions and comments. 

Monday, October 11, 2010

Co-existence : IBM DB2 and MS SQL Server 2005 using Linked Server

Well, this article explains about connecting to an IBM DB2 Database using a Linked Server which is available in MS SQL Server 2005. The underlying connection is established through an ODBC connection. You can also use this Linked Server to retrieve data and execute updates, to and from IBM DB2 Database.

This method has proven very effective in a project which I recently worked on. Currently, it is being used to Synchronize data with an IBM DB2 Database which resides on an AS 400 system and a MS SQL Server  2005 Database.

Prerequisites :
You must have,
- IBM DB2 Server or Client Installed
- MS SQL Server 2005 Installed

Step 1 : Creating the ODBC Connection.

Navigate to Start -> Control Panel -> Adminitrative Tools -> Data Sources (ODBC)

Pic 1.1 - Select IBM DB2 ODBC DRIVER and Click Finish Button














Pic 1.2 - Give a Name to the Data Source

Remember to Select the Database alias of the IBM DB2 Database you want to connect to.
Pic 1.3 - Once the ODBC is added, Click on Configure



Pic 1.4 - Then give the Username and Password of DB2

Pic 1.5 - Test your connection by Clicking on Connect Button
Specify the User Name and Password which is used to connect to IBM DB2.








































Step 2 : Creating the Linked Server in MS SQL Server 2005


Navigate to Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio


Pic 2.1 - Click on New Linked Server

Pic 2.2 - General Settings


Give the Linked Server a name. Select Other Data Source for Server Type.

Select Microsoft OLE DB Provider for ODBC Drivers as the provider.

Specify the Product Name and the Data Source exactly as the Name you gave when creating the ODBC Data Source. It is TEST in this example.






Pic 2.3 - Security Settings
Then move on to the Security Settings of the Linked Server and Select Be Made using this security context and specify the User Name and Password of the IBM DB2 Database.













Pic 2.4 - Server Options Settings
Then move on to the Server Options Settings of the Linked Server and Set RPC, RPC Out as True (this is required since we are going to use Remote Procedure Calls when Inserting and Updating values of IBM DB2)












Finally Click on OK to complete the Creation of the Linked Server and we are ready to go.

Step 3 : Executing Queries, Inserts, Updates and Deletes On the Linked Server


Well this is the most Important and the Interesting part where we can check whether everything we did above really works or not. We will imagine a table named Employee exists in the IBM DB2 within a schema or a table space named BICDEV. Remember the name of our Linked Server is TEST.


Once you have created the Linked Server successfully. There are several ways to query from the Linked Server.


One way is,


SELECT * FROM [TEST]..[BICDEV].[Employee]


another way is,


SELECT * FROM OPENQUERY(TEST, 'SELECT * FROM BICDEV.Employee')

Now we will look at how we can Execute Inserts, Deletes and Updates to the Linked Server. Since we set the RPC, RPC Out values to True when we created the Linked Server we can use the following method to accomplish out task.


EXEC ('INSERT INTO BICDEV.Employee(Name,Age,Salary) VALUES
            (''Tom'',24,350000)
) AT [TEST]





EXEC ('UPDATE BICDEV.Employee SET Age = 24 WHERE Name=''Jerald'') AT  [TEST]

EXEC ('DELETE FROM BICDEV.Employee WHERE Salary<=10000') AT  [TEST]



Well, we come to the conclusion of this small article on how to connect an IBM DB2 Database and a MS SQL Server Database using Linked Servers. While surfing through the internet I found out that there are many ways to do this. But this is something which I personally have worked on. And thought of sharing with you guys and gals. 


I welcome any comments, suggestions and corrections related to the contents of this article.