ASP.Net webservice with SQL Server database connectivity

1. Create the Project in Visual Studio

go to Visual Studio>New Project(select .Net Framework 3.5) >ASP.net Web Service Application This will create a web service with a HelloWorld example like

    public string HelloWorld()
    {
        return "Hello World";
    }

2. Create a Database and Obtain the connection string

3. Define WebMethod

To create a new method that can be accessed by clients over the network,create functions under [WebMethod] tag.

4.Common structure for using database connection

add using statements like

using System.Data;
using System.Data.SqlClient;

Create an SqlConnection like

SqlConnection con = new SqlConnection(@"<your connection string>");

create your SqlCommand like

 SqlCommand cmd = new SqlCommand(@"<Your SQL Query>", con);

open the Connection by calling

con.Open();

Execute the query in a try-catch block like:

try
        {
            int i=cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception e)
        {
            con.Close();
            return "Failed";

        }

Remember ExecuteNonQuery() does not return a cursor it only returns the number of rows affected, for select operations where it requires a datareader,use an SqlDataReader like

SqlDataReader dr = cmd.ExecuteReader();

and use the reader like

using (dr)
            {
                while (dr.Read())
                {
                    result = dr[0].ToString();



                }
                dr.Close();
                con.Close();

            }