Creating Web Service

In this session we will create Web Service with required methods . Just create new project from Visual Studio and in this project add a web service and named it Student Service . Then you will see in Solution explorer  two files are created, one is App_Code where out student service class will be present and other one is studentservice.svc where its mentioned how web service will be injected. We need to define the connection string into out web.config file . Here is the code for defined the connection string .

 <connectionStrings>

<add name="StudentConnectionString" connectionString="Data Source=.;Initial Catalog=3TierInWindowsApplication;Integrated Security=True"  providerName="System.Data.SqlClient" />

    </connectionStrings>

Here is the code for insert , update ,delete and retrieve operation ,just copy and paste it .

 using System;

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
/// <summary>
/// Summary description for StudentService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class StudentService : System.Web.Services.WebService
{

    public StudentService()
    {

        //Uncomment the following line if using designed components
        //InitializeComponent();
    }

    [WebMethod]
    public DataTable Get()
    {
        string constr = ConfigurationManager.ConnectionStrings["StudentConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Student"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        dt.TableName = "Student";
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }
    }

    [WebMethod]
    public void Insert(string Name, string Address, string Email,string Mobile)
    {
        string constr = ConfigurationManager.ConnectionStrings["StudentConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("AddNewStudent",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", Name);
                cmd.Parameters.AddWithValue("@Address", Address);
                cmd.Parameters.AddWithValue("@EmailID", Email);
                cmd.Parameters.AddWithValue("@Mobile", Mobile);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    [WebMethod]
    public void Update(int Student_Id, string Name, string Address, string Email, string Mobile)
    {
        string constr = ConfigurationManager.ConnectionStrings["StudentConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("UpdateStudent", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Student_Id", Student_Id);
                cmd.Parameters.AddWithValue("@Name", Name);
                cmd.Parameters.AddWithValue("@Address", Address);
                cmd.Parameters.AddWithValue("@EmailID", Email);
                cmd.Parameters.AddWithValue("@Mobile", Mobile);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    [WebMethod]
    public void Delete(int Student_Id)
    {
        string constr = ConfigurationManager.ConnectionStrings["StudentConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("DeleteStudent",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Student_Id", Student_Id);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

Let save all the changes and build it then run web service into local host .

In the next session we will see how to call this web service from asp.net application.