Creating Insert scripts in Sql Server 2008 R2

There were times when I wanted to experiment something in my project and needed a separate local DB with the real time data. It used to take lot of time to achieve it. but with Sql Server 2008 R2 management studio itself provides this feature handy.

This feature is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

Here are the steps to create Insert Data script using SQL Management Studio 2008 R2. You do not need any pugins or third party tools for this.

1. Right Click on Database name like ‘Master’
2. Select Tasks -> select Generate Scripts
3. Next select the tables or object to which you want to generate script
4. Go to set scripting options ,then click on advance button you find different options with catogories.
5. In the General category: go to type of data to script
6. we find 3 options for that
   1. Schema Only
   2. Data Only (really useful)
   3. Schema and Data
   4. Select the appropriate option and click on OK.

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

for a full discussion thread over this refer to the

For more insights refer to

http://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table

Hope it helps!!

Consuming WCF REST service

Update :  We can consume the WebApi services also in the same way using the code below. To read about Asp.Net WebApi services refer to my post

In my last post ( http://wp.me/p1hH3Q-20 ) i have shown how to create a basic REST enabled service using WCF.

Here i am going to discuss how to consume your REST service. There are couple of ways we can consume the service but i am taking an approach using WebClient.

To understand the WCF service structure i am trying to consume please have a look at  http://wp.me/p1hH3Q-20

using System;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.Services;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
using System.Net;
using System.Runtime.Serialization.Json;

<strong>// GET request</strong>
 {
 <strong>WebClient proxy = new WebClient();</strong>

//DownloadData sends data to server using the specific URI defined for your webmethod.
 byte[] data = proxy.<strong>DownloadData</strong>("http://localhost:3971/Service1.svc/GetEmployees");

 Stream stream = new MemoryStream(data);

// serializing the returned type which is Employee[]
 DataContractJsonSerializer obj = new DataContractJsonSerializer(typeof(Employee[]));
 var arrEmp = obj.ReadObject(stream) as Employee[];
 gvEmployees.DataSource = arrEmp as Employee[];
 gvEmployees.DataBind();
 }

</pre>
<strong>// POST request</strong>
 {

Employee newItem = new Employee() { EmployeeID = "112", Name = "Prasdjf", Email = "test@tes.com" };
 WebClient proxy = new WebClient();
 proxy.Headers["Content-type"] = "application/json";
 MemoryStream ms = new MemoryStream();

//serialize the input parameter

DataContractJsonSerializer serializerToUpload = new DataContractJsonSerializer(typeof(Employee));

serializerToUpload.WriteObject(ms, newItem);

// upload the data to call AddEmployee web method which returns Employee object.
 byte[] data = proxy.UploadData("http://localhost:3971/Service1.svc/AddEmployee", "POST", ms.ToArray());
 Stream stream = new MemoryStream(data);
// serialize the returned type
DataContractJsonSerializer obj = new DataContractJsonSerializer(typeof(Employee));

// read the returned type as Employee

var resultSampleItem = obj.ReadObject(stream) as Employee;
 Response.Write(resultSampleItem.EmployeeID + " => " + resultSampleItem.Name + " => " +
 resultSampleItem.Email);
<pre>}

Things to note:
1. There is no need to add any web reference to our service as all web methods are available as URI.
2. DataContractJsonSerializer is really helpful if your response format is Json. JSON is the lightest data format accepted by almost all the parties. XML is also supported.
3. There are other approaches too, using HttpWebRequest/HttpWebResponse is another way of doing it.
Consuming REST web service by Jquery $.ajax method.
Here is a jquery call to GetEmployeeDetails webmethod to a wcf service. Method signature is like below

</pre>
[OperationContract]
 [WebInvoke(Method = "POST", UriTemplate = "/GetEmployeeDetails/{EmployeeID}", ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json)]
 Employee GetEmployeeDetails(string EmployeeID);
<pre>

Notice the URL http://localhost:3971/Service1.svc/GetEmployeeDetails/54543 which has input parameter in the last. it depends on how URI is defined for our webmethod.

</pre>
<script type="text/javascript">
 var Type = "POST";
 var Url = "http://localhost:3971/Service1.svc/GetEmployeeDetails/54543";
 var Data = {};
 var ContentType = "application/json;charset=utf-8";
 var DataType = "json";
 var ProcessData = false;
 var method = "GetEmployeeDetails";

$("#btnCallRest").click(function () {

// to enable cross - site scripting
jQuery.support.cors = true;
 $.ajax({
 type: Type, //GET or POST or PUT or DELETE verb
 url: Url, // Location of the service
 data: Data, //Data sent to server
 contentType: ContentType, // content type sent to server
 dataType: DataType, //Expected data format from server
 processdata: ProcessData, //True or False
 success: function (result) {//On Successfull service call
 ServiceSucceeded(result);
 },
 error: ServiceFailed// When Service call fails
 });

});

function ServiceFailed(result) {
 alert('Service call failed: ' + result.status + '' + result.statusText);
 Type = null;
 Url = null;
 Data = null;
 ContentType = null;
 DataType = null;
 ProcessData = null;
 }

function ServiceSucceeded(result) {
 if (DataType == "json") {
 if (method == "GetEmployeeDetails") {
 alert(' ID :'+ result.EmployeeID + '\n Name:' + result.Name + '\n Email : ' + result.Email);
 }
 else {
 resultObject = result.GetEmployeeResult;
 var string = result.EmployeeID + " \n " + result.Name + " \n " + result.Email;
 alert(string);
 }
 }
 }
<pre>

That’s it! we are good to go.
Hope it helps!!

WCF REST service – part 1

REST (Representational State Transfer) has emerged in the last few years alone as a predominant Web service design model. In fact, REST has had such a large impact on the Web that it has mostly displaced SOAP- and WSDL-based interface design because it’s a considerably simpler style to use.
But i am not going to talk about SOAP Vs REST. This post only describes how to implement REST web services usinf WCF model. Again WCF is just another way of implement REST web service.

Whenever we consume a WCF service from a client, we need to create a proxy and need to import all the types(xsd) to your client. Well, nothing wrong with this but think about a scenario if we can use all the web methods as they were a resource on a remote machine. REST will enable us to consume our web methods with good old Http verbs (GET,POST,PUT,DELETE).
Enough discussion right? Lets start some code 🙂

Create WCF service
Data Contract

 <code> [DataContract]
 public partial class Employee
 {
 [DataMember]
 public string Name { get; set; }</code>

[DataMember]
 public string EmployeeID { get; set; }

[DataMember]
 public string Email { get; set; }
 }

Service contract Interface

 <code></pre>
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;

using System.ServiceModel.Activation;
<pre>[ServiceContract]
 public interface IEmployeeService
 {
 [OperationContract]
 [WebGet(UriTemplate = "/GetEmployees", ResponseFormat = WebMessageFormat.Json,RequestFormat = WebMessageFormat.Json )]
 Employee[] GetEmployees();</code>

[OperationContract]
 [WebInvoke(Method = "POST", UriTemplate = "/GetEmployeeDetails/{EmployeeID}", ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json)]
 Employee GetEmployeeDetails(string EmployeeID);

[OperationContract]
 [WebInvoke(UriTemplate = "/AddEmployee", Method = "POST",RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
 Employee AddEmployee(Employee emp);

[OperationContract]
 [WebInvoke(UriTemplate = "/DeleteEmployee", Method = "DELETE", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
 bool DeleteEmployee(string EmployeeID);
 }

1. UriTemplate is the url format which a client will request. lets say our service is accessed at “http:localhost:3839/Service1.svc” then “http:localhost:3839/Service1.svc/GetEmployees” will call the GetEmployees method which returns Employee[].

2. ResponseFormat/RequestFormat is the data format which is used to send/receive the data to/from server. JSON and XML are two formats supported by WCF.

3. WebGet and WebInvoke WebGet is used for GET requests and WebInvoke can specify any http verb with Method=”GET|POST|DELETE|PUT”.

Service Implementation

 <code>[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
 [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
 public class EmployeeService : IEmployeeService
 {
 private List employees = null;
 #region IEmployeeService Members
 public Employee[] GetEmployees()
 {
 employees = new List();
 employees.Add(new Employee() { EmployeeID = "1", Email = "test@test.com", Name = "Praveen" });
 employees.Add(new Employee() { EmployeeID = "2", Email = "test@test.com", Name = "Prashant" });
 employees.Add(new Employee() { EmployeeID = "3", Email = "test@test.com", Name = "Ritu" });
 return employees.ToArray();
 }</code>

public Employee GetEmployeeDetails(string EmployeeID)
 {
 return new Employee() { EmployeeID = EmployeeID, Email = "tdfgdgest@test.com", Name = "sfsdgg" };
 }

public Employee AddEmployee(Employee emp)
 {
 return emp;
 }

public bool DeleteEmployee(string EmployeeID)
 {
 // delete in DB
 return true;
 }
 #endregion
 }

Note: You can replace these methods with something significant code 🙂 here it is just sample.

Web.config settings

<system.serviceModel>
 <services >
 <service name="WCFSamples.EmployeeService" behaviorConfiguration="restBehavior" >
 <endpoint address=""
 binding="webHttpBinding"
 contract="WCFSamples.IEmployeeService"
 behaviorConfiguration="rest" >
 <identity >
 <dns value="localhost" />

</identity>

</endpoint>
 <endpoint contract="IMetadataExchange" binding="mexHttpBinding" address="mex" />
 </service>
 </services>
 <behaviors>
 <endpointBehaviors>
 <behavior name="rest">
 <webHttp />
 </behavior>
 </endpointBehaviors>
 <serviceBehaviors>
 <behavior name="restBehavior">
 <serviceMetadata httpGetEnabled="true" />
 <serviceDebug includeExceptionDetailInFaults="false" />
 </behavior>
 </serviceBehaviors>
 </behaviors>
 <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
 </system.serviceModel>

Note: For REST service Binding is always webHttpBinding also endpointbehavior also must be added like below.

<endpointBehaviors>
<behavior name="rest">
<webHttp />
</behavior>
</endpointBehaviors>

.Svc file markup 

</code>

<%@ ServiceHost Language="C#" Debug="true" Service="WCFSamples.EmployeeService" CodeBehind="Service1.svc.cs" %>

This is all we need to know about creating a WCF REST enabled service.
Next post will describe how to consume it in different ways.

Hope it helps!!

Jquery – Calculate number of days between two dates.

This is a very common requirement we get almost in every project that’s why It is really helpful to keep it handy.


function calculateDifference() {
var startDate = $("#start_date").val();

if (startDate == “”) {
alert(“Enter start date”);
return;
}
var endDate = $(“#end_date”).val();
if (endDate == “”) {
alert(“Enter end date”);
return;
}

var startDateSplit = startDate.split(“/”);
var endDateSplit = endDate.split(“/”);

var stDate = new Date(startDateSplit[2], startDateSplit[0] – 1, startDateSplit[1]);
var enDate = new Date(endDateSplit[2], endDateSplit[0] – 1, endDateSplit[1]);

var difference = (enDate.getTime() – stDate.getTime()) / (1000 * 60 * 60 * 24);

$(“#date_difference”).val(difference);
}

I hope it helps.