Archive for the ‘ASP.net & SQL’ Category

ASP.NET Ajax Grid and Pager

May 7, 2010

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

March 3, 2010
   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

February 23, 2010
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

October 27, 2009

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

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

July 16, 2008

Please Go and Find out Stuff

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