Simple SQL Server ASP.NET Web Service Part 2: Deploying to IIS

In Part 1 we walked through creating a Simple SQL Server ASP .NET Web Service. Now let's work on deploying it to IIS.

As a another reminder, I am not a .NET developer, I am a database guy. Talk to some of your developer friends if you want some advanced help on the right way to do this stuff. I am just showing a very simple web service to call a Stored Procedure.

Assuming you have your web service project open in Visual studio...

• Click on the Build Menu and click Build Solution.
• Assuming you have no errors then click the Build Menu again and this time click Publish Web App
• You should see something like this (reminder I am using Visual Studio 2015)
  
• Click Custom and name it Local
  
• Enter your server name and the name of the web site you want to deploy to. You can give your service a name here. Small Side Conversation here: Probably a good idea to name the service whatever you called it at creation time. I of course learned this the hard way when I went to deploy it and gave it a new name in flight. So now I have a project called Website in Visual Studio and a Service named totally different on my IIS server. This is all well and go if I have one service but as you grow it will be difficult to keep track of what is what.
  
• Make sure you validate connection and its green and click Next
• Click Next on the Setting Screen
• Click Publish on the Preview Screen.
• If all goes well your app should be deployed...if not google it. Check your output screen for details
  
• If the publish was successful, Visual Studio will try to open a browse with your web service. I received a HTTP 500 error during this preview.
  
• After some googling I found the work around. Right click on your web site in solution explorer and click Property Pages
  
• Click the build option on the left and select .NET 4.5 Framework as Target Framework. Click Yes to the warning message.
  
• Build your Web Site again and then Publish the Web App
• This time I received a 404.13 Forbidden document. 
• I added Service.asmx to the end of the URL and it worked. 
• So I then added Service.asmx under Default Document for the web site. Just click on your web site in IIS and then find Default Document. Click Add and then add Service.asmx
  
• Reload your web service and it should work now.
• Now fire up your web service Using the invoke button we did earlier.
• Ughhh
  
• If you get this the fix is simple. You want to setup a local user on your server that has access to EXECUTE a stored proc on your SQL Server. 
• You then want to create a new Application Pool in IIS. This will be used to call web services that need access to SQL.
• Click Application Pools and on the Right side of the screen you should see a link for Add Application Pool
  
• Give the Pool a name and click OK
• Now right click on the new pool and click Advanced Settings
• JUST FOR THIS EXAMPLE I created an Application Pool and then set the identity to my local administrator. This is a bad bad bad idea if you plan to go showtime with this web service. Click the Ellipses and use the account you created that has access to the database.
  
• You are doing this because in your C# web service on your database Connection we are using Integrated Security, so the App Pool Identity must have the ability to connect and execute the stored procedure.
• Now Highlight your service web site and on the Right side you should see a basic settings link. Click that Link
• Click the Select button by the app pool and choose the app pool you created
  
• And hopefully this now works. Whew...crack open a celebratory Mt. Dew.
  
  
  

UPDATE:

In order for this web service to work outside the friendly confides of your internal network you need to add a section to your web.config. In the <system.web> section of your config add the following Web Services section:

Again be careful here when you expose a web service to the world.

Holler if you have questions, remember Google and Stack Exchange are your friends.

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.