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”]

ASP.NET MVC File Architecture

Solution Explorer

ASP.NET Web application is created with the MVC template and is auto generated by the Development Environment of Visual studio. Unlike any other framework MVC has very strict guidelines about where to place different elements like a Model should be in the Model Folder, the controller must be in the controller folder, View must be in the View folder and there’s more to it than that. If you put it a wrong place, your application might not work as you expected.

MVC is also created for the purpose of having a clean and organized coding technique. For further knowledge on how this folder is being used in this framework, let’s go down the line and make an overview of each element and folder in our solution explorer.

(You can also see blog post How to start with MVC ASP.NET Web Application)

MVC Architecture C#

The image below is the folders and files generated for a newly created Visual Studio ASP.NET MVC Web Application project. Let’s see significance in each file in the solution explorer.

MVC Architecture C#

Solution Explorer

App_Data

This folder contains application data like local Database used by the application like .mdf file. This folder doesn’t have special meaning in the MVC framework.

App_Start

This contains code that helps configure MVC application. Generally, we don’t modify this part of the code unless you are already familiar with it and wanted to configure it manually. Like RouteConfig.cs which is responsible for the routing of your application web address. BundleConfig.cs which responsible for minification of your JavaScript and CSS files.

MVC Architecture C#

App_start

Content

Content folder contains style sheet for your sites like Site.css.

MVC Architecture C#

Content

Controller

This is the heart of MVC where you can find all the classes that are responsible for the processing of data. The controller is always tied to the View folder.

MVC Architecture C#

Controller

For example, if we have HomeController in the controller folder, then we also have a Home folder in the views folder and if we have AccountController in the controller folder then we also have Account Folder in the views folder. Home Controller must be named with a Controller at the end in order for it to work.

MVC Architecture C#

View

View

This folder contains User interface of your application. See image below.

Account, Home and Manage folder are the View of your Controller while Shared folder contains a template of your Web Application. _layout.cshtml, it’s like a master page and responsible of how all your pages will look like. _LoginPartial.cshtml contains code for partial menu .Error.cshtml is responsible for displaying error. View Start.cshtml it calls the default layout of the page which is _layout.cshtml. Web.config is an app configuration file that contains assemblies needed for the project. 

MVC Architecture C#

Views

Fonts

Contains customize font you want to use in your project.

MVC Architecture C#

Font

Models

This folder contains classes that are responsible for the identification of data. This is where all application requirements are registered.

MVC Architecture C#

Script

Contains all JavaScript file of your application.

MVC Architecture C#

Script Folder

Favicon.ico -> default site logo icon

Packages.config -> this comes from nugget packages.

Web.config -> Configuration settings of your application

MVC Architecture C#

Default files

You can also refer to this link from DotNetmob.com for additional info on ASP.NET MVC Architecture.

Thank you for reading. Hope this helps. Enjoy Coding!!!

 


Related Post:

 

[pt_view id=”2384ae03it”]

How to start with ASP.NET MVC Web Application – Development

Web Application

This blog demonstrates how to start with ASP.NET Web Application. For those who are struggling with how to start a simple ASP.NET Project, let me guide you throughout the process.

Before we start we need a Development Environment to start with the project.

You can download Visual Studio 2013 from this  https://www.visualstudio.com/downloads/

If you have already installed Visual Studio on your machine we can proceed with the steps to start a project.

Begin with opening visual studio Development Environment. There are two options in starting a new project you can start from the file menu located at the top navigation bar as shown below.

Web Application

Visual Studio Developement Environtment

Or you can select New Project link from the start page of Visual Studio development environment refer to the image shown below.

Web Application

Start Page link

After that expand Visual C# node on the left pane and navigate to Web option. Then search for ASP.NET Web Application on the middle pane. Name it as MVC-SimpleApp. You can also name your apps to your preference. If you want to use different directory of your project use the browse button right below your project name. If your done click OK to proceed to the next steps. Use the image below for your guide.

Web Application

Project Options

From the New ASP.NET project dialog select template for your project Choose MVC as shown below.

Web Application

Project Template

You can also change application authentication. It is set to Individual User Accounts in default but here we‘re going to use No Authentication.  Image below is set to its default value change it to No Authentication.

Web Application

Authentication

The application will go idle for some time. Wait for Visual Studio to load necessary files needed for your project.

To run the project press f5 to run in debugging mode or Ctrl + f5 to run without debugging mode

Web Application

Website Preview

ASP.NET MVC Application includes bootstrap CSS and JavaScript by default so your site already responsive. Below is the image of the website view in small screen.

Web Application

Mobile View

From this moment you can start your first ASP.NET MVC Project.

Thank you for reading. Enjoy Coding!!

 


Related Post:

[pt_view id=”2384ae03it”]

Basic ASP.NET MVC Web Application Implementation

What is MVC?

  • Model View Controller (MVC) is a framework, a Programming architecture where you designed your code in 3 different modules. Model, it is where all your logical properties or data are declared. View, it is where you designed or creates your Client user interface and lastly Controller, it is where you process data and connect model to view.

MVC Diagram:

To illustrate how MVC work in ASP.NET lets create a sample project.

To be guided with the sample Project. Below are the names I used for the function.

Note:  I’m using visual studio 2013 for this tutorial

  • MVCBasicModel.cs => Name of the Model Class(Located under Models Folder)
  • HomeController.cs => Default controller generated by Visual studio after Creation of new     MVC Application Project(Located under Controller folder)
  • Index.cshtml => Default view for HomeController(Located under Views/Home Folder)

Goal: 

  • Generate data from database
  • Save in a list with model binding
  • Pass to view and display in a table

Output Preview:

To begin let’s create a new Project in Visual studio. You can also refer to my blog how to start with ASP.NET MVC Web application for detailed instructions on how to start a project.

  1. First open your Visual Studio. Navigate to File/New and then Choose Project right on top of your Visual Studio IDE.
  • IDE => Integrated Development environment
  1. New Project options will popup. Select Visual C# from the left side panel and Search for ASP.NET Web Application from the right side panel, then Click OK.
  2. After that select a template, which is MVC then click ok to proceed
  3. Congratulation!! You have successfully created your MVC project.

Now let’s create a Database and table. There are two ways in creating a database; you can use a SQL management studio or via Visual Studio. Here I’m going to use Visual Studio.

Note:  You need to have SQL Server installed on your machine to proceed with these steps

  1. To open SQL Server Explorer in Visual Studio Navigate to View from the menu bar and then click on SQL Server Object Explorer. SQL Server Object Explorer will show on left side of your visual studio IDE.
  2. Navigate to your local SQL Server. Right click on the Database folder and then click Add New Database. You can name it whatever you like, for me I name it as sad_db.
  3. After that navigate to your local SQL server again and under that you can see you’re newly created Database. Open your newly created database and go to Table Right Click on it to see add new Table option.
  4. Then add columns to your table.
  • Id => int
  • Employee_Name => varchar(100)
  • EmpCode => varchar(100)
  • Position => varchar(100)
  • Location => varchar(100)
  1. To manually insert sample data. Right click on your table again and choose view data.
  • Id => 1
  • Employee_Name => John
  • EmpCode => j-123
  • Position => Cashier
  • Location => Place

And now let’s proceed with the coding part. Let’s add our model class. From your solution explorer located on the right side panel of your Visual Studio IDE, navigate to the Model folder, then right click on it and select add option, then select the class. Name it as MVCBasicModel. This model will be used to bind with a list in our controller.

Declare these properties from your MVCBasicModel class:

In our HomeController.cs (Located under Controller folder from the solution explorer) create a separate function/method that will fetch data from our database and return data as a list. Name it as GetData ();

Note: To view Database Connection String navigate to SQL Server Object Explorer, right click on your database and then click properties. Under properties go down to Connection string then copy and replace SQL Connection string from the code below.

Passing data to the View

In your HomeController Index call our function GetData() to get the data we fetch from our database and return it  into our view, which is index.cshtml located under view/Home Folder

Index.cshtml

  • @model IEnumerable<MVCBasic.Models.MVCBasicModel> => to gain access the list we pass from our HomeController
  • Use foreach loop to manually fill our data to our table

and we’re done.. Thank you for reading. Happy coding!!

 

Code Summary:

MVCBasicModel.cs

 HomeController.cs

Index.cshtml


Related Post:

 

[pt_view id=”2384ae03it”]

1 2