Saturday, 25 August 2012

Using Autocomplete Extender passing an extra parameter through the contextKey.

I'm trying to pass a static value (from a list box) into an Autocomplete Extender.   I've had Autocomplete working with just the prefixText but can not get it to work when passing an extra parameter through the contextKey.

Please see below:

On Page Load:
 AutoCompleteExtender1.ContextKey = lstUserID.SelectedValue

(the 'lstUserID' holds the ID of the group logged in)

the autoComplete Extender:

<cc1:AutoCompleteExtender
                    ServiceMethod="SearchNino"
                    MinimumPrefixLength="2"
                    CompletionInterval="100"
                    EnableCaching="false"
                    CompletionSetCount="10"
                    TargetControlID="txtNinoSearch"
                    ID="AutoCompleteExtender1"
                    runat="server"
                    FirstRowSelected = "false"
                    UseContextKey="True"
                    >

Here's the method..
<System.Web.Script.Services.ScriptMethod(), _
  System.Web.Services.WebMethod()> _
 Public Shared Function SearchNINO(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As List(Of String)

        Dim conn As SqlConnection = New SqlConnection
        conn.ConnectionString = ConfigurationManager _
         .ConnectionStrings("employersConnectionString").ConnectionString
        Dim cmd As SqlCommand = New SqlCommand
        cmd.CommandText = "select NINO from table where" & _
         " ((NINO like @SearchText + '%') AND (Emp_Code = contextKey)) Group By NINO"
        cmd.Parameters.AddWithValue("@SearchText", prefixText)
        cmd.Connection = conn
        conn.Open()

        Dim NINO As List(Of String) = New List(Of String)
        Dim sdr As SqlDataReader = cmd.ExecuteReader
        While sdr.Read
            PayRef.Add(sdr("NINO").ToString)
        End While
        conn.Close()
        Return NINO

    End function
 
C# Code: 

 <asp:ToolkitScriptManager runat="server" ID="TSM">
        </asp:ToolkitScriptManager>
        <script type="text/javascript">
            function pageLoad(sender, e) {
                var o = $find("ACE1");
                o.add_populating(function () {
                    o.set_contextKey($get("<%=TxtPara1.ClientID %>").value + "|" + $get("<%=TxtPara2.ClientID %>").value);
                });
            }
        </script>
        para1:
        <asp:TextBox runat="server" ID="TxtPara1"></asp:TextBox><br />
        para2:
        <asp:TextBox runat="server" ID="TxtPara2"></asp:TextBox><br />
        AutoCompleteTextBox:
        <asp:TextBox runat="server" ID="auto"></asp:TextBox>
        <asp:AutoCompleteExtender runat="server" ID="ACE1" MinimumPrefixLength="1" TargetControlID="auto"
            UseContextKey="true" ServicePath="service.asmx" ServiceMethod="AutoMethod">
        </asp:AutoCompleteExtender>
 Webservice
 [WebMethod]
    public static string[] AutoMethod(string prefixText, int count, string contextKey)
    {
        string[] paras = contextKey.Split('|');
        List<string> rsl = new List<string>();
        rsl.Add("para1:" + paras[0]);
        rsl.Add("para2:" + paras[1]);
        return rsl.ToArray();
    } 

Friday, 24 August 2012

Inline User-Defined Functions

Inline function rules
  • The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
  • There is no function_body delimited by BEGIN and END.
  • The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
  • The table-valued function accepts only constants or @local_variable arguments
    CREATE FUNCTION dbo.fx3
     (
    @eid int
     )
    RETURNS TABLE
    AS
    
    RETURN (
            SELECT *
            FROM emp
            WHERE eid = @eid
           )
    EXECUTE:
    SELECT        EID, NAME, DID, SAL, MGR
    FROM            dbo.fx3(3) AS fx3_1
    Press Me 

Monday, 20 August 2012

Create a new Class Library project in Visual Studio

I'm using Visual Studio 2008, but other versions should work as well.
  1. After starting Visual Studio click File -> New -> Project and select Class Library under C#.
  2. Call the project 'AxControls' and click OK.

2. Create a new class that inherits from UserControl

  1. Rename 'Class1.cs' to 'HelloWorld.cs', making sure to rename the class name as well.
  2. Add a project reference to System.Windows.Forms.
  3. Make the HelloWorld class inherit UserControl.

3. Create a new interface that exposes the controls methods and properties to COM interop

  1. Right click the project in Visual Studio and click Add -> New Item.
  2. Select 'Interface' from the list of components, name it 'IHelloWorld.cs' and click Add.
  3. Edit the 'IHelloWorld.cs' file so it looks like this:
    01using System;
    02using System.Collections.Generic;
    03using System.Text;
    04using System.Runtime.InteropServices;
    05 
    06namespace AxControls
    07{
    08    [ComVisible(true)]
    09    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    10    [Guid("41E85D5D-C57A-4386-B722-4031D0B1E1B7")]
    11    public interface IHelloWorld
    12    {
    13        string GetText();
    14    }
    15}
We now have a COM visible interface with a single method 'GetText()'.
[ComVisible(true)] makes the interface visible to COM.
[InterfaceType(ComInterfaceType.InterfaceIsDual)] sets the COM interface type to Dual, see InterfaceTypeAttribute Class on MSDN.
[Guid("41E85D5D-C57A-4386-B722-4031D0B1E1B7")] let's us manually assign a GUID to the interface. Use guidgen.exe to generate your own.

4. Make the control class implement the new interface

Make the HelloWorld class implement the IHelloWorld interface and have the GetText() method return a string of your choice. This is what the file might look like:
01using System;
02using System.Collections.Generic;
03using System.Text;
04using System.Windows.Forms;
05using System.Runtime.InteropServices;
06 
07namespace AxControls
08{
09    [ComVisible(true)]
10    [ClassInterface(ClassInterfaceType.None)]
11    [Guid("1FC0D50A-4803-4f97-94FB-2F41717F558D")]
12    [ProgId("AxControls.HelloWorld")]
13    [ComDefaultInterface(typeof(IHelloWorld))]
14    public class HelloWorld : UserControl, IHelloWorld
15    {
16        #region IHelloWorld Members
17 
18        public string GetText()
19        {
20            return "Hello ActiveX World!";
21        }
22 
23        #endregion
24    }
25}
We now have a COM visible control that implements the IHelloWorld interface.
[ComVisible(true)] makes the control visible to COM, see ComVisibleAttribute Class on MSDN.
[ClassInterface(ClassInterfaceType.None)] indicates that no class interface is generated for this class, see ClassInterfaceType Enumeration on MSDN.
[Guid("1FC0D50A-4803-4f97-94FB-2F41717F558D")] let's us manually assign a GUID to the control, see GuidAttribute Class on MSDN. Use guidgen.exe to generate your own.
[ProgId("AxControls.HelloWorld")] is a "user friendly" ID that we'll use later from JavaScript when initiating the control, see ProgIdAttribute Class on MSDN.
[ComDefaultInterface(typeof(IHelloWorld))] sets IHelloWorld as the default interface that will be exposed to COM, see ComDefaultInterfaceAttribute Class on MSDN.

5. Mark the control as safe for scripting and initialization

By default IE will not allow initializing and scripting an ActiveX control unless it is marked as safe. This means that we won't be able to create instances of our ActiveX class with JavaScript by default. We can get around this by modifying the browser security settings, but a more elegant way would be to mark the control as safe. Before you do this to a "real" control, be sure to understand the consequences. I found an ancient (1996) MSDN article that explains this here. We will mark the control as safe by implementing the IObjectSafety interface.
  1. Right click the project in Visual Studio and click Add -> New Item.
  2. Select 'Interface' from the list of components, name it 'IObjectSafety.cs' and click Add.
  3. Edit the 'IObjectSafety.cs' file so it looks like this:
    01using System;
    02using System.Collections.Generic;
    03using System.Text;
    04using System.Runtime.InteropServices;
    05 
    06namespace AxControls
    07{
    08    [ComImport()]
    09    [Guid("CB5BDC81-93C1-11CF-8F20-00805F2CD064")]
    10    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    11    interface IObjectSafety
    12    {
    13        [PreserveSig()]
    14        int GetInterfaceSafetyOptions(ref Guid riid, out int pdwSupportedOptions, out int pdwEnabledOptions);
    15 
    16        [PreserveSig()]
    17        int SetInterfaceSafetyOptions(ref Guid riid, int dwOptionSetMask, int dwEnabledOptions);
    18    }
    19}
  4. Make the HelloWorld class implement the IObjectSafety interface. The end result should look something like this:
    01using System;
    02using System.Collections.Generic;
    03using System.Text;
    04using System.Windows.Forms;
    05using System.Runtime.InteropServices;
    06 
    07namespace AxControls
    08{
    09    [ComVisible(true)]
    10    [ClassInterface(ClassInterfaceType.None)]
    11    [Guid("1FC0D50A-4803-4f97-94FB-2F41717F558D")]
    12    [ProgId("AxControls.HelloWorld")]
    13    [ComDefaultInterface(typeof(IHelloWorld))]
    14    public class HelloWorld : UserControl, IHelloWorld, IObjectSafety
    15    {
    16        #region IHelloWorld Members
    17 
    18        public string GetText()
    19        {
    20            return "Hello ActiveX World!";
    21        }
    22 
    23        #endregion
    24 
    25        #region IObjectSafety Members
    26 
    27        public enum ObjectSafetyOptions
    28        {
    29            INTERFACESAFE_FOR_UNTRUSTED_CALLER = 0x00000001,
    30            INTERFACESAFE_FOR_UNTRUSTED_DATA = 0x00000002,
    31            INTERFACE_USES_DISPEX = 0x00000004,
    32            INTERFACE_USES_SECURITY_MANAGER = 0x00000008
    33        };
    34 
    35        public int GetInterfaceSafetyOptions(ref Guid riid, out int pdwSupportedOptions, out int pdwEnabledOptions)
    36        {
    37            ObjectSafetyOptions m_options = ObjectSafetyOptions.INTERFACESAFE_FOR_UNTRUSTED_CALLER | ObjectSafetyOptions.INTERFACESAFE_FOR_UNTRUSTED_DATA;
    38            pdwSupportedOptions = (int) m_options;
    39            pdwEnabledOptions = (int) m_options;
    40            return 0;
    41        }
    42 
    43        public int SetInterfaceSafetyOptions(ref Guid riid, int dwOptionSetMask, int dwEnabledOptions)
    44        {
    45            return 0;
    46        }
    47 
    48        #endregion
    49    }
    50}
[ComImport()] IObjectSafety is a native interface so we have to redefine it for managed .Net use. This is done with the ComImport() attribute, see ComImportAttribute Class on MSDN.
[Guid("DCE0B4D4-FA5C-43e4-AE3E-0C881A6DD293")] This is the GUID of the original IObjectSafety interface. Do not change it.
[InterfaceType(ComInterfaceType.InterfaceIsIUnknown)] sets the COM interface type to Unknown, see InterfaceTypeAttribute Class on MSDN.
This is just a simple implemetation of the IObjectSafety interface that will mark the control as safe. In "real life" there would probably be some sort of logic to determine if the control is safe or not.

6. Create a .msi installer for the control

Before an ActiveX control can be used it must be installed and registered on the client. This can be done in a number of ways, from manually editing the registry to using regasm.exe, but we're going to create a Vistual Studio setup project to handle the installation for us.
  1. Right click the Visual Studio solution, select Add -> New Project and select Setup Project under Other Project Types.
  2. Call the project 'AxControlsInstaller' and click OK.
  3. Right click the 'AxControlsInstaller' project, select Add -> Project Output, select 'Primary output' from the 'AxControls' project and click OK.
  4. Right click 'Primary output from AxControls (Active)' and select Properties.
  5. Change the Register property from 'vsdrpDoNotRegister' to 'vsdrpCOM'.
  6. Right click the 'AxControlsInstaller' project and select Build.
The installer should now be located in the AxControlsInstaller's output folder (bin\Debug or bin\Release). In the corporate domain this .msi file can de run manually on the client, or automatically with a Group Policy.

7. Package the installer in a .cab file for web deployment

For public web sites we obviously can't deploy our ActiveX control to the client with a Group Policy. In this case we're gonna have to use Internet Explores built-in ability to download and install controls that are packaged in .cab files.
  1. Download the Microsoft Cabinet Software Development Kit.
  2. Unpack the kit to a local folder and copy Cabarc.exe to the 'AxControlsInstaller' folder.
  3. Create a new file named 'AxControls.inf' in the 'AxControlsInstaller' folder and add the following content:
    01[version]
    02signature="$CHICAGO$"
    03AdvancedINF=2.0
    04 
    05[Add.Code]
    06AxControlsInstaller.msi=AxControlsInstaller.msi
    07 
    08[AxControlsInstaller.msi]
    09file-win32-x86=thiscab
    10clsid={1FC0D50A-4803-4f97-94FB-2F41717F558D}
    11FileVersion=1,0,0,0
    12 
    13[Setup Hooks]
    14RunSetup=RunSetup
    15 
    16[RunSetup]
    17run="""msiexec.exe""" /i """%EXTRACT_DIR%\AxControlsInstaller.msi""" /qn
  4. Click the AxControlsInstaller project and then click the Properties window (View -> Properties Window if it's not visible).
  5. Click the '...' button next to the PostBuildEvent property and add the following content:
    1"$(ProjectDir)\CABARC.EXE" N "$(ProjectDir)AxControls.cab" "$(ProjectDir)AxControls.inf" "$(ProjectDir)$(Configuration)\AxControlsInstaller.msi"
  6. Right click the 'AxControlsInstaller' project and select Build.
  7. There should now be a 'AxControls.cab' file in the 'AxControlsInstaller' folder.
NB! Make sure you use ANSI encoding for the 'AxControls.inf' file or you will be unable to install the control.

8. Ininitalize and test the control with JavaScript

  1. Right click the AxControls solution, select Add -> New Project and select 'ASP.Net Web Application' under 'Web'.
  2. Call the project 'WebAppTest' and click OK.
  3. Right click the 'WebAppTest' project, select Add -> New Item and select 'HTML Page'.
  4. Call it 'index.html' and click OK.
  5. Add the following content to index.html:
    01<html>
    02    <head>
    03 
    04        <object name="axHello" style='display:none' id='axHello' classid='CLSID:1FC0D50A-4803-4f97-94FB-2F41717F558D' codebase='AxControls.cab#version=1,0,0,0'></object>
    05 
    06      <script language="javascript">
    07 
    08        <!-- Load the ActiveX object  -->
    09        var x = new ActiveXObject("AxControls.HelloWorld");
    10 
    11        <!-- Display the String in a messagebox -->
    12        alert(x.GetText());
    13 
    14      </script>
    15    </head>
    16    <body>
    17    </body>
    18</html>
    Note that 'classid' matches the GUID of the HelloWorld control.
  6. Right click 'index.html' and select 'Set as start page'.
  7. Right click the 'WebAppTest' project and select 'Set as startup project'.
  8. Copy 'AxControls.cab' from the 'AxControlsInstaller' folder to the same folder as index.html.
  9. Uninstall the control from the client by going to Control Panel -> Programs and Features, selecting 'AxControlsInstaller' on the list and clicking Uninstall. This forces Internet Explorer to download and install the .cab file and is an important step in case you've already installed the control.
  10. Run the application (F5). This will open 'index.html' in Internet Explorer.
  11. Internet Explorer will display a security warning, asking if you want to install 'AxControls.cab'. Click Install.
  12. When the page loads it should display a message box with the string you defined in HelloWorld's GetText() method.
If the message box displayed without any more warnings or errors we've implemented everyting correctly.

Saturday, 11 August 2012

how to find chebox in grideview in javascript

var grid = document.getElementById("<%=grdInvoice.ClientID%>"); var inputs = grid.getElementsByTagName("input"); for (var i = 0; i < inputs.length; i++) { if (inputs[i].type == "text") { if (inputs[i].name == "txtAmount" || inputs[i].id == "txtAmount") { amnt = parseInt(inputs[i].value); alert(amnt.toString()); } } }

Thursday, 9 August 2012

simple example of linq

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        List<emp> x = new List<emp>()
        {
            new emp{ EID=1, NAME="siv", MGR=0, SALARY=5000,DID=1},
            new emp{ EID=2, NAME="sankar", MGR=1, SALARY=4000,DID=2},
              new emp{ EID=3, NAME="mahadev", MGR=2, SALARY=3000,DID=3},
               new emp{ EID=4, NAME="ram", MGR=1, SALARY=2000,DID=5}
        };
        List<dept> y = new List<dept>()
        {
            new dept{ DID=1, DNAME="A"},
            new dept{ DID=2, DNAME="B"},
            new dept{ DID=3, DNAME="C"},
            new dept{ DID=4, DNAME="D"}
        };
        List<salgrade> z = new List<salgrade>()
        {
             new salgrade{ LOWSAL=1, HISAL=1000, RANK=1},
              new salgrade{ LOWSAL=1001, HISAL=2000, RANK=2},
               new salgrade{ LOWSAL=2001, HISAL=3000, RANK=3},
               new salgrade{ LOWSAL=3001, HISAL=4000, RANK=4},
               new salgrade{ LOWSAL=4001, HISAL=5000, RANK=5}
        };
        var m = from a in x select a;
     var m = from a in x where a.SALARY>=3000 select a;
      var m = from a in x where a.SALARY==4000 && a.NAME=="sankar" select a;
    var m = from a in x where a.SALARY>=4000 || a.NAME=="sankar" select a;
    var m = from a in x where  a.NAME!="sankar" select a;
    var m = (from x in o.EMP5s select x.SAL).Distinct();
    var m = from a in x orderby a.SALARY ascending  select a;
     var m = from a in x group a by a.DID into b where b.Count() > 1 select new {b.Key,n=b.Count() };
      var m = from x in o.EMP5s group o.EMP5s by x.DID into y
                from z in o.DEPTs where z.DID==y.Key
                select new {DID=y.Key,z.DNAME, coun = y.Count() };   
     var m = from a in x where a.SALARY < x.Max(b => b.SALARY) select a;
     var m = from a in x where a.SALARY > x.Min(b => b.SALARY) select a;
         var m = from a in x select new {a.EID,a.NAME,Sumsallary=x.Sum(b=>b.SALARY) };
     var m=from a in x
                from b in y
                where a.DID == b.DID
                select new {a.EID,a.NAME,b.DID,b.DNAME};
    var m=from a in x
                from b in y
                from c in z
                where a.DID == b.DID && (a.SALARY >= c.LOWSAL && a.SALARY <= c.HISAL)
                select new {a.EID,a.NAME,a.SALARY,b.DNAME,c.RANK};
var m = from a in x
                from b in y
                where 1==1
                select new { a.EID, a.NAME, b.DNAME };
        var m=from a in x
                from b in y
                from c in z
                where a.DID == b.DID && (a.SALARY >= c.LOWSAL && a.SALARY <= c.HISAL)
                select new {a.EID,a.NAME,a.SALARY,b.DNAME,c.RANK};
     var m =from a in x
               join b in y on a.DID equals b.DID into c
               from b in c.DefaultIfEmpty()
               select new {a.EID,a.NAME,DNAME=(b==null?null:b.DNAME)};
 var m = from x in o.EMPs
                join y in o.DEPTs
                on 1 equals 1
                select new {x.EID,x.NAME,y.DNAME};
 var m = from x in o.EMPs
                join y in o.DEPTs on x.DID equals y.DID
                join z in o.SALGRADEs on 1 equals 1
                where x.SAL >= z.LOWSAL && x.SAL <= z.HISAL
                select new {x.EID,x.NAME,y.DNAME,z.RANK };

    var  m=from a in y
                join b in x
                on a.DID equals b.DID
                into c
                from b in c.DefaultIfEmpty()
                where b == null
                select a;
    var m=from a in x
              join b in x
              on a.MGR equals b.EID
              select new { mark = a.NAME + " is working under " + b.NAME };
     var m = from a in x
                join b in x
                on a.MGR equals b.EID
                into c from b in c.DefaultIfEmpty()
                select new { mark =  a.NAME + " is working under " + (b==null?null :b.NAME)};
 var m = from x in o.DEPTs
                join y in o.EMPs
                on x.DID equals y.DID
                into z
                from n in z.DefaultIfEmpty()
                select new {NAME=(n.NAME==null?null:n.NAME),x.DID,x.DNAME};

var m = from x in o.DEPTs
                join y in o.EMPs
                on x.DID equals y.DID
                into z
                from n in z.DefaultIfEmpty()
                where n==null
                select new {NAME=(n.NAME==null?null:n.NAME),x.DID,x.DNAME};
    var m = from a in x
                let b = (a.EID == 1 ? "siv" : a.EID == 2 ? "sankar" : "mahadev")
                select new {a.EID,a.NAME,b };
        gv.DataSource = m;
        gv.DataBind();
    }
    class emp
    {
        public int EID
        {
            get;
            set;
        }
        public string NAME
        {
            get;
            set;
        }
        public int MGR
        {
            get;
            set;
        }
        public decimal SALARY
        {
            get;
            set;
        }
        public int DID
        {
            get;
            set;
        }
    }
    class dept
    {
        public int DID
        {
            get;
            set;
        }
        public string DNAME
        {
            get;
            set;
        }
    }
    class salgrade
    {
        public decimal LOWSAL
        {
            get;
            set;
        }
        public decimal HISAL
        {
            get;
            set;
        }
        public int RANK
        {
            get;
            set;
        }
    }
}

Tuesday, 7 August 2012

use of transation in sql server

ALTER PROCEDURE Uams.AMS_ReceiveAsset
(
    @PurchaseOrderId int,
    @InvoiceId int,
    @GrossTotal decimal(18,2),
    @LocationId int,
    @CreatedBy int,
    @DataTable_Asset as Uams.AMS_ReceiveAssetDetails_Type READONLY,
    @msg varchar(50) output,
    @ReceiptNo varchar(50) output
)
As
Begin
    --Begin Transaction
        --Begin Try
            declare @ReceiveId int
            declare @x int
            if(not exists(select Receive_ID from Uams.AMS_ReceiveAssetMaster where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId))
            begin
                set @x=(select isnull(max(Receive_ID),0) from Uams.AMS_ReceiveAssetMaster)
                if(@x=0)
                    begin
                        set @x=1
                    end
                else
                    begin
                        set @x=@x+1
                    end
                set @ReceiptNo='Receipt/'+Convert(varchar(10),@x);
                insert into Uams.AMS_ReceiveAssetMaster(Receipt_No,Invoice_ID,Purchase_Order_ID,Gross_Total,Location_ID,Created_On,Created_By,Is_Active,Is_Deleted)
                                                 values(@ReceiptNo,@InvoiceId,@PurchaseOrderId,@GrossTotal,@LocationId,GETDATE(),@CreatedBy,1,0)
                if(@@ROWCOUNT>0)
                begin
                    set @ReceiveId=@@Identity
                    insert into Uams.AMS_ReceiveAssetDetails(Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,Receive_ID,Status,Created_On,Created_By,Is_Active,Is_Deleted)
                    select Asset_ID,Quantity,Unit_Price,Vat,Discount,Total_Price,@ReceiveId,'Pending',GETDATE(),@CreatedBy,1,0 from @DataTable_Asset
                    set @msg='Asset received against Receipt No : Receipt/'+Convert(varchar(10),@ReceiveId)

                   
                end
                begin
                    declare @ReceiveSum decimal(18,2)
                    declare @PurchaseSum decimal(18,2)
                    update Uams.AMS_InvoiceMaster set Status='Receive' where Invoice_ID=@InvoiceId and Purchase_Order_ID=@PurchaseOrderId
                    set @ReceiveSum=(select sum(Quantity) from Uams.AMS_ReceiveAssetDetails where Receive_ID=@ReceiveId)
                    set @PurchaseSum=(select sum(Quantity) from Uams.AMS_PurchaseItem where Purchase_Order_ID=@PurchaseOrderId)
                    if(@ReceiveSum=@PurchaseSum)
                        begin
                            update Uams.AMS_ReceiveAssetMaster set Status='FullyReceive' where Receive_ID=@ReceiveId
                            update Uams.AMS_PurchaseOrder set Status='FullyReceive' where Purchase_Order_ID=@PurchaseOrderId
                        end
                    else
                        begin
                            update Uams.AMS_ReceiveAssetMaster set Status='PartialReceive' where Receive_ID=@ReceiveId
                            update Uams.AMS_PurchaseOrder set Status='PartialReceive' where Purchase_Order_ID=@PurchaseOrderId
                        end
                end
            end
            else
                begin
                    set @ReceiptNo='No Receipt'
                    set @msg='Asset already received'
                    return
                end
            --Commit
        --end Try   
        --Begin Catch
            --Rollback
        --End Catch                                          
End

Sunday, 5 August 2012

Programming DateTime using C#


In .NET Framework, a DateTime structure represents dates and times. We use DateTime to work with dates, times, and both. The value of DateTime is between 12:00:00 midnight, January 1, 0001 to 11:59:59 P.M., December 31, 9999 A.D.
This chapter is all about date and time representation in .NET framework. The chapter covers the following topics:
  • How to create a DateTime
  • Understand DateTime properties
  • How to add and subtract date and time using DateTime
  • Find days in a month and year
  • How to compare two dates, times or DateTime
  • How to format dates and times

Creating DateTime
There are several ways to create a DateTime object. A DateTime object can have a Date, Time, Localization, culture, milliseconds, and kind.
The code in Listing 1 uses various constructors of DateTime structure to create DateTime objects.
// Create a DateTime from date and time
DateTime dob = new DateTime(1974, 7, 10, 7, 10, 24);
 
// Create a DateTime from a String
string dateString = "7/10/1974 7:10:24 AM";
DateTime dateFromString =
    DateTime.Parse(dateString, System.Globalization.CultureInfo.InvariantCulture);
Console.WriteLine(dateFromString.ToString());
 
// Empty DateTime
DateTime emptyDateTime = new DateTime();
 
// Just date
DateTime justDate = new DateTime(2002, 10, 18);
 
// DateTime from Ticks
DateTime justTime = new DateTime(1000000);
 
// DateTime with localization
DateTime dateTimeWithKind = new DateTime(1974, 7, 10, 7, 10, 24, DateTimeKind.Local);
 
// DateTime with date, time and milliseconds
DateTime dateTimeWithMilliseconds = new DateTime(2010, 12, 15, 5, 30, 45, 100);
 
Listing 1
 
DateTime Properties
 
The Date and the Time properties of DateTime get the date and the time of a DateTime. Some self-explanatory DateTime properties are Hour, Minute, Second, Millisecond, Year, Month, and Day.
 
Here is a list of some other properties with their brief description.
  • DayOfWeek property returns the name of the day in a week.
  • DayOfYear property returns the day of a year.
  • TimeOfDay property returns the time element in a DateTime.
  • Today property returns the DateTime object that has today's values. Time value is 12:00:00.
  • Now property returns a DateTime object that has right now date and time values.
  • UtcNow property returns a DateTime in Coordinated Universal Time (UTC)
  • A tick represents one hundred nanoseconds or one ten-millionth of a second. Ticks property of DateTime returns the number of ticks in a DateTime.
  • Kind property returns a value that indicates whether the time represented by this instance is based on local time, Coordinated Universal Time (UTC), or neither. The default value is unspecified.

The code listed in Listing 2 creates a DateTime object and reads its properties.
 
DateTime dob = new DateTime(1974, 7, 10, 7, 10, 24);
Console.WriteLine("Day:{0}", dob.Day);
Console.WriteLine("Month:{0}", dob.Month);
Console.WriteLine("Year:{0}", dob.Year);
Console.WriteLine("Hour:{0}", dob.Hour);
Console.WriteLine("Minute:{0}", dob.Minute);
Console.WriteLine("Second:{0}", dob.Second);
Console.WriteLine("Millisecond:{0}", dob.Millisecond);
 
Console.WriteLine("Day of Week:{0}", dob.DayOfWeek);
Console.WriteLine("Day of Year: {0}", dob.DayOfYear);
Console.WriteLine("Time of Day:{0}", dob.TimeOfDay);
Console.WriteLine("Tick:{0}", dob.Ticks);
Console.WriteLine("Kind:{0}", dob.Kind);
 
Listing 2
 
The output of Listing 2 looks like Figure 1.
 
DateTimeImg1.gif
Figure 1
 
Adding and Subtracting DateTime
 
DateTime structure provides methods to add and subtract date and time to and from a DateTime object. The TimeSpan structure plays a major role in addition and subtraction.
 
We can use Add and Subtract methods to add and subtract date and time from a DateTime object. First we create a TimeSpan with a date and/or time values and use Add and Subtract methods.
 
The code listed in Listing 3 adds and subtracts 30 days from today and displays the day on the console.
 
DateTime aDay = DateTime.Now;
TimeSpan aMonth = new System.TimeSpan(30, 0, 0, 0);
DateTime aDayAfterAMonth = aDay.Add(aMonth);
DateTime aDayBeforeAMonth = aDay.Subtract(aMonth);
Console.WriteLine("{0:dddd}", aDayAfterAMonth);
Console.WriteLine("{0:dddd}", aDayBeforeAMonth);
 
Listing 3
 
The DateTime structure has methods to add years, days, hours, minutes, seconds, milliseconds and ticks. The code listed in Listing 4 uses these Addxxx methods to add various components to a DateTime object.
 
// Add Years and Days
aDay.AddYears(2);          
aDay.AddDays(12);
// Add Hours, Minutes, Seconds, Milliseconds, and Ticks
aDay.AddHours(4.25);
aDay.AddMinutes(15);
aDay.AddSeconds(45);           
aDay.AddMilliseconds(200);
aDay.AddTicks(5000);  
 
Listing 4
 
The DateTime structure does not have similar Subtract methods. Only Subtract method is used to subtract the DateTime components. For example, if we need to subtract 12 days from a DateTime, we can create another DateTime object or a TimeSpan object with 12 days and subtract it from the DateTime. Alternatively, we can use a minus operator to subtract a DateTime or TimeSpan from a DateTime.
 
The code snippet in Listing 5 creates a DateTime object and subtracts another DateTime and a TimeSpan object. Code also shows how to subtract just days or hours or other components from a DateTime.
 
DateTime dob = new DateTime(2000, 10, 20, 12, 15, 45);
DateTime subDate = new DateTime(2000, 2, 6, 13, 5, 15);
 
// TimeSpan with 10 days, 2 hrs, 30 mins, 45 seconds, and 100 milliseconds
TimeSpan ts = new TimeSpan(10, 2, 30, 45, 100);
 
// Subtract a DateTime
TimeSpan diff1 = dob.Subtract(subDate);
Console.WriteLine(diff1.ToString());
 
// Subtract a TimeSpan
DateTime diff2 = dob.Subtract(ts);
Console.WriteLine(diff2.ToString());
 
// Subtract 10 Days
DateTime daysSubtracted = new DateTime(dob.Year, dob.Month, dob.Day - 10);
Console.WriteLine(daysSubtracted.ToString());
 
// Subtract hours, minutes, and seconds
DateTime hms = new DateTime(dob.Year, dob.Month, dob.Day, dob.Hour - 1, dob.Minute - 15, dob.Second - 15);
Console.WriteLine(hms.ToString());
Listing 5
 
Find Days in a Month
 
The DaysInMonth static method returns the number of days in a month. This method takes a year and a month in numbers from 1 to 12. The code snippet in Listing 6 gets the number of days in Feb month of year 2002. The output is 28 days.
 
int days = DateTime.DaysInMonth(2002, 2);
Console.WriteLine(days);
Listing 6
 
Using the same approach, we can find out total number of days in a year. The GetDaysInAYear method in Listing 7 takes a year and returns total number of days in that year.
 
private int GetDaysInAYear(int year)
{
    int days = 0;
    for (int i = 1; i <= 12; i++)
    {
        days += DateTime.DaysInMonth(year, i);
    }
    return days;
}
 
Listing 7
Compare Two DateTime
 
The Compare static method is used to compare two DateTime objects. If result is 0, both objects are the same. If the result is less than 0, then the first DateTime is earlier; otherwise the first DateTime is later.
 
The code snippet in Listing 8 compares two DateTime objects.
 
DateTime firstDate = new DateTime(2002, 10, 22);
DateTime secondDate = new DateTime(2009, 8, 11);
int result = DateTime.Compare(firstDate, secondDate);
 
if (result < 0)
    Console.WriteLine("First date is earlier");
else if (result == 0)
    Console.WriteLine("Both dates are same");
else
    Console.WriteLine("First date is later");
 
Listing 8
 
The CompareTo method can also be used to compare two dates. This method takes a DateTime or object. The code snippet in Listing 9 compares two DateTime objects using the CompareTo method.
 
DateTime firstDate = new DateTime(2002, 10, 22);
DateTime secondDate = new DateTime(2009, 8, 11);
int compareResult = firstDate.CompareTo(secondDate);
if (compareResult < 0)
    Console.WriteLine("First date is earlier");
else if (compareResult == 0)
    Console.WriteLine("Both dates are same");
else
    Console.WriteLine("First date is later");
 
Listing 9
Formatting DateTime
 
I have to admit; the folks at Microsoft have done a great job of providing DateTime formatting solutions. Now you can format a DateTime to any kind of string format you can imagine.
 
The GetDateTimeFormats method returns all possible DateTime formats for the current culture of a computer. The code snippet in Listing 10 returns an array of strings of all possible standard formats.
 
DateTime dob = new DateTime(2002, 10, 22);
string[] dateFormats = dob.GetDateTimeFormats();
foreach (string format in dateFormats)
    Console.WriteLine(format)
Listing 10
 
The code snippet in Listing 10 generates output as in Figure 2.
 
DateTimeImg2.gif
Figure 2
 
The GetDateTimeFormats method also has an overload that takes a format specifier as a parameter and converts a DateTime to that format. It is very important to understand the DateTime format specifiers to get the desired formats. Table 1 summarizes the formats and their codes.
 
Code
Pattern
Format
Sample
"d"
Sort date


"D"
Long date


"f"
Full date time. Short time.


"F"
Full date time. Long time.


"g"
Generate date time. Short time.


"G"
General date time. Long time.


"M", 'm"
Month/day.


"O", "o"
Round-trip date/time.


"R", "r"
RFC1123


"s"
Sortable date time.


"t"
Sort time.


"T"
Long time.


"u"
Universal sortable date time.


"U"
Universal full date time.


"Y", "y"
Year month.


 
Table 1
 
The code snippet in Listing 11 uses some of the DateTime format specifiers.
 
DateTime dob = new DateTime(2002, 10, 22);
// DateTime Formats: d, D, f, F, g, G, m, o, r, s, t, T, u, U,
Console.WriteLine("----------------");
Console.WriteLine("d Formats");
Console.WriteLine("----------------");
string[] dateFormats = dob.GetDateTimeFormats('d');
foreach (string format in dateFormats)
    Console.WriteLine(format);
Console.WriteLine("----------------");
Console.WriteLine("D Formats");
Console.WriteLine("----------------");
dateFormats = dob.GetDateTimeFormats('D');
foreach (string format in dateFormats)
    Console.WriteLine(format);
 
Console.WriteLine("----------------");
Console.WriteLine("f Formats");
Console.WriteLine("----------------");
dateFormats = dob.GetDateTimeFormats('f');
foreach (string format in dateFormats)
    Console.WriteLine(format);
 
Console.WriteLine("----------------");
Console.WriteLine("F Formats");
Console.WriteLine("----------------");
dateFormats = dob.GetDateTimeFormats('F');
foreach (string format in dateFormats)
    Console.WriteLine(format);
 
Listing 11
 
The code snippet in Listing 11 generates output as in Figure 3.
 
DateTimeImg3.gif
Figure 3
 
We can also format a DateTime by passing the format specifier in the ToString() method of DateTime. The code snippet in Listing 12 formats a DateTime using ToString() method. 
 
Console.WriteLine(dob.ToString("r"));
Listing 12
 
The code snippet in Listing 13 uses some of the DateTime format specifiers within the ToString() method to format a DateTime.
 
DateTime dob = new DateTime(2002, 10, 22);
Console.WriteLine("d: "+ dob.ToString("d"));
Console.WriteLine("D: "+ dob.ToString("D"));
Console.WriteLine("f: "+ dob.ToString("f"));
Console.WriteLine("F: "+ dob.ToString("F"));
Console.WriteLine("g: " + dob.ToString("g"));
Console.WriteLine("G: " + dob.ToString("G"));
Console.WriteLine("m: " + dob.ToString("m"));
Console.WriteLine("M: " + dob.ToString("M"));
Console.WriteLine("o: " + dob.ToString("o"));
Console.WriteLine("O: " + dob.ToString("O"));
Console.WriteLine("r: " + dob.ToString("r"));
Console.WriteLine("R: " + dob.ToString("R"));
Console.WriteLine("s: " + dob.ToString("s"));
Console.WriteLine("t: " + dob.ToString("t"));
Console.WriteLine("T: " + dob.ToString("T"));
Console.WriteLine("u: " + dob.ToString("u"));
Console.WriteLine("U: " + dob.ToString("U"));
Console.WriteLine("y: " + dob.ToString("y"));
Console.WriteLine("Y: " + dob.ToString("Y"));
 
Listing 13
 
The code snippet in Listing 13 generates output as in Figure 4.
 
DateTimeImg4.gif
Figure 4    
 
Leap Year and Daylight Saving Time
 
The IsDayLightSavingTime() and IsLeapYear() methods can be used to determine if a DateTime is DayLightSaving time and leap year respectively. The code snippet in Listing 14 shows how to use these methods.
 
DateTime dob = new DateTime(2002, 10, 22);
Console.WriteLine(dob.IsDaylightSavingTime());
Console.WriteLine(DateTime.IsLeapYear(dob.Year));
 
Listing 14
 
 
Converting String to DateTime
 
The Parse method is used to convert a string to a DateTime object. The string passed on the Parse method must have a correct DateTime format. Conversion from a DateTime to a string is done using the ToString() method.
 
The code snippet in Listing 15 converts a string to a DateTime.
 
string dt = "2010-10-04T20:12:45-5:00";
DateTime newDt = DateTime.Parse(dt);
Console.WriteLine(newDt.ToString());
 
Listing 15
 
Converting DateTime
 
The DateTime structure is full of self-explanatory conversion methods that convert a DateTime to a specified type.  These methods are ToBinary, ToFileTime, ToLocalTime, ToLongDateString, ToLongTimeString, ToOADate, ToShortDateString, ToShortTimeString, ToString, and ToUniversalTime.
 
The code snippet in Listing 16 uses some of these methods to convert a DateTime to specified types.
 
DateTime dob = new DateTime(2002, 10, 22);
Console.WriteLine("ToString: " + dob.ToString());
Console.WriteLine("ToBinary: " + dob.ToBinary());
Console.WriteLine("ToFileTime: " + dob.ToFileTime());
Console.WriteLine("ToLocalTime: " + dob.ToLocalTime());
Console.WriteLine("ToLongDateString: " + dob.ToLongDateString());
Console.WriteLine("ToLongTimeString: " + dob.ToLongTimeString());
Console.WriteLine("ToOADate: " + dob.ToOADate());
Console.WriteLine("ToShortDateString: " + dob.ToShortDateString());
Console.WriteLine("ToShortTimeString: " + dob.ToShortTimeString());
Console.WriteLine("ToUniversalTime: " + dob.ToUniversalTime());
 
Listing 16
 
The code snippet in Listing 11 generates output looks like Figure 5.
 
DateTimeImg5.gif