CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

Using a Stored Procedure to Retrieve Data from SQL Server



The Goals for this Example


This time we modify the previous example instead of creating a new web app. The main goal is to learn how to use a stored procedure to retrieve the data from our product table. Stored procedures are more secure than plain SQL, particularly when user data is used as parameters to the stored procedure or as part of the SQL command. Although we have no parameters in this example, the knowledge of how to create and use a stored procedure is valuable. The reason that a stored procedure is more secure is that it passes its parameters as typed items, thus limiting the chances that a hacker could successfully attack your application by submitting malicious data to be used with the database query. In addition, the stored procedure is precompiled code, so that nothing the user submits should be able to be executed. With plain SQL it is all too common that SQL injection attacks work, wherein malicious data that the user submits to be part of the query gets executed, so that the query does more than it should be allowed to do.

Changing the Previous Example



Creating a Stored Procedure

  • Open your usual solution in Visual Studio, the solution that contains the web site that you have been working on in these examples.
  • In Solution Explorer, open the previous example, Display.aspx, and show it in Design View.
  • In Server Explorer, find your data connection, such as carlsondConnectionString. Do not use the webapp connection string. Expand the node for your connection string. Right click on Stored Procedures and select Add New Stored Procedure.
  • You will be given an outline, such as the following, of the SQL to create a new stored procedure.

CREATE PROCEDURE [dbo].[Procedure]
   @param1 int = 0,
   @param2 int
AS
   SELECT @param1, @param2
RETURN 0

  • Notice that this code is intended to create a new stored procedure, which will be named Procedure by default. Change that name to GetProductInfo, just by editing the code itself.
  • Delete the 2 lines for parameters param1 and param2 as we do not need any parameters to this stored procedure. However, do note that they begin with the @ symbol, as you may need to use parameters to stored procedures later.
  • The main thing to change is the SELECT statement. This is the SQL that will be placed in the stored procedure. Wipe out the 2 parameters and use this SELECT statement:
    SELECT ProductName, Price, NumInStock From ProductTable Order By NumInStock Desc
  • This SQL command picks out the 3 fields named here from ProductTable, displaying these lines of 3 fields in descending order according to the NumInStock field.
  • The code to create your stored procedure should now look like this:

CREATE PROCEDURE [dbo].[GetProductInfo]
AS
   SELECT ProductName, Price, NumInStock From ProductTable Order By NumInStock Desc
RETURN 0

  • Click the Update button in the upper left corner of the code to create your stored procedure.
  • You will then either get an error message or a window where you click on a button labeled Update Database. If the former is the case, then you either have an error in your code or there is a permissions problem on the database end. Consult your instructor if need be. If you do not get an error, do click on the Update Database button.
  • You then have a stored procedure named GetProductInfo that should show up in Server Explorer, under your database's node and under the Stored Procedures node within that. If it does not show up, click the circular Refresh button near the top of Server Explorer.
  • You can test your stored procedure (and should do so) as follows: Still in Server Explorer under your connection string (such as carlsondConnectionString), right click on your stored procedure (under the Stored Procedures node). Click on execute. Hopefully all works OK, and you get some code displayed that shows how your stored procedure is getting called, with the output of your stored procedure shown below that. You should see 3 lines of data pulled from your ProductTable.
  • If your stored procedure does not execute, read the error message carefully for clues. Sometimes it happens that a needed permission has not been turned on for the login associated with this connection string. For example, you might get an error about being unable to view a certain definition. If you get this, get your system administrator to grant the "View any definition" permission to your login (such as carlsond).
  • But wait! When your web app gets run by users who access it from a separate web server, your app has to access the SQL Server database using a different connection string, the one we earlier called carlsondwebappConnectionString or similar. You should test that the stored procedure works OK with that connection.
  • So, find your webapp connection in Server Explorer and then your stored procedure below it. Execute it as you did above for the other connection. Guess what? It probably does not work. The likely reason is that this login does not have sufficient permissions.
  • You should be able to fix this permissions problem yourself, but you will have to use SQL Server Managment Studio. Start this management studio and log in with your login (for example, carlsond) using Windows authentication.
  • Once logged in, expand the node for your particular database under databases. Then expand the Programmability node under that.
  • Expand the Stored Procedures node under that and right click on the stored procedure that you are trying to get to work.
  • Select Properties and then click on Permissions.
  • Click on Search and in the "Enter object names to select" box, type in webapp, carlsondwebapp, or whatever you used for the webapp connection's login. (Alternatively, you can click on the Browse button and look for the correct item in the list that it displays. Check the desired one and then OK.)
  • In the permissions box at the bottom of the window, put a checkmark in the row that says Execute and the column labeled Grant. Do the same for the row that says view any definition. Then click OK.
  • Next, go to your overall database (not stored procedures or anything inside your database). Right click. Select Properties, and then Permissions. Make sure that carlsondwebapp (or whatever your webapp connection string is named) is selected and then grant View any definition. Click OK.
  • Go back to Server Explorer in Visual Studio and try to execute this stored procedure as above, being sure that you are under the section for your webapp connection string. The execution should now work.

Reconfiguring the Data Source

  • In Design View for your form, find the smart tag for either the SqlDataSource or your GridView.
  • Then click on Configure Data Source.
  • In the Choose Your Data Connection window, the correct connection string should already be shown (webappConnectionString or whatever you called it). Just click Next.
  • At the Configure the Select Statement window, select "Specify a custom SQL statement or stored procedure" and click Next.
  • In the Define Custom Statements or Stored Procedures window, select "SQL statement" and then click on Query Builder just to try it out. It is fairly intuitive. You select one or more tables to use, then check off what fields to include, etc. However, cancel out of Query Builder as this is not really what we want to use.
  • Instead, select "Stored procedure" in the Define Custom Statements or Stored Procedures window. Then in the pulldown select the desired stored procedure and click Next.
  • In the next window, click on the Test Query button. If it does not work, you will have to figure out what is wrong and fix it. However, due to our earlier testing of the stored procedure, it should work (though we are running the stored procedure using your login, not the webapp one, which is the one that will be used when your app is accessed when it is put into production.
  • Click the Finish button.

Building and Running Your App

  • Save all of your files in Visual Studio.
  • Then use Build, Build Page.
  • If the build succeeded, the Output window will give you a message to that effect.
  • If not, fix any problems.
  • Use the debugging methods suggested in the last example, if need be.
  • Then run your app by going to the correct URL with a browser. For example, the URL I am using is: http://cis2.stvincent.edu/carlsond/Wrox/Display.aspx
  • Check that, when you click the Display Data button in your web app, the correct data is shown. In particular, you should see 3 rows of data, in reverse order by NumInStock.

Back to the main page for ASP.NET 4.5 Web Apps



Author: Br. David Carlson
Last updated: September 22, 2017
Disclaimer