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:

 

SOLVED: An unexpected error occurred in the .NET Framework Data Provider for MySQL

If you are here I’m expecting that you encountered same problem as mine. I was transferring a DataSet file to ...
Read More

How to create custom 404 error handler in ASP.NET MVC

Custom 404 error handler - 404 Error refer to a not found resource/URL . Here’s how a default 404 Error ...
Read More

AngularJS CRUD Operation using ASP.NET MVC

I am a .NET Developer and I’m new in AngularJS. I started using AngularJS in about a week ago and ...
Read More
SignalR Chat App With ASP.NET

SignalR Chat Application with ASP.NET MVC

SignalR Chat App With ASP.NET - This article will show you an actual application using SignalR in ASP.NET MVC. The ...
Read More
AgularJS

Submit Forms using AgularJS with ASP.NET MVC

Ways on how to submit forms using AngularJS: I’m new to AngularJS and I was trying to create a CRUD application ...
Read More
Self Signed Certificate

How: Creating Self Signed Certificate

As defined in sslshopper.com. A self-signed certificate is a certificate that is signed by the person creating it rather than a trusted ...
Read More
AngularJS

AngularJS with Asp.Net MVC

What is Angular? AngularJS is a JavaScript framework that extends HTML attribute to help develop a dynamic Web application. AngularJS ...
Read More

Importing Excel with ASP.NET MVC

Importing excel, In my previous article, I demonstrated how to perform simple way of downloading data into excels. Now, this ...
Read More

Export Data to Excel with ASP.NET MVC

In creating a system there are times that we need to export data to excel, it could either be for ...
Read More

Easy Steps to Implement Partial View in Asp.Net MVC

One of the best ways to implement a dynamic table without loading the whole page is by using Jquery Ajax ...
Read More

Leave a Reply

Your email address will not be published. Required fields are marked *