Connecting to Sql on Visual Studio,Sql with Asp.net

Connecting Visual Studio Asp.net with sql.

Download Sample Project

Prerequisites:
Vs2013, MSSql Server

Sample table Script.

CREATE TABLE [dbo].[personalinfo](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NULL,
	[Place] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Sppersonalinfo]
@Mode int=0,
@Name varchar(100)=NULL,
@Place varchar(100)=NUll
AS

	begin
	
		INSERT INTO personalinfo(Name,Place)
		VALUES(@Name,@Place)
	end
GO

Asp.net ServerSide Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Info
{

    public partial class Home : Page
    {
        private SqlConnection con = new SqlConnection("Data Source=ADMIN\\SQLEXPRESS;Initial Catalog=info; Persist Security Info=true; UID=sa; PWD=1234;");

        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                DisplayRecord();
            }
        }
        protected void DisplayRecord()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from personalinfo", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            con.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                gvGridview.DataSource = ds;
                gvGridview.DataBind();
            }

        }

        protected void btnSave_Click(object sender, EventArgs e)
        {

            con.Open();
            using (SqlCommand cmd = new SqlCommand("Sppersonalinfo", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                cmd.Parameters.AddWithValue("@Place", txtPlace.Text);
                cmd.Parameters.AddWithValue("@Mode", 0);

                cmd.ExecuteNonQuery();

                con.Close();
                cmd.Parameters.Clear();
            }
            txtName.Text = "";
            txtPlace.Text = "";
            Page.RegisterStartupScript("sa", "<script>alert('Saved Successfully')</script>");
            DisplayRecord();



        }

              protected void gvGridview_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvGridview.EditIndex = e.NewEditIndex;
            DisplayRecord();
        }

        protected void gvGridview_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int Id = int.Parse(gvGridview.Rows[e.RowIndex].Cells[1].Text.ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand("delete FROM personalinfo where id='" + Id + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            DisplayRecord();
            
        }

        protected void gvGridview_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int Id =Convert.ToInt16 ( ((TextBox)gvGridview.Rows[e.RowIndex].Cells[1].Controls[0]).Text.ToString());
            string Name = ((TextBox)gvGridview.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
            string Place = ((TextBox)gvGridview.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
            
            con.Open();
            SqlCommand cmd = new SqlCommand("Update personalinfo set Name='" + Name + "',Place='" + Place + "' where id='" + Id + "'", con);
            cmd.ExecuteNonQuery();
            con.Close();
            gvGridview.EditIndex = -1;
            DisplayRecord();
        }

      
        protected void gvGridview_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gvGridview.EditIndex = -1;
            DisplayRecord();
        }

       
        protected void gvGridview_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvGridview.PageIndex = e.NewPageIndex;
            DisplayRecord();
        }



    }
}