How to DataAdapter DataGridView - Sql Server

SqlDataAdapter plays a key role in establishing effective communication between the Dataset and the Data Source, facilitated by the SqlConnection Object. The SqlConnection Object focuses solely on establishing a connection and retrieving data, lacking information about the specific data it fetches. Similarly, the Dataset remains unaware of the Data Source from which it receives the data. As a result, it is the responsibility of the SqlDataAdapter to efficiently manage the communication between these two entities. The SqlDataAdapter utilizes the TableMapping Collections to accomplish this crucial task.

SqlDataAdapter object

To ensure seamless synchronization, the InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter object are instrumental in updating the database with the data modifications executed on a DataSet object. These properties enable efficient data manipulation and maintain consistency between the Dataset and the Data Source.

To exemplify the practical implementation of updating a Dataset through SqlDataAdapter using a DataGridView, the following C# source code demonstrates the necessary steps. To execute this code, create a new C# project, add two buttons and a DataGridView to the default Form1, and copy and paste the provided source code.

Full Source C#
using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsApplication1 { public partial class Form1 : Form { string connetionString; SqlConnection connection; SqlDataAdapter adapter; SqlCommandBuilder cmdBuilder; DataSet ds = new DataSet(); DataSet changes; string Sql; Int32 i; public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; connection = new SqlConnection(connetionString); Sql = "select * from Product"; try { connection.Open(); adapter = new SqlDataAdapter(Sql, connection); adapter.Fill(ds); connection.Close(); dataGridView1.DataSource = ds.Tables[0]; } catch (Exception ex) { MessageBox.Show (ex.ToString()); } } private void button2_Click(object sender, EventArgs e) { try { cmdBuilder = new SqlCommandBuilder(adapter); changes = ds.GetChanges(); if (changes != null) { adapter.Update(changes); } MessageBox.Show("Changes Done"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }