Microsoft Dot Net Master: August 2011

Tuesday, August 30, 2011

Asp.net WebService or Creating and Consuming WebService in asp.net or Create and call webservice in asp.net or how to create webservice and how to use webservice in asp.net

What is Web Service?

Web Service is an application that is designed to interact directly with other applications over the internet. In simple sense, Web Services are means for interacting with objects over the Internet. The Web serivce consumers are able to invoke method calls on remote objects by using SOAP and HTTP over the Web. WebService is language independent and Web Services communicate by using standard web protocols and data formats, such as

HTTP
XML
SOAP

Advantages of Web Service

Web Service messages are formatted as XML, a standard way for communication between two incompatible system. And this message is sent via HTTP, so that they can reach to any machine on the internet without being blocked by firewall.

Examples for Web Service

Weather Reporting: You can use Weather Reporting web service to display weather information in your personal website.

Stock Quote: You can display latest update of Share market with Stock Quote on your web site.

News Headline: You can display latest news update by using News Headline Web Service in your website.

In summary you can any use any web service which is available to use. You can make your own web service and let others use it. Example you can make Free SMS Sending Service with footer with your advertisement, so whosoever use this service indirectly advertise your company... You can apply your ideas in N no. of ways to take advantage of it.

Frequently used word with web services

What is SOAP?

SOAP (simple object access protocol) is a remote function calls that invokes method and execute them on Remote machine and translate the object communication into XML format. In short, SOAP are way by which method calls are translate into XML format and sent via HTTP.

What is WSDL?

WSDL stands for Web Service Description Language, a standard by which a web service can tell clients what messages it accepts and which results it will return.

WSDL contains every detail regarding using web service and Method and Properties provided by web service and URLs from which those methods can be accessed and Data Types used.

What is UDDI?

UDDI allows you to find web services by connecting to a directory.

What is Discovery or .Disco Files?

Discovery files are used to group common services together on a web server. Discovery files .Disco and .VsDisco are XML based files that contains link in the form of URLs to resources that provides discovery information for a web service. Disco File contains URL for the WSDL, URL for the documentation and URL to which SOAP messages should be sent.

Before start creating web service first create one table in your database and give name UserInformation in my code I am using same name and enter some dummy data for our testing purpose

Column Name	Data Type	Allow Nulls
UserId	Int(Set Identity=true)	No
UserName	Varchar(50)	Yes
FirstName	Varchar(50)	Yes
LastName	Varchar(50)	Yes
Location	Varchar(50)	Yes

Now we will see how to create new web service application in asp.net

Open visual studio ---> Select File ---> New ---> Web Site ---> select ASP.NET Web Service

Now our new web service ready our webservice website like this

Now open your Service.cs file in web service website to write the code to get the user details from database

Before writing the WebMethod in Service.cs first add following namespaces

using System.Xml;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

After adding namespaces write the following method GetUserDetails in Service.cs page

[WebMethod]

public XmlElement GetUserDetails(string userName)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());

con.Open();

SqlCommand cmd = new SqlCommand("select * from UserInformation where UserName like @userName+'%'", con);

cmd.Parameters.AddWithValue("@userName", userName);

cmd.ExecuteNonQuery();

SqlDataAdapter da = new SqlDataAdapter(cmd);

// Create an instance of DataSet.

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

// Return the DataSet as an XmlElement.

XmlDataDocument xmldata = new XmlDataDocument(ds);

XmlElement xmlElement = xmldata.DocumentElement;

return xmlElement;

}

Here we need to remember one point that is adding [WebMethod] before method definition because we need to access web method pulically otherwise it’s not possible to access method publically. If you observe above code I converted dataset to XmlElement t because sometimes we will get error like return type dataset invalid type it must be either an IListSource, IEnumerable, or IDataSource to avoid this error I converted dataset to XmlElement.

Here we need to set the database connection in web.config because here I am getting database connection from web.config

</connectionStrings>

Now run your web service it would be like this

Our web service is working fine now we need to know how we can use webservice in our application? Before to know about using web service in application first Deploy your webservice application in your local system if you want to know how to deploy application in your local system check this link deploy application in local system

How to Use Web service in web application?

By using this webservice we can get the user details based on username. For that first create one new web application

Open visual studio ---> Select File ---> New ---> Web Site ---> select ASP.NET Web Site

After creation of new website right click on solution explorer and choose “Add web reference” that would be like this

After select Add Web reference option one window will open like this

Now enter your locally deployed web service link and click Go button after that your web service will found and window will looks like this

Now click on Add Reference button web service will add successfully. Now open your Default.aspx page and design like this

<title>Getting Data from WebService</title>

</head>

<body>

<div>

<table>

<tr>

<td>

<b>Enter UserName:</b>

</td>

<td>

<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

</td>

<td>

<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />

</td>

</tr>

</table>

</div>

<div>

<asp:GridView ID="gvUserDetails" runat="server" EmptyDataText="No Record Found">

</asp:GridView>

</div>

</form>

</body>

</html>

Now in code behind add following namespaces

using System.Data; using System.Xml;

After adding namespaces write the following code in code behind

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

BindUserDetails("");

}

protected void BindUserDetails(string userName)

{

localhost.Service objUserDetails = new localhost.Service();

DataSet dsresult = new DataSet();

XmlElement exelement = objUserDetails.GetUserDetails(userName);

if(exelement!=null)

{

XmlNodeReader nodereader = new XmlNodeReader(exelement);

dsresult.ReadXml(nodereader, XmlReadMode.Auto);

gvUserDetails.DataSource = dsresult;

gvUserDetails.DataBind();

}

else

{

gvUserDetails.DataSource = null;

gvUserDetails.DataBind();

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

BindUserDetails(txtUserName.Text);

}

Now run your application and check output

Wednesday, August 24, 2011

SQL Server 2008 ERROR: Saving changes are not permitted. The Changes you have made require the following tables to be dropped and re-created.

Introduction

Here I will explain how to solve problem in SQL Server 2008 Saving changes are not permitted.

Description

I installed SQL Server 2008 and I created one new database after that I created one table and saved it everything working fine again I opened newly created table to change column name previously I entered wrong name again I changed column name and if I am trying to save that modifications I got error like this

After that I have changed some of options to rectify this error in SQL Server 2008

Follow these steps to solve this problem

1) 1) Open SQL Server Management Studio

2) 2) Go to Tools

3) 3) In that go to Options

4) 4) In leftside treeview select Designers

5) 5) In Designers select Table and Designers

6) 6) After that in right side “Uncheck” check box of Prevent saving changes that require table re-creation.

Now modify your table and save it now that table will save successfully into your database

how to get the column names and corresponding datatypes in particular table using SQL Server

Introduction:

Here I will explain how to get the column names and corresponding datatypes in particular table using SQL Server.

Description:

I have one table with lot columns in database at that time I tried to know the column names and corresponding datatypes and their maximum sizes in particular table for that I written the following query in SQL server to get column names and datatypes in particular table.

USE MySampleDB

SELECT column_name 'Column Name',

data_type 'Data Type',

character_maximum_length 'Maximum Length'

FROM information_schema.columns

WHERE table_name = 'Country'

Demo

If anyone gets error with above query like

Invalid object name 'information_schema.columns'

This error because of case sensitive databases to rectify this error we need to write query like this

USE CRMK

SELECT column_name 'Column Name',

data_type 'Data Type',

character_maximum_length 'Maximum Length'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'Country'

Here you need to enter your table name if that is in Caps lock you should enter caps lock name of your table only because it it is case sensitive

Saturday, August 20, 2011

Explain different types of constraints in SQL Server | SQL Server Constraints | SQL Server Primary Key Constraints | SQL Server Foreign Key Constraints | SQL Server Unique Key Constraints | SQL Server Not Null Constraint | SQL Server Check Constraints

Introduction:

Here I will explain what constraints available in SQL Server are and I will explain how to use constraints in database using SQL server.

Description:

A Constraint is a property that we can assign to column in table. By assigning constraint property on column we can prevent the users to enter inconsistent of data in columns. We can assign these Constraint properties during the time of creation of table (By Using CREATE TABLE statement) or during the time of changing the existing table structure (By Using ALTER TABLE statement).

In SQL we have different types of constraints are available those are

1. Primary Key Constraint

2. Unique Key Constraint

3. Foreign Key Constraint

4. Not Null Constraint

5. Check Constraint

Now I will explain about each constraint clearly

SQL Primary Key Constraint:

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values.

Example of creating Primary Key constraint during the time of CREATE TABLE

Create Table SampleUserDetail

(

UserID integer PRIMARY KEY,

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

)

Example of creating Primary Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD PRIMARY KEY (UserID)

To Drop Primary Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint UserID

SQL Unique Key Constraint

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table.

Example of creating Unique Key constraint during the time of table creation

Create Table SampleUserDetail

(

UserID integer ,

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

CONSTRAINT us_UserId UNIQUE (UserID)

)

Example of creating Unique Key constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetail ADD CONSTRAINT us_UserId UNIQUE (UserID)

To Drop Unique Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint us_UserId

SQL Foreign Key Constraint

A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables.

Example of Foreign key constraint

Create one table with primary key and give name as UserDetails

UserID	UserName	FirstName	LastName
1	SureshDasari	Suresh	Dasari
2	PrasanthiDonthi	Prasanthi	Donthi
3	MaheshDasari	Mahesh	Dasari

After create another table with Foreign Key and give name as SalaryDetails

SalID	Salary	UserID
1	10000	1
2	20000	2
3	30000	3

The column “UserID” is a primary key in UserDetails table

The column “SalID” is a foreign key in SalaryDetails tables

If you observe above two tables UserID in “UserDetails” table points to UserID in “SalaryDetails”

Example of creating Foreign Key constraint during the time of table creation

Create Table SalaryDetails

(

SalaryID integer ,

Salary integer,

UserID varchar(50),

PRIMARY KEY (SalaryID),

CONSTRAINT fk_SalaryID FOREIGN KEY(UserID)

REFERENCES UserDetails(UserID)

)

Example of creating Foreign Key constraint during the time of ALTER TABLE

ALTER TABLE SalaryDetails ADD CONSTRAINT fk_SalaryID FOREIGN KEY (UserID) REFERENCES UserDetails(UserID)

To Drop Foreign Key constraint on table use the below statement

ALTER TABLE SampleUserDetail DROP Constraint fk_SalaryID

SQL Not NULL Constraint:

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column

Example of creating NOT NULL constraint during the time of table creation

Create Table SampleUserDetail

(

UserID integer NOT NULL,

UserName varchar(50) NOT NULL,

FirstName varchar(50),

LastName varchar(50)

)

SQL Check Constraint:

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint.

Example of creating Check constraint during the time of CREATE TABLE

Create Table SampleUserDetail

(

UserID integer NOT NULL CHECK(UserID > 0),

UserName varchar(50),

FirstName varchar(50),

LastName varchar(50)

)

Example of creating Check constraint during the time of ALTER TABLE

ALTER TABLE SampleUserDetails ADD CONSTRAINT chk_UserID CHECK(UserID > 0)

To Drop Check constraint on table use the below statement

ALTER TABLE SampleUserDetails DROP CONSTRAINT chk_UserID