Using Case in a SQL Query

Using Case in Update Statement
UPDATE dbo.Customer SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts' WHEN statecode = 'VA' THEN 'Virginia' WHEN statecode = 'PA' THEN 'Pennsylvania' ELSE NULL END
 
Using Case in Select Statement
SELECT COUNT(*) AS TotalCustomers, 
SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers, 
AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales 
FROM dbo.Customer 

Using Case in Stored Procedure

CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4) AS
SET nocount ON

SELECT customerid, firstname, lastname, statecode, statedescription, totalsales FROM dbo.Customer ORDER BY  CASE @sortdirection
     WHEN 'asc' THEN
      CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END 
ASC, CASE @sortdirection
      WHEN 'desc' THEN
       CASE @sortby 
       WHEN 'firstname' THEN firstname 
       WHEN 'lastname' THEN lastname 
       END
END
DESC GO EXEC dbo.getCustomerData 'lastname', 'desc' 

Generating BarCode Using C-Sharp

Following are the methods to generate the barcode using C-Sharp...

private void btn_GenerateBarCode_Click(object sender, EventArgs e)

{

     GenerateBarCode();

}

private void GenerateBarCode()

{

    int barSize = System.Convert.ToInt32(39);

System.Byte[] imgBarcode = Code39("*" + txtBarCodeText.Text + "*", barSize, false, "");

    MemoryStream memStream = new MemoryStream(imgBarcode);

    PictureBox_BarCodeResult.Image = new Bitmap(memStream);

    //statbar.Panels[0].Text = "Done.";

    this.Cursor = Cursors.Default;

}

// This Method is used in above method

public byte[] Code39(string code, int barSize, bool showCodeString, string title)

{

    MyBarCodeClass c39 = new MyBarCodeClass();

    string appPath = Path.GetDirectoryName(Application.ExecutablePath);

    // Create stream....

    MemoryStream ms = new MemoryStream();

    c39.FontFamilyName = "Free 3 of 9";

    c39.FontFileName = appPath + "\\FREE3OF9.TTF";

    c39.FontSize = barSize;

    c39.ShowCodeString = showCodeString;

    if (title + "" != "")

        c39.Title = title;

    Bitmap objBitmap = c39.GenerateBarcode(code);

    objBitmap.Save(ms, ImageFormat.Png);

    //return bytes....

    return ms.GetBuffer();

}

 
Bellow is the Class used in above Method

public class MyBarCodeClass

{

private const int _itemSepHeight = 3;

SizeF _titleSize = SizeF.Empty;

SizeF _barCodeSize = SizeF.Empty;

SizeF _codeStringSize = SizeF.Empty;

#region Barcode Title

private string _titleString = null;

private Font _titleFont = null;

public string Title

{

    get { return _titleString; }

    set { _titleString = value; }

}

public Font TitleFont

{

    get { return _titleFont; }

    set { _titleFont = value; }

}

#endregion

#region Barcode code string

private bool _showCodeString = false;

private Font _codeStringFont = null;

public bool ShowCodeString

{

    get { return _showCodeString; }

    set { _showCodeString = value; }

}

public Font CodeStringFont

{

    get { return _codeStringFont; }

    set { _codeStringFont = value; }

}

#endregion

#region Barcode Font

private Font _c39Font = null;

private float _c39FontSize = 12;

private string _c39FontFileName = null;

private string _c39FontFamilyName = null;

public string FontFileName

{

    get { return _c39FontFileName; }

    set { _c39FontFileName = value; }

}

public string FontFamilyName

{

    get { return _c39FontFamilyName; }

    set { _c39FontFamilyName = value; }

}

public float FontSize

{

    get { return _c39FontSize; }

    set { _c39FontSize = value; }

}

private Font Code39Font

{

    get

    {

        if (_c39Font == null)

        {

            // Load the barcode font                 

            PrivateFontCollection pfc = new PrivateFontCollection();

            pfc.AddFontFile(_c39FontFileName);

            FontFamily family = new FontFamily(_c39FontFamilyName, pfc);

            _c39Font = new Font(family, _c39FontSize);

        }

        return _c39Font;

    }

}

#endregion

public MyBarCodeClass()

{

    _titleFont = new Font("Arial", 10);

    _codeStringFont = new Font("Arial", 10);

}

#region Barcode Generation

public Bitmap GenerateBarcode(string barCode)

{

    int bcodeWidth = 0;

    int bcodeHeight = 0;

    // Get the image container...

    Bitmap bcodeBitmap = CreateImageContainer(barCode, ref bcodeWidth, ref bcodeHeight);

    Graphics objGraphics = Graphics.FromImage(bcodeBitmap);

    // Fill the background               

    objGraphics.FillRectangle(new SolidBrush(Color.White), new Rectangle(0, 0, bcodeWidth, bcodeHeight));

    int vpos = 0;

    // Draw the title string

    if (_titleString != null)

    {

        objGraphics.DrawString(_titleString, _titleFont, new SolidBrush(Color.Black), XCentered((int)_titleSize.Width, bcodeWidth), vpos);

        vpos += (((int)_titleSize.Height) + _itemSepHeight);

    }

    // Draw the barcode

    objGraphics.DrawString(barCode, Code39Font, new SolidBrush(Color.Black), XCentered((int)_barCodeSize.Width, bcodeWidth), vpos);

    // Draw the barcode string

    if (_showCodeString)

    {

        vpos += (((int)_barCodeSize.Height));

        objGraphics.DrawString(barCode, _codeStringFont, new SolidBrush(Color.Black), XCentered((int)_codeStringSize.Width, bcodeWidth), vpos);

    }

    // return the image...                                                   

    return bcodeBitmap;

}

private Bitmap CreateImageContainer(string barCode, ref int bcodeWidth, ref int bcodeHeight)

{

    Graphics objGraphics;

    // Create a temporary bitmap...

    Bitmap tmpBitmap = new Bitmap(1, 1, PixelFormat.Format32bppArgb);

    objGraphics = Graphics.FromImage(tmpBitmap);

    // calculate size of the barcode items...

    if (_titleString != null)

    {

        _titleSize = objGraphics.MeasureString(_titleString, _titleFont);

        bcodeWidth = (int)_titleSize.Width;

        bcodeHeight = (int)_titleSize.Height + _itemSepHeight;

    }

    _barCodeSize = objGraphics.MeasureString(barCode, Code39Font);

    bcodeWidth = Max(bcodeWidth, (int)_barCodeSize.Width);

    bcodeHeight += (int)_barCodeSize.Height;

    if (_showCodeString)

    {

        _codeStringSize = objGraphics.MeasureString(barCode, _codeStringFont);

        bcodeWidth = Max(bcodeWidth, (int)_codeStringSize.Width);

        bcodeHeight += (_itemSepHeight + (int)_codeStringSize.Height);

    }

    // dispose temporary objects...

    objGraphics.Dispose();

    tmpBitmap.Dispose();

    return (new Bitmap(bcodeWidth, bcodeHeight, PixelFormat.Format32bppArgb));

}

#endregion

#region Auxiliary Methods

private int Max(int v1, int v2)

{

    return (v1 > v2 ? v1 : v2);

}

private int XCentered(int localWidth, int globalWidth)

{

    return ((globalWidth - localWidth) / 2);

}

#endregion

}

 

Selecting Record to using cursor in stored procedure

Following is an example using cursor to fetch record from table and performing action on each row using while loop, also used try catch in this stored procedure

ALTER PROCEDURE [dbo].[Test_Procedure_By_Khalid]

(

      -- Add the parameters for the stored procedure here

      @TableName nvarchar(255),

      @AreaName varchar(50),

      @SysDate DateTime,

      @ReturnCode INT   OUTPUT

)

AS

     

      DECLARE @GraphID NVARCHAR(50)

      DECLARE @PERIOD INT

      DECLARE @MODELKEY NVARCHAR(50)

      DECLARE @ORIGNALDATE NVARCHAR(50)

      DECLARE @DATE INT

      DECLARE @QTY INT

      DECLARE @TOTALQTY INT

      DECLARE @MODELKEYTYPE INT

      DECLARE @SCREENID NVARCHAR(50)

      DECLARE @RetailModelClassificationName NVARCHAR(50)

      DECLARE @SELECTEDAREA varchar(50)

      DECLARE @INF_Cursor INT

      DECLARE @THEMONTH NVARCHAR(6)

      DECLARE @LASTMONTH NVARCHAR(6)

      DECLARE @LASTYEARMONTH NVARCHAR(6)

      --Cursor Definition

      DECLARE     @Cur_TableParm    CURSOR

     

BEGIN TRY

      ----------------------------

      -- Start Transaction --

      ----------------------------

      BEGIN TRANSACTION

-- Set Cursor to select data from Table CPT_TableParm

      SET @Cur_TableParm = CURSOR FAST_FORWARD FOR

SELECT GraphID,ModelKey,AreaName,ModelClassificationName,ScreenID FROM CPT_TableParm

      WHERE TableName = @TableName

      AND ActFcType = 1

     

      -- Executing Cursor to select data from Table CPT_TableParm

      OPEN  @Cur_TableParm

      FETCH NEXT FROM @Cur_TableParm  INTO

             @GraphID, @MODELKEY,@SELECTEDAREA,

 @RetailModelClassificationName,@SCREENID

     

      -- Looping through cursor result   

      WHILE  @@FETCH_STATUS = 0

        BEGIN

           

Print  @SELECTEDAREA+','+cast(@PERIOD as varchar)+'~'+@MODELKEY+'~'+@ORIGNALDATE+'~'+cast(@DATE as varchar)+'~'+cast(@QTY as varchar)+'~'+

                                          cast(@TOTALQTY as varchar)+'~'+@GraphID+'~''3''~'+@SCREENID

                              SET @ReturnCode = 2

        FETCH NEXT FROM @Cur_TableParm  INTO

                                    @GraphID, @MODELKEY,@SELECTEDAREA,

@RetailModelClassificationName,@SCREENID

       END

      -- Closing @Cur_TableParm to select data from CPT_TableParm

      CLOSE       @Cur_TableParm

      DEALLOCATE  @Cur_TableParm

END TRY

BEGIN CATCH

-- Rollback Transaction --

      ROLLBACK TRANSACTION

      -- Reset Cursor State

      BEGIN

            SET @INF_Cursor = CURSOR_STATUS('global','@Cur_TableParm')

            IF @INF_Cursor = -1

            BEGIN

                  DEALLOCATE  @Cur_TableParm    -- Delete Cursor

            END

            ELSE IF @INF_Cursor <> -3

                  BEGIN

                        CLOSE       @Cur_TableParm    -- Close Cursor

                        DEALLOCATE  @Cur_TableParm    -- Delete Cursor

                  END

            PRINT       ERROR_MESSAGE()

      END

      RETURN 999

END CATCH

Very Simple Data Access Layer for Three Tire Application

Below code is for very simple Data Access Layer that can be use in three tier application, it also includes the Method used in with Transaction

using System;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

namespace DataAccessLayer

{

    public static class DAcess

    {

        static string constr = "";

        static SqlCommand sqlcom = new SqlCommand();

        static SqlConnection sqlcon = new SqlConnection();

        static SqlDataAdapter sqlda;

        static SqlTransaction SqlTran;

       

        public static void ConnectToDatabase()

        {

            if (sqlcon.State == ConnectionState.Closed)

            {

                constr = ConfigurationManager.ConnectionStrings["ZaaSConString"].ToString();

                sqlcon = new SqlConnection(constr);

                sqlcon.Open();

            }

        }

        public static void DisconnectToDataBase()

        {

            if(sqlcon.State == ConnectionState.Open)

            sqlcon.Close();

        }

        public static DataTable returnDataTable(string Query)

        {

            DataTable dtResult = new DataTable("ResultTable");

            try

            {

                ConnectToDatabase();

                sqlda = new SqlDataAdapter(Query, sqlcon);

                sqlda.Fill(dtResult);

            }

            catch (Exception ex)

            {

            }

            finally

            {

            }

            return dtResult;

        }

        public static DataTable returnDataTableSpecial(string Query)

        {

            DataTable dtResult = new DataTable("ResultTable");

            try

            {

                ConnectToDatabase();

                sqlcom = new SqlCommand(Query, sqlcon, SqlTran);

                sqlda = new SqlDataAdapter(sqlcom);

                sqlda.Fill(dtResult);

            }

            catch (Exception ex)

            {

            }

            finally

            {

            }

            return dtResult;

        }

        public static int ExecuteQuery(string Query)

        {

            int Result = 0;

            try

            {

                ConnectToDatabase();

                sqlcom = new SqlCommand(Query, sqlcon);

                Result = sqlcom.ExecuteNonQuery();

                sqlcon.Close();

            }

            catch (Exception ex)

            {

                sqlcon.Close();

                throw ex;

            }

            finally

            {

                sqlcon.Close();

            }

           

            return Result;

        }

        public static int NewExecuteQuery(string Query)

        {

            int Result = 0;

            sqlcom = new SqlCommand(Query, sqlcon, SqlTran);

            Result = sqlcom.ExecuteNonQuery();

            return Result;

        }

        public static void StartTransaction()

        {

            SqlTran = sqlcon.BeginTransaction(IsolationLevel.ReadUncommitted);

        }

        public static void CommitTransaction()

        {

            SqlTran.Commit();

        }

        public static void RollBackTransaction()

        {

            SqlTran.Rollback();

        }

        public static void CloseTransaction()

        {

            SqlTran.Dispose();

        }

       

       

        public static string returnScalar(string Query)

        {

            string ResultString = "";

            try

            {

                ConnectToDatabase();

                sqlcom = new SqlCommand(Query, sqlcon);

                ResultString = Convert.ToString(sqlcom.ExecuteScalar());

                sqlcon.Close();

            }

            catch (Exception ex)

            {

                sqlcon.Close();

                throw ex;

            }

            finally

            {

                sqlcon.Close();

            }

            return ResultString;

        }

        public static string NewreturnScalar(string Query)

        {

            string ResultString = "";

            sqlcom = new SqlCommand(Query, sqlcon,SqlTran);

            ResultString = Convert.ToString(sqlcom.ExecuteScalar());

            return ResultString;

        }

    }

}