Saving a DataSet back to Database using SqlDataAdapter

by Jagadish Pulakhandam on 9/1/2011 1:51:32 PM
Rated 0 from 0 votes
Brief: Demonstrates on fetching data into DataSets / DataTables, modifying data (in offline mode) and updating back to database.
Posted to: Manipulating (insert/update/delete) Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates the following:
  • Fetching data into DataSet
  • Adding/Modifying/Deleting rows from DataTable (operating offline i.e., without connecting to Database)
  • Check DataSet to see all modifications done (prior to saving data)
  • Saving DataSet back to Database using SqlDataAdapter (batch save)
  • Check errors for rows in DataTables after save
Screen shot:



Source Code:

001.using System;
002.using System.Collections.Generic;
003.using System.ComponentModel;
004.using System.Data;
005.using System.Drawing;
006.using System.Linq;
007.using System.Text;
008.using System.Windows.Forms;
009.using System.Data.SqlClient;
010. 
011.namespace CSUpdateUsingDataAdapter
012.{
013.    public partial class Form1 : Form
014.    {
015.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=sql2008";
016.        DataSet ds;
017. 
018.        public Form1()
019.        {
020.            InitializeComponent();
021.        }
022. 
023.        private void Form1_Load(object sender, EventArgs e)
024.        {
025.            ds = new DataSet();
026.            using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Emp", new SqlConnection(CONNSTR)))
027.            {
028.                da.Fill(ds, "tEmployees");
029.            }
030.            this.DataGridView1.DataSource = ds.Tables["tEmployees"];
031. 
032.        }
033. 
034.        private void btnAdd_Click(object sender, EventArgs e)
035.        {
036.            try
037.            {
038.                DataTable dt = ds.Tables["tEmployees"];
039.                DataRow dr = dt.NewRow();
040.                dr["empno"] = this.txtEmpno.Text;
041.                dr["ename"] = this.txtEname.Text;
042.                dr["sal"] = this.txtSal.Text;
043.                dr["deptno"] = this.txtDeptno.Text;
044.                dt.Rows.Add(dr);
045.                btnClear_Click(null, null);
046.            }
047.            catch (Exception ex)
048.            {
049.                MessageBox.Show(ex.Message);
050.            }
051.        }
052. 
053.        private void btnUpdate_Click(object sender, EventArgs e)
054.        {
055.            try
056.            {
057.                DataTable dt = ds.Tables["tEmployees"];
058.                DataRow[] dr = dt.Select("Empno = " + this.lblOldEmpno.Text);
059.                if (dr.Length > 0)
060.                {
061.                    dr[0]["empno"] = this.txtEmpno.Text;
062.                    dr[0]["ename"] = this.txtEname.Text;
063.                    dr[0]["sal"] = this.txtSal.Text;
064.                    dr[0]["deptno"] = this.txtDeptno.Text;
065.                }
066.                btnClear_Click(null, null);
067.            }
068.            catch (Exception ex)
069.            {
070.                MessageBox.Show(ex.Message);
071.            }
072.        }
073. 
074.        private void btnDelete_Click(object sender, EventArgs e)
075.        {
076.            DataTable dt = ds.Tables["tEmployees"];
077.            DataRow[] dr = dt.Select("Empno = " + this.lblOldEmpno.Text);
078.            if (dr.Length > 0)
079.            {
080.                dr[0].Delete();
081.            }
082.            btnClear_Click(null, null);
083. 
084.        }
085. 
086.        private void DataGridView1_Click(object sender, EventArgs e)
087.        {
088.            if (this.DataGridView1.CurrentRow != null)
089.            {
090.                this.txtEmpno.Text = this.DataGridView1.CurrentRow.Cells[0].Value.ToString();
091.                this.txtEname.Text = this.DataGridView1.CurrentRow.Cells[1].Value.ToString();
092.                this.txtSal.Text = this.DataGridView1.CurrentRow.Cells[2].Value.ToString();
093.                this.txtDeptno.Text = this.DataGridView1.CurrentRow.Cells[3].Value.ToString();
094.                this.lblOldEmpno.Text = this.txtEmpno.Text;
095.                this.btnUpdate.Enabled = true;
096.                this.btnDelete.Enabled = true;
097.            }
098.        }
099. 
100.        private void btnClear_Click(object sender, EventArgs e)
101.        {
102.            this.txtEmpno.Text = string.Empty;
103.            this.txtEname.Text = string.Empty;
104.            this.txtSal.Text = string.Empty;
105.            this.txtDeptno.Text = string.Empty;
106.            this.lblOldEmpno.Text = string.Empty;
107.            this.btnUpdate.Enabled = false;
108.            this.btnDelete.Enabled = false;
109. 
110.        }
111. 
112.        private void btnSave_Click(object sender, EventArgs e)
113.        {
114.            try
115.            {
116.                using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Emp", new SqlConnection(CONNSTR)))
117.                {
118.                    SqlCommandBuilder cb = new SqlCommandBuilder(da);
119.                    da.Update(ds, "tEmployees");
120.                }
121.                MessageBox.Show("Saved to database");
122.            }
123.            catch (Exception ex)
124.            {
125.                MessageBox.Show(ex.Message);
126.            }
127. 
128.        }
129. 
130.        private void btnDispChanges_Click(object sender, EventArgs e)
131.        {
132.            if (ds.HasChanges())
133.            {
134.                DataTable dt = ds.Tables["tEmployees"];
135.                StringBuilder sb = new StringBuilder();
136.                foreach (DataRow dr in dt.GetChanges().Rows)
137.                {
138.                    switch (dr.RowState)
139.                    {
140.                        case DataRowState.Added:
141.                            sb.AppendFormat("Added: {0}, {1}, {2}, {3}", dr["Empno"], dr["Ename"], dr["Sal"], dr["Deptno"]);
142.                            break;
143.                        case DataRowState.Modified:
144.                            sb.Append("Modifed: ");
145.                            foreach (DataColumn dc in dt.Columns)
146.                            {
147.                                if (string.Compare(dr[dc.ColumnName].ToString(), dr[dc.ColumnName, DataRowVersion.Original].ToString()) != 0)
148.                                {
149.                                    sb.AppendFormat("{0}-{1}/{2}, ", dc.ColumnName, dr[dc.ColumnName, DataRowVersion.Original], dr[dc.ColumnName]);
150.                                }
151.                            }
152.                            break;
153.                        case DataRowState.Deleted:
154.                            sb.AppendFormat("Deleted: {0}", dr["Empno", DataRowVersion.Original]);
155.                            break;
156.                    }
157.                    sb.AppendLine();
158.                }
159.                MessageBox.Show(sb.ToString());
160.            }
161.            else
162.            {
163.                MessageBox.Show("No changes were made");
164.            }
165. 
166.        }
167. 
168.        private void btnDisplayErrors_Click(object sender, EventArgs e)
169.        {
170.            if (ds.HasErrors)
171.            {
172.                DataTable dt = ds.Tables["tEmployees"];
173.                StringBuilder sb = new StringBuilder();
174.                foreach (DataRow dr in dt.GetErrors())
175.                {
176.                    sb.AppendFormat("Errors of Employee row {0}: {1}", dr["Empno", DataRowVersion.Original], dr.RowError);
177.                    sb.AppendLine();
178.                }
179.                MessageBox.Show(sb.ToString());
180.            }
181.            else
182.            {
183.                MessageBox.Show("No errors exist");
184.            }
185. 
186.        }
187.    }
188.}


Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following