Using CommandBuilder to perform DML Operations



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

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=True");
    DataTable dt = new DataTable();
    SqlDataAdapter da;


    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            da = new SqlDataAdapter("select * from tb_Student", con);
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.Fill(dt);
        }

    }


    protected void btnRegister_Click(object sender, EventArgs e)
    {

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (txtRollNo.Text == dt.Rows[i]["RollNO"].ToString())
            {
                Response.Write("<script>alert('Roll No Already Exists')</script>");
                return;

            }
        }

        DataRow dr = null;
        dr = dt.NewRow();
        dr["RollNO"] = txtRollNo.Text;
        dr["Name"] = txtStuName.Text;
        dr["Class"] = txtClass.Text;
        dr["Section"] = txtSection.Text;
        dr["Age"] = txtAge.Text;
        dt.Rows.Add(dr);
        dt.AcceptChanges();
        da.Update(dt);
     
        Response.Write("<script>alert('Student Added Successfully')</script>");
    
    }


  
 protected void btnDelete_Click(object sender, EventArgs e)
    {
        int k = 0;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (txtRollNo.Text == dt.Rows[i]["RollNO"].ToString())
            {
                k = 1;
                DataRow dr = null;
                dr = dt.Select("RollNo=" + txtRollNo.Text)[0];
                if (dr != null)
                {
                    dr.Delete();
      dt.AcceptChanges();
                    da.Update(dt);
                    Response.Write("<script>alert('Deleted Successfully')</script>");
                
                    break;
                }

            }
         
        }
        if (k == 0)
        {
            Response.Write("<script>alert('There is no such RollNO to Delete')</script>");
   
        }     
     
    }




    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        int k = 0;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            if (txtRollNo.Text == dt.Rows[i]["RollNO"].ToString())
            {
                k = 1;
                DataRow dr = null;
                dr = dt.Select("RollNo=" + txtRollNo.Text)[0];
                if (dr != null)
                {

                    dr.BeginEdit();
                    dr["RollNO"] = txtRollNo.Text;
                    dr["Name"] = txtStuName.Text;
                    dr["Class"] = txtClass.Text;
                    dr["Section"] = txtSection.Text;
                    if (txtAge.Text != "")
                    {
                        dr["Age"] = (txtAge.Text);
                    }
                    dr.EndEdit();
      dt.AcceptChanges();

                    int res = da.Update(dt);
                    if (res > 0)
                    {
                        Response.Write("<script>alert('Updated Successfully')</script>");
                    }
                    break;
                }

            }
        }

        if (k == 0)
        {
            Response.Write("<script>alert('There is no such Roll No available')</script>");

        }
    }

0 comments

Post a Comment