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 a table reviewing various SqlDataAdapter members.

This C# article describes SqlDataAdapter. It uses SqlDataAdapter in a Windows Forms program.

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. Additionally, you may need a DataGridView in your Windows Forms program. Note that 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.

Part 2. After calling Open() on the SqlConnection, we use another using block for the SqlDataAdapters. The using statements are ideal for disposing of resources, making your programs more efficient and reliable.

Part 3. Here we instantiate and Fill a new DataTable. 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.

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 for completeness.

Fill. Fill is the most important method on the DataAdapter objects. It executes the query and fills the DataAdapter object with the results.

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

OnFillError. This is an event that allows you to listen to when an error occurred when filling the DataTable. Please see the Fill method.

OnRowUpdated, OnRowUpdating. These are events that allow you to receive messages when the specified actions occur.

GetFillParameters. This allows you to get the parameters that are being used in a SELECT statement. Can be useful when you need to check what columns are being queried in your database.

TableMappings, HasTableMappings, DataTableMappingCollection, DataTableMapping, MissingMappingOption. DataTableMappings are useful for when you need define reusable mappings between database columns are your data objects. This is out of the article's scope.

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

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

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 easily encapsulate the DELETE command. 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, meaning you are using large series of data.

Similar. SqlCeDataAdapter is essentially the same as SqlAdapter, which is detailed above. Other DataAdapters in .NET are also very similar and actually share implementations through inheritance sometimes.

Internals

My biggest point of confusion here was the extent of the internal machinery in the SqlClient code. The internal implementations of these ADO.NET providers is very extensive, and it does tons of work for you.

SqlConnectionBuilder

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 in a complete data-driven application.

Summary

In this article, we saw a simple example of SqlDataAdapter in the C# language. We reviewed many members of SqlDataAdapter next, and also touched on SqlCeDataAdapter, which is very similar. This pattern of code is useful in many data-driven applications.