Dotnet Codes

Some tricky SQL queries Part 2 (Interview Questions)

2013-07-02T12:07:00.000+05:30

1.Update Employee salary Department wise in single query

update tblEMP
set salary=
case when dept='HR' then
salary + (salary*10)/100 
when dept='IT' then
salary + (salary*20)/100 
when dept='Admin' then
salary + (salary*5)/100 
when dept='SAP' then
salary + (salary*2)/100 
end

2.Get 3 Max salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

3.Get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

4. Count of number of employees in department wise

select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

5. Delete duplicate rows in a table

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);

Drop all the tables, stored procedures, triggers, constriants and all dependencies from SQL Server 2005

2012-07-29T13:36:00.000+05:30

Drop all the tables, stored procedures, triggers, constriants and all dependencies from SQL Server 2005

Code


/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Sorting asp.net Gridview using Jquery

2012-04-12T16:59:00.000+05:30

Tablesorter is a Jquery pulgin easy to use. Now you can sort your data without postbacking the page.It gives the client side sorting functionality.
Click to check more on Tablesorter

Code

Default.aspx

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="jqueryTableSort._Default" %>


    
    
    
    


          Sort gridview using Jquery plugin Table Sorter

Default.aspx.cs

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

namespace jqueryTableSort
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) 
            {
                BindGrid();
                
            }

        }
        #region BIND GRID
        private void BindGrid() 
        {
            CountryDataData objData = new CountryDataData();
            GridView1.DataSource = objData.GetCountryDataList();
            GridView1.DataBind();
            GridView1.UseAccessibleHeader = true;
            GridView1.HeaderRow.TableSection = TableRowSection.TableHeader; 

        }
        #endregion
    }
}

CountryData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace jqueryTableSort
{
    public class CountryDataData
    {
        public int Id { get; set; }
        public string CountryName { get; set; }
        public List GetCountryDataList()
        {
            return new List
            {
                new CountryDataData{Id=1,CountryName="India"},
                new CountryDataData{Id=2,CountryName="Australia"},
                new CountryDataData{Id=3,CountryName="UK"},
                new CountryDataData{Id=4,CountryName="UAE"},
                new CountryDataData{Id=5,CountryName="Bangladesh"},
                new CountryDataData{Id=6,CountryName="Austria"},
                new CountryDataData{Id=7,CountryName="Japan"},
                new CountryDataData{Id=8,CountryName="China"},
                new CountryDataData{Id=9,CountryName="Dubai"},
                new CountryDataData{Id=10,CountryName="South Africa"},
                new CountryDataData{Id=11,CountryName="Mexcico"},
                new CountryDataData{Id=12,CountryName="Merryland"},
                new CountryDataData{Id=13,CountryName="USA"},
                new CountryDataData{Id=14,CountryName="Peru"},
                new CountryDataData{Id=15,CountryName="Nepal"},
                new CountryDataData{Id=16,CountryName="Pakistan"},
                new CountryDataData{Id=17,CountryName="Srilanka"},
                new CountryDataData{Id=18,CountryName="Vietnam"},
                new CountryDataData{Id=19,CountryName="Westindies"},
                new CountryDataData{Id=20,CountryName="England"},
                new CountryDataData{Id=21,CountryName="Afganistan"},
                new CountryDataData{Id=22,CountryName="Russia"},
                new CountryDataData{Id=23,CountryName="Newzeland"},
                new CountryDataData{Id=24,CountryName="Timore"},
                new CountryDataData{Id=25,CountryName="Canada"}

            };

        }

    }
}

Download Code

Creating ZIP and UNZIP files in ASP.NET using DotNetZip library.

2012-04-02T12:27:00.000+05:30

DotNetZip is easy to use free class library for ziping and extracing the zip files.
Check this DotNetZip for more information.

Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ionic.Zlib;
using Ionic.Zip;
using System.IO;




namespace ZipFilesAspDotnet
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        #region Download ZIP File
        private void DownloadFile()
        {
            if (FileUpload1.HasFile)
            {
                //file upload folder App_Data
                string _fileName = FileuploadUtility.UploadFile(FileUpload1, Server.MapPath("~/App_Data/"), Session.SessionID);
                string[] _zip_fileName = _fileName.ToString().Split('.');
                Response.Clear();
                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "filename=" + "download_" + _zip_fileName.ToString() + ".zip");

                using (ZipFile zip = new ZipFile())
                {
                    zip.AddEntry(_fileName.ToString(), File.ReadAllBytes(Server.MapPath("~/App_Data/" + _fileName.ToString())));
                    zip.Save(Response.OutputStream);
                }
            }

        }
        #endregion

        #region EXTRACT THE ZIP FILE
        private void ExtractZipFile() 
        {
            string _fileName = FileuploadUtility.UploadFile(FileUpload1, Server.MapPath("~/App_Data/"), Session.SessionID);

            using (ZipFile zip1 = ZipFile.Read(Server.MapPath("~/App_Data/" + _fileName.ToString())))
            {
                
                foreach (ZipEntry e in zip1)
                {
                    //destination folder zipfiles
                    e.Extract(Server.MapPath("~/ZipFiles/"), ExtractExistingFileAction.OverwriteSilently);
                }
            }

        }
        #endregion

        protected void Button1_Click(object sender, EventArgs e)
        {
            DownloadFile();

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            ExtractZipFile();
        }
    }
}

Download Sample

Change DB owner in SQL Server Database

2012-02-05T15:21:00.000+05:30

Change db owner in sql server 2005 database.

Example

DECLARE @old sysname, @sql varchar(1000)

SELECT

 @old = 'oldOwner_CHANGE_THIS'

 , @sql = '

 IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

 WHERE

     QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

     AND TABLE_SCHEMA = ''' + @old + '''

 )

 ALTER SCHEMA dbo TRANSFER ?'

EXECUTE sp_MSforeachtable @sql

Dotnet Codes

2012-02-02T15:55:00.000+05:30

Dotnet Codes

Difference between ref and out parameters in c#

2011-10-13T11:44:00.000+05:30

The difference between ref and out parameters is at the language level. In case of ref you must have to assign it as in "Example by ref" string val is assigned.
In case of out you don't need to assign an out parameter before passing it to a method. But the out parameter in that method must assign the parameter before returning.

Example

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
        Response.Write("Example by ref");
        string val = "hello";
        ExampleByRef(ref  val);
        Response.Write(val);
        Response.Write("Example by out
");
        string vals;
        ExampleByOut(out  vals);

    }
    public static void ExampleByRef(ref string val)
    {
        if (val == "hello")
        {

            System.Web.HttpContext.Current.Response.Write("Example by ref case");
        }
        else
        { System.Web.HttpContext.Current.Response.Write("Example by ref else response"); }
        val = "hi";
    }
    public static void ExampleByOut(out string vals)
    {
        vals = "Example by Out case
";
        System.Web.HttpContext.Current.Response.Write(vals);
    }
     
 }

Output
Example by ref
Example by ref case
hi
Example by out
Example by Out case

Track User status if user closes the browser window.

2011-09-14T14:03:00.001+05:30

This is a sample application of "User Status" tracking in asp.net. When the user is logged in. This will show the user status as online user. If the user click on logout then it automatically logout user and update the user status as off line. But what if the user closes the browser then also you can track the status.
Here in this example I have used SQL server as database. I have created a table [tbluserlogins] in database with a "userstatus" column as bit datatype. Every time I update the "userstatus" when user logged in and logged out.

Sample Table Structure

CREATE TABLE [dbo].[tblUserLogins](
 [sno] [int] IDENTITY(1,1) NOT NULL,
 [username] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [password] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [userStatus] [bit] NOT NULL,
 [lastLogin] [datetime] NOT NULL CONSTRAINT [DF_tblUserLogins_lastLogin]  DEFAULT (getdate()),
 CONSTRAINT [PK_tblUserLogins] PRIMARY KEY CLUSTERED 
(
 [username] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

1. We have to maintain the session state like this

2. In global.asax there we can update the status.

void Application_End(object sender, EventArgs e)
    {
        //  Code that runs on application shutdown
        //Update the user status if the application is closed

        try
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.mainConnectionString, System.Data.CommandType.Text, "update tblUserLogins set userStatus='false' where sno=" + Session["userid"].ToString(), null);
        }
        catch (Exception ex)
        {

            throw ex;
        }


    }

void Session_End(object sender, EventArgs e)
    {
        // Code that runs when a session ends. 
        // Note: The Session_End event is raised only when the sessionstate mode
        // is set to InProc in the Web.config file. If session mode is set to StateServer 
        // or SQLServer, the event is not raised.
        //If the user close the browser window or session is end. It will automatically update the staus
        try
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.mainConnectionString, System.Data.CommandType.Text, "update tblUserLogins set userStatus='false' where sno=" + Session["userid"].ToString(), null);
        }
        catch (Exception ex)
        {

            throw ex;
        }

    }

Download the sample code

Client Callbacks in ASP.NET

2011-06-13T13:24:00.001+05:30

Some time a situation comes wherein you want to process some server side processing without refreshing the whole page. This can be achived by using Microsoft ajax controls like "UpdatePanel" but some time you want to implement a functionality on your own in that case you can use your own client Callbacks.
For this first we will implement the System.Web.UI.ICallbackEventHandler. The ICallbackEventHandler has two methods.
1. GetCallbackResult : This method will return the reslut of server side processing.

2. RaiseCallbackEvent : This method will be called by client and you can use it to get the parameter values from client.

In this example I have used a Textbox and getting the value of that text box value in a lable while typing in textbox.

Code

Welcome to ASP.NET!

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

namespace CallbackExample
{


    public partial class _Default : System.Web.UI.Page, System.Web.UI.ICallbackEventHandler
    {
        string _result = string.Empty;
        protected void Page_Load(object sender, EventArgs e)
        {
            //callback reference of the client-side function which will be called after the completion of server side processing. 
            string callbackRef = Page.ClientScript.GetCallbackEventReference(this, "args", "ClientFunction", "");
            // Javascript function that will be called from client to call the server
            string callBackFunctionScript = @"function CallServerFunction(args){" + callbackRef + "}";
            // Now register the script on the page
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallServerFunction", callBackFunctionScript, true);
        }

        public string GetCallbackResult()
        {
            //throw new NotImplementedException();
            return _result;


        }

        public void RaiseCallbackEvent(string eventArgument)
        {
            _result = eventArgument;
        }

        
    }
}

Download Code

Import gmail address using asp.net c#

2011-03-16T17:08:00.000+05:30

Import email addresses like in any social networking site.Below example we have used to fetch the gmail address.
For this we require Google Data API and install the same to get the required dlls.
Add these dlls as reference in your project.
1. Google.GData.Client
2. Google.GData.Contacts
3. Google.GData.Extensions
Use .net framework 3.5 or 4.0

Code

Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Google.Contacts;
using Google.GData.Client;
using Google.GData.Contacts;
using Google.GData.Extensions;

namespace GmailContacts
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        #region FETCH EMAIL IDS
        private List FetchEmailIds(string emailid, string password)
        {
            List emaillist = new List();
            try
            {

                RequestSettings Logindetails = new RequestSettings("", emailid, password);
                Logindetails.AutoPaging = true;
                ContactsRequest getRequest = new ContactsRequest(Logindetails);
                Feed listContacts = getRequest.GetContacts();
                foreach (Contact mailAddress in listContacts.Entries)
                {
                    // Response.Write(mailAddress.Title + "
");
                    foreach (EMail emails in mailAddress.Emails)
                    {
                        EmailLists emailaddress = new EmailLists();
                        emailaddress.Title = mailAddress.Title;
                        emailaddress.EmailAddress = emails.Address;
                        //Response.Write(emails.Address + "
");
                        emaillist.Add(emailaddress);
                    }

                }

            }
            catch (Exception)
            {

                Label1.Text = "Sorry your email id  or password does not match.";
            }
            return emaillist;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            GridView1.DataSource = FetchEmailIds(txtEmailId.Text, txtPassword.Text);
            GridView1.DataBind();

        }


        #endregion
    }

}

EmailLists.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace GmailContacts
{
    public class EmailLists
    {
        public string Title { get; set; }
        public string EmailAddress { get; set; }

    }
}

Download Code

Scheduling the task using WSH script

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

Sometimes you need to do some automated task perform by the system itself, for a specific time period. You can do this by WSH script.
The below code uses to move the file from one folder to another folder at a specific time period

Code

Dim OriginFolder, DestinationFolder, sFile, oFSO, oShell  
 Set oFSO = CreateObject("Scripting.FileSystemObject") 
 Set oShell = CreateObject("Wscript.Shell") 
 OriginFolder = "d:\folder1" 
 DestinationFolder = "d:\folder2" 
 On Error Resume Next  
  
   Call MoveFiles()  
   
 Sub MoveFiles() 
  
     For Each sFile In oFSO.GetFolder(OriginFolder).Files 
       If Not oFSO.FileExists(DestinationFolder & "\" & oFSO.GetFileName(sFile)) Then 
  oFSO.GetFile(sFile).copy DestinationFolder & "\" & oFSO.GetFileName(sFile),True 
  oFSO.deleteFile(OriginFolder & "\" & oFSO.GetFileName(sFile))
     End If 
     Next 
    
 End Sub

Steps to install the script in Scheduled Task. Browse your .vbs file in Scheduled Task.

Download Code

A basic tutorial for WCF: Windows Communication Foundation

2011-01-30T15:35:00.005+05:30

What is WCF: Windows Communication Foundation (WCF) is an SDK for developing and deploying services on Windows. WCF provides a runtime environment for your services, enabling you to expose CLR types as services, and to consume other services as CLR types
WCF is part of .NET 3.0 and requires .NET 2.0, so it can only run on operation systems that support it.

Why do we need this: This is the latest SOA technologies and it combines the features of Web Service,MSMQ,Remoting and COM+.
It provides a common plateform for all .NET communication. Interoperability is the fundamental characteristics of WCF.

ABC's of WCF: The 3 parts of the WCF. Address, Binding and Contract.
Address: Address is the address of the service. like (http://localhost:61685/Service1.svc)
Binding: It has number of binding models. It specifies the protocol to communicate.
Contract: Contract is the method exposed to the client. It gives the Data member serialization this is the main reason that it is faster than Webservice.

It includes Service Contract, Operation Contract and Data Contract

[Service Contract]: This attribute define saying which application interface will be exposed as service.
[Operation Contract]: It defines which method should be exposed to the external client, using this service.
[Data Contract]: Data Contract defines which type of complex data will be exchanged between the client and server.

The sample code is not a simple "Hello World" application. The sample includes a login system application.
It includes WCF host and one client. The host authenticate the user against a username and password, checked by WCF host and returns a boolean variable.The client includes a username and password screen, where user can put the username and password to authenticate the client application.

Download Code

Inline Table-valued Function in SQL server for optimized result.

2010-12-21T17:45:00.000+05:30

Sometime we want to parameterize our view for getting the optimized result, but we cannot pass the parameter to view. In this case we can use inline table valued parameterize function which will return the "Table". We can use this table in joins, sql queries, stored procedures and anywhere like as normal table.
First we will create view and see the result and time taken by view. Then after we will create the function which returns table with a parameter.
Code

View

create view GetAllDatabyCountry
as
select product.productid,product.productname,product.code,product.productPrice
   ,country.countryname,city.cityname
   from product
   left outer join
   country on product.countryid=country.countryid
   left outer join
   city on product.cityId=city.cityId

Function

Create function GetDatabyCountryId(@countryId int)
returns table
as
return (
   select product.productid,product.productname,product.code,product.productPrice
   ,country.countryname,city.cityname
   from product 
   left outer join
   country on product.countryid=country.countryid
   left outer join
   city on product.cityId=city.cityId
   where country.countryid=@countryId
  )

Using Function in Query

create procedure getDataByCountryId
@countryId int
as
begin
select getDataBycountry.* from  dbo.GetDatabyCountryId(@countryId) as getDataBycountry
end

Validate checkbox in ASP.NET

2010-12-15T15:02:00.001+05:30

You can validate check box or any custom control by using "CustomValidator" and javascript.

 
 Check box in your aspx page
  
   
   
     *

Sharing settings specified in appSettings element across multiple projects in .NET.

2010-12-03T14:27:00.001+05:30

When you are developing multiple .NET projects and want to share common custom configuration settings specified in the
element, for this you can create a common setting file for your all .NET projects.
In web.config file in you can use file attribute like this.

Code
Web.config file

settings.config file

Accessing Master Page User Control (ascx) event and value in content page.

2010-11-24T12:39:00.003+05:30

Sometimes a situation comes wherein you want to do some action on any event fired by master page user control in to a content page.
We can do this by the help of "Delegates".

The situation is I have a user control with a dropdown in header.ascx file, which I have integrated in my masterpage.master.
Now I want to access the event of that dropdownbox (SelectedIndexChanged ) in my content page default2.aspx and the value of the drop down box.

How to do that ?

1. Create a Delegate and event Handler in your usercontrol.ascx.

//Creating delegate for dropdownlist
public delegate void OnSelectedIndex(object sender, EventArgs e);

public event OnSelectedIndex ddl_selectedIndexChanged;

2. Create a Delegate and Eventhandler in your Master page, which we will access in Content Page.
//Creating Delegates and Event handler
public delegate void MasterDropDown(object sender, EventArgs e);
public event MasterDropDown Header_DropDown;

3. Now access the same on content page
For this first call the master page like
<%@ MasterType VirtualPath="~/MasterPage.master"%>
//And then in your content page load method
protected void Page_Load(object sender, EventArgs e)
{
Master.Header_DropDown += new MasterPage.MasterDropDown(Master_Header_DropDown);
}

For Accessing the value

1. Define a property in ascx control

//Geting the Drop Down Value
public string DropDownValue
{
get { return DropDownList1.SelectedValue; }
}
2. Access the value in master page

//Geting the User Control value
public string UserControlValue
{
get { return uc_usercontrol1.DropDownValue; }
}
3. Now access the value in Content page in a lable

Label1.Text =Label1.Text+" "+ Master.UserControlValue;

Complete Code

User Control

uc_usercontrol.ascx
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="uc_usercontrol.ascx.cs"
Inherits="uc_Usercontrols" %>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
onselectedindexchanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList>
uc_usercontrol.ascx.cs
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Collections.Specialized;
public partial class uc_Usercontrols : System.Web.UI.UserControl
{
//Geting the Drop Down Value
public string DropDownValue
{
get { return DropDownList1.SelectedValue; }
}
//Creating delegate for dropdownlist
public delegate void OnSelectedIndex(object sender, EventArgs e);
public event OnSelectedIndex ddl_selectedIndexChanged;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
this.ddl_selectedIndexChanged(sender, e);
}
}

Master Page

MasterPage.master
<%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
<%@ Register Src="uc_usercontrol.ascx" TagName="uc_usercontrol" TagPrefix="uc1" %>
<!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></title>
<asp:ContentPlaceHolder ID="head" runat="server">
</asp:ContentPlaceHolder>
</head>
<body>
<form id="form1" runat="server">
<div>
<uc1:uc_usercontrol ID="uc_usercontrol1" runat="server" />
<asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>
MasterPage.master.cs
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class MasterPage : System.Web.UI.MasterPage
{
//Geting the User Control value
public string UserControlValue 
{
get { return uc_usercontrol1.DropDownValue; }
}
//Creating Delegates and Event handler
public delegate void MasterDropDown(object sender, EventArgs e);
public event MasterDropDown Header_DropDown;

protected void Page_Load(object sender, EventArgs e)
{
this.uc_usercontrol1.ddl_selectedIndexChanged += new uc_Usercontrols.OnSelectedIndex(uc_usercontrol1_ddl_selectedIndexChanged);
}
void uc_usercontrol1_ddl_selectedIndexChanged(object sender, EventArgs e)
{
this.Header_DropDown(sender, e);

}
}

Default2.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<%@ MasterType VirtualPath="~/MasterPage.master"%>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:Label ID="Label1" runat="server" Text="Hi I m visible now" Visible="false"></asp:Label>
</asp:Content>
Default2.aspx.cs
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Master.Header_DropDown += new MasterPage.MasterDropDown(Master_Header_DropDown);
}
void Master_Header_DropDown(object sender, EventArgs e)
{
//Here you can do any activity on change dropdown list
Label1.Visible = true; 
//Accessing the Master page User Control Value in content Page
Label1.Text =Label1.Text+" "+ Master.UserControlValue;
}
}

Download Code

Dynamic SQL Query

2010-11-19T10:40:00.000+05:30

Dynamic Sql query means genrating sql query at run time according to need. Some times a situation comes where we have to genrate the dynamic query. Below is the example of a search condition in stored procedure, where I am genrating dynamic query, according to parameter passed in Stored Procedure.
It has demerits too. It will loose the performance boost, what we usually get in stored procedures and most importantly stored procedure can not cache the execution plan for this dynamic query.

Stored Procedure

CREATE proc usp_Getsearchresult  
@countryid int,  
@cityid int,  
@categoryid int,  
@keywords varchar(255)  
as  
--TO HOLD THE QUERY
Declare @setQuery nvarchar(2200)      
--TO HOLD THE WHERE CONDITION VALUES
declare @whereTosearch nvarchar (2000)      
--TO HOLD THE CONDITION
declare @condition int      
set @setQuery='select * from product with (NOLOCK) where '  
 --Condition for country ID  . WHERE COUNTRY ID IS MANDATORY IN CODE
  if (@countryid is not Null)      
 begin      
   set @condition=1      
   set @whereTosearch='countryId='+cast(@countryid as varchar(5))      
 --print '1'  
 end     
 -- Condition for city   
  if (@cityid is not Null and @condition>0)      
 begin      
   set @condition=1      
   set @whereTosearch=@whereTosearch+' and cityId='+ cast(@cityid as varchar(5))  
 --print '2'  
 end     
 --Condition if catetory is selected  
  if (@categoryid is not Null and @condition>0)      
 begin      
   set @condition=1    
   set @setQuery='select product.*,product_category.product_category_type_id from product with (NOLOCK)  
   left  outer join product_category on  product.service_id=product_category.service_id where product_category.product_category_type_id is not null and '  
   set @whereTosearch=@whereTosearch+' and product_category.product_category_type_id='+cast(@categoryid as varchar(5))  
 --print '3'  
 end     
 -- Condition for Keywords   
  if (@keywords is not Null and @condition>0)      
 begin      
   set @condition=1      
   set @whereTosearch=@whereTosearch+' and (productname like ''%'+@keywords+'%'' or product_keyword like ''%'+@keywords+'%'')'  
 --print '4'  
 end   
  
set @setQuery=@setQuery+@whereTosearch      
--print @setQuery  
exec(@setQuery)

Hiding datagrid column at runtime in asp.net

2010-11-03T11:19:00.000+05:30

Sometimes you want to hide the column at runtime in datagrid. Below is the code. You can do this on RowCreated event of datagrid.

protected void gvreport_RowCreated(object sender, GridViewRowEventArgs e)
    {
       

        if (username=="Admin")// Your Condition for hiding the column
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Cells[4].Visible = false;

            }
            if (e.Row.RowType == DataControlRowType.Header)
            {
                e.Row.Cells[4].Visible = false;
            }
        }
    }

Formatted Dropdown List and Listbox control in ASP.NET

2010-10-30T17:58:00.000+05:30

Formatted Dropdown List and Listbox control in ASP.NET

Code

Some tricky SQL queries (Interview Questions)

2010-10-08T14:05:00.000+05:30

1.Select nth highest salary and name from emp table

SELECT DISTINCT (a.empsalary),a.Employee_Name FROM empData A WHERE  N =(SELECT COUNT (DISTINCT (b.empsalary)) FROM empData B WHERE a.empsalary<=b.empsalary);
--where N is your number (2nd or 5th)
e.g SELECT DISTINCT (a.empsalary),a.Employee_Name FROM empData A WHERE  5 =(SELECT COUNT (DISTINCT (b.empsalary)) FROM empData B WHERE a.empsalary<=b.empsalary);

2.Select query without using "Like" operator . For eg select employee name start with 'j' and city is 'Noida' in company_name

select * from empdata where CHARINDEX('j',Employee_name)=1 and CHARINDEX('Noida',company_name)>0

3.Select query for grouping 2 coulmns in one column using Case statement

select result=case when p1 is not null then p1 when p2 is not null then p2 end from tbltest

4.Create identity like coulmn in query

select row_number () over( order by p1) as sno, result=case when p1 is not null then p1 when p2 is not null then p2 end from tbltest

ELMAH: Error Logging Modules and Handlers for ASP.NET

2010-09-30T17:48:00.000+05:30

A very good open source project for handling and loging .net errors. It logs nearly all the unhandled errors. You can view full details of your error log using http://www.yoursite.com/elmah.axd . An e-mail notification of each error at the time it occurs. For Download and more information on ELMAH

Download Sample Code

A basic example of LINQ

2010-09-28T12:56:00.000+05:30

LINQ stands for Language-Integrated Query.It return results of type: IEnumerable<T> where <T> is determined by the object type of the “select” clause.

And asp.net controls support databinding to any IEnumerable collection so we can easily bind the datagrid,reapter,datalist and dropdownlist with Linq query.

default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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></title>

</head>

<body>

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

<div>

<asp:GridView ID="grdCountryList" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"

BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black"

GridLines="None" AllowPaging="True">

<Columns>

<asp:BoundField DataField="CountryId" HeaderText="CountryId" />

<asp:BoundField DataField="CountryName" HeaderText="Country Name" />

<asp:BoundField DataField="CountryCode" HeaderText="Country Code" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

<br />

Linq Where country code lenght is less than 3

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"

BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">

<Columns>

<asp:BoundField DataField="CountryId" HeaderText="CountryId" />

<asp:BoundField DataField="CountryName" HeaderText="Country Name" />

<asp:BoundField DataField="CountryCode" HeaderText="Country Code" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

<br />

Linq Where country id between 31 to 70

<br />

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"

BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">

<Columns>

<asp:BoundField DataField="CountryId" HeaderText="CountryId" />

<asp:BoundField DataField="CountryName" HeaderText="Country Name" />

<asp:BoundField DataField="CountryCode" HeaderText="Country Code" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

<br />

Linq Where country started from "A"

<br />

<asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"

BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">

<Columns>

<asp:BoundField DataField="CountryId" HeaderText="CountryId" />

<asp:BoundField DataField="CountryName" HeaderText="Country Name" />

<asp:BoundField DataField="CountryCode" HeaderText="Country Code" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

<br />

Linq Where country with like query

<br />

<asp:GridView ID="GridView4" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"

BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None">

<Columns>

<asp:BoundField DataField="CountryId" HeaderText="CountryId" />

<asp:BoundField DataField="CountryName" HeaderText="Country Name" />

<asp:BoundField DataField="CountryCode" HeaderText="Country Code" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

</div>

</form>

</body>

</html>

default.aspx.cs

using System;using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

}

#region BIND GRID

private void BindGrid()

{

List<CountryEntity> countryList = new List<CountryEntity>();

CountryPersistence objCountry = new CountryPersistence();

countryList = objCountry.GetCountryList();

//Simple binding of grid using LIST 

grdCountryList.DataSource = countryList;

grdCountryList.DataBind();

 //Now we will start Linq. It will fetch all the data where country code length is less than 3

GridView1.DataSource = from country in countryList where country.CountryCode.Length < 3 orderby country.CountryName select country;

GridView1.DataBind();

 //Where country ID between 31 to 70

GridView2.DataSource = from country in countryList where (country.CountryId >= 31 && country.CountryId <= 70) orderby country.CountryId select country;

GridView2.DataBind();

//Where country name Starts with "A"

GridView3.DataSource = from country in countryList where country.CountryName.StartsWith("A") select country;

GridView3.DataBind();

 //Where country name with like query

GridView4.DataSource = from country in countryList where country.CountryName.Contains('z') orderby country.CountryName descending select country;

GridView4.DataBind();

}

#endregion

}

Download Code

Import Excel Sheet data in SQL server

2010-09-25T12:43:00.000+05:30

Upload Excel file data to SQL server using SqlBulkCopy. A new feature in ADO.NET 2.0 . In this example we will import the Excel Sheet data in to SQL server.
Following are the connection strings for different version of Excel sheet.

FOR xls files: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1

FOR xlsx files: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1

Where HDR=YES specifies first row of column is header column and
IMEX=1 specifies that the driver should always read the “intermixed”
data columns as text

By SqlBulkCopy you can upload complete excel sheet data in a single instance, quick and easy.

Files
Default.aspx

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

<!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>Excel File Uploading Demo</title>

<style type="text/css">

.style1

{

font-family: Verdana, Arial, Helvetica, sans-serif;

font-size: 12px;

}

</style>

</head>

<body>

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

<div>

<table width="52%" border="0" cellpadding="2" cellspacing="2" class="style1" style="border: #666666 1px solid">

<tr>

<td width="24%" nowrap="nowrap">

<strong>Excel Upload Example</strong>

</td>

<td width="76%" nowrap="nowrap">

&nbsp;

</td>

</tr>

<tr>

<td nowrap="nowrap">

<strong>Select your file </strong>

</td>

<td nowrap="nowrap">

&nbsp;

<asp:FileUpload ID="FileUpload1" runat="server" CssClass="style1" />

&nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="FileUpload1"

CssClass="style1" Display="Dynamic" ErrorMessage="Please select your file."></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td nowrap="nowrap">

&nbsp;

</td>

<td nowrap="nowrap">

&nbsp;

<asp:Button ID="Button1" runat="server" CssClass="style1" OnClick="Button1_Click"

Text="Upload Data" />

<br />

</td>

</tr>

<tr>

<td nowrap="nowrap">

&nbsp;

</td>

<td nowrap="nowrap">

&nbsp;

<asp:Label ID="lblMsg" runat="server" Font-Bold="True"></asp:Label>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data.OleDb;

using System.Data.Common;

using System.Data.SqlClient;

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

{

string excelConnectionString = string.Empty;

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

UploadExclData();

}

#region UPLOAD DATA  TO SQL SERVER

private void UploadExclData()

{

try

{

 //First we will upload the file to our hard drive

//Now check if control has file

if (FileUpload1.HasFile)

{

 ///execlData folder must have read and write permission

string excelFile = FileUploadToServer.UploadFile(FileUpload1, Server.MapPath("~/execlData/"));

 //Now Create the Connection to excel File

////CONNECTION STRING FOR DIFFRENT EXCEL VERSIONS

 //FOR xls Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1”

//FOR xlsx Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1

 //HDR=Yes specifies that the first row of the data contains column names and not data.

//If you don’t want the first row to be skipped, change this to No.

//IMEX=1  specifies that the driver should always read the “intermixed” data columns as text.

 //Create connection string to Excel work book

excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/execlData/" + excelFile) + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

OleDbConnection exclConnection = new OleDbConnection(excelConnectionString);

 //NOW FETCH DATA FROM EXCEL SHEET USING OLEDB 

OleDbCommand exclCmd = new OleDbCommand("select * from [Sheet1$]", exclConnection);

exclConnection.Open();

OleDbDataReader exclReader = exclCmd.ExecuteReader();

 //NOW CONNECT THE DESTINATION DATABASE CONNECTION AND TABLE NAME

SqlBulkCopy sqlBulk = new SqlBulkCopy(SqlHelper.mainConnectionString);

sqlBulk.DestinationTableName = "empData";

 //NOW MAP THE DESTINATION AND SOURCE TABLE COLUMN MAPPING

sqlBulk.ColumnMappings.Add("company_name", "company_name");

sqlBulk.ColumnMappings.Add("Employee_Name", "Employee_Name");

sqlBulk.ColumnMappings.Add("Emp_Code", "Emp_Code");

sqlBulk.ColumnMappings.Add("Date_Of_Joining", "Date_Of_Joining");

 //WRITE THE DATA TO TABLE

sqlBulk.WriteToServer(exclReader);

lblMsg.Text = "Your data uploaded successfuly.";

}

}

catch (Exception ex)

{

throw (ex);

}

}

#endregion

}

Download Code Files

Extension Method in ASP.NET

2010-09-15T22:28:00.000+05:30

Extension method is new to C#. It allow you to add new method to the existing class.
Extension method are special kind of static method but they are called using instance method syntax.
The first parameter specify which type the method operate on, and the parameter is precede by "this" modifier.

Download Code

Creating PDF with ASP.NET on fly

2010-09-14T11:10:00.000+05:30

Last day I have given a situation where I have to create an HTML Invoice from database and send it through email with PDF of invoice as attachment. I found a very interesting and easy solution for this iTextPDF. A pdf library for creating pdf documents in c#.
In this example we will export grid data in PDF format and send the same by mail as PDF attachment.

default.aspx

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

<!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></title>

</head>

<body>

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

<div>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click"

Text="Export To PDF" />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"

CellPadding="2" ForeColor="Black" GridLines="None">

<Columns>

<asp:BoundField DataField="country_id" HeaderText="Country ID" />

<asp:BoundField DataField="country_code" HeaderText="Country Code" />

<asp:BoundField DataField="country_name" HeaderText="Country Name" />

</Columns>

<FooterStyle BackColor="Tan" />

<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"

HorizontalAlign="Center" />

<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />

<HeaderStyle BackColor="Tan" Font-Bold="True" />

<AlternatingRowStyle BackColor="PaleGoldenrod" />

</asp:GridView>

</div>

</form>

</body>

</html>

default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.Text;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.text.html;

using iTextSharp.text.html.simpleparser;

using System.IO;

using System.Collections;

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

{

#region BIND GRID

private void BindGrid()

{

//GETING DATA FROM SQL SERVER

string sql = "select country_id,country_code,country_name from country order by country_name";

GridView1.DataSource = SqlHelper.GetDataSet(SqlHelper.mainConnectionString, CommandType.Text, sql, null);

GridView1.DataBind();

}

#endregion

#region EVENTS

protected void Button1_Click(object sender, EventArgs e)

{

WritePdf();

}

public override void VerifyRenderingInServerForm(Control control)

{

//don't throw any exception!

}

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack) { BindGrid(); }

}

#endregion

#region WRITE PDF

private void WritePdf()

{

StringBuilder _strRepeater = new StringBuilder();

Html32TextWriter _ObjHtm = new Html32TextWriter(new System.IO.StringWriter(_strRepeater));

GridView1.RenderControl(_ObjHtm);

string _str = _strRepeater.ToString();

//CREATE A UNIQUE NUMBER FOR PDF FILE NAMING USING DATE TIME STAMP

string filename = string.Format("{0:d7}", (DateTime.Now.Ticks / 10) % 10000000);

//CREATE DOCUMENT

Document document = new Document(new Rectangle(500f, 700f));

//SAVE DOCUMENT. CHECK IF YOU HAVE WRITE PERMISSION OR NOT

PdfWriter.GetInstance(document, new FileStream(Server.MapPath("~/pdfs/" + filename + ".pdf"), FileMode.Create));

document.Open();

List<IElement> htmlarraylist = iTextSharp.text.html.simpleparser.HTMLWorker.ParseToList(new StringReader(_str), null);

//add the collection to the document

for (int k = 0; k < htmlarraylist.Count; k++)

{

document.Add((IElement)htmlarraylist[k]);

}

document.Close();

//NOW SEND EMAIL TO USER WITH ATTACHMENT

string msg = "Hi this is test message";

email.SendMailAsHTML("Mail With PDF", "test@test.com", "abc@test.com", msg, Server.MapPath("~/pdfs/" + filename + ".pdf").ToString());

}

#endregion

}

I have used this code in one of my online shopping cart, Manmeet Singh CEO of Yoginet Web solutions offering web designing , web development and SEO services for Individuals , small businesses and Corporates. Specialized in developing websites using latest HTML5 and designing Responsive websites.For more information about web solution please click here

Download Code