SQL Stored Procedures using ASP.NET MVC APPLICATION

OUTPUT VIEW:

Project 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:

@{

    ViewBag.Title = "Basic function";

}

<div class="row">

    <div class="body-content">

        <div class="panel panel-default">

            <div class="panel-heading"><h4>Sample Project</h4></div>

            <div class="panel-body">

                <table id="data" class="table table-striped table-hover"></table>

            </div>

            <div class="panel-footer">

            </div>

        </div>

    </div>

</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>

<script src="~/Scripts/function.js"></script>

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

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:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;


namespace Basic_Function.Service

{
    public class function

    {
        string Sqlconnection = "Data Source=PROG-RA;Initial Catalog=basicfunction;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

        public DataSet SelectListing()

        {
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(Sqlconnection))

            {

                SqlCommand cmd = new SqlCommand("SelectFromTable", conn);

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds);

            }

            return ds;
        }

    }

}

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:

using Basic_Function.Service;

using Newtonsoft.Json;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace Basic_Function.Controllers

{
    public class HomeController : Controller

    {
          Basic_Function.Service.function myservice = new function();


        public ActionResult Index()

        {

            return View();

        }

        public ActionResult SelectListing()

        {

        DataSet ds = new DataSet();

        DataTable dt = new DataTable();

        ds = myservice.SelectListing();

        dt = ds.Tables[0];

        String jsonResult = JsonConvert.SerializeObject(dt); 

        return Json(jsonResult, JsonRequestBehavior.AllowGet);

        }

    }

}

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:

var Controller_url = { SelectListing: '/Home/SelectListing' };
$(document).ready(function () {

    var select = $(function () {

        $.ajax({

            url: Controller_url.SelectListing,  //Address to controller

            type: 'GET',

            cache: false,

            success: function (list) {

                var parse_list = JSON.parse(list);

                $("#data").html(table(parse_list)); //Fill Table from Database

            }

        });

    });

});

function table(data) // Javascript function for Table template  

{

    var result = "";

    result += "      <thead>";

    result += "                      <tr>";

    result += "                          <th>itemNo</th>";

    result += "                          <th>FirstName</th>";

    result += "                          <th>LastName</th>";

    result += "                          <th>Address</th>";

    result += "                          <th>Contact Person</th>";

    result += "                          <th>Contact No.</th>";

    result += "                      </tr>";

    result += "                  </thead>";

    result += "                  <tbody>";

    for (var i = 0; i < data.length; i++) {

        result += "          <tr>";

        result += "              <td>" + (Number(i) + Number(1)) + "</td>";

        result += "              <td>" + data[i].FirstName + "</td>";

        result += "              <td>" + data[i].LastName + "</td>";

        result += "              <td>" + data[i].Address + "</td>";

        result += "              <td>" + data[i].Contact_Person + "</td>";

        result += "              <td>" + data[i].Contact_Number + "</td>";

        result += "          </tr>";

    }

    result += "";

    result += "          </tbody>";

    return result;

}

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.