JSD Ideas: 2010

Thursday, November 18, 2010

MVC Application Configration in IIS

I have referred this site to get the MVC applicaiton working using Virtual directory. There is no direct way to get the MCV working on the IIS. It can run through port by default but making it work with IIS Virtual Directory follow this link.

1) http://haacked.com/archive/2008/11/26/asp.net-mvc-on-iis-6-walkthrough.aspx
2) http://www.asp.net/mvc/tutorials/using-asp-net-mvc-with-different-versions-of-iis-vb

Tuesday, July 20, 2010

Get All Relationship of Primary Key


SELECT
    PKTableInfo.TABLE_NAME AS PK_Table_Name
    , ConstarintReference.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = PKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = PKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = PKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = PKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = PKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = PKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [PK_CONSTRAINT_COLUMNS]
    , FKTableInfo.TABLE_NAME AS FK_Table_Name
    , ConstarintReference.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    , STUFF( (
                SELECT
                    ',' + kcu.COLUMN_NAME
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  AS kcu
                WHERE kcu.TABLE_CATALOG = FKTableInfo.TABLE_CATALOG
                    AND kcu.TABLE_SCHEMA = FKTableInfo.TABLE_SCHEMA
                    AND kcu.TABLE_NAME = FKTableInfo.TABLE_NAME
                    AND kcu.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
                    AND kcu.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
                    AND kcu.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
                ORDER BY kcu.ORDINAL_POSITION
                FOR XML PATH('')
            ), 1, 1, '') AS [FK_CONSTRAINT_COLUMNS]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  AS ConstarintReference
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS PKTableInfo ON PKTableInfo.CONSTRAINT_CATALOG = ConstarintReference.UNIQUE_CONSTRAINT_CATALOG
        AND PKTableInfo.CONSTRAINT_SCHEMA = ConstarintReference.UNIQUE_CONSTRAINT_SCHEMA
        AND PKTableInfo.CONSTRAINT_NAME = ConstarintReference.UNIQUE_CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE AS FKTableInfo ON ConstarintReference.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG
        AND ConstarintReference.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA
        AND ConstarintReference.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME
ORDER BY PK_Table_Name, FK_Table_Name
GO

Saturday, July 17, 2010

Get detailed error handling information in SQL

Refer this link

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Wednesday, July 14, 2010

Searching and Multipal Selection Criteria in JQuery



    <div>
        <div style="float: left; width: 150px">
            Your Gender :
        </div>
        <div style="float: left; width: 250px">
            <asp:RadioButtonList ID="rdbGender" runat="server">
                <asp:ListItem Text="Male" Value="1"></asp:ListItem>
                <asp:ListItem Text="Female" Value="0"></asp:ListItem>
            </asp:RadioButtonList>
        </div>
    </div>
    <div style="clear: both">
        <div style="float: left; width: 150px">
            Searching :
        </div>
        <div style="float: left; width: 250px">
            <asp:RadioButtonList ID="rdbSearching" runat="server">
                <asp:ListItem Text="Groom" Value="1"></asp:ListItem>
                <asp:ListItem Text="Bride" Value="0"></asp:ListItem>
            </asp:RadioButtonList>
        </div>
    </div>

    <script type="text/javascript" language="javascript">
        $(document).ready(function() {
            $("input[id*='rdbGender'], input[id*='rdbSearching']").click(function() {

                var foundIn = $(this).attr('id').toString().search(new RegExp(/rdbGender/i));

                var strOpositeRadioButton = "rdbGender";

                if (foundIn > -1) {
                    strOpositeRadioButton = "rdbSearching";
                }

                if ($(this).val() == 1) {
                    $("input[id*=" + strOpositeRadioButton + "][value=0]").attr("checked", "checked");
                }
                else {
                    $("input[id*=" + strOpositeRadioButton + "][value=1]").attr("checked", "checked");
                }
            });
        });
    </script>

Monday, July 12, 2010

JQuery


Ready
-----------------------------------
$(document).read(function(){
    alert('hi');
})

$(document).read(FormSetting)
Function FormSetting()
{
    alert('hi');
}
-----------------------------------

Selection Criteria
-----------------------------------
=> ID
   $("#txtUserName")

=> Control
   $("input")
   $("div")

=> Inner Control
   $("div div input")

=> Class
   $(".button")

=> Selection on standard attributes
   $("input[type='button']") 

=> Selection on custome attributes
   <input type="text" IsGridText="True" />
   $("input[IsGridText ='True']")
-----------------------------------

Functions
-----------------------------------
=> VAL()
   Var strUserName = $("#txtUserName).val();
   $("#txtUserName).val("jsd24");

=> HTML()
   Var strInnerHtml = $("#divUserInfo).html();
   $("#divUserInfo).html("<a href='#'     OnClick='Alert(1)'>Click Me</a>");

=> Attr ( attribute)
   var IsGridText = $("#txtUserName").attr("IsGridText");
   $("#txtUserName").attr("IsGridText", "False");
   $("#txtUserName").attr({IsGridText:'True', IsSelected:'True'});

=> Css (style sheet)
   var color = $("#txtUserName").css("color");
   $("#txtUserName").css("color", "red");
   $("#txtUserName").css({'width': '100px', 'height':'25px'});
 
=> Each
   $("input").each(function() { $(this).val("jd"); })

=> addClass()
   $("input[type='button']").addClass("button");

=> removeClass()
   $("input[type='button']").removeClass("button");

=> haseClass()
   $("#txtUserName").haseClass("UserName");
   $("#txtUserName").haseClass("EmailID");

=> toggleClass()
   $("#divUserInfo").toggleClass("Content");

=> height()
   $("#divUserInfo").height();

=> innerHeight()
   $("#divUserInfo").innerHeight();

=> outerHeight()
   $("#divUserInfo").outerHeight();

=> width()
   $("#divUserInfo").width();

=> innerWidth ()
   $("#divUserInfo").innerWidth ();

=> outerWidth ()
   $("#divUserInfo").outerWidth ();

=> fadeIn()
   $("#divUserInfo").fadeIn();

=> fadeOut()
   $("#divUserInfo").fadeOut ();

=> hide()
   $("#divUserInfo").hide();

=> show()
   $("#divUserInfo").show();

=> appendTo    
$("Hello").appendTo("#divUserInfo");

=> clone
$("#divUserInfo").clone().appendTo("#divUserInfo1");

=> not
$("#ddlProductList option").not("[value*='Computer']").clone().appendTo("#ddlFilterList");
-----------------------------------

Multiple Action in single line
-----------------------------------
$("#txtUserName").val("JSD24").css("color", "green").attr("IsSelected", "true")
-----------------------------------

Events
-----------------------------------
=> change()
   $("#txtUserName").change(function(){});
   
=> click()
   $("a").click(function(){});

=> dblclick()
   $("a").dblclick(function(){});

=> focus()
   $("#txtUserName").focus();

=> focusIn()
   $("#txtUserName").focusIn(function());

=> focusOut()
   $("#txtUserName").focusOut(function());

=> hover()
   $("#txtUserName").hover(function());

=> keydown()
   $("#txtUserName").keydown(function());

=> keyup()
   $("#txtUserName").keyup(function());

=> keypress ()
   $("#txtUserName").keypress(function());

=> mousedown()
   $("#txtUserName").mousedown(function());

=> mouseup()
   $("#txtUserName").mouseup(function());

=> mousemove()
   $("#txtUserName").mousemove(function());
-----------------------------------

AJAX / JSON
-----------------------------------
$.ajax({
        url : "test.html",
        type:"GET" ,         //Post
        data : { id : "2", pid : "1" },
        context: document.body,
        datatype : "html"        //JSON, XML
        success : handleResponse,
        error : handleError
});

Function handleResponse(data)
{
    alert(data);
}

Function handleError(msg)
{
    alert(msg);
}
-----------------------------------

Sunday, July 11, 2010

Using Enum in Javascript


<script type="text/javascript" language="javascript">
    var enumTabInfo = { 
                        BasicInformation: 1, 
                        SkillDetail: 2, 
                        EducationDetail: 3, 
                        ProjectDetail: 4, 
                        CompanyDetail: 5 
                      }
    
    $(document).ready(function(){ 
        $("div.UserTab").Hide();
        
        var intTabNo = Number($("#<%= hdnCurrentTab.ClientID %>").val());

        switch (intTabNo) {
            case enumPageStep.BasicInformation:
                $("#divBasicInformation").show();
                break;

            case enumPageStep.SkillDetail:
                $("#divSkillDetail").show();
                break;

            case enumPageStep.EducationDetail:
                $("#divEducationDetail").show();
                break;

            case enumPageStep.ProjectDetail:
                $("#divProjectDetail").show();
                break;

            case enumPageStep.CompanyDetail:
                $("#divCompanyDetail").show();
                break;
        }
    });
</script>

Saturday, July 10, 2010

Enum Extention

Enum Extention :


    public static class EnumExtention
    {
        public static Dictionary<int, string> GetEnumData(this Type EnumType)
        {
            Dictionary<int, string> objEnumList = new Dictionary<int, string>();

            if (EnumType != null && EnumType.IsEnum)
            {
                foreach (int intCount in Enum.GetValues(EnumType))
                {
                    objEnumList.Add(intCount, Enum.GetName(EnumType, intCount).ToString().Replace("_", " "));
                }
            }
            return objEnumList;
        }
    }

Get Enum Data :


        private enum enumUserType
        {
            Admin,
            Sales_Department,
            Purchase_Department,
            Guest
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            ddlUserType.DataSource = typeof(enumUserType).GetEnumData();
            
            ddlUserType.DataTextField = "Value";
            ddlUserType.DataValueField = "Key";

            ddlUserType.DataBind();
        }

Output :


<select name="ddlUserType" id="ddlUserType">
    <option value="1">Admin</option>
    <option value="2">Sales Department</option>
    <option value="3">Purchase Department</option>
    <option value="4">Guest</option> 
</select>

Friday, July 9, 2010

OUTPUT Clause

SQL QUERY :


    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblOutPut ( OutputAction VARCHAR(20), ID INT, GroupID INT, NewValueName VARCHAR(10), OldValueName VARCHAR(10) )
    
    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    
    --- Inserted
    BEGIN
        INSERT INTO #tblTarget
                OUTPUT 
                    'Inserted' AS OutputAction
                    , INSERTED.ID 
                    , INSERTED.GroupID
                    , INSERTED.Name
                    , NULL
                INTO #tblOutPut
            SELECT * FROM #tblSource
            UNION ALL
            SELECT ID + 10, GroupID, Name FROM #tblSource;
    END
    
    --- Updated
    BEGIN
        UPDATE #tblTarget
        SET Name = CAST(ID AS VARCHAR) + ' : ' + Name
        OUTPUT 
            'Updated' AS OutputAction
            , INSERTED.ID 
            , INSERTED.GroupID
            , INSERTED.Name
            , deleted.Name
        INTO #tblOutPut;
    END
    
    --- Deleted
    BEGIN
        DELETE FROM #tblTarget
            OUTPUT 
                'Deleted' AS OutputAction
                , deleted.ID 
                , deleted.GroupID
                , null
                , deleted.Name
            INTO #tblOutPut
        WHERE ID >= 10;
    END
    
    SELECT * FROM #tblOutPut

    DROP TABLE #tblSource
    DROP TABLE #tblTarget
    DROP TABLE #tblOutPut

Output :

OutputAction         ID          GroupID     NewValueName OldValueName
-------------------- ----------- ----------- ------------ ------------
Inserted             1           1           a11          NULL
Inserted             2           1           a21          NULL
Inserted             3           1           a31          NULL
Inserted             11          1           a11          NULL
Inserted             12          1           a21          NULL
Inserted             13          1           a31          NULL
Updated              1           1           1 : a11      a11
Updated              2           1           2 : a21      a21
Updated              3           1           3 : a31      a31
Updated              11          1           11 : a11     a11
Updated              12          1           12 : a21     a21
Updated              13          1           13 : a31     a31
Deleted              11          1           NULL         11 : a11
Deleted              12          1           NULL         12 : a21
Deleted              13          1           NULL         13 : a31

Merge Statement with insert into output table


    DECLARE @tblMergeOutput TABLE
    (
        ID                BIGINT IDENTITY(1,1)
        , MergeAction    NVARCHAR(100)
        , InsertedId    INT
        , DeletedId        INT
        , GroupID        INT
    )

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    INSERT INTO #tblSource VALUES (4,1,'a41')

    INSERT INTO #tblTarget VALUES (1,1,'a1')
    INSERT INTO #tblTarget VALUES (3,1,'a3')
    INSERT INTO #tblTarget VALUES (5,1,'a5')
    INSERT INTO #tblTarget VALUES (6,2,'b2')
    INSERT INTO #tblTarget VALUES (7,2,'b1')

    
    ---- Method 2 : Using Output insert into table
    BEGIN
        INSERT INTO @tblMergeOutput
            SELECT MergeAction, InsertedId, DeletedID, GroupID 
            FROM
            (
                MERGE #tblTarget
                USING (
                        SELECT *
                        FROM #tblSource
                    ) AS tblSource
                        ON tblSource.GroupID = #tblTarget.GroupID
                            AND tblSource.ID = #tblTarget.ID
                WHEN MATCHED THEN 
                    UPDATE
                    SET Name = tblSource.Name
                WHEN NOT MATCHED THEN
                    INSERT (
                                ID
                                , GroupID
                                , Name
                            )
                    VALUES ( 
                                tblSource.ID
                                , tblSource.GroupID
                                , tblSource.Name
                            )
                WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
                    THEN DELETE
                OUTPUT 
                    $action                AS MergeAction
                    , inserted.Id        AS InsertedId
                    , deleted.Id        AS DeletedID
                    , tblSource.GroupID    AS GroupID
            ) AS MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);
    END

    SELECT * FROM #tblTarget order by GroupID, ID
    SELECT * FROM @tblMergeOutput

    DROP TABLE #tblSource
    DROP TABLE #tblTarget

Thursday, July 8, 2010

Enable AD Hoc Distributed Queries OPENROWSET

By default Ad Hoc Distributed Queries are disabled on SQL server

If you try to run OPENROWSET you will get this error message

Server: Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

To enable it do this


EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Openrowset Command in SQL

Syntax :


OPENROWSET 
( { 'provider_name', { 'datasource';'user_id';'password' 
   | 'provider_string' } 
   , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file', 
       { FORMATFILE ='format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE ='file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH =rows_per_batch ]

Import CSV File :


SELECT 
    'EmployeeInfo.csv' AS FileName
    , '.csv' AS FileType
    , BulkColumn
FROM OPENROWSET(BULK N'C:\EmployeeInfo.csv', SINGLE_CLOB) AS Document;

Output :

FileName         FileType BulkColumn
---------------- -------- ----------------------------------------------
EmployeeInfo.csv .csv     Eugene Malarky,Grove Street Smithtown,New York

Example Of SQL Database :


SELECT JSDTest.*
FROM OPENROWSET('SQLOLEDB','PC\SQLSERVER2008';'sa';'softweb',
   'SELECT CategoryName, ProductName
   FROM JSDTest.dbo.Product AS Product
        INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID
   ORDER BY CategoryName, ProductName') AS JSDTest

Example of MDB :


SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) 
   AS a

Example of Excel :


SELECT *
INTO db1.dbo.table1
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls',
    'SELECT * FROM [sheet1$]')

Import CSV File using SQL Query

SQL Query :


DECLARE @FileName VARCHAR(100) = 'C:\EmployeeInfo.csv'

CREATE TABLE #UserInfo
(
    UserName    VARCHAR(50)
    , Address    VARCHAR(100)
    , CITY        VARCHAR(10)
)

/*** Get Data From Import File ***/
DECLARE @SqlQuery VARCHAR(2000)
SELECT @SqlQuery =     'BULK INSERT'
                    + ' #UserInfo' 
                    + ' FROM'
                    + ' ''' + @FileName + ''' '
                    + ' WITH (FIELDTERMINATOR='','''
                    + ',ROWTERMINATOR = ''' + CHAR(10) + ''')'

EXECUTE ( @SqlQuery )
/*** Get Data From Import File ***/

SELECT * FROM #UserInfo

DROP TABLE #UserInfo

CSV File :

Eugene Malarky,Grove Street Smithtown,New York
Sam Adams,Main Street,Ashville
Loren Sandler,Main Street,Hometown

Output :

UserName                       Address                        CITY
------------------------------ ------------------------------ ----------
Eugene Malarky                 Grove Street Smithtown         New York

Sam Adams                      Main Street                    Ashville

Loren Sandler                  Main Street                    Hometown

Excel Permission Configration for ASP.NET Application

Refer this link

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

Create XLS File in ASP.NET application

Class CreateXLS :


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;
using System.Reflection;

public class CreateXLS
{
    #region Excel Variables
    private Microsoft.Office.Interop.Excel.Application oXL;
    private Microsoft.Office.Interop.Excel._Workbook oWB;
    private Microsoft.Office.Interop.Excel._Worksheet oSheet;

    private long iExcelRow = 2;
    private long iExcelCol = 2;
    private long iStartRow = 0;

    private string sFromCellNo = "";
    private string sToCellNo = "";

    private enum enumXLSFields
    {
        EmployeeName,
        Address,
        AccountNo,
        Earnings,
        Deduction,
        NetSalary
    }

    private float LogoLeft = 50;
    private float LogoTop = 15;
    private float LogoWidth = 52;
    private float LogoHeight = 52;
    #endregion

    #region Properties
    public string XLSPath { get; set; }
    public string XLSFileName { get; set; }
    public string XLSSheetName { get; set; }

    public List<EmployeeInfo> EmployeeList { get; set; }

    /// <summary>
    /// Logo Properties
    /// </summary>
    public string LogoFile { get; set; }
    #endregion

    /// <summary>
    /// Cunstructor
    /// </summary>
    public CreateXLS()
    {
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oXL.Visible = false;
        oXL.UserControl = false;

        this.EmployeeList = new List<EmployeeInfo>();
    }

    #region Public Methods
    public void GenerateXLSFile()
    {
        try
        {
            #region GenrateXLSFile
            GC.Collect();
            oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
            //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet));
            oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
            oSheet.Name = this.XLSSheetName;

            /*----------------Main Coding----------------*/
            InsertLogoFileToSheet();

            SetXLSTitle((int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary, "Employee Report", 10, true);

            DisplayEmployeeInfo();

            SheetAutoFit();
            /*-------------------------------------------*/

            #region XLS File Save into Server Path

            #region Check XLS File if Exists
            string sFileName = this.XLSPath + this.XLSFileName + ".XLS";
            if (File.Exists(sFileName))
            {
                File.Delete(sFileName);
            }
            #endregion

            //Create BillingXLS Directory
            if (!System.IO.Directory.Exists(XLSPath))
            {
                System.IO.Directory.CreateDirectory(XLSPath);
            }

            string strXLSFile = string.Format("{0}\\{1}.xls", this.XLSPath, this.XLSFileName);
            if (File.Exists(strXLSFile))
            {
                File.Delete(strXLSFile);
            }
            oWB.SaveAs(string.Format("{0}\\{1}", this.XLSPath, this.XLSFileName), XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
            oWB.Close(null, null, null);
            #endregion

            #endregion
        }
        catch
        {
            System.GC.Collect();
        }
        finally
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

            oSheet = null;
            oWB = null;
        }
    }

    private void DisplayEmployeeInfo()
    {
        #region Header
        iExcelRow++;

        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Employee Name";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = "Address";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = "Account No";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = "Earnings";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = "Deduction";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = "Net Salary";

        SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        iExcelRow++;
        #endregion

        #region Detail
        iStartRow = iExcelRow;
        foreach (EmployeeInfo Employee in EmployeeList)
        {
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = Employee.EmployeeName;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = Employee.Address;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = Employee.AccountNo;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = Employee.Earnings;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = Employee.Deduction;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = Employee.NetSalary;
            iExcelRow++;
        }
        #endregion

        #region Total

        #region Print Total
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Total";

        SetRangeTotal(iExcelRow, (int)enumXLSFields.Earnings, iStartRow, iExcelRow - 1, (int)enumXLSFields.Earnings);
        SetRangeTotal(iExcelRow, (int)enumXLSFields.Deduction, iStartRow, iExcelRow - 1, (int)enumXLSFields.Deduction);
        SetRangeTotal(iExcelRow, (int)enumXLSFields.NetSalary, iStartRow, iExcelRow - 1, (int)enumXLSFields.NetSalary);
        #endregion

        #region Range Format
        SetRangeNumberFormat(iStartRow, iExcelRow, (int)enumXLSFields.Earnings, (int)enumXLSFields.NetSalary);
        SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        SetRangeBorder(iStartRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        #endregion

        #endregion
    }
    #endregion

    #region Private Methods
    private void InsertLogoFileToSheet()
    {
        if (File.Exists(this.LogoFile))
        {
            oSheet.Shapes.AddPicture(this.LogoFile, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, LogoLeft, LogoTop, LogoWidth, LogoHeight);
        }
        SetXLSTitle((int)enumXLSFields.Address, (int)enumXLSFields.NetSalary, "ABC Inc.", 12, true);

        iExcelRow = 6;
    }

    private void SheetAutoFit()
    {
        Range oRng = oSheet.get_Range("A1", "Z1");
        oRng.EntireColumn.AutoFit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetXLSTitle(int StartColumn, int EndColumn, string ColumnHeading, int FontSize, bool MergeColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + iExcelRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + iExcelRow.ToString();

        oSheet.Cells[iExcelRow, iExcelCol + StartColumn] = ColumnHeading;

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Size = FontSize;
        oRng.Font.Bold = true;
        if (MergeColumn)
        {
            oRng.Merge(1);
            oRng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            oRng.Font.Color = Color.White.ToArgb();
            oRng.Interior.Color = Color.FromArgb(128, 128, 128).ToArgb();
            oRng.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
            oRng.Borders.Value = 1;
            oRng.Borders.Color = Color.Black.ToArgb();
        }
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeTotal(long DisplayRow, long DisplayColumn, long StartRow, long EndRow, long RangeColumn)
    {
        sFromCellNo = ((char)(iExcelCol + RangeColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + RangeColumn + 64)) + EndRow.ToString();

        oSheet.Cells[DisplayRow, iExcelCol + DisplayColumn] = "=SUM(" + sFromCellNo + ":" + sToCellNo + ")";
    }

    private void SetRangeNumberFormat(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.NumberFormat = "###,###,##0.00";
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBorder(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Borders.Value = 1;
        oRng.Borders.Color = Color.Black.ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBackColor(long StartRow, long EndRow, int StartColumn, int EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Bold = true;
        oRng.Font.Color = Color.Black.ToArgb();
        oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBold(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Bold = true;
        oRng.Font.Color = Color.Black.ToArgb();
        oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }
    #endregion
}

public class EmployeeInfo
{
    public string EmployeeName { get; set; }
    public string Address { get; set; }
    public string AccountNo { get; set; }
    public float Earnings { get; set; }
    public float Deduction { get; set; }
    public float NetSalary { get; set; }
}

ProcessXLS.ASPX :


public partial class ProcessXLS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GenrateXLS();
    }

    private void GenrateXLS()
    {
        CreateXLS objXLS = new CreateXLS();

        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Eugene Malarky", Address = "Grove Street, Smithtown", AccountNo = "A001", Earnings = 1500, Deduction = 450, NetSalary = 1050 });
        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Sam Adams", Address = "Main Street, Suite 1000, Ashville", AccountNo = "A002", Earnings = 1250, Deduction = 320, NetSalary = 930 });
        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Loren Sandler", Address = "Main Street, Hometown", AccountNo = "A003", Earnings = 1800, Deduction = 550, NetSalary = 1250 });

        objXLS.XLSPath = Server.MapPath("XLSFiles");
        objXLS.XLSFileName = "EmployeeSalary";
        objXLS.XLSSheetName = "EmployeeList";

        objXLS.LogoFile = Server.MapPath("XLSFiles/JDLogo.jpeg");

        objXLS.GenerateXLSFile();
    }
}

Sample XLS File

Wednesday, July 7, 2010

MERGE Statement and compatibility level

In SQL 2008 Merge Statement when execute and give the error like this

Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Solution
SQL Command

ALTER DATABASE <DBName>
SET COMPATIBILITY_LEVEL = 100

Monday, July 5, 2010

Delegate using in ASPX Page and user control

In ASPX Page :


public partial class Activity_ContactActivity : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        MonthActivity1.LoadWeekTabInformation += new UserControls_Activity_MonthActivity.LoadTabInformation(MonthActivity1_LoadWeekTabInformation);
    }

    void MonthActivity1_LoadWeekTabInformation()
    {
        this.ActivityDate = MonthActivity1.ActivityDate;
        this.CurrentActivityTab = enumContactActivityTab.Week;
        LoadActivityInformation();
    }
}

In User Control Page :


public partial class UserControls_Activity_MonthActivity : UserControl
{
    public delegate void LoadTabInformation();
    public event LoadTabInformation LoadWeekTabInformation;

    private void DisplayWeekTabInformation()
    {
        LoadWeekTabInformation();
    }
}

Execute Process in Threading Pool


 using System.Threading;

 public class ImportData()
 {
  public void Import()
  {
   ThreadPool.QueueUserWorkItem(new WaitCallback(SendImportDataRequest), new ImportParameter()
    {
     PageURL = "ImportData.aspx",
     CurrentUpload = enumUploadData.CustodianData,
     XMLCustodianIds = sbCustodianId.ToString().Substring(1),
     ImportDate = txtImportDate.Text.ToDateTimeValue(),
     LoginContactId = this.LoginContactId,
     LoginUserId = this.LoginUserId
    });
  }
  
  public static void SendImportDataRequest(object Parameter)
  {
   ImportParameterDTO objParameter = (ImportParameterDTO)Parameter;
   string urlEncodedUserInput = string.Format("StartImport={0}&CurrentUpload={1}&CustodianIds={2}&ImportDate={3}&LoginContactId={4}&LoginUserId={5}"
           , (int)enumYesNo.Yes            // 0
           , objParameter.CurrentUpload    // 1
           , objParameter.XMLCustodianIds     // 2
           , objParameter.ImportDate.ToShortDateString() // 3
           , objParameter.LoginContactId   // 4
           , objParameter.LoginUserId      // 5
          );

   System.Net.WebRequest httpRequest = System.Net.WebRequest.Create(objParameter.PageURL);

   httpRequest.Method = "POST";
   httpRequest.ContentType = "application/x-www-form-urlencoded;charset=utf-8";
   byte[] bytedata = System.Text.Encoding.UTF8.GetBytes(urlEncodedUserInput);
   httpRequest.ContentLength = bytedata.Length;
   System.IO.Stream requestStream = httpRequest.GetRequestStream();
   requestStream.Write(bytedata, 0, bytedata.Length);
   requestStream.Close();

   try
   {
    System.Net.WebResponse res = httpRequest.GetResponse();
   }
   catch (Exception ex)
   {
   }
  }
 }
 
 public class ImportParameter()
 {
        public string PageURL { get; set; }
        public enumUploadData CurrentUpload { get; set; }
        public string XMLCustodianIds { get; set; }
        public DateTime ImportDate { get; set; }
        public long LoginContactId { get; set; }
        public long LoginUserId { get; set; }
 }

Saturday, July 3, 2010

Checking current user Menu Rights

SQL QUERY :


    SET NOCOUNT ON

    DECLARE @CurrentUserTypeID INT = 1  ------ <---- Pass Current User Type

    -----------------------------------
    DECLARE @tblUserType TABLE
    (
        UserTypeID            INT
        , TypeName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblUserType 
    VALUES (1, 'Admin', 1)
        , (2, 'GenralManager', 2)
        , (3, 'SalesManager', 4)
        , (4, 'SalesMan', 8)
        , (5, 'Client', 16)

    DECLARE @tblMenu TABLE
    (
        MenuID            INT
        , MenuName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblMenu 
    VALUES (1, 'Admin Setting', 1)
        , (2, 'Genral Setting', 3)
        , (3, 'Sales Report', 7)
        , (4, 'Sales Order Folloup', 15)
        , (5, 'Client FeedBack', 31)

    DECLARE @UserRights BIGINT = 0

    SELECT @UserRights = MenuRights
    FROM @tblUserType
    WHERE UserTypeID = @CurrentUserTypeID 

    SELECT * 
    FROM @tblMenu
    WHERE MenuRights & @UserRights = @UserRights


Admin Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
1           Admin Setting                                      1
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31


GenralManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31


SalesManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

Cross Apply in XML

Query :


 DECLARE @XML XML = 
 '<Customers>;
  <Customer ID="1">;
   <Address1>;Address - 11</Address1>;
   <Address2>;Address - 12</Address2>;
  </Customer>;
  <Customer ID="2">;
   <Address1>;Address - 21</Address1>;
   <Address2>;Address - 22</Address2>;
  </Customer>;
 </Customers>;'

 SELECT 
  Customer.value('@ID', 'INT') AS CustomerID
  , CustAdd1.value('.', 'VARCHAR(25)') Address1
  , CustAdd2.value('.', 'VARCHAR(25)') Address2
 FROM @XML.nodes('/Customers/Customer')a(Customer)
  CROSS APPLY Customer.nodes('Address1')aa(CustAdd1)
  CROSS APPLY Customer.nodes('Address2')bb(CustAdd2)


Output :
     ----------- ------------------------- -------------------------
     CustomerID  Address1                  Address2
     ----------- ------------------------- -------------------------
     1           Address - 11              Address - 12
     2           Address - 21              Address - 22
     ----------- ------------------------- -------------------------

Friday, June 11, 2010

Using ConvertAll and Aggregate in LINQ


string strImportResult = this.ImportResult.ToList()
     .ConvertAll<string>(Import => string.Format("<tr><td> 
         {0}</td><td>{1}</td><td>{2}</td><td>
         {3}</td></tr>"
         , Import.CustodianName
         , Import.ImportFile
         , Import.ImportStatus
         , Import.Particular))
    .Aggregate((ImportA, ImportB) => ImportA + ImportB);

Multipal Field Group By IN LINQ



var objOrderList = from OrderList in
        (from OrderDetail in objOrderDetail
      join Item in objItem on OrderDetail.ItemId equals Item.ItemId
      join ItemGroup in objItemGroup on Item.GroupId equals ItemGroup.GroupId
      select new
      {
       GroupName = ItemGroup.GroupName,
       ItemName = Item.ItemName,
       Rate = Item.Rate,
       Qty = OrderDetail.Qty
      }
      )
       group OrderList by new
       {
        OrderList.GroupName,
        OrderList.ItemName
       } into OrderList
       select new
       {
        GroupName = OrderList.Key.GroupName,
        ItemName = OrderList.Key.ItemName,
        OrderTotal = OrderList.Sum(OL => OL.Qty * OL.Rate)
       };

Left Outer Join and Group By In LINQ


List Table1 = new List();
List Table2 = new List();

var objList = from ListData in
                  (from a in Table1
                   join b in Table2 on a.Id equals b.Id into t2
                   from t2Data in t2.DefaultIfEmpty()
                   select new
                   {
                       Product = a.Product,
                       Title = t2Data.Title,
                       Amount = a.Amount
                   }
                )
              group ListData by new
              {
                  ListData.Product,
                  ListData.Title
              } into GroupData
              select new
              {
                  Product = GroupData.Key.Product,
                  Title = GroupData.Key.Title,
                  Amount = GroupData.Sum(OL => OL.Amount)
              };

EditTemplates for FieldType in MVC

View->Shared->EditorTemplates->Decimal.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.TextBox("", String.Format("{0:F}", Model)) %>

DisplayFor in MVC

Views->Shared->DisplayTemplates->EmailAddress.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<a href="mailto:<%=  Html.Encode(Model) %>"><%= Html.Encode(Model) %></a>
<img src="/Content/images/sendemail.gif" />

EditorFor in MVC

Models->Supplier:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;

namespace FeaturesOfMVC2.Models
{
    [MetadataType(typeof(Supplier_Validation))]
    public partial class Supplier
    {
        class Supplier_Validation
        {
            [ScaffoldColumn(false)]
            [DisplayName("Supplier Id :")]
            public int SupplierId { get; set; }

            [Required(ErrorMessage = "Name Required!")]
            [DisplayName("Supplier Name :")]
            public string SupplierName { get; set; }

            [UIHint("StateDDL")]
            [DisplayName("State :")]
            public int StateId { get; set; }

            [UIHint("CityDDL")]
            [DisplayName("City :")]
            public int CityId { get; set; }

            [UIHint("EmailAddress")]
            [DisplayName("Email Address :")]
            public string EmailId { get; set; }
        }
    }
}

Views->Shared->EditorTemplates->StateDDL.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>

Views->Shared->EditorTemplates->CityDDL.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>

Views->Supplier->Edit.aspx


<%= Html.EditorFor(model => model.StateID) %>
<%= Html.EditorFor(model => model.CityID) %>

JSON Calling from MVC Page

HTML:


<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>

Javascript:


<script type="text/javascript" language="javascript">
  $(document).ready(function() {
      $("select[id='StateId']").change(function() {
          $.ajax({
              type: "POST",
              url: "/Supplier/StateCityInfo/",
              data: { StateId: $(this).val() },
              dataType: "json",
              error: function(xhr, status, error) {
                  // you may need to handle me if the json is invalid
                  // this is the ajax object
                  alert(status);
              },
              success: function(data) {
                  $("#CityId").empty();
                  $.each(data, function(key, City) {
                      $("#CityId").append($("<option></option>").val(City.Id).html(City.Name));
                  });
              }
          });
      })
  })
</script>

Supplier Controller:


public class SupplierController : Controller
{
  [AcceptVerbs("POST")]
  public ActionResult StateCityInfo(int StateId)
  {
      return Json(this.AllCity(StateId));
  }
}

JSON Calling from ASP.Net

HTML:


<body>
    <form id="form1" runat="server">
    No Of Doors :
    <input id="txtNoOfDoor" type="text" /><br />
    <input type="button" id="Button1" value="Get All Cars" />
    <input type="button" id="Button2" value="Get Car By No Of Door" />
    <div id="output">
    </div>
    </form>
</body>

Javascript:


<script type="text/javascript" language="javascript">
        $(function() {
            $('#Button1').click(getCars);
            $('#Button2').click(getCarsByDoors);
        });

        function getCars() {
            $.ajax({
                type: "POST",
                url: "CarService.asmx/GetAllCars",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(response) {
                    var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
                    $('#output').empty();
                    for (var i = 0; i < cars.length; i++) {
                        $('#output').append('<p><strong>' + cars[i].Make + ' ' +
                                cars[i].Model + '</strong><br /> Year: ' +
                                cars[i].Year + '<br />Doors: ' +
                                cars[i].Doors + '<br />Colour: ' +
                                cars[i].Colour + '<br />Price: £' +
                                cars[i].Price + '</p>');
                    }
                },

                failure: function(msg) {

                    $('#output').text(msg);

                }
            });
        }

        function getCarsByDoors() {
            var data = '{doors: ' + $('#txtNoOfDoor').val() + ', Name : "Jayesh" }';
            $.ajax({
                type: "POST",
                url: "CarService.asmx/GetCarsByDoors",
                data: data,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(response) {
                    var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
                    $('#output').empty();
                    for (var i = 0; i < cars.length; i++) {
                        $('#output').append('<p><strong>' + cars[i].Make + ' ' +
                                cars[i].Model + '</strong><br /> Year: ' +
                                cars[i].Year + '<br />Doors: ' +
                                cars[i].Doors + '<br />Colour: ' +
                                cars[i].Colour + '<br />Price: £' +
                                cars[i].Price + '</p>');
                    }
                },

                failure: function(msg) {

                    $('#output').text(msg);

                }
            });
        }

</script>

CarService.asmx:


/// <summary>
/// Summary description for CarService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
[ScriptService]
public class CarService : System.Web.Services.WebService
{
    List<Car> objCarList = new List<Car>{
                                    new Car{Make="Audi",Model="A4",Year=1995,Doors=5,Colour="Red",Price=2995f},
                                    new Car{Make="Ford",Model="Focus",Year=2002,Doors=5,Colour="Black",Price=3250f},
                                    new Car{Make="BMW",Model="5 Series",Year=2006,Doors=4,Colour="Grey",Price=24950f},
                                    new Car{Make="Renault",Model="Laguna",Year=2000,Doors=5,Colour="Red",Price=3995f},
                                    new Car{Make="Toyota",Model="Previa",Year=1998,Doors=5,Colour="Green",Price=2695f},
                                    new Car{Make="Mini",Model="Cooper",Year=2005,Doors=2,Colour="Grey",Price=9850f},
                                    new Car{Make="Mazda",Model="MX 5",Year=2003,Doors=2,Colour="Silver",Price=6995f},
                                    new Car{Make="Ford",Model="Fiesta",Year=2004,Doors=3,Colour="Red",Price=3759f},
                                    new Car{Make="Honda",Model="Accord",Year=1997,Doors=4,Colour="Silver",Price=1995f}
                                };

    [WebMethod]
    public List<Car> GetAllCars()
    {
        return objCarList;
    }

    [WebMethod]
    public List<Car> GetCarsByDoors(int doors, string Name)
    {
        var query = from c in objCarList
                    where c.Doors == doors
                    select c;

        return query.ToList();
    }

    [WebMethod]
    public string GetAllCarsInString()
    {
        JavaScriptSerializer objJS = new JavaScriptSerializer();
        return objJS.Serialize(objCarList);
    }
}

Car Class:


public class Car
{
    public string Make { get; set; }
    public string Model { get; set; }
    public int Year { get; set; }
    public int Doors { get; set; }
    public string Colour { get; set; }
    public float Price { get; set; }
}

Merge Statement

SQL Statement:


DECLARE @tblMergeOutput TABLE
 (
  ID                BIGINT IDENTITY(1,1)
  , MergeAction    NVARCHAR(100)
  , InsertedId    INT
  , DeletedId        INT
  , GroupID        INT
 )

 CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
 CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

 INSERT INTO #tblSource VALUES (1,1,'a11')
 INSERT INTO #tblSource VALUES (2,1,'a21')
 INSERT INTO #tblSource VALUES (3,1,'a31')
 INSERT INTO #tblSource VALUES (4,1,'a41')

 INSERT INTO #tblTarget VALUES (1,1,'a1')
 INSERT INTO #tblTarget VALUES (3,1,'a3')
 INSERT INTO #tblTarget VALUES (5,1,'a5')
 INSERT INTO #tblTarget VALUES (6,2,'b2')
 INSERT INTO #tblTarget VALUES (7,2,'b1')

 MERGE #tblTarget
 USING (
   SELECT *
   FROM #tblSource
  ) AS tblSource
   ON tblSource.GroupID = #tblTarget.GroupID
    AND tblSource.ID = #tblTarget.ID
 WHEN MATCHED THEN 
  UPDATE
  SET Name = tblSource.Name
 WHEN NOT MATCHED THEN
  INSERT (
     ID
     , GroupID
     , Name
    )
  VALUES ( 
     tblSource.ID
     , tblSource.GroupID
     , tblSource.Name
    )
 WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
  THEN DELETE
 OUTPUT 
  $action
  , inserted.Id
  , deleted.Id
  , tblSource.GroupID
 INTO @tblMergeOutput;

 SELECT * FROM #tblSource
 SELECT * FROM #tblTarget order by GroupID, ID
 SELECT * FROM @tblMergeOutput

 DROP TABLE #tblSource
 DROP TABLE #tblTarget

Target Table Output:


 ------------------
 ID  GroupID   Name
 ------------------
 1   1         a11
 2   1         a21
 3   1         a31
 4   1         a41
 6   2         b2
 7   2         b1
 ------------------

Merge Table Output:


 ----------------------------------------------------
 ID  Merge Action    InsertedID   DeletedID   GroupID
 ----------------------------------------------------
 1   INSERT          2            NULL        1
 2   INSERT          4            NULL        1
 3   UPDATE          1            1           1
 4   UPDATE          3            3           1
 5   DELETE          NULL         5           NULL
 ----------------------------------------------------

Wednesday, June 9, 2010

User Tree


DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )

INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')

;WITH cteNodes AS
(
 --initialization
 SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
 FROM @tblUInfo
 --WHERE ParentUserID IS NULL

 UNION ALL

 ----recursive execution
 SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
 FROM cteNodes AS P
  INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)

, cteEmployeePath AS
(
 SELECT tblUInfo.UserID
  , tblUInfo.UserName
  ,(
   SELECT CAST(UserTree AS VARCHAR ) + ':'
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
   ORDER BY UserLevel DESC
   FOR XML PATH('')
   ) AS UserTree
  ,(
   SELECT MAX(UserLevel)
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
  ) AS UserLevel
 FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName 
FROM cteEmployeePath 
ORDER BY UserTree 
OPTION (MaxRecursion 32767)

Tuesday, April 20, 2010

Pivot / UnPivot Example

SQL Query :


   DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

   INSERT INTO @Tbl
    SELECT 1, 'a', 'b', 'c'
    UNION 
    SELECT 2, 'd', 'e', 'f'
    UNION 
    SELECT 3, 'g', 'h', 'i'

   SELECT 
    *
   FROM
   (
    SELECT *
    FROM
    (
     SELECT 
      Col1 AS Cols
      , A AS [A(N1)] 
      , B AS [B(N2)] 
      , C AS [C(N3)] 
     FROM @Tbl
    ) A
    UNPIVOT
    ( [Value] For [Col1] IN ( 
           [A(N1)]
           , [B(N2)]
           , [C(N3)]
          ) ) AS upvt
   ) B
   PIVOT
   (
    MIN ( [Value])
    FOR [Cols]
    IN ([1], [2], [3])
   ) AS pvt;

Input :


    ----------------
    Col1       A B C
    ----------------
    1          a b c 
    2          d e f
    3          g h i
    ----------------

OutPut :

    
    -------------------
    Col1          1 2 3
    -------------------
    A(N1)         a d g
    B(N2)         b e h
    C(N3)         c f i
    -------------------