ASP.Net MVC & ExtJs Grid [with Paging, Remote Soring and Filtering features] & Entity Framework Code First

In this post I am going to show how I created an Ext JS Grid and set up Paging, Remote Sorting and Filter features.

I have shared code at Github, feel free to download it and use it in your project.

In this project I have used Entity Framework code first for data access. After you download project you need to set up DB server name accordingly in Web.Config for application to run successfully.

 

In my solution I have 6 projects which, which are described as below:
1)MvcApplication.Core : It contains all core classes which will be used by other layers.
2)MvcApplication.DAL : It contains Models, Repository, DB context which is used by Main Application.
3)NewMvcApplication : Its main application which has a Person page that shows all persons.

Others are Test projects , as I have not written any Unit Test, I wont be discussing about them.

Once you run application first time , It will create all Tables and DB, once the DB is created you can insert some dummy data in your table.

Now I am going to discuss main components of my solution.
1) Persongrid.js  : It contains Javascript code which creates Person grid and sets up filter, Paging toolbar which is used by Grid. Here is the content of that file, As you can see here first I am creating a Data store, then using that Data store to create my ExtJs grid. I am also creating Paging toolbar to show my page numbers. I also specify filter information while defining columns.


Ext.Loader.setConfig({ enabled: true });

Ext.require([
    'Ext.grid.*',
    'Ext.data.*',
    'Ext.ux.grid.FiltersFeature',
    'Ext.toolbar.Paging',
    'Ext.ux.ajax.JsonSimlet',
    'Ext.ux.ajax.SimManager'
]);


Ext.onReady(function () {

    Ext.define('Person', {
        extend: 'Ext.data.Model',
        fields: [
        { name: 'FirstName', type: 'string' },
        { name: 'FirstName', type: 'string' },
        { name: 'MI', type: 'string' },
        { name: 'LastName', type: 'string' },
        { name: 'FaceBookId', type: 'string' },
        { name: 'Email', type: 'string' },
        { name: 'TwitterId', type: 'string' },
        { name: 'LinkedInId', type: 'string' },
        { name: 'BlogUrl', type: 'string' }
        ]
    });

    var store = Ext.create('Ext.data.Store', {
        model: 'Person',
        remoteSort: true,
        proxy: {
            type: 'ajax',
            url: '/Person/ListExtJs',
            reader: {
                type: 'json',
                root: 'data',
                totalProperty: 'totalCount'
            }
        },
        autoLoad: true,
        
    });

    // configure whether filter query is encoded or not (initially)
    var encode = true;

    // configure whether filtering is performed locally or remotely (initially)
    var local = true;

    var filters = {
        ftype: 'filters',
        encode: encode, 
        local: false
    };

    var grid = new Ext.grid.GridPanel({
        store: store,
        columns: [
           { header: 'Id', dataIndex: 'Id', hidden: true},
           { header: 'First Name', dataIndex: 'FirstName', width: 100, filterable:true },
           {
               header: 'Middle Initial', dataIndex: 'MI', width: 100
           },
           { header: 'Last Name', dataIndex: 'LastName', width: 100 },
           {
               header: 'FaceBook Id', dataIndex: 'FaceBookId', width: 100,
               filter: {
                   type: 'string'
                   // specify disabled to disable the filter menu
                   //, disabled: true
               }
           },
           {
               header: 'Email', dataIndex: 'Email', width: 100,
               filter: {
                   type: 'string'
                   // specify disabled to disable the filter menu
                   //, disabled: true
               }
           },
           {
               header: 'TwitterId', dataIndex: 'TwitterId', width: 100,
               filter: {
                   type: 'string'
                   // specify disabled to disable the filter menu
                   //, disabled: true
               }
           },
           { header: 'LinkedInId', dataIndex: 'LinkedInId', width: 100 },
           { header: 'BlogUrl', dataIndex: 'BlogUrl', width: 200 }
        ],
        
        renderTo: 'grid',
        width: 1000,
        autoHeight: true,
        bbar: new Ext.PagingToolbar({
            store: store,
            pageSize: 5,
            displayInfo: true,
            displayMsg: 'Displaying employees {0} - {1} of {2}',
            emptyMsg: "No employees to display"
        }),
        pageSize: 25,
        title: 'Employees',
        features: [filters],
    });
                
    grid.getStore().load({ params: {
        start: 0,
        limit: 25
    }
    });
});


2) I have added following method in my Person controller, here I am based on requested data I use filter and  sort data to return requested data.


      public ActionResult ListExtJs(int start, int limit,string sort, string filter)
        {
            int totalcount = db.Persons.Count();
            List<FilterData> filters;
            List<SortData> sorts;
            List<Person> persons;

            filters = FilterData.GetData(filter);
            sorts = SortData.GetData(sort);
            if (sorts.Count() == 0)
            {
                sorts.Add(new SortData() { direction = "ASC", property = "FirstName" });
            }
            string sortproperty = sorts[0].property;
            string sortdirection = sorts[0].direction;

            persons = db.Persons.Where(FilterData.GetWhereCriteria(filters)).OrderBy(sortproperty + " " + sortdirection).Skip(start).Take(limit).ToList();
            
            return Json(new { data = persons.ToArray(), totalCount = totalcount }, JsonRequestBehavior.AllowGet);

        }


3) I have created FilterData and SortData classes which are present in MvcApplication.Core they are class representation of data passed from ExtJs grid while loading data.

You can download this code from github using this link.

Generating KnockOut ViewModel from a sql table

Here is the script that can be used to generate a Knockout V.M. from a table




DECLARE @TableName VARCHAR(100) = 'Persons'
DECLARE @ShortTableName varchar(100) = 'person'
DECLARE @TableSchema VARCHAR(3) = 'dbo'
DECLARE @result varchar(max) = ''


SET @result = @result + CHAR(13)


SET @result = @result + 'var ' + @TableName +  'VM = function('+ @ShortTableName +'){' + CHAR(13)

set @result = @result + 'var self = this ;' + char(13)

SELECT @result = @result + CHAR(13)
     + 'self.' + ColumnName + ' = ko.observable(' + @ShortTableName + ' ? ' + @ShortTableName +'.' +ColumnName +' : '''');' + CHAR(13)
FROM
(
    SELECT  c.COLUMN_NAME   AS ColumnName
        , 'var' AS ColumnType
        , c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION


SET @result = @result  + '}' + CHAR(13)



PRINT @result

Enabling Tracing for WCF Service

If you want to enable tracing for your WCF Service Copy Paste following code under configuration


<system.diagnostics>
    <sources>
      <source name="System.ServiceModel" switchValue="Information, ActivityTracing"
          propagateActivity="true">
        <listeners>
          <add type="System.Diagnostics.DefaultTraceListener" name="Default">
            <filter type="" />
          </add>
          <add name="ServiceModelTraceListener">
            <filter type="" />
          </add>
        </listeners>
      </source>
    </sources>
    <sharedListeners>
      <add initializeData="C:\temp\web_tracelog.svclog" type="System.Diagnostics.XmlWriterTraceListener, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
          name="ServiceModelTraceListener" traceOutputOptions="Timestamp">
        <filter type="" />
      </add>
    </sharedListeners>
    <trace autoflush="true"/>
  </system.diagnostics>

Frequently Used JQuery functions for select list

Clearing a Select list:

$("#select_list_name").empty();

Adding an option in Select List:

$("# select_list_name ").append($('<option>', {

                            value: value_to_add,

                            text: text_to_add

                        }));

Here ‘value_to_add’ & text_to_add aer values that you want to be added.

To Check if an option exist by value in a Select List:

if ($('#select_list_name).find('option[value=' + value_to_search + ']').length > 0)

{

       //When an Item exist

}

Here value_to_search is the value that you want to searched in select list.

To select a particular value in select list after you find it:

$('#select_list_name).find('option[value=' + value_to_search + ']').attr("selected", "selected");

To read a selected value:

var selectedvalue = $("#select_list_name option:selected").val();

To set Margin value & Width for select list:

$('#select_list_name').css('margin-left', '5px');

$('#select_list_name ').css('width', (window.screen.width * 0.14) + 'px');

Auto Generate Class from a Table in SQL Server

Here is the SQL Script which will generate a class for a table

--Begin


DECLARE @TableName VARCHAR(MAX) = 'Person'
DECLARE @TableSchema VARCHAR(MAX) = 'dbo'
DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)
END

SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13)

SET @result = @result + '#region Instance Properties' + CHAR(13)

SELECT @result = @result + CHAR(13)
    + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM
(
    SELECT  c.COLUMN_NAME   AS ColumnName
        , CASE c.DATA_TYPE  
            WHEN 'bigint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
            WHEN 'binary' THEN 'Byte[]'
            WHEN 'bit' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END          
            WHEN 'char' THEN 'String'
            WHEN 'date' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                      
            WHEN 'datetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                      
            WHEN 'datetime2' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                      
            WHEN 'datetimeoffset' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                  
            WHEN 'decimal' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                  
            WHEN 'float' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Single?' ELSE 'Single' END                                  
            WHEN 'image' THEN 'Byte[]'
            WHEN 'int' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
            WHEN 'money' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                              
            WHEN 'nchar' THEN 'String'
            WHEN 'ntext' THEN 'String'
            WHEN 'numeric' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                          
            WHEN 'nvarchar' THEN 'String'
            WHEN 'real' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Double?' ELSE 'Double' END                                                                      
            WHEN 'smalldatetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                  
            WHEN 'smallint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END          
            WHEN 'smallmoney' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                                      
            WHEN 'text' THEN 'String'
            WHEN 'time' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                  
            WHEN 'timestamp' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                  
            WHEN 'tinyint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                              
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)

SET @result = @result  + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + CHAR(13) + '}'
END

PRINT @result




--and here is its output

using System;

namespace dbo
{
public class Person
{
#region Instance Properties

 public Int64 Id { get; set; }

 public String FirstName { get; set; }

 public String MI { get; set; }

 public String LastName { get; set; }

 public String FaceBookId { get; set; }

 public String Email { get; set; }

 public String TwitterId { get; set; }

 public String LinkedInId { get; set; }

#endregion Instance Properties
}

}

A Sample NANT Build Script with Subversion

Here is a sample NANT script which is originally referred from  Build Script in "Expert .Net Delivery", here it uses Subversion to fetch latest code from subversion which is what is different from original script.


<?xml version="1.0" encoding="utf-8" ?>
<project name="SampleTool" default="help">
<description>Build file for the SampleTool application.</description>

<property name="nant.onfailure" value="fail"/>
<property name="svnLocation" value="c:\program files\subversion\bin\svn.exe" />

     <loadtasks assembly="D:\dotNetDelivery\Tools\NAntContrib\0.85rc2\bin\NAnt.Contrib.Tasks.dll"/>
     <loadtasks assembly="D:\dotNetDelivery\Tools\NUnit2Report\1.2.2\bin\NAnt.NUnit2ReportTasks.dll"/>
<sysinfo/>

<target name="go" description="The main target for full build process execution." depends="clean, get, version1, version2, build, test, document, publish, notify"/>

<target name="clean" description="Clean up the build environment.">
<delete dir="D:\dotNetDelivery\BuildArea\Source\" failonerror="false"/>
<delete dir="D:\dotNetDelivery\BuildArea\Output\" failonerror="false"/>
<delete dir="D:\dotNetDelivery\BuildArea\Docs\" failonerror="false"/>
<delete dir="D:\dotNetDelivery\BuildArea\Reports\" failonerror="false"/>
<delete dir="D:\dotNetDelivery\BuildArea\Distribution\" failonerror="false"/>

<mkdir dir="D:\dotNetDelivery\BuildArea\Source\"/>
<mkdir dir="D:\dotNetDelivery\BuildArea\Output\"/>
<mkdir dir="D:\dotNetDelivery\BuildArea\Docs\"/>
<mkdir dir="D:\dotNetDelivery\BuildArea\Reports\"/>
<mkdir dir="D:\dotNetDelivery\BuildArea\Distribution\"/>
<mkdir dir="D:\dotNetDelivery\BuildArea\Publish\" failonerror="false"/>

</target>

<target name="get" description="Grab the source code." >
  <exec program="${svnLocation}" commandline="checkout http://some.repository.com/trunk/src/SampleTool SampleTool --username *username* --password *somepassword*"/>
 </target>


<target name="version1" description="Apply versioning to the source code files.">

<property name="sys.version" value="0.0.0.0"/>

<ifnot test="${debug}">
<version buildtype="increment" revisiontype="increment" path="SampleTool.Build.Number"/>
</ifnot>

<attrib file="D:\dotNetDelivery\BuildArea\Source\CommonAssemblyInfo.cs" readonly="false" />

<asminfo output="D:\dotNetDelivery\BuildArea\Source\CommonAssemblyInfo.cs" language="CSharp">
<imports>
<import name="System" />
<import name="System.Reflection"/>
</imports>
<attributes>
<attribute type="AssemblyVersionAttribute" value="${sys.version}" />
<attribute type="AssemblyProductAttribute" value="SampleTool" />
<attribute type="AssemblyCopyrightAttribute" value="Copyright (c) 2005, Etomic Ltd."/>
</attributes>
</asminfo>

<attrib file="D:\dotNetDelivery\BuildArea\Source\CommonAssemblyInfo.cs" readonly="true" />
</target>

<target name="version2">
<ifnot test="${debug}">
<vsslabel
user="builder"
password="builder"
dbpath="D:\dotNetDelivery\VSS\srcsafe.ini"
path="$/Solutions/SampleTool/"
comment="Automated Label"
label="NAnt - ${sys.version}"
/>
</ifnot>
</target>

<target name="build" description="Compile the application.">
<solution solutionfile="D:\dotNetDelivery\BuildArea\Source\SampleTool.sln" configuration="Debug" outputdir="D:\dotNetDelivery\BuildArea\Output\"/>
</target>

<target name="test" description="Apply the unit tests.">
<property name="nant.onfailure" value="fail.test"/>

<nunit2>
<formatter type="Xml" usefile="true" extension=".xml" outputdir="D:\dotNetDelivery\BuildArea\Reports\" />
<test assemblyname="D:\dotNetDelivery\BuildArea\Output\SampleToolTests.dll" />
</nunit2>

<nunit2report out="D:\dotNetDelivery\BuildArea\Reports\NUnit.html">
<fileset>
<include name="D:\dotNetDelivery\BuildArea\Reports\SampleToolTests.dll-results.xml" />
</fileset>
</nunit2report>

<exec program="D:\dotNetDelivery\Tools\FxCop\1.30\FxCopCmd.exe" commandline="/f:D:\dotNetDelivery\BuildArea\Output\SampleToolEngine.dll /f:D:\dotNetDelivery\BuildArea\Output\SampleToolGui.exe /o:D:\dotNetDelivery\BuildArea\Reports\fxcop.xml /r:D:\dotNetDelivery\Tools\FxCop\1.30\Rules\" failonerror="false"/>

<style style="D:\dotNetDelivery\Tools\FxCop\1.30\Xml\FxCopReport.xsl" in="D:\dotNetDelivery\BuildArea\Reports\fxcop.xml" out="D:\dotNetDelivery\BuildArea\Reports\fxcop.html"/>

<property name="nant.onfailure" value="fail"/>

</target>

<target name="document" description="Generate documentation and reports.">
<ndoc>
<assemblies basedir="D:\dotNetDelivery\BuildArea\Output\">
                <include name="SampleToolEngine.dll" />
                <include name="SampleToolGui.dll" />
            </assemblies>
            <summaries basedir="D:\dotNetDelivery\BuildArea\Output\">
                <include name="SampleToolEngine.xml" />
                <include name="SampleToolGui.xml" />
            </summaries>
            <documenters>
                <documenter name="MSDN">
                    <property name="OutputDirectory" value="D:\dotNetDelivery\BuildArea\Docs\" />
                    <property name="HtmlHelpName" value="SampleTool" />
                    <property name="HtmlHelpCompilerFilename" value="hhc.exe" />
                    <property name="IncludeFavorites" value="False" />
                    <property name="Title" value="SampleTool (NDoc)" />
                    <property name="SplitTOCs" value="False" />
                    <property name="DefaulTOC" value="" />
                    <property name="ShowVisualBasic" value="False" />
                    <property name="ShowMissingSummaries" value="True" />
                    <property name="ShowMissingRemarks" value="False" />
                    <property name="ShowMissingParams" value="True" />
                    <property name="ShowMissingReturns" value="True" />
                    <property name="ShowMissingValues" value="True" />
                    <property name="DocumentInternals" value="True" />
                    <property name="DocumentProtected" value="True" />
                    <property name="DocumentPrivates" value="False" />
                    <property name="DocumentEmptyNamespaces" value="False" />
                    <property name="IncludeAssemblyVersion" value="True" />
                    <property name="CopyrightText" value="Etomic Ltd., 2005" />
                    <property name="CopyrightHref" value="" />
                </documenter>
            </documenters>
        </ndoc>
</target>

<target name="publish" description="Place the compiled assets in agreed location.">
<copy todir="D:\dotNetDelivery\BuildArea\Distribution\">
<fileset basedir="D:\dotNetDelivery\BuildArea\Output\">
<include name="SampleToolEngine.dll"/>
<include name="SampleToolGui.exe"/>
</fileset>
</copy>

<zip zipfile="D:\dotNetDelivery\BuildArea\Publish\SampleTool-Build-${sys.version}.zip">
<fileset basedir="D:\dotNetDelivery\BuildArea\Distribution\">
<include name="**" />
</fileset>
</zip>
</target>

<target name="notify" description="Tell everyone of the success or failure.">
<echo message="Notifying you of the build process success."/>
</target>

<target name="fail">
<echo message="Notifying you of a failure in the build process."/>
</target>

<target name="fail.test">
<nunit2report out="D:\dotNetDelivery\BuildArea\Reports\NUnit.html">
<fileset>
<include name="D:\dotNetDelivery\BuildArea\Reports\SampleToolTests.dll-results.xml" />
</fileset>
</nunit2report>
</target>

<target name="help">
<echo message="The skeleton file for the build process is designed to execute the following targets in turn:"/>
<echo message="-- clean"/>
<echo message="-- get"/>
<echo message="-- version"/>
<echo message="-- build"/>
<echo message="-- test"/>
<echo message="-- document"/>
<echo message="-- publish"/>
<echo message="-- notify"/>
<echo message="This file should be run with a Boolean value for 'debug'."/>
<echo message="-- True indicates that no versioning be set (0.0.0.0)."/>
<echo message="-- False indicates that a regular version be set(1.0.x.0)."/>
<echo message="Example: -D:debug=true"/>
</target>

</project> 

Setting up NAnt on your pc/server

In this post we are going to see how to set up NANT on your PC/Server. Its first step towards Build Automation.

1.       Download Nant Binaries from http://nant.sourceforge.net/

2.       Once you download all Binary files, right click on Zip file and open Properties as show below

3.       Once properties window opens click on Unblock button as shown below

4.       Now unzip the Zipped file contents at some preferred location for eg. C:\Program Files (x86)\ Nant, as show below.

5.       Now go to “My Computer “ => “Advanced System Settings” => “Environment Variables”

And add following “System variable” as shown below

6.       Edit “Path” Variable in System Variables section as show below, append newly added system variable in your existing paths

 

7.       Open a command prompt and type “Nant – help”, you should see o/p like this.

LINQ to Excell

I am going to share a code sample where I am going to read data from excell and use Linq Query to process that data.

            //declaring all variables that I will use

            Workbook workBook;

            IEnumerable<Sheet> workSheets;

            WorksheetPart excelSheet;

            string excellsheetid;

            List<FormTag> tags;

            SharedStringTable sharedStrings;

            //Code to open Excel File

              using (SpreadsheetDocument document =

                SpreadsheetDocument.Open(

                    @"C:\Temp\TestExcel.xlsx",

                    true))

              {

                  //References to the workbook and Shared String Table.

                  workBook = document.WorkbookPart.Workbook;

                  workSheets = workBook.Descendants<Sheet>();

                  sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

                  //Reference to Excel Worksheet with Customer data.

                  excellsheetid =

                      workSheets.First(s => s.Name == "Sheet1").Id;

                  //Reading Excell Sheet

                  excelSheet =

                      (WorksheetPart)document.WorkbookPart.GetPartById(excellsheetid);

                  //LINQ query to skip first row with column names.

                  IEnumerable<Row> dataRows =

                      from row in excelSheet.Worksheet.Descendants<Row>()

                      where row.RowIndex > 1

                      select row;

                  //Looping through all rows after eliminating first row..assuming first row is a header row

                  foreach (Row row in dataRows)

                  {

                      //LINQ query to return the row's cell values.

                      //Where clause filters out any cells that do not contain a value.

                      //Select returns the value of a cell unless the cell contains

                      //  a Shared String.

                      //If the cell contains a Shared String, its value will be a 

                      //  reference id which will be used to look up the value in the 

                      //  Shared String table.

                      IEnumerable<String> textValues =

                          from cell in row.Descendants<Cell>()

                          where cell.CellValue != null

                          select

                              (cell.DataType != null

                               && cell.DataType.HasValue

                               && cell.DataType == CellValues.SharedString

                                   ? sharedStrings.ChildElements[

                                       int.Parse(cell.CellValue.InnerText)].InnerText

                                   : cell.CellValue.InnerText)

                          ;

                      //Check to verify the row contained data.

                      if (textValues.Count() > 0)

                      {

                          //Create your object for eg. a customer object and and manipulate it.

                          var textArray = textValues.ToArray();

                          Cutomer cust = new Cutomer();

                          cust.CustId = textArray[0];

                          cust.CustName = textArray[1];

                      }

                      else

                      {

                          //If no cells, then you have reached the end of the table.

                          break;

                      }

                  }

                  

              }

Using SQL Server Windowing Function with Aggregates Function

In this post we will see how we can use SQL Server Windowing function in conjunction with Aggregate functions. In SQL Server 2005 Windowing (Over Clause) was supported with all Aggregate functions (Avg , Count, Min, Max and Sum) , in SQL Server 2012 it got further better with support of "Order By" Clause.


Let's look at some Query and their O/P..

Query

1. Lets Create a TransData Table, with following Schema


CREATE TABLE TransData (AcctId int, TransDate date, Amount decimal)
INSERT INTO TransData (AcctId, TransDate, Amount) VALUES
  (1, DATEFROMPARTS(2011, 8, 10), 500),  -- 5 transactions for acct 1
  (1, DATEFROMPARTS(2011, 8, 22), 250),
  (1, DATEFROMPARTS(2011, 8, 24), 75),
  (1, DATEFROMPARTS(2011, 8, 26), 125),
  (1, DATEFROMPARTS(2011, 8, 28), 175),
  (2, DATEFROMPARTS(2011, 8, 11), 500),  -- 8 transactions for acct 2
  (2, DATEFROMPARTS(2011, 8, 15), 50),
  (2, DATEFROMPARTS(2011, 8, 22), 5000),
  (2, DATEFROMPARTS(2011, 8, 25), 550),
  (2, DATEFROMPARTS(2011, 8, 27), 105),
  (2, DATEFROMPARTS(2011, 8, 27), 95),
  (2, DATEFROMPARTS(2011, 8, 29), 100),
  (2, DATEFROMPARTS(2011, 8, 30), 2500),
  (3, DATEFROMPARTS(2011, 8, 14), 500),  -- 4 transactions for acct 3
  (3, DATEFROMPARTS(2011, 8, 15), 600),
  (3, DATEFROMPARTS(2011, 8, 22), 25),
  (3, DATEFROMPARTS(2011, 8, 23), 125)


2. Now we write a Query which will partition data by AccountId and Order them by TransDate


SELECT AcctId, TxnDate, Amount,
  RAvg = AVG(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RCnt = COUNT(*)    OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMin = MIN(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RMax = MAX(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate),
  RSum = SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate)
 FROM TxnData
 ORDER BY AcctId, TxnDate
GO

3. Here is the O/P of above Query

Delegates in C#

A method that is specified in an interface is implemented with the same name in the

base class. However, such close coupling is not always appropriate. The delegate construct

can be used to break the coupling for this purpose.

A delegate is a type that

defines a method signature. A delegate instance is then able to accept a method of that

signature, regardless of its method name or the type that encapsulates it.

The delegate syntax in C# evolved considerably from Versions 1.0 to 2.0 to 3.0. We

shall concentrate on the 3.0 version, which is the simplest to code. The language has

predefined standard generic delegate types, as follows:

        delegate R Func<R>();

        delegate R Func<A1, R>(A1 a1);

        delegate R Func<A1, A2, R>(A1 a1, A2 a2);

        // ... and up to 16 arguments

where R is the return type and the As and as represent the argument types and names,

respectively. Thus, declaring a delegate instance is now straightforward. For example,

we can define a Request delegate that takes an integer parameter and returns a string:

public Func<int, string> Request;

Next, we can assign an actual method to Request, as in:

Request = Target.Estimate;

The delegate can then be invoked just as any other method would be:

string s = Request(5);

This statement would invoke the Estimate method in the Target class, returning a

string.

Anonymous functions simplify the creation of one-time behavior for delegates. They are

useful when additional behavior is to be added before or after a method is invoked. For

example:

Request = delegate(int i) {

return "Estimate based on precision is " +

(int) Math.Round(Precise (i,3));

};

Here, the method to be invoked is Precise. The parameters are different from the ones

in the delegate, as is the return value. The anonymous function can wrapupthe

changes and assign a “complete solution” to the delegate for later invocation.

Delegates are used extensively in Windows GUI event-driven programming, where

they reflect the need to call back into the user’s code when some event happens.

Mostly, existing code of this type will use an older syntax. Also, because the new Func

delegates must have return types, void delegates must use the original syntax too. Consider

a simple example of wanting to inform one object that input has occurred in

another object (this is part of Example 4-4). We first declare a delegate visible to both

classes:

public delegate void InputEventHandler(object sender, EventArgs e, string s);

Then, in the class where the event is handled, we create an instance of the delegate and

add it to the event object of the class that will receive the event. When creating the delegate,

we indicate the method that it will call (in this case, OnInput):

   visuals.InputEvent += new InputEventHandler(OnInput);

        void OnInput(object sender, EventArgs e, string s)

        {

            // Do something

        }

The signature of OnInput must match that of InputEventHandler, which it does. Now,

in the class where event occurs, we declare the event:

       public event InputEventHandler InputEvent;

and in some method we invoke it:

       public void Input(object source, EventArgs e)

       {

           InputEvent(this, EventArgs.Empty, who);

       }

The action of invoking the InputEvent delegate causes the method currently assigned

to it (here, OnInput) to be invoked. Thus, the callback from one class to the other is

achieved.

More than one method can be associated with a delegate; when such a delegate is

invoked, all its methods are called. Thus, if another object needed to know about input

in the preceding example, it could add its own handler method on to InputEvent using

+=. Event handlers can be removed using -=