Update Database using DataSet

In this article, we will see how to update a database using a Data Adapter's Update method using C# and ADO.NET.

Here we are going to see how to update database using DataSet and SqlDataAdapter.

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient; 

namespace UpdateDatabase

{ 

          public class TestDataSet : System.Windows.Forms.Form

          {  

                    private System.Windows.Forms.DataGrid dataGrid1;

                    private System.Windows.Forms.Button btnLoad;

                    private System.Windows.Forms.Button btnUpdate;

                    private DataSet ds;

                    private SqlDataAdapter adap;

                    private SqlConnection con;

                    private System.Windows.Forms.Label lblTitle;

                    private System.Windows.Forms.TextBox txtTitle; 

                    private System.ComponentModel.Container components = null;

                    public TestDataSet()

                    {

                             InitializeComponent();

                             con = new SqlConnection("server=.;uid=sa;pwd=test;database=test");} 

                    /// <summary> 

                    /// Clean up any resources being used. 

                    /// </summary> 

                    protected override void Dispose( bool disposing )

                    {

 

                             if( disposing )

                             {

                                       if (components != null)

                                       {

                                                components.Dispose();

                                       }

                             }

                             base.Dispose( disposing );

                    }

 

                    #region Windows Form Designer generated code

 

                    /// <summary>

                    /// Required method for Designer support - do not modify

                    /// the contents of this method with the code editor.

                    /// </summary>

 

                    private void InitializeComponent()

                    {

 

                             this.dataGrid1 = new System.Windows.Forms.DataGrid();

                             this.btnUpdate = new System.Windows.Forms.Button();

                             this.btnLoad = new System.Windows.Forms.Button();

                             this.txtTitle = new System.Windows.Forms.TextBox();

                             this.lblTitle = new System.Windows.Forms.Label();

                             ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();

                             this.SuspendLayout();

 

                             //

                             // dataGrid1

                             //  

                             this.dataGrid1.DataMember = "";

                             this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;

                             this.dataGrid1.Location = new System.Drawing.Point(16, 80);

                             this.dataGrid1.Name = "dataGrid1";

                             this.dataGrid1.Size = new System.Drawing.Size(568, 280);

                             this.dataGrid1.TabIndex = 0;

 

                             //

                             // btnUpdate

                             //  

                             this.btnUpdate.Location = new System.Drawing.Point(176, 369);

                             this.btnUpdate.Name = "btnUpdate";

                             this.btnUpdate.Size = new System.Drawing.Size(112, 23);

                             this.btnUpdate.TabIndex = 1;

                             this.btnUpdate.Text = "Update Title";

                             this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);

 

                             //

                             // btnLoad

                             //  

                             this.btnLoad.Location = new System.Drawing.Point(504, 56);

                             this.btnLoad.Name = "btnLoad";

                             this.btnLoad.TabIndex = 2;

                             this.btnLoad.Text = "Load";

                             this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);

 

                             //

                             // txtTitle

                             //  

                             this.txtTitle.Location = new System.Drawing.Point(64, 372);

                             this.txtTitle.Name = "txtTitle";

                             this.txtTitle.TabIndex = 3;

                             this.txtTitle.Text = "";

 

                             //

                             // lblTitle

                             //  

                             this.lblTitle.AutoSize = true;

                             this.lblTitle.Location = new System.Drawing.Point(16, 376);

                             this.lblTitle.Name = "lblTitle";

                             this.lblTitle.Size = new System.Drawing.Size(26, 16);

                             this.lblTitle.TabIndex = 4;

                             this.lblTitle.Text = "Title";

 

                             //

                             // TestDataSet

                             //  

                             this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

                             this.ClientSize = new System.Drawing.Size(616, 438);

                             this.Controls.Add(this.lblTitle);

                             this.Controls.Add(this.txtTitle);

                             this.Controls.Add(this.btnLoad);

                             this.Controls.Add(this.btnUpdate);

                             this.Controls.Add(this.dataGrid1); 

                             this.Name = "TestDataSet";

                             this.Text = "Data";

                             ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

                             this.ResumeLayout(false);

 

                    }

 

                    #endregion 

                    /// <summary>

                    /// The main entry point for the application.

                    /// </summary>

 

                    [STAThread]

                    static void Main()

                    {

                             Application.Run(new TestDataSet());

                    }

 

                    private void btnLoad_Click(object sender, System.EventArgs e)

                    {

                             LoadData();

                    }  

                    //Load data from database make sure we are fetching primarykey too so we could use adapter

                    update method

                    //on dataset because commandbuilder will create command on that primary key.

                    //Commands will be created as it see rowstate of dataset table's row's rowstate.

                    private void LoadData()

                    {

                             if(ds != null)

                                       ds.Clear();

                             adap = new SqlDataAdapter("select id,title, description from testtable", con);

                             ds = new DataSet();

                             adap.Fill(ds);

                             dataGrid1.DataSource = ds.Tables[0];

                    } 

                    //This click will update one of the field in the database using adapter update() method on

                    dataset.

                    private void btnUpdate_Click(object sender, System.EventArgs e)

                    {

                             SqlCommandBuilder com = new SqlCommandBuilder(adap);

                             foreach(DataRow dr in ds.Tables[0].Rows)

                                       dr["title"] = txtTitle.Text;

                             adap.Update(ds);

                    } 

          }

}