Implement a Relational Database With ASP.NET Core

Learn Stored Procedures in SSMS

From the video above, you know that:

1. Stored procedures are executable objects that are stored within a database.

2. The two major advantages of using stored procedures are speed and security.

3. T-SQL is used to write stored procedures, and it incorporates all of SQL with some extra stuff to help you build robust queries and complex functions.

4. Stored procedures parameterize all user input.

5. Stored procedures are one of the ways (and the best way) to prevent SQL injection attacks.

What you don’t know is how to write them. I’m not going to bog you down with a reference manual for T-SQL. Most of what you’ll write in your stored procedures is standard SQL anyway, and the other parts are best learned through working with the code. SSMS helps out a lot in that area. There are two ways you can easily construct your stored procedures in SSMS:

1. Use the generic stored procedure code template SSMS provides and manually make your modifications.

2. Using the guided and intuitive interface that allows you to specify what you want to build, then let SSMS generates most of the code for you.

I’ll introduce you to the first of these, then move right on and show you how to take advantage of the second.

Create a Stored Procedure in SSMS

Stored procedures are found beneath the Programmability folder for your database in the SSMS Object Explorer. Open SSMS, connect to your server and locate the database in which you stored your recipes. Then expand the database and find the Programmability folder. The first subfolder under Programmability is Stored Procedures. This is where you will create all of yours. You may notice a subfolder within the Stored Procedures folder called System Stored Procedures. Leave this alone. Do not alter anything here. These are the stored procedures SQL Server uses internally for managing your database. You don’t want to mess those up.

To create a new stored procedure, right-click the Stored Procedures folder and select Stored Procedure or New Stored Procedure. The result is a new query window containing the generic stored procedure template.  

At this point, you could start editing the template to turn it into your custom query. Looking at the code, it’s easy to identify which items are placeholders. But there’s another way to do this: click the Query menu on the menu bar, then select Specify Values for Template Parameters.

You’ll see something interesting happen. First, a new window displays over the code. Second, all of the placeholder code in the template is highlighted.

Now you can start customizing this stored procedure. Let’s start at the top of the window and work our way down.

1. Enter your name for the Author parameter.

2. Enter today’s date for the Create Date parameter

3. For the Description parameter, enter All recipes whose bake time is less than a user-provided number of minutes.

4. For the Procedure Name parameter, enter BakeTimeLessThanX.

5. Enter @bakeTime for the @param1 parameter.

6. Select int for the Datatype_For_Param1 parameter.

7. Enter 0 for Default_Value_For_Param1 parameter.

8. Delete the values for the remaining parameters and click OK.

All of the placeholder code will now be changed to the parameter values you entered.

Your stored procedure is almost complete. All that’s left is to insert the query logic. For this query, that’s pretty simple. All you have to do is make a little change to the SELECT statement:

SELECT * FROM Recipes WHERE BakeTime < @bakeTime

Now you need to test the syntax to make sure everything checks out. To do this, from the Query menu, click Parse. If you receive an error message, you need to correct your syntax.

When you are satisfied that it's correct, create the procedure within the database. To do this, from the Query menu, click Execute. This action creates the procedure as an object in the database. To verify that it's been created, right-click the Stored Procedures folder in Object Explorer, then click Refresh. You should now see your stored procedure in the Stored Procedures folder. 

You can close the code editor window for your procedure at this point. It is not necessary to save the SQL file. The procedure now exists as an object, so storing the code is not necessary.

Testing a Stored Procedure

It’s important to test your stored procedures within the database to verify accuracy before you begin calling them from your application code.

In Object Explorer, right-click your stored procedure name, then select Execute Stored Procedure. This brings up a window for parameter values; for example, I’ve entered 60 for the @bakeTime parameter. 

After clicking OK, the procedure runs and returns its results, which are displayed for you in the Results pane below the SQL code that is generated by the procedure execution. 

Coding Exercise: Write Your Stored Procedures

The queries written in Part 1 were all hard-coded. For your stored procedures, all previously specified values will become variables to hold user-provided data. And now that you’ve created your first stored procedure, you should have a pretty good idea of how to write them.

Take a few minutes now and create new stored procedures in your database using SSMS to perform the following queries:

1. All recipes whose name includes a user-provided string of text.

2. All recipes whose bake temperature is greater than or equal to a user-provided number of degrees Fahrenheit.

3. All recipes whose ethnic origin is a user-specified value.

4. All recipes for either breakfast, lunch, dinner, or dessert (user-provided value).

When you’re finished, we’ll move on to executing stored procedures within your application.

Execute Stored Procedures in C#

In Part 1, Chapter 4 of this course, you completed some coding exercises in which you wrote code in your HomeController that would execute SQL and LINQ queries on your database. Review the code you wrote for those exercises because you’ll be doing something similar here.

Let’s talk about how to execute the stored procedures you just wrote from within your application, just like those queries from Part 1.

There are three ways you can execute a stored procedure in C#:

1. Wrap the supplied value in a parameter and insert the generated parameter name where the  {0}  placeholder is specified: 

   var bakeTime = "30";
   var recipes = db.Recipes
       .FromSql("EXECUTE dbo.BakeTimeLessThanX {0}", bakeTime)
       .ToList();

2. Use string interpolation syntax, supported in EF Core 2.0 and above, in which the variable holding the parameter value is inserted as the placeholder:  

   var bakeTime = "30";
   var recipes = db.Recipes
       .FromSql($"EXECUTE dbo.BakeTimeLessThanX {bakeTime}")
       .ToList();

3. Construct a  DbParameter  object for the query and supply it as a parameter value. This option allows you to use named parameters as part of the SQL query string:  

   var bakeTime = new SqlParameter("bakeTime", 30);
   var recipes = db.Recipes
       .FromSql("EXECUTE dbo.BakeTimeLessThanX @bakeTime”, bakeTime)
       .ToList();

You can use whichever of these acceptable methods for executing your stored procedure. Note that all three methods return the same thing - a list of Recipe objects matching the provided criteria.

Normally you would build a web page to allow the user to enter the required values, but we’re not building pages in this course. So let’s talk about how you can execute your C# code and verify that you are calling your stored procedure correctly.

Test Your Code

In Part 1, you placed all of those SQL and LINQ queries inside methods in your HomeController. Now you’ll do the same for your stored procedures, but using your new project, MyRecipes2.

To begin, make sure the following using statements are present at the top of the file:

using Microsoft.EntityFrameworkCore;
using MyRecipes.Data;

Next, inject your new  DbContext  into the controller: 

private readonly MyRecipes2Context _context;
 
public HomeController(MyRecipes2Context context)
{
    _context = context;
}

Now start creating methods for your stored procedures. Start with a new method called  GetRecipesByBakeTime. Since you aren’t going to build a user interface for this, you won’t have any views to return. So instead of making these methods return  IActionResult, have them return a  JsonResult  just like we did for the queries in Part 1.

public JsonResult GetRecipesByBakeTime(int bakeTime)
{
    var recipes = _context.Recipes
        .FromSql("EXECUTE dbo.BakeTimeLessThanX {0}", bakeTime)
        .ToList();

    return Json(recipes);
}

Now compile your code and correct any errors. If it compiles cleanly, it’s time to test your new method to see if it works. To execute your method, all you need to do is enter the URL in the address bar of your browser:

https://localhost:your-localhost-port/Home/GetRecipesByBakeTime/30

This will execute the method you just wrote, which calls your stored procedure and retrieves all of the recipes whose bake time is less than the supplied parameter. In this example, we used 30, but you may use any value to test your procedure. Your browser will display your result as a JSON object. If no recipes match the criteria, an empty JSON object (empty square brackets) will be displayed.

Coding Exercise: Add and Test Remaining Methods

Add methods to your SQLController for the rest of your stored procedures, then test them for accuracy just as you did in the previous example.

Let's Recap!

You completed all of the practice work for this course, and in doing so, you learned:

• How to use SSMS to quickly build and save stored procedures in your database (remember, stored procedures are the most secure and fastest way to execute SQL queries).

• How to test stored procedures within the SSMS IDE. 

• How to inject a database context object into an MVC controller.

• Three different ways to call and execute a stored procedure within C# code using your database context object.

Learning stored procedures is a great way to close out this course. Let’s take a moment to review the rest of the things you learned here, and then you can complete the course with your final quiz.