Dotnet Codes: September 2010

Thursday, September 30, 2010

ELMAH: Error Logging Modules and Handlers for ASP.NET

Posted by Vipin at 5:48 PM

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

Posted by Vipin at 12:56 PM

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

Posted by Vipin at 12:43 PM

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

Posted by Vipin at 10:28 PM

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

Posted by Vipin at 11:10 AM

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