05 June 2011

Windows App - Detecting Running Instance

Code to detect if an windows application instance is already running or not:

static class Program
    {
        ///
        /// The main entry point for the application.
        ///
        [STAThread]
        static void Main()
        {
            if (IsPreviousInstanceRunning)
            {
                MessageBox.Show("Another instance of this application is already running...", "Application already running", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new frmMain());
            }
        }

        private static bool IsPreviousInstanceRunning
        {
            get
            {
                // get the name of our process
                string proc = Process.GetCurrentProcess().ProcessName;
                // get the list of all processes by that name
                Process[] processes = Process.GetProcessesByName(proc);
                // if there is more than one process...
                if (processes.Length > 1)
                    return true;
                return false;
            }
        } 
    }

20 May 2011

Started learning Silverlight



Just when i figured out the keys to the door, they changed the lock!!!

Yeah... I am still a Fresh Web Developer

16 May 2011

Dynamic Stored Procedures

Have you every felt the need to write a stored procedure that would be modified at runtime?

Well then let me show you how you can achieve this in SQL Server 2005 or upper version.

First of all let me tell you about the scenario where i had to write such a stored procedure:
Recently i was dealing with a large database (having more than 5 million records), and i had to provide the client with an option to search and filter data from a table with various search parameters in an asp.net web page.
Now few of the parameters when combined together in the where clause of the search query worked fine without much delay (mostly integer columns), but some the text columns took really long time especially when combined together in the where clause. For instance:
Query: select * from table 1 where
personame ='' or
cityid= '' or
state='' .....

In such a scenario i had to write an dynamic stored procedure which would accept parameters and form query according to the number of  paramters passed.

Here is the stored procedure that i had wrote for fetching some patients information from an insurance database:

ALTER procedure [dbo].[sp_GetEligibility] 
(@Insured nvarchar(50)=null,@GroupKey nvarchar(50)=null,MAX_RESULTS nvarchar(50)=null )
 
AS 
    BEGIN

    if @MAX_RESULTS is null
    begin
    set @MAX_RESULTS=250
    end

    DECLARE @SQL nvarchar(MAX)
    DECLARE @SQLWhere nvarchar(MAX)

    set @SQLWhere = ''
    set @Insured = LTRIM(RTRIM(@Insured))        
    set @GroupKey = LTRIM(RTRIM(@GroupKey))

    if (@Insured is not null And @Insured <> '')
    begin
    --set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%' + @Insured + '%'' ) '
    set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%'' + @Insured + ''%'' ) '
    end

   
    if (@GroupKey is not null And @GroupKey = '')  
    begin 
    set @GroupKey = null
    end
    Else if @GroupKey <> '0'
    begin
    --set @SQLWhere = @SQLWhere + ' and (E.fk_group = '+ @GroupKey + ')'
    set @SQLWhere = @SQLWhere + ' and (E.fk_group =  @GroupKey )'
    end

   

    IF not (@Insured is null and @GroupKey is null)  

    BEGIN 
 
     Select @SQL ='Select TOP(' + @MAX_RESULTS + ') * from (' +
      'select TOP(' + @MAX_RESULTS + ') I.first_name,' +
      'R.Relationship,' +
      'I.last_name, ' +
      'I.SSN,' +
      'E.subscriber_id,' + 
      'I.first_name + '' ''+ I.last_name as Names,' +
      'e.eff_date,' +  
      'e.term_date,' +
      'G.group_name,' +
      'B.benefitPlan_name, ' +     
      'convert(varchar,D.eff_date ,101) as dependEffDate,' +
      'convert(varchar,d.Term_date ,101)  as dependTermdate,' +        
      'I.pk_insured,' +  
      '(SELECT top 1 ' +
      '(' + 
      '         SELECT distinct(C.benefit_name + '', '')' +  
      '         FROM  EligibilityBenefitCodes B ' +  
      '         left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes' + 
      '         left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
      '         WHERE  C.pk_benefitplancodes = B.fk_benefitplancodes' +  
      '         and  B.fk_eligibility = E.pk_eligibility  FOR XML PATH('''')' +    
      ' )' +
      ')AS TypeofCoverage ' +   
      'from Eligibility E With(NOLOCK) ' + 
      'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_insuredprimary ' +  
      'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_insuredDependent and E.fk_insured = I.pk_insured ' +  
      'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_benefitplan ' +  
      'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +  
      'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +  
      'where e.eff_date <= getDate()  and E.IsArchived=0 ' +        
       @SQLWhere +                       
      ' UNION  ' +
     'select TOP(' + @MAX_RESULTS + ') I.first_name,R.Relationship,I.last_name, I.SSN,  E.subscriber_id, ' + 
     'I.first_name + '' ''+ I.last_name as Names, e.eff_date, ' +  
     'e.term_date, G.group_name, B.benefitPlan_name, ' +  
     'convert(varchar,D.eff_date ,101) as dependEffDate, ' +
     'convert(varchar,d.Term_date ,101)  as dependTermdate, ' +  
     'I.pk_insured  , ' +  
     '(SELECT top 1' + 
     ' (  ' +
     '  SELECT distinct(C.benefit_name + '', '')' +  
     'FROM  EligibilityBenefitCodes B ' +  
     'left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes ' + 
     'left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' + 
     'WHERE  C.pk_benefitplancodes = B.fk_benefitplancodes  and  B.fk_eligibility = E.pk_eligibility ' + 
     ' FOR XML PATH('''') ' +    
     '  )) AS BenefitCode ' +   
     'from Eligibility E With(NOLOCK) ' +   
     'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_insuredprimary ' + 
     'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_insuredDependent ' +  
     'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_benefitplan ' +  
     'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +  
     'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +  
     'where  e.eff_date <= getDate()  and E.IsArchived=0 ' +
     @SQLWhere +
    ')A  ' +
    '  order by A.Relationship,A.first_name,A.last_name desc' 
 
    --EXEC(@SQL)
    --For preventing SQL Injection use following method
    EXEC sp_executesql @SQL, N'@Insured nvarchar(50),@GroupKey nvarchar(50)',@Insured,@GroupKey

  END

END

As you can see in this example, the stored procedure generates the sql query as string and at the end the parameters are passed to the generated final query.
This is pretty much similar to the if else conditions that we used to write in code behind to generate a query.

06 September 2010

IntelliSense & Type Safety for Session Variables

Keeping track of your Session variables in ASP.NET can become a bit cumbersome task if you end up creating many variables across a large Web Application. Sometimes you might make common mistakes like misspelling the session variable name or at times end up with incorrect data types.

I have found a very good solution for this problem which i use regularly in my ASP.NET applications. The solution is to use an "SessionHandler" class in your Application in which one creates properties to access the session variables. By this method we not only are able to access the Session variables through Visual Studio IntelliSense but also apply type safety to them.

Here is an quick example of this class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/*
 *
 File Name : SessionHandler.cs
 Class Name: SessionHandler
 Summary   : This class is used to handle the session variables and keep a track of them using the
             SessionHandler class object.
             Advantage is that these variables are shown in VS Intellisense.
             And we can also implement type safety.
 *
 */
public static class SessionHandler
{
    ///////////////////////////////////////////////////////////////////////////////////////////

    private static string _UserKey = "UserKey";
    private static string _Username = "Username";  
    private static string _LoginType = "LoginType";
    
       

    ///////////////////////////////////////////////////////////////////////////////////////////

    public static string UserKey
    {
        get
        {
            object obj = HttpContext.Current.Session[SessionHandler._UserKey];

            if (obj == null)
            {
                return string.Empty;
            }
            else
            {
                return obj.ToString();
            }
        }
        set
        {
            HttpContext.Current.Session[SessionHandler._UserKey] = value;
        }
    }

  

    ///////////////////////////////////////////////////////////////////////////////////////////

    public static string LoginType
    {
        get
        {
            object obj = HttpContext.Current.Session[SessionHandler._LoginType];

            if (obj == null)
            {
                return string.Empty;
            }
            else
            {
                return obj.ToString();
            }
        }
        set
        {
            HttpContext.Current.Session[SessionHandler._LoginType] = value;
        }
    }

    ///////////////////////////////////////////////////////////////////////////////////////////

    public static string Username
    {
        get
        {
            object obj = HttpContext.Current.Session[SessionHandler._Username];

            if (obj == null)
            {
                return string.Empty;
            }
            else
            {
                return obj.ToString();
            }
        }
        set
        {
            HttpContext.Current.Session[SessionHandler._Username] = value;
        }
    }

}

Please Note: You need to add this class file to your AppCode folder in your Web Application.

06 June 2010

Working On Windows Application

Web to Windows Application shift was not something i was hoping for when i stared with ASP.NET.
But as it turns around i got a Good Domain "Medical Insurance" to work on, and so i readily accepted the challange.
Been busy eversince last 4 months.
Hey it does get boring at times but just look at the good things i got to learn...

1.Large database handling techniques!!
2.Really improved my SQL Server knowledge and skills.
3.Learnt a lot more about Crystal Reports and Windows Application.
4.Got to know how to communicate with clients (USA). And loving it!
5.Really learnt how to work in a shared environment, using source safe, communicating with developers across the globe, coding standards etc etc.

So all in all its a great addition to my Resume!

And now the Best Part is that we are implementing the same functionality on a Web Portal as well.
Ain't that great!!

ASP.NET here i come again!!