Update database using DataSet and SqlDataAdapter

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");}

///

/// 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.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

///

/// The main entry point for the application.

///

[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);

}

}

}