DataGridView - Show/Insert/Update/Delete

Question

Hi all,

I'm new to C# and our forum.

I just start code in C#.

I'm using visual studio 2008 and oracle database9i.

I need a code data manipulation with oracle db. ie. I want to access the oracle table through datagridview[showdata/insert/update/delete].

1. How to Bind the data to datagridview using dataset.

2. How to insert/update/delete using dataset in a datagridview.

Can you give me the samples to start.

Thanks in advance,

thenndral

Satyapriya Nayak · Answer

Hi thenndral, Look at the code below and do accordingly. 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 btnload_Click(object sender, System.EventArgs e) { // con.Open(); // str = "select * from student"; // com = new SqlCommand(str, con); // sqlda = new SqlDataAdapter(com); // ds = new DataSet(); // sqlda.Fill(ds, "student"); // dataGrid1.DataSource = ds; // dataGrid1.DataMember = "student"; // con.Close(); } private void btnclose_Click(object sender, System.EventArgs e) { // Application.Exit(); this.Close(); } } } Thanks If this post helps you mark it as answer

thenndral · Answer

Thank you Jitendra Sampathirao

thenndral · Answer

Hi sathya, Thanks for your reply. after delete a data from a grid, we need to refresh grid by using showall() method, here i have question, suppose in a table we have 10000 records we delete a single record, then load /bind all the data using adapter.fill, is this a good idea. when we delete and load continous performance is good. or is there any idea to delete grid data and refresh. I dont know my question make sense or not. thanks for being with me.... thenndral.

Jiteendra Sampathirao · Answer

go through this Link

thenndral · Answer

Thank you for your reply. Its Help me to good start... Here I attach my code, have a look and give your guidance. 1. after save data how to refresh datagridview. 2. how to update / delete from datagridview and at the same time it should be affect the table too... After i finish this work, i want to start study....1 dataset into many tables... thanks for all helps... thenndral

Sam Hobbs · Answer

If you can create a data source from your database, then my Database Table Update in a DataGridView without Writing Code article shows one way.

Prabhu Raja · Answer

Hi, I have attached my Source Code. It Will Help You.

Satyapriya Nayak · Answer

Hi thenndral, 1.How to Bind the data to datagridview using dataset. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsFormsApplication1 { public partial class Form1 : Form { OleDbConnection con = new OleDbConnection("connection string"); OleDbCommand com; OleDbDataAdapter oledbda; DataSet ds; string str; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { con.Open(); str = "select * from student"; com = new OleDbCommand(str, con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds, "student"); dataGridView1.DataSource = ds; dataGridView1.DataMember = "student"; con.Close(); } } }