Wednesday, January 4, 2017

Simple SQL Server ASP.NET Web Service

I am not a developer. I am a database dude. So don't take this article as your source for the best way to develop a web service. This is just a simple example of how to call a stored procedure from an ASP .NET web service.
  • Start up Visual Studio (I am using 2015)
  • Click New...Web Site
  • Up top you should see a version drop down for .NET Framework. I selected .NET 3.5
  • On the left side under templates choose C# 
  • You should see an option for an ASP .NET Web Service, Select that
  • Give the Web Service a name by the Browse Button and Click OK
  • You should see some sample code and a Hello World Method.
  • Start a new Method by copying this Method.
  • Give the Method a new name. Mine is called ExecuteSweetStoredProc
  • Now we are going to add the SQL Server stuff
  • You will start seeing some sweet red squiggly lines. That means you got problems jack. In my example above we are missing some Reference (add ins to allow  us to connect to SQL and stuff, and also some errors about not all code paths return values. That is just letting you know that yo bro, you run this code and it bombs or something wonky happens its not going to end well.
  • Let's add some references. Up a at the top you should notice some using statements.
  • Add these 3
    • using System.Data;
    • using System.Data.SqlClient;
    • using System.ServiceModel;
  • Now we will add a Try Catch block. This will try a something and then if that something is unsuccessful it will catch the error and display it.
  • So we set the SqlCommand to our stored proc and now we are executing it using the ExecuteNonQuery() function.  We return the success code if its all good
  • If we have a problem then we display the results
  • Click the Build Menu and click Build Solution
  • Check your output Screen
  • If you have errors then start googling, 9 times out of 10 you'll find your answer on stack exchange.
  • Now Click the Play Button in the top menu of Visual Studio
  • Should see something like this
  • Click Hello World Link and then Click Invoke. Should say Hello World in some sweet XML
  • Go Back and Click your Other method (whatever you named it)
  • If all good you should see this. (if not start googling or leave a comment below and I will try to help)
  • So what did it do...well what does your stored procedure do? You will notice on my EXECUTE statement I execute dbo.webServiceBackup. I am running a FULL database backup on one of my databases. Your stored proc can do whatever you want. You can also display results or whatever you want to do.

Full c# Code for Easy Pasting into Visual Studio

I will try to get some more examples of simple web services up on the blog here including reading through results from a stored proc, returning JSON formatted data for easy consumption, etc.


Post a Comment