Sample UPDATE Trigger in SQL Server

Thursday, November 22, 2012

This is a simple Trigger on the table named -BranchAllotmentDetail , this trigger was fired when the column named ' IsReAllocated ' -is updated.
We get access the values of same Row where the trigger is fired,. here BranchID BranchAllotID .

alter TRIGGER trgAfterUpdateBranchAllotmentDetail ON [dbo].[BranchAllotmentDetail]
FOR UPDATE
AS
declare @BranchID int;
declare @BranchAllotID  int;


select @BranchID=i.BranchID from inserted i;
select @BranchAllotID=i.BranchAllotID from inserted i;
--select @empsal=i.Emp_Sal from inserted i;

if update(IsReAllocated)
begin
update  BranchAllotmentDetail set IsActiveSeries=1
where BranchID=@BranchID
and BranchAllotID=@BranchAllotID

end

GO

Transaction in SQL Server

Tuesday, October 9, 2012

This is one of the sample procedure ::

ALTER procedure [dbo].[insertInto]
(
@a varchar(16)=null
)
as
begin
declare @c int=0,@d int=0
begin tran
select * from aaa                           -- aaa is the table name
print @@TRANCOUNT             -- 1

begin tran
-- set @c=@@TRANCOUNT
 -- print @c
  print @@TRANCOUNT       -- 2    
          insert Into aaa values (@a)   
   
  
  Select @d = @@ERROR
  print @d
       IF (@d <> 0) GOTO PROBLEM
   --    rollback tran
   --else
   --     Commit tran    
commit tran
print @@TRANCOUNT
commit tran
print @@TRANCOUNT

PROBLEM:
if(@d <> 0)
begin
  print 'ERROR !!'
   rollback tran
--commit tran
end
end

Find a Integer from a nvarchar(max) / string in SQL

Wednesday, July 25, 2012

Find a Integer from a nvarchar (string) fild ::
In this procedure firstly i create a table ( containing Int value) , from a comma delimited string(nvarchar).
Here you can use like function but there is a problem , suppose according to bellow procedure you are find
"id" whose value is "56". and the string(nvarchar) value is "2,45,212,156,178".
here if you use " like " operator, following select statement reture true as because of 156 in that string(nvarchar) .
For avoiding this problem, i solved that issue by the following SP & Function :


ALTER procedure [dbo].[AbhisHiFi] --'amrita_agp'
(@uNAME nvarchar(20))
as
begin
-- for get the string 
declare @a nvarchar(max)=(select menu_list1 from menu_permit where UserName=@uNAME)

select * from  menu where status=1 and id not in (465) and Url is null
union
-- find "id" within that string(nvarchar) ,here that nvarchar was modified to a table by 
-- a user define function
select * from  menu where status=1 and id in (SELECT ID FROM [dbo].[Abhi_StringToIntList](@a,',')) order by pos

end

AND The  Function is as follows :


ALTER FUNCTION [dbo].[Abhi_StringToIntList]
(@str VARCHAR (MAX), @delimeter CHAR (1))
RETURNS 
    @result TABLE (
        [ID] INT NULL)
AS
BEGIN
-- here the function is return a table , and the table is created through XML
    DECLARE @x XML 
    SET @x = '<t>' + REPLACE(@str, @delimeter, '</t><t>') + '</t>'

    INSERT INTO @result
    SELECT DISTINCT x.i.value('.', 'int') AS token
    FROM @x.nodes('//t') x(i)
    ORDER BY 1

RETURN
END

Delete or Filter Duplicate Item From ArrayList

Avoid duplicate item i use a function , that will return a ArrayList also.
And the Function is like :

protected ArrayList RemoveDuplicate(ArrayList ArrList) 
    { 
        ArrayList list = new ArrayList();
        foreach (string item in ArrList)
        {
            if (!list.Contains(item))
            {
                list.Add(item);
            }
        }
        return list; 
    }

---this will return a filtred ArrayList.

Delete Duplicate rows of a DataTable by C#

Thursday, July 19, 2012

Some time when you bind gridview ,you can find some duplicate rows in that grid, by this underlying piece of code you can easily avoid (or delete ) duplicate rows to bind  with that gridview :

// This method is used to delete duplicate rows of table
    public DataTable RemoveDuplicateRows(DataTable dTable, string colName)
    {
        Hashtable hTable = new Hashtable();
        ArrayList duplicateList = new ArrayList();
        foreach (DataRow dtRow in dTable.Rows)
        {
            if (hTable.Contains(dtRow[colName]))
                duplicateList.Add(dtRow);
            else
                hTable.Add(dtRow[colName], string.Empty);
        }
        foreach (DataRow dtRow in duplicateList)
            dTable.Rows.Remove(dtRow);
        return dTable;
    }

Set Color from C# in ASP.NET

Wednesday, July 18, 2012

You have to import namespace System.Drawing.
And then use the following piece of code to change color from code behind :

// For Html color code
grdv.BackColor = ColorTranslator.FromHtml("#f4f4f4");

// for C# color
grdv.ForeColor = System.Drawing.Color.Black;

Set page header for every content page from Master Page

Tuesday, July 17, 2012
Suppose you have a div with a lebel in the master page, and dynamically assign text for that label (as header)  from the content page's title.
It is one of the simple way for set header, here  'PgTitle' is the label id. and the code is like :
    
    this.PgTitle.Text = Page.Title;

-- that should be written in page load of master page

Register a Control with ScriptManager of MasterPage from Content Page

Monday, July 16, 2012
By the following pice of code you can register a control with scriptmanager , that was in the master page, from content page ::

ScriptManager.GetCurrent(this.Page).RegisterPostBackControl(btnSetCourier);


---- this is for avoiding parsing problem with Update panel.

DateTime Formating Issue In ASP.NET

Tuesday, July 10, 2012
Convert between 'dd/MM/yyyy' & 'MM/dd/yyyy'
You have to initialized  the Cultureinfo under the globalization namespace.

1)  For Convert to "MM/dd/yyyy" :

 IFormatProvider cl = new System.Globalization.CultureInfo("en-GB", true);

DateTime dts = new DateTime();
 dts = Convert.ToDateTime(t1.Text, cl);

2) For Convert to "dd/MM/yyyy" :

 IFormatProvider cl = new System.Globalization.CultureInfo("en-US", true);
DateTime dts = new DateTime();
 dts = Convert.ToDateTime(t1.Text, cl);

Format DateTime in the Label of a GridView Control

Monday, July 9, 2012

Use the following Format for the Label of the GridView ::

<asp:Label ID="lblApplDated" runat="server" Text='<%# Eval("APPLDATE", "{0:dd/MM/yyyy}")%>'></asp:Label>
                                                             

Generate PDF Formatted Report From DataTable

Here I am using Itextsharp.dll, please download and add reference to the project.And then --

This is the class file ::

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using iTextSharp.text;
using iTextSharp.text.pdf;

/// <summary>
///  PDFExporter:  Created by Abhirup Sinha Roy,help to generate Customise PDF (used for report purpose)
/// </summary>
public class PDFExporter
{
    private readonly DataTable dataTable;
    private readonly string fileName;
    private readonly bool timeStamp;

    public PDFExporter(DataTable dataTable, string fileName, bool timeStamp)
    {
        this.dataTable = dataTable;
        this.fileName = timeStamp ? String.Format("{0}-{1}", fileName, GetTimeStamp(DateTime.Now)) : fileName;
        this.timeStamp = timeStamp;
    }

    public void ExportPDF()
    {
        HttpResponse Response = HttpContext.Current.Response;
        Response.Clear();
        Response.ContentType = "application/pdf";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");
       
        // step 1: creation of a document-object
        Document document = new Document(PageSize.A4, 10, 10, 90, 10);

        // step 2: we create a writer that listens to the document
        PdfWriter writer = PdfWriter.GetInstance(document, Response.OutputStream);

        //set some header stuff
        document.AddTitle(fileName);
        document.AddSubject(String.Format("Table of {0}", fileName));
        document.AddCreator("www.i-novaonline.com");
        document.AddAuthor("KLJ");

        // step 3: we open the document
        document.Open();

        // step 4: we add content to the document
        CreatePages(document);

        // step 5: we close the document
        document.Close();    
    }

    public void ExportPDF1()
    {
        HttpResponse Response = HttpContext.Current.Response;
        Response.Clear();
        Response.ContentType = "application/pdf";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");

        // step 1: creation of a document-object
        Document document = new Document(PageSize.A4, 10f, 10f, 90f, 10f);

        // step 2: we create a writer that listens to the document
        PdfWriter writer = PdfWriter.GetInstance(document, Response.OutputStream);

        //set some header stuff
        document.AddTitle(fileName);
        document.AddSubject(String.Format("Table of {0}", fileName));
        document.AddCreator("www.i-novaonline.com");
        document.AddAuthor("KLJ");

        // step 3: we open the document
        document.Open();

        // step 4: we add content to the document
        CreatePages(document);

        // step 5: we close the document
        document.Close();

    }

    private void CreatePages(Document document)
    {
        document.NewPage();
        document.Add(FormatPageHeaderPhrase(dataTable.TableName));
        PdfPTable pdfTable = new PdfPTable(dataTable.Columns.Count);
        pdfTable.DefaultCell.Padding = 3;
        pdfTable.WidthPercentage = 100; // percentage
        pdfTable.DefaultCell.BorderWidth = 2;
        pdfTable.DefaultCell.HorizontalAlignment = Element.ALIGN_CENTER;

        //PdfPCell cell = new PdfPCell(new Phrase(": List of Certificate For  Courier :"));
        //cell.Colspan = 9;
        //cell.Border = 1;
        ////cell.Height = 9f;
        //cell.HorizontalAlignment = 1;
        //cell.BackgroundColor = iTextSharp.text.BaseColor.LIGHT_GRAY;
        ////c = iTextSharp.text.BaseColor.BLUE;
        //pdfTable.AddCell(cell);

        foreach (DataColumn column in dataTable.Columns)
        {
            pdfTable.AddCell(FormatHeaderPhrase(column.ColumnName));
        }
        // this is the end of the table header
        pdfTable.HeaderRows = 1;
        pdfTable.DefaultCell.BorderWidth = 1;

        foreach (DataRow row in dataTable.Rows)
        {
            foreach (object cells in row.ItemArray)
            {
                //attach the formated output( all cells of datatable )
                pdfTable.AddCell(FormatPhrase(cells.ToString()));
            }
        }
        document.Add(pdfTable);
    }
    private static Phrase FormatPageHeaderPhrase(string value)
    {
        return new Phrase(value, FontFactory.GetFont(FontFactory.COURIER, 12, Font.BOLD, new BaseColor(102, 0, 153)));
    }
    private static Phrase FormatHeaderPhrase(string value)
    {
        return new Phrase(value, FontFactory.GetFont(FontFactory.COURIER, 8, Font.ITALIC, new BaseColor(0, 0, 255)));
    }
    private Phrase FormatPhrase(string value)
    {
        return new Phrase(value, FontFactory.GetFont(FontFactory.COURIER, 6));
    }
    private string GetTimeStamp(DateTime value)
    {
        return value.ToString("yyyyMMddHHmmssffff");
    }
}


You have to call that class from an event like button click as ::


PDFExporter pdf = new PDFExporter(getPrintData(), "customer", true);
 pdf.ExportPDF();

Pass Table as a Parameter In SQL StoreProcedure

Tuesday, June 26, 2012

/* Create a table type. This table behave like a user define Variable */
create TYPE ASROYTableType AS TABLE
( sl INT
, tr VARCHAR(10)
 ,ptype nvarchar(2));
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE UpdatePrintSt
    (
    @TTP ASROYTableType READONLY
    --@type nvarchar(2)
    )
    AS
    begin
SET NOCOUNT ON
declare @pt nvarchar(2)
set @pt=(select top 1 ptype from @TTP)
--select @pt=tr from @TTP
if(@pt ='2')
update Tbl_Sap_master set prnt='Y'where TR_No in(select tr from @TTP)
else
update Inova_PIS_Master set prnt='Y'where TR_No in(select tr from @TTP)

    end


Create Dynamic Table on Selected CheckBox of a GridView

Monday, June 25, 2012

#region " Declare All "
    DataTable dtPrint = new DataTable();                                   // Create Table object
    Int32 no=1;
    Int32 i = 0;  

   DataColumn dc1=new DataColumn("sl",typeof(int));         //declare Column
   DataColumn dc2=new DataColumn("tr",typeof(string));
   dtPrint.Columns.Add(dc1);                                              // Add Column to tha Table
   dtPrint.Columns.Add(dc2);
   DataRow dr;                                                                   // Create dataRow Object

#endregion

# region " In the Event like- Button Click"

        foreach (GridViewRow gvr in grdUnPrinted.Rows)
        {
            CheckBox ckb=(CheckBox)gvr.FindControl("CheckBox1");
            if (ckb.Checked == true)
            {
           

                Label lbltrno=(Label)gvr.FindControl("lblTrNo");
                string trno=lbltrno.Text;

                dr = dtPrint.NewRow();
                dtPrint.Rows.Add(dr);

                //for(int i=0;i<10;i++)
                //{

                    dtPrint.Rows[i]["sl"] = no;
                    dtPrint.Rows[i]["tr"] = trno;
                    i++;

                //}
                    no++;
            }
        }
#endregion

Hack / Break Password of a Windows 7 PC

Friday, June 15, 2012
At the time of Start up, when the black screen was display,
1.  Press F8 for Start with Command Prompt.
2. Then Follows those underlying command for Create a new User with Admin privilege :

net user Antahin abhirup /ADD /FULLNAME:"Abhirup Sinha Roy" /COMMENT:"Created By Abhi"
 /ACTIVE:Yes /PASSWORDCHG:NO /EXPIRES:NEVER
net localgroup "Administrators" Antahin /add
wmic useraccount where "name='Antahin'" set PasswordExpires=False

* Antahin = user name & abhirup = password

Boot From Pen Drive

Wednesday, April 18, 2012
Follow those step One by One : 


The systems on which you want to install Windows 7 via the USB flash drive have to be able to be configured to boot from a USB drive. Most new systems have the capability to boot from a USB flash drive, and the operation can usually be configured in the BIOS or by simply pressing a certain key during bootup. You’ll need to check your specific hardware in order to be sure.
Now, locate the command Prompt shortcut on the Start menu, right-click on it, and select the Run as Administrator command. Then,


Step 1 :












Step 2 :


















Step 3 :


Step 4 :


Step 5 :



Step 6 :
*For  Format fs=fat32




Step 7 :

The USB flash drive is now ready. At this point, you can use the Exit command to exit the DiskPart environment and then close the Command Prompt window.

Step 8 :

Copying the Windows 7 files is easy. Just open Windows Explorer, access the Windows 7 DVD, select all the files and folders, and then drag and drop them on the USB flash drive icon. Keep in mind that the copy operation will take a little while to complete.

Step 9 :

Just boot the system from the USB drive and the installation procedure will begin as it normally would, However, the installation procedure will actually run quicker off a USB flash drive since it doesn’t contain any physically moving components.

& Now Your job is DONE . 

Test1

This only for test purpose, nothing else.