In this Article, we will see an example which pulls data from the database and displays that in the Dotnet Windows Form using ADO DataTable, DataAdapter and DataGridControl. The control at the top of the form is DataGridControl. We will use this control to display the data in the DataTable. In this example, we are going to load the data to the DataTable through DataAdapter and then going to display it in the DataGridControl. This action happens twice in this example and one is at the form load and another one is at the Reload button click.
In this example, we placed a multi-line text box control in the top portion of the sample application (Marked as 1) and this control shows the data returned by executing the stored procedure. The clear button (Marked as 2) will clear the current content in the multi-line text box. The button marked as 3 in the example executes the stored procedure listed in listing 1.1. Button marked as 4 is used to execute the stored procedure which accepts parameters (Listing 1.2). Two text box items (Marked as 4 and 5) supply the parameters for the stored procedures in listing 1.2 and 1.3. The button marked as 7 in our example executes the stored procedure that has a return statement and accepts output parameters (Listing 1.3). The value received is shown in the labels marked as 8 and 9. That is all about this example application and you can see the video at the end of this article to see how it works.
The item marked as 1 is a multi-line text-box control used to show the table schema shown in section 1 of this example. The Get Table Schema button (Marked as 2) displays the schema data of the discount table in the multi-line text box. Before using this example, one need to setup the connection string to the Pubs database. Setting the connection string for the NorthWnd database is shown in the below video. We can follow the same method to set the connection string for the Pubs database as the table DISCOUNTS resides in it. When you create Connection String name it as PUBSDB.
In this Example, we will see packing more than one SQL statement in a SqlCommand and process it through the SqlDataReader object. From the past articles on ado.net we are already familiar with Connection, Command and Reader objects. Hence, we will focus on dealing with multiple result sets.
The example retrieves the data from the SQL Server sample database Pubs. A total number of authors queried from the table authors is displayed in a label control marked as 1 and author name from the same table is displayed in the combo box item marked as 2. The list box marked as 3 displays all store names by querying the table stores from the Pubs database. When we click the Get Data button (Marked as 4), the example fetches all the data through a single SqlCommand formed by three SQL statements.
To fetch data from the database, we need DB connection. In ADO.Net, based on the Database Provider, there are distinct connection classes. For example, to hook up to an Oracle Database we need to use OracleConnection and in the same way we need SqlConnection to hook up to the SQL Server database. The Command Object is also database specific. For Example, SqlCommand object can talk with SQL Server DB through SqlConnection object and OracleCommand object can talks to the Oracle DB through OracleConnection. These command objects know what needs to be executed in the database. In this article, we will study how to use SqlCommand and its methods ExecuteScalar, ExecuteReader and ExecuteNonQuery.