How to Create web service using Database and How to Consume web service in ASP.NET

By: Mohammad Tuaha               Download Source Code

How to Create web service using Database and How to Consume web service.

Previous Web service -->  Series

1.  How to use web service  or how to consume web service using ASP.NET
2.  How To create Web service and Consume in web page using ASP.NET

Requirement
1. SQL server 2008 - upper
2. Visual Studio 2010 - upper

Introduction 

Welcome to web service learning. Today our learning is Database + web Service = Database web service.

I am discussing about how to create web service for use SQL server database table data sample and use into any web page.

I am using sample Database Northwind.mdf Data Sample. Northwind database is available for download.

Procedure

First Create a Store procedure using [Employees] table which name SP_EmployeesInfo
 

Our SP is ready to use into web service file .asmx [WebService.cs]

Here is the main web service Method EmployeesInfoFromDatabase.

[WebMethod]
    public DataSet EmployeesInfoFromDatabase(string value)
    {

        String  ConnectionString =         ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand("SP_EmployeesInfo", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@value", value);

        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adap.Fill(ds);
        return ds;          // Return whole table

        ////

        //string domname = ds.Tables[0].Rows[1].ItemArray[1].ToString();
        // return DataSet ds.Tables[0].Rows[1].ItemArray[1].ToString();

    }

Now Run it our web service is ready to use.

How to Consume web service ?

It’s database dependent web service so we have to assign DB name, UID into connection String so Please open web.config file and provide your Database name , User ID , Password.

<add name="connectionStr" connectionString="Data Source=(local);Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sapass123!" providerName="System.Data.SqlClient"/>

Add web Reference

Now Copy the web service URL   

http://localhost:55011/WebServiceWithDB/WebService.asmx

Add web Reference --> paste Link into URL textbox

or

 follow our previous Article how consume web service

How To create Web service and Consume in web page using ASP.NET

Now add a web page

In Default.aspx page add one textbox , button and a label.

This is the button event code. 

protected void BtnSave_Click(object sender, EventArgs e)
    {

        DatabaseWebservice.WebService DBWS = new DatabaseWebservice.WebService();

        //create our DataSet to hold the member information returned
        DataSet dsMembers = new DataSet();

      

        //set our DataSet to the GetAllUsers Method of our web service
        dsMembers = DBWS.EmployeesInfoFromDatabase(txtEmpID.Text);

       //// grdviewuserDetails.AutoGenerateColumns = true;
        grdviewuserDetails.EmptyDataText = "No Records Found";
        grdviewuserDetails.DataSource = dsMembers.Tables[0].DefaultView; // dsMembers..ExecuteReader();
       grdviewuserDetails.DataMember = "value";
        grdviewuserDetails.DataBind();      

        // return only cell
        //mydataset.Tables[0].Rows[i]["id"].ToString();
        lblName.Text = dsMembers.Tables[0].Rows[0][1].ToString();  
    }

 

Now your project is ready to use press F5 .

Parameter value is = Nancy, Andrew  or London

Conclusion 

This is database dependent web service. User can create any DB web service. This sample help you to solve any complex problem. Users can consume this one any dynamic web project. OK good bye.

Hope to hear from you soon.

Download Source Code