Asp.Net and SQL SERVER

Exporting gridview to excel with multiple headers

2012-01-19T18:02:00.000+05:30

In the code behind declare GridViewRow globally for the page.
Refer the linkmerging-gridview-with-multiple-headers
Add the ImageButton in the aspx page.

<asp:ImageButton ToolTip="Csv" ID="btnCsvDown" runat="server" ImageUrl="~/Images/csv.jpg" OnClick="ibtnDownload_Click" CommandName="Csv"/>

<asp:ImageButton ToolTip="Word" ID="btnWordDown" runat="server" ImageUrl="~/Images/word-icon.png" OnClick="ibtnDownload_Click" CommandName="Word" />

<asp:ImageButton ToolTip="Excel" ID="btnExcelDown" runat="server" ImageUrl="~/Images/excel-icon.png" OnClick="ibtnDownload_Click" CommandName="Excel"/>

<asp:ImageButton ToolTip="Pdf" ID="btnPdfDown" runat="server" ImageUrl="~/Images/pdf-icon.png" OnClick="ibtnDownload_Click" CommandName="Pdf"/>

Click events for all the download Image buttons.


    protected void ibtnDownload_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton imgButton = sender as ImageButton;
        GridViewExportUtil.Export("Report.xls", this.gvStylish, HeaderRow,
            DateTime.Now.AddDays(-20).ToShortDateString(),
            DateTime.Now.ToShortDateString(), imgButton.CommandName.ToUpper());
    }

The below source code is developed by Mattberseth.
I have updated this, when we are exporting a gridview with multiple headers.mattberseth


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
using System.Reflection;


/// 
/// 
/// 

public class GridViewExportUtil
{
    /// 
    /// 
    /// 

    /// 
    /// 
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "utf-8");
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("oCodepage", "65001");

        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1253");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                //   if (gv.FooterRow != null)
                //   {
                //       GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                //       table.Rows.Add(gv.FooterRow);
                //   }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response

                String s = sw.ToString();

                HttpContext.Current.Response.Write(s);
                HttpContext.Current.Response.End();
            }
        }
    }


    public static void Export(string fileName, GridView gv, GridViewRow HeaderRow, string fdate, string tdate, string strExportType)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

        switch (strExportType)
        {
            case "WORD":
                HttpContext.Current.Response.ContentType = "application/ms-word";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".doc");
                break;
            case "EXCEL":
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".xls");
                break;
            case "PDF":
                HttpContext.Current.Response.ContentType = "application/pdf";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".pdf");
                break;
            case "CSV":
                HttpContext.Current.Response.ContentType = "application/text";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".csv");
                break;
        }

        HttpContext.Current.Response.AddHeader(
    "content-disposition", string.Format("attachment; filename={0}", fileName));

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                table.Rows.Add(CreateTableRow("Report from " + fdate + " to " + tdate, 5));
                table.Rows.Add(CreateTableRow("Report Generated on:" + DateTime.Now.ToString("MM/dd/yyyy"), 5));

                //  add the new header row to the table
                if (HeaderRow != null)
                {
                    PrepareControlForExport(HeaderRow);
                    foreach(TableCell tcHeader in HeaderRow.Cells )
                        tcHeader.BackColor = System.Drawing.Color.LightGray;
                    //HeaderRow.BackColor = System.Drawing.Color.LightGray;
                    table.Rows.Add(HeaderRow);
                }

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    //Since we are merging the header cell. We have to remove the cell
                    /**/
                    GridViewRow gridViewHeader = gv.HeaderRow;
                    for (int removeCell = 2; removeCell >= 0; removeCell--)
                        gridViewHeader.Cells.RemoveAt(removeCell);
                    foreach (TableCell tcHeader in gridViewHeader.Cells)
                        tcHeader.BackColor = System.Drawing.Color.LightGray;
                    //gridViewHeader.BackColor = System.Drawing.Color.LightGray;
                    /**/

                    //GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    //table.Rows.Add(gv.HeaderRow);
                    GridViewExportUtil.PrepareControlForExport(gridViewHeader);
                    table.Rows.Add(gridViewHeader);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);

                    /**/
                    //Set the default color
                    System.Drawing.Color color = row.BackColor;
                    if (color != System.Drawing.Color.White)
                        row.BackColor = System.Drawing.Color.White;

                    if (row.Cells[0].Text.ToUpper().Contains("TOTAL") ||
                        row.Cells[0].Text.ToUpper().Contains("GRAND"))
                    {
                        color = System.Drawing.Color.LightGray;
                        row.Font.Bold = true;
                    }
                    if (color != System.Drawing.Color.White)
                        foreach (TableCell tcRow in row.Cells)
                            tcRow.BackColor = color;
                    /**/

                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
    /*

    private static void ExportToPdf()
    {
        // Create instance of a new document
        Document doc = new Document(PageSize.A4, 10, 10, 50, 50);
        // Change   the content type to application/pdf !Important
        HttpContext.Current.Response.ContentType = "application/pdf";
        // Get   Instance of pdfWriter to be able to create 
        // the document in the OutputStream
        PdfWriter.GetInstance(doc, HttpContext.Current.Response.OutputStream);
        // Create   Style Sheet
        StyleSheet styles = new iTextSharp.text.html.simpleparser.StyleSheet();
        //--styles.LoadTagStyle("ol", "leading", "16,0");
        doc.Add(new Header(iTextSharp.text.html.Markup.HTML_ATTR_STYLESHEET, "Style.css"));
        // Open the   document to be able to write to it
        doc.Open();
        //--styles.LoadTagStyle("li", "face", "garamond");
        //--styles.LoadTagStyle("span", "size", "8px");
        //--styles.LoadTagStyle("body", "font-family", "times new roman");
        //--styles.LoadTagStyle("body", "font-size", "10px");
        // Parse   html to PDF understandable objects
        var objects = iTextSharp.text.html.simpleparser.HTMLWorker.ParseToList(new StreamReader(Server.MapPath("Order.aspx"), Encoding.Default), styles);
        for (int k = 0; k < objects.Count; k++)
        {
            doc.Add((IElement)objects[k]);
        }
        // Close   the document, rendering it to the browser
        doc.Close();
    }
     */
   
    /// 
    /// Replace any of the contained controls with literals
    /// 

    /// 
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            //ctl.GetType().FullName == "System.Web.UI.WebControls.DataControlLinkButton")
            Control current = control.Controls[i];
            switch (current.GetType().Name)
            {
                case "LinkButton":
                    //ReplaceControl(control, i, (current as LinkButton).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                    break;
                case "ImageButton":
                    //ReplaceControl(control, i, (current as ImageButton).AlternateText);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                    break;
                case "HyperLink":
                    //ReplaceControl(control, i, (current as HyperLink).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                    break;
                case "DropDownList":
                    //ReplaceControl(control, i, (current as DropDownList).SelectedItem.Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                    break;
                case "CheckBox":
                    //ReplaceControl(control, i, (current as CheckBox).Checked ? "True" : "False");
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                    break;
            }
           
            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }

    //private static void ReplaceControl(Control control, int i, string text)
    //{
    //    control.Controls.Remove(text);
    //    control.Controls.AddAt(i, new LiteralControl(text));
    //}

    public static TableRow CreateTableRow(string text, int iColSpan)
    {
        TableRow objTableRow = new TableRow();
        TableCell tc = new TableCell();
        tc.Text = text;
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Font.Bold = true;
        tc.ColumnSpan = iColSpan;
        objTableRow.Cells.Add(tc);
        return objTableRow;
    }
}

Export GridView with Images to Word Excel and PDF Formats in ASP.Net
Soure Code
References

Merging Gridview with Multiple Headers

2011-12-19T19:25:00.001+05:30


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

public partial class _Default : System.Web.UI.Page 
{
    #region Declarations
    CustomersDataObject objData = null;
    GridViewRow HeaderRow = null;
    #endregion

    #region Events
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            objData = new CustomersDataObject();
            //DataTable dt =  objData.Select().Table ;
            gvStylish.DataSource = objData.Select();
            gvStylish.DataBind();
        }
    }

    protected void gvStylish_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //http://www.codeproject.com/Articles/249155/Rows-and-Columns-Merging-in-ASP-NET-GridView-Contr
        //http://sammisoft.net/mboard/mboard/mboard.asp?exe=view&board_id=pds&group_name=sammisoft&idx_num=194&page=22&category=0&search_category=&search_word=&order_c=intVote&order_da=asc
        //http://www.thaicreate.com/dotnet/forum/048135.html

        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            HeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            for (int cellCount = 0; cellCount < e.Row.Cells.Count; cellCount++)
            {
                if (e.Row.Cells[cellCount].Text.Trim() == "CustomerID"
                    || e.Row.Cells[cellCount].Text.Trim() == "CompanyName"
                    || e.Row.Cells[cellCount].Text.Trim() == "ContactName")
                {
                    //Row Span
                    HeaderRow.Cells.Add(HeaderRowMerg(e, 2, cellCount, System.Drawing.Color.Gray.Name));
                }

                else
                {
                    //Col Span
                    HeaderRow.Cells.Add(HeaderColumnMerg(e, 3, "Subjects", System.Drawing.Color.Gray.Name));
                    break;
                }

            }
            gvStylish.Controls[0].Controls.AddAt(0, HeaderRow);
        }
    }

    /// 
    /// Merging datarow cells
    /// 

    /// 
    /// 
    protected void gvStylish_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            /*Merging starts*/
            int _iNoOfCellsToCheck = 6;
            for (int rowIndex = gvStylish.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            {
                GridViewRow gvRow = gvStylish.Rows[rowIndex];
                GridViewRow gvPreviousRow = gvStylish.Rows[rowIndex + 1];
                //for (int cellCount = 0; cellCount < gvRow.Cells.Count; cellCount++)
                for (int cellCount = 1; cellCount < _iNoOfCellsToCheck; cellCount++)
                {
                    if (gvRow.Cells[cellCount].Text == gvPreviousRow.Cells[cellCount].Text)
                    {
                        gvRow.Cells[cellCount].RowSpan =
                            gvPreviousRow.Cells[cellCount].RowSpan < 2 ?
                            2 :
                            gvPreviousRow.Cells[cellCount].RowSpan + 1;

                        gvPreviousRow.Cells[cellCount].Visible = false;
                    }
                }
            }
            /*Merging ends*/

            //If we want to set background color for the row
            //if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("TOTAL"))
            if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("MUTHU"))
            {

                SetRowBackColor(e, System.Drawing.Color.Silver.Name);
            }
            else
                //if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("GRAND"))
                if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("VIJAY"))
                {
                    SetRowBackColor(e, "#999999");
                }
        }
    }
    #endregion

    #region Functions
    private TableCell HeaderRowMerg(GridViewRowEventArgs e, int iRowSpan, int iIndex, string backcolor)
    {
        TableHeaderCell Cell_Header = new TableHeaderCell();
        Cell_Header.Text = e.Row.Cells[iIndex].Text;
        Cell_Header.HorizontalAlign = HorizontalAlign.Center;
        Cell_Header.Font.Bold = true;
        Cell_Header.RowSpan = iRowSpan;
        Cell_Header.Style.Add("background-color", backcolor);
        e.Row.Cells[iIndex].Attributes.Add("style", "display: none;");
        return Cell_Header;
    }

    private TableCell HeaderColumnMerg(GridViewRowEventArgs e, int iColumnSpan, string _strHeaderText, string backcolor)
    {
        TableCell Cell_Header = new TableCell();
        Cell_Header.Text = _strHeaderText;
        Cell_Header.HorizontalAlign = HorizontalAlign.Center;
        Cell_Header.ColumnSpan = iColumnSpan;
        Cell_Header.Font.Bold = true;
        Cell_Header.Style.Add("background-color", backcolor);
        return Cell_Header;
    }

    bool IsInt(string strVal)
    {
        try
        {
            int value = int.Parse(strVal);
            return true;
        }
        catch
        {
            return false;
        }
    }

    private void SetRowBackColor(GridViewRowEventArgs e, string color)
    {
        e.Row.BackColor = System.Drawing.Color.FromName(color);
        e.Row.Font.Bold = true;
        foreach (TableCell tc in e.Row.Cells)
        {
            if (IsInt(tc.Text))
                tc.Text = Convert.ToInt32(tc.Text).ToString("#,#");
        }
    }
    #endregion
}

AddressAccessDeniedException in WCF 2010

2011-10-12T15:09:00.000+05:30

Today when executing the service it gives the below exception.
HTTP could not register
URL http://+:8002/.../. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details).

The reason for the above exception is given in the book Programming WCF Services O'REILLY by JUVAL LOWY

How to instruct windows to register the URL

Go to programs and select "Visual Studio Tools"
Right click on Visual Stutdio Command Prompt and select Run as administrator

We get a command prompt shows the path "C:\WINDOWS\system32"We have to give netsh http add urlacl url=http://+:8002/ user="MachineOrDomain\UserName"

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

2011-10-06T15:54:00.001+05:30

@@IDENTITY	SCOPE_IDENTITY	IDENT_CURRENT	IDENT_SEED	IDENT_INCR
Any table Current session Any scopes.	Any table Current session Current scope.	Specific table Any session Any scope.	Returns the original seed value If we re-seed using DBCC CHECKIDENT won't change the value.	Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.
Table or View	Table or View	Table or View
If user truncates or drop the table also, it will return the last identity value generated in the current session	If user truncates or drop the table also, it will return the last identity value generated in the current session	If user truncated the table it will return the Ident_Seed value. It return null when user drop the table.

A scope is a module: a stored procedure, trigger, function, or batch.
Each query window in Query Analyzer is a session.

How to get all the session running in the server?
EXEC sp_who 'test';

How to check the current session

SELECT @@SPID       SessionID,
       USER_NAME()  AS [User_Name] ,
       SUSER_NAME() AS [Login identification name of the user.],
       CURRENT_USER AS [Name of the current user] ,
       SYSTEM_USER  AS [System_user],
       SESSION_USER AS [Session_user],
       USER         AS [User]

IDENT_SEED

DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)
Returns the original seed value, if we re-seed using DBCC CHECKIDENT.
IDENT_SEED will return the seed value created for the Idetntiy column( when table creation or Alteration).
It won't changed by DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)

References
RESED Identity Column
SQLAuthority

Trim Leading Or Trailing Control Characters

2011-09-26T16:12:00.001+05:30

DECLARE @MyString VARCHAR(1000)
--Control character

DECLARE @Tab            VARCHAR(2)
DECLARE @LineFeed       VARCHAR(2)
DECLARE @CarriageReturn VARCHAR(2)
DECLARE @SearchPattern  VARCHAR(10)

SET @Tab            =              CHAR(9)
SET @LineFeed       =              CHAR(10)
SET @CarriageReturn =              CHAR(13)

--Search for any single of more chars not with specified range
SET @SearchPattern = '%[^ '   + @Tab + @LineFeed + @CarriageReturn + ']%'

SET @MyString = CHAR(10) + ' ' + CHAR(10)+ CHAR(9) + 'My name is ' + CHAR(10) 
				+ ' babu ' + CHAR(10) + ' ' + CHAR(10) + CHAR(10) + ' ' + CHAR(10)

SELECT LEN(@MyString) AS TotalLength
SELECT @MyString AS MyString

SELECT LEN( 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) 
) AS AfterTrimedLength

SELECT 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) AS AfterTrimed

Auto center pop up window

2011-09-13T15:32:00.003+05:30


function PopupCenter() {
    var width = 600;
    var height = 480;
    var top = (screen.height / 2) - (height / 2);
    var left = (screen.width / 2) - (width / 2);

    var parametar = 'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width=' + width + ', height=' + height + ', top=' + top + ', left=' + left;
    var url = 'Default.aspx'
    var windowName = 'Default';
    var targetWin = window.open(url, windowName, parametar);
    targetWin.focus();
    return false;
}

Page with query string


function PopupCenter(Name, Age) {
    var width = 600;
    var height = 480;
    var top = (screen.height / 2) - (height / 2);
    var left = (screen.width / 2) - (width / 2);

    var parametar = 'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width=' + width + ', height=' + height + ', top=' + top + ', left=' + left;
    var url = 'Default.aspx?Name=' + Name + '&Age=' + Age;
    var windowName = 'Default';
    var targetWin = window.open(url, windowName, parametar);
    targetWin.focus();
    return false;
}

References
www.javascripter.net
msdn
htmlgoodies

Creating a Windows Service in C#.net and call a method every hour

2011-08-05T19:39:00.012+05:30

In Visual Studio Select File->New->Project
Select Windows and select Windows Service

Right click on the Service1 designer and select Add Installer.
This adds ProjectInstaller

To avoid Set Service Login prompt being asked about the system username and password you must change the Account for the serviceProcessInstaller to LocalSystem.
ServiceAccount Enumeration
ServiceAccount
stackoverflow

How to Start the service automatically?

Rename Service1 to to MyService

Create a folder and write the code to write to file

How to check whether the service called function is working or not?
Right click on the Solution(DemoService) and select Add -> New Project
This console application is used to test whether file writing works or not.

How to create Service Installer?
Right click on the Solution(DemoService) and select Add -> New Project
Select Other Project Types -> Setup and Deployment -> Setup Project

We will get the below screen.

Right click on the Setup project

Select the service project

Double click on the Application Folder

Press Ok.

Select the setup project and press F4
Manufacturer will come in path as "C:\Program Files\ManufacturerValue"
Title will come in Setup Wizard

Click on Configuration Manager

Change Debug mode to Release for the Service and Setup projects

To avoid this prompt give LocalSystem to Account property of serviceProcessInstaller.

Type control in run and select Administrative Tools -> Services

Make the service to call every 10 minutes
Create a file as the below image

public class WriteToFile
    {
        public WriteToFile()
        {
            string strDateTime = DateTime.Now.ToString();
            StreamWriter sw = new StreamWriter(Path.Combine(System.IO.Path.GetTempPath(), "babu.txt"), true);
            sw.WriteLine(strDateTime);

            sw.Close();
        }
    }

Update the MyService.cs file

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using DemoService.Functions;
using System.Timers;

namespace DemoService
{
    public partial class MyService : ServiceBase
    {
        //Make the service to call the for every 5 min
        System.Timers.Timer TimerObj = new System.Timers.Timer(ConvertMinutesToMilliseconds(5));

        public MyService()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            // TODO: Add code here to start your service.
            TimerObj.Elapsed += new ElapsedEventHandler(OnElapsedTime);
            TimerObj.Enabled = true;
        }

        protected override void OnStop()
        {
            // TODO: Add code here to perform any tear-down necessary to stop your service.
            TimerObj.Enabled = false;
        }

        /// 
        /// At the end of every 5 min the event will be triggered.
        /// 

        /// 


        /// 


        protected void OnElapsedTime(object source, ElapsedEventArgs e)
        {
            //Write the current datetime to the file
            WriteToFile objWriteToFile = new WriteToFile();
        }

        /// 
        /// Convert minutes to milliseconds
        /// 

        /// 


        /// 
        public static double ConvertMinutesToMilliseconds(double minutes)
        {
            return TimeSpan.FromMinutes(minutes).TotalMilliseconds;
        }
    }
}

Switch Theme Without Postback

2011-07-08T12:29:00.003+05:30

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;



public partial class ChangeTheme : System.Web.UI.Page, ICallbackEventHandler

{

    protected void Page_PreInit(object sender, EventArgs e)

    {

        //If theme is null or not changed by user use Default theme

        if (String.IsNullOrEmpty(Convert.ToString(Session["Theme"])))

            Page.Theme = "Default";

        else

            Page.Theme = Convert.ToString(Session["Theme"]);

    }



    protected void Page_Load(object sender, EventArgs e)

    {

        //JavaScript Callbacks

        ClientScriptManager scriptManager = Page.ClientScript;

        String cbReference = scriptManager.GetCallbackEventReference(this, "returnValue", "ReceiveServerData", "context");

        String callbackScript = "function CallServer(returnValue, context) {" + cbReference + "; }";

        scriptManager.RegisterClientScriptBlock(this.GetType(), "CallServer", callbackScript, true);



        TextBox1.Text = DateTime.Now.ToString();

    }



    #region ICallbackEventHandler Members

    //Return value from the server to the client

    string ICallbackEventHandler.GetCallbackResult()

    {

        return Convert.ToString(Session["Theme"]);

    }



    //Get the theme name from the javascript function

    public void RaiseCallbackEvent(string eventArgument)

    {

        Session["Theme"] = eventArgument.Trim();

    }



    #endregion

}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ChangeTheme.aspx.cs" Inherits="ChangeTheme" %>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />

    <link href="StyleSheet3.css" rel="stylesheet" type="text/css" />

    <link href="App_Themes/Default/StyleSheet.css" rel="stylesheet" type="text/css" />



    <script language="javascript" type="text/javascript">



var updatedTheme = '';



function createjscssfile(filename, filetype) {

    if (filetype == "js") { //if filename is a external JavaScript file

        var fileref = document.createElement('script')

        fileref.setAttribute("type", "text/javascript")

        fileref.setAttribute("src", filename)

    } else if (filetype == "css") { //if filename is an external CSS file

        var fileref = document.createElement("link")

        fileref.setAttribute("rel", "stylesheet")

        fileref.setAttribute("type", "text/css")

        fileref.setAttribute("href", filename)

    }

    return fileref

}



function replacejscssfile(newTheme, filetype) {



    updatedTheme = newTheme;



    var targetelement = (filetype == "js") ? "script" : (filetype == "css") ? "link" : "none" //determine element type to create nodelist using

    var targetattr = (filetype == "js") ? "src" : (filetype == "css") ? "href" : "none" //determine corresponding attribute to test for

    var allsuspects = document.getElementsByTagName(targetelement)

    for (var i = allsuspects.length; i >= 0; i--) { //search backwards within nodelist for matching elements to remove

        /*new updates starts*/

        //var newTheme = ''

        var oldTheme = ''



        if (allsuspects[i] && allsuspects[i].getAttribute(targetattr) != null) {

            var lnk = allsuspects[i].getAttribute(targetattr);

            if (lnk.indexOf("App_Themes") != -1) {

                var from = lnk.indexOf('/', 0);

                var to = lnk.indexOf('/', from + 1);



                oldTheme = lnk.substring(from + 1, to)

                //Get old theme full path       

                //oldTheme = lnk.substring(0, from + 1) + lnk.substring(from +1, to) + lnk.substring(to, lnk.length)

                oldTheme = lnk

                newTheme = lnk.substring(0, from + 1) + newTheme + lnk.substring(to, lnk.length)



                if (allsuspects[i] && allsuspects[i].getAttribute(targetattr) != null && allsuspects[i].getAttribute(targetattr).indexOf(oldTheme) != -1) {

                    //var newelement = createjscssfile(newfilename, filetype)

                    var newelement = createjscssfile(newTheme, filetype)

                    allsuspects[i].parentNode.replaceChild(newelement, allsuspects[i])

                }

            }

        }



        /*new updates ends*/

        /*

        if (allsuspects[i] && allsuspects[i].getAttribute(targetattr) != null && allsuspects[i].getAttribute(targetattr).indexOf(oldfilename) != -1) {

            //var newelement = createjscssfile(newfilename, filetype)

            var newelement = createjscssfile(newTheme, filetype)

            allsuspects[i].parentNode.replaceChild(newelement, allsuspects[i])

        }

        */

    }

}



        function ReceiveServerData(returnValue, context) {

        alert(returnValue);

        }

        function SendRequest() {

            CallServer(updatedTheme);

            return false;

        }



    </script>



</head>

<body>

    <form id="form1" runat="server">

        <div style ="height:auto; width:auto;">

            <asp:TextBox ID="TextBox1" runat="server" CssClass="input"></asp:TextBox>

            <div id="theme-preview-dialog" class="modal xl yui3-overlay-content yui3-widget-stdmod"

                role="dialog" style="display: block;" tabindex="0">

                <h2 class="modal-hd">

                    Themes <span class="close" data-action="cancel" title="Close"></span>

                </h2>

                <div class="modal-bd">

                    <p>

                        Preview the themes below and customise my site! with your favourite!

                    </p>

                    <div class="inner themes" id="yui_3_2_0_1_13091761919062132">

                        <div id="purple" class="theme ">

                            <a href="JavaScript:replacejscssfile('Green', 'css');" title="Purple"><b></b></a>

                        </div>

                        <div id="orange" class="theme">

                            <a href="JavaScript:replacejscssfile('Red', 'css');" title="orange"><b></b></a>

                        </div>

                        <div id="blue" class="theme">

                            <a href="JavaScript:replacejscssfile('Default', 'css');" title="blue"><b></b></a>

                        </div>

                    </div>

                </div>

                <div class="modal-ft">

                    <asp:Button ID="btnApplyTheme" runat="server" Text="Apply Theme" OnClientClick="return SendRequest()" />

                </div>

            </div>

        </div>

    </form>

</body>

</html>

You can see the time is not changed.

The style set for the background body and the textbox.

Missing ASP.NET Web Application template in Visual Studio 2005?

2011-07-07T15:19:00.005+05:30

First Install

http://download.microsoft.com/download/8/0/8/8086c312-16b1-4e54-a4cc-80a6c171112e/VS80-KB915364-X86-ENU.exe

Second Install
http://download.microsoft.com/download/9/0/6/906064ce-0bd1-4328-af40-49dca1aef87c/WebApplicationProjectSetup.msi

This will not work in express edition

GridView makeover using CSS

2011-03-26T18:39:00.001+05:30

                AllowPaging="True" PageSize="4" AllowSorting="true" OnPageIndexChanging="gvStylish_PageIndexChanging"
                OnRowDataBound="gvStylish_RowDataBound" OnSorting="gvStylish_Sorting">

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

//http://atashbahar.com/post/GridView-makeover-using-CSS.aspx
public partial class StylishGridView_Demo_GridViewMakeOver : System.Web.UI.Page
{
    CustomersDataObject objData = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            objData = new CustomersDataObject();
            //DataTable dt =  objData.Select().Table ;
            gvStylish.DataSource = objData.Select();
            gvStylish.DataBind();
        }
    }

    protected void gvStylish_Sorting(object sender, GridViewSortEventArgs e)
    {
        //Change sort direction
        SortDirection = SortDirection == SortDirection.Descending ? SortDirection.Ascending : SortDirection.Descending;
        string sSortExpression = SortDirection == SortDirection.Ascending ? " Asc" : " Desc";

        //If new column, set as asc
        if (Convert.ToString(SortExpression) != e.SortExpression)
        {
            SortDirection = SortDirection.Ascending;
            sSortExpression = " Asc";
        }

        sSortExpression = e.SortExpression + sSortExpression;
        SortExpression = e.SortExpression;

        objData = new CustomersDataObject();
        gvStylish.DataSource = new DataView(objData.Select().Table, string.Empty, sSortExpression, DataViewRowState.CurrentRows);
        gvStylish.DataBind();
    }

    protected void gvStylish_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        objData = new CustomersDataObject();
        //DataTable dt =  objData.Select().Table ;
        gvStylish.PageIndex = e.NewPageIndex;
        gvStylish.DataSource = objData.Select();
        gvStylish.DataBind();
    }

    protected void gvStylish_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        GridView gridView = (GridView)sender;

        //You have to set the SortExpression in the aspx page.
        if (e.Row.RowType == DataControlRowType.Header)
        {
            //To add tooltip for the header
            if (e.Row.RowType == DataControlRowType.Header)
            {
                foreach (TableCell cell in e.Row.Cells)
                {
                    foreach (Control ctl in cell.Controls)
                    {
                        if (ctl.GetType().ToString().Contains("DataControlLinkButton"))
                        {
                            cell.Attributes.Add("title", "Click to sort " + ((LinkButton)ctl).Text);
                        }
                    }
                }
            }

            //Set default header style for the gridview
            //Get sorted column index for the templated gridview
            SortIndex = -1;
            foreach (DataControlField field in gridView.Columns)
            {
                e.Row.Cells[gridView.Columns.IndexOf(field)].CssClass = "sortable";

                if (!string.IsNullOrEmpty(SortExpression))
                {
                    if (field.SortExpression == SortExpression)
                    {
                        SortIndex = gridView.Columns.IndexOf(field);
                    }
                }
            }

            //Set Sorted style for the gridview header
            if (SortIndex > -1)
                e.Row.Cells[SortIndex].CssClass = (SortDirection == SortDirection.Ascending
                                            ? " sortable sorted asc" : " sortable sorted desc");
        }
        else if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //To change color for the sorded gridvew entire column
            if (SortIndex > -1)
                e.Row.Cells[SortIndex].CssClass += (e.Row.RowIndex % 2 == 0 ? " sortaltrow" : "sortrow");

            //#region Add toolTip to the asp:CommandField Starts
            //int lastCell = e.Row.Cells.Count - 1;
            //foreach (Control ctrl in e.Row.Cells[lastCell].Controls)
            //{
            //    if (ctrl.GetType().BaseType.Name == "LinkButton")
            //    {
            //        LinkButton lnkBtn = ctrl as LinkButton;

            //        switch (lnkBtn.Text.Trim())
            //        {
            //            case "Edit":
            //                lnkBtn.ToolTip = "Click to edit the record.";
            //                break;

            //            case "Delete":
            //                lnkBtn.Attributes.Add("onclick", "javascript:return confirm('Do you want to delete the selected Course?');");
            //                lnkBtn.ToolTip = "Click to delete the record.";
            //                break;
            //        }
            //    }
            //}
            //#endregion
        }
    }

    #region "Properties"
    public Int32 SortIndex
    {
        get
        {
            if (ViewState["_SortIndex_"] == null)
                ViewState["_SortIndex_"] = -1;

            return (Int32)ViewState["_SortIndex_"];
        }
        set { ViewState["_SortIndex_"] = value; }
    }

    public string SortExpression
    {
        get
        {
            if (ViewState["_SortExpression_"] == null)
                ViewState["_SortExpression_"] = string.Empty;

            return (string)ViewState["_SortExpression_"];
        }
        set { ViewState["_SortExpression_"] = value; }
    }

    public SortDirection SortDirection
    {
        get
        {
            if (ViewState["_SortDirection_"] == null)
                ViewState["_SortDirection_"] = SortDirection.Ascending;

            return (SortDirection)ViewState["_SortDirection_"];
        }
        set { ViewState["_SortDirection_"] = value; }
    }

    #endregion
}

#container /*make horizontal center of a div*/
{
 margin: 10px auto;
 width: 700px;
}
.mGrid
{
 background-color: #fff;
 border: solid 1px #525252;
 border-collapse: collapse;
 margin: 5px 0 10px 0;
 width: 100%;
 font: 11px Tahoma;
}

/*Header style starts*/
.mGrid tr th
{
 background: #424242 url(Images/grd_head.png) repeat-x top;
 border:0px;
 border-bottom: 1px solid #c1c1c1;
 border-right: 1px solid #c1c1c1;
 color: #fff;
 font-size: 0.9em;
 padding: 4px 2px;
}

/*Sorting Starts*/
.mGrid tr th.sortable
{
 padding: 0px;
}

.mGrid tr th.sortable:hover
{
 background: #b7e7fb url('Images/grid-header-sortable-back-hover.gif') top left repeat-x;
 border: 1px solid #C4C4C4;
 border-left: none;
 border-top: none;
}

.mGrid tr th.sortable a
{
 color: white;
 display: block;
 min-height: 1px;
 padding: 3px 3px 2px 2px;
 text-decoration: none;
}

.mGrid tr th.sortable a:hover
{
 text-decoration: none;
}

.mGrid tr th.sorted
{
 background: #d8ecf6 url('Images/grid-header-sorted-back.jpg') top left repeat-x;
 border: 1px solid #8B8878;
 border-left: none;
 border-top: none;
}

.mGrid tr th.asc a
{
 background: transparent url('Images/grid-header-asc-glyph.gif') center 1px no-repeat;
}

.mGrid tr th.desc a
{
 background: transparent url('Images/grid-header-desc-glyph.gif') center 1px no-repeat;
}
/*Sorting Ends*/

.mGrid tr .RowStyle
{
 border: 1px solid red;
 padding: 2px 6px 2px 4px;
}

.mGrid tr.alt
{
 background: #f2f9fc;
}

.mGrid tr.RowStyle:hover, .mGrid tr.alt:hover
{
 background: #c1c1c1 url(Images/grid-header-hover.gif) repeat-x top;
}


/*Select entire sorted column starts*/
.mGrid tr.RowStyle .sortaltrow, .mGrid tr.alt .sortaltrow 
{
    background-color: #D6D6D6;
}

.mGrid tr.RowStyle .sortrow, .mGrid tr.alt .sortrow 
{
    background-color: #EAEAEA;
}
/*Select entire sorted column ends*/

.mGrid .pgr
{
 background: #424242 url(Images/grd_pgr.png) repeat-x top;
 text-align: center; /*Make pager to be center*/
}
.mGrid .pgr table
{
 margin: 5px 0;
}
.mGrid .pgr td
{
 border-width: 0;
 color: #fff;
 font-weight: bold;
 line-height: 12px;
 padding: 0 6px;
 border-left: solid 1px #666;
}
.mGrid .pgr a
{
 color: #666;
 text-decoration: none;
}
.mGrid .pgr a:hover
{
 color: #000;
 text-decoration: none;
}

Download Images
Curtesyatashbahar

Grouping RadioButton inside gridveiw

2011-02-28T11:56:00.000+05:30

        function uncheckOthers(id) {
            var elm = document.getElementsByTagName('input');
            for (var i = 0; i < elm.length; i++) {
                if (elm.item(i).id.substring(id.id.lastIndexOf('_')) == id.id.substring(id.id.lastIndexOf('_'))) {
                    if (elm.item(i).type == "radio" && elm.item(i) != id) elm.item(i).checked = false;
                }
            }
        }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        try
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //To make the RadioButton mutually exclusive
                string strScript = "uncheckOthers(" + ((RadioButton)e.Row.Cells[0].FindControl("rbSelect")).ClientID + ");";
                ((RadioButton)e.Row.Cells[0].FindControl("rbSelect")).Attributes.Add("onclick", strScript);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Selecting or DeSelecting multiple checkbox inside a gridview

2011-02-28T11:32:00.005+05:30

function SelectAllOrDeselect(CheckBox, Type, Column, HCheckBox) {
    try {
        var TotalChkBx = parseInt('<%= grd_EmpSelection.Rows.Count %>');
        var TargetBaseControl = document.getElementById('<%= grd_EmpSelection.ClientID%>');
        var TargetChildControl = null;

        if (Column == "1") TargetChildControl = "chkTestSelect";
        else TargetChildControl = "chkTrainingSelect";

        var Inputs = TargetBaseControl.getElementsByTagName("input");
        var SelectCount = 0;
        //Checked/Unchecked all the checkBoxes in side the GridView.
        if (Type == "1") {
            for (var iCount = 0; iCount < Inputs.length; ++iCount) {
                if (Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl, 0) >= 0) 
                    Inputs[iCount].checked = CheckBox.checked;
            }

            //Reset Counter
            SelectCount = CheckBox.checked ? TotalChkBx : 0;
        } else if (Type == "2") {
            //Reset Counter
            for (var iCount = 0; iCount < Inputs.length; ++iCount) {
                if (Inputs[iCount].type == 'checkbox' && Inputs[iCount].id.indexOf(TargetChildControl, 0) >= 0) 
                    if (Inputs[iCount].checked == true) SelectCount++;
            }

            //Modifiy Counter;        
            if (!CheckBox.checked && SelectCount > 0) SelectCount--;

            //Change state of the header CheckBox.
            if (SelectCount < TotalChkBx) HCheckBox.checked = false;
            else if (SelectCount == TotalChkBx) HCheckBox.checked = true;
        }
    } catch (err) {}
}

protected void grd_EmpSelection_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate))
        {

            CheckBox chkTestSelectAll = (CheckBox)this.grd_EmpSelection.HeaderRow.FindControl("chkTestSelectAll");
            CheckBox chkTestSelect = (CheckBox)e.Row.Cells[1].FindControl("chkTestSelect");

            chkTestSelectAll.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'1','1', {0});", chkTestSelectAll.ClientID);
            chkTestSelect.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'2','1', {0});", chkTestSelectAll.ClientID);


            CheckBox chkTrainingSelectAll = (CheckBox)this.grd_EmpSelection.HeaderRow.FindControl("chkTrainingSelectAll");
            CheckBox chkTrainingSelect = (CheckBox)e.Row.Cells[1].FindControl("chkTrainingSelect");

            chkTrainingSelectAll.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'1','2', {0});", chkTrainingSelectAll.ClientID);
            chkTrainingSelect.Attributes["onclick"] = string.Format("javascript:SelectAllOrDeselect(this,'2','2', {0});", chkTrainingSelectAll.ClientID);

        }
    }

The gridview checkbox will maintain the checked state during postback.

Reference
http://www.codeproject.com/KB/webforms/SelectingAllCheckBoxes.aspx

Generate Insert Multiple Records Using One Insert Statement – Use of UNION ALL

2010-12-02T16:05:00.010+05:30

Version I

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)

SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)

SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
                  CASE
                           WHEN
                                    (
                                             data_type = 'bit'
                                    OR       data_type = 'bigint'
                                    OR       data_type = 'decimal'
                                    OR       data_type = 'float'
                                    OR       data_type = 'int'
                                    OR       data_type = 'money'
                                    OR       data_type = 'numeric'
                                    OR       data_type = 'tinyint'
                                    OR       data_type = 'image'
                                    )
                           THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name  + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + ''''   + ', ' + '''' + ',' + '''' + ',' )
                           ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR)  +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
                  END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC

--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)

--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement

--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName

--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13) + CHAR(10)

SELECT @InsColNames

/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/

EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Version II

DECLARE @TableName            VARCHAR(50)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @InsColNames AS       VARCHAR(8000)
DECLARE @NewLineChar AS       VARCHAR(2)
DECLARE @HorizontalTabChar AS VARCHAR(1)
DECLARE @SQuoteFront AS       VARCHAR(15)
DECLARE @SQuoteBack AS        VARCHAR(15)
DECLARE @Comma AS             VARCHAR(150)
SET @SQuoteFront       = '''''''''+'
SET @SQuoteBack        = '+'''''''''
SET @Comma             = ''',''' + ', '
SET @TableName         = 'NetworkPath'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
SELECT   @ColNames     = ( COALESCE (@ColNames + ( '''' + column_name + '''' + ', ' ), '') ),
         @InsColNames  = (
         CASE
                  WHEN
                           (
                                    data_type = 'bit'
                           OR       data_type = 'bigint'
                           OR       data_type = 'decimal'
                           OR       data_type = 'float'
                           OR       data_type = 'int'
                           OR       data_type = 'money'
                           OR       data_type = 'numeric'
                           OR       data_type = 'tinyint'
                           OR       data_type = 'image'
                           )
                  THEN COALESCE (@InsColNames, '') + ( 'CAST( ' + column_name + ' AS VARCHAR)' + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + '''' + ',' + '''' + ',' )
                  ELSE COALESCE (@InsColNames, '') + ( @SQuoteFront + 'CAST( ' + column_name + ' AS VARCHAR('+ CAST(character_maximum_length AS VARCHAR) +') )' + @SQuoteBack + ' + ' + '''' + ' AS ' + column_name + '''' + ', ' + @Comma )
         END )
FROM     information_schema.columns
WHERE    table_name = @TableName
ORDER BY ordinal_position ASC
--Remove comma(,) at the end
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)
--PRINT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )'
--Generate Inserte statement for the given table
SELECT 'INSERT INTO ' + @TableName + ' ( ' + @ColNames + ' )' AS InsertStatement
--PRINT 'SELECT '+  @InsColNames + ' FROM ' + @TableName
--Remove ,',', at the end
SET @InsColNames = LEFT(@InsColNames, LEN(@InsColNames) - 5)
PRINT CHAR(13)                                          + CHAR(10)
SELECT @InsColNames
/*
PRINT 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames +
''' UNION ALL' +
'''' + CHAR(13) + CHAR(10) + 'FROM ' + @TableName
*/
EXEC( 'SELECT ' + '''SELECT ' + '''' + ' ,' + ' ' + @InsColNames + ''' UNION ALL' + '''' + 'FROM ' + @TableName )

Reference

Process Comma Separated Values for Insert Delete and Update into Table

2010-10-19T18:55:00.000+05:30

Download Full Script

Collapsible Div

2010-09-02T18:09:00.009+05:30

function ToggleCollapsible(ControlIdToShow, SaveStateField, ControlFireID) {
    var control = document.all[ControlIdToShow].style;
    var expandstate = document.all[SaveStateField];

    var ControlFireID = document.getElementById(ControlFireID);

    if (control.display == 'none') {
        control.display = '';
        expandstate.value = 'true';

        if (ControlIdToShow == "ToShow") {
            //showImageID.style.display = "none";
            //hideImageID.style.display = "block";
            //On button click change the image
            ControlFireID.src = "Collapsible/collapse.gif";
            ControlFireID.title = "Hide";
        }
    }
    else {
        control.display = 'none';
        expandstate.value = 'false';

        if (ControlIdToShow == "ToShow") {
            //showImageID.style.display = "block";
            //hideImageID.style.display = "none";
            //On button click change the image
            ControlFireID.src = "Collapsible/expand.gif";
            ControlFireID.title = "Show";
        }
    }

    return false;
}

<input type="hidden" name="CollapsiblePanelHidden" value="True" />
<input id="imgBtnShow" name="imgBtnShow" type="image" src="Collapsible/expand.gif"
onclick="return ToggleCollapsible ('ToShow', 'CollapsiblePanelHidden', 'imgBtnShow')"
title="Show" style="display: block;" />
<div id="ToShow" style="display: none;">
<table id="ShowTable">
<tr>
<td>
<a id="ShowTableContent"
title="Click to expand/collapse" onclick="return ToggleCollapsible ('PanelToShowOrHide', 'CollapsiblePanelHidden', 'imgBtnShow', 'imgBtnHide')"
href="#">Collapsible panel (click to expand).</a></td>
</tr>
<tr>
<td id="PanelToShowOrHide" style="width: 100%; display: none; color: Red;">
http://asp-tech.blogspot.com/
</td>
</tr>
</table>
</div>

Generate CSS DropDown Menu from DataBase

2010-09-01T12:22:00.005+05:30

IF OBJECT_ID (N'dbo.MenuMain', N'U') IS NOT NULL

 DROP TABLE dbo.MenuMain;

GO



CREATE TABLE dbo.MenuMain(ID INT, [Name] VARCHAR(50),[Link] VARCHAR(50), [Title] VARCHAR(50), ParentID INT, [Order] INT)



INSERT INTO dbo.MenuMain



SELECT 1, 'CSS Drop Down Menus', NULL, NULL, 0, 1 UNION ALL

SELECT 2, 'Vertical CSS Pop-Out Menu', NULL, NULL, 0, 2 UNION ALL

SELECT 3, 'CSS Hover Navigation',     '#', 'SEO Consultants Directory', 2, 1 UNION ALL

SELECT 4, 'Horizontal Drop & Pop Menu', NULL, NULL, 0, 3 UNION ALL

SELECT 5, 'SEO Consultants Sample',     '#', 'SEO Consultants Vertical Example', 2, 2 UNION ALL

SELECT 6, 'Tanfa Demo example',         '#', 'Complete Example', 5, 1 UNION ALL

SELECT 7, 'Stage 1',                    '#', 'Vertical Menu - Page 1', 6, 1 UNION ALL

SELECT 8, 'Stage 2',                    '#', 'Vertical Menu - Page 2', 6, 2 UNION ALL

SELECT 9, 'Stage 3',                    '#', 'Vertical Menu - Page 3', 6, 3 UNION ALL

SELECT 10, 'Stage 4',                   '#', 'Vertical Menu - Page 4', 6, 4 UNION ALL

SELECT 11, 'Stage 5',                   '#', 'Vertical Menu - Page 5', 6, 5



SELECT   ID    ,

         [Name],

		 [Link],

		 [Title],

         CASE WHEN ParentID = 0

              THEN NULL

              ELSE ParentID

         END AS ParentID,

         [Order]

FROM     dbo.MenuMain

ORDER BY ParentID,

         [Order]

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using System.Text;



public partial class HorizontalCssMenu : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        //http://ago.tanfa.co.uk/css/examples/menu/tutorial-h.html#hs7

        //http://www.alistapart.com/articles/horizdropdowns/



        if (!IsPostBack)

        {

            LoadMenu();

        }

    }



    private void LoadMenu()

    {

        DataTable dtMenu = LoadData();

        

        StringBuilder sbMenu = new StringBuilder();



        foreach (DataRow dr in dtMenu.Rows)

        {

            if (string.IsNullOrEmpty(Convert.ToString(dr["ParentID"]))

                || Convert.ToString(dr["ParentID"]) == "0")

            {

                if (sbMenu.Length != 0)

                    sbMenu.Append(Environment.NewLine);

                sbMenu.Append("" + Environment.NewLine);                 sbMenu.Append("

");

sbMenu.Append("" + Convert.ToString(dr["Name"]) + "
" + Environment.NewLine);

sbMenu.Append("

" + Environment.NewLine + "");

            }

            else

            {

                StringBuilder sbTemp = new StringBuilder();

                if (Convert.ToInt32(dr["Order"]) == 1)

                    sbTemp.Append(Environment.NewLine + "");                 sbTemp.Append(Environment.NewLine + "

");

sbTemp.Append("" + Convert.ToString(dr["Name"]) + "");

sbTemp.Append(Environment.NewLine + "

");                 if (Convert.ToInt32(dr["Order"]) == 1)                     sbTemp.Append(Environment.NewLine + "");



                string id = "ID=\"" + Convert.ToString(dr["ParentID"]) + "\"";

                int length = sbMenu.ToString().IndexOf(id);

                if (length != -1)

                {

                    string Previous = sbMenu.ToString().Substring(0, length);

                    string Next = sbMenu.ToString().Substring(length);

                    if (Convert.ToInt32(dr["Order"]) == 1)

                        sbMenu.Insert((Previous.Length + Next.IndexOf("

")), sbTemp);

                    else

                        sbMenu.Insert((Previous.Length + Next.IndexOf("")), sbTemp);

                }

                else

                    sbMenu.Append(sbTemp);

            }

        }



        if (sbMenu.ToString().Length > 0)

        {

            menu.InnerHtml = sbMenu.ToString();

        }

    }



    public DataTable LoadData()

    {

        DataSet dsUserMenu = null;

        try

        {

            //Connection string from Web.Config

            string sDBConnection = "SERVER=;DATABASE=;UID=;pwd=";

            string[] sTableName = { "Menu" };



            string sQuery = @"SELECT     ID    ,

                                         [Name],

		                                 [Link],

		                                 [Title],

                                         CASE WHEN ParentID = 0

                                              THEN NULL

                                              ELSE ParentID

                                         END AS ParentID,

                                         [Order]

                                FROM     dbo.MenuMain

                                ORDER BY ParentID,

                                         [Order]";



            dsUserMenu = new DataSet();



            DBHelper.FillDataset(sDBConnection, CommandType.Text, sQuery, dsUserMenu, sTableName);



            dsUserMenu.DataSetName = "Menus";

            DataRelation objMenuRelation = new DataRelation("ParentChild",

                    dsUserMenu.Tables["Menu"].Columns["ID"],

                    dsUserMenu.Tables["Menu"].Columns["ParentID"],

                    false);

            objMenuRelation.Nested = true;

            dsUserMenu.Relations.Add(objMenuRelation);

            return dsUserMenu.Tables[0];

        }

        catch (Exception ex)

        {

            throw ex;

        }

    }

}

Download .cs, .aspx, .css, .sql file form below
HorizontalCssMenu.zip

References
ago.tanfa.co.uk
Example
alistapart

Joins in SQL SERVER

2010-08-28T15:22:00.002+05:30

DECLARE @Employee TABLE
(
	EmpID		INT,
	EmpName		VARCHAR(50),
	DeptID		INT
)

INSERT INTO @Employee
SELECT 1,'kannu',1 UNION ALL
SELECT 2,'Ram',1 UNION ALL
SELECT 3,'Kumar',2 UNION ALL
SELECT 4,'Murali',3 UNION ALL
SELECT 5,'Ranjith',4 UNION ALL
SELECT 6,'Suresh',4 UNION ALL
SELECT 7,'Selva',5 UNION ALL
SELECT 8,'Muthu',6 

DECLARE @Department TABLE
(
	DeptID		INT,
	DeptName	VARCHAR(50)
)

INSERT INTO @Department
SELECT 1,'Account' UNION ALL
SELECT 2,'Testing' UNION ALL
SELECT 3,'Web Develop' UNION ALL
SELECT 5,'PDA Develop' UNION ALL
SELECT 6,'Java' UNION ALL
SELECT 7,'DBA'

What is Join in SQL SERVER?

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Join Types

Inner Join
- Inner Join
- Equi-Join
- Natural Join
Outer join
- Left Join
  - Left Join Excluding Inner Join
- Right Join
  - Right Join Excluding Inner Join
- Full Outer Join
  - Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join
Cross Join / Cartesian product
Self Join / Auto Join

Join
The query compares each row of table(@Employee) with each row of table(@Department) to find all pairs of rows which satisfy the join predicate.

Inner Join
The query produces a new result set by combining the column values of both the table @Employee and @Department.
It will produce the resultset if there is atleast one match.

An inner join is a join with a join condition that may contain both equality and non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign. So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keyword. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed.

Equi-join or Inner Join
Join based on equality test. Using other operator disqualifies equijoin.

        SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       INNER JOIN @Department B
 ON     A.DeptID=B.DeptID

Complex Join Or Inner Join
If join nested then it is called complex join
Jon based on more then one column from each table.

Natural Join
Select the Particular column in Both TableA and TableB. Both table should have same no of columns and names (i.e. Identical columns in both tables). This is accomplished by using union operator in both the select statement.

SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee
UNION --Eliminates duplicates
--UNION ALL --Get all the records from both the table
SELECT EmpID  ,
       EmpName,
       DeptID
FROM   @Employee

Outer Join
Left Outer Join
Display all columns in left side and only Match values in right side. Remaining values will be NULL.

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Left Join Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       LEFT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE B.DeptID IS NULL

Right Outer Join
Just Opposite Of Left Outer Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID

Right Join Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       RIGHT JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE A.DeptID IS NULL

Full Outer Join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID  ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID

Outer Join Excluding Inner Join Or Left & Right Joins Excluding Inner Join

SELECT A.EmpID  ,
       A.EmpName,
       A.DeptID ,
       B.DeptID ,
       B.DeptName
FROM   @Employee A
       FULL OUTER JOIN @Department B
       ON     A.DeptID=B.DeptID
WHERE  A.DeptID IS NULL
OR     B.DeptID IS NULL

Cartesian Product Or Cross Join
Known as Join without condition
ResultSet = No of Rows in TableA * No of Rows in TableB
Display all the Possibilities of combinations.

SELECT *
FROM   @Employee
INNER JOIN @Department
ON 1 = 1

--Explicit
SELECT *
FROM   @Employee
CROSS JOIN @Department

--Implicit
SELECT *
FROM   @Employee, @Department

Self-Join
A table joins with itself with one or two aliases to stave off confusion are called self-join.

SELECT A.EmpName ,
       B.EmpName ,
       A.DeptID  ,
       B.DeptID
FROM   @Employee A
       INNER JOIN @Employee B
       ON     A.DeptID = B.DeptID

References:
codeproject
wikipedia
c-sharpcorner.com
techbubbles
marcoullis

asp menu

2010-08-18T12:52:00.001+05:30

Table structure for creating the Menu

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MainMenu](
 [MenuID] [int] NOT NULL,
 [ParentMenuID] [int] NULL,
 [LabelMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LinkMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [TargetMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [HighlightMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_MainMenu] PRIMARY KEY CLUSTERED 
(
 [MenuID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Column' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the menu.' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LabelMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Url for the label' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LinkMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Main menu' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'HighlightMenu'

Query to insert records

INSERT INTO dbo.MainMenu
SELECT 1,NULL,'Home,'#,'_self','1' UNION ALL
SELECT 2,NULL,'Accessories,'#,'_self','2' UNION ALL
SELECT 3,NULL,'Products,'#,'_self','3' UNION ALL
SELECT 4,NULL,'Contact,'#,'_self','4' UNION ALL
SELECT 5,2,'Accessory1,'#,'_self',NULL UNION ALL
SELECT 6,3,'Product1,'#,'_self',NULL UNION ALL
SELECT 7,3,'Product2,'#,'_self',NULL UNION ALL
SELECT 8,5,'Accessory2,'#,'_self',NULL UNION ALL
SELECT 9,5,'Accessory3,'#,'_self',NULL UNION ALL
SELECT 10,8,'Acc,'#,'_self',NULL

Query to generate Insert statements

SELECT 'SELECT ' + CAST(MenuID AS VARCHAR) +',' + COALESCE(CAST(ParentMenuID AS VARCHAR), 'NULL')+ ',''' + LabelMenu + ','''+ LinkMenu + ','''+ TargetMenu + ''','+ (CASE WHEN HighlightMenu IS NULL OR HighlightMenu = '' THEN 'NULL' ELSE +''''+ HighlightMenu +'''' END) + ' UNION ALL' FROM dbo.MainMenu

C# Code

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateMenu();
        }
    }

    private void PopulateMenu()
    {
        //Get the menu structure from the table
        DataSet dsMenu = GetDataSetForMenu();

        foreach (DataRow drDataRow in dsMenu.Tables["Menu"].Rows)
        {
            //If the ParentMenuID is NullOrEmpty then it is the top most menu
            if (string.IsNullOrEmpty(Convert.ToString(drDataRow["ParentMenuID"])))
            {
                MenuItem mainMenuItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
                Menu1.Items.Add(mainMenuItem);
                //CreateSubMenu(dsMenu, drDataRow, mainMenuItem);
            }
            else //Otherwise it is the submenu
            {
                CreateSubMenu(dsMenu, drDataRow, new MenuItem());
            }
        }
    }

    private void CreateSubMenu(DataSet dsSubMenu, DataRow drDataRow, MenuItem mainMenuItem)
    {
        //MenuID is the subMenu of the ParentMenuID
        //string MenuID = Convert.ToString(drDataRow["MenuID"]);
        string ParentMenuID = Convert.ToString(drDataRow["ParentMenuID"]);

        //Which is always true
        if (mainMenuItem.Text == string.Empty)
        {
            //If it is the firstChild of the parent find it here itself
            mainMenuItem = Menu1.FindItem(Convert.ToString(drDataRow["ParentMenuID"]));
            //If we can't able to find it in the main menu we have to look for sub-menu
            if (mainMenuItem == null)
                foreach (MenuItem mi in Menu1.Items)
                {
                    //Find the ParentMenu by ParentMenuID
                    mainMenuItem = GetParentMenuItem(mi, Convert.ToString(drDataRow["ParentMenuID"]));
                    if (mainMenuItem == null)
                        continue;
                    if (!mainMenuItem.Text.Trim().Equals(string.Empty))
                        break;
                }
        }

        if (mainMenuItem == null)
            return;
        //This the child element
        if (!string.IsNullOrEmpty(ParentMenuID))
        {
            MenuItem childrenItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
            childrenItem.Target = (string)drDataRow["LinkMenu"];
            mainMenuItem.ChildItems.Add(childrenItem);
        }
    }

    // Recursive function   
    private MenuItem GetParentMenuItem(MenuItem root, string value)
    {
        MenuItem menuItem;
        //Is MenuItem has ChildItems
        if (root.ChildItems.Count > 0)
        {
            //Check which ChildItems is the Parent
            for (int i = 0; i < root.ChildItems.Count; i++)
            {
                menuItem = GetParentMenuItem(root.ChildItems[i], value);
                if (menuItem != null)
                    return menuItem;
            }
        }
        if (root.Value == value)
        {
            return root;
        }
        else
            return null;
    }

    private DataSet GetDataSetForMenu()
    {
        String constr = "Server=10.16.105.171;Database=test;Uid=test;Pwd=test123;Connect Timeout=0; pooling='true'; Max Pool Size=200";
        String query = @"SELECT MenuID, ParentMenuID, LabelMenu, LinkMenu FROM dbo.MainMenu ORDER BY MenuID ASC";
        DataSet dsMenu = new DataSet();

        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlDataAdapter da = new SqlDataAdapter(query, conn);
            da.Fill(dsMenu);
            da.Dispose();
        }

        dsMenu.DataSetName = "Menus";
        dsMenu.Tables[0].TableName = "Menu";
        DataRelation relation = new DataRelation("ParentChild",
                                dsMenu.Tables["Menu"].Columns["MenuID"],
                                dsMenu.Tables["Menu"].Columns["ParentMenuID"], true);

        relation.Nested = true;
        dsMenu.Relations.Add(relation);

        //This constraint cannot be enabled as not all values have corresponding parent values.

        return dsMenu;
    }

ReferenceException:This constraint cannot be enabled as not all values have corresponding parent values.We will get this exception if we set 0 for ParentMenuID. Where the LabelMenu is parent. Since there is no MenuID with value 0

ArrayList to List Conversion

2010-08-16T16:17:00.005+05:30

ArrayList To List

List newList = new List(arrayList.ToArray(typeof(int)));
List newList = new List(arrayList.ToArray(typeof(string)));

Undocumented SQL Server Commands

2010-08-16T11:30:00.001+05:30

Some Useful Undocumented SQL Server 7.0 and 2000 DBCC Commands
SQL Server 2000 useful undocumented stored procedures
SQL Server Extended Stored Procedures

Constraints In SQL Server

2010-08-14T12:34:00.003+05:30

Constraints: Rules defined on the table, which cannot be violated by the users.

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:

Entity Integrity
Domain Integrity
Referential integrity
User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows in a table.

Default Constraint
Null Constraint
NOT null Constraint
Primary Key Constraint
Unique Key Constraint

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

Check

Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).

Foreign Key

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories. Each of these categories of the data integrity can be enforced by the appropriate constraints.

Microsoft SQL Server supports the following constraints:

PRIMARY KEY
- Composite Key
- Single-Column Key
- Natural Key
- Surrogate Key
- Super Key
- Candidate Key
- Alternate Key
UNIQUE
FOREIGN KEY
CHECK
NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity. No null values are allowed. A table can have only one primary key. A primary key adds a clustered index to the table The key can created for single column or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Not Null Constraint is automatically enforced. SQLPrimaryKeys

Composite Key
Creating Primary Key on more than a column is called Composite Key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
Rules

At all times it holds in the relation assigned to that variable that there are no
two distinct tuples (rows) with the same values for these attributes and
There is not a proper subset of this set of attributes for which (1) holds.

Natural Key
It’s also Primary Key. For example creating Primary Key on employees Social Security Number.

Surrogate Key
It’s also Primary Key. If there no suitable natural key exists then we have to create surrogate key. It is a single column primary key that created automatically (example: Identity in SQL Server) or system generated values (like generated via a table in the schema).

Super Key
A Super Key is a combination of attributes that can be uniquely used to identify a database record. A table might have many Super Keys. A primary key is therefore a minimum Super Key.

Candidate Key
Definition: A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table Primary Key. The remaining is called Alternate Keys.

Alternate Key
A candidate key that is not the primary key is called an alternate key.

A UNIQUE constraint enforces the uniqueness of the values in a single column or a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints. Not Null Constraint is not automatically enforced. So it allows only one null value.

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints. We can create constraints when the table is created, as part of the table definition by using the CREATE TABLE statement.

Super Key - Any combination of keys that make it unique.
Candidate Key - Minimum SK is called candidate key.
Primary Key - Any CK, which does not take null value.
Foreign Key - Reference to PK of another table with no nulls
Unique Key - Same feature is PK but can take null values. Also, in a table there can be many UK.

Any attribute that is uniquely identifying a row in a table is candidate key for the table. We select one of the candidate keys as Primary key.
All candidate keys, which are not chosen as primary key, are Alternate keys.

The key which uniquely identify the rows of the table and which is made up of more than one attribute is called Composite key. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key one of them will become the primary key and the rest are called alternate keys.

Query to get PrimaryKey, ForeignKey, IdentityKey, UniqueKey column names

2010-07-30T19:11:00.005+05:30

DECLARE  @TableName NVARCHAR(50)
SET @TableName = 'PkFkIkTestIdentAsPk'
 
 SELECT
  C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION 
 FROM
  INFORMATION_SCHEMA.COLUMNS C 
  LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS COL
  ON col.TABLE_NAME = C.TABLE_NAME 
  AND col.COLUMN_NAME = C.COLUMN_NAME
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons 
 ON tblCons.CONSTRAINT_NAME = col.CONSTRAINT_NAME 
 WHERE tblCons.TABLE_NAME = @TableName 
  AND 
  (
   tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'UNIQUE' 
  )
 UNION 
  SELECT C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION
  FROM
  INFORMATION_SCHEMA.COLUMNS C
  WHERE C.TABLE_NAME = @TableName
  AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME,'IsIdentity') = 1

SQL SERVER DataTypes

2010-07-10T16:52:00.006+05:30

To get the DataType information
SP_HELP DataTypeName

SELECT 'User_type' = name, 
       'Storage_type' = Type_name(xtype), 
       'Length(Size)' = length, 
       'Prec' = Typeproperty(name, 'precision'), 
       'Scale' = Typeproperty(name, 'scale'), 
       'Nullable' = CASE 
                      WHEN Typeproperty(name, 'AllowsNull') = 1 THEN 'Yes' 
                      ELSE 'No' 
                    END, 
       'Default_name' = Isnull(Object_name(tdefault), 'None'), 
       'Rule_name' = Isnull(Object_name(domain), 'None'), 
       'Collation' = collation 
FROM   systypes 
ORDER  BY name

2008	2005	2000
Exact Numerics	Exact Numerics	Exact Numerics
bigint	bigint	bigint
numeric	numeric	numeric
bit	bit	bit
smallint	smallint	smallint
decimal	decimal	decimal
smallmoney	smallmoney	smallmoney
int	int	int
tinyint	tinyint	tinyint
money	money	money

Approximate Numerics	Approximate Numerics	Approximate Numerics
float	float	float
real	real	real

Date and Time	Date and Time	Date and Time
date
datetimeoffset
datetime2
smalldatetime	smalldatetime	smalldatetime
datetime	datetime	datetime
time

Character Strings	Character Strings	Character Strings
char	char	char
varchar	varchar	varchar
text	text	text

Unicode Character Strings	Unicode Character Strings	Unicode Character Strings
nchar	nchar	nchar
nvarchar	nvarchar	nvarchar
ntext	ntext	ntext

Binary Strings	Binary Strings	Binary Strings
binary	binary	binary
varbinary	varbinary	varbinary
image	image	image

Other Data Types	Other Data Types	Other Data Types
cursor	cursor	cursor
timestamp	timestamp	timestamp
hierarchyid
uniqueidentifier	uniqueidentifier	uniqueidentifier
sql_variant	sql_variant	sql_variant
xml	xml
table	table	table
2008	2005	2000

OutOfMemory Exception

2010-06-26T16:10:00.008+05:30

The below code is used to merge multipage tiff with single page tiffs.

/// 
/// To Merge the Single Page Tifs in folder to a Multipage Tiff Image
/// 

/// This denotes Multipage Image Full Path/// This denotes Single Page Tif Image List/// After Merge where need to store the New Merged Tif Filesprivate bool MergeTifImages(String sMultiPageTif, List lstSingleTifs, String sDestinationPath)
{
String SinglePageTif = String.Empty;
bool sflg = true;
try
{              
// To Check Destination Path is Exist or Not, if not create the path and folder structure
if (!Directory.Exists(@sDestinationPath)) Directory.CreateDirectory(@sDestinationPath);
//Open file in read only mode 
using (FileStream fs = new FileStream(@sMultiPageTif, FileMode.Open, FileAccess.Read))
{
//To Form the Destination Tiff File Name
String _sDestinatinPath = Path.Combine(@sDestinationPath, Path.GetFileName(@sMultiPageTif));
//To Create the Image Codec Info                   
ImageCodecInfo imageCodecInfo = GetEncoderInfo("image/tiff");

//To Create the New bmp Image from the existing Multipage Tif
//Image bmp = Image.FromStream(fs);
Image newTiff = null;
//To Select the Encoder and Compresstion formates for the Tif Images
EncoderParameters SaveEncoderParameters = new EncoderParameters(2);
System.Drawing.Imaging.Encoder SaveEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
EncoderParameter CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.MultiFrame));
SaveEncoderParameters.Param[0] = CompressEncodeParam;
SaveEncoder = System.Drawing.Imaging.Encoder.Compression;
CompressEncodeParam = new EncoderParameter(SaveEncoder, (long)(EncoderValue.CompressionCCITT4));
SaveEncoderParameters.Param[1] = CompressEncodeParam;

System.Drawing.Imaging.Encoder AddEncoder = System.Drawing.Imaging.Encoder.SaveFlag;
EncoderParameter AddEncodeParam = new EncoderParameter(AddEncoder, (long)EncoderValue.FrameDimensionPage);
System.Drawing.Imaging.Encoder AddCompressionEncoder = System.Drawing.Imaging.Encoder.Compression;
EncoderParameter AddCompressionEncodeParam = new EncoderParameter(AddCompressionEncoder, (long)EncoderValue.CompressionCCITT4);
EncoderParameters AddEncoderParams = new EncoderParameters(2);

AddEncoderParams.Param[0] = AddEncodeParam;
AddEncoderParams.Param[1] = AddCompressionEncodeParam;

using (Image bmp = Image.FromStream(fs))
{
//Get the Page Count, i.e Frame Count
int frameCount = bmp.GetFrameCount(FrameDimension.Page);

//To Create Temp New Tiff                 
newTiff = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));

// To Save the Multipage Tiff
newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
Application.DoEvents();
//To Update frame by frame to the multipage
int pageCount = 0;
for (; pageCount < frameCount; pageCount++)
                        {
                            switch (pageCount)
                            {
                                case 0:

                                    newTiff.Save(_sDestinatinPath, imageCodecInfo, SaveEncoderParameters);
                                    break;
                                default:
                                    bmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
                                    // Convert image to bitonal for saving to file

                                    using (Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height)))
                                    {
                                        //Bitmap newPage = Converter.ConvertToBitonal(new Bitmap(bmp, bmp.Width, bmp.Height));
                                        newTiff.SaveAdd(newPage, AddEncoderParams);
                                    }

                                    break;
                            }
                            Application.DoEvents();
                        }
                    }

                    // To Merage the Single Page Tif Image to Multipage Tif Image form Single Tifs List
                    foreach (String SingleTif in lstSingleTifs)
                    {
                        try
                        {
                            CommonDeclarations.WriteLog("Single Page TIF: " + Path.GetFileName(SingleTif), false, false); 
                            using (FileStream fs1 = new FileStream(@SingleTif, FileMode.Open, FileAccess.Read))
                            {
                                //To over come Out of memory.
                                //Image Singlebmp = Image.FromStream(fs1);
                                using (Image Singlebmp = Image.FromStream(fs1))
                                {
                                    Application.DoEvents();
                                    int frameCount = Singlebmp.GetFrameCount(FrameDimension.Page);
                                    //To Check the Given Single Page tif is having More than One Frame
                                    if (frameCount > 1)
{
CommonDeclarations.WriteLog("The Selected Single Page TIF Having More Then One Page. Page Count: " + frameCount, false, false);
//This works if Multipage exist 
int pageCount = 0;
for (; pageCount < frameCount; pageCount++)
{
Singlebmp.SelectActiveFrame(System.Drawing.Imaging.FrameDimension.Page, pageCount);
// Convert image to bitonal for saving to file
Bitmap SinglePageTiff = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
newTiff.SaveAdd(SinglePageTiff, AddEncoderParams);
//  break;
}
}
else
{
//This will works while Single Frame of Single Tiff Image
using (Bitmap tempBmp = new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height))
{
using (Bitmap SinglePageTifs = Converter.ConvertToBitonal(tempBmp))
{
//Bitmap SinglePageTifs = Converter.ConvertToBitonal(new Bitmap(Singlebmp, Singlebmp.Width, Singlebmp.Height));
Application.DoEvents();
newTiff.SaveAdd(SinglePageTifs, AddEncoderParams);

SinglePageTifs.Dispose();
GC.Collect();
}
tempBmp.Dispose();
GC.Collect();
}
}

Singlebmp.Dispose();
GC.Collect();
}

fs1.Close();
fs1.Dispose();
GC.Collect();
}
}
catch (Exception ex)
{
CommonDeclarations.WriteLog(ex.Message.ToString(), false, false);
sflg = false;
}               

}
//Save the New Tiff and Encode the Tiff  Images
AddEncoderParams.Param[0] = new EncoderParameter(AddEncoder, (long)EncoderValue.Flush);
newTiff.SaveAdd(AddEncoderParams);
newTiff.Dispose();
GC.Collect();

}             
}
catch (Exception ex)
{
//CommonDeclarations.ShowMessage(ex.Message.ToString(), "ERROR", lblShowMessage);
CommonDeclarations.WriteLog("Some Error in MergeTifImages, Error: " + ex.Message.ToString(), false, false);
sflg = false;
}
return sflg;
}

After running the application I looked how much memory the Proces use. Object.Dispose(); doesn't release the memory occuped by the object immediately. It shows constant memory increase when seeing.After using GC.Collect(); if frees the memory. Afterwares no OutOfMemory Exception thrown.Here we added each image to a panel(pnlThumbnail) pnlThumbnail.Controls.Add(picBoxArray[iThumbCurrPage]); This occupies more memory. We have to clear that in the following way.

//Clear the picture box stored in thumbnailArray
                if (picBoxArray != null)
                {
                    foreach (PictureBox tempPb in picBoxArray)
                    {
                        if (tempPb.Image != null)
                        {
                            tempPb.Image.Dispose();
                            tempPb.Image = null;

                            GC.Collect();
                        }
                    }
                    picBoxArray = null;
                }

                for (int clearPictureBox = 0; clearPictureBox < pnlThumbnail.Controls.Count; clearPictureBox++)
                {

                    PictureBox pb = pnlThumbnail.Controls[clearPictureBox] as PictureBox;

                    pnlThumbnail.Controls.Remove(pb);

                    if (pb.Image != null)
                    {
                        pb.Image.Dispose();
                        pb.Image = null;
                    }
                }
                
                if (pbShowTiffImage.Image != null)
                {
                    pbShowTiffImage.Image = null;
                    pbShowTiffImage.Invalidate();
                    GC.Collect();
                }
                                
                //The above one is better than this
                //foreach (IDisposable control in pnlThumbnail.Controls)
                //    control.Dispose(); 

                pnlThumbnail.Controls.Clear();
                GC.Collect();

REferencesTracking down managed memory leaks (how to find a GC leak)
IDisposable.Dispose Method
Detecting .NET application memory leaks
memory leak with delegates and workflow foundation
http://msdn.microsoft.com/en-us/magazine/cc163491.aspx
Memory Leak Detection in .NET

Stored Procedure Generator for SQL SERVER

2010-06-16T15:38:00.008+05:30

Download the tool

1. Get Sql Server Name.
2. Get Sql Server IP Address.
3. Load Server from local machine.
4. Load Server from your network.
5. Press the button load to load the Server Name or IP Address.
6. Press the button load to load the DataBase name for the the Selected Server Name or IP Address..
7. Save to File save the Procudure as .sql file.
8. Append The Scripts In SQLFile puts all the Procedure in a single .sql file.
9. Create New File For Each SP creates new .sql file with the name of the procedure.
10. Execute Script To Server, executes your procedure directly to your SQL server.
11. Overwrite the SP If Already Exists. If any procedure already exists it drops the procedure and creates a new one.
12. The left list box contails all the tables in your selected database.
13. >> button to move from left listbox to right for creating procedure for the table.
14. << botton to move from the right listbox to left if you don't want to create procedure for the table.
15. Select All To Move Right to select all tables in the left listbox for moving right.
16. Select All To Move Left to select all tables in the right listbox for moving left.
17. Click Create Scripts button to generate procedure according to the conditions.

Happy Coding.

SQL SERVER Parameter Directions

2010-04-30T17:11:00.002+05:30

There are 4 types of parameter direction in SQL SERVER.

Input
Output or Out
InputOutput
Return

But we can specify only OUT or OUTPUT as parameter direction for the Procedure or Function.

RETURN at the last statement of the Procedure or Function and not in the parameter declaration of the Procedure or Function.

IN

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID


ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

We can’t able to specify in any where during the execution or in the SP parameter direction.

DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID IN –Not allowed

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT IN) -–Not allowed AS
BEGIN
SELECT @ParentID
END

We will get this error.
Incorrect syntax near the keyword 'in'.

OUTPUT
Output is not only for sending value back to the caller, it also accepts input from the caller. Since by default OUT implies both IN and OUT.

E.g. 1

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID = @ParentID + 5
END


DECLARE @ParentID int
SET @ParentID =6
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

Result: 11

There is no need of RETURN or SELECT statement for the OUT direction parameter. Just assing the value, we will get the result.

If we didn’t assing a new value, we will get only the value we sent during execution of the Procedure or Function.(i.e. 6 for the above condion.)

If we didn’t sent any value during execution of the Procedure or Function we will get only nothing for the below condion.

DECLARE @ParentID int
exec [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

E.g. 2

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
SELECT @ParentID
END

No need to pass value. Just specify the keyword OUT near the parameter.

DECLARE @ParentID INT
EXEC [dbo].[GetTestTable] @ParentID OUT
PRINT @ParentID

Result: 10

For the above Procedure or Function we are assigning value to the parameter and also we are using SELECT statement. Here we will get a result-set and aslo the value assigned to our argument.

RETURN

There should be a RETURN statement at the end of the Procedure or Function.

ALTER PROCEDURE [dbo].[GetTestTable](
@ParentID INT OUTPUT)
AS
BEGIN
SET @ParentID =10
RETURN @ParentID
END

DECLARE @ParentID    INT
DECLARE @ReturnValue INT
SET @ParentID     =6
EXEC @ReturnValue = [dbo].[GetTestTable] @ParentID
PRINT @ReturnValue

If there is no RETURN statemnt at the end of the Procedure or Function, we will get only the default value of the RETURN argument datatype.

For example there is no RETURN statemnt for the above Procedure the result will be 0.

We can’t able to return a table variable from a RETURN statement.

Note:

By default OUT implies both IN and OUT.
Since our parameter is OUT we can pass value, since OUT implies both IN and OUT.
The default parameter direction is IN.
If the last statement is just RETURN without any value or a variable we will get
nothing (if we didn’t pass value to the parameter of assign) or default value (if
we assign value when passing). This is default for all the parameter direction.

These all are wrong.
@ReturnValue EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

@ReturnValue = EXEC [dbo].[GetTestTable] @ParentID

Line 4: Incorrect syntax near '@ReturnValue'.

EXEC [dbo].[GetTestTable] @ParentID, @ReturnValue RETURN

Procedure or Function GetTestTable has too many arguments specified.

Three tire code Generator for C#.NET

2010-04-28T11:37:00.006+05:30

Download the toolOr

Select Table Tab to get the tables for your ConnectionString.
Enter your connection string.
Press to get the tables for your ConnectionString.
After pressing the button Load Tables the tables are loaded. When selecting a table it focus on the Conditions tab and all the column and DataType are loaded automatically in 9, 10, 10.1.
Conditon tab to specify the property and field prefix.
Namespace for your class.
Name of your property class.
Name of your data access layer class.
Enter your table name. This helps to produce the sp name. For Select method it creates spname as GetTableName.
Table column name along with DataType.
10.1. Get distinct of DataType from 10.
Clear all the controls.
For each datatype in 10.1 we are generating a textbox to enter the prefix for the DataType.
For each data-type you want to specify a separate prefix check it. For exampele for string specify str. Your field will be created like strUserID. The panel will be enabled and enter it.
By default the field names are generated with underscore (_). If you want to change give a new one.
Enter the prefix for the fields. This will be enabled by checking Is prefix required for fields.
For properties some of us specify pUserID. If you want to specify enter it by check the checkbox of is prefix required for properties.
To genereate the Data Tire Classes.
If your get procedure contains any parameter check the select.
If your Delete procedure contains any parameter check the select.
Select the column names. These are the parameters for procedure Insert and Update.
This is single select. This column will be your direction. For all the four procedures.
Select your ParameterDirection
Select your ParameterDirection.
Select your ParameterDirection.
Select your ParameterDirection.
If you are using SQLHelper class check it. The code generated accordingly.
If you want to save the calsses as file check it. It opens a folder dialogue to select a path where to store the class files.
After select you can view the selected path.
The tab Properties contains a class with properties.
The tab DAL contains a class with functions for calling Select, Insert, Delete Update procecures.
The tab SQLHelper contains a class with functions for calling Select, Insert, Delete Update procecures. This class uses SQLHelper.cs

Happy Coding.

ADO.NET Parameter Direction

2010-04-27T18:54:00.004+05:30

Input	InputOutput	Output	ReturnValue
Passing value to procedure or function.	Pass value to the procedure or function and get back the assigned value from the procedure or function.	Get value from the procedure or function.	Get the value of the return statement.
ParameterDirection .Input;	ParameterDirection .InputOutput	ParameterDirection .Output	ParameterDirection .ReturnValue
(@Columndatetime datetime) By default it is IN. So it is not possible to use the keyword IN.	(@Columndatetime datetime OUT) Since OUT implies both IN and OUT and there is no INOUT.	(@Columndatetime datetime OUT)	We are not passing the parameter in Procedure or function.
DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo]. [GetTestTable] @Columnnumeric, 'BABU' PRINT @ReturnValue	We can assign the value any where in the procedure or function. We can assign value at declaration itself. (@Columndatetime datetime = 10 OUT) If we didn’t assign value in the procedure or function it took the sent value. If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value.	We can assign the value any where in the procedure or function. We can assign value at declaration itself. (@Columndatetime datetime = 10 OUT) If we didn’t assign value in the procedure or function it took the sent value. If we didn’t pass value and we didn’t assing value in the procedure or function but we assign default value it took the default value. DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC [dbo].[GetTestTable] @Columnnumeric OUT, 'BABU' PRINT @Columnnumeric	This should be the last statement of the procedure or function. return 10 or like this. DECLARE @ReturnValue numeric SET @ReturnValue = 10 RETURN 10 Get value from the procedure or function. DECLARE @Columnnumeric numeric, @ReturnValue NUMERIC SET @Columnnumeric = 10 EXEC @ReturnValue = [dbo].[GetTestTable] @Columnnumeric PRINT @ReturnValue

objCmd. Parameters .Add(" @Columnnumeric" , SqlDbType .Decimal) .Value = 10.0d;	objCmd.Parameters .Add("@ Columnnumeric", SqlDbType. Decimal) .Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . InputOutput;	objCmd. Parameters .Add("@ Columnnumeric", SqlDbType. Decimal). Value = 10.0d; objCmd. Parameters["@ Columnnumeric"] .Direction = Parameter Direction . Output;	objCmd. Parameters .Add(" @Columnnumeric", SqlDbType .Decimal). Value = 10.0d; objCmd. Parameters [" @Columnnumeric"] .Direction = Parameter Direction .ReturnValue;
	Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value;	Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value;	Decimal Columnnumeric = (Decimal)objCmd. Parameters ["@ColumnnTest"] .Value;
			If the ParameterDirection is ParameterDirection .ReturnValue we should not use the parameter in prarameter declaration of the procedure or function. !supportEmptyParas]> <![endif]>

http://weblogs.asp.net/andrewrea/archive/2008/02/19/examples-of-using-system-data-parameterdirection-with-sql-server.aspx

Rules to follow

Rule 1

If our ParameterDirection is
ParameterDirection.ReturnValue and we are using in the parameter of the
Procedure or Function it expects the parameter. And we throw this error.

Procedure 'GetTestTable'
expects parameter '@Columnnumeric', which was not supplied.

We should not use the parameter in the procedure if we set
ParameterDirection as ParameterDirection.ReturnValue
in ADO.net.

E.g.

CREATE PROCEDURE [dbo].[GetTestTable] (@Columndatetime DATETIME,
@Columnnumeric NUMERIC)

BEGIN

RETURN 15

END

objCmd.Parameters.Add("@Columndatetime",
SqlDbType.DateTime).Value = objPropertiesClassName.Columndatetime;

objCmd.Parameters.Add("@Columnnumeric",
SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric;

objCmd.Parameters["@Columnnumeric"].Direction =
ParameterDirection.ReturnValue;

objCmd.ExecuteNonQuery();

int i = (int)objCmd.Parameters["@Columnnumeric"].Value;

Rule 2

Consider that we have given the parameter order as given below.

The parameter @ColumnnTest will contain the return value. We will think that we have specified
the direction ReturnValue to @Columnnumeric before @ColumnnTest and @Columnnumeric will contain the value. But it is
not like that. Since we added the parameter @ColumnnTest before @Columnnumeric.
It took according to the order of parameter that we are adding to the command.

objCmd.Parameters.Add("@ColumnnTest",
SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric;

objCmd.Parameters.Add("@Columnnumeric",
SqlDbType.Decimal).Value = objPropertiesClassName.Columnnumeric;

objCmd.Parameters["@Columnnumeric"].Direction =
ParameterDirection.ReturnValue;

objCmd.Parameters["@ColumnnTest"].Direction =
ParameterDirection.ReturnValue;

int j = (int)objCmd.Parameters["@ColumnnTest"].Value;

object i = (object)objCmd.Parameters["@Columnnumeric"].Value;

Rule 3

objCmd.Parameters.Add("@ColumnnTest",
SqlDbType.Decimal).Value = 20.0d;

objCmd.Parameters.Add("@Columnnumeric",
SqlDbType.Decimal).Value = 10.0d;

objCmd.Parameters["@Columnnumeric"].Direction =
ParameterDirection.ReturnValue;

objCmd.Parameters["@ColumnnTest"].Direction =
ParameterDirection.ReturnValue;

If the procedure or function
doesn’t return any value, the first added parameter tooks the default value.

But the next parameter took
the value that we asigned.

Here
@ColumnnTest returns 0.0d, while ("@Columnnumeric
returns the assigned value 10.0d.

We can ask instead of ReturnValue can we use ExecuteScalar(), since it also returns
single value.

The ExecuteScalar returns a single value that is in the form of a result set. That
means the value must be "SELECTed" in the SQL.
I.e. if the result-set contains 3 columns and 2 rows, it took only the 1^st
row and 0^th column value. The remaining are discarded.

Using the RETURN keyword is possible through the use of a ReturnValue parameter.

References
Configuring Parameters and Parameter Data Types (ADO.NET)

Examples of using System.Data.ParameterDirection with Sql Server

Errors and Solution

2010-04-20T17:08:00.001+05:30

Error 1:

string isEmpty = string.Empty;
DateTime dt = isEmpty == string.Empty ? BNull.Value : DateTime.Now;

Error
Type of conditional expression cannot be determined because there is no implicit conversion between 'System.DBNull' and 'System.DateTime'

Solution
Cast it to object.

string isEmpty = string.Empty;
DateTime dt = Convert.ToDateTime(isEmpty == string.Empty ? (object)DBNull.Value : (object)DateTime.Now);

User Defined Functions

2010-04-15T19:31:00.002+05:30

Types of User Defined Functions
I. Scalar Functions
II. In-Line Table Functions
III. Multistatement Table Functions

Accept parameters of any scalar data type except rowversion

Scalar Functions	In-Line Table Functions	Multistatement Table Functions
Returns only scalar value.	Return a table variable that was created by a single select statement.	Returns a table variable. No multi result set. This type of function is used when we want to apply more logic except we can express in a single query. I.e. like stored procedure we can manipulate with more statements.
Support multiple T-SQL statements.	Supports only sing T-SQL Statement.	Support multiple T-SQL statements.
Return type is fixed.	No need to specify the table column names and its type.	We have to specify the table columns.
CREATE FUNCTION Testscalar(@ID INT) RETURNS INT AS BEGIN SELECT @ID = 10 SELECT @ID = @ID + 1 SELECT @ID = @ID + 1 RETURN @ID END	CREATE FUNCTION [dbo].[Testinline] ( Params ) RETURNS TABLE AS RETURN SELECT * FROM dbo.treeview CREATE FUNCTION [dbo].[Testinline](@i INT) RETURNS TABLE AS RETURN SELECT @i AS [ColName]	CREATE FUNCTION [dbo].[Testmultiline] ( @ID INT ) RETURNS @TableVariable TABLE ( I INT, J VARCHAR(10)) AS BEGIN INSERT INTO @TableVariable SELECT 1, '10' UNION ALL SELECT 2, '20' UNION ALL SELECT 3, '30' RETURN END
The last statement included within a function must be a return statement RETURN statements in scalar valued functions must include an argument. Simply retrun statemet is wrong.	The last statement included within a function must be a return statement. Return @I is wrong Return @I as a is wrong	The last statement included within a function must be a return statement. return @TableVariable is wrong.

Original Link : 15seconds

Valid

· Control-flow statements
· Assignment statements
· Variable declarations
· SELECT statements that modify local variables
· Cursor operations that fetch into local variables
· INSERT, UPDATE, DELETE statement that act upon local table variables
Invalid:

· Built-in, nondeterministic functions such as GetDate()
· Statements that update, insert, or delete tables or views
· Cursor fetch operations that return data to the client
. can’t able to execute a stored procedure.

How to Call Scalar-valued Functions

SELECT ColNames, dbo.ScalarValuedFunction(starttime,endtime) AS ColName FROM TableName
WHERE BankerID = dbo.ScalarValuedFunction('IBM')

How to Call Table-valued Functions

SELECT * FROM dbo.TableValuedFunctions('key1|key2|key3|key4|key5', '|')
-- For converting arrays to table

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 

SELECT * FROM Department D 
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

Handlers Module

2010-04-13T18:37:00.000+05:30

HttpContext
- Request
- Response

HttpApplication
- maintaining application-scope methods
- data
- events
- After the HttpApplication object massages the request, it pushes the request through one or more HttpModule objects.

HttpModule

- When an HttpModule is hooked into the pipeline (via an entry in web.config), the ASP.NET runtime calls the module's Init and Dispose methods.
- Init is called when the module attaches itself to the HttpApplication object
- Dispose is called when the module is detached from HttpApplication.

Event	Occurs
AcquireRequestState	When ASP.NET acquires the current state (for example, session state) associated with the current request
AuthenticateRequest	When a security module has established the identity of the user
AuthorizeRequest	When a security module has verified user authorization
BeginRequest	When the first event in the HTTP pipeline chain of execution responds to a request
Disposed	When ASP.NET completes the chain of execution when responding to a request
EndRequest	When the last event in the HTTP pipeline chain of execution responds to a request
Error	When an unhandled exception is thrown
PostRequestHandlerExecute	When the ASP.NET handler (page, XML Web Service) finishes execution
PreRequestHandlerExecute	Just before ASP.NET begins executing a handler such as a page or XML Web Service
PreSendRequestContent	Just before ASP.NET sends content to the client
PreSendRequestHeaders	Just before ASP.NET sends HTTP headers to the client
ReleaseRequestState	After ASP.NET finishes executing all request handlers; also causes state modules to save the current state data
ResolveRequestCache	When ASP.NET completes an authorization event to let the caching modules serve requests from the cache, bypassing execution of the handler (the page or XML Web Service, for example)
UpdateRequestCache	When ASP.NET finishes executing a handler in order to let caching modules store responses that will be used to serve subsequent requests from the cache

ISNULL() vs COALESCE()

2010-04-13T18:18:00.001+05:30

ISNULL()	COALESCE()
Specifc to SQL SERVER	ANSI standard. The query can be used in another RDBMS which follows the ANSI standard.
select ISNULL(NULL, NULL) --O/P null	select COALESCE(NULL, NULL) Error: None of the result expressions in a CASE specification can be NULL. In coalesce atleast one parameter should not be null.
declare @a varchar(10) declare @b varchar(10) select isnull(@a, @b) --O/P null	declare @a varchar(10) declare @b varchar(10) select COALESCE (@a, @b) --O/P null
Required 2 parameters	Requires atleast 2 parameters
Accepts only 2 parameters. But we can nest like this if we want more than 2 parameter. ISNULL(ISNULL(Col1,Col2), Col3)	I tried 18600 parameters. If it reaches more than that or with this count itself the SQL SERVER throws "System.OutOfMemoryException". So I think this blongs to hardware specification.
Consider 2nd parameter data type as first datatype. (i.e. It uses as the first parameter datatype.) If the length of the second parameter is greater than the first parameter it will be truncated to first parameter size. Eg.1 declare @a varchar(10) declare @b DATETIME SET @b = GETDATE() select isnull(@a, @b) --O/P Apr 13 201 --It took the format 100 and varchar size to 10. --Change the varchar size of below query and you will understand. select isnull(@a, convert(varchar(11), @b, 100)) --O/P Apr 13 2010 Eg.2 declare @a varchar(5) declare @b varchar(8) SET @b = 'SQL SERVER' select isnull(@a, @b) --O/P SQL S It truncates to first parameter size. Eg.3 declare @a varchar(5) declare @b varchar(8) declare @c int SET @c = 123456 select isnull(@a, @c) --O/P * If SET @c = 12345--O/P 12345 Since @a size is 5 which is the first parameter.	Multiple datatype is allowed. It took highest data type in the expression list. If we are having int and double. It took double. Eg.1 declare @a varchar(10) declare @b DATETIME SET @b = GETDATE() select coalesce(@a, @b) --O/P 2010-04-13 17:21:12.330 Eg.2declare @a varchar(5) declare @b varchar(8) SET @b = 'SQL SERVER' select coalesce(@a, @b) --O/P SQL SERV It tooks the datatype of the parameter which is not null. Eg.3 declare @a varchar(5) declare @b varchar(8) declare @c int SET @c = 123456 select coalesce(@a, @b, @c) --O/P 123456 DECLARE @a INT DECLARE @b DATETIME SET @a = 2 SET @b = GETDATE() SELECT COALESCE (@a, @b) --O/P 1900-01-03 00:00:00.000 if we set SET @a = 0 --O/P 1900-01-01 00:00:00.000 if we set, it increments month and date SET @a = 35 --O/P 1900-02-05 00:00:00.000

Reference: sqlserver-qa.net

Brute force protect your website

2010-04-08T13:00:00.000+05:30

<head runat="server">
    <title></title>
    <style type="text/css">
        .table
        {
            background-image: url('Images/buttonbg.png');
            background-repeat: repeat;
        }
        .buttonBg
        {
            background-color: Silver;
            background-image: none;
            border-style: solid;
            border-width: 1;
            border-color: #c63 #930 #930 #c63;
        }
        .textbox_username
        {
            background: #ffffff url('images/icon_username.png') no-repeat;
            background-position: 1 1;
            padding-left: 19px;
            border: 1px solid #999999;
            border-top-color: #CCCCCC;
            border-left-color: #CCCCCC;
            color: #333333;
            font: 90% Verdana, Helvetica, Arial, sans-serif;
            font-size: 12px;
            height: 20px;
        }
        .textbox_password
        {
            background: #ffffff url('images/icon_password.png') no-repeat;
            background-position: 1 1;
            padding-left: 19px;
            border: 1px solid #999999;
            border-top-color: #CCCCCC;
            border-left-color: #CCCCCC;
            color: #333333;
            font: 90% Verdana, Helvetica, Arial, sans-serif;
            font-size: 12px;
            height: 20px;
        }
        .button
        {
            border: 1px solid #999999;
            border-top-color: #CCCCCC;
            border-left-color: #CCCCCC;
            background-color: white;
            color: #333333;
            font: 90% Verdana, Helvetica, Arial, sans-serif;
            font-size: 11px;
            -moz-border-radius: 3px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table class="table">
            <tr>
                <td colspan="4">
                    <asp:Label ID="lblUserLogOn" runat="server" Text="User Log On" Style="font-size: smaller;
                        font-family: Verdana; font-weight: bolder;"></asp:Label>
                </td>
            </tr>
            <tr>
                <td rowspan="3">
                    <img src="Images/Security.PNG" style="width: 80px; height: 80px;" alt="" />
                </td>
                <td>
                    <asp:Label ID="lblUserName" runat="server" Text="User Name" Style="font-size: x-small;
                        font-family: Verdana; font-weight: bold;"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtUserName" runat="server" CssClass="textbox_username" TabIndex = "1" ></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblPassword" runat="server" Text="Password" Style="font-size: x-small;
                        font-family: Verdana; font-weight: bold;"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtPassword" runat="server" CssClass="textbox_password" TabIndex = "2"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align:right; padding-right:2px;">
                    <asp:Button ID="btnLogOn" runat="server" Text="LogOn" CssClass="buttonBg" 
                        TabIndex = "3" onclick="btnLogOn_Click"/>
                </td>
            </tr>
            <tr>
            <td colspan="3" style="text-align:right; padding-right:2px;">
                <asp:Label ID="lblInvalid" runat="server" Text="Incorrect username or password." style="color:#FF0000;"></asp:Label>
            </td>
            </tr>
        </table>
    </div>
    
    </form>
</body>
</html>

public partial class SimpleLoginTemplate_LogOn : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {
        //ClearLogonCounter();

        switch (btnLogOn.Enabled)
        {
            case false:
                break;
            case true:
                btnLogOn.Enabled = NumberOfLogonAttemps() <= 5 ? true : false;
                break;
        }
    }

    protected void btnLogOn_Click(object sender, EventArgs e)
    {
        AddOrCountLogonAttempt();

        if (NumberOfLogonAttemps() > 5)
        {
            if (!lblInvalid.Text.Trim().Equals("User has been locked for 5 minutes."))
                //If the attempt is > 5 Lock the user for 5 min
                //After that the userName cleared from the cache
                Cache.Insert(txtUserName.Text.Trim(), (int)Cache[txtUserName.Text.Trim()], null, DateTime.Now.AddMinutes(5), TimeSpan.Zero);

            lblInvalid.Text = "User has been locked for 5 minutes.";
            btnLogOn.Enabled = false;
        }
        else
        {
            btnLogOn.Enabled = true;
            switch (LogOn())
            {
                //Clear the count if user logon correctly
                case true:
                    ClearLogonCounter();
                    break;
                case false:
                    break;
            }
        }
    }

    private bool LogOn()
    {
        lblInvalid.Text = "Incorrect username or password.";
        return false;
    }

    #region "Brute force protect"
    //http://madskristensen.net/post/Brute-force-protect-your-website.aspx
    private int NumberOfLogonAttemps()
    {
        if (Cache[txtUserName.Text.Trim()] == null)
           return 0;
        //txtNoOfTries.Text = Convert.ToString( Cache[txtUserName.Text.Trim()]);
        return (int)Cache[txtUserName.Text.Trim()];
    }

    private void ClearLogonCounter()
    {
        if (Cache[txtUserName.Text.Trim()] != null)
        {
            Cache.Remove(txtUserName.Text.Trim());
        }
    }

    private void AddOrCountLogonAttempt()
    {
        if (Cache[txtUserName.Text.Trim()] == null)
        {
            //NoAbsoluteExpiration -- item should never expire
            // Sliding expiration means we reset the X seconds after each request.
            //http://wiki.asp.net/page.aspx/655/caching-in-aspnet/

            Cache.Insert(txtUserName.Text.Trim(), 1, null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(1));
        }
        else
        {
            int tries = (int)Cache[txtUserName.Text.Trim()];
            Cache[txtUserName.Text.Trim()] = tries + 1;
        }
    }

    #endregion
}

Reference:madskristensen.net
All credits goes to him.

Struct

2010-03-23T17:23:00.000+05:30

Within a struct declaration, fields cannot be initialized unless they are declared as const or static.
```
 
   //public int UserId = 10; //Error
```
A struct may not declare a default constructor (a constructor without parameters) or a destructor. Any private or otherwise inaccessible members can be initialized only in a constructor.
Copy a struct to struct.
If you copy a struct, C# creates a new copy of the object and assigns the copy of the object to a separate struct instance.
Structs are copied on assignment. When a struct is assigned to a new variable, all the data is copied, and any modification to the new copy does not change the data for the original copy. This is important to remember when working with collections of value types such as Dictionary
.
```
 
            Student s1 = new Student();
            Student s2;
            s1 = s2;
            Response.Write(s1.PUserId);
            Response.Write(s2.PUserId);
```
Here all the values of s1 will be copied to s2.
Structs are value types and classes are reference types.

Unlike classes, structs can be instantiated without using a new operator.

 
            Student s1;
            s1.Address = "Savadipalayam";

Initialize the fields through properties. Use of unassigned local variable 's1'.
```
 
Student s1;
Response.Write(s1.PUserId)
```
In fact, when instantiating a struct without the new keyword, we must first initialize its fields explicitly.
```
 
Student s1;
s1.PUserId = 10;
Response.Write(s1.PUserId)
```

Structs can declare constructors that have parameters. But all the field variables should be fully initialzed.

 
        public Student(int UserId, int ZipCode, double Salary, string Address)
        {
            this.UserId = UserId;
            this.ZipCode = ZipCode;
            this.Salary = Salary;
            //this.Address = Address;
        }

This will produce an error Field 'StructTest.Student.Address' must be fully assigned before control is returned to the caller". Since Address is not initialized.

A struct cannot inherit from another struct or class, and it cannot be the base of a class. All structs inherit directly from System.ValueType, which inherits from System.Object.
A struct can implement interfaces, and it does that exactly as classes do.
Cannot convert null to 'StructTest.Student'
```
   Student s2 = null;
```
DateTime is struct. We can't assign null to DateTime.
Cannot convert null to 'System.DateTime' because it is a non-nullable value type.
eg.
```
   DateTime dt = null;
```
A struct can be used as a nullable type and can be assigned a null value.
Working with Nullable Types in struct.
If a field is not initialized whether it is a primitive data type it produce the error "Use of possibly unassigned field 'i'" when we didn’t create instance for the struct.
```
            Student1 s3;
            Response.Write(s3.i);
```

When we create an instance for the struct but we don’t have any constructor to assing value, it will asign the default value for premitive datatype and for others it will assing null(null for string).


            public struct Student1
            {
                  public int i;
                  public string j;
            }

            Student1 s3 = new Student1();
            Response.Write(s3.i); //O/P = 0
            Response.Write(s3.j); //O/P = null

struct can also have copy constructor but have to be fully initialzed.


        public Student(Student stud)
        {
            this.UserId = stud.UserId;
            this.ZipCode = stud.ZipCode;
            this.Salary = stud.Salary;
            
            //Field 'StructTest.Student.Address' must be fully assigned before control is returned to the caller
            //this.Address = stud.Address;

            this.Address = stud.Address;
        }

Struct can contain methods. It may be static, and a static method can call only another static methods.


        public void sub()
        {
            
        }

        public static string multiply()
        {
            return Send();
        }

        public static string Send()
        {
            //An object reference is required for the non-static field, method, or property 'StructTest.Student.Address'
            //Address = "Mottur Road";
            //return Address;

            Street = "Mottur Road";
            return Street;
        }

//The allowed modifiers are new, static, virtual, override, and a valid combination of the four access modifiers (public, internal and private).


  // Override the ToString method so the value appears in text
  public override string ToString()
  {
    return String.Format("({0},{1})", Street, Address);
  }

Support access modifiers, constructors, indexers, methods, fields, nested types, operators, and properties.


        public int PUserId
        {
            get { return UserId; }
            set { UserId = value; }
        }

Elements defined in a namespace cannot be explicitly declared as private, protected, or protected internal.. If we declare a struct inside a namespace with any of the access specifier as private, protected, or protected internal, we will get this error.
So it is always public when we declare a struct inside a namespace.
Abstract and sealed modifiers are not permitted in a struct declaration since it is always implicitly sealed.
Since it is implicitly sealed, struct members may not be declared protected.
So we can't able to inherit another struct.
Since it is not abstract, struct can't be a base to a class or another structure.


    struct Student : IOne, ITwo
    {
        int UserId;
        //public int UserId = 10;  //Within a struct declaration, fields cannot be initialized unless they are declared as const or static.

        public static string Street = "Savadipalayam";

        int ZipCode;
        double Salary;
        public string Address;

        //Structs cannot contain explicit parameterless constructors(Default constructor)
       /* public Student()
        {
            this.UserId = 10;
            this.ZipCode = 637101;
            this.Salary = 2000;
            this.Address = "Idappadi";
        }
        */

        // all the members of the struct has to be initialized in this way
        public Student(int UserId, int ZipCode, double Salary, string Address)
        {
            this.UserId = UserId;
            this.ZipCode = ZipCode;
            this.Salary = Salary;
            this.Address = Address;
        }

        // struct can also have copy constructor but have to be fully initialzed
        public Student(Student stud)
        {
            this.UserId = stud.UserId;
            this.ZipCode = stud.ZipCode;
            this.Salary = stud.Salary;
           
            //Field 'StructTest.Student.Address' must be fully assigned before control is returned to the caller
            //this.Address = stud.Address;

            this.Address = stud.Address;
        }

        public int PUserId
        {
            get { return UserId; }
            set { UserId = value; }
        }

        //new protected member declared in struct
        //protected void add()
        //{
        //}

        //Struct can contain methods. It may be static, and can call only another static methods.
        public void sub()
        {
           
        }

        public static string multiply()
        {
            return Send();
        }

        public static string Send()
        {
            //An object reference is required for the non-static field, method, or property 'StructTest.Student.Address'
            //Address = "Mottur Road";
            //return Address;

            Street = "Mottur Road";
            return Street;
        }

        //The modifier 'abstract' is not valid for this item
        //public abstract void Return() { }

        #region ITwo Members

        void ITwo.add()
        {
            throw new System.NotImplementedException();
        }

        public void sum()
        {
            throw new System.NotImplementedException();
        }

        #endregion

        #region IOne Members

        void IOne.add()
        {
            throw new System.NotImplementedException();
        }

        void IOne.sum()
        {
            throw new System.NotImplementedException();
        }

        #endregion
    }

      //Nested Structures


    public struct outer
    {
        public int i;
        public struct inner
        {
            public int j;
        }
    }

How to call?.


outer.inner inn = new outer.inner();
inn.j = 10;

References
C# School
vijaymukhi
C# Online.net
codeproject
csharp-station

Can't access App_Code class in Code Behind file

2010-03-23T14:51:00.000+05:30

I had created a struct in file in the App_Code. But I can't able to access the namespace.

 
namespace StructTest
{
    public struct Student
    {
        int id;
        int zipcode;
        double salary;
    }
}

Solution
Right click on the file and select properties then change the Build Action to Compile.

Generate Select and Update Procedure

2010-03-19T16:38:00.001+05:30

Generates Select Procedure
Use the this link create-parameters to create paramaters for this.

declare @ProcName varchar(250)
declare @SchemaName varchar(250)
DECLARE @NewLineChar AS CHAR(2)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @VerticalTabChar AS CHAR(1)
DECLARE @ColNames AS varchar(8000)
DECLARE @TableName AS varchar(200)

SET @ProcName = 'Getcompany'
SET @SchemaName = 'dbo'
SET @NewLineChar =  CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
set @VerticalTabChar = CHAR(11)
SET @ColNames = REPLICATE(@HorizontalTabChar, 3)
SET @TableName = 'company'

SELECT
@ColNames = @ColNames + syscolumns.name + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
where sysobjects.ID = OBJECT_ID(@TableName)

SET @ColNames = Left(@ColNames, Len(@ColNames)-6)

PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[' +@SchemaName+ '].[' + @ProcName +']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
+ @NewLineChar + 'BEGIN'
+ @NewLineChar + @HorizontalTabChar + 'DROP PROCEDURE [' +@SchemaName+ '].[' + @ProcName +'] '
+ @NewLineChar + 'END'
+ @NewLineChar + 'GO'
+ @NewLineChar
+ @NewLineChar + 'CREATE PROCEDURE [' +@SchemaName+ '].[' + @ProcName +']'
+ @NewLineChar + 'AS'
+ @NewLineChar + @HorizontalTabChar + ' BEGIN'
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2) + ' SELECT'
+ @NewLineChar + @ColNames
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2)+  'FROM ' + @TableName
+ @NewLineChar + @HorizontalTabChar + ' END'

Generates Update Procedure
To pass parameters to the Update Procedure use the link to generate Parameters.
Generate Parameters

DECLARE @ProcName             VARCHAR(250)
DECLARE @SchemaName           VARCHAR(250)
DECLARE @NewLineChar AS       CHAR(2)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @VerticalTabChar AS   CHAR(1)
DECLARE @ColNames AS          VARCHAR(8000)
DECLARE @TableName AS         VARCHAR(200)

SET @ProcName          = 'zxcvbnm'
SET @SchemaName        = 'dbo'
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @HorizontalTabChar = CHAR(9)
SET @VerticalTabChar   = CHAR(11)
SET @ColNames          = REPLICATE(@HorizontalTabChar, 3)
SET @TableName   = 'company'

SELECT @ColNames       = @ColNames + syscolumns.name + ' = @' + syscolumns.name + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM   sysobjects
JOIN syscolumns
ON     sysobjects.id = syscolumns.id
WHERE  sysobjects.ID        = OBJECT_ID(@TableName)

SET @ColNames = LEFT(@ColNames, LEN(@ColNames)-6)

PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[' +@SchemaName+ '].[' + @ProcName +']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
+ @NewLineChar + 'BEGIN'
+ @NewLineChar + @HorizontalTabChar + 'DROP PROCEDURE [' +@SchemaName+ '].[' + @ProcName +'] '
+ @NewLineChar + 'END'
+ @NewLineChar + 'GO'
+ @NewLineChar
+ @NewLineChar + 'CREATE PROCEDURE [' +@SchemaName+ '].[' + @ProcName +']'
+ @NewLineChar + 'AS'
+ @NewLineChar + @HorizontalTabChar + ' BEGIN'
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 2) + ' UPDATE ' + @TableName
+ @NewLineChar + REPLICATE(@HorizontalTabChar, 3) + 'SET'
+ @NewLineChar + @ColNames
+ @NewLineChar + @HorizontalTabChar + ' END'

Where column alows null

2010-03-18T17:01:00.002+05:30

DECLARE @Test TABLE (ID INT  IDENTITY (1,1),  
Name VARCHAR(250) NOT NULL, Street VARCHAR(250) NULL)

INSERT INTO @Test
SELECT 'Ashok', NULL UNION ALL
SELECT 'Guru', NULL UNION ALL
SELECT 'Babu', 'Idappadi' UNION ALL
SELECT 'Gopi', NULL UNION ALL
SELECT 'Balaji', NULL UNION ALL
SELECT 'Senthil', NULL UNION ALL
SELECT 'Saravanan', NULL UNION ALL
SELECT 'Suresh', NULL

I want to get the records whose street not equal to 'Idappadi'

The query given below returns nothing.

SELECT Name, Street FROM @Test
WHERE Street != 'Idappadi'

Then I modified as below which returns correct result.

SELECT Name, Street FROM @Test
WHERE ISNULL(Street, '') != 'Idappadi'

Get the Last charindex of a char in a string

2010-03-13T18:19:00.001+05:30

DECLARE @TempTable TABLE (NewPK INT identity(1,1), Customer VARCHAR(100) NOT NULL)
DECLARE @SearchChars VARCHAR(10)

SET @SearchChars = '-'

INSERT
INTO  @TempTable (Customer)
SELECT 'BabuKumarasamy-100313-0001' UNION ALL
SELECT 'BabuKumarasamy-1003313-00110' UNION ALL
SELECT 'KrishnaMoorthi-Konganapuram-0020' UNION ALL
SELECT 'Saravanan-100313-0006' UNION ALL
SELECT 'Sankar-100313-0001'

SELECT (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer))) AS LastIndex,
       SUBSTRING (Customer, 0, (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer))) + 1) AS StringBeforeTheIndex,
       SUBSTRING (Customer,(LEN(Customer)- CHARINDEX(@SearchChars, reverse(Customer))) + 2, LEN(Customer) - (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer)))) AS StringAfterTheIndex
FROM   @TempTable
WHERE  Customer LIKE 'BabuKumarasamy%'

null-coalescing operator

2010-03-01T18:54:00.000+05:30

The ?? operator is called the null-coalescing operator and is used to define a default value for a nullable value types as well as reference types. It returns the left-hand operand if it is not null; otherwise it returns the right operand.

If you try to assign a nullable value type to a non-nullable value type without using the ?? operator, you will generate a compile-time error. If you use a cast, and the nullable value type is currently undefined, an InvalidOperationException exception will be thrown.

  
string partner = Request.QueryString["GoogleId"] ??

    Request.QueryString["PartnerId"] ??

    Request.QueryString["UserKey"] ??

    string.Empty;

Copy a ListBox Items to Another ListBox

2010-02-19T16:55:00.000+05:30

private void CopyAListItemToAnotherListItem()
{
ArrayList al = new ArrayList();
for (int i = 97; i < 105; i++)
{
al.Add(Convert.ToChar(i));
}

ListBox1.DataSource = al;
ListBox1.DataBind();

ListBox2.Items.AddRange(ListBox1.Items.Cast().ToArray());
}

The third pillar of object-oriented programming - polymorphism

2010-02-09T12:17:00.000+05:30

The third pillar of object-oriented programming - polymorphism

Static

2010-01-11T08:48:00.000+05:30

Static variables are called Class Fields
Static methods are called Class Members
Not static fields are called instance fields or members

What are the things that we can declare as static?
1.data fields
2.member functions
3.properties
4.events

CLASS
A C# class can contain both static and non-static members. When we declare a member with the help of the keyword static, it becomes a static member.

A static member belongs to the class rather than to the objects of the class. Hence static members are also known as class members and non-static members are known as instance members.

In C#, data fields, member functions, properties and events can be declared either as static or non-static. Remember that indexers in C# can't declare as static.

Static classes cannot be instantiated. (I.e. creating an object). If we try to create an object we will get the error, Cannot create an instance of the abstract class or interface 'StaticClassName'

When a class is declared to as static, it is sealed, abstract, and no instance members can be overridden or declared. A static class contains only static members

Since static classes are sealed so they cannot be inherited.

Advantage
No need to create instance.
When to use static class?
Suppose we are using certain methods or properties often we can just call them without creating instance.
It contains only the last updated value. The updated value reflects to all the users.
Static classes can be used when there is no data or behavior in the class that depends on object identity.

static class A
{
static void Foo()
{

A x = null as A; // Cannot declare a variable of static type 'A'
A a; // Cannot declare a variable of static type 'A'
a = new A(); // Cannot create an instance of the static class 'A'

}
}

Static Fields
Static fields can be declared as follows by using the keyword static.

class MyClass
{
public static int x;
public static int y = 20;
}

When we declare a static field inside a class, it can be initialized with a value as shown above.

The static field for a class executes at most once in a given application domain (i.e. when we keep the breakpoint both at a static and non static field and when we refresh the page, the non static field executes but static field didn't). But the value can be changed later in the constructor or anywhere. The last updated value will be maintained there after.

All un-initialized static fields automatically get initialized to their default values when the class is loaded first time.

The C# provides a special type of constructor known as static constructor to initialize the static data members when the class is loaded at first.

// C# static constructor
// Author: rajeshvs@msn.com
using System;
class MyClass
{
public static int x;
public static int y;
static MyClass()
{
x = 100;
Y = 200;
}
}
class MyClient
{
public static void Main()
{
Console.WriteLine("{0},{1},{2}", MyClass.x, MyClass.y);
}
}

Constructor:
Note that static constructor is called when the class is loaded at the first time. However, we can't predict the exact time and order of static constructor execution.

They are called before an instance of the class is created, before a static member is called and before the static constructor of the derived class is called.

Static class can't have instance constructor. (Since we can’t able to instantiate a static class)

A static constructor must be parameter less. (That means there is only one form of static constructor, without any parameters. In other way it is not possible to overload a static constructor).

There is no access modifier require defining a static constructor.

The static constructor for a class executes at most once in a given application domain (i.e. Static constructors are used to perform a particular action that needs performed once only.) The execution of a static constructor is triggered by the first of the following events to occur within an application domain:

1. An instance of the class is created.
2. Any of the static members of the class are referenced.

Static constructor called before the class is referenced for the first time in your program.
It contains only static variables.

Static constructors can't access non-static data members directly.

(A non-static class can have static constructor. When creating instance the static constructor will be called first. Here also the constructor will be called only once till we restart web server.)

Member Functions
Only static members are allowed inside a static class. (I.e. if a class is declared as static all the fields, members should be static.)

We can invoke a static member only through the name of the class.

But a static member function can access only other static members. (I.e. if a member is declared as static all the fields should be static and the method that we are calling from (the caller method) should be static.)
We can’t able to declare a variable as static inside a static method.

An object reference is required for the non-static field, method, or property.

public class foo
{

int x;

static void doSomthingWithX()
{

Console.Write(x.toString);

}
}

Member modifier 'static' must precede the member type and name

void static doSomthingWithX()

Static classes cannot have instance constructors

Cannot declare instance members in a static class

public static partial class StaticClassTest
{

//Static classes cannot have instance constructors
StaticClassTest()
{
}

//cannot declare instance members in a static class
int j = 10;

static int i = 10;

public static int ClassMethod()
{

//The modifier 'static' is not valid for this item
//static int k = 10;

int k = 10;
return i;

return i;
}

//'InstanceMethod': cannot declare instance members in a static class
public int InstanceMethod()
{
return 8;
}

}

The modifier 'static' is not valid for this item
private static void StaticTest()
{
static int i;
i =10;
}

Solution
Whatever we declare inside static method, it is static by default.. So no need to declare a variable as static in static method.

Static Properties
The properties also can be declared as static. The static properties are accessing using the class name. All the rules applicable to a static member are applicable to static properties.

public class PropertyClass
{
private static int x;
public static int X
{
get
{
return x;
}
set
{
x = value;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
PropertyClass.X = 10; // calls setter
int value = PropertyClass.X; // calls getter
}

Remember that set/get accessor of static property can access only other static members of the class. Also static properties are invoking by using the class name.

Static Members & Inheritance
A derived class can inherit a static member.

But a static member in C# can't be marked as override, virtual or abstract.

However it is possible to hide a base class static method in a derived class by using the keyword new.

public class BaseClassTest
{
public static int i = 10;
public static int get()
{
return i;
}
}

public class ChildClassTest : BaseClassTest
{
public static int j;

//ChildClassTest()
//{
// //cannot be accessed with an instance reference; qualify it with a type name instead
// j = base.i;
//}

//static ChildClassTest()
//{
// //Keyword 'base' is not available in a static method
// j = base.i;
//}

}

class Final : ChildClassTest
{
int p = ChildClassTest.i;
int o = ChildClassTest.get();
int r = ChildClassTest.j;
}

class tester
{
int p = Final.i;
int o = Final.get();
int r = Final.j;
}

Finally remember that it is not possible to use this to reference static methods.
Static Indexers
In C# there is no concept of static indexers, even though static properties are there.
http://www.c-sharpcorner.com/UploadFile/rajeshvs/PropertiesInCS11122005001040AM/PropertiesInCS.aspx

http://www.csharphelp.com/2006/04/c-static-members/

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

2009-12-07T11:44:00.000+05:30

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

1. Consider our table having some row. Now we are deleting the table with query


DELETE FROM table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)

The table will generate an identity value form 1 and not from 0.

2. Now we are truncating the table with TRUNCATE TABLE Statement.


TRUNCATE TABLE table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)

The table will generate an identity value form 0.

During INSERT if there is an error, the row will not Inserted but the Identity value increased according to(IDENT_INCR) the Increment.

Methods to find table INDEX or Statistics age for better performance

2009-12-02T15:31:00.000+05:30

Original Post

 
SELECT OBJECT_NAME(tbl.object_id),
       'Index Name'      = ind.name   ,
       'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM   SYS.INDEXES ind
       INNER JOIN SYS.TABLES tbl
       ON     tbl.OBJECT_ID = ind.object_id
WHERE  tbl.TYPE             = 'U'          
            
--USE babu;
--GO
--EXEC sp_updatestats --To update all STATISTICS in the DB

Create Parameters

2009-12-02T14:25:00.006+05:30

DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
SET @TableName = 'TableTest'
SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +
       CASE
              WHEN DATA_TYPE = 'bigint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'binary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Bit'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Char'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Date'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime2'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTimeOffset'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Decimal'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'float'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'geography'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'geometry'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'hierarchyid'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'image'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'int'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'money'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'numeric'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NVARCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'ntext'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'REAL'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smalldatetime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'smallint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smallmoney'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'sql_variant'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'text'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'time'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'timestamp'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'tinyint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'uniqueidentifier'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'varbinary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'varchar'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'xml'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
       END )           + ','
FROM   INFORMATION_SCHEMA.Columns
WHERE  TABLE_NAME = @TableName

SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)
PRINT @ParamNames

Re Written Query

DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',
@DATA_TYPE  VARCHAR(50) = 'numeric'

SELECT paremeters       = ('@' + column_name + ' ' +
CASE
WHEN data_type   = 'BIGINT'
OR     data_type = 'Bit'
OR     data_type = 'Date'
OR     data_type = 'DateTime'
OR     data_type = 'DateTime2'
OR     data_type = 'DateTimeOffset'
OR     data_type = 'geography'
OR     data_type = 'geometry'
OR     data_type = 'hierarchyid'
OR     data_type = 'image'
OR     data_type = 'smalldatetime'
OR     data_type = 'sql_variant'
OR     data_type = 'time'
OR     data_type = 'timestamp'
OR     data_type = 'uniqueidentifier'
OR     data_type = 'xml'
THEN data_type
WHEN data_type   = 'binary'
OR     data_type = 'Char'
OR     data_type = 'NCHAR'
OR     data_type = 'NVARCHAR'
OR     data_type = 'text'
OR     data_type = 'varbinary'
OR     data_type = 'varchar'
THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN data_type   = 'Decimal'
OR     data_type = 'money'
OR     data_type = 'numeric'
OR     data_type = 'smallmoney'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN data_type   = 'float'
OR     data_type = 'int'
OR     data_type = 'REAL'
OR     data_type = 'smallint'
OR     data_type = 'tinyint'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'
ELSE data_type
END) + ' ,'
FROM   information_schema.columns
WHERE  table_name = @TABLE_NAME
AND
(
CASE
WHEN
(
@DATA_TYPE IS NULL
OR     @DATA_TYPE       = ''
)
THEN 1
END       = 1
OR     data_type = @DATA_TYPE
)

Re Written Query

DECLARE @TableName AS         VARCHAR(200)
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @NewLineChar AS       CHAR(2)
SET @TableName         = 'TableTest'
SET @HorizontalTabChar = CHAR(9)
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @ParamNames        = REPLICATE(@HorizontalTabChar, 3)
SELECT @ParamNames     = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (
                     CASE
                            WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL
                            THEN '('  + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'
                            ELSE '('  + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'
                     END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       INNER JOIN systypes t
       ON     syscolumns.xusertype = t.xusertype
WHERE  sysobjects.ID               = OBJECT_ID(@TableName)
SET @ParamNames                    = LEFT(@ParamNames, LEN(@ParamNames)-6)
PRINT @ParamNames

Another One Not Much Confident

DECLARE @TABLE_NAME VARCHAR(50) 
SET @TABLE_NAME =  'tabletest'

SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +
       CASE
              WHEN syscolumns.prec    IS NULL
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    = 0
              AND    syscolumns.scale = 0
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    > 0
              AND    syscolumns.scale = 0
              THEN ''--CAST(syscolumns.length AS VARCHAR)
              WHEN syscolumns.prec          = 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec          > 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              ELSE '(' + CAST(syscolumns.prec AS   VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'
       END
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       JOIN systypes
       ON     syscolumns.xtype=systypes.xtype
WHERE  sysobjects.xtype       ='U'
AND    sysobjects.name        = @TABLE_NAME

Find the tables which does not have an Identity Key column

2009-11-17T08:45:00.000+05:30

 
SELECT   isc.table_name
FROM     information_schema.columns AS isc
         INNER JOIN sys.tables
         ON       SYS.tables.object_id = object_id(isc.TABLE_NAME)
WHERE    SYS.tables.type               = 'u'
GROUP BY isc.table_name
HAVING   MAX(COLUMNPROPERTY( OBJECT_ID(isc.table_name), isc.column_name,'Isidentity')) =0--( To get the table which doesnot contains identity columns')
ORDER BY isc.table_name

Find the index of row in DataTable

2009-11-16T11:09:00.000+05:30

string query = "Year = " + "2000" + " AND Period = " + "'1'";

int rowIndex = dtSaveAll.Rows.IndexOf(dtSaveAll.Select(query)(0));

Find all SQL Identity columns

2009-11-16T08:29:00.000+05:30

 
SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type
FROM     information_schema.columns
WHERE    columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
ORDER BY table_name


SELECT   '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
         table_name                                                            ,
         column_name                                                           ,
         ordinal_position                                                      ,
         data_type                                                             ,
         Seed_Value                               AS SeedValue                                               ,
         Increment_Value                          AS IncrementValue                                          ,
         ident_current(Object_Name(ic.Object_ID)) AS CurrentValue                                            ,
         Last_Value                               AS LastValue
FROM     information_schema.columns               AS isc
         INNER JOIN sys.identity_columns          AS ic
         ON       ic.object_id = OBJECT_ID('['+isc.table_schema+'].['+isc.table_name+']')
         INNER JOIN sys.tables
         ON       SYS.tables.object_id                                                            = ic.object_id
WHERE    columnproperty(object_id(isc.table_schema + '.' + table_name), column_name,'IsIdentity') = 1 --( To get the table which contains only identity columns')
     AND SYS.tables.type                                                                          = 'u'
ORDER BY table_name

Find table and index name for fragmented indexes

2009-11-05T16:08:00.000+05:30


;
WITH GetFragmatation AS
     ( SELECT object_id                   AS ObjectID       ,
             OBJECT_NAME(object_id)       AS TableName      ,
             index_id                     AS IndexID        ,
             avg_fragmentation_in_percent AS PercentFragment,
             fragment_count               AS TotalFrags     ,
             avg_fragment_size_in_pages   AS PagesPerFrag   ,
             page_count                   AS NumPages
     FROM    sys.dm_db_index_physical_stats(DB_ID('RKPMS'), NULL, NULL, NULL , 'DETAILED')
     WHERE   avg_fragmentation_in_percent > 0
     )
SELECT gf.ObjectID                                              ,
       '['+SCHEMA_NAME(schema_id)+'].['+Sch.name+']' AS FullName,
       gf.TableName                                             ,
       gf.IndexID                                               ,
       IND.name AS IndexName                                    ,
       gf.PercentFragment                                       ,
       gf.TotalFrags                                            ,
       gf.PagesPerFrag                                          ,
       gf.NumPages
FROM   GetFragmatation        AS gf
       INNER JOIN sys.indexes AS ind
       ON     ind.object_id = gf.ObjectID
          AND index_id      = gf.IndexID
       INNER JOIN sys.tables AS Sch
       ON     Sch.object_id = gf.ObjectID
              --Use the below one to defragment the Index
              ALTER INDEX PK_TableNameIndexName
       ON     [dbo].[TableName]REBUILD WITH ( FILLFACTOR = 70, ONLINE = ON )

--To alter all the index for a table
--ALTER INDEX ALL ON TableName REBUILD


SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent AS [Percentage of the logical index that is fragmented], 
   IPS.avg_fragment_size_in_pages AS [Average number of pages in a leaf-level fragment.], 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count AS [Total number of records], 
   IPS.ghost_record_count,
   IPS.fragment_count AS [Number of fragments in the leaf level.], 
   IPS.avg_fragment_size_in_pages AS PagesPerFrag,
   IPS.page_count AS [Number of index or data pages.]
FROM sys.dm_db_index_physical_stats(db_id(N'Rkpms'), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND ST.TYPE = 'U'
ORDER BY 4 DESC

List Everything in My Database

2009-11-05T12:37:00.000+05:30

 
SELECT B.Name as TableName,A.name as TriggerName
FROM sysobjects A,sysobjects B
WHERE A.xtype='TR'
AND A.parent_obj = B.id

Here are the list of all possible values for this column (xtype):

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TR = Trigger

U = User table

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure

To list the triggers

 

SELECT S2.[name] TableName, S1.[name] TriggerName, 
CASE 
WHEN S2.deltrig = s1.id  THEN 'Delete' 
WHEN S2.instrig = s1.id THEN 'Insert' 
WHEN S2.updtrig = s1.id THEN 'Update' 
END 'TriggerType' , 'S1',s1.*,'S2',s2.*
FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

Path

2009-10-12T15:45:00.000+05:30

Making Sense of ASP.NET Paths
Full URL : http://localhost:2000/virtual_dir/myrep/page.aspx?q=qvalue
Original Path

Request.ApplicationPath :	/virtual_dir
Request.CurrentExecutionFilePath :	/virtual_dir/myrep/page.aspx
Request.FilePath :	/virtual_dir/myrep/page.aspx
Request.Path :	/virtual_dir/myrep/page.aspx
Request.PhysicalApplicationPath :	d:\Inetpub\wwwroot\Websitename\virtual_dir\
Request.QueryString :	/virtual_dir/myrep/page.aspx?q=qvalue
Request.Url.AbsolutePath :	/virtual_dir/myrep/page.aspx
Request.Url.AbsoluteUri :	http://localhost:2000/virtual_dir/myrep/page.aspx?q=qvalue
Request.Url.Host :	localhost
Request.Url.Authority :	localhost:2000
Request.Url.LocalPath :	/virtual_dir/myrep/page.aspx
Request.Url.PathAndQuery :	/virtual_dir/myrep/page.aspx?q=qvalue
Request.Url.Port :	2000
Request.Url.Query :	?q=qvalue
Request.Url.Scheme :	http
Request.Url.Segments :	/
	virtual_dir/
	myrep/
	page.aspx

Custom PrimaryKey Based on IdentityValue

2009-10-07T14:45:00.000+05:30

 
CREATE TABLE [dbo].[PrimaryKeyTest](
 [ID] [int] IDENTITY (1,1),
 [PKV] [nchar](50) NOT NULL
) ON [PRIMARY]

GO


DECLARE @CustomPKV NVARCHAR(50)

SELECT @CustomPKV = (
CASE WHEN (ISNULL(MAX(Id), -1) = -1) THEN 1
 WHEN MAX(Id) >= 1 THEN MAX(Id) + 1
END 
)
FROM [dbo].[PrimaryKeyTest]

PRINT @CustomPKV

INSERT INTO [dbo].[PrimaryKeyTest] (PKV) VALUES(@CustomPKV)


SELECT ID, PKV FROM [dbo].[PrimaryKeyTest]


--To CHAR DATA TYPE 'R' +  CAST(
--(CASE WHEN (ISNULL(MAX(Id), -1) = -1) THEN 1
-- WHEN MAX(Id) >= 1 THEN MAX(Id) + 1
--END)  AS VARCHAR)

Numeric Validation in Javascript

2009-10-01T11:50:00.000+05:30

/*
NUMERIC
prefix --> For number of digits before .(Period)
If you didn't give the period also it allows only the digits that given in the prefix part

suffix --> For the number of digits after the .(Period)

NumberOnly
If we specify suffix as 0 it allows only integer
*/

  
function NumericValidation(ctrlName, e, prefix, suffix) {

    var evt = e || window.event;
    var code = e.charCode ? e.charCode : e.keyCode;
    var EndKey = 35;
    var HomeKey = 36;
    var NumeriPeriodKey = 110;
    var PeriodKey = 190;
    if (e.shiftKey || e.ctrlKey) {
        return false;
    }


    //debugger;
    // IE
    if (evt.type == 'keydown') {

        if ((code == EndKey || code == HomeKey || code == 37 || code == 38 || code == 39 || code == 40 || code == 9)) {
            return true;
        }

        if (code == 46 || code == 8) {
            var val = ctrlName.value;
            var pos = val.indexOf('.');
            var lenofBox = val.length;

            if (code == 8) {
                //Since back key deletes backward
                if (doGetCaretPosition(ctrlName) == (pos + 1)) {

                    ctrlName.value = val.substring(0, pos)
                }
                else {
                    return true;
                }
            }
            else (code == 46)
            {
                //Since del key deletes forward
                if (doGetCaretPosition(ctrlName) == pos) {

                    ctrlName.value = val.substring(0, pos)
                }
                else {
                    return true;
                }
            }
        }

        if ((evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96 && evt.keyCode <= 105) || (code == PeriodKey || code == NumeriPeriodKey)) {

            var val = ctrlName.value;

            if (val != "") {
                var pos = val.indexOf('.');
                var lenofBox = val.length;

                //To allow only int
                if ((suffix == 0) && (code == PeriodKey || code == NumeriPeriodKey)) {
                    return false;
                }

                //if already period is available
                if ((code == PeriodKey || code == NumeriPeriodKey) && (pos > -1)) {
                    return false;
                }

                //if prefix reaches and the current one is not period the stop the event
                if ((pos == -1 && lenofBox == prefix) && (code != PeriodKey && code != NumeriPeriodKey)) {
                    return false;
                }

                if (doGetCaretPosition(ctrlName) > pos) {

                    if (pos > -1) {
                        var decSting = val.substring(pos + 1, lenofBox + 1);
                        // debugger;
                        //alert(doGetCaretPosition(document.getElementById('TextBox1')));
                        if (decSting.length == suffix) {
                            return false;
                        }
                    }
                }
                else if (pos >= prefix) {
                    return false;
                }

            }

            // alert(document.getElementById('TextBox1').value + String.fromCharCode(evt.keyCode));
            return true;
        }
        else {
            //  alert(document.getElementById('TextBox1').value);
            return false;
        }
    }
    else if (evt.type == 'keypress') {

        if ((code == 37 || code == 38 || code == 39 || code == 40) || (code == 46 || code == 8 || code == 9)) {
            return true;
        }

        if ((evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96 && evt.keyCode <= 105) || (code == PeriodKey || code == NumeriPeriodKey)) {

            var val = ctrlName.value;

            if (val != "") {
                var pos = val.indexOf('.');
                var lenofBox = val.length;

                //To allow only int
                if ((suffix == 0) && (code == PeriodKey || code == NumeriPeriodKey)) {
                    return false;
                }

                //if already period is available
                if ((code == PeriodKey || code == NumeriPeriodKey) && (pos > -1)) {
                    return false;
                }

                //if prefix reaches and the current one is not period the stop the event
                if ((pos == -1 && lenofBox == prefix) && (code != PeriodKey || code != NumeriPeriodKey)) {
                    return false;
                }

                if (doGetCaretPosition(ctrlName) > pos) {

                    if (pos > -1) {
                        var decSting = val.substring(pos + 1, lenofBox + 1);
                        // debugger;
                        //alert(doGetCaretPosition(document.getElementById('TextBox1')));
                        if (decSting.length == suffix) {
                            return false;
                        }
                    }
                }
                else if (pos >= prefix) {
                    return false;
                }
            }

            // alert(document.getElementById('TextBox1').value + String.fromCharCode(evt.keyCode));
            return true;
        }
        else {
            //   alert(document.getElementById('TextBox1').value);
            return false;
        }
    }
}

function doGetCaretPosition(ctrl) {
    var CaretPos = 0; // IE Support
    if (document.selection) {
        ctrl.focus();
        var Sel = document.selection.createRange();
        Sel.moveStart('character', -ctrl.value.length);
        CaretPos = Sel.text.length;
    }
    // Firefox support
    else if (ctrl.selectionStart || ctrl.selectionStart == '0') CaretPos = ctrl.selectionStart;
    return (CaretPos);
}

Numeric Validation in Javascript

2009-10-01T11:39:00.000+05:30


/* Babu Kumarasamy
NUMERIC
prefix --> For number of digits before .(Period)
If you didn't give the period also it allows only the digits that given in the prefix part
       
suffix --> For the number of digits after the .(Period)

NumberOnly
If we specify suffix as  0 it allows only integer
*/

function NumericValidation(ctrlName, e, prefix, suffix) {

  var evt = e || window.event;
  var code = e.charCode ? e.charCode : e.keyCode;
  var EndKey = 35;
  var HomeKey = 36;
  var NumeriPeriodKey = 110;
  var PeriodKey = 190;
  if (e.shiftKey || e.ctrlKey) {
      return false;
  }


  //debugger;
  // IE
  if (evt.type == 'keydown') {

      if ((code == EndKey || code == HomeKey || code == 37 || code == 38 || code == 39 || code == 40 || code == 9)) {
          return true;
      }

      if (code == 46 || code == 8) {
          var val = ctrlName.value;
          var pos = val.indexOf('.');
          var lenofBox = val.length;

          if (code == 8) {
              //Since back key deletes backward
              if (doGetCaretPosition(ctrlName) == (pos + 1)) {

                  ctrlName.value = val.substring(0, pos)
              }
              else {
                  return true;
              }
          }
          else (code == 46)
          {
              //Since del key deletes forward
              if (doGetCaretPosition(ctrlName) == pos) {

                  ctrlName.value = val.substring(0, pos)
              }
              else {
                  return true;
              }
          }
      }

      if ((evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96 && evt.keyCode <= 105) || (code == PeriodKey || code == NumeriPeriodKey)) {

          var val = ctrlName.value;

          if (val != "") {
              var pos = val.indexOf('.');
              var lenofBox = val.length;

              //To allow only int
              if ((suffix == 0) && (code == PeriodKey || code == NumeriPeriodKey)) {
                  return false;
              }

              //if already period is available
              if ((code == PeriodKey || code == NumeriPeriodKey) && (pos > -1)) {
                  return false;
              }

              //if prefix reaches and the current one is not period the stop the event
              if ((pos == -1 && lenofBox == prefix) && (code != PeriodKey && code != NumeriPeriodKey)) {
                  return false;
              }

              if (doGetCaretPosition(ctrlName) > pos) {

                  if (pos > -1) {
                      var decSting = val.substring(pos + 1, lenofBox + 1);
                      // debugger;
                      //alert(doGetCaretPosition(document.getElementById('TextBox1')));
                      if (decSting.length == suffix) {
                          return false;
                      }
                  }
              }
              else if (pos >= prefix) {
                  return false;
              }

          }

          // alert(document.getElementById('TextBox1').value + String.fromCharCode(evt.keyCode));
          return true;
      }
      else {
          //  alert(document.getElementById('TextBox1').value);
          return false;
      }
  }
  else if (evt.type == 'keypress') {

      if ((code == 37 || code == 38 || code == 39 || code == 40) || (code == 46 || code == 8 || code == 9)) {
          return true;
      }

      if ((evt.keyCode >= 48 && evt.keyCode <= 57) || (evt.keyCode >= 96 && evt.keyCode <= 105) || (code == PeriodKey || code == NumeriPeriodKey)) {

          var val = ctrlName.value;

          if (val != "") {
              var pos = val.indexOf('.');
              var lenofBox = val.length;

              //To allow only int
              if ((suffix == 0) && (code == PeriodKey || code == NumeriPeriodKey)) {
                  return false;
              }

              //if already period is available
              if ((code == PeriodKey || code == NumeriPeriodKey) && (pos > -1)) {
                  return false;
              }

              //if prefix reaches and the current one is not period the stop the event
              if ((pos == -1 && lenofBox == prefix) && (code != PeriodKey || code != NumeriPeriodKey)) {
                  return false;
              }

              if (doGetCaretPosition(ctrlName) > pos) {

                  if (pos > -1) {
                      var decSting = val.substring(pos + 1, lenofBox + 1);
                      // debugger;
                      //alert(doGetCaretPosition(document.getElementById('TextBox1')));
                      if (decSting.length == suffix) {
                          return false;
                      }
                  }
              }
              else if (pos >= prefix) {
                  return false;
              }
          }

          // alert(document.getElementById('TextBox1').value + String.fromCharCode(evt.keyCode));
          return true;
      }
      else {
          //   alert(document.getElementById('TextBox1').value);
          return false;
      }
  }
}

function doGetCaretPosition(ctrl) {
  var CaretPos = 0; // IE Support
  if (document.selection) {
      ctrl.focus();
      var Sel = document.selection.createRange();
      Sel.moveStart('character', -ctrl.value.length);
      CaretPos = Sel.text.length;
  }
  // Firefox support
  else if (ctrl.selectionStart || ctrl.selectionStart == '0') CaretPos = ctrl.selectionStart;
  return (CaretPos);
}

/* Babu Kumarasamy */

DataTable Creation

2009-08-14T15:53:00.004+05:30



    public class DataTableHelper
    {
        /// 
        /// To Create Table Skeleton.
        /// 

        /// Table Column Name as an Array/// Column DataType as an Array/// Name of the DataTable/// 
        public DataTable CreateDataTable(string[] ColumnName, string[] DataType, string TableName)
        {
            // Create DataTable.   
            DataTable table = new DataTable(TableName);
            try
            {
                for (int i = 0; i < ColumnName.Length; i++)
                {
                    // Create DataColumns and add them to DataTable.   
                    table.Columns.Add(CreateDataColumn(DataType[i].Trim(), ColumnName[i].Trim(), "Column", false, false, false));
                }
            }
            catch (Exception)
            {
            }
            return table;
        }

        /// 
        /// To create the table structure
        /// 

        /// Column DataType/// ColumnName/// Column Caption/// Is Auto Increament/// IsReadOnly/// /// 
        private DataColumn CreateDataColumn(string colType, string name, string caption, bool autoInc, bool readOnly, bool unique)
        {
            DataColumn column = new DataColumn();
            try
            {
                column.DataType = System.Type.GetType(colType);
                column.ColumnName = name;
                column.Caption = caption;
                column.AutoIncrement = autoInc;
                column.ReadOnly = readOnly;
                column.Unique = unique;
            }
            catch (Exception)
            {
            }

            return column;
        }

        /// 
        /// Add Row to a DataTable
        /// //How to Call?
        /// //tempDt = oblDataTableHelper.AddRowsToTable(strArrField, tempDt);
        /// 

        /// Column Data as String Array/// Name of DataTable to add the Row/// 
        public DataTable AddRowsToTable(string[] value, DataTable table)
        {
            // Generate row with values for each column.   
            DataRow row = table.NewRow();
            try
            {
                //Add values to the columns
                for (int i = 0; i < value.Length; i++)
                    row[i] = value[i];

                // Add row to table.   
                table.Rows.Add(row);
            }
            catch (Exception)
            {
            }

            return table;
        }

        /// 
        /// Check whether the table contains Duplicate value for a particular column
        /// 

        /// DataTable/// Name of Column to Check for Duplicate/// 
        public bool HasDuplicates(DataTable table, string columnName)
        {
            bool IsContainsDuplicates = false;
            try
            {
                Hashtable hTable = new Hashtable();

                foreach (DataRow row in table.Rows)
                    if (!hTable.Contains(row[columnName]))
                        hTable.Add(row[columnName], null);
                    else
                    {
                        IsContainsDuplicates = true;
                        break;
                    }
            }
            catch (Exception)
            {
            }

            return IsContainsDuplicates;
        }

        /// 
        /// Remove rows for certains columns contains duplicate value
        /// //How to Call?
        /// tempDt = oblDataTableHelper.RemoveDuplicateRows(tempDt, new System.Collections.Generic.List(ColumnName));
        /// 

        /// DataTable Instance/// ColumnName as List/// 
        public DataTable RemoveDuplicateRows(DataTable table, List keyColumns)
        {
            //DataTable results = myDataTable.AsEnumerable().Distinct().CopyToDataTable();

            DataTable duplicateTable = table.Clone();
            DataColumn[] primaryKey = new DataColumn[duplicateTable.Columns.Count];
            duplicateTable.Columns.CopyTo(primaryKey, 0);
            duplicateTable.PrimaryKey = primaryKey;

            DataRow[] dataRows = new DataRow[table.Rows.Count];
            table.Rows.CopyTo(dataRows, 0);
            foreach (DataRow dataRow in dataRows)
                if (duplicateTable.Rows.Contains(dataRow.ItemArray))
                    table.Rows.Remove(dataRow);
                else
                    duplicateTable.Rows.Add(dataRow.ItemArray);

            return table;
        }

        //tempDt = oblDataTableHelper.RemoveDuplicateRows(tempDt, ColumnName);
        /// 
        /// Remove rows for certains columns contains duplicate value
        /// 

        /// DataTable Instance/// ColumnName as Array/// 
        public DataTable RemoveDuplicateRows(DataTable dTable, string[] strColumns)
        {
            // create a dv from the source dt 
            DataView dv = new DataView(dTable);
            // set the output columns array of the destination dt 
            // true = yes, i need distinct values. 
            return dv.ToTable(true, strColumns);
        }
    }

Calling The Function


DataTableHelper oblDataTableHelper = new DataTableHelper();
string[] ColumnName = new string[] { "Column1", "Column2", "Column3", "Column4" };
string[] DataType = new string[] { "System.String", "System.String", "System.String", "System.String" };
System.Data.DataTable tempDt = null;
tempDt = oblDataTableHelper.CreateDataTable(ColumnName, DataType, "DataTableName");

Path that gives me the idea

Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during

2009-08-10T13:38:00.000+05:30

Failed to load viewstate.
match the control tree that was used to save viewstate
during the previous request. For example, when adding
controls dynamically, the controls added during a
post-back must match the type and position of the
controls added during the initial request.

This occurs in when press the command field(Edit button) 
in the gridview.

To solve we have to bind the GridView once again.

 
protected void GridView1_RowEditing(object sender, 
System.Web.UI.WebControls.GridViewEditEventArgs e)
{
    GridView1.EditIndex = e.NewEditIndex;
    BindGrid();
}

Image to Byte Array and Byte Array to Image

2009-07-30T18:43:00.001+05:30

http://www.codeproject.com/KB/recipes/ImageConverter.aspx

 
public byte[] imageToByteArray(System.Drawing.Image imageIn)
{
 MemoryStream ms = new MemoryStream();
 imageIn.Save(ms,System.Drawing.Imaging.ImageFormat.Gif);
 return  ms.ToArray();
}

public Image byteArrayToImage(byte[] byteArrayIn)
{
     MemoryStream ms = new MemoryStream(byteArrayIn);
     Image returnImage = Image.FromStream(ms);
     return returnImage;
}

HttpModule Execution Sequence

2009-07-30T17:19:00.000+05:30

An application executes events that are handled by modules or user code that is defined in the Global.asax file in the following sequence:

1. BeginRequest
2. AuthenticateRequest
3. PostAuthenticateRequest
4. AuthorizeRequest
5. PostAuthorizeRequest
6. ResolveRequestCache
7. PostResolveRequestCache

After the PostResolveRequestCache event and before the PostMapRequestHandler event, an event handler (a page corresponding to the request URL) is created.

8. PostMapRequestHandler
9. AcquireRequestState
10. PostAcquireRequestState
11. PreRequestHandlerExecute

The event handler is executed.

12. PotRequestHandlerExecute
13. ReleaseRequestState
14. PostReleaseRequestState

After the PostReleaseRequestState event, response filters, if any, filter the output.

15. UpdateRequestCache
16. PostUpdateRequestCache
17. EndRequest

byte array to bitmap

2009-07-29T18:52:00.000+05:30

http://social.msdn.microsoft.com/Forums/en-US/winforms/thread/e57f7731-c703-4c17-b1a2-32b155f9b745

 
    public static Bitmap BytesToBitmap(byte[] byteArray)
   {
       using (MemoryStream ms = new MemoryStream(byteArray))
       {
           Bitmap img = (Bitmap)Image.FromStream(ms);
           return img;
       }
   }


TypeConverter tc = TypeDescriptor.GetConverter(typeof(Bitmap));

Bitmap bitmap1 = (Bitmap)tc.ConvertFrom(byteArray);

Or,



ImageConverter ic = new ImageConverter();

Image img = (Image)ic.ConvertFrom(byteArray);

Bitmap bitmap1 = new Bitmap(img);

Tips

2009-07-24T13:56:00.000+05:30

Tip #1 How to behold the power of incremental search

Command: Edit.IncrementalSearch

Shortcut: Ctrl+i

Tip #2 Ctrl+F3 to search for currently-selected word

Command: Edit.FindNextSelected

Tip #3 F3 to search for last thing you searched for

Command: Edit.FindNext

Tip #4 Customize what files to find in

Find In Files – Look in – Choose Search Folders

Tip #5 You can use a reg hack for customizing search results

HKCU\Software\Microsoft\VisualStudio\9.0\Find String Find=$f$e($l,$c):$t\r\n

Editing Tips

Tip #6 How not to accidentally copy a blank line

Tools – Options – Text Editor – All Languages – General, Uncheck Apply cut or copy to blank lines

Tip #7 How to cycle through the Clipboard ring

Command: Edit.CycleClipboardRing

Shortcut: Ctrl+Shift+v

Tip #8 How to use box/column selection in the editor

Command: Edit.LineUpColumnExtend, Edit.LineDownColumnExtend, Edit.CharRightColumnExtend, Edit.CharLeftColumnExtend

Shortcut: Shift+Alt+Arrow

Tip #9 You can copy a file’s full path / open windows explorer from the file tab channel

Command: File.CopyFullPath

Tip #10 Drag and drop code onto the toolbox’s general tab

Shortcut: Ctrl+Alt+x

Tip #11 You can use Ctrl+. to show a smart tag

Command: View.ShowSmartTag

Tip #12 You can insert a snippet by pressing Tab Tab

Type in snippet shortcut, then press Tab Tab

Customizing Tips

Tip #13 You can create temp or throw away projects

Tools - Options - Projects and Solutions - General, uncheck Save new projects when created

Tip #14 Change text editor font size via keyboard (Accessibility macros)

Command: Macros.Samples.Accessibility.DecreaseTextEditorFontSize

Command: Macros.Samples.Accessibility.IncreaseTextEditorFontSize

Tip #15 How to open a file without any UI

Ctrl+/ (or whatever Tools.GoToCommandLine is bound to)

alias fo file.openfile

fo

Tip #16 Guidelines in the editor registry key hack

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\Text Editor String RBG(128,0,0) 5, 20

Tip #17 You can create a macro for your import/export settings

Tools – customize – commands – macros – drag and drop macro to toolbar

Tip #18 How to not show the start page (or have the last loaded solution open)

Tools - Options - Environment - Startup, At Startup

Tip #19 File tab channel registry hack

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0 key, you can create a DWORD UseMRUDocOrdering = 1

Tip #20 How to show Misc Files Project to keep your files around

tools - options - environment – documents, show miscellaneous files in Solution Explorer

Tip #21 Edit project file from within IDE (unload project)

Unload project, edit project, reload project

Debugging Tips

Tip #22 You can use tracepoints to log stuff in your code

Right-click in indicator margin, select breakpoints, select Insert Tracepoint

Tip #23 How to get the find source dialog back

Solution Properties, under Common Properties - Debug Source Files, Delete Do no look for these source files edit box contents

Tip #24 You can disable the exception assistant

Tools – Options – Debugging – General, uncheck Enable the Exception Assistant