JSD Ideas

SignalR Implementation in XAML Page

2015-12-29T02:59:00.000-08:00

XAML Page:

using System.Collections.Generic;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input;
using System.Windows.Media;
using Microsoft.AspNet.SignalR.Client;

namespace SignalRDemo
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        HubConnection _connection;
        IHubProxy _hub;

        // Keep track of when fake "drag and drop" mode is enabled.
        private bool isDragging = false;

        // Store the location where the user clicked the control.
        private double clickOffsetX, clickOffsetY;

        private void window_Loaded(object sender, RoutedEventArgs e)
        {
            Start();
        }

        void label_MouseDown(object sender, MouseButtonEventArgs e)
        {
            isDragging = true;
            var position = e.GetPosition(window);

            var label = sender as Label;
            var l = (double)label.GetValue(Canvas.LeftProperty);
            var t = (double)label.GetValue(Canvas.TopProperty);

            clickOffsetX = l - position.X;
            clickOffsetY = t - position.Y;
        }

        void label_MouseMove(object sender, MouseEventArgs e)
        {
            if (isDragging == true)
            {
                // The control coordinates are converted into form coordinates
                // by adding the label position offset.
                // The offset where the user clicked in the control is also
                // accounted for. Otherwise, it looks like the top-left corner
                // of the label is attached to the mouse.

                var label = sender as Label;
                var position = e.GetPosition(window);

                var x = position.X + clickOffsetX;
                var y = position.Y + clickOffsetY;

                SetShapePosition(label, x, y);

                _hub.Invoke("MoveShape", int.Parse(label.Name.Replace("shape", "")), x, y);
            }
        }

        void label_MouseUp(object sender, MouseButtonEventArgs e)
        {
            isDragging = false;
        }

        private void Start()
        {
            _connection = new HubConnection("http://localhost/SignalRChat/myhubs/hubs");

            _hub = _connection.CreateHubProxy("SignalRHub");

            _connection.Start().Wait();

            _hub.On<int>("usersConnected", (count) =>
            {
                this.Dispatcher.Invoke(() =>
                    UpdateUserCount(count)
                );
            });

            _hub.On<List<Common.Shape>>("shapeList", (list) =>
            {
                this.Dispatcher.Invoke(() =>
                {
                    canvas.Children.Clear();

                    list.ForEach(q =>
                    {
                        var label = new Label();

                        label.Name = string.Format("shape{0}", q.Id);
                        label.Content = q.Name;
                        label.Height = 100;
                        label.Width = 100;
                        label.Padding = new Thickness(10, 10, 0, 0);
                        label.Background = new SolidColorBrush((Color)ColorConverter.ConvertFromString(q.Color));
                        SetShapePosition(label, q.X, q.Y);
                        label.FontSize = 20;

                        label.MouseDown += label_MouseDown;
                        label.MouseMove += label_MouseMove;
                        label.MouseUp += label_MouseUp;

                        canvas.Children.Add(label);
                    });
                });
            });

            _hub.On<Common.Shape>("shapeMoved", (shape) =>
            {
                this.Dispatcher.Invoke(() =>
                    UpdatePosition(shape)
                );
            });

            _hub.Invoke("GetShapeList");
        }

        private void SetShapePosition(Label label, double x, double y)
        {
            if (label != null)
            {
                label.SetValue(Canvas.LeftProperty, x);
                label.SetValue(Canvas.TopProperty, y);
            }
        }

        private void UpdateUserCount(int count)
        {
            lblCount.Content = count;
        }

        private void UpdatePosition(Common.Shape shape)
        {
            var label = UIHelper.FindChild<Label>(Application.Current.MainWindow, string.Format("shape{0}", shape.Id));
            SetShapePosition(label, shape.X, shape.Y);
        }
    }

    public class UIHelper
    {
        /// <summary>
        /// Finds a Child of a given item in the visual tree. 
        /// </summary>
        /// <param name="parent">A direct parent of the queried item.</param>
        /// <typeparam name="T">The type of the queried item.</typeparam>
        /// <param name="childName">x:Name or Name of child. </param>
        /// <returns>The first parent item that matches the submitted type parameter. 
        /// If not matching item can be found, 
        /// a null parent is being returned.</returns>
        public static T FindChild<T>(DependencyObject parent, string childName)
           where T : DependencyObject
        {
            // Confirm parent and childName are valid. 
            if (parent == null) return null;

            T foundChild = null;

            int childrenCount = VisualTreeHelper.GetChildrenCount(parent);
            for (int i = 0; i < childrenCount; i++)
            {
                var child = VisualTreeHelper.GetChild(parent, i);
                // If the child is not of the request child type child
                T childType = child as T;
                if (childType == null)
                {
                    // recursively drill down the tree
                    foundChild = FindChild<T>(child, childName);

                    // If the child is found, break so we do not overwrite the found child. 
                    if (foundChild != null) break;
                }
                else if (!string.IsNullOrEmpty(childName))
                {
                    var frameworkElement = child as FrameworkElement;
                    // If the child's name is set for search
                    if (frameworkElement != null && frameworkElement.Name == childName)
                    {
                        // if the child's name is of the request name
                        foundChild = (T)child;
                        break;
                    }
                }
                else
                {
                    // child element found.
                    foundChild = (T)child;
                    break;
                }
            }

            return foundChild;
        }
    }

}

SignalR Hub Page :

using Microsoft.AspNet.SignalR;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Common;

namespace SignalRChat
{
    public class SignalRHub : Hub
    {
        public SignalRHub()
        {

        }

        #region Moveable Shape
        public async Task GetShapeList() // this method will be called from the client, when the user drag/move the shape
        {
            await Clients.Caller.shapeList(ShapeHandler.Instance.ShapeList); // this method will send the coord x, y to the other users but the user draging the shape
        }

        public async Task MoveShape(int id, double x, double y) // this method will be called from the client, when the user drag/move the shape
        {
            var shape = ShapeHandler.Instance.GetShape(id, x, y);

            if (shape.X != x || shape.Y != y) await Clients.Caller.shapeMoved(shape);

            await Clients.Others.shapeMoved(shape); // this method will send the coord x, y to the other users but the user draging the shape
        }

        public override Task OnConnected() //override OnConnect, OnReconnected and OnDisconnected to know if a user is connected or disconnected
        {
            ShapeHandler.Instance.ConnectedIds.Add(Context.ConnectionId); //add a connection id to the list
            Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count()); //this will send to ALL the clients the number of users connected
            return base.OnConnected();
        }

        public override Task OnReconnected()
        {
            ShapeHandler.Instance.ConnectedIds.Add(Context.ConnectionId);
            Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count());
            return base.OnConnected();
        }

        public override Task OnDisconnected(bool stopCalled)
        {
            ShapeHandler.Instance.ConnectedIds.Remove(Context.ConnectionId);
            Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count());
            return base.OnDisconnected(stopCalled);
        }
        #endregion
    }
}

SignalR Implementation in HTML Page

2015-12-29T02:45:00.000-08:00

Html Page :

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <label id="serverDate"></label>
    <button id="initTimer">Start Timer</button>

    <!--Script references. -->
    <script src="Scripts/jquery-1.6.4.min.js"></script>
    <script src="Scripts/jquery.signalR-2.0.0.js"></script>

    <!--Reference the autogenerated SignalR hub script. -->
    <script src="signalR/hubs"></script>
    <script type="text/javascript">
        $(function () {
            // Declare a proxy to reference the hub.
            var hub = $.connection.demoHub;

            hub.client.serverDateTimeInfo = function (time) {
                $("#serverDate").text(time);
            };

            $.connection.hub.start().done(function () {
                alert('Server connected');

                hub.server.getServerDatetime();

                $("#initTimer").click(function () {
                    hub.server.initTimer();
                });
            });
        });
    </script>
</body>
</html>

SignalR Hub Page :

using Microsoft.AspNet.SignalR;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Timers;
using System.Web;

namespace SignalRChat
{
    public class DemoHub :Hub
    {
        public void GetServerDatetime()
        {
            SendNotification();
        }

        private void SendNotification()
        {
            try
            {
                Clients.Caller.serverDateTimeInfo(DateTime.Now.ToString());
            }
            catch { }
        }

        public void InitTimer()
        {
            Timer timer = new Timer(1000);
            timer.Elapsed += new ElapsedEventHandler(timer_Elapsed);
            timer.AutoReset = true;
            timer.Enabled = true;
        }

        public void timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            SendNotification();
        }
        
    }
}

Diffrence between CROSS APPLY and OUTER APPLY of SQL XML

2012-03-15T22:34:00.003-07:00


Sample XML :
DECLARE @XML XML = 
'<Root>
    <UserInfo Id="1" Name="Name 1">
        <CityInfo Id="2" City="City 1" />
        <CityInfo Id="3" City="City 2" >
            <PrefInfo Id="33" Name="Show Whether" />
        </CityInfo>
    </UserInfo>
    <UserInfo Id="2" Name="Name 2">
        <CityInfo Id="4" City="City 3" />
        <CityInfo Id="5" City="City 4" >
            <PrefInfo Id="33" Name="Show Temprature" />
        </CityInfo>
    </UserInfo>
</Root>'

Cross Apply :  Cross apply is basically use inner join of two xml collection
SELECT
    UserInfo.value('@Id', 'BIGINT') as UserId
    , UserInfo.value('@Name', 'VARCHAR(20)') as UserName
    , CityInfo.value('@Id', 'BIGINT') as CityId
    , CityInfo.value('@City', 'VARCHAR(20)') as CityName
    , PrefInfo.value('@Id', 'BIGINT') as PrefId
    , PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
    CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
    CROSS APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :

UserId   UserName  CityId  CityName  PrefId   PrefName
-------- --------- ------- --------- -------- ----------------
1        Name 1    3       City 2    33       Show Whether
2        Name 2    5       City 4    33       Show Temprature 

Outer Apply :  Outer apply is basically use left outer join of two xml collection
SELECT
    UserInfo.value('@Id', 'BIGINT') as UserId
    , UserInfo.value('@Name', 'VARCHAR(20)') as UserName
    , CityInfo.value('@Id', 'BIGINT') as CityId
    , CityInfo.value('@City', 'VARCHAR(20)') as CityName
    , PrefInfo.value('@Id', 'BIGINT') as PrefId
    , PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
    CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
    OUTER APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)

Output :
UserId  UserName  CityId  CityName  PrefId  PrefName
------- --------- ------- --------- ------- ----------------
1       Name 1    2       City 1    NULL    NULL
1       Name 1    3       City 2    33      Show Whether
2       Name 2    4       City 3    NULL    NULL
2       Name 2    5       City 4    33      Show Temprature

How to Handle button click event in jquery

2012-03-14T04:25:00.003-07:00

Code :


   <script type="text/javascript" language="javascript">
        $(document).ready(function () {
            $("[id*='btnSave']").click(function (event) {
                event.preventDefault();
                if (confirm("Want to save data?")) {
                    $(this).unbind('click').click()
                }
            });

        });
    </script>

jQuery Selector

2011-09-20T23:41:00.000-07:00


#No     Syntax                              Description
---------------------------------------------------------------------------------------
1.      $(this)                             Current HTML element
2.      $(".intro")                         All elements with class="intro"
3.      $("#intro")                         The first element with id="intro"
4.      $("p")                              All <p> elements
5.      $("p.intro")                        All <p> elements with class="intro"
6.      $("p#intro")                        All <p> elements with id="intro"
7.      $("p#intro:first-child")            The first <p> element with id="intro"
8.      $("ul li:first-child")              The first <li> element of each <ul>
9.      $("ul li:last-child")               The last <li> element of each <ul>
10.     $("ul li:nth-child(4)")             The fourth <li> element of each <ul>
11.     $("div#intro .head")                All elements with class="head" and id="intro" of DIV
12.     $("[href*='User']")                 All elements with href contains "User"
13.     $("[href^='User']")                 All elements with href start with "User"
14.     $("[href$='.html']")                All elements with an href attribute
                                            that ends with ".html"
15.     $("[href*='User'] div")             AND condition for Getting all element 
                                            which have href contains "User" and inner element div
16.     $("[href*='User'],div")             OR condition for Getting all element 
                                            which have href contains "User" or div element
17.     $("[href!='UserInfo.html']")        NOT condition for Getting all element   
                                            which have href not equle to "UserInfo.html"
18.     $("div > input#User")               Getting all element which have a parent element is
                                            DIV and next element is INPUT have a id User
19.     $("div").find("input#User")         Getting all element of parent element is 
                                            DIV and child element is INPUT have a id User
20.     $("div").not(".UserInfo, #UserId")  Getting all div element which not have a 
                                            class USERINFO or id is USERID

Rounding date in SQL query

2011-08-29T00:23:00.000-07:00


Query : 
SELECT
    DATEADD( MI, DATEDIFF( MI, 0, DATEADD( SS, 30, Dates.SampleDate) ), 0) AS RoundedDate
FROM
(
    SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:29.998')
    UNION ALL
    SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:30.000')
) Dates

Results : 

RoundedDate
-----------------------
2011-08-29 12:59:00.000
2011-08-29 13:00:00.000

Use of Rollup, Grouping, Grouping_ID and Cube in Group By

2011-07-01T00:16:00.000-07:00


ROLLUP = Generates the simple GROUP BY aggregate rows
  , plus subtotal or super-aggregate rows
  , and also a grand total row.
GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
GROUPING_ID = Is a function that computes the level of grouping.
CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
Sample Data
DECLARE @Sales TABLE (Year INT, Quarter VARCHAR(50), SalesPerson VARCHAR(50), Amount FLOAT)

INSERT INTO @Sales values
      (1998, 'Q1', 'SalePerson1', 123)
    , (1998, 'Q2', 'SalePerson2', 234)
    , (1998, 'Q3', 'SalePerson4', 345)
    , (1998, 'Q4', 'SalePerson3', 556)
    , (1999, 'Q1', 'SalePerson1', 623)
    , (1999, 'Q2', 'SalePerson2', 734)
    , (1999, 'Q3', 'SalePerson3', 845)
    , (1999, 'Q4', 'SalePerson4', 956)

Example 1 : (Single field in grouping)

SELECT
     [Year]
    , AVG(Amount) as Average
    , GROUPING([Year]) as [YearRollUp?]
FROM 
    @Sales
GROUP BY
    [Year] WITH ROLLUP

Output :

Year        Average                YearRollUp?
----------- ---------------------- -----------
1998        314.5                  0
1999        789.5                  0
NULL        552                    1

Example 2 : (Multipal field in grouping)

SELECT
    [Year]
    , Quarter
    , AVG(Amount) as Average
    , GROUPING( [Year] ) as [YearRollUp?]
FROM 
    @Sales
GROUP BY
    GROUPING SETS (( [Year], Quarter), ( [Year] ), ())

Output : 

Year        Quarter Average                YearRollUp?
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1998        Q2      234                    0
1998        Q3      345                    0
1998        Q4      556                    0
1998        NULL    314.5                  0
1999        Q1      623                    0
1999        Q2      734                    0
1999        Q3      845                    0
1999        Q4      956                    0
1999        NULL    789.5                  0
NULL        NULL    552                    1

Example 3 :( Example of Grouping_ID )

SELECT
    [Year]
    , Quarter
    , AVG(Amount) as Average
    , GROUPING_ID( [Year], Quarter) as [x]
FROM 
    @Sales
GROUP BY
    GROUPING SETS (( [Year], Quarter), ( [Year] ), ( [Quarter] ), ())

Output :

Year        Quarter Average                x
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1999        Q1      623                    0
NULL        Q1      373                    2
1998        Q2      234                    0
1999        Q2      734                    0
NULL        Q2      484                    2
1998        Q3      345                    0
1999        Q3      845                    0
NULL        Q3      595                    2
1998        Q4      556                    0
1999        Q4      956                    0
NULL        Q4      756                    2
NULL        NULL    552                    3
1998        NULL    314.5                  1
1999        NULL    789.5                  1

Example 4:(Example of Cube)

SELECT
     [Year]
    , [Quarter]
    , AVG(Amount) as Average
    , GROUPING_ID( [Year], [Quarter]) as [x]
FROM 
    @Sales
GROUP BY
    CUBE( [Year], [Quarter])

Output :

Year        Quarter Average                x
----------- ------- ---------------------- -----------
1998        Q1      123                    0
1999        Q1      623                    0
NULL        Q1      373                    2
1998        Q2      234                    0
1999        Q2      734                    0
NULL        Q2      484                    2
1998        Q3      345                    0
1999        Q3      845                    0
NULL        Q3      595                    2
1998        Q4      556                    0
1999        Q4      956                    0
NULL        Q4      756                    2
NULL        NULL    552                    3
1998        NULL    314.5                  1
1999        NULL    789.5                  1

Query with static output

2011-06-30T23:15:00.000-07:00

Query :


select * from
(
    values (1, 2), (2, 3), (3, 4)
) as MyTable(Col1, Col2)

Output :

Col1        Col2
----------- -----------
1           2
2           3
3           4

Genrate table to ENUM

2011-04-28T03:35:00.000-07:00

DECLARE @TableName  VARCHAR(20) = 'tblFileType'        -------------> type table name

DECLARE @ValueField VARCHAR(20) = 'Id'                -------------> type ValueField Name

DECLARE @NameField  VARCHAR(20) = 'Name'            -------------> type NAme Field Name

BEGIN

    DECLARE @SqlQuery VARCHAR(MAX) =

    'SELECT ''public enum enum' + REPLACE( @TableName, 'tbl', '')  + ''' AS enum

    UNION ALL

    SELECT ''{''

    UNION ALL

    SELECT ( REPLACE(' + @NameField + ', '' '', '''') + '' = '' + CAST( ' + @ValueField + ' AS VARCHAR(10) ) ) + '','' FROM ' + @TableName

    + ' UNION ALL SELECT ''}'' '



    PRINT @SqlQuery

    EXECUTE( @SqlQuery )

    -- SELECT Name + ' = ' + CAST( Id AS VARCHAR(10) ) + ',' FROM tblUserRoll

END

MVC Application Configration in IIS

2010-11-18T00:27:00.000-08:00

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

Get All Relationship of Primary Key

2010-07-20T01:21:00.001-07:00


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

Get detailed error handling information in SQL

2010-07-17T03:15:00.001-07:00

Refer this link

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

Searching and Multipal Selection Criteria in JQuery

2010-07-14T23:06:00.001-07:00



    <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>

JQuery

2010-07-12T01:21:00.000-07:00


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);
}
-----------------------------------

Using Enum in Javascript

2010-07-11T21:32:00.000-07:00


<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>

Enum Extention

2010-07-10T03:39:00.000-07:00

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>

OUTPUT Clause

2010-07-09T23:19:00.000-07:00

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

2010-07-09T22:04:00.001-07:00


    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

Enable AD Hoc Distributed Queries OPENROWSET

2010-07-08T23:14:00.001-07:00

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

2010-07-08T22:54:00.000-07:00

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

2010-07-08T22:38:00.001-07:00

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

2010-07-08T03:25:00.000-07:00

Refer this link

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

Create XLS File in ASP.NET application

2010-07-08T03:13:00.000-07:00

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

MERGE Statement and compatibility level

2010-07-07T21:55:00.002-07:00

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

Delegate using in ASPX Page and user control

2010-07-05T02:30:00.000-07:00

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();
    }
}