C# ADO.NET SqlDataAdapter

The SqlDataAdapter Class is indeed part of the C# ADO.NET Data Provider and is found within the System.Data.SqlClient namespace. It facilitates communication between the Dataset and the SQL database.

SqlDataAdapter Object

When working with the SqlDataAdapter, it is typically used in combination with the Dataset Object. The DataAdapter provides a mapping mechanism through its Fill() method, which synchronizes the data in the Dataset to match the data in the SQL database. Conversely, the Update() method of the DataAdapter updates the data in the SQL database to match the data in the Dataset. This bidirectional synchronization ensures consistency between the Dataset and the SQL database.

SqlDataAdapter adapter = new SqlDataAdapter(); adapter.Fill(ds);

The combination of the SqlDataAdapter Object and the Dataset Object enables both data access and data manipulation operations within the SQL Server Database. The DataAdapter's Fill() method is responsible for retrieving data from the SQL database and populating the Dataset with the retrieved data. This allows for efficient data access and retrieval.

When the user performs SQL operations like Select or Insert on the data contained in the Dataset Object, it does not directly affect the underlying SQL Server Database. The changes made within the Dataset are isolated until the user explicitly invokes the Update() method on the SqlDataAdapter. This update operation synchronizes the changes made in the Dataset with the corresponding data in the SQL Server Database, ensuring that modifications are applied appropriately.

Full Source C#
using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection sqlCnn ; SqlCommand sqlCmd ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string sql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; sql = "Select * from product"; sqlCnn = new SqlConnection(connetionString); try { sqlCnn.Open(); sqlCmd = new SqlCommand(sql, sqlCnn); adapter.SelectCommand = sqlCmd; adapter.Fill(ds); for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[0] + " -- " + ds.Tables[0].Rows[i].ItemArray[1]); } adapter.Dispose(); sqlCmd.Dispose(); sqlCnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

Conclusion

The SqlDataAdapter Class in C# ADO.NET Data Provider, residing in the System.Data.SqlClient namespace, facilitates communication between the Dataset and the SQL database. The Fill() method maps the data from the SQL database to the Dataset, while the Update() method synchronizes changes from the Dataset back to the SQL database. This combination enables both data access and data manipulation capabilities, ensuring consistent and efficient interaction between the application and the SQL Server Database.