Sample MVC database access example source code

step 1-> Create a databse as follows








Step 2-> Write stored Procedure for Insertion

USE [Guestbook]
GO
/****** Object:  StoredProcedure [dbo].[spInsert]    Script Date: 07/14/2014 14:29:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsert](@Name nvarchar(50),@Message nvarchar(50),@DateAdded datetime)

AS
BEGIN
    Insert into GuestBookEntry(Name,Message,DateAdded) values(@Name,@Message,@DateAdded)
END





step-> Create Model as



using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Globalization;
using System.Web.Mvc;
using System.Data.Sql;
using System.Configuration;
using System.Web.Security;
using System.Data.SqlClient;
using System.Data;
namespace GuestBook.Models
{
    //public class GuestBookContext : DbContext
    //{
    //    public GuestBookContext()
    //        : base("dbConnection")
    //    {
    //    }

    //    public DbSet<GuestBookEntry> Entries { get; set; }
    //}
    //[Table("GuestBookEntry")]
    public class GuestBookEntry
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Message { get; set; }
        public DateTime DateAdded { get; set; }
    }
    public class dataAccesLayer
    {
        public string insertData(GuestBookEntry gbe)
        {
            SqlConnection con = null;

            string result = "";

            try
            {

                con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString);

                SqlCommand cmd = new SqlCommand("spInsert", con);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Name", gbe.Name);

                cmd.Parameters.AddWithValue("@Message", gbe.Message);

                cmd.Parameters.AddWithValue("@DateAdded",gbe.DateAdded);
                       

                con.Open();

                result = cmd.ExecuteScalar().ToString();

                return result;

            }

            catch
            {

                return result = "";

            }

            finally
            {

                con.Close();

            }
        }

    }
}


step 4->Create a Controller 


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GuestBook.Models;

namespace GuestBook.Controllers
{
    public class GuestBookController : Controller
    {
        //
        // GET: /GuestBook/
     
           
        public ActionResult Ceate()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Ceate(GuestBookEntry entry)
        {
            entry.DateAdded = DateTime.Now;
           
            dataAccesLayer dal = new dataAccesLayer();
            dal.insertData(entry);
            return Content("successfully added");
        }

    }
}



step 5-> Create a View as 


@model GuestBook.Models.GuestBookEntry
@{
   
    ViewBag.Title = "Add New entry";
}

<h2>AddNew Entry</h2>
@using (Html.BeginForm())

{

    <table width="100%">    

        <tr>

            <td>

                @Html.LabelFor(a => a.Name)

            </td>

        </tr>

        <tr>

            <td>

                @Html.TextBoxFor(a => a.Name)

              

            </td>

        </tr>

        <tr>

            <td>

                @Html.LabelFor(a => a.Message)

            </td>

        </tr>

        <tr>

            <td>

                @Html.TextBoxFor(a => a.Message)

            

            </td>

        </tr>


        <tr>

            <td colspan="2">

                <input id="Submit1" type="submit" value="submit" />

            </td>

        </tr>

    </table>  

}