need to perform the Get,insert,update,delete functionalities in disconnected Architecture in ado.net.my code worked perfectly for Get,insert,update functionality but delete functionality does not work
my 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.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select * from Student where sid=@sid",con);
cmd.Parameters.Add("@sid", SqlDbType.Int).Value = TextBox1.Text;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
TextBox1.Text = dr[0].ToString();
TextBox2.Text = dr[1].ToString();
TextBox3.Text = dr[2].ToString();
}
else
{
Response.Write("Record not found");
}
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from Student", con);
DataSet ds = new DataSet();
da.Fill(ds, "Student");
SqlCommand insert = new SqlCommand("insert into Student values(@sid,@sname,@marks)", con);
insert.Parameters.Add("@sid", SqlDbType.Int).Value = TextBox1.Text;
insert.Parameters.Add("@sname", SqlDbType.VarChar, 50).Value = TextBox2.Text;
insert.Parameters.Add("@marks", SqlDbType.Int).Value = TextBox3.Text;
da.InsertCommand = insert;
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
dr[0] = TextBox1.Text;
dr[1] = TextBox2.Text;
dr[2] = TextBox3.Text;
dt.Rows.Add(dr);
da.Update(ds, "Student");
Response.Write("inserted successfully");
}
protected void Button3_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from Student", con);
DataSet ds = new DataSet();
da.Fill(ds, "Student");
SqlCommand update = new SqlCommand("Update Student set sname = @sname where sid = @sid", con);
update.Parameters.Add("@sname", SqlDbType.VarChar, 50).Value = TextBox2.Text;
update.Parameters.Add("@sid", SqlDbType.Int).Value = TextBox1.Text;
da.UpdateCommand = update;
if (ds.Tables[0].Rows.Count > 0)
{
DataRow r = ds.Tables[0].Rows[0];
r[0] = TextBox1.Text;
r[1] = TextBox2.Text;
da.Update(ds, "Student");
Console.WriteLine("Updated sucessfully");
}
}
protected void Button4_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=AMMA-PC\\SQLEXPRESS;Initial Catalog=E_learn;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("select * from Student", con);
DataSet ds = new DataSet();
da.Fill(ds, "Student");
SqlCommand delete = new SqlCommand("delete from Student where sid=@sid", con);
delete.Parameters.Add("@sid", SqlDbType.Int).Value = TextBox1.Text;
da.DeleteCommand = delete;
if (ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
DataColumn[] Columns = new DataColumn[1];
Columns[0] = dt.Columns["sid"];
dt.PrimaryKey = Columns;
DataRow dr = dt.Rows[0];
if(dr[0]==TextBox1.Text )
{
dr.Delete();
dt.AcceptChanges();
}
da.Update(ds, "Student");
Response.Write("deleted successfully");
}
}
}
please help me.
thank u.
What I have tried:
my code worked perfectly for Get,insert,update functionality but delete functionality does not work