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
}

}