getting problem in insert,Del,update in ADO.Net Disconnected

Question

I m facing problem in Insertion, deletion and update operation in ADO.Net Disconnected Model .. hv searched but most of demos r related to connected one...

M not able to get best solution..

Plz Help me ..
Thanks in Advance

Satyapriya Nayak · Answer

using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace Ado_Project_Data_Insert_Update_Save_Delete_Using_Disconnected_Mode_In_CSharp { /// /// Summary description for Form1. /// public class Form1 : System.Windows.Forms.Form { SqlConnection con=new SqlConnection("workstation id=\"HOME-Z8CKE1NER2\";packet size=4096;user id=sa;initial catalog=Dotn" + "et;persist security info=False"); SqlCommand com; SqlDataAdapter sqlda; DataSet ds=new DataSet(); SqlCommandBuilder objcom; String str; int flag; DataTable dt; DataRow dr; internal System.Windows.Forms.Button btnclose; internal System.Windows.Forms.Button btnload; internal System.Windows.Forms.Button btndelete; internal System.Windows.Forms.Button btnsave; internal System.Windows.Forms.Button btnmodify; internal System.Windows.Forms.Button btnadd; internal System.Windows.Forms.Button btnlast; internal System.Windows.Forms.Button btnprevious; internal System.Windows.Forms.Button btnnext; internal System.Windows.Forms.Button btnfirst; internal System.Windows.Forms.Label Label5; internal System.Windows.Forms.Label Label4; internal System.Windows.Forms.Label Label3; internal System.Windows.Forms.Label Label2; internal System.Windows.Forms.Label Label1; internal System.Windows.Forms.TextBox TextBox5; internal System.Windows.Forms.TextBox TextBox4; internal System.Windows.Forms.TextBox TextBox3; internal System.Windows.Forms.TextBox TextBox2; internal System.Windows.Forms.TextBox TextBox1; internal System.Windows.Forms.DataGrid dataGrid1; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// /// Clean up any resources being used. /// /// protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.btnclose = new System.Windows.Forms.Button(); this.btnload = new System.Windows.Forms.Button(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.btndelete = new System.Windows.Forms.Button(); this.btnsave = new System.Windows.Forms.Button(); this.btnmodify = new System.Windows.Forms.Button(); this.btnadd = new System.Windows.Forms.Button(); this.btnlast = new System.Windows.Forms.Button(); this.btnprevious = new System.Windows.Forms.Button(); this.btnnext = new System.Windows.Forms.Button(); this.btnfirst = new System.Windows.Forms.Button(); this.Label5 = new System.Windows.Forms.Label(); this.Label4 = new System.Windows.Forms.Label(); this.Label3 = new System.Windows.Forms.Label(); this.Label2 = new System.Windows.Forms.Label(); this.Label1 = new System.Windows.Forms.Label(); this.TextBox5 = new System.Windows.Forms.TextBox(); this.TextBox4 = new System.Windows.Forms.TextBox(); this.TextBox3 = new System.Windows.Forms.TextBox(); this.TextBox2 = new System.Windows.Forms.TextBox(); this.TextBox1 = new System.Windows.Forms.TextBox(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // btnclose // this.btnclose.Location = new System.Drawing.Point(368, 292); this.btnclose.Name = "btnclose"; this.btnclose.Size = new System.Drawing.Size(88, 23); this.btnclose.TabIndex = 41; this.btnclose.Text = "Form Close"; this.btnclose.Click += new System.EventHandler(this.btnclose_Click); // // btnload // this.btnload.Location = new System.Drawing.Point(480, 244); this.btnload.Name = "btnload"; this.btnload.Size = new System.Drawing.Size(96, 23); this.btnload.TabIndex = 40; this.btnload.Text = "Load Records"; this.btnload.Click += new System.EventHandler(this.btnload_Click); // // dataGrid1 // this.dataGrid1.DataMember = ""; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(304, 16); this.dataGrid1.Name = "dataGrid1"; this.dataGrid1.Size = new System.Drawing.Size(312, 160); this.dataGrid1.TabIndex = 39; // // btndelete // this.btndelete.Location = new System.Drawing.Point(280, 268); this.btndelete.Name = "btndelete"; this.btndelete.TabIndex = 38; this.btndelete.Text = "Delete"; this.btndelete.Click += new System.EventHandler(this.btndelete_Click); // // btnsave // this.btnsave.Location = new System.Drawing.Point(192, 268); this.btnsave.Name = "btnsave"; this.btnsave.TabIndex = 37; this.btnsave.Text = "Save"; this.btnsave.Click += new System.EventHandler(this.btnsave_Click); // // btnmodify // this.btnmodify.Location = new System.Drawing.Point(104, 268); this.btnmodify.Name = "btnmodify"; this.btnmodify.TabIndex = 36; this.btnmodify.Text = "Modify"; this.btnmodify.Click += new System.EventHandler(this.btnmodify_Click); // // btnadd // this.btnadd.Location = new System.Drawing.Point(16, 268); this.btnadd.Name = "btnadd"; this.btnadd.TabIndex = 35; this.btnadd.Text = "Add"; this.btnadd.Click += new System.EventHandler(this.btnadd_Click); // // btnlast // this.btnlast.Location = new System.Drawing.Point(280, 220); this.btnlast.Name = "btnlast"; this.btnlast.TabIndex = 34; this.btnlast.Text = "Last"; this.btnlast.Click += new System.EventHandler(this.btnlast_Click); // // btnprevious // this.btnprevious.Location = new System.Drawing.Point(192, 220); this.btnprevious.Name = "btnprevious"; this.btnprevious.TabIndex = 33; this.btnprevious.Text = "Previous"; this.btnprevious.Click += new System.EventHandler(this.btnprevious_Click); // // btnnext // this.btnnext.Location = new System.Drawing.Point(104, 220); this.btnnext.Name = "btnnext"; this.btnnext.TabIndex = 32; this.btnnext.Text = "Next"; this.btnnext.Click += new System.EventHandler(this.btnnext_Click); // // btnfirst // this.btnfirst.Location = new System.Drawing.Point(16, 220); this.btnfirst.Name = "btnfirst"; this.btnfirst.TabIndex = 31; this.btnfirst.Text = "First"; this.btnfirst.Click += new System.EventHandler(this.btnfirst_Click); // // Label5 // this.Label5.Location = new System.Drawing.Point(8, 148); this.Label5.Name = "Label5"; this.Label5.TabIndex = 30; this.Label5.Text = "Year"; // // Label4 // this.Label4.Location = new System.Drawing.Point(8, 116); this.Label4.Name = "Label4"; this.Label4.TabIndex = 29; this.Label4.Text = "Student Address"; // // Label3 // this.Label3.Location = new System.Drawing.Point(8, 84); this.Label3.Name = "Label3"; this.Label3.TabIndex = 28; this.Label3.Text = "Student Marks"; // // Label2 // this.Label2.Location = new System.Drawing.Point(8, 52); this.Label2.Name = "Label2"; this.Label2.TabIndex = 27; this.Label2.Text = "Student Name"; // // Label1 // this.Label1.Location = new System.Drawing.Point(8, 20); this.Label1.Name = "Label1"; this.Label1.TabIndex = 26; this.Label1.Text = "Student Id"; // // TextBox5 // this.TextBox5.Location = new System.Drawing.Point(128, 148); this.TextBox5.Name = "TextBox5"; this.TextBox5.TabIndex = 25; this.TextBox5.Text = ""; // // TextBox4 // this.TextBox4.Location = new System.Drawing.Point(128, 116); this.TextBox4.Name = "TextBox4"; this.TextBox4.TabIndex = 24; this.TextBox4.Text = ""; // // TextBox3 // this.TextBox3.Location = new System.Drawing.Point(128, 84); this.TextBox3.Name = "TextBox3"; this.TextBox3.TabIndex = 23; this.TextBox3.Text = ""; // // TextBox2 // this.TextBox2.Location = new System.Drawing.Point(128, 52); this.TextBox2.Name = "TextBox2"; this.TextBox2.TabIndex = 22; this.TextBox2.Text = ""; // // TextBox1 // this.TextBox1.Location = new System.Drawing.Point(128, 20); this.TextBox1.Name = "TextBox1"; this.TextBox1.TabIndex = 21; this.TextBox1.Text = ""; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(664, 334); this.Controls.Add(this.btnclose); this.Controls.Add(this.btnload); this.Controls.Add(this.dataGrid1); this.Controls.Add(this.btndelete); this.Controls.Add(this.btnsave); this.Controls.Add(this.btnmodify); this.Controls.Add(this.btnadd); this.Controls.Add(this.btnlast); this.Controls.Add(this.btnprevious); this.Controls.Add(this.btnnext); this.Controls.Add(this.btnfirst); this.Controls.Add(this.Label5); this.Controls.Add(this.Label4); this.Controls.Add(this.Label3); this.Controls.Add(this.Label2); this.Controls.Add(this.Label1); this.Controls.Add(this.TextBox5); this.Controls.Add(this.TextBox4); this.Controls.Add(this.TextBox3); this.Controls.Add(this.TextBox2); this.Controls.Add(this.TextBox1); this.Name = "Form1"; this.Text = "Form1"; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.Run(new Form1()); } private void Form1_Load(object sender, System.EventArgs e) { str = "select * from student"; com = new SqlCommand(str, con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds, "student"); dt = ds.Tables["student"]; TextBox1.DataBindings.Add ("Text",dt,"sid"); TextBox2.DataBindings.Add ("Text",dt,"sname"); TextBox3.DataBindings.Add ("Text",dt,"smarks"); TextBox4.DataBindings.Add ("Text",dt,"saddress"); TextBox5.DataBindings.Add ("Text",dt,"year"); dataGrid1.DataSource =ds; dataGrid1.DataMember ="student"; } private void btnfirst_Click(object sender, System.EventArgs e) { this.BindingContext[dt].Position=0; } private void btnnext_Click(object sender, System.EventArgs e) { this.BindingContext[dt].Position+=1; } private void btnprevious_Click(object sender, System.EventArgs e) { this.BindingContext[dt].Position-=1; } private void btnlast_Click(object sender, System.EventArgs e) { this.BindingContext[dt].Position =dt.Rows.Count-1; } private void btnadd_Click(object sender, System.EventArgs e) { flag=1; TextBox1.Text = ""; TextBox2.Text = ""; TextBox3.Text = ""; TextBox4.Text = ""; TextBox5.Text = ""; TextBox1.Focus(); } private void showall() { con.Open (); str = "select * from student"; com = new SqlCommand(str, con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds, "student"); con.Close(); dataGrid1.DataSource =ds; dataGrid1.DataMember ="student"; } private void btnmodify_Click(object sender, System.EventArgs e) { flag=2; dr=dt.Rows[this.BindingContext[dt].Position]; TextBox1.Focus(); } private void btnsave_Click(object sender, System.EventArgs e) { if (flag==1) { dr = dt.NewRow(); dr["sid"] = TextBox1.Text; dr["sname"] = TextBox2.Text; dr["smarks"] = TextBox3.Text; dr["saddress"] = TextBox4.Text; dr["year"] = TextBox5.Text; dt.Rows.Add(dr); objcom= new SqlCommandBuilder(sqlda); sqlda.Update(ds, "student"); sqlda.Fill(ds, "student"); MessageBox.Show("Record Sucessfully Inserted"); } else if (flag==2) { dr.BeginEdit(); dr["sid"] = TextBox1.Text; dr["sname"] = TextBox2.Text; dr["smarks"] = int.Parse(TextBox3.Text); dr["saddress"] = TextBox4.Text; dr["year"] = TextBox5.Text; dr.EndEdit(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds, "student"); sqlda.Fill(ds, "student"); MessageBox.Show("Record Sucessfully Modified"); } showall(); } private void btndelete_Click(object sender, System.EventArgs e) { if( MessageBox.Show ("do u 1 2 delete the Record",this.Text, MessageBoxButtons.YesNo , MessageBoxIcon.Question )== DialogResult.Yes ) { dr = dt.Rows[this.BindingContext[dt].Position]; dr.Delete(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds, "student"); sqlda.Fill(ds, "student"); MessageBox.Show("Record Sucessfully Deleted"); showall(); } } private void btnclose_Click(object sender, System.EventArgs e) { this.Close(); } } }