ADO.NET

DataAdapter Example in ADO.NET

This article has been excerpted from the book "A Programmer's Guide to ADO.NET in C#".
 
Now you'll create your first sample using data adapters. In this sample example, I'll show you how to create data adapters using Sql and OleDb data providers and fill data from the data adapter to a DataGrid control.
 
First, create a Windows application using Visual C# projects and add two buttons and a DataGrid control to the form by dragging the controls from the toolbox to the form. Second, set both button's Name property; use OleDbDataAdapter and SqlDataAdapter. Next set the properties to OleDbData Adapter and SQL DataAdapter. After setting these properties, the form will look like figure 5-40. As you can see, there are two buttons, OleDbDataAdapter and SQL DataAdapter.
 
Now add button-click event handlers for both the OleDbDataAdapter and SQL DataAdapter buttons. You can add a button-click event handler either by double-clicking on the button or by using the Events tab of the Properties window of the button control. On the OleDb DataAdapter button-click event handler, you'll write code to read data from an OleDb data source and fill data to the data grid. On the SQL DataAdapter button-click event handler, you'll write code to read data from a SQL Server datasource and fill data to the data grid.
 
Figure-5.40.jpg
 
Figure 5-40. Creating a Windows Forms application and adding controls to the form
 
 Listing 5-44 shows the source code for the OleDb DataAdapter button click, and Listing 5-44 shows the source code for the SQL DataAdapter button click, and Listing 5-45 shows the source code for the SQL DataAdatper button click. As you can see, you follow the same steps as before. Open a connection, create a data adapter object with a SELECT string, create a dataset object, call the data adapter's FILL method to fill the dataset, and bind the dataset to the DataGrid.DataSource property as DataSet.DefaultViewManager, which represents the default view of a DataSet object.
 
Listing 5-44. Displaying the Order table data in a Data Grid
  1. using OleDbDataAdapter;  
  2.   
  3. private void OleDbDataAdapter_Click(object sender, System.Event Args e) {  
  4.         //Create a connection object  
  5.         string ConnectionString = @ "provider=Microsoft.Jet.OLEDB.4.0;" +  
  6.                 "Data Source= C:/northwind.mdb";  
  7.         string SQL = "SELECT * FROM Orders";  
  8.         OleDbConnection conn = new OleDbConnection(ConnectionString);  
  9.   
  10.         // open the connection  
  11.         conn.Open();  
  12.   
  13.         // Create an OleDbDataAdapter object  
  14.         OleDbDataAdapter adapter = new OleDbDataAdapter();  
  15.         adapter.SelectCommand = new OleDbCommand(SQL, conn);  
  16.   
  17.         // Create Data Set object  
  18.         DataSet ds = new DataSet("orders");  
  19.         // Call DataAdapter's Fill method to fill data from the  
  20.         // DataAdapter to the DataSet  
  21.         adapter.Fill(ds);  
  22.   
  23.         // Bind dataset to a DataGrid control  
  24.         dataGrid1.DataSource = ds.DefaultViewManager;  
  25. } 
The output of Listing 5-44 looks like figure 5-41.
 
Figure-5.41.jpg
 
Figure 5-41. Filling data from an Access database to a DataGrid control using OleDbDataAdapter
 
The output of the listing looks like figure 5-42.
 
Figure-5.42.jpg
 
Figure 5-42. Filling data from a SQL Server database to a DataGrid control using SqlDataAdapter
 
 Listing 5-45. Displaying the Customers tables data in a DataGrid
  1. using SqlDataAdapter;  
  2.   
  3. private void SqlDataAdapter_Click(object sender, System.EventArgs e) {  
  4.         string ConnectionString = "Integrated Security = SSPI;" +  
  5.                 "Initial catalog = Northwind;" + " Data Source =MAIN-SERVER; ";  
  6.         string SQL = "SELECT CustomerID, CompanyName FROM Customers";  
  7.         SqlConnection conn = new SqlConnection(ConnectionString);  
  8.   
  9.         // open the connection  
  10.         conn.Open();  
  11.   
  12.         //Create a SqlDataAdapter object  
  13.         SqlDataAdapter adapter = new SqlDataAdapter(SQL, conn);  
  14.   
  15.         // Call DataAdapter's Fill method to fill data from the  
  16.         // Data Adapter to the DataSet  
  17.         DataSet ds = new DataSet("Customers");  
  18.         adapter.Fill(ds);  
  19.         // Bind data set to a DataGrid control  
  20.         dataGrid1.DataSource = ds.DefaultViewManager;  
  21. } 

Conclusion

 
 Hope this article would have helped you in understanding DataAdapter Example in ADO.NET. See my other articles on the website on ADO.NET.
 
adobook.jpg This essential guide to Microsoft's ADO.NET overviews C# then leads you toward a deeper understanding of ADO.NET.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.
    Membership not found