& Sql server fundas with Rajat Jaiswal

April 10, 2014

Match value in all the column of table


Dear All,

Recently one of colleague shared a new way of matching the value in all the columns.

Lets understand this by an example

We have person table in which  three column FirstName, LastName, MiddleName exists now suppose we need to find  in all the three column if any column contain letter “A”.

simply this query can be write as mention below

SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName like (‘%a%’) Or LastName like (‘%a%’) Or MiddleName like (‘%a%’)  ORDER BY BusinessEntityID

Now same query can be write in following manner

SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName + LastName + ISNULL(MiddleName,”) like (‘%a%’)  ORDER BY BusinessEntityID

So if you see above query we have concatenate  all the column and then apply like.

The important point is to remember here if a column contain NULL value then you have to convert it.

I hope this is another new way which I learnt hope it may be new for you.

So Enjoy,

Your host

Rajat Jaiswal

April 1, 2014

Build 2014–2nd–4th April

Filed under: — indiandotnet @ 2:21 am
Tags: ,

Dear all,

Its back enjoy build on 2nd & 4th April. Although I am very interested to join and see this great event live at San Francisco but unfortunately Sad smile not able to go there.

I will enjoy video

You can find more detail with below link

So enjoy learning


Rajat Jaiswal

March 23, 2014

Sammy.Js–simplest steps to create Restful web application (Single Page Applications) SPAs

Filed under:,JQUERY,JUERY,MVC,SPAs(Single Page Applications) — indiandotnet @ 6:45 pm
Tags: , , ,

Dear All,

Today I  am sharing with you one of the best and easy to understand JS which is “Sammy.Js”.

“Sammy.js” is 16 k java script over JQUERY which provide facility to developer to create RESTFUL SPAs (single Page Application).

It is very  clean, simple and easy to adapt java script.rl

You get Restful URL easily with this.

It added “#”  hash in URL. For example see below example

Let us understand this with example.

In last post you have seen how to consume web API with JQUERY

Here I am using the sample source code to move forward with sammy.js.

What we are planning here to create a page with menus and in this page we will call sammy.js on menu to call responsive URL to show states list ,particular state according to id and default page . as shown in below figure


Step 1:-  Add “Sammy.js” in the project using Nuget package manager as shown in below figure


Step 2:- Once the Sammy.Js added in your project you will find the Js in your project’s script folder


Step 3:-

Add sammy js reference in the project

   1: <script src="~/Scripts/jquery-2.1.0.min.js"></script>

   2:    <script src="~/Scripts/sammy-0.7.4.min.js"></script>

Step 4:-

Now below screen shot show the html which is designed to achieve our task


Step 5:- Now when Home menu link click by end user we will show  default page which in Index page in our case and its URL will be


And when user click on States link in navigation then it will show URL


And page will show list of states

last but not the least when user click on MP menu in navigation  then it will show URL


and page will show state name according to Id which is currently 1 in above url

Step 6:-

Now to achieve this we need to invoke Sammy.js  as shown below in below code

   1: <script type="text/javascript">


   3:       $(document).ready(function () {

   4:           var sammySPA = $.sammy("#main", function () { // initializing main div which is base for SPAs application content 


   6:               //Default url  

   7:               this.get('#/', function (context) {


   9:         ''); // clear what ever previously written in main div

  10:                   $("#main").append("<B> Showing default url content by code</b>"); // this code append the content in msin div 


  12:               });

  13:               //below code will fire when user click on state link 

  14:               this.get('#/States', function (context) {


  16:                   //calling the Web API to provide State name array 

  17:                   $.get("http://localhost:43597/api/mystate/", function (data, textStatus) {

  18:                       var str = "";

  19:                       // create list with sate name which again referencing to new url

  20:                       for (var i = 0; i < data.length; i++) {

  21:                           str = str + "<li><a href='#/States/" + (i+1).toString() + "'>" + data[i] + "</a></li>";

  22:                       }


  24:                       //Clean the main div 

  25:             '');

  26:                       //Apeend the data in main div

  27:                       $("#main").append("<ul>" + str + "</ul>");



  30:                   });


  32:               });


  34:               //States/stateId url 

  35:               this.get('#/States/:id', function (context) {

  36:                   //calling web api to get particular state by id

  37:                   $.get("http://localhost:43597/api/mystate/"+, function (data, textStatus) {


  39:             '');

  40:                       $("#main").append("<h1>" + data + "</h1>");



  43:                   });

  44:               });




  48:           });


  50: '#/'); //run sammy.js




  54:       });

  55:   </script>

Step 7:-

Let me explain the above code here

We have define $.sammy function which is bounded with the div whose.

$.sammy(‘#main’,function() {}).run(); this is the basic function to invoke sammy.js

Step 8:- Now to which url we need to entertain by which method like  get, put, Post, delete. We need to define our functionality according to our need in current example we have used get function.

this.get(‘#/’,function (context){});

The meaning of above line is when ever a request (default) invoke by end user just execute function as defined. in the function there is context parameter . Which help in accessing method type (get/post) , parameters

Step 9:-  Now when you run and click on States link you will get following screen


Step 10:- you can test it further I have added list of states also with dynamic url  so when you click on MP, RJ, MH etc in above list you will get a new URL according to our need


I hope this example will help you to create your own SPA application.

I defiantly say here “Sammy.js” rock. I like this script hope you will also.

Enjoy learning

your Host


March 21, 2014

How to consume Web API using JQUERY ?

Filed under:,MVC,Web API — indiandotnet @ 7:42 pm
Tags: ,



In last two articles we understood

 what is Web API ? (

and How to create it ? (

Now next is how to consume it ?

So here we will understand this by an example. I am pretty much sure you all  are comfortable with server side code so here in current example we using  client side code to consume web API.

In current example I have created a Web API with name “MyState” . The basic functionality of this Web API is to provide list of all the State with default get method and provide specific state according to provided id.

Let me show you snap shots of created code & running API here


Above figure shows default get method which response array of string (name of state)


Now to consume this Web API in our project we have added a view with following Java script

in below screen we have used a “get” JQUERY function and as we  know the API URL we wrote that and once the response from API returned we concatenated the response array with <li> tag and show as a innerHTML of stateName div as shown in below fig


Now when we run this program on index.cshtml we got following response


In current example we have used  “GET” method we can also consume post operation for INSERT/UPDATE/Delete

I hope you will like this post.


Your host


March 16, 2014

How to split entity classes in a separate project from Entity Framework in easy step

Filed under:,Entity Framework,MVC — indiandotnet @ 10:37 am
Tags: ,

Dear All,

Below are easy step to split the entity classes in a separate project. I have created a MVC sample project in below example. Now follow below steps

Step 1:- Add  a Ado.NET Entity Data Model  as shown below. I am using SQL Server “Friends” Database in this example.defaultedm 

Step 2:-

Once EDMX is added I got below structure as shown in below figure. Entities for each table object of Friends Database shown in below fig


Step 3:- Now as our goal to separate this entity class  I added a new class project as shown in below figure


Step 4:-

Now in this class project add  EF 5.x Db Context Generator  as shown in below figure


Step 5:-

Now  click on file in your new class project. Just edit  the Input file  as I edited in below fix you need to give your EDMX Path. 

Like I did “ using string inputFile = @”..\youredmxprojectname\youredmxname.edmx”


Step 6:-   Once this code is changed delete the all the  entities files from EDMX and rebuild  the project

Step 7:-  Once the file is generated you are good to go  just see as I got the files  in separate class project.



I hope you will achieve the same with above steps.

So enjoy learning Smile

Your Host,

Rajat Jaiswal

March 9, 2014

Start “Knockout.Js” for Beginners

Filed under:,JUERY,Knockout.js — indiandotnet @ 4:57 am
Tags: , ,

Dear All,

I heard a lot about “Knockout.js”.

So let us understand it first. “knockout.js “ is rich java script mode. It helps you to work on “MVVM” (Model View View Model) architecture pattern.

It helps you to work on “Reactive programming  or push model ". It means you can track your UI changes without much effort what ever changes done on View level (UI) it easily reflected in your viewmodel.

Let up try it and make a sample, but how to start it this is one of the concern.

So here I am with step by step approach how to start it

Step 1:-  Create a  new web project. I have selected empty template. so right now there is no item exists in it

Step 2:- click on nuget package as shown below


Step 3: We need to add first Jquery package so search Jquery package and add it as shown in below fig


Step 4:- Once JQUERY package is installed on your project now add new package which is knockout.Js package as shown in below fig


Step 5:- Once both this package installed we will add a new page which is default.html now add reference of both JQUERY & Knockout.js to this page

Now I am adding employee view as shown below


Let me explain each important line in this.

In our example we have created EmployeeReviewVM function which is basically a view model function for knockout js.In this function we have define Name & Rating two property and one more computed property (dependent on Rating property value).

we have used “ko.observable” it is used for push mode means whatever changed on View should reflect in viewModel also and vice versa.

Now we have used “ko.computed” function it means what ever the value changed of Rating the increment property calculated at runtime.

Step 5:

Now how to use in view part.


So if you see in the above picture we have used data-bind property which basically  bind the EmployeeViewmodel to html. We have used span so internally we have used text if we have used input tag then we can use value property.

Step 6: Now when you run it you will get following  page result.


I hope you are good to go with this. you can use different property like array , for each etc to fetch data.

You can use knockout js for SPA creation.

I hope this  tutorial give you basic idea.

Enjoy Knockout js.

Thanks & Best Regards,

Rajat Jaiswal

February 27, 2014

How to create Web API ?

Filed under:,MVC,Web API — indiandotnet @ 4:12 am
Tags: , ,

Dear Friends,

In Last article 

We have discussed what is Web API. Now in this article we will see how to create web API. So with out wasting much time lets start visual studio (In my case I am running VS 2013 web express).

So in my example I am creating a web API project for Friends. this API should return either all the friends or specific friend according to Id so lets start.

Step 1: Create a  new web project when you click on web project you will get following option. Select Web API option as shown in below screen shot.


You will get default project as below with defined folder structure


The main point here is to remember Model class should come in model folder & Controller class should come in controller folder

Step 2:- Once you click on Web API option project will open. Now Create a model (a simple class) with name Friend. this model class have basic friend’s property like his firstName, LastName,Id etc. as shown in below fig


Step 3:- Now create  a Friend controller so for this just add new item which is controller as shown in below fig


In the above option I have selected Web API 2 controller Empty template

things to remember here the name of controller class will be use later on in your API URL so select the name properly and the controller class name should followed by controller.

In my case I have created controller with following name =”FriendController.cs”. So now your project structure is as shown below


Step 4:- Now remember all the coding part need to be done here . I am creating two method here one method with name “GetAllFriends” and “GetFriendById”.

So as name suggest one will return list of all friends and one will return particular friend for a given id. If you see below instead of fetching data from database I have created a array of friends as shown.


Now in above fig you saw two method with different return type. Now concentrate on GetFriendByID It’s return type is HttpActionResult. so if you see below in that particular method the return type is OK which means status code 200. It means http response will be return in this method.

Now another interesting part is you can add different attribute with method like HTTPGET, HTTPPOST,HTTPDELETE etc Just see in below screen shot as shown below.


Now another interesting point is what if you want two attribute on same method then you can use below option which shown “AcceptVerbs”


Step 5:- Now our most part is done now you need to access web API but before accessing web API you need to check route and if something is missing their then you need to modify WebAPI config  which you will get in App_Start function. In my case I have added API/{controller}/{action}/{id} which was not exist earlier. as shown in below fig


Step 6:- Now we are good to go now just run the application and type in url http://localhost:yourport/API/Friend you will get list of all the friends as shown below


Now if you want to get specific friend with particular id then you have to write url like http://localhost:youport/API/Friend/1

You will get particular friend with that id as shown below



Isn’t it simple ?

I hope you understand how easy to create Web API. Now in next step we will learn how to consume the Web API.

Mean while you can create different web api.

So enjoy till than

Your host,


February 23, 2014

What is Web API ?

Filed under:,MVC — indiandotnet @ 4:30 am
Tags: ,

HTTP is simple way to connect broad range of clients, including browser, mobile & desktop application. To achieve above broad range a new framework is introduce on top of .NET framework which is called ASP.NET web API.

With ASP.NET Web API you can call simple RESTFul service. You can perform various GET, PUT, POST, DELETE operation using Web API.

Web API is enable to service data request from client in both (JASON /XML) format according to client need.

Web API introduced with ASP.NET MVC framework in later stage. If you are familiar with MVC application then you will think it is same.

For example you want to get list of all the customers then a URL may be like below

Now suppose you want specific customer with customerId = 1 then you may need to write following URL in browser

So above URL shows you can easily call the Web API services

You can easily host web API in IIS same as you host a web site.

I hope now we have a basic understanding of Web API.

In next topic we will discuss How to implement Web API.

Thanks & Best Regards,

Rajat Jaiswal

February 21, 2014

Get Admin rights to your local SQL server Express with this simple script–Unable to create database issue


Dear All,

Recently  I was facing the issue when login through my domain user account I am unable to create database. SQL Server throwing error like you don’t have permission.

By mistake I don’t have  “SA” username ‘s password.

I tried to search on internet  at after reading few articles I found below link which resolved my issue. It assign my domain user sysadmin permission.

I hope it will help you people also.

So enjoy downloading it and running it 

Thanks &  Best Regards,

Rajat Jaiswal

February 19, 2014

How to disable Right click on web page ?

Filed under:,JUERY,Utility — indiandotnet @ 9:36 am
Tags: , ,

Dear All,
Sometime, due to some security reference your application require to stop right click on web page.
In such case you need to write just simple code as shown below.

Right click disable

So now when you run your web page and right click you will get following message.

So just each code and you are good to go.

Hope you like it.

Thanks & Best Regards,
Rajat Jaiswal

Next Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 189 other followers