Thursday 16 May 2013

Most imp Asp.Net Insert Delete Update And Grid View

Asp.Net Insert Delete Update And Grid View 
http://dotnet4beginner.com/Tutorials/Insert-Delete-Update-And-Grid-View.html


Insert,Delete,Update and Gridview in ASP.NET
Start Visual Studio and create a new project.

Create a database 'name' and add a table 'NameList' to database.



Change the Connectionstring in Web.config file.

<connectionStrings>
    <add name="connectionString"
           connectionString="data source=LEEDHAR1-PC\SQLEXPRESS;database=name;uid=sa;pwd=sa123;Integrated Security=SSPI;User Instance=false"
             providerName="System.Data.SqlClient" />
    </connectionStrings>
Add Content pages

1.Insert.aspx.

2.Update.aspx

3.Delete.aspx

4.View.aspx

Insert.aspx



To save first name and last name into database add the following code in Insert.aspx.cs
Insert.aspx.cs

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

namespace DatabaseConnection
{
    public partial class Insert : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            string con = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
            SqlConnection conn = new SqlConnection(con);
            conn.Open();
            string sql = "insert into NameList (fname,lname) values('" + TextBox1.Text + "','" + TextBox2.Text + "')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
Update.aspx



The dropdownlist loads all names we entered into the database,and we can select anyone of them and click on 'GO>>' button,Then the texboxes allows you to edit the first name,last name of selected one

Add the following code in Update.aspx.cs
Update.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace DatabaseConnection
{
    
    public partial class Edit : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection();
        DataTable dataTable = new DataTable();
        public void open()
        {
            string con = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
            conn = new SqlConnection(con);
            conn.Open();
        }
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fillcombo();
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
           
            open();
            string sql = "update NameList set fname='" + txtFname.Text + "',lname='"+ txtLname.Text  +"' where id='" + DropDownList1.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            fillcombo();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            
            open();
            string sql = "select * from NameList where id='" + DropDownList1.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            rdr.Read();

            txtFname.Text = rdr["fname"].ToString();
            txtLname.Text = rdr["lname"].ToString(); 
            conn.Close();
           
        }
        public void fillcombo()
        {
          
            open();
            string sql = "select * from NameList ";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteReader();
            conn.Close();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            
            da.Fill(dataTable);
            DataRow dr = null;
            dr = dataTable.NewRow();
            dr["fname"] = "------select";
            dr["lname"] = "-----------";
            dr["id"] = "0";
            dataTable.Rows.InsertAt(dr, 0);
           
            
            for (int count = 0; count < dataTable.Rows.Count; count++)
            {
                dataTable.Rows[count]["fname"] = dataTable.Rows[count]["fname"].ToString() + " " + dataTable.Rows[count]["lname"].ToString();
            }
            
            DropDownList1.DataTextField = "fname";

            DropDownList1.DataValueField = "id";
            DropDownList1.DataSource = dataTable;
            DropDownList1.DataBind();
        }
    }
}
Delete.aspx



The dropdownlist loads all names we entered into the database,and we can select anyone of them and click on 'DELETE' button to delete the selected item.

Add the following code in Delete.aspx.cs
Delete.aspx.cs

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

namespace DatabaseConnection
{
    public partial class Delete : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection();
        DataTable dataTable = new DataTable();
        public void open()
        {
            string con = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
            conn = new SqlConnection(con);
            conn.Open();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fillcombo();
            }
        }
        public void fillcombo()
        {

            open();
            string sql = "select * from NameList ";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteReader();
            conn.Close();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dataTable);
            DataRow dr = null;
            dr = dataTable.NewRow();
            dr["fname"] = "------select";
            dr["lname"] = "-----------";
            dr["id"] = "0";
            dataTable.Rows.InsertAt(dr, 0);
            for (int count = 0; count < dataTable.Rows.Count; count++)
            {
                dataTable.Rows[count]["fname"] = dataTable.Rows[count]["fname"].ToString() + " " + dataTable.Rows[count]["lname"].ToString();
            }
            DropDownList1.DataTextField = "fname";

            DropDownList1.DataValueField = "id";
            DropDownList1.DataSource = dataTable;
            DropDownList1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            open();
            string sql = "Delete from NameList where id='" + DropDownList1.SelectedValue  + "' ";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            fillcombo();
        }
    }
}
Add the following code in View.aspx.cs to view the entered data in GridView.
View.aspx.cs

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

namespace DatabaseConnection
{
    public partial class View : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            GridView1.DataSource = GetData();
            GridView1.DataBind();
           
        }
        DataTable GetData()
        {
            DataTable dt = new DataTable();
            string con = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
            SqlConnection conn = new SqlConnection(con);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from NameList ", conn);
            SqlDataAdapter adpt = new SqlDataAdapter(cmd);
            adpt.Fill(dt);
            return dt;
        }
    }
}
Run the project

Insert



update



Delete

No comments:

Post a Comment