December 31, 2012

C# - SQL Helper Class

Below I have mentioned my SQL Helper class that is used to communicate SQL in better way.

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Utility
{
    public static class SqlHelper
    {
        #region "FILL DATA TABLE"
 
        public static void Fill(DataTable dataTable, String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataTable);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        public static void Fill(DataTable dataTable, String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            if (parameters != null)
                oCommand.Parameters.AddRange(parameters);
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataTable);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        #endregion
 
        #region "FILL DATASET"
 
        public static void Fill(DataSet dataSet, String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataSet);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        public static void Fill(DataSet dataSet, String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            if (parameters != null)
                oCommand.Parameters.AddRange(parameters);
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataSet);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        #endregion
 
        #region "EXECUTE SCALAR"
 
        public static object ExecuteScalar(String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            object oReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oCommand.Transaction = oTransaction;
                    oReturnValue = oCommand.ExecuteScalar();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return oReturnValue;
        }
 
        public static object ExecuteScalar(String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            object oReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    if (parameters != null)
                        oCommand.Parameters.AddRange(parameters);
 
                    oCommand.Transaction = oTransaction;
                    oReturnValue = oCommand.ExecuteScalar();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return oReturnValue;
        }
 
        #endregion
 
        #region "EXECUTE NON QUERY"
 
        public static int ExecuteNonQuery(string procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            int iReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oCommand.Transaction = oTransaction;
                    iReturnValue = oCommand.ExecuteNonQuery();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return iReturnValue;
        }
 
        public static int ExecuteNonQuery(string procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            int iReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    if (parameters != null)
                        oCommand.Parameters.AddRange(parameters);
 
                    oCommand.Transaction = oTransaction;
                    iReturnValue = oCommand.ExecuteNonQuery();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return iReturnValue;
        }
 
        #endregion
 
    }
}


How to Use:

public static int YourFunction1(int param1, int param2)
{
    SqlParameter[] objParameter = new SqlParameter[2];
    objParameter[0] = new SqlParameter("@param1", param1);
    objParameter[1] = new SqlParameter("@param2", param2);
    return Convert.ToInt32(Data.ExecuteScalar("YourProcedureName", objParameter));
}
 
public static int YourFunction2()
{
    return Data.ExecuteNonQuery("YourProcedureName");
}
 
public static void YourFunction3(int param1, int param2, int param3, ref DataTable dt)
{
    SqlParameter[] objParameter = new SqlParameter[3];
    objParameter[0] = new SqlParameter("@param1", param1);
    objParameter[1] = new SqlParameter("@param2", param2);
    objParameter[2] = new SqlParameter("@param3", param3);
    dt.Fill("YourProcedureName", objParameter);
}
 

Feedback are accepted.

SQL Server - Generate data script for a table, conditionally

While Data migration or Project Release, we use Generate data script frequently. For taking whole data means we can use “Tasks -> Generate Scripts” Option, provided by SQL. But if we want to generate data script for a specific data rows than, there is no option in SQL to do this.

After a little bit of googling i find out a link, that has a procedure to facilitate generate data script, But problem is we can’t generate data script conditionally (Again the same problem).

But this time we have a source, the procedure formed by dynamic query. I have added a little bit of stuff to conditional table data script Generation.


CREATE PROC [dbo].[InsertGenerator]
(
    @tableName            VARCHAR(100),
    @whereCondition        VARCHAR(MAX) = NULL
)
AS
BEGIN
 
    --    Declare a cursor to get col name and 
    --    its data type for the specified table
    DECLARE cursCol CURSOR 
    FAST_FORWARD 
    FOR 
        SELECT 
            column_name, data_type 
        FROM information_schema.columns 
        WHERE table_name = @tableName
 
    -- OPEN CURSOR
    OPEN cursCol
 
    DECLARE 
        @string NVARCHAR(3000),        --for storing the first half of INSERT statement
        @stringData NVARCHAR(3000), --for storing the data (VALUES) related statement
        @dataType NVARCHAR(1000);    --data types returned for respective columns
    
    SET @string='INSERT '+@tableName+'('
    SET @stringData=''
 
    DECLARE @colName NVARCHAR(50)
 
    FETCH NEXT FROM cursCol INTO @colName,@dataType
 
    IF @@fetch_status <> 0
        BEGIN
            PRINT 'Table '+@tableName+' not found, processing skipped.'
            CLOSE curscol
            DEALLOCATE curscol
            RETURN
        END
 
    WHILE @@FETCH_STATUS=0
        BEGIN
            IF @dataType in ('varchar','char','nchar','nvarchar')
                BEGIN
                    SET @stringData 
                            = @stringData+''''+'''+isnull('''''+'''''+' 
                              + @colName + '+'''''+''''',''NULL'')+'',''+'
                END
            ELSE IF @dataType in ('text','ntext') --if the datatype is text or something else 
                BEGIN
                    SET @stringData 
                            = @stringData+'''''''''+isnull(cast(' 
                              + @colName + ' as varchar(2000)),'''')+'''''',''+'
                END
            ELSE IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
                BEGIN
                    SET @stringData 
                            = @stringData+'''convert(money,''''''+isnull(cast(' 
                              + @colName + ' as varchar(200)),''0.0000'')+''''''),''+'
                END
            ELSE IF @dataType='datetime'
                BEGIN
                    SET @stringData
                            = @stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'
                              + @colName +',121)+'''''+''''',''NULL'')+'',121),''+'
                END
            ELSE IF @dataType='image' 
                BEGIN
                    SET @stringData 
                            = @stringData+'''''''''+isnull(cast(convert(varbinary,'
                              + @colName+') as varchar(6)),''0'')+'''''',''+'
                END
            ELSE --presuming the data type is int,bit,numeric,decimal 
            BEGIN
                SET @stringData 
                            = @stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'
                              + @colName + ')+'''''+''''',''NULL'')+'',''+'
            END
 
            SET @string= @string + @colName+','
 
            FETCH NEXT FROM cursCol INTO @colName,@dataType
        END
    DECLARE @Query nvarchar(4000)
 
    SET @query 
            = 'SELECT ''' + SUBSTRING(@string,0,len(@string)) + ') VALUES(''+ ' 
                + SUBSTRING(@stringData,0,LEN(@stringData)-2)+'''+'')'' FROM '
                + @tableName + ISNULL(' WHERE ' + @whereCondition, '')
                
    exec sp_executesql @query
    --select @query
 
    CLOSE cursCol
    DEALLOCATE cursCol
 
END


How to use:
USE [NorthWind]
GO
 
EXEC    [InsertGenerator]
        @tableName = N'Orders',
        @whereCondition = N'ShipCountry = ''USA'' AND EmployeeID = 3'
GO

December 27, 2012

InterView - Quickest Way to Find Prime Numbers


This week one of my college conducting interview for fresher candidates,
One question i have heard,

"Write a program to Find Prime.?"

Question on my mind, If that question asked on me, Am i capable to solve this in best way..?

That made the following program.


class Program
{
    static void Main(string[] args)
    {
        int limit = 1000;
        PrintPrimeNos(limit);
    }
 
    static void PrintPrimeNos(long limit)
    {
        // Any number must divide with 1 and itself
        // and Even number never be a prime number (Any even no at-least can able to divide by 2 )
        // So dividend start with 3 & incremented by 2
        // This will reduce no of iterations
        for (long dividend = 3; dividend < limit; dividend = dividend + 2)
        {
            bool IsPrimeNo = true;
 
            // Same as above
            // dividend / 2: Less than half of dividend only able to divide the dividend fully
            for (long divisor = 3; divisor < dividend / 2; divisor = divisor + 2)
            {
                if (dividend % divisor == 0)
                {
                    IsPrimeNo = false;
                    break;
                }
            }
 
            if (IsPrimeNo)
                Console.WriteLine(dividend);
        }
        Console.ReadLine();
    }
}

December 6, 2012

Interview - Quickest Way to Get nth Maximum Value From a Table

Now-a-days  "Query to find 2nd Maximum value  or Nth Maximum value from a table" are a common questions in interviews.

Here I'm given the best one to find nth max value from a table.
Below i mentioned 3 queries. 2 takes inbuild method, 1 Without using inbuild method

USE NorthWind
GO
 
WITH result AS 
( SELECT DENSE_RANK() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )
SELECT * FROM result WHERE SecondMaximum = 2;
 
GO
 
WITH result AS 
( SELECT ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )
SELECT * FROM result WHERE SecondMaximum = 2;
 
GO
 
SELECT TOP 1 * FROM 
( SELECT TOP 2 * FROM Products ORDER BY UnitPrice DESC ) result 
ORDER BY UnitPrice ASC

the result is, the first one (DENSE_RANK) takes less time then remaining.


SecondMaximum value or Top value in subquery you can get nth maximum value

[ Hint: Here we can use "RANK" method instead of "DENSE_RANK" method, If the "UnitPrice" values are different. ]

November 28, 2012

C# - Accessing Remote File By Using Impersonation

Sometime we may face “Could not create the file” or “Access denied on 'Some file Name' ” or "Unable to copy file. Access to the path is denied"  error while try to access or modify a file on a remote machine. After reviewing the code you find out, that because the current user does not have access on a remote machine.

Now we want to force your application to do some restricted activity by a user that who not having privileges to do. we call this procedure Impersonation.

In General Impersonation is, A person act like another.

Unprivileged user can access remote machine like privileged one by using privileged users credentials.

We can achieve Impersonation in may ways, I`m taking "WNetCancelConnection2" function from  GAC dll ("mpr.dll").

Note:
mpr.dll is a module containing functions used to handle communication between the Windows operating system and the installed network providers.

Download Here or copy and use below Remote Access Helper class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.ComponentModel;
using System.Net;
 
namespace SomeNamespace.Utility
{
    public class RemoteAccessHelper
    {
        public class NetworkConnection : IDisposable
        {
            string _networkName;
 
            public NetworkConnection(string networkName, NetworkCredential credentials)
            {
                _networkName = networkName;
 
                var netResource = new NetResource()
                {
                    Scope = ResourceScope.GlobalNetwork,
                    ResourceType = ResourceType.Disk,
                    DisplayType = ResourceDisplaytype.Share,
                    RemoteName = networkName
                };
 
                var result = WNetAddConnection2(
                    netResource,
                    credentials.Password,
                    credentials.UserName,
                    0);
 
                if (result != 0)
                {
                    throw new Win32Exception(result, "Error connecting to remote share");
                }
            }
 
            ~NetworkConnection()
            {
                Dispose(false);
            }
 
            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }
 
            protected virtual void Dispose(bool disposing)
            {
                WNetCancelConnection2(_networkName, 0, true);
            }
 
            [DllImport("mpr.dll")]
            private static extern int WNetAddConnection2(NetResource netResource,
                string password, string username, int flags);
 
            [DllImport("mpr.dll")]
            private static extern int WNetCancelConnection2(string name, int flags,
                bool force);
        }
 
        [StructLayout(LayoutKind.Sequential)]
        public class NetResource
        {
            public ResourceScope Scope;
            public ResourceType ResourceType;
            public ResourceDisplaytype DisplayType;
            public int Usage;
            public string LocalName;
            public string RemoteName;
            public string Comment;
            public string Provider;
        }
 
        public enum ResourceScope : int
        {
            Connected = 1,
            GlobalNetwork,
            Remembered,
            Recent,
            Context
        };
 
        public enum ResourceType : int
        {
            Any = 0,
            Disk = 1,
            Print = 2,
            Reserved = 8,
        }
 
        public enum ResourceDisplaytype : int
        {
            Generic = 0x0,
            Domain = 0x01,
            Server = 0x02,
            Share = 0x03,
            File = 0x04,
            Group = 0x05,
            Network = 0x06,
            Root = 0x07,
            Shareadmin = 0x08,
            Directory = 0x09,
            Tree = 0x0a,
            Ndscontainer = 0x0b
        }
    }
}


Below i`m describing some piece of code to show, how to use RemoteAccessHelper.cs ?

var oNetworkCredential = 
    new System.Net.NetworkCredential()
    {
        Domain = "domainName",
        UserName = "domainName" + "\\" + "admin login name",
        Password = "admin password"
    };
            
using (new RemoteAccessHelper.NetworkConnection(@"\\" + "domainName", oNetworkCredential))
{
    String[] sFolderNames = Directory.GetDirectories( "domainName" + "\\FolderName");
    foreach (String sFolderName in sFolderNames)
    {
        string[] sarrZipFiles = Directory.GetFiles(sFolderName, "*.txt");
        foreach (String sFile in sarrZipFiles)
        {
            // Some unprivileged operations
        }
    }
}

November 26, 2012

Fixed - AJAX AsyncFileUpload is Not Working in IE9


Today i got a mail from client, says that "File Upload functionality not seems working."
I went to site and find out.
 "AJAX AsyncFileUpload control not access-able, It was enabled false" 
i knew, Problem not made by me, it from somewhere. After plugging my hair for an hour,  got this solution.
Problem not comes from CSS, Script or Code Behind,

Solution:
Problem was, i`m using AJAX control tool kit version 3.5.40412.2, This one not up-to-date, It having errors with IE9.

Download Latest version of Ajax Control Toolkit Or use the below one.
http://ajaxcontroltoolkit.codeplex.com/releases/view/63654
The new release solved my AsyncFileUpload problem with IE 9.

Jason Overview - Cascade Dropdown Using JSON

Javascript
Below i have specified sample of cascaded Dropdown using JSON.

function fnDrpChange()
{
try
{
 
var countries =
{
    "table" :
    [
        {"countryid": "0", "countryname": "Japan"},
        {"countryid": "1", "countryname": "India"},
        {"countryid": "2", "countryname": "Pakistan"},
        {"countryid": "3", "countryname": "Srilanka"}
    ]
};
 
var listItems ="";
for (var i = 0; i < countries.table.length; i++) {
    listItems += "<option value='" + countries.table[i].countryid + "'>" + countries.table[i].countryname + "</option>";
}
document.getElementById("ddlcountries").innerHTML =listItems;
}
catch(e)
{
alert(e);
}
}
 
 
function fnCountryChange(countryid)
{
try
{
 
var states =
{
    "table" :
    [
        {"countryid": "1", "stateid": "1", "statename": "TamilNadu"},
        {"countryid": "1", "stateid": "2", "statename": "Andra"},
        {"countryid": "1", "stateid": "3", "statename": "Kerala"}
    ]
};
 
 
var listItems ="";
for (var i = 0; i < states.table.length; i++) {
if(countryid == states.table[i].countryid)
{
    listItems += "<option value='" + states.table[i].stateid + "'>" + states.table[i].statename + "</option>";
}
}
document.getElementById("ddlstates").innerHTML =listItems;
}
catch(e)
{
alert(e);
}
}

Click Here to See Demo

October 22, 2012

C# - Check File is being used by another process, if not delete files


Some times while try to deleting a file we may got a error message like
"It is being used by another person or program."

This means we can't delete a file, if it is being used.

I provide a solution to handle this kind of exception.


 
        /// <summary>
        /// This function is used to check specified file being used or not
        /// </summary>
        /// <param name="file">FileInfo of required file</param>
        /// <returns>If that specified file is being processed 
        /// or not found is return true</returns>
        public static Boolean IsFileLocked(FileInfo file)
        {
            FileStream stream = null;
 
            try
            {
                //Don't change FileAccess to ReadWrite, 
                //because if a file is in readOnly, it fails.
                stream = file.Open
                (
                    FileMode.Open, 
                    FileAccess.Read, 
                    FileShare.None
                );
            }
            catch (IOException)
            {
                //the file is unavailable because it is:
                //still being written to
                //or being processed by another thread
                //or does not exist (has already been processed)
                return true;
            }
            finally
            {
                if (stream != null)
                    stream.Close();
            }
 
            //file is not locked
            return false;
        }


     /// This function is used to delete all files inside a folder 
     public static void CleanFiles()
     {
            if (Directory.Exists("FOLDER_PATH"))
            {
                var directory = new DirectoryInfo("FOLDER_PATH");
               foreach (FileInfo file in directory.GetFiles())
               { 
                  if(!IsFileLocked(file)) file.Delete(); 
               }
            }
     }

Source: Internet

September 26, 2012

FIXED: "IE: Microsoft JScript runtime error: 'JSON' is undefined"


Recently, while i`m working with javascript i got the following error.

Microsoft JScript runtime error: 'JSON' is undefined

But, It was working in some others system.

Question is Why i got this error ?
After a Little Bit of Googling, i found the reason.
That is, In ie7 or lesser versions native implementation of JSON not supported.
>ie7 have a native implementation of JSON by default. For to use JSON in ie7 or lesser version you need to refer the below js file.

But some time you may get the same error in IE8 or greater versions, in this time you have to check your IE browser rendering in > IE7 standards mode or not. If it is not change to higher version or use the below solution like IE7.

The Solution that works for me was.

Create a script file of below code and call it before JSON object 
called.


var JSON;if(!JSON){JSON={}}(function(){'use strict';function f(n){return n<10?'0'+n:n}if(typeof Date.prototype.toJSON!=='function'){Date.prototype.toJSON=function(key){return isFinite(this.valueOf())?this.getUTCFullYear()+'-'+f(this.getUTCMonth()+1)+'-'+f(this.getUTCDate())+'T'+f(this.getUTCHours())+':'+f(this.getUTCMinutes())+':'+f(this.getUTCSeconds())+'Z':null};String.prototype.toJSON=Number.prototype.toJSON=Boolean.prototype.toJSON=function(key){return this.valueOf()}}var cx=/[\u0000\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,escapable=/[\\\"\x00-\x1f\x7f-\x9f\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,gap,indent,meta={'\b':'\\b','\t':'\\t','\n':'\\n','\f':'\\f','\r':'\\r','"':'\\"','\\':'\\\\'},rep;function quote(string){escapable.lastIndex=0;return escapable.test(string)?'"'+string.replace(escapable,function(a){var c=meta[a];return typeof c==='string'?c:'\\u'+('0000'+a.charCodeAt(0).toString(16)).slice(-4)})+'"':'"'+string+'"'}function str(key,holder){var i,k,v,length,mind=gap,partial,value=holder[key];if(value&&typeof value==='object'&&typeof value.toJSON==='function'){value=value.toJSON(key)}if(typeof rep==='function'){value=rep.call(holder,key,value)}switch(typeof value){case'string':return quote(value);case'number':return isFinite(value)?String(value):'null';case'boolean':case'null':return String(value);case'object':if(!value){return'null'}gap+=indent;partial=[];if(Object.prototype.toString.apply(value)==='[object Array]'){length=value.length;for(i=0;i<length;i+=1){partial[i]=str(i,value)||'null'}v=partial.length===0?'[]':gap?'[\n'+gap+partial.join(',\n'+gap)+'\n'+mind+']':'['+partial.join(',')+']';gap=mind;return v}if(rep&&typeof rep==='object'){length=rep.length;for(i=0;i<length;i+=1){if(typeof rep[i]==='string'){k=rep[i];v=str(k,value);if(v){partial.push(quote(k)+(gap?': ':':')+v)}}}}else{for(k in value){if(Object.prototype.hasOwnProperty.call(value,k)){v=str(k,value);if(v){partial.push(quote(k)+(gap?': ':':')+v)}}}}v=partial.length===0?'{}':gap?'{\n'+gap+partial.join(',\n'+gap)+'\n'+mind+'}':'{'+partial.join(',')+'}';gap=mind;return v}}if(typeof JSON.stringify!=='function'){JSON.stringify=function(value,replacer,space){var i;gap='';indent='';if(typeof space==='number'){for(i=0;i<space;i+=1){indent+=' '}}else if(typeof space==='string'){indent=space}rep=replacer;if(replacer&&typeof replacer!=='function'&&(typeof replacer!=='object'||typeof replacer.length!=='number')){throw new Error('JSON.stringify')}return str('',{'':value})}}if(typeof JSON.parse!=='function'){JSON.parse=function(text,reviver){var j;function walk(holder,key){var k,v,value=holder[key];if(value&&typeof value==='object'){for(k in value){if(Object.prototype.hasOwnProperty.call(value,k)){v=walk(value,k);if(v!==undefined){value[k]=v}else{delete value[k]}}}}return reviver.call(holder,key,value)}text=String(text);cx.lastIndex=0;if(cx.test(text)){text=text.replace(cx,function(a){return'\\u'+('0000'+a.charCodeAt(0).toString(16)).slice(-4)})}if(/^[\],:{}\s]*$/.test(text.replace(/\\(?:["\\\/bfnrt]|u[0-9a-fA-F]{4})/g,'@').replace(/"[^"\\\n\r]*"|true|false|null|-?\d+(?:\.\d*)?(?:[eE][+\-]?\d+)?/g,']').replace(/(?:^|:|,)(?:\s*\[)+/g,''))){j=eval('('+text+')');return typeof reviver==='function'?walk({'':j},''):j}throw new SyntaxError('JSON.parse')}}}());

Source: https://github.com/douglascrockford/JSON-js/blob/master/json2.js

September 22, 2012

Fixed - "Error: A downgrade path is not supported & The Windows Management Instrumentation (WMI) service cannot be started. To continue with the installation, you must troubleshoot and repair your Windows Management Instrumentation (WMI) service."


While trying to publish one of  my application in live environment, i`m getting the following error.

"The database 'E:\blabla\APP_DATA\ASPNETDB.MDF' cannot be opened because it is version 661. This server supports version 612 and earlier. A downgrade path is not supported.
Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'."

The Error says you fool, you are using old version of sql instance. To run this MDF you need to update your SQL instance.
So i decide to update my SQL instance from SQL Server 2008 to SQL Server 2008 R2.

while trying to update my server, i got a another error.
"The Windows Management Instrumentation (WMI) service cannot be started. 
To continue with the installation, you must troubleshoot and repair your Windows Management Instrumentation (WMI) service."

It says WMI services need to be run, before starting installation, after some of my hair plugged i find out a solution from a forum.

The solution that works for me was:

1. Copy the below code in a notepad and save it as fixwmi.cmd
2. Run through command promt e.g: type c:\fixwmi.cmd (File location \ filename)
    in command promt and enter.
It takes several minutes to complete, After it is complete, you see the :END statement start the SQL server installation again and you should be fixed.

Code:

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End

:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer

:SkipSrv
goto End

:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End

Jquery - Validate Multiple input controls Inside a Parent

function fnValidateChildFields(parentElement, selector) {
    var isValid = true;
 
    //Get Required Elements inside "Parent Element" By using selector
    //It will return filtered elements.
    var elements = jQuery(parentElement).find(selector);
 
    //For Each all fields and do your stuff
    jQuery.each(elements, function () {
 
        // Do validation Here..
 
        /* SAMPLE VALIDATION STARTS HERE */
 
        alert(this.id);
        alert(this.value);
        alert(this.getAttribute('class'));
 
        // Required Field validation
        if (this.value.length == 0)
            isValid = false;
 
        /* SAMPLE VALIDATION ENDS HERE */
 
    });
 
    return isValid;
}
 
 
function fnValidatePageII() {
    var isValid = false;
    try {
 
        // Parent ID name
        var parentElement = "#divParentID"
 
        // Class Name, It is used to get classes that needs to be replaced.
        var selector = ".className"
 
        if (fnValidateChildFields(parentElement, selector) == true)
            isValid = true;
    }
    catch (e) {
        alert(e);
    }
    return isValid;
}

Jquery - Change/Replace All Css Class Names Inside a Parent

Use the below to change all the class names inside a parent


function replaceCSSClass()
{
    // Parent ID name
    var parentElement = "#divParentID"
 
    // Class Name, It is used to get classes that needs to be replaced.
    var selector= ".className"
 
    //Get All Elements
    var elements = jQuery(parentElement).find(selector);
 
 
    //Here 'oldClassName' is going to be replaced by 'className'
    jQuery.each(elements, function () {
        this.setAttribute
        (
            'class', 
            this.getAttribute('class').replace(/(?:^|\s)oldClassName(?!\S)/g, ' className')
        );
    });
}

C# - Avoid SQL Injection By Validating Strings


What is SQL Injection:
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.
By MSDN

For Eg:
Assume the below dynamic query going to execute
String sql = "select * from Products where ProductID = '" + @ProductID + "'";

However, assume a user enters @ProductID as P213445'; drop table Products--

After assembled the above query will be
select * from Products where ProductID = 'P213445'; drop table Products--

This one syntactically correct, if this executes means we lost a table, Right..!!
So We should keep in mind SQL Injection while developing.
We can avoid SQL Injection by several methods, I given one of it.

Use the below one validate user input

//** Doubles up single quotes to stop breakouts from SQL strings **
public static string SQLSafe(string strRawText)
{
    string strCleanedText = "";
    int iCharPos = 0;
 
    while (iCharPos < strRawText.Length)
    {
        //** Double up single quotes, but only if they aren't already doubled **
        if (strRawText.Substring(iCharPos, 1) == "'")
        {
            strCleanedText = strCleanedText + "''";
            if (iCharPos != strRawText.Length)
            {
                if (strRawText.Substring(iCharPos + 1, 1) == "'")
                    iCharPos = iCharPos + 1;
            }
        }
        else
        {
            strCleanedText = strCleanedText + strRawText.Substring(iCharPos, 1);
        }
 
        iCharPos++;
    }
 
    return strCleanedText.Trim();
}

September 10, 2012

SQL SERVER - How to get Master Database File (.mdf) ?

Solution:
SELECT
   NAME
   ,TYPE_DESC
   ,STATE_DESC
   ,PHYSICAL_NAME 'LOCATION'
   ,SIZE
FROM MASTER.SYS.MASTER_FILES
WHERE NAME LIKE '% [ DATABASE NAME ] %'


You will get output like below
NAME        TYPE_DESC    STATE_DESC    LOCATION                                                                  SIZE
-----------------------------------------------------------------------------------------------------------------------
TEST        ROWS         ONLINE        c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf         280
TEST_log    LOG          ONLINE        c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_log.LDF     128

2. Go through that resulted location, now you may noticed that, you are unable to copy or move.
     To Copy or Move a file, you need to "detach" required Database from sql server.

3. Copy the required .mdf & .ldf files from the location folder. and attach, file again if it is required.

Fix - "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."


While trying to change something in my project, i got the following error.

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."


The below solution that worked for me..

Solution:
Delete all files inside the following folder

C:\Documents and Settings\ USER_NAME \Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

[ Note: Remember to replace USERNAME with your username. ]

Re-Run the application, That's it.

August 30, 2012

SQL - Concatenate Column Values from Multiple Rows into a Single Column

Use the below query to Concatenating Row Values

CREATE TABLE #PRODUCTS
(
    CategoryId        INT,
    ProductName        VARCHAR(50)
)
GO
INSERT INTO #PRODUCTS VALUES(1, 'ONE - ONE')
INSERT INTO #PRODUCTS VALUES(1, 'ONE - TWO')
INSERT INTO #PRODUCTS VALUES(2, 'TWO - ONE')
INSERT INTO #PRODUCTS VALUES(2, 'TWO - TWO')
GO
WITH Ranked ( CategoryId, rnk, ProductName ) 
AS 
(
    SELECT 
        CategoryId,
        ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),
        CAST( ProductName AS VARCHAR(8000) )
    FROM #PRODUCTS
),
AnchorRanked ( CategoryId, rnk, ProductName ) 
AS 
(
    SELECT 
        CategoryId, 
        rnk, 
        ProductName
    FROM Ranked
    WHERE rnk = 1 
),
RecurRanked ( CategoryId, rnk, ProductName )
AS
( 
    SELECT 
        CategoryId,
        rnk,
        ProductName
    FROM AnchorRanked
    UNION ALL
    SELECT 
        Ranked.CategoryId,
        Ranked.rnk,
        RecurRanked.ProductName + ', ' + Ranked.ProductName
    FROM Ranked
    JOIN RecurRanked ON Ranked.CategoryId = RecurRanked.CategoryId AND Ranked.rnk = RecurRanked.rnk + 1
)
SELECT CategoryId, MAX( ProductName )
FROM RecurRanked
GROUP BY CategoryId;


ACTUAL TABLE:
CategoryId  ProductName
----------- -----------
1           ONE - ONE
1           ONE - TWO
2           TWO - ONE
2           TWO - TWO
 

QUERY OUTPUT:
CategoryId  ProductName
----------- ----------------------
1           ONE - ONE, ONE - TWO
2           TWO - ONE, TWO - TWO

Original Source: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

August 29, 2012

ASP.Net - Enable GZip Compression

Enable GZip Compression By using Web.Config File

Past the below code inside the Configuration tag

<!-- Enabling Compression Starts Here -->
 
<system.webServer>
    <httpCompression directory="%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files">
        <scheme name="gzip" dll="%Windir%\system32\inetsrv\gzip.dll"/>
 
        <dynamicTypes>
            <add mimeType="text/*" enabled="true"/>
            <add mimeType="message/*" enabled="true"/>
            <add mimeType="application/javascript" enabled="true"/>
            <add mimeType="*/*" enabled="false"/>
        </dynamicTypes>
 
        <staticTypes>
            <add mimeType="text/*" enabled="true"/>
            <add mimeType="message/*" enabled="true"/>
            <add mimeType="application/javascript" enabled="true"/>
            <add mimeType="*/*" enabled="false"/>
        </staticTypes>
    </httpCompression>
 
    <urlCompression doStaticCompression="true" doDynamicCompression="true"/>
 
</system.webServer>
 
<!-- Compression Ends Here -->


Enable GZip Compression By using Global.asax

Past the below code inside the Global.asax File

private const string GZIP = "gzip";
private const string DEFLATE = "deflate";
 
void Application_ReleaseRequestState(object sender, EventArgs e)
{
    HttpApplication app = (HttpApplication)sender;
    if (app.Context.CurrentHandler is Page && app.Request["HTTP_X_MICROSOFTAJAX"] == null)
    {
        if (IsEncodingAccepted(DEFLATE))
        {
            app.Response.Filter = new System.IO.Compression.DeflateStream(app.Response.Filter, System.IO.Compression.CompressionMode.Compress);
            SetEncoding(DEFLATE);
        }
        else if (IsEncodingAccepted(GZIP))
        {
            app.Response.Filter = new System.IO.Compression.GZipStream(app.Response.Filter, System.IO.Compression.CompressionMode.Compress);
            SetEncoding(GZIP);
        }
    }
}
 
private static void SetEncoding(string encoding)
{
    HttpContext.Current.Response.AppendHeader("Content-encoding", encoding);
}

August 24, 2012

C#/LINQ - Split Number From Strings

Code to Split Number From Strings

/// <summary>
/// This Function used to spilt No from word
/// </summary>
/// <param name="strInput">Given String</param>
/// <returns>Expected No, If not found then returns -1</returns>
public Int64 SpiltNumberFromString(String strInput)
{
    string[] digits = Regex.Split(strInput, @"\D+", RegexOptions.Compiled);
    string numbers = string.Empty;
    foreach (string value in digits)
    {
        int number;
        if (int.TryParse(value, out number))
            numbers = numbers + number.ToString(CultureInfo.InvariantCulture);
    }
    return numbers.Length == 0 ? -1 : int.Parse(numbers);
}


LINQ Approach

/// <summary>
/// This Function used to spilt No from word
/// </summary>
/// <param name="strInput">Given String</param>
/// <returns>Expected No, If not found then returns -1</returns>
public Int64 SpiltNumberFromString(String strInput)
{
    int number = -1;
    string numberString = Regex.Split(strInput, @"\D+", RegexOptions.Compiled)
                            .Where(value => int.TryParse(value, out number))
                            .Aggregate(string.Empty, (current, value) => current + number.ToString(CultureInfo.InvariantCulture));
    return Int64.Parse(numberString);
}

C# – Convert Numbers into Words

Convert Money Numbers into Strings

public static string NumberToWords(int number)
{
    if (number == 0)
        return "ZERO";
 
    if (number < 0)
        return "MINUS " + NumberToWords(Math.Abs(number));
 
    string words = "";
 
    if ((number / 1000000) > 0)
    {
        words += NumberToWords(number / 1000000) + " MILLION ";
        number %= 1000000;
    }
 
    if ((number / 1000) > 0)
    {
        words += NumberToWords(number / 1000) + " THOUSAND ";
        number %= 1000;
    }
 
    if ((number / 100) > 0)
    {
        words += NumberToWords(number / 100) + " HUNDRED ";
        number %= 100;
    }
 
    if (number > 0)
    {
        if (words != "")
            words += "AND ";
 
        var unitsMap = new[] { "ZERO", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN" };
        var tensMap = new[] { "ZERO", "TEN", "TWENTY", "THIRTY", "FORTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", "NINETY" };
 
        if (number < 20)
            words += unitsMap[number];
        else
        {
            words += tensMap[number / 10];
            if ((number % 10) > 0)
                words += "-" + unitsMap[number % 10];
        }
    }
 
    return words;
}

SQL SERVER – Query to Convert Numbers into Words

Convert Money Numbers into Strings

CREATE FUNCTION fnNumberToWords
(
    @Number AS BIGINT
) RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Below20 TABLE (ID INT IDENTITY(0,1), Word VARCHAR(32))
    DECLARE @Below100 TABLE (ID INT IDENTITY(2,1), Word VARCHAR(32))
    DECLARE @BelowHundred AS VARCHAR(126) 
    
    INSERT @Below20 (Word) VALUES ('ZERO')
    INSERT @Below20 (Word) VALUES ('ONE')
    INSERT @Below20 (Word) VALUES ( 'TWO' )
    INSERT @Below20 (Word) VALUES ( 'THREE')
    INSERT @Below20 (Word) VALUES ( 'FOUR' )
    INSERT @Below20 (Word) VALUES ( 'FIVE' )
    INSERT @Below20 (Word) VALUES ( 'SIX' )
    INSERT @Below20 (Word) VALUES ( 'SEVEN' )
    INSERT @Below20 (Word) VALUES ( 'EIGHT')
    INSERT @Below20 (Word) VALUES ( 'NINE')
    INSERT @Below20 (Word) VALUES ( 'TEN')
    INSERT @Below20 (Word) VALUES ( 'ELEVEN' )
    INSERT @Below20 (Word) VALUES ( 'TWELVE' )
    INSERT @Below20 (Word) VALUES ( 'THIRTEEN' )
    INSERT @Below20 (Word) VALUES ( 'FOURTEEN')
    INSERT @Below20 (Word) VALUES ( 'FIFTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SIXTEEN' )
    INSERT @Below20 (Word) VALUES ( 'SEVENTEEN')
    INSERT @Below20 (Word) VALUES ( 'EIGHTEEN' )
    INSERT @Below20 (Word) VALUES ( 'NINETEEN' )
 
    INSERT @Below100 VALUES ('TWENTY')
    INSERT @Below100 VALUES ('THIRTY')
    INSERT @Below100 VALUES ('FORTY')
    INSERT @Below100 VALUES ('FIFTY')
    INSERT @Below100 VALUES ('SIXTY')
    INSERT @Below100 VALUES ('SEVENTY')
    INSERT @Below100 VALUES ('EIGHTY')
    INSERT @Below100 VALUES ('NINETY')
 
    IF @Number > 99
    BEGIN
        SELECT @belowHundred = dbo.fnNumberToWords( @Number % 100)
    END
 
    DECLARE @NumberInWords VARCHAR(MAX)
    SET @NumberInWords  = 
    (
      SELECT
        CASE 
            WHEN @Number = 0 THEN  ''
 
            WHEN @Number BETWEEN 1 AND 19 
                THEN (SELECT Word FROM @Below20 WHERE ID=@Number)
 
            WHEN @Number BETWEEN 20 AND 99
                THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' + dbo.fnNumberToWords( @Number % 10) 
 
            WHEN @Number BETWEEN 100 AND 999 
                THEN (dbo.fnNumberToWords( @Number / 100)) + ' HUNDRED '+ 
                        CASE
                            WHEN @belowHundred <> '' 
                                THEN 'AND ' + @belowHundred else @belowHundred
                        END
 
            WHEN @Number BETWEEN 1000 AND 999999 
                THEN (dbo.fnNumberToWords( @Number / 1000))+ ' THOUSAND '+ dbo.fnNumberToWords( @Number % 1000)  
 
            WHEN @Number BETWEEN 1000000 AND 999999999 
                THEN (dbo.fnNumberToWords( @Number / 1000000)) + ' MILLION '+ dbo.fnNumberToWords( @Number % 1000000) 
 
            WHEN @Number BETWEEN 1000000000 AND 999999999999 
                THEN (dbo.fnNumberToWords( @Number / 1000000000))+' BILLION '+ dbo.fnNumberToWords( @Number % 1000000000) 
            
            ELSE ' INVALID INPUT'
        END
    )
 
    SELECT @NumberInWords = RTRIM(@NumberInWords)
 
    SELECT @NumberInWords = RTRIM(LEFT(@NumberInWords,LEN(@NumberInWords)-1)) WHERE RIGHT(@NumberInWords,1)='-'
 
    RETURN (@NumberInWords)
 
END