Tuesday, December 21, 2010

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

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

Wednesday, December 15, 2010

Validate checkbox in ASP.NET

You can validate check box or any custom control by using "CustomValidator" and javascript.
 
 Check box in your aspx page
  
   
   
     *
 

Friday, December 3, 2010

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

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

  
  
   
 
 

Wednesday, November 24, 2010

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

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

Friday, November 19, 2010

Dynamic SQL Query

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) 

Wednesday, November 3, 2010

Hiding datagrid column at runtime in asp.net

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;
            }
        }
    }

Saturday, October 30, 2010

Friday, October 8, 2010

Some tricky SQL queries (Interview Questions)

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

Thursday, September 30, 2010

ELMAH: Error Logging Modules and Handlers for ASP.NET

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

Tuesday, September 28, 2010

A basic example of LINQ

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

Saturday, September 25, 2010

Import Excel Sheet data in SQL server

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

Wednesday, September 15, 2010

Extension Method in ASP.NET

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

Tuesday, September 14, 2010

Creating PDF with ASP.NET on fly

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