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

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#

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

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

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

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

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

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