You want to see an example of SqlDataAdapter and its usage in the C# language. This is to fill a DataTable with a table from your SQL Server database. Here we see a quick example, and a table reviewing various SqlDataAdapter members.
First, 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 very common usage.
--- Program that uses SqlDataAdapter type (C#) ---
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication9
{
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.
Here are the author's notes when researching SqlDataAdapter in his 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. [DataAdapter DataTable and DataColumn Mappings (ADO.NET) - MSDN] 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. [DataRow.AcceptChanges Method - MSDN] 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. [Component Class (System.ComponentModel) - MSDN] 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. All methods All properties All fields 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.
The author's 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.
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.
See SqlCommandBuilder Example.
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.