Connectivity with Sql Server 2008 using Web Services………………………..

Connectivity  with Sql Server 2008 using Web Services………………………..

First we create  WebMethod in Web Service which  web service name is  WebService.cs and Method name is Login…………………………………….

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

using System.Data;

/// <summary>

/// Summary description for WebService

/// </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 WebService : System.Web.Services.WebService {

    public WebService () {

        //Uncomment the following line if using designed components

        //InitializeComponent();

    }

//create  WebMethod  for  Login………………………………….

    [WebMethod]

    public  Boolean Login (string username,string password)

    {

        SqlConnection con = new SqlConnection();

        con.ConnectionString="Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

        SqlCommand cmd=new SqlCommand();

        cmd.CommandText="select 1 from logintable where username='"+username+"' and Password='"+password+"'";

        cmd.Connection=con;

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)

        {

            return true;

        }

        else

        {

            return false;

        }

    }

//create  WebMethod  for  Details………………………………….

    [WebMethod]

    public DataSet Details(string tbname)

    {

        SqlConnection con = new SqlConnection("Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True");

        SqlCommand com = new SqlCommand("select * from " + tbname, con);

        SqlDataAdapter da = new SqlDataAdapter(com);

        DataSet ds = new DataSet();

        da.Fill(ds);

        return ds;

      

    }

We take  another  Website  and  call above  services in this……………………..

//code  for login ……………………………..

  protected void BtnLogin_Click(object sender, EventArgs e)

    {

        abc.WebService ww = new abc.WebService();

        if (ww.Login(txtusername.Text, txtpassword.Text))

        {

            Response.Redirect("WelCome.aspx");

        }

        else

        {

            //string msg = @"<script>alert('username or password is not match')</script>";

            //Page.ClientScript.RegisterStartupScript(this.GetType(), "message", msg, true);

Or

         // For this code  …. Script Manager must exist in web page

            string msg = @"alert('userid or password not match');";

          ScriptManager.RegisterStartupScript(this, this.GetType(), "message", msg, true);

  

           

        }

//code  for gettable from  database  ……………………………..

  protected void btngettable_Click(object sender, EventArgs e)

    {

        try

        {

            abc.WebService ww = new abc.WebService();

            DataSet ds = ww.Details(txttbname.Text);

            GridView1.DataSource = ds;

            GridView1.DataBind();

        }

        catch (Exception ex)

        {

            lblmessage.ForeColor = Color.Red;

            lblmessage.Text = ex.Message;

        }

      

    }