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;

                      }

                  }

                  

              }