C# SqlDataAdapter Example

Use SqlDataAdapter with SQL to populate a DataTable for use in Windows Forms.

SqlDataAdapter interacts with the DataTable type. It can fill a DataTable with a table from your SQL Server database. Here we see a quick example. We then review important members (methods, events and properties) on SqlDataAdapter.

Example. This is an example of the SqlDataAdapter code. You will need to change the SELECT command to use a table from your custom database. You may also need to add a DataGridView to your Windows Forms program.

Note: SqlDataAdapter doesn't require the DataGridView. However, DataGridViews are a common usage.

Part 1: This creates a new SqlConnection instance. Note that you must include the System.Data.SqlClient namespace in your program.

Part 2: We use another using block. The using statements are ideal for disposing of resources, making your programs more efficient and reliable.

SqlClient

Part 3: We instantiate and Fill a new DataTable. The Fill method will populate the internal rows and columns of the DataTable to match the SQL result.

Part 4: This part is commented out. The DataSource is assigned to the DataTable. The result will be a filled DataGridView with data from SQL Server.

C# program that uses SqlDataAdapter type using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); FillData(); } void FillData() { // 1 // Open connection using (SqlConnection c = new SqlConnection( Properties.Settings.Default.DataConnectionString)) { c.Open(); // 2 // Create new DataAdapter using (SqlDataAdapter a = new SqlDataAdapter( "SELECT * FROM EmployeeIDs", c)) { // 3 // Use DataAdapter to fill DataTable DataTable t = new DataTable(); a.Fill(t); // 4 // Render data onto the screen // dataGridView1.DataSource = t; // <-- From your designer } } } } }

Fill. Fill is the most important method on the DataAdapter objects. It executes the query and fills the DataAdapter object with the results. There are several events, methods and properties that are used with Fill.

OnFillError: This is an event that allows you to listen to when an error occurred when filling the DataTable.

GetFillParameters: This allows you to get the parameters that are being used in a SELECT statement.

Options. LoadOption is an enumeration of the options you can use when the DataAdapter is loaded. You can specify OverwriteChanges, PreserveChanges and Upsert. You can easily insert or upload with the same query.

AcceptChangesDuringUpdate: You code can use the AcceptChanges method when you want to accept all changes made to the row.

Properties. Here are my notes when researching SqlDataAdapter in my work. Note that Dispose and Finalize aren't really useful with the using-statement. They are included in this section for completeness.

OnRowUpdated and OnRowUpdating are events. They allow you to receive messages when the specified actions occur. These are not covered in depth on this page. But they are useful when you must be alerted to changes.

Dispose, Finalize. The Dispose and Finalize methods are available because SqlDataAdapter uses important system resources. You should use it with using. When you use using, you don't need to call these methods.Using

Internals. My biggest point of confusion here was the extent of the internal machinery in the SqlClient code. The internal implementation of these ADO.NET providers is very extensive. It does most of the work.

SqlCommandBuilder. SqlDataAdapater can push even more of the error-prone coding into the ADO.NET methods when you also use SqlCommandBuilder. Read the article on this site about SqlCommandBuilder, which features DataTable and SqlDataAdapter.SqlCommandBuilder

Summary. We used SqlDataAdapter, part of the System.Data.SqlClient namespace. There are many members of SqlDataAdapter. We touched on SqlCeDataAdapter, which is similar. This pattern of code is useful in many data-driven applications.