Create webservice in asp.net and mysql database

Now start writing the code to generate json data. By default asp.net adds the following block which is pretty good to kick start.

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

    public user_favourites () {

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

}

In the above code the following namespace needs to be changed when deploying in the remote server. In this case I have used http://saradesh.com as a unique namespace.

[WebService(Namespace = "http://tempuri.org/")]

Now I would like apply a bit of security to prevent unauthorised access and prevent from any bots using this service. This can be done in many ways, you can ask user to authenticate first before sends the request to the API.

In this example I am implementing a basic token authentication with random number and letters to ask users to provide the token before they can proceed. Pretty good hay.

Let’s look at the full code snippet below.

public string tkn = "cfrerwe316cdzc3w7f";
    public string uid;
    [WebMethod(Description = "Gets user favourites from CMS.")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string GetUserFavourites(string token, string uid)
    {

        if (token == tkn)
        {
            string connStr = String.Format("server={0};user id={1}; password={2}; database=my_database; pooling=false", "106.6.21.133","database_user", "my_password");
            MySql.Data.MySqlClient.MySqlConnection mycon = new MySqlConnection(connStr);
                
                string str = "select content_id, type, n.title, fc.uid from flag_content fc  inner join node n on fc.content_id=n.nid where fc.uid=@uid;";
            
            MySqlCommand cmd = new MySqlCommand(str, mycon);
            cmd.Parameters.Add("uid", uid);

           
            try 
    
                {
                    mycon.Open();
                    
                    DataSet objDataSet = new DataSet();
                    MySqlDataAdapter objDataAdapter = new MySqlDataAdapter(cmd);
                    objDataAdapter.Fill(objDataSet, "fav");


                    //using json.NET library
                    string json = JsonConvert.SerializeObject(objDataSet, Formatting.Indented);
                    mycon.Close();
                  return json;

                }
                catch (MySqlException ex)
                {
                    throw (ex);
                }


        }
        else
        {
            return "invalid token provided";
        }

Let’s explain the above code, I have declared static string and set the initial value for the token. I have also declared another string for user id to pass before we can  retrieve the data from MySql database.

The codes are very self explanatory, I had to use couple of join tables to retrieve data from Drupal CMS. Now save the file and run the page.

It displays the number of available operations for the webservice. Now click on the operation “GetUsersFavourites” link to access. It will prompt for input the token and userid to retrieve information. User would not be able to execute the action without providing the correct information. It will throw an error when the token number does not match with the assigned static token value.

Please note webservice test option is only available when you run the service in local server but it will not be available from the remote server.

I have entered the correct parameters and clicked on invoke, it produces the jSon data in a separate window. You can use jQuery or any other tools to consume the API data

The service page contains couple of example to implement Soap services which might help you to get start calling the service.

Implement the webservice in remote machine

Before you implement the webservice in the remote server, you have to enable HTTP GET/POST request in the web.config file. By default they are disabled for security reasons. Please add the following code inside the system.web section in the web.config file.

  <system.web>
<webServices>
      <protocols>
        <add name="HttpGet"/>
        <add name="HttpPost"/>
      </protocols>
    </webServices>

  </system.web>

Once done, you can call the webservice API from any application regardless of what language used in the app.

Here is an example to request data through webservices  API using jQuery.

<script type="text/javascript">

    $(function () {
        $.ajax({
            type: 'POST',
            url: 'user_favourites.asmx/GetUserFavourites',
            data: "{token: 'cfrerwe316cdzc3w7f', uid:'191'}",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            error: function (jqXHR, textStatus, errorThrown) {
                alert(jqXHR.responseText);
            },
            success: function (data) {
                $('#msg').html(JSON.stringify(data));

            }
        });

    });

</script>

Here is the response from the API –

Happy programming. Let me know if you have any issues.