Tuesday, August 21, 2012

Example for Reading & Writing XML File Using XElement

C# XElement
 XElement loads and parses XML. It allows you to remove lots of old code and eliminate the possibility of bugs and typos. Here we see an example of XElement from LINQ. We use it to load an XML document. We then query it using expressions in the C# language.
Here we look at how you can use the static Load method on an XML document with XElement. XElement and XDocument make using XML easy. To load an XML document into memory you only need one line of code.
Then: You can use methods on the XElement object to access the elements. 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;

namespace sampleTest
{
public partial class XElementExample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Example();
}

public void Example()
{
XElement _x;
_x = XElement.Load(@"D:\myFile\myFile.xml");

try
{
// Get pages matching the title specified.
string t = "Alphanumeric Sorting in C#";
var v = (from page in _x.Elements("SitePage")
where t == page.Element("Title").Value
select page).ToList();


var k = (from page in _x.Elements("SitePage")
select page).ToList();

// Get category of first page matched.
string c = v.First().Element("Category").Value;

string d = k.First().Element("Category").Value;

// Count number of elements with that category element value.
int count = (from p in _x.Elements("SitePage")
where p.Element("Category").Value == c &&
p.Element("Visibility").Value == "Regular"
select p).Count();
}
catch (Exception ex)
{ }

}


}

}

XML
Here you can see the XML used for this article. You can match up the strings used in the above code samples with the tag and attribute names. This part of the article is completely application-specific, and your XML will be very different.
However:You can use some of the code samples in this article to help you deal with your XML.
File used [XML]

<?xml version="1.0"?>
<ArrayOfSitePage xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SitePage>
    <Visibility>Supplementary</Visibility>
    <Title>C# .NET Examples and Resources</Title>
    <Url></Url>
    <Category>None</Category>
  </SitePage>
  <SitePage>
    <Visibility>Supplementary</Visibility>
    <Title>Usability Guidelines for Web Writing</Title>
    <Url>Content/Usability-Writing.aspx</Url>
    <Category>None</Category>
  </SitePage>
  <SitePage>
    <Visibility>Regular</Visibility>
    <Title>Alphanumeric Sorting in C#</Title>
    <Url>Content/Alphanumeric-Sorting.aspx</Url>
    <Category>Algorithms</Category>
  </SitePage>
  <SitePage>
    <Visibility>Regular</Visibility>
    <Title>Word Count Algorithm in C#</Title>
    <Url>Content/Word-Count-Algorithm.aspx</Url>
    <Category>Algorithms</Category>
  </SitePage>
</ArrayOfSitePage>
Summary
We saw how you can use XElement and its Load method to quickly and effortlessly load XML. Load your XML file in a single statement and then run queries on it to generate output. Improvements could store more detailed data or be modified at runtime.
This below  article shows how to create an XML document using XLinq and later load it and loop through the elements of XML.


You must import following namespace:
using System.Xml.XLinq;
The XElement class represents an XML element in XLinq. The code creaes a root node called Authors and adds children Author nodes. The XAttribute class represents an attribute of an element.

XElement.Save method saves the contents of XElement to a XML file.

// Create a root node
            XElement authors = new XElement("Authors");
            // Add child nodes
            XAttribute name = new XAttribute("Author", "Mahesh Chand");
            XElement book = new XElement("Book", "GDI+ Programming");
            XElement cost = new XElement("Cost", "$49.95");
            XElement publisher = new XElement("Publisher", "Addison-Wesley");
            XElement author = new XElement("Author");
            author.Add(name);
            author.Add(book);
            author.Add(cost);
            author.Add(publisher);
            authors.Add(author);

            name = new XAttribute("Name", "Mike Gold");
            book = new XElement("Book", "Programmer's Guide to C#");
            cost = new XElement("Cost", "$44.95");
            publisher = new XElement("Publisher", "Microgold Publishing");
            author = new XElement("Author");
            author.Add(name);
            author.Add(book);
            author.Add(cost);
            author.Add(publisher);
            authors.Add(author);

            name = new XAttribute("Name", "Scott Lysle");
            book = new XElement("Book", "Custom Controls");
            cost = new XElement("Cost", "$39.95");
            publisher = new XElement("Publisher", "C# Corner");
            author = new XElement("Author");
            author.Add(name);
            author.Add(book);
            author.Add(cost);
            author.Add(publisher);
            authors.Add(author);          

            authors.Save(@"Authors.xml");
The output xml file looks like this:
<?xml version="1.0" encoding="utf-8" ?>
- <Authors>
- <Author Author="Mahesh Chand">
  <Book>GDI+ Programming</Book>
  <Cost>$49.95</Cost>
  <Publisher>Addison-Wesley</Publisher>
  </Author>
- <Author Name="Mike Gold">
  <Book>Programmer's Guide to C#</Book>
  <Cost>$44.95</Cost>
  <Publisher>Microgold Publishing</Publisher>
  </Author>
- <Author Name="Scott Lysle">
  <Book>Custom Controls</Book>
  <Cost>$39.95</Cost>
  <Publisher>C# Corner</Publisher>
  </Author>
  </Authors>
The following code reads the Authors.xml file and loops through authors and displays the contents.
            XElement allData = XElement.Load("Authors.xml");
            
if (allData != null)
            {
                
IEnumerable<XElement> authors = allData.Descendants("Author");
                
foreach(XElement author in authors)
                    
Console.WriteLine((string)author);
            }



Wednesday, August 8, 2012

Set Two Different Colour For Two Differennt Text In Same Cell Using EPPlus

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using System.Data;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");               


                    AddRecords(ws);
                    pck.Workbook.Properties.Title = "Excel Export";
                    pck.Workbook.Properties.Author = "Me";
                    pck.Workbook.Properties.Comments = "Sample Record Details";
                    pck.Workbook.Properties.Company = "ABC Tech";


                    byte[] fileBytes = pck.GetAsByteArray();

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.ClearContent();
                    HttpContext.Current.Response.ClearHeaders();
                    HttpContext.Current.Response.Cookies.Clear();

                    //Add the header & other information
                    HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.Private);
                    HttpContext.Current.Response.CacheControl = "private";
                    HttpContext.Current.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
                    HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
                    HttpContext.Current.Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
                    HttpContext.Current.Response.AppendHeader("Pragma", "cache");
                    HttpContext.Current.Response.AppendHeader("Expires", "60");

                    string DateTimes = DateTime.Now.TimeOfDay.ToString().Replace(":", "_").Replace(".", "_");

                    //Default Download Filename
                    string FileName = "ExportBiblio_" + DateTimes + ".xlsx";

                    HttpContext.Current.Response.AppendHeader("Content-Disposition",
                    "attachment; " +
                    "filename=\"" + FileName + "\"; " +
                    "size=" + fileBytes.Length.ToString() + "; " +
                    "creation-date=" + DateTime.Now.ToString("R") + "; " +
                    "modification-date=" + DateTime.Now.ToString("R") + "; " +
                    "read-date=" + DateTime.Now.ToString("R"));
                    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.sampleDemo.sheet";

                    //Write it back to the client
                    HttpContext.Current.Response.BinaryWrite(fileBytes);
                    HttpContext.Current.Response.End();

                }
            }

            catch (Exception ex)
            {
                ex.ToString();

            }

        }


        public void AddRecords(ExcelWorksheet _Wsheet1)
        {

            _Wsheet1.Column(1).Width = 15;
            _Wsheet1.Column(2).Width = 25;
            _Wsheet1.Column(3).Width = 20;

            _Wsheet1.Cells["B2"].Value = "Emp No ";
            _Wsheet1.Cells["B2"].Style.Font.Bold = true;
            _Wsheet1.Cells["C2"].Value = "777";

            _Wsheet1.Cells["C2"].Style.Font.Color.SetColor(Color.FromArgb(0, 112, 192));
            _Wsheet1.Cells["C2"].Style.Font.Bold = true;
            _Wsheet1.Cells["C2"].Style.Font.Size = 14;


            _Wsheet1.Cells["B3"].Value = "Name";
            _Wsheet1.Cells["B3"].Style.Font.Bold = true;
            _Wsheet1.Cells["C3"].Value = "My Name";
            _Wsheet1.Cells["C3"].Style.WrapText = true;
            _Wsheet1.Cells["C3:G3"].Merge = true;

            _Wsheet1.Cells["C3"].Style.Font.Color.SetColor(Color.FromArgb(0, 112, 192));
            _Wsheet1.Cells["C3"].Style.Font.Bold = true;
            _Wsheet1.Cells["C3"].Style.Font.Size = 14;

            _Wsheet1.Cells["B1:G2"].Style.WrapText = true;


            _Wsheet1.Cells["C3"].IsRichText = true;

            var rtDir1 = _Wsheet1.Cells["C3"].RichText.Add(" is Karthi.");
            rtDir1.Color = Color.Red;          

            // Set two colors for different text in same cell
            _Wsheet1.Cells["C5"].Value = " This is an ";
            _Wsheet1.Cells["C5"].Style.WrapText = true;
            _Wsheet1.Cells["C5"].IsRichText = true;
            _Wsheet1.Cells["C5"].Style.Font.Bold = true;
            _Wsheet1.Cells["C5"].Style.Font.Size = 12;
            _Wsheet1.Cells["C5"].Style.Font.Color.SetColor(Color.Blue);

            var rtDir2 = _Wsheet1.Cells["C5"].RichText.Add(" Example.");
            rtDir2.Color = Color.Red;

            _Wsheet1.Cells["B1:G2"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            _Wsheet1.Cells["B1:G2"].Style.Border.Right.Style = ExcelBorderStyle.Thin;

            var CellVal = _Wsheet1.Cells["C1"].RichText;

            var part = CellVal.Add("A");
            part.Color = Color.Blue;

        }
    }
}

Friday, July 27, 2012

AutoEventWireup

AutoEventWireup is an attribute of the @ Page directive. The AutoEventWireupattribute may have a value of true or false.


You can specify a default value of the AutoEventWireup attribute in several places:
  • The Machine.config file
  • The Web.config file
  • Individual ASP.NET Web Forms (.aspx files)
  • Web User Controls (.ascx files)
When AutoEventWireup is true, ASP.NET does not require that you explicitly bind event handlers to a page event such as Load.
When AutoEventWireup is false, you must explicitly bind the event to a method.
For example, if you have a Page_Load method in the code for a page, the method will be called in response to the Load event only if you write code like this.

public partial class AutoEventWireupExample : System.Web.UI.Page
{
    protected void Page_Load(object sender, System.EventArgs e)
    {
        Response.Write("Executing Page_Load");
    }
    override protected void OnInit(EventArgs e)
    {
        this.Load += new System.EventHandler(this.Page_Load);
    }
}

ASP.NET Directives


ASP.NET pages actually support eight different directives.
  • @ Page
  • @ Control
  • @ Import
  • @ Implements
  • @ Register
  • @ Assembly
  • @ OutputCache
  • @ Reference
Page directives are the most commonly used directives, and are used to edit a wide variety of settings that control how the page parser and page compiler work. The following is a list of some of the more commonly used page directive attributes in ASP.NET.
 Example:
@ Page language="c#" Codebehind="WebForm1.aspx.cs"
         AutoEventWireup="false" Inherits="TestWebApp.WebForm1"

  • Language indicates the language in which the inline script code within the ASP.NET page is written (the code between <% %> tags). The value of this attribute can be C#, VB, or JS.
  • Codebehind indicates the name of the file being used as the code supporting this ASP.NET page. This file should reflect the Language setting; that is, if the language being used is C#, the CodeBehind file should have a .cs extension and be written in C#.
  • Inherits indicates a qualified class from which this ASP.NET page should inherit. Generally, this will be the name of the class described in the code-behind file.
  • AutoEventWireup is a Boolean attribute that indicates whether the ASP.NET pages events are auto-wired.


Wednesday, July 25, 2012

SQL Rank

 SQL RANK:

          Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
There are four functions
  • RANK
  • DENSE_RANK
  • ROW_NUMBER
  • NTILE
RANK  
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
DENSE_RANK
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
ROW_NUMBER
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
NTILE
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Difference between the RANK and DENSE_RANK
RANK leaves gaps while ranking the records whereas DENSE_RANK doesn’t leave any gaps and always have consecutive ranks.
 
Example:

select price,
ROW_NUMBER() over(order by price) as 'Row Number',
RANK() over(order by price) as 'Rank',
DENSE_RANK() over (order by price) as 'Dense Rank',
NTILE(5) over (order by price) as 'Partition'
 from tbl_price

Output:

price    Row Number    Rank    Dense Rank    Partition
5           1                     1               1                  1
5           2                     1               1                  1
6           3                     3               2                  1
10         4                     4               3                  2
15          5                    5               4                  2
25         6                     6               5                  2
50         7                     7                6                 3
90         8                     8                7                 3
90        9                      8                7                 4
90        10                    8                7                 4
95       11                     11               8                 5
100     12                     12               9                 5

Cache Callback

Cache Callback:
Cache Callback provides an ability where by some actions can be performed when that item is removed from cache.

Example: 


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace sampleTest
{
    public partial class WebForm3 : System.Web.UI.Page
    {

        static string r = "click";
        protected void Page_Load(object sender, EventArgs e)
        {
            System.Web.Caching.CacheItemRemovedCallback callback = new System.Web.Caching.CacheItemRemovedCallback(OnRemove);
           
            Cache.Insert("key", "1234", null, System.Web.Caching.Cache.NoAbsoluteExpiration, TimeSpan.Zero,
            System.Web.Caching.CacheItemPriority.Default, callback);

        }

        public static void OnRemove(string key, object cacheItem, System.Web.Caching.CacheItemRemovedReason reason)
        {
            r = "welcome";

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Cache.Remove("key");
            Button1.Text = r.ToString();

        }
    }
}