Friday, January 30, 2015

How to use AdventureWorks2012 in SQL SERVER Management Studio

Following steps will give us idea how to use AdventureWorks2012 in SQL SERVER Management Studio 2012

Step 1:
Download AdventureWorks2012 database from following link

https://msftdbprodsamples.codeplex.com/releases/view/55330

This will download two files for database AdventureWorks2012 as data file and log file.

Step 2:
Copy these two files to following location
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA


Step 3:
Now open SQL SERVER management studio.
Right click Databases and select Attach..
Add AdventureWorks2012_Data.mdf file.
Refresh database to view AdventureWorks2012 database in SQL SERVER management studio.


 

Thursday, January 29, 2015

How to use Adventure Works 2014 Sample Databases in SQL SERVER

Follow the steps below to use  Adventure Works 2014 Sample Databases in SQL SERVER Management Studio 2012.

Step 1:

To download Adventure Works database from codeplex click here
This will download database backup zip file.

Step 2 :

Copy this backup to the location
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup

Step 3:

Execute following script on SQL SERVER management studio


USE [master]

RESTORE DATABASE AdventureWorks2014

FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorks2014.bak'

WITH MOVE 'AdventureWorks2014_data'

TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf',

MOVE 'AdventureWorks2014_Log'

TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf'

,REPLACE




Step 4 :
 
Refresh databases in object explorer to view AdventureWorks2014 database.

 

 
 
 



 

 

 
 

 

Thursday, January 22, 2015

WCF : How to

As mentioned in my earlier post of web services in ASP.NET, web services support HTTP protocol and do not maintain any state information.

However WCF (Windows Communication Foundation) services can be hosted on different types of application and more secured compared to web services.
 
WCF service endpoint is composed of
A : Address  -> link to service endpoint address -> where the endpoint can be found
B : Binding   -> port type -> how a client can communicate with the end point
C : Contract -> message ->  message exchange patterns and operations -> available operations
 
Simple example of WCF service is explained below. This example will give you an idea how WCF works. For more complex functionalities, keep visiting my blog.
 
1. Create WCF service application as shown below.
 
 

 
 
 
2. WCF service contract (C : Contract) is implemented in Interface section IService1. So methods in this interface will help user to understand available operation with this service.
 

 
[ServiceContract]

public interface IService1
 
 
{
 
[OperationContract]
string GetData(int value);
[OperationContract]
CompositeType GetDataUsingDataContract(CompositeType composite);
// TODO: Add your service operations here
[OperationContract]
int sum(int num1, int num2);
 
 
}

 
 
Note : WCF interface by default has two methods  GetData() and  GetDataUsingDataContract().
Also interface IService1 is defined public so that it should be accessible to code outside this project. 
 
2. This interface is inherited in class Service1
public class Service1 : IService1
 
 
{
 
public string GetData(int value)
 
 
{
 
return string.Format("You entered: {0}", value);
 
 
}
 
public CompositeType GetDataUsingDataContract(CompositeType composite)
 
 
{
 
if (composite == null)
 
 
{
 
throw new ArgumentNullException("composite");
 
 
}
 
if (composite.BoolValue)
 
 
{
 
composite.StringValue += "Suffix";
 
 
}
 
return composite;
 
 
}
 
public int sum(int num1, int num2)
 
 
{
 
return num1 + num2;
 
 
}
}
 
 
 
3. Run and execute this WCF service you can see WCF service URL : This is Address part of WCF.
4. Now create another website project and add service reference for our earlier created WCF service  to this newly created project as follows.
 
 
 
5. Write following code and execute to see the result.
 
protected void Button1_Click(object sender, EventArgs e)
 
 
{
 
ServiceReference1.Service1Client sc = new ServiceReference1.Service1Client();
int sum=sc.sum(Convert.ToInt32(TextBox1.Text), Convert.ToInt32(TextBox2.Text));
 
 
Label1.Text = sum.ToString();
}
 
 
 
 
 
 

 
 
 
 


 
 

Wednesday, January 21, 2015

ASP.NET WebService : How to

Web Service is an entity which is used to provide various functionality over the internet irrespective of programming language, OS and platform.
 
ASP.NET web service is easy to create and  is accessible over HTTP (SOAP).
 
Following example shows how to use web service in ASP.NET
 
1. To create Web service in VS 2013, create an empty project and then add web service item.
 
2. Web class  test uses getUser  web method to implement desired functionality.
Objective of getUser() method is to get User details from database for a particular city.
 
[WebMethod]

public DataSet getUser(string strCity)
 
 
 

{

 
//get City Users

string strConnect = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnectKBTestDB"].ConnectionString;

SqlConnection con = new SqlConnection(strConnect);
 
 

con.Open();

 
SqlCommand cmd = new SqlCommand("SP_City_User", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);
 
 
 
 
 
 
SqlParameter City=new SqlParameter();

City.ParameterName=("@City");
 
 

City.Value=strCity;

cmd.Parameters.Add(City);

 
DataSet ds = new DataSet();
 
 

da.Fill(ds);

con.Close();

 
return ds;
 
 

}
 
 
Run and execute web service to see the result.
Note : asmx file created as a result of this webservice as http://localhost:62798/test.asmx which would look like
 
 
 
 
 
 
3. In order to utilize this web service method, create a sample website project.
Add Service Reference (Solution Explorer --> Add --> Service Reference )
 
4. Copy the URL of web service created just now
http://localhost:62798/test.asmx and paste it to the reference as shown in figure below
 
 
 
5. So now we have to consume this web service , web method in our web application.
When we click OK button (Figure above), .NET framework would generate a series of classes and methods in order to use webmethods. Code to use the same is as follows.
On our web page, we have a dropdown list which shows list of available cities. On selecting a particular city (SelectedIndexChanged event) our task is to show webservice result in gridview.
 
 
 
 
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
 
 
{
 
ServiceReference1.testSoapClient tc = new ServiceReference1.testSoapClient();
DataSet ds=tc.getUser(ddlCity.SelectedItem.Text);
 
 
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
 
 
6. As we run web service, we get result as follows.