CODEWITHSUNDEEP
c#.netweb-services
Jack W
Jack W

Reputation: 1

C# - What is the best way to create a web service that will run a stored procedure and then return the data fetched in a json format?

The website/application I am working on is built using web forms and this is my first time trying to create a web service - I did create an ASMX web service which looks similar to the code at the end of this question.

Found out now that ASMX web service is an old and outdated technology and currently I am in a place where I don't want to use what I created and at the same time don't really know another way to create a different type of web service.

What I want to know is

  1. How can this be achieved ? WCF or Web API ?

  2. Guessing both - so which would be the right way to do this?

  3. How can I do this? Any suggestion on resources?

More info on the data I am trying to get and send - one of the vendors we use has their own database which gets updated nightly through exported reports sent to them... we want to create a web service (with basic authentication) to send the same data in a json format so they can use that to updates their database more frequently.

Here is the sample ASMX code I created

[WebService(Namespace = "http://..../")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] 
public class VendorUpdates : System.Web.Services.WebService
{
public class Users
{
    public int user_no { get; set; }
    public string user_name { get; set; }
}

[WebMethod]
public void GetUsers()
{
    List<Users> listUsers = new List<Users>();

    string connectionString = ConfigurationManager.ConnectionStrings["testdatabase"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "sp_get_users";
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Users user = new Users();
            user.user_no = Convert.ToInt32(reader["user_no"]);
            user.user_name = reader["user_name"].ToString();
            listUsers.Add(user);
        }
    }

    JavaScriptSerializer js = new JavaScriptSerializer();
    Context.Response.Write(js.Serialize(listUsers));
}

Upvotes: 0

Views: 104

Answers (2)

Karthick Trichy Chandrasekaran
Karthick Trichy Chandrasekaran

Reputation: 454

Please use Web API. It follows the MVC pattern (there is no view here). Controllers has the methods which the UI would call and Models has the data classes and members which you might use. Web API is pretty simple to create and the values can be returned in JSON format. You can just move the same code which you have in Web Method to the controller. If you do not want to have all the code in a single class and like to follow some pattern then you can do that.

Upvotes: 0

rywem
rywem

Reputation: 355

You have a few questions in here. I would look into using Web API. Web API is probably your path of least resistance. Check out this youtube tutorial series by Kudvenkat, it may offer some tips:

ASP.NET Web API tutorial for beginners: https://www.youtube.com/playlist?list=PL6n9fhu94yhW7yoUOGNOfHurUE6bpOO2b

He also has a series on entity framework, which may offer insight into your database functionality.

Good luck

Upvotes: 1

Related Questions