CRUD Operation Using ASP.NET MVC, JSON, ADO.NET, Stored Procedure

Our first step is to create and design a SQL database. In the following steps we have the demonstration of creating a database table and basic CRUD Stored Procedure.

Let’s Create a Sample Database

Let’s create a sample database named ‘SampleDB’ with SQL Management Studio.

Using the ‘SampleDB’ now create a Table name ‘tblCustomer’.

Script:

CREATE TABLE [dbo].[tblCustomer](
    [CustID] [bigint] NOT NULL,
    [CustName] [nvarchar](50) NULL,
    [CustEmail] [nvarchar](50) NOT NULL,
    [CustAddress] [nvarchar](256) NULL,
    [CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
    [CustID] ASC,
    [CustEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Stored Procedure

Now in this step we will go through to perform CRUD operations with stored procedure: 

1. First we will create a stored procedure (SP) to RETRIVE record from Customer table.
2. Now we will create a stored procedure( SP) to INSERT record into Customer table.
3. Now we will create another procedure (SP) to UPDATE existing data in our Customer table.
4. The last step we will create a stored procedure to DELETE existing record from customer table.

Stored Procedure to RETRIVE Record:

ALTER PROCEDURE [dbo].[CREATE_CUSTOMER]
    -- Add the parameters for the stored procedure here
(
     @CustName NVarchar(50)
    ,@CustEmail NVarchar(50)
    ,@CustAddress NVarchar(256)
    ,@CustContact  NVarchar(50)
)
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    ---- Try Catch--
    BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @CustID Bigint
            SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')

        -- Insert statements for procedure here
        INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])
        VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)
        SELECT 1
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACK TRANSACTION
    END CATCH

END

Stored Procedure to INSERT Record:

ALTER PROCEDURE [dbo].[CREATE_CUSTOMER]
    -- Add the parameters for the stored procedure here
(
     @CustName NVarchar(50)
    ,@CustEmail NVarchar(50)
    ,@CustAddress NVarchar(256)
    ,@CustContact  NVarchar(50)
)
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    ---- Try Catch--
    BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @CustID Bigint
            SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')

        -- Insert statements for procedure here
        INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])
        VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)
        SELECT 1
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACK TRANSACTION
    END CATCH

END

Stored Procedure to UPDATE Record:

ALTER PROCEDURE [dbo].[UPDATE_CUSTOMER]
    -- Add the parameters for the stored procedure here
     @CustID BIGINT
    ,@CustName NVarchar(50)
    ,@CustEmail NVarchar(50)
    ,@CustAddress NVarchar(256)
    ,@CustContact  NVarchar(50)
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    ---- Try Catch--
    BEGIN TRY
        BEGIN TRANSACTION

        -- Update statements for procedure here
        UPDATE [dbo].[tblCustomer]
        SET [CustName] = @CustName,
            [CustAddress] = @CustAddress,
            [CustContact] = @CustContact
        WHERE [CustID] = @CustID AND [CustEmail] = @CustEmail
        SELECT 1
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACK TRANSACTION
    END CATCH

END

Stored Procedure to DELETE Record:

ALTER PROCEDURE [dbo].[DELETE_CUSTOMER]
    -- Add the parameters for the stored procedure here
      @CustID BIGINT
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;

    ---- Try Catch--
    BEGIN TRY
        BEGIN TRANSACTION

        -- Delete statements for procedure here
        DELETE [dbo].[tblCustomer]
        WHERE [CustID] = @CustID
        SELECT 1
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACK TRANSACTION
    END CATCH

END

Stored Procedure to VIEW Single Record Details:

ALTER PROCEDURE [dbo].[VIEW_CUSTOMER]
    -- Add the parameters for the stored procedure here
    @CustID BIGINT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
    -- Select statements for procedure here

    SELECT * FROM [dbo].[tblCustomer]
    WHERE [CustID] = @CustID
END

Let’s Start:

Open Visual Studio 2015, Click File, New, then Project. In this window give a name to the project and solution.



Click ok and another window will appear with project template, choose MVC:



Click ok and visual studio will create and load a new ASP.NET application template. In this case we are using ASP.NET MVC 5. The new window will look like the following:



Our next step is to create a new model. Now right click on model folder and add new item, choose data from the left side menu and select ADO.NET Entity Data Model. Follow the process and click next.



Next step is to connect to database to generate .edmx file:



Let’s Start with Design (UI)

Here are the .cshtml file of Customer CRUD operations. In the index view we have used a partial view to render the Customer list with Infinite scroll.

Index.cshtml 

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>

<!-- list -->
<div id="resultCust"></div>
<div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">
    <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...
</div>
<!-- list -->

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

    var inProgress = false;
    $(document).ready(function () {

        //======================Default Load================//
        inProgress = true;
        $("#loader").show();
        $.get("@Url.Action("GetCustomer", "Customer")", { "RowCountPerPage": 5 },
                   function (data) {
                       $("#resultCust").append(data);
                       $("#loader").hide();
                       inProgress = false;
                   });

        //======================UL SCroll Load================//
        var page = 1;
        var _inCallback = false;

        $(window).scroll(function () {

            if ($(window).scrollTop() == $(document).height() - $(window).height()) {
                if (page > -1 && !_inCallback) {
                    _inCallback = true;
                    page++;
                    $("#loader").show();
                    $.get("@Url.Action("GetCustomer_Scroll", "Customer")", { "PageNo": page, "RowCountPerPage": 5 },
                     function (data) {
                         if (data != '') {
                             $("#resultCust").append(data);
                         }
                         else {
                             page = -1;
                         }
                         $("#loader").hide();
                         _inCallback = false;
                     });
                }
            }
        });
    });

</script>

Partial view to view Customers:

@model IEnumerable<CRUD_MVC5.Models.tblCustomer>


@foreach (var item in Model)
{
    <div>
        <strong>Name: @Html.DisplayFor(modelItem => item.CustName)</strong>
    </div>
    <div>
        Adddress: @Html.DisplayFor(modelItem => item.CustAddress)
    </div>
    <div>
        @Html.DisplayFor(modelItem => item.CustEmail)
    </div>
    <div>
        @Html.DisplayFor(modelItem => item.CustContact)
    </div>
    <div>
        @Html.ActionLink("Edit", "Edit", new { id = item.CustID }) |
        @Html.ActionLink("Delete", "Delete", new { id = item.CustID }, new { @class = "delete", @id = item.CustID })
    </div>
    <hr />
}

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

<script type="text/javascript">
    $(document).ready(function () {

        $('a.delete').click(function (e) {
            e.preventDefault();

            var CustomerId = e.target.id;
            var flag = confirm('You are about to delete Customer ID ' + CustomerId + ' permanently.Are you sure you want to delete this record?');
            if (flag) {
                $.ajax({
                    url: '/Customer/Delete',
                    async: false,
                    type: "POST",
                    data: { id: CustomerId },
                    dataType: "json",
                    success: function (data, textStatus, jqXHR) {
                        if (data.Message == 1) {
                            //alert('Record Deleted!');
                            $("#" + CustomerId).parent().parent().remove();
                            window.location.href = '/Customer';
                        }
                        else {
                            alert('Record not Affected to DataBase!!');
                        }

                        $("#loader").hide();
                    },
                    error: function (jqXHR, textStatus, errorThrown) {
                        alert(jqXHR + "-" + textStatus + "-" + errorThrown);
                    }
                });
            }
            return false;
        });
    });
</script>

Create.cshtml

In this view we have sent a JSON object to MVC Action Method and have your Action Method return you a JSON result.

@model CRUD_MVC5.Models.tblCustomer

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>

<div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">
    <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...
</div>

@using (Html.BeginForm(null, null, FormMethod.Post, new { @id = "CustomerForm", @class = "form" }))
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Create Customer</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

        <div class="form-group">
            @Html.LabelFor(model => model.CustName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.CustEmail, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustEmail, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustEmail, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.CustAddress, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustAddress, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustAddress, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.CustContact, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustContact, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustContact, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" id="SubmitForm" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

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

<script type="text/javascript">
    $(document).ready(function () {

        $("#SubmitForm").click(function (e) {
            e.preventDefault();
            $("#loader").show();

            var model_data = {};
            model_data.CustName = $("#CustName").val();
            model_data.CustEmail = $("#CustEmail").val();
            model_data.CustAddress = $("#CustAddress").val();
            model_data.CustContact = $("#CustContact").val();

            if (model_data.CustEmail != '') {

                $.ajax({
                    url: '/Customer/Create',
                    async: false,
                    type: "POST",
                    data: JSON.stringify(model_data),
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    success: function (data, textStatus, jqXHR) {
                        if (data.Message == 1) {
                            alert('Record Successfully Saved!');
                            window.location.href = '/Customer';
                        }
                        else {
                            alert('Record not Affected to DataBase!!');
                        }

                        $("#loader").hide();
                    },
                    error: function (jqXHR, textStatus, errorThrown) {
                        alert(jqXHR + "-" + textStatus + "-" + errorThrown);
                    }
                });
            }
            else {
                return;
            }
        });
    });
</script>

Edit.cshtml

In this view we have sent a JSON object to MVC Action Method and have your Action Method return you a JSON result.

@model CRUD_MVC5.Models.tblCustomer

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

<div id="loader" style="text-align: center; display: none; margin-bottom: 20px;">
    <img alt="Loading" src="@Url.Content("~/Content/loader.gif")" /> Loading...
</div>

@using (Html.BeginForm(null, null, FormMethod.Post, new { @id = "CustomerForm", @class = "form" }))
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>Edit Customer</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.CustID)

        <div class="form-group">
            @Html.LabelFor(model => model.CustName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustName, "", new { @class = "text-danger" })
            </div>
        </div>

        @Html.HiddenFor(model => model.CustEmail)

        <div class="form-group">
            @Html.LabelFor(model => model.CustAddress, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustAddress, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustAddress, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.CustContact, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CustContact, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CustContact, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" id="SubmitForm" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>


<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {

        $("#SubmitForm").click(function (e) {
            e.preventDefault();
            $("#loader").show();

            var model_data = {};
            model_data.CustID = $("#CustID").val();
            model_data.CustName = $("#CustName").val();
            model_data.CustEmail = $("#CustEmail").val();
            model_data.CustAddress = $("#CustAddress").val();
            model_data.CustContact = $("#CustContact").val();

            if (model_data.CustEmail != '') {

                $.ajax({
                    url: '/Customer/Edit',
                    async: false,
                    type: "POST",
                    data: JSON.stringify(model_data),
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    success: function (data, textStatus, jqXHR) {
                        if (data.Message == 1) {
                            alert('Record Successfully Saved!');
                            window.location.href = '/Customer';
                        }
                        else {
                            alert('Record not Affected to DataBase!!');
                        }

                        $("#loader").hide();
                    },
                    error: function (jqXHR, textStatus, errorThrown) {
                        alert(jqXHR + "-" + textStatus + "-" + errorThrown);
                    }
                });
            }
            else {
                return;
            }
        });
    });
</script>

Let’s Start with Coding

In this app we are going to apply CRUD operation on a single table named Customer. To do first we need to create a Controller for the operations. To add a new Controller file we need to click right mouse and an option menu will appear. Click Add, then Controller.



Let’s name it CustomerController. In the controller we will create action methods to perform CRUD operations:

Below code sample is for Customer CRUD operations:

// Customer Controller
public class CustomerController : Controller
{
    // GET: Customer
    public ActionResult Index()
    {
        return View();
    }

    // GET: Customer
    [HttpGet]
    public ActionResult GetCustomer(int RowCountPerPage)
    {
        try
        {
            int PageNo = 0;
            int IsPaging = 0;

            CrudDataService objCrd = new CrudDataService();
            List<tblCustomer> modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);
            return PartialView("_ListCustomer", modelCust);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    // GET: Customer/InfinitScroll
    [HttpGet]
    public ActionResult GetCustomer_Scroll(int PageNo, int RowCountPerPage)
    {
        try
        {
            Thread.Sleep(2000);
            int IsPaging = 1;
            CrudDataService objCrd = new CrudDataService();
            List<tblCustomer> modelCust = objCrd.GetCustomerList(PageNo, RowCountPerPage, IsPaging);
            return PartialView("_ListCustomer", modelCust);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    // GET: Customer/Create
    public ActionResult Create()
    {
        return View();
    }

    // GET: Customer/Create
    [HttpPost]
    public JsonResult Create(tblCustomer objCust)
    {
        try
        {
            CrudDataService objCrd = new CrudDataService();
            Int32 message = 0;

            if ((objCust.CustName != null) && (objCust.CustEmail != null)) message = objCrd.InsertCustomer(objCust);
            else message = -1;
            return Json(new
            {
                Success = true,
                Message = message
            });
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    // GET: Customer/Edit
    public ActionResult Edit()
    {
        return View();
    }

    // GET: Customer/Edit
    [HttpGet]
    public ActionResult Edit(long? id)
    {
        try
        {
            CrudDataService objCrd = new CrudDataService();
            tblCustomer modelCust = objCrd.GetCustomerDetails(id);
            return View(modelCust);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    // GET: Customer/Edit
    [HttpPost]
    public JsonResult Edit(tblCustomer objCust)
    {
        try
        {
            CrudDataService objCrd = new CrudDataService();
            Int32 message = 0;
            message = objCrd.UpdateCustomer(objCust);
            return Json(new
            {
                Success = true,
                Message = message
            });

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }


    // GET: Customer/Delete
    [HttpPost]
    public JsonResult Delete(long? id)
    {
        try
        {
            CrudDataService objCrd = new CrudDataService();
            Int32 message = 0;
            message = objCrd.DeleteCustomer(id);
            return Json(new
            {
                Success = true,
                Message = message
            });

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

As we know earlier that we will use ADO.NET, Stored Procedure, so to connect the database we need to modify our config file to add Connection String for database connection:

<connectionStrings>
   <add name="dbConn" connectionString="Data source=DESKTOP-4L9DM2J; Initial Catalog=SampleDB; User Id=sa; Password=sa@123" providerName="System.Data.SqlClient"/>
</connectionStrings>

Now we need to create another class to use connection string and open our database connection. Let’s name it dbConnector.

// Database Connection
public class dbConnector
{
    private SqlConnection SqlConn = null;

    public SqlConnection GetConnection
    {
        get { return SqlConn; }
        set { SqlConn = value; }
    }

    public dbConnector()
    {
        string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
        SqlConn = new SqlConnection(ConnectionString);
    }
}

To perform CRUD operations we will create a separate class called CrudDataService. In this class we have five methods that will interact with the database to perform CRUD operations.

// Database Service
public class CrudDataService
{
    public List<tblCustomer> GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging)
    {
        dbConnector objConn = new dbConnector();
        SqlConnection Conn = objConn.GetConnection;
        Conn.Open();

        try
        {
            List<tblCustomer> _listCustomer = new List<tblCustomer>();
            //_listCustomer = null;

            if (Conn.State != System.Data.ConnectionState.Open)
                Conn.Open();

            SqlCommand objCommand = new SqlCommand("READ_CUSTOMER", Conn);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.AddWithValue("@PageNo", PageNo);
            objCommand.Parameters.AddWithValue("@RowCountPerPage", RowCountPerPage);
            objCommand.Parameters.AddWithValue("@IsPaging", IsPaging);
            SqlDataReader _Reader = objCommand.ExecuteReader();

            while (_Reader.Read())
            {
                tblCustomer objCust = new tblCustomer();
                objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                objCust.CustName = _Reader["CustName"].ToString();
                objCust.CustEmail = _Reader["CustEmail"].ToString();
                objCust.CustAddress = _Reader["CustAddress"].ToString();
                objCust.CustContact = _Reader["CustContact"].ToString();
                _listCustomer.Add(objCust);

            }

            return _listCustomer;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }
    }

    public tblCustomer GetCustomerDetails(long? id)
    {

        dbConnector objConn = new dbConnector();
        SqlConnection Conn = objConn.GetConnection;
        Conn.Open();

        try
        {
            tblCustomer objCust = new tblCustomer();

            if (Conn.State != System.Data.ConnectionState.Open)
                Conn.Open();

            SqlCommand objCommand = new SqlCommand("VIEW_CUSTOMER", Conn);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.AddWithValue("@CustID", id);
            SqlDataReader _Reader = objCommand.ExecuteReader();

            while (_Reader.Read())
            {
                objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                objCust.CustName = _Reader["CustName"].ToString();
                objCust.CustEmail = _Reader["CustEmail"].ToString();
                objCust.CustAddress = _Reader["CustAddress"].ToString();
                objCust.CustContact = _Reader["CustContact"].ToString();
            }

            return objCust;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }
    }

    public Int32 InsertCustomer(tblCustomer objCust)
    {
        dbConnector objConn = new dbConnector();
        SqlConnection Conn = objConn.GetConnection;
        Conn.Open();

        int result = 0;

        try
        {
            if (Conn.State != System.Data.ConnectionState.Open)
                Conn.Open();

            SqlCommand objCommand = new SqlCommand("CREATE_CUSTOMER", Conn);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);
            objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);
            objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);
            objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);

            result = Convert.ToInt32(objCommand.ExecuteScalar());

            if (result > 0)
            {
                return result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }
    }

    public Int32 UpdateCustomer(tblCustomer objCust)
    {
        dbConnector objConn = new dbConnector();
        SqlConnection Conn = objConn.GetConnection;
        Conn.Open();

        int result = 0;

        try
        {
            if (Conn.State != System.Data.ConnectionState.Open)
                Conn.Open();

            SqlCommand objCommand = new SqlCommand("UPDATE_CUSTOMER", Conn);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.AddWithValue("@CustID", objCust.CustID);
            objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);
            objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);
            objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);
            objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);

            result = Convert.ToInt32(objCommand.ExecuteScalar());

            if (result > 0)
            {
                return result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }
    }

    public Int32 DeleteCustomer(long? id)
    {
        dbConnector objConn = new dbConnector();
        SqlConnection Conn = objConn.GetConnection;
        Conn.Open();

        int result = 0;

        try
        {
            if (Conn.State != System.Data.ConnectionState.Open)
                Conn.Open();

            SqlCommand objCommand = new SqlCommand("DELETE_CUSTOMER", Conn);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.AddWithValue("@CustID", id);
            result = Convert.ToInt32(objCommand.ExecuteScalar());

            if (result > 0)
            {
                return result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (Conn != null)
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                    Conn.Dispose();
                }
            }
        }
    }
}

Note: This is a very basic app to demonstrate the process who are still confused how to start, my goal was to make it simple.