Friday 17 October 2014

How to use multiple aggregate functions in a single query(without sub query) in oracle/sql server


Here i am using two count functions with different conditions in a single query.


select count(case when department_id = 10 and salary<5000 then 1 end), 
          count(case when department_id = 20 then 1 end) 
from employees;

Output:





If we want the output as the two results in same column,


select count(EMPLOYEE_ID) from employees where DEPARTMENT_ID=30 and salary<5000
union all

select count(EMPLOYEE_ID) from EMPLOYEES where DEPARTMENT_ID=50;

Output:








References: http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query.


Five things to remember to improve the stored procedure performance while create stored procedure in SQL Server

Steps to improve the performance  of Stored procedure in SQL Server

 1) Set NOCOUNT ON;
 2) Don't use input parameters directly. Assign them to Locally  declared variables in stored procedure.
 3) Don't use Count(*) use Count(pk_Id)
 4) Use Exists instead of IN in where condition
 5) Use distinct instead of Group by clause in all possible cases

Example stored procedure
 Here my using group by clause to full file my requirement. Try to avoid the using of group by clause

ALTER PROCEDURE [dbo].[your sp name here] 
@warehouseId char(4)
AS
BEGIN
    SET NOCOUNT ON;
    Declare @Local_warehouseId char(4)=@warehouseId
   
    select oh.OrderNum,case when od.picked is null then 0 else od.picked end as picked,oh.numLines,mem.firstName,mem.lastName,mem.phone,mem.Suburb,oh.memberid as memberID,oh.PickUpPointTimeSlot as EstPickUpTime  from OrderHeader oh join 
members mem on mem.memberID=oh.memberid
join PickupPointTimes pPoint on pPoint.PickupPointID=oh.PickupPointID and oh.PickUpPointTimeSlot=pPoint.TimeSlot and pPoint.PickUpDay=oh.pickupDay
Left outer join (select OrderNum,COUNT(LineItemID) as picked from OrderDetails where PickedQTY is not null group by OrderNum )  as od  on od.OrderNum=oh.OrderNum 
where exists (select * from PickupPoints pp where pp.PickupPointID=oh.PickupPointID and WarehouseID=@Local_warehouseId) and oh.status='RP'
order by pPoint.SortCode,oh.numLines
 
END

Thursday 9 October 2014

Label and Barcode printing using ZPL printer from C#.Net


Printing label and Barcodes using ZPL printer from C#.Net

  Here I am printing Label using PrintLabel Method and printing Barcode  using PrintBarcode method. We can use this code windows,web or any other applications which are using code behide as C#.Net. It's works if ZPL printer is connected via parallel port or via Bluetooth to your system.

Here I am passing ZPL language command  to zpl printer (zpl printer supports EPL and ZPL commands).

Note:Code is written according to my requirements. You can change it according to your requirements. If anyone needs help then you can shoot an email to hareesh434@gmail.com with subject as Share your doubts and answers with IT world. If I can i will help you.

C#.Net  Code

Below class  is for passing the ZPL command to printer

  public class UpcLabel
    {

        private string strFirstName;
        private string strLastName;
        private string strNoOfCopies;
        private string PickUpTime;
        private string Cold;
        private string NewMember;
        public UpcLabel()
        {
        }
        public UpcLabel(string strFirstName, string strLastName, string strNoOfCopies, string PickUpTime, string Cold, string NewMember)
        {
            if (strFirstName == null || strLastName == null || strNoOfCopies == "" || strNoOfCopies=="0")
            {
                throw new ArgumentNullException("strFirstName");
            }

            this.strFirstName = strFirstName;
            this.strLastName = strLastName;
            this.strNoOfCopies = strNoOfCopies;
            this.PickUpTime = PickUpTime;
            this.Cold = Cold;
            this.NewMember = NewMember;
        }
        public void PrintBarcode(string printerName, string pProductName, string pBarcode,string strNumOfCopies)
        {
            if (printerName == null)
            {
                throw new ArgumentNullException("printerName");
            }
            StringBuilder strBldr = new StringBuilder();
            strBldr.AppendLine("^XA");

            strBldr.AppendLine("^FO40,100");
            strBldr.AppendLine("^AQ,50,30");
            // sb1.AppendLine("^FDAnja^FS");
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", pProductName));
            //^FO100,100^BY3
            strBldr.AppendLine("^FO80,200^BY3");
            //^BCN,150,Y,N,Y,N
            strBldr.AppendLine("^BCN,125,Y,N,Y,N");
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", pBarcode));
            strBldr.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", strNumOfCopies));
            strBldr.AppendLine("^XZ");
            RawPrinterHelper.SendStringToPrinter(printerName, strBldr.ToString());



        }
        public void Print(string printerName)
        {
            if (printerName == null)
            {
                throw new ArgumentNullException("printerName");
            }
            StringBuilder sb1=new StringBuilder();
            //^XA=Indicates Starting of Zpl
                sb1.AppendLine("^XA");
                sb1.AppendLine("^LL350");//^FS
                sb1.AppendLine("^PW930");//^FS
                sb1.AppendLine("^FO10,10");
                sb1.AppendLine("^AQ,80,80");
                // sb1.AppendLine("^FDAnja^FS");

                //FOa,b
                //a=Postion from x-axis
                //b=Position from y-axis
                //Aa,b,c
                //a=Font size Like Q,V,R,0
                //b=Font width
                //c=Font Height
             
                sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.strFirstName));
                sb1.AppendLine("^FO10,68");
                sb1.AppendLine("^AQ,80,80");
                sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.strLastName));
       
            sb1.AppendLine("^FO10,150");
            sb1.AppendLine("^AQ,50,50");
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.PickUpTime));

            sb1.AppendLine("^FO240,150");
            sb1.AppendLine("^AQ,50,50");
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture,"^FD{0}^FS",this.Cold));
            //^PQ2= Indicates number of copies to print
            sb1.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", this.strNoOfCopies));
            //sb1.AppendLine("^PQ2");
            //^XZ=Indicates ending of ZPL page
            sb1.AppendLine("^XZ");
            RawPrinterHelper.SendStringToPrinter(printerName, sb1.ToString());
            //for (int counter = 0; counter <Convert.ToInt32(this.strNoOfCopies); counter++)
            //{
            //    RawPrinterHelper.SendStringToPrinter(printerName, sb1.ToString());
            //    System.Threading.Thread.Sleep(500);
            //}
            if (this.NewMember != "")
            {
                StringBuilder strb= new StringBuilder();
                strb.AppendLine("^XA");
                strb.AppendLine("^LL350");//^FS
                strb.AppendLine("^PW930");//^FS
                strb.AppendLine("^FO20,30");
                strb.AppendLine("^AQ,80,80");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.NewMember));
                strb.AppendLine("^FO20,150");
                strb.AppendLine("^AQ,50,50");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.PickUpTime));
             
                strb.AppendLine("^FO240,150");
                strb.AppendLine("^AQ,50,50");
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^FD{0}^FS", this.Cold));
                //^PQ2= Indicates number of copies to print
                strb.AppendLine(string.Format(CultureInfo.InvariantCulture, "^PQ{0}", "1"));
                //sb1.AppendLine("^PQ2");
                //^XZ=Indicates ending of ZPL page
                strb.AppendLine("^XZ");

                RawPrinterHelper.SendStringToPrinter(printerName, strb.ToString());
            }
        }
    }
For Printing



public class RawPrinterHelper
    {
        // Structure and API declarions:
        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi)]
        public class DOCINFOA
        {
            [MarshalAs(UnmanagedType.LPStr)]
            public string pDocName;
            [MarshalAs(UnmanagedType.LPStr)]
            public string pOutputFile;
            [MarshalAs(UnmanagedType.LPStr)]
            public string pDataType;
        }
        [DllImport("winspool.Drv", EntryPoint = "OpenPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool OpenPrinter([MarshalAs(UnmanagedType.LPStr)] string szPrinter, out IntPtr hPrinter, IntPtr pd);

        [DllImport("winspool.Drv", EntryPoint = "ClosePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool ClosePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "StartDocPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool StartDocPrinter(IntPtr hPrinter, Int32 level, [In, MarshalAs(UnmanagedType.LPStruct)] DOCINFOA di);

        [DllImport("winspool.Drv", EntryPoint = "EndDocPrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool EndDocPrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "StartPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool StartPagePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "EndPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool EndPagePrinter(IntPtr hPrinter);

        [DllImport("winspool.Drv", EntryPoint = "WritePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
        public static extern bool WritePrinter(IntPtr hPrinter, IntPtr pBytes, Int32 dwCount, out Int32 dwWritten);

        // SendBytesToPrinter()
        // When the function is given a printer name and an unmanaged array
        // of bytes, the function sends those bytes to the print queue.
        // Returns true on success, false on failure.
        public static bool SendBytesToPrinter(string szPrinterName, IntPtr pBytes, Int32 dwCount)
        {
            Int32 dwError = 0, dwWritten = 0;
            IntPtr hPrinter = new IntPtr(0);
            DOCINFOA di = new DOCINFOA();
            bool bSuccess = false; // Assume failure unless you specifically succeed.

            di.pDocName = "My C#.NET RAW Document";
            di.pDataType = "RAW";

            // Open the printer.
            if (OpenPrinter(szPrinterName.Normalize(), out hPrinter, IntPtr.Zero))
            {
                // Start a document.
                if (StartDocPrinter(hPrinter, 1, di))
                {
                    // Start a page.
                    if (StartPagePrinter(hPrinter))
                    {
                        // Write your bytes.
                        bSuccess = WritePrinter(hPrinter, pBytes, dwCount, out dwWritten);
                        EndPagePrinter(hPrinter);
                    }
                    EndDocPrinter(hPrinter);
                }
                ClosePrinter(hPrinter);
            }
            // If you did not succeed, GetLastError may give more information
            // about why not.
            if (bSuccess == false)
            {
                dwError = Marshal.GetLastWin32Error();
            }
            return bSuccess;
        }

        public static bool SendFileToPrinter(string szPrinterName, string szFileName)
        {
            // Open the file.
            FileStream fs = new FileStream(szFileName, FileMode.Open);
            // Create a BinaryReader on the file.
            BinaryReader br = new BinaryReader(fs);
            // Dim an array of bytes big enough to hold the file's contents.
            Byte[] bytes = new Byte[fs.Length];
            bool bSuccess = false;
            // Your unmanaged pointer.
            IntPtr pUnmanagedBytes = new IntPtr(0);
            int nLength;

            nLength = Convert.ToInt32(fs.Length);
            // Read the contents of the file into the array.
            bytes = br.ReadBytes(nLength);
            // Allocate some unmanaged memory for those bytes.
            pUnmanagedBytes = Marshal.AllocCoTaskMem(nLength);
            // Copy the managed byte array into the unmanaged array.
            Marshal.Copy(bytes, 0, pUnmanagedBytes, nLength);
            // Send the unmanaged bytes to the printer.
            bSuccess = SendBytesToPrinter(szPrinterName, pUnmanagedBytes, nLength);
            // Free the unmanaged memory that you allocated earlier.
            Marshal.FreeCoTaskMem(pUnmanagedBytes);
            return bSuccess;
        }
        public static bool SendStringToPrinter(string szPrinterName, string szString)
        {
            IntPtr pBytes;
            Int32 dwCount;
            // How many characters are in the string?
            dwCount = (szString.Length + 1) * Marshal.SystemMaxDBCSCharSize;//szString.Length;
            // Assume that the printer is expecting ANSI text, and then convert
            // the string to ANSI text.
            pBytes = Marshal.StringToCoTaskMemAnsi(szString);
            // Send the converted ANSI string to the printer.
            SendBytesToPrinter(szPrinterName, pBytes, dwCount);
            Marshal.FreeCoTaskMem(pBytes);
            return true;
        }
    }
Call the above functionality using below code:

public void PrintLabel(string strFirstName, string strLastName, string strNoOfCopies, string PickUpTime, string Cold,string NewMember="")
        {
           
            UpcLabel lbl = new UpcLabel(strFirstName, strLastName, strNoOfCopies, PickUpTime, Cold, NewMember);
            //Printer name
            lbl.Print("ZDesigner QLn220 (ZPL)");
         
        }
        public void PrintBarcode(string pProductName,string pLocation,string pNoOfCopies)
        {
            UpcLabel upcLabel = new UpcLabel();
            //Printer name
            upcLabel.PrintBarcode("ZDesigner QLn220 (ZPL)", pProductName, pLocation, pNoOfCopies);
       
        }






Wednesday 1 October 2014

Access html tag in c# code behind and Set value to html tag from c# code behind

Accessing html tag data or value from c# code behind

Here is my Asp.Net page 


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="TestingHtmlControl.aspx.cs" Inherits="WebApplication2.TestingHtmlControl" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <select name="sltTest" id="slt">
        <option value="test1">test1</option>
        <option value="test2">test2</option>
        <option value="test3">test3</option>
        <option value="test4">test4</option>
    </select>
    </div>
        <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
    </form>
</body>
</html>

Now I am going to access my html select tag selected value from C#.Net code behind  

C#.Net Code

//Submit button click event
 protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string seletedDropDownValueOfHtmlSelect = "";
            seletedDropDownValueOfHtmlSelect = Request.Form["sltTest"].ToString();

//Assigning value to html select tag from C#.Net code behind
            var script = "document.getElementById('slt').value = 'test3';";
            ClientScript.RegisterStartupScript(typeof(string), "textvaluesetter", script, true);

            Response.Write("Name : " + seletedDropDownValueOfHtmlSelect);

        }