Consuming SQL Stored Procedures (ASP.NET MVC Web Application)

For beginners: – SQL Stored Procedures

The objective of this project to get data from a Database Table using a stored procedure and display in a HTML Table .

Note: I’m using ASP.NET MVC Applciation

Summary of the classes I used in creating this example

  • index.cshtml » In MVC framework index is the View. This is responsible for the User Interface in the front end of your website. In this example it is the table design.
  • Controller.cs » Class that is responsible for processing of data that pass to the View.
  • function.js » Most common question asks for beginners in using MVC framework is how to pass data from a controller to a view. This is one of the answers instead of using the normal way of doing it I’m using JavaScript in transmitting data from a controller. This process populates your data in your view without reloading the whole page.
  • function.cs » I created a separate class for calling Store Procedure to modularized our function for easy debugging.

Now that you have known all of the function used for this example. I will go down the line and proceed with copying of code for your personal testing and better understanding.

OUTPUT VIEW:

Project Output

Project File structure:

File Structure

Index.cshtml

This file is responsible for your User Interface design. In ASP.NET MVC framework in every controller there is always a specific views associated with it. In this case we are using HomeController so the folder from Views->Home->index.cshtml is our main view. Navigate to this file and copy and paste code below.

User Interface

 Note:

  • <script src=”~/Scripts/jquery-1.10.2.min.js”></script> -> include JQuery Script from Scripts folder
  • <script src=”~/Scripts/function.js”></script> -> include function.js Script

Code:

 

function.cs

To modularize project I created a separate class use to connect to our store procedure. This is where all code associated to the database are found. I also created a folder called Service where I put this class.

To create a folder right click on your project name and choose Add to show all available option, then selects New Folder. Name your folder as Service.

Create new class name function.cs by right clicking on the folder you have just created. Then Select Add and choose class.

Add Class

Name your class as function.cs

Name your Class

Open the file you have just created and copy the code below.

Service Folder

To manually view Database Connection string visit this blog content How to view Connection String in Visual Studio

Code:

Note: 

  • SelectFromTable -> Name of Stored procedure we created from the previous tutorial. To view visit this blog Creating Stored Procedure.

 

HomeController.cs

This is the default Controller generated during the creation of project. We will use this as our main controller for this project. Navigate to this file and copy and paste code shown below.

Controller folder

Note:

  • Service.function myservice = new function(); -> use to access our method from service-> function.cs class.
  • Basic_Function -> Project Name
  • Service -> Folder name where I created my function.cs
  • function -> class Name

 

Code:

function.js

This script is used to update data to our html table in view. To create script file right click on the Scripts folder, Select Add then click on JavaScript File.

Add JavaScript File

Name your script as function.js

Script Name

Navigate to your file and copy and paste code below.

Script Folder

Note:  “/Home/SelectListing”

  • Home-> Controller name
  • SelectListing-> Method from HomeController

Code:

To run your project simply hit f5 button from your keyboard to run in debugging mode or hit Ctrl + f5 to run without debugging mode.


Related Post:

 

[pt_view id=”2384ae03it”]