Archive for the ‘SQL’ Category

XML Data Schema(XSD) To Excel Format

March 12, 2013

This article will help you to parse the XSD file to MS-excel format in easy way (in 6 steps)

1 .Create Web application project from VS.IDE

CreateWebproject

2  Paste the following code to Default.aspx.cs  file

 public partial class _Default : System.Web.UI.Page
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        protected void Page_Load(object sender, EventArgs e)
        {
            sb.Append("</pre>
<table border="1">"); sb.Append("
<tbody>
<tr>
<td colspan="6" align="middle">"); sb.Append("
<h3>Schema Design for: - 10.6</h3>
"); sb.Append("</td>
</tr>
"); sb.Append("
<tr>
<td colspan="6" align="middle"> </td>
</tr>
"); TraverseSOM(Server.MapPath("CustomersOrders.xsd")); sb.Append("</tbody>
</table>
<pre>
");
            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();
        }
        ///
    /// Reads the and compile schema.    ///

        /// ///Name of the file.
        ///
        private static XmlSchema ReadAndCompileSchema(string fileName)
        {
            XmlTextReader tr = new XmlTextReader(fileName, new NameTable());         // The Read method will throw errors encountered
            // on parsing the schema
            XmlSchema schema = XmlSchema.Read(tr, new ValidationEventHandler(ValidationCallbackOne));
            tr.Close(); XmlSchemaSet xset = new XmlSchemaSet();
            xset.Add(schema);
            xset.ValidationEventHandler += new ValidationEventHandler(ValidationCallbackOne);         // The Compile method will throw errors         // encountered on compiling the schema
            xset.Compile();
            return schema;
        }
        public static void ValidationCallbackOne(object sender, ValidationEventArgs args)
        {
            Console.WriteLine("Exception Severity: " + args.Severity);
            Console.WriteLine(args.Message);
        }
        private void TraverseSOM(string xsfFilename)
        {
            XmlSchema custSchema = ReadAndCompileSchema(xsfFilename);
            foreach (XmlSchemaElement elem in custSchema.Elements.Values)
            {           // XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                ProcessElement(elem);               // ProcessElement(elem);
                sb.Append("
&nbsp

");
            }
        }
        private void ProcessElement(XmlSchemaElement elem)
        {
            if (elem.ElementSchemaType is XmlSchemaComplexType)
            {
                sb.Append("
&nbsp");
                sb.Append("<b>Root Element Name : " + elem.Name + "</b>
");                sb.Append("");
                sb.Append("<b> Element Type  </b>

");
                sb.Append("
&nbspMaxOccurs: " + elem.MaxOccurs + " MinOccurs: " + elem.MinOccurs + "" + elem.SchemaTypeName.Name + "

"); sb.Append("
<b> SL No</b><b> Element Name</b><b> Data Type</b><b> Mandatory?</b><b> Constraint </b><b> Explanation </b>

");
                XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                int i = 1;
                if (ct.Attributes.Count != 0)
                {
                    foreach (XmlSchemaAttribute obj in ct.Attributes)
                    {
                       string strAnno = "";
                        if (obj.Annotation != null)
                        {
                            var ann = obj.Annotation.Items;
                            if (ann is XmlSchemaObjectCollection)
                            {
                                foreach (XmlSchemaDocumentation document in ann)
                                {
                                    strAnno = strAnno + document.Markup[0].Value;
                                }
                            }
                        }
                        sb.Append("
" + i + "" + obj.Name + "" + (obj.AttributeSchemaType as XmlSchemaType).Name + "" + obj.Use + "" + strAnno + " 

");
                        i++;
                    }
                }
                var sequence = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaSequence;
                if (sequence != null)
                {
                    foreach (XmlSchemaParticle childParticle in sequence.Items)
                    {
                        XmlSchemaElement elementSequ = (childParticle as XmlSchemaElement);
                        decimal minOccurs = childParticle.MinOccurs;
                        string strMan;
                        if (minOccurs == 0)
                        {
                            strMan = "No";
                        }
                        else
                        {
                            strMan = "Yes";
                        }
                        if (elementSequ != null)
                            sb.Append("
" + i + "" + elementSequ.QualifiedName.Name + "" + elementSequ.SchemaTypeName.Name + "" + strMan + "  

");
                        i++;
                    }
                }
                var Choice = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaChoice;
                if (Choice != null)
                {
                    foreach (XmlSchemaParticle childParticle in Choice.Items)
                    {
                        XmlSchemaElement elementChoice = (childParticle as XmlSchemaElement);

                        if (elementChoice != null)
                            sb.Append("
" + i + "" + elementChoice.QualifiedName.Name + "" + elementChoice.SchemaTypeName.Name + "" + "Mandatory for the instance" + "  

");
                        i++;
                    }
                }

                ProcessSchemaObject(ct.ContentTypeParticle);
            }
        }
        private void ProcessSequence(XmlSchemaSequence sequence)
        {
            ProcessItemCollection(sequence.Items);
        }
        private void ProcessChoice(XmlSchemaChoice choice)
        {
            ProcessItemCollection(choice.Items);
        }
        private void ProcessItemCollection(XmlSchemaObjectCollection objs)
        {
            foreach (XmlSchemaObject obj in objs)
            {
                ProcessSchemaObject(obj);
            }
        }
        private void ProcessSchemaObject(XmlSchemaObject obj)
        {
            if (obj is XmlSchemaElement)
            {
                ProcessElement(obj as XmlSchemaElement);
            }
            if (obj is XmlSchemaChoice)
            {
                ProcessChoice(obj as XmlSchemaChoice);
            }
            if (obj is XmlSchemaSequence)
            {
                ProcessSequence(obj as XmlSchemaSequence);
            }
        }
    }

3 Add XSD file to the solution folder (Same folder)

4 Change the file name to code file to your XSD file “TraverseSOM(Server.MapPath(“Your.xsd”));”

 5. Press F5 to run the application

 6 Save or View your Excel file with formatted XSD details ,  Please see the below sample out put .

excelOutput

I think this post is helpful for large schema analysis  and development  activities 

Advertisements

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

Configuring Database Mail

March 13, 2008

Configuring Database Mail