C# SqlDataAdapter

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.

Program that uses SqlDataAdapter type: C#

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
		}
	    }
	}
    }
}

Part 1. This creates a new SqlConnection instance. Note that you must include the System.Data.SqlClient namespace in your program. The using directive is at the top of the program.

Next: In part 2, after calling Open() on the SqlConnection, we use another using block for the SqlDataAdapters.

Tip: The using statements are ideal for disposing of resources, making your programs more efficient and reliable.

In part 3, we instantiate and Fill a new DataTable. Please note that the Fill method on DataTable will populate the internal rows and columns of the DataTable to match the SQL result.

Part 4. This part is commented out because it won't compile unless you have a DataGridView. We see the DataSource being assigned to the DataTable. The result will be a filled DataGridView with data from SQL Server.

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.

TableMappings, HasTableMappings, DataTableMappingCollection, DataTableMapping, MissingMappingOption. DataTableMappings are useful for when you need define reusable mappings between database columns are your data objects.

Note: This is outside the scope of this article.
Please see MSDN for more details.

CanRaiseEvents, Container, DesignMode, Events, Site. These properties are all inherited from the Component class in .NET. Component "enables object sharing between applications" and is useful for very large projects.

DeleteCommand, InsertCommand, SelectCommand, UpdateCommand. DeleteCommand "Gets or sets a Transact-SQL statement or stored procedure to delete records from the data set." This property allows you to encapsulate the DELETE command.

And: InsertCommand and SelectCommand provide the same benefit for INSERT and SELECT.

AddToBatch, ClearBatch, ExecuteBatch, GetBatchedRecordsAffected, GetBatchedParameter, InitializeBatching, TerminateBatching, UpdateBatchSize. These methods would be useful for batch processing, with large series of data.

Similar: SqlCeDataAdapter is essentially the same as SqlAdapter. Other DataAdapters in the .NET Framework are similar.

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.

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.

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.