ASP.NET Ajax Grid and Pager

I found one very nice article for Ajax Grid and Pager thanks Kazi Manzur Rashid for this sharing article

http://dotnetslackers.com/articles/ajax/aspnetajaxgridandpager.aspx

SQL Database Schema To Excel

   I searched lot of web site for document preparation for Database design
in excel format, at last I developed one web application for generating
SQL DB Schema to excel format , use the following code In page load
event of aspx page
protected void Page_Load(object sender, EventArgs e)
 {
 System.Data.SqlClient.SqlConnection con =
 new System.Data.SqlClient.SqlConnection
 (ConfigurationManager.ConnectionStrings["connectionStrings"].ConnectionString);
 con.Open();
 //System.Data.DataTable tbl = con.GetSchema("Databases");
 //ExportExcel(tbl);

 System.Text.StringBuilder sb = new System.Text.StringBuilder();
 sb.Append(" <TABLE BORDER=1>");
 sb.Append("<tr><td colspan='6' align='center'>");
 sb.Append("<H3>Table Design for: -" + con.Database.ToString() + "</H3>");

 sb.Append("</td></tr>");

 sb.Append("<tr><td colspan='6' align='center'> </td></tr>");
 con.Close();
 System.Data.DataTable tblTables = null;

 con.Open();
 tblTables = con.GetSchema(System.Data.SqlClient.
 SqlClientMetaDataCollectionNames.Tables,
 new string[] { null, null, null, "BASE TABLE" });
 DataTable columnsTable = null;
 foreach (System.Data.DataRow rowDatabase in tblTables.Rows)
 {
 columnsTable = con.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, rowDatabase["TABLE_NAME"].ToString() });
 sb.Append("<tr><td colspan='5' align='center'>");
 sb.Append("<b>Table Description</b></td>");
 sb.Append("<td align='center'>");
 sb.Append("<b>Table Name</b></td></tr>");
 sb.Append("<tr><td colspan='5' align='center' >" + rowDatabase["TABLE_NAME"].ToString() + "</td><td colspan='1' align='center'>" + rowDatabase["TABLE_NAME"].ToString() + "</td></tr>");
 sb.Append("<tr><td><b> SL No</b></td> <td><b> Field Name</b></td> <td><b> Data Type</b></td> <td><b> Size</b></td> <td><b> Constraint </b></td><td><b> Explanation </b></td></tr>");
 int i = 1;
 foreach (System.Data.DataRow columnsRows in columnsTable.Rows)
 {

 sb.Append("<tr><td>" + i + "</td> <td>" + columnsRows["COLUMN_NAME"].ToString() + "</td> <td>" + columnsRows["DATA_TYPE"].ToString() + "</td> <td>" + columnsRows["CHARACTER_MAXIMUM_LENGTH"].ToString() + "</td> <td></td><td></td></tr>");
 i++;
 }
 sb.Append("<tr><td colspan=6></td></tr>");
 }

 con.Close();
 sb.Append(" </TABLE>");

 Response.Clear();
 Response.ClearContent();
 Response.ClearHeaders();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
 Response.Write(sb);
 Response.End();
 }


The out put will be displayed like as follows :

Excel Repot From Data Table

Code for coverting Data table Execl Report

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Reflection;

namespace Common
{
 /// <summary>
 /// Download Excel
 /// </summary>
 public class DownloadExcel : System.Web.UI.Page
 {

 #region Methods (4)

 // Public Methods (3)

 /// <summary>
 /// Generics the list to data table.
 /// </summary>
 /// <param name="list">The list.</param>
 /// <returns></returns>
 public static DataTable GenericListToDataTable(object list)
 {
 DataTable dt = null;
 Type listType = list.GetType();
 if (listType.IsGenericType)
 {
 //determine the underlying type the List<> contains
 Type elementType = listType.GetGenericArguments()[0];

 //create empty table -- give it a name in case
 //it needs to be serialized
 dt = new DataTable(elementType.Name + "List");

 //define the table -- add a column for each public
 //property or field
 MemberInfo[] miArray = elementType.GetMembers(
 BindingFlags.Public | BindingFlags.Instance);
 foreach (MemberInfo mi in miArray)
 {
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 dt.Columns.Add(pi.Name);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 dt.Columns.Add(fi.Name, fi.FieldType);
 }
 }

 //populate the table
 IList il = list as IList;
 foreach (object record in il)
 {
 int i = 0;
 object[] fieldValues = new object[dt.Columns.Count];
 foreach (DataColumn c in dt.Columns)
 {
 MemberInfo mi = elementType.GetMember(c.ColumnName)[0];
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 fieldValues[i] = pi.GetValue(record, null);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 fieldValues[i] = fi.GetValue(record);
 }
 i++;
 }
 dt.Rows.Add(fieldValues);
 }
 }
 return dt;
 }

 /// <summary>
 /// Gets the date from string.
 /// </summary>
 /// <param name="strDate">The STR date.</param>
 /// <returns></returns>
 public static DateTime GetDateFromString(string strDate)
 {
 DateTime dt;
 try
 {
 Int32 intOADate = Int32.Parse(strDate);
 dt = DateTime.FromOADate(intOADate);
 }
 catch
 {
 try //try with dd/MM/yyyy
 {
 IFormatProvider culture = new CultureInfo("fr-FR", true);
 dt = DateTime.Parse(strDate, culture);
 }
 catch //try with MM/dd/yyyy
 {
 IFormatProvider culture = new CultureInfo("en-US", false);
 dt = DateTime.Parse(strDate, culture);
 }
 }
 return dt;
 }

 /// <summary>
 /// Function to export grid data to excel
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 public void GetExcel<T>(List<T> entityList, Dictionary<int, string> hashTable) where T : class
 {

 ExportExcel(entityList, hashTable);

 }

 // Protected Methods (1)

 /// <summary>
 /// Exports the excel.
 /// </summary>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 protected void ExportExcel(object entityList, Dictionary<int, string> hashTable)
 {

 DataTable dt = GenericListToDataTable(entityList);
 Response.Clear();
 Response.ClearContent();
 Response.ClearHeaders();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
 //Response.Clear();
 //Response.ContentType = "application/vnd.ms-excel";
 string sep = "";
 ICollection keyCollection = hashTable.Keys;
 int[] keys = new int[keyCollection.Count];
 keyCollection.CopyTo(keys, 0);
 for (int i = 0; i < dt.Columns.Count; i++)
 {

 Response.Write(sep + dt.Columns[i].ColumnName);
 sep = "\t";
 //excelTableColumn = excelTableColumn + "[" + hashTable[keys[i]] + "] text ,";
 }
 //foreach (DataColumn dc in dt.Columns)
 //{
 //    Response.Write(sep + dc.ColumnName);
 //    sep = "\t";
 //}
 Response.Write("\n");

 // int i;
 foreach (DataRow dr in dt.Rows)
 {
 sep = "";
 for (int i = 0; i < dt.Columns.Count; i++)
 {
 Response.Write(sep + dr[i].ToString());
 sep = "\t";
 }
 Response.Write("\n");
 }
 Response.End();

 }

 #endregion Methods

 }
}



The Above code you can be placed in common folder or App_code(freamwork 2.0)  when  you are creating report page
inherit this class to Report page like following code
/// <summary>
 ///
 /// </summary>
 public partial class DelayedVechiclesBodyTypeReport :  DownloadExcel
 {
 #region Private variables
//your code
 #endregion
 }


in Generate Report button Click write the following code :  
#region Web Generated Events
 /// <summary>
 ///
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 protected void btnGenerateReport_Click(object sender, EventArgs e)
 {
 List<ReportDelayedVehicleEntity> delayedVehicleDetailsList;
 delayedVehicleDetailsList = reportService.GetDelayVechiclesBodyTypeReport(Convert.ToInt32( rdoBodyType.SelectedValue));
 Dictionary<int, string> excelHashTable = new Dictionary<int, string>();
 excelHashTable.Add(0, "BodyNo");
 excelHashTable.Add(1, "ModelCode");
 excelHashTable.Add(2, "SuffixCode");
 excelHashTable.Add(3, "ColorCode");
 excelHashTable.Add(4, "JigInDate");
 excelHashTable.Add(5, "FrameNo");
 excelHashTable.Add(6, "ASSPlanned_LO");
 excelHashTable.Add(7, "EngineNo");
 excelHashTable.Add(8, "CurrentStageID");
 excelHashTable.Add(9, "CarFamily");
 excelHashTable.Add(10, "LotCode");
 excelHashTable.Add(11, "SpecSheetNumber");
 excelHashTable.Add(12, "EngineModelcode");
 excelHashTable.Add(13, "StageName");
 excelHashTable.Add(14, "StandardDefectName");
 excelHashTable.Add(15, "Remark");
 excelHashTable.Add(16, "DelayedDays");
 excelHashTable.Add(17, "RootResponsibilityName");
 excelHashTable.Add(18, "RepairResponsibilityName");
 excelHashTable.Add(19, "TargetDate");

 GetExcel(delayedVehicleDetailsList, excelHashTable);
 }

 #endregion

ASP.NET Performance Check List

I found these articles very helpful improve the  Performance  of  our ASP.net  sites

find out from these articles

http://articles.sitepoint.com/article/aspnet-performance-tips/5

http://msdn.microsoft.com/en-us/magazine/cc163854.aspx

http://msdn.microsoft.com/en-us/library/ms973839.aspx

then  prepare check list like  as follows:

Check List	Status (Done/Not Done)	Performance (in %)
Database Side 1.       Use proper indexing 2.        Avoid joints for high density tables 3.       Use SQL profiler to check work load 4.       Use Database Tuning Advisor for analyzing database performance 5. Use NOLOCK select Query for viewing data 6.       Select proper Hardware for running SQL Server 7.       Dot use Cursor 8.       Use Custom Pagination in SQL2005 (Row Count) 9.       Add your own check list
ASP.Net 1)       Use Trace log to analyze performance of each function 2)       Turn off View Sate if not required 3) Compressing the View State (GZIP) 4)       Minimize use of Session object and don’t initialize large object like data set to session 5) Enabling HTTP Compression Support in IIS 6 6)       Use proper ASP.NET cache mechanism according to situation 7)       Use light weight controls like repeater replace with grid control and minimize use of controls 8)       Use StringBuilder for Complex String Manipulation 9)       Throw Fewer Exceptions 10)   ValueTypes are far less flexible than Objects  avoid boxing and unboxing 11)   Use generic list 12)   Use proper architecture according to   situation (MVP, MVC, N-tire etc) 13)   Add your own check list

then fill Performance column in the above table

Getting Started Tutorial for WCF

Hi all

I found following links are useful for learning  WCF

1. http://msdn.microsoft.com/en-us/library/ms734712.aspx
2. http://www.wcf-training-guide.com/
3. http://bloggingabout.net/blogs/dennis/archive/2007/04/20/wcf-simple-example.aspx

Custom Paging User Control in ASP.NET 2.0 with SQL Server 2005

Read above Topic

http://www.codeproject.com/KB/aspnet/paginguc.aspx

Creating the Stored Procedure in SOL 2005

as like this

create procedure [dbo].[Usp_UISHKFindDistrict]

@startRowIndex int=0,
@pageSize int=0
As
BEGIN
declare    @RecordCount int
set @RecordCount=0
SET NOCOUNT ON;

SET @startRowIndex = @startRowIndex + 1
set @RecordCount=0

Select @RecordCount=count(*)  from
tHKDistrict A,
tHKLocality B

Select
tDistrictCode,
tDistrictName_Eng,
tDistrictName_Arb,
tLocalityId,
LocalityName_Eng,
IsActive,
tRecordCount
From

(Select
A.tDistrictCode,
A.tDistrictName_Eng,
A.tDistrictName_Arb,
A.tLocalityId,
B.LocalityName_Eng,
A.IsActive,
ROW_NUMBER() OVER(ORDER BY A.tDistrictCode) as RowNum,  @RecordCount  as tRecordCount
from
tHKDistrict A,
tHKLocality B
) as temp WHERE RowNum BETWEEN @startRowIndex  AND (@startRowIndex + @pageSize - 1)

END

For Demo Code (source Code Download)

Any Queries Plz Contact Me : nissankg@yahoo.com

Using XML To Send Multiple Rows At Once Into A Stored Procedure

Please Go and Find out Stuff

http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx

Report Viewer Control

Steps for creating Web Control Library

from VS IDE Select FIle>New>Project>WebControlLibrary

As in following Image :

Try this code for creating Report Viewer Control to cs file:

//Author:Nissan .K. George
//Web Developer
//nissankg@yahoo.com

using System;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Data;
using Microsoft.Reporting.WebForms;
namespace DBReportViewer
{
[DefaultProperty("DataTable"), ToolboxData("<{0}:DBReportViewer runat=server></{0}:DBReportViewer>")]

public partial class DBReportViewer : Microsoft.Reporting.WebForms.ReportViewer
{
[Bindable(true)]
[Category("Appearance")]
[DefaultValue("")]
[Localizable(true)]

#region “Properties”

private string objectTypeName;
[Bindable(true),
DefaultValue("")]

public string ObjectTypeName
{
get { return objectTypeName; }
set { objectTypeName = value; }
}

private DataTable DatatableSource;
public DataTable DataTableSource
{
get
{
return DatatableSource;
}

set
{
DatatableSource = value;
}
}
private Object objectSource;
public Object ObjectSource
{
get
{
return objectSource;
}

set
{
objectSource = value;
}
}

private string XmlDataSetName;

public string XMLDataSetName
{
get
{
return XmlDataSetName;
}

set
{
XmlDataSetName = value;
}
}
private string reportpath;

public string ReportPath
{
get
{
return reportpath;
}

set
{
reportpath = value;
base.LocalReport.ReportPath = value;

}
}

#endregion

#region Members overrides
//protected override void RenderContents(HtmlTextWriter output)
//{

// output.Write(Text);
//}

// METHOD:: OnLoad
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);

Type t = this.GetType();

}

// METHOD:: OnPreRender
protected override void OnPreRender(EventArgs e)
{
// Do as usual
base.OnPreRender(e);

}
public override void DataBind()
{

try
{
if ((XMLDataSetName == null) && (DatatableSource == null) && (objectSource == null))
{

base.LocalReport.DataSources.Add(new ReportDataSource());

}
else if ((XMLDataSetName == null) && (DatatableSource != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(“DB”, DatatableSource));

}
else if ((XMLDataSetName != null) && (DatatableSource != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(XMLDataSetName, DatatableSource));
}
else if ((ObjectSource != null) && (ObjectTypeName != null))
{

base.LocalReport.DataSources.Add(new ReportDataSource(ObjectTypeName, ObjectSource));
}

base.DataBind();

base.LocalReport.Refresh();
}
catch (Exception ex)
{
throw ex;
}
}

#endregion

}
}
after creating control add dll to toolkit then drag into page for use

use following code :

private void loadReports()
{
int allReopts = 1;
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();

MarketingReportView.LocalReport.ReportEmbeddedResource = “DB.Portal.Maintenance.MarketingReports.ReportTemplates.ActiveTrialReport.rdl”;

startDate = DateTime.Now.Date;
endDate = DateTime.Now.Date;
MarketingReportView.LocalReport.DataSources.Clear();

Sherston.Portal.BusinessData.Common.DataList<School> obj = SchoolService.GetSchoolsSubscriptionReports(SchoolService.ReportType.ActiveTrialUsersReport, startDate, endDate, allReopts, 0, 0);
MarketingReportView.ObjectSource = obj;

MarketingReportView.ObjectTypeName = “PortalDataSource”;
MarketingReportView.DataBind();

}

Row Clickable GridView

Steps for creating Web Control Library

from VS IDE Select FIle>New> Project >WebControlLibrary

As in following Image :

Try this code for creating Row Clickable GridView control to cs file:

//Author:Nissan .K. George
//Web Developer
//nissankg@yahoo.com
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
namespace RowClickableGridView
{

[ToolboxData("<{0}:DBGridView runat=server></{0}:DBGridView>")]
public partial class DBGridView : System.Web.UI.WebControls.GridView
{
#region “Properties”

[Bindable(true)]
[Category("Appearance")]
[DefaultValue("")]
[Localizable(true)]
public Color RowHoverColor
{
get
{
// look for hover color in ViewState
object o = ViewState["RowHoverColor"];
if (o == null)
return Color.Empty;
else
return (Color)o;
}

set
{
ViewState["RowHoverColor"] = value;
}
}

#endregion

#region Members overrides

public DBGridView() : base() { }

protected override void PrepareControlHierarchy()
{
base.PrepareControlHierarchy();

// Add a click handler to each row
for (int i = 0; i < this.Rows.Count; i++)
{
string argument = “rowClicked:” + i;
this.Rows[i].Attributes.Add(“onclick”, Page.ClientScript.GetPostBackEventReference(this, argument));

if (!(this.RowHoverColor.IsEmpty))
{

Rows[i].Attributes.Add(“onmouseover”,
(“this.style.backgroundColor=’” + String.Format(“#{0:x2}{1:x2}{2:x2}”, this.RowHoverColor.R, this.RowHoverColor.G, this.RowHoverColor.B) + “‘;”));
if (!(this.BackColor.IsEmpty))
{
Rows[i].Attributes.Add(“onmouseout”,
(“this.style.backgroundColor=’” + String.Format(“#{0:x2}{1:x2}{2:x2}”, this.BackColor.R, this.BackColor.G, this.BackColor.B) + “‘;”));
}
else { Rows[i].Attributes.Add(“onmouseout”, “this.style.backgroundColor=\’\';”); }
}
}
if (this.SelectedIndex != -1)
{
string argument = “Edited:” + SelectedIndex.ToString();
Rows[this.SelectedIndex].Attributes.Remove(“onmouseover”);
Rows[this.SelectedIndex].Attributes.Remove(“onmouseout”);
this.Rows[this.SelectedIndex].Attributes.Remove(“onclick”);
this.Rows[this.SelectedIndex].Attributes.Add(“onblur”, Page.ClientScript.GetPostBackEventReference(this, argument));

}
}

protected override void RaisePostBackEvent(string eventArgument)
{
// Only override “rowClicked” post-backs
if (eventArgument.StartsWith(“rowClicked:”))
{
eventArgument = eventArgument.Remove(0, 11);
int row = int.Parse(eventArgument);
this.SelectedIndex = row;
this.OnSelectedIndexChanged(new EventArgs());

this.EditIndex = row;

}

else
{
base.RaisePostBackEvent(eventArgument);
}
}

protected override void OnSelectedIndexChanged(EventArgs e)
{
this.Rows[this.SelectedIndex].Attributes.Remove(“rowClicked”);
int row=-1;
row = this.SelectedIndex ;

}

protected override void OnInit(EventArgs e)
{
base.OnInit (e);

}
#endregion

}
}

after creating control add dll to toolkit then drag into page use following code :

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

<%@ Register Assembly=”RowClickableGridView” Namespace=”RowClickableGridView” TagPrefix=”cc2″ %>

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

<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>Untitled Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:GboxConnectionString %>”
SelectCommand=”SELECT [AssetTypeId], [AssetTypeName] FROM [AssetTypes]” UpdateCommand=”UPDATE AssetTypes SET AssetTypeName = @AssetTypeName where AssetTypeid=@AssetTypeId”>
<UpdateParameters>
<asp:ControlParameter ControlID=”DBGridView1″ Name=”AssetTypeName” PropertyName=”SelectedValue” />
<asp:ControlParameter ControlID=”DBGridView1″ Name=”AssetTypeId” PropertyName=”SelectedValue” />
</UpdateParameters>
</asp:SqlDataSource>

<cc2:DBGridView ID=”DBGridView1″ runat=”server” AutoGenerateColumns=”False” DataKeyNames=”AssetTypeId”
DataSourceID=”SqlDataSource1″ AutoGenerateDeleteButton=”True” AutoGenerateEditButton=”True” RowHoverColor=”255, 192, 192″>
<Columns>
<asp:BoundField DataField=”AssetTypeId” HeaderText=”AssetTypeId” InsertVisible=”False”
ReadOnly=”True” SortExpression=”AssetTypeId” />
<asp:BoundField DataField=”AssetTypeName” HeaderText=”AssetTypeName” SortExpression=”AssetTypeName” />
</Columns>
<EditRowStyle BackColor=”Red” />
</cc2:DBGridView>
</form>
</body>
</html>
then run it and test it

Configuring Database Mail

Configuring Database Mail