How to use AliaSQL

AliaSQL

Introducti​on I am looking to improve our database change management. For years we have worked off of a common "development" database that we periodically compared with Redgate SQL Compare and generated change scripts. For the most part we have been doing continuous deployment - unless the code corresponded with a database change. In that case it required manual intervention. So we introduce AliaSQL database change management tool. AliaSQL is a command line tool for database deployments.


How to start with AliaSQL


Following steps to start working with AliaSQL:


To get started, create an empty C# console app then install Nuget package AliaSQL.Kickstarter from the package manager console. It will create Scripts folder, AliaSQL.exe and update App.config, Program.cs files. Scripts folder contains 4 other folders Create, Everytime, TestData & Update. Change DatabaseName & DatabaseServer App.config key as per your database. Add schema_compare.ps1 power shell script for database compare and get database change script file. You can get schema_compare.ps1 file from https://github.com/ClearMeasure/AliaSQL . Run schema_compare.ps1 file in Powershell ISE to get database change script under scripts\Update folder with .sql.temp extension. Verify it and convert it to .sql extension and include in project. And put your all Static Data scripts under Update folder in proper sequence. Run console app and it will give you different command options to apply database changes in your database.

Scripts location decision

Once Create command execute it will create database in SQL server if not exist and execute all scripts under Create folder. It will also log executed scripts to usd_AppliedDatabaseScript SQL table.


Put your all schema_compare.ps1 generated database change scripts under Update folder. Once Update command execute it will execute all scripts under Update folder. It will also log executed scripts to usd_AppliedDatabaseScript SQL table so it will not execute second time of Update command execution.


Put your all Static Data scripts under Everytime folder with proper sequence.

Command Usage

AliaSQL console application provide 5 different type of commands:


1. Create: Create database and run all scripts in Create folder. Runs all new scripts and changed scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.​


2. Update: Run all scripts in Create and Update folders that have not yet been ran. If target database does not already exist it will be created. Runs all scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.


3. Rebuild: Drop and recreate database then run all scripts in Create and Update folders. Runs all scripts in Everytime folder. Logs to usd_AppliedDatabaseScript sql table.


4. TestData: Run all scripts in TestData folder that have not yet been ran - expects target database to already exist. Logs to usd_AppliedDatabaseTestDataScript sql table.


5. Baseline: Logs (but does not execute) all scripts in Create and Update folders that have not yet been ran - expects database to already exist. This adds the usd_AppliedDatabaseScript table and a record of all scripts to an existing database. This is useful when you have an existing database that you want to bring into change management without running all of your current scripts against it. Logs to usd_AppliedDatabaseScript.

How to implement Ajax.BeginForm in MVC OR How to validate MVC model data-annotation asynchronously

Here I am going to implement Ajax.BeginForm in MVC. It is replacement of Html.BeginForm html helper.

Html.BeginForm : Html helper post model to controller not asynchronously means your page post back and data-annotation validation fire.
Ajax.BeginForm : Ajax helper post model to controller asynchronously means your page doesn’t post back and data-annotation validation fire.

Step 1 : Add jquery.unobtrusive-ajax.min.js jquery reference to your view. And add 

 <add key="ClientValidationEnabled" value="true" />  
 <add key="UnobtrusiveJavaScriptEnabled" value="true" />  

This 2 key add to web.config under appSettings section.

Step 2 : Create first partialview ValidationSummary for show validation summary on the page.

 @using System.Web.Mvc.ViewUserControl  
 @Html.ValidationSummary("Please correct the errors and try again.")  

Step 3 : Create second partialview UserRegisterForm for registration form fields like Name, Age, Username, Password etc.

 @model EntityModel.UserModel  
 <div id="validationSummary">  
    @Html.RenderPartial("ValidationSummary");  
 </div>  
  <table width="455" border="0" cellspacing="0" cellpadding="10">  
               <tr>  
                 <td width="190">Username :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.TextBoxFor(m => m.USR_Username, new { @class = "TextBox250", @autofocus = "autofocus" })</td>  
               </tr>  
               <tr>  
                 <td width="190">Password :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.PasswordFor(m => m.USR_Password, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td width="190">Confirm Password :<font class="Mandt">*</font></td>  
                 <td width="265">  
                   @Html.PasswordFor(m => m.USR_Confirm_Password, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td>Email :</td>  
                 <td>  
                   @Html.TextBoxFor(m => m.USR_Email, new { @class = "TextBox250" })</td>  
               </tr>  
               <tr>  
                 <td>Phone :</td>  
                 <td>  
                   @Html.TextBoxFor(m => m.USR_Contact_No, new { @class = "TextBox250" })</td>  
               </tr>  
             </table>  

Step 4: Create view UserRegistration for User registration

 @using (Ajax.BeginForm("register",null, new AjaxOptions { HttpMethod = "POST", UpdateTargetId = "myForm" }, new { id = "myForm" }))  
 @Html.RenderPartial("UserRegisterForm");  

You can get better idea of AjaxOptions from below link
http://msdn.microsoft.com/en-us/library/system.web.mvc.ajax.ajaxoptions(v=vs.118).aspx


Step 5: Create Controller method register
 

 [AcceptVerbs(HttpVerbs.Post)]  
 public ActionResult Register(UserModel user)  
 {  
   if (Request.IsAjaxRequest())  
   {  
     return PartialView("UserRegisterForm");  }  
   else  
   {  
     return View();  
   }  
 }  

If Ajax request then return partialview in UpdateTargetId html element otherwise whole view will be sent back.

How to create custom search & optimize paging stored procedure (SP)

Here I am going to implement SP which provides Optimize Paging for tabular data representation and also custom search.

First step to create one sql table Users by below sql script

 CREATE TABLE [dbo].[Users](  
      [USR_User_ID] [bigint] IDENTITY(1,1) NOT NULL,  
      [USR_Department_ID] [bigint] NOT NULL,  
      [USR_FirstName] [nvarchar](100) NOT NULL,  
      [USR_MiddleName] [nvarchar](100) NULL,  
      [USR_LastName] [nvarchar](100) NOT NULL,  
      [USR_Email] [nvarchar](100) NULL,  
      [USR_Username] [nvarchar](100) NOT NULL,  
      [USR_Password] [nvarchar](200) NOT NULL,  
      [USR_Contact_No] [nvarchar](20) NULL,  
      [USR_Last_Login] [datetime] NOT NULL,  
      [USR_Status] [bit] NOT NULL,  
      [USR_Created_Date] [datetime] NOT NULL,  
      [USR_Created_By] [bigint] NOT NULL,  
      [USR_Updated_Date] [datetime] NULL,  
      [USR_Updated_By] [bigint] NULL,  
      [USR_IsDeleted] [bit] NOT NULL,  
      [USR_IsAdmin] [bit] NOT NULL,  
  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED   
 (  
      [USR_User_ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  

I am going to add search parameters for
•    Username
•    Name
•    Status
•    DepartmentId

 /* Optional Filters for Dynamic Search*/  
   @Username NVARCHAR(50) = NULL,  
   @Name NVARCHAR(50) = NULL,  
   @Status tinyint=2,  
   @DepartmentId bigint,  

I am going to add pagination parameters
•    PageNo
•    PageSize

 /*– Pagination Parameters */  
   @PageNo INT = 0,  
   @PageSize INT = 10,  

Now adding dynamic filter expression

   @filterExpression NVARCHAR(max) = NULL,  

Now adding column sort parameters
•    SortColumn
•    SortOrder

 /*– Sorting Parameters */  
   @SortColumn NVARCHAR(50) = 'USR_User_ID',  
   @SortOrder NVARCHAR(4)='DESC',  

Now adding export flag parameter for return all the data

 /*– Export to excel flag */  
   @Export BIT = 0,  

Now adding total count output parameters for total no of records after applying filter parameters

 /*– Output Parameters */  
   @TotalCount INT OUTPUT  

Declare variables

 DECLARE @SQLQuery NVARCHAR(MAX)='';   
 DECLARE @SQLCountQuery NVARCHAR(MAX)='';  
 DECLARE @WhereClause NVARCHAR(MAX) = '';  
 DECLARE @OffsetRows INT;  

Set values to variables

 SET @OffsetRows = @PageNo * @PageSize;  
 SET @SQLQuery = 'SELECT * FROM Users U WHERE USR_IsDeleted=0 ';  
 SET @SQLCountQuery = 'SELECT COUNT(*) FROM Users U WHERE USR_IsDeleted=0 ';  

Set filter expression to where clause

 IF(@filterExpression IS NOT NULL AND @filterExpression !='')  
           SET @WhereClause = @WhereClause + @filterExpression;  

Set search parameters to where clause

 IF(@Username IS NOT NULL AND @Username !='')  
           SET @WhereClause = @WhereClause + ' AND USR_Username LIKE ''%' + @Username +'%''';                 
      IF(@Name IS NOT NULL AND @Name !='')  
           SET @WhereClause = @WhereClause + ' AND (U.USR_FirstName LIKE ''%' + @Name + '%'' OR U.USR_LastName LIKE ''%' + @Name + '%'' OR U.USR_FirstName + '' '' + U.USR_LastName LIKE ''%' + @Name + '%'')';  
      IF(@Status != 2)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Status = ' + CAST(@Status AS NVARCHAR(10)) + ''  
      IF(@DepartmentId != 0)  
           SET @WhereClause = @WhereClause + ' AND U.USR_Department_ID  
  = ' + CAST(@DepartmentId AS NVARCHAR(50));  

Build both sql queries

 SET @SQLQuery = @SQLQuery + @WhereClause  
 SET @SQLCountQuery = @SQLCountQuery + @WhereClause  

Execute build sql query base on column sort parameters, export parameter & paging parameters
 

 IF(@Export = 1)  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder ;  
           END  
      ELSE  
           BEGIN  
                SET @SQLQuery = @SQLQuery + ' ORDER BY '+ @SortColumn + ' ' + @SortOrder +' OFFSET '+ CAST(@OffsetRows AS NVARCHAR(10)) +' ROWS FETCH NEXT '+ CAST(@PageSize AS NVARCHAR(10)) +' ROWS ONLY';  
           END  

 DECLARE @RowCount TABLE (Value int);  
      INSERT INTO @RowCount  
      EXECUTE(@SQLCountQuery)  
      SELECT @TotalCount = Value FROM @RowCount;  
 EXECUTE(@SQLQuery)  

How to implement Remote Validation in MVC

Here I am going to implement Remote Validation functionality in MVC.

First question came in mind when I heard about ‘Remote Validation’ is.
Why is it required?

Answer is, In MVC sometime require on the fly validation on some unique field like Username, Product Name, Barcode etc. On this type of requirement we can used Remote Validation.

What is unique ?

My Scenario: I have one sql table Product with different column like ProductId,  ProductName and ProductPrice. In this sql table ProuctName should be unique. And validate this field on the fly without refresh the browser.



Solution:

Step 1: Create model for Product and apply remote validation via Data Annotation.

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Web;  
 using System.ComponentModel.DataAnnotations;  
 using System.Web.Mvc;  
 namespace Product.Models  
 {  
   public class Product  
   {  
     [Required]  
     public long ProductId { get; set; }  
     [Required]  
     public decimal ProductPrice { get; set; }  
     [Required]  
     [Remote("IsProductNameUnique","Product",AdditionalFields="ProductName",ErrorMessage="This {0} is already used.")]  
     public string ProductName { get; set; }  
   }  
 }  

Step 2: Create method in controller to check the validation for that column. You can also send the additional parameters by adding AdditionFields attribute.

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Web;  
 using System.Web.Mvc;  
 using ItemCatalog.Models;  
 namespace Product.Controllers  
 {  
   public class ProductController : Controller  
   {  
     public ActionResult Product()  
     {  
       Product objProduct = new Catalog();  
       return View(objProduct);  
     }  
     public JsonResult IsProductNameUnique (Product objProduct)  
     {  
       return IsExist(objProduct.ProductName)  
         ? Json(true, JsonRequestBehavior.AllowGet)  
         : Json(false, JsonRequestBehavior.AllowGet);  
     }      
   }  
 }  

Step 3: Create view from created Product model.

 @model ItemCatalog.Models.Catalog  
 @{  
   ViewBag.Title = "Catalog";  
   Layout = "~/Views/Shared/_Layout.cshtml";  
 }  
   <script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>  
   <script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>  
 }  
 <h2>  
   Item</h2>  
 @using (Ajax.BeginForm("SaveCatalog", new AjaxOptions { HttpMethod = "POST"}))  
 {   
   <fieldset>  
     <div>  
       @Html.TextBoxFor(x => x.ProductName, Model.ProductName)  
       @Html.ValidationMessageFor(x => x.ProductName)  
     </div>  
     <div class="row">  
       @Html.TextBoxFor(x => x.ProductPrice, Model.ProductPrice)  
       @Html.ValidationMessageFor(x => x.ProductPrice)  
     </div>  
   </fieldset>  
   <div>  
     <input type="submit" value="Save" />  
   </div>  
 }  

What is difference between Table Variable and Temp Table

Feature	Table Variable	Temporary Table
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Indexes	Indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL	Statements are not allowed.	Statements are allowed.
Truncate table	Not allowed.	Allowed.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).

How to comma separator string use in Where clause in SQL

Using below function you can create table with valid value from comma separator string. And then INNER JOIN on your table.


CREATE FUNCTION [dbo].[fn_split] (
@value VARCHAR(MAX)
, @Delimeter CHAR(1)
)
RETURNS @SplitData TABLE (Data VARCHAR(50))
AS
BEGIN
DECLARE @XML XML

SELECT @XML = '' + REPLACE(@value, @Delimeter, '') + ''

INSERT INTO @SplitData
SELECT x.v.value('.', 'VARCHAR(50)') AS Data
FROM @XML.nodes('IDs/ID') x(v)

RETURN
END

Different type of WCF Binding

WCF supports nine types of bindings. 

Basic binding 

Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services. 

TCP binding 

Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF. 


Peer network binding 

Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it. 


IPC binding 

Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding. 


Web Service (WS) binding 

Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet. 


Federated WS binding 

Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security. 


Duplex WS binding 

Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client. 


MSMQ binding 

Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls. 


MSMQ integration binding 
Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients. 

Basic JavaScript Part-1

Introduction

JavaScript is the client side scripting language for the Web. Now a days all modern HTML, ASPX, ASP, JSP, PHP pages are using JavaScript to add additional client side functionality, validate input, communicate with web servers and much more.

JavaScript is very easy to learn. I'm sure you will enjoy it.

Lets start with something strange about JavaScript language. Try to understand JavaScript language by yourself.

Here i have mentioned some of the example, first line is input and second line is output generated by JavaScript language:

> parseInt("11", 2)

3

>"hello, world".replace("hello", "goodbye")

goodbye, world

> Boolean("")

false

> Boolean(234)

true

> "3" + 4 + 5

345

> 3 + 4 + "5"

75

> 1 === true

false

> true === true

true

> 1 / 0

Infinity

> -1 / 0

-Infinity

Main Data Type of JavaScript :

Type is building block of any type of language.

• Numbers
• Strings
• Booleans
• Functions
• Objects : Function, Array, Date, RegExp
• Null
• Undefined

Main Control structures of JavaScript :

JavaScript IF LOOP

var name = "kittens";

if (name == "puppies")

{

name += "!";

}

else if (name == "kittens")

{

name += "!!";

}

else

{

name = "!" + name;

}

name == "kittens!!"

JavaScript While Loop

while (true)

{

// an infinite loop!

}

JavaScript Do While loop

var input;

do {

input = get_input();

} while (inputIsNotValid(input))

JavaScript For loop

for (var i = 0; i < 5; i++) {

// Will execute 5 times

}

JavaScript Switch Case

switch(action) {

case 'draw':

drawit();

break;

case 'eat':

eatit();

break;

default:

donothing();

}

JavaScript logical operator :

var name = o && o.getName();

var name = otherName || "default";

var allowed = (age > 18) ? "yes" : "no";

Objects :

How to create object in javascript :

var obj = new Object();

var obj = {};

How to assign value and get value to object :

obj.name = "Simon";

var name = obj.name;

obj["name"] = "Simon";

var name = obj["name"];

obj.for = "Simon"; // Syntax error, because 'for' is a reserved word

obj["for"] = "Simon"; // works fine

var obj = {

name: "Carrot",

"for": "Max",

details: {

color: "orange",

size: 12

}

}

> obj.details.color

orange

> obj["details"]["size"]

12

Arrays :

How to create array in javaScript :

> var a = new Array();

> a[0] = "dog";

> a[1] = "cat";

> a[2] = "hen";

> a.length

3

> var a = ["dog", "cat", "hen"];

> a.length

3

> var a = ["dog", "cat", "hen"];

> a[100] = "fox";

> a.length

101

> typeof a[90]

undefined

Functions :

How to create function in javaScript:

function add(x, y) {

var total = x + y;

return total;

}

> add()

NaN // You can't perform addition on undefined

> add(2, 3, 4)

5 // added the first two; 4 was ignored

function avg() {

var sum = 0;

for (var i = 0, j = arguments.length; i < j; i++) {

sum += arguments[i];

}

return sum / arguments.length;

}

> avg(2, 3, 4, 5)

3.5

But it would be nice to be able to reuse the function that we've already created. Luckily, JavaScript lets you call a function and call it with an arbitrary array of arguments, using the apply()method of any function object.

> avg.apply(null, [2, 3, 4, 5])

3.5

The second argument to apply() is the array to use as arguments; the first will be discussed later on. This emphasizes the fact that functions are objects too.

How to create anonymous function in javaScript :

var avg = function() {

var sum = 0;

for (var i = 0, j = arguments.length; i < j; i++) {

sum += arguments[i];

}

return sum / arguments.length;

}

find out value of a and b :

> var a = 1;

> var b = 2;

> (function() {

var b = 3;

a += b;

})();

Custom objects :

function Person(first, last) {

this.first = first;

this.last = last;

}

Person.prototype.fullName = function() {

return this.first + ' ' + this.last;

}

Person.prototype.fullNameReversed = function() {

return this.last + ', ' + this.first;

}

Person.prototype is an object shared by all instances of Person. It forms part of a look-up chain (that has a special name, "prototype chain"): any time you attempt to access a property of Person that isn't set, JavaScript will check Person.prototype to see if that property exists there instead. As a result, anything assigned to Person.prototype becomes available to all instances of that constructor via the this object.

Inner functions

function betterExampleNeeded() {

var a = 1;

function oneMoreThanA() {

return a + 1;

}

return oneMoreThanA();

}

If you want to more detail then please follow below link.

Ref : https://developer.mozilla.org/en-US/docs/Web/JavaScript/A_re-introduction_to_JavaScript?redirectlocale=en-US&redirectslug=JavaScript%2FA_re-introduction_to_JavaScript