C# Datset with Sql Server Data Provider

The DataSet in ADO.NET acts as a memory-resident representation of data retrieved from a data source. It serves as a container that holds a copy of the data based on the SQL statement or query executed.

The DataSet provides a consistent relational programming model, irrespective of the underlying data source. This means that you can interact with the data in a uniform manner regardless of whether it originated from a SQL Server database, an OLEDB source, or any other supported data provider.

SqlDataAdapter Class

When working with the DataSet, you can use it in conjunction with the SqlDataAdapter Class. The SqlDataAdapter facilitates the communication between the data source and the DataSet.

Full Source C#
using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection connection ; SqlCommand command ; 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 = "Your SQL Statement Here"; connection = new SqlConnection(connetionString); try { connection.Open(); command = new SqlCommand(sql, connection); adapter.SelectCommand = command; adapter.Fill(ds); adapter.Dispose(); command.Dispose(); connection.Close(); 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]); } } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

To populate the DataTables within the DataSet with data from the data source, you can utilize the SqlDataAdapter. It provides methods such as Fill() that retrieve data from the data source and populate the DataTables within the DataSet.

The Fill() method of the SqlDataAdapter retrieves the data from the data source based on the provided SQL statement or query and fills the corresponding DataTables in the DataSet.

Conclusion

The DataSet serves as a memory-resident representation of data, providing a consistent programming model regardless of the data source. The SqlDataAdapter enables the population of DataTables within the DataSet with data from the data source. This combination allows for seamless data retrieval and manipulation using ADO.NET in your C# applications.