Simple ADO.NET program with Visual Studio .NET IDE

In this article I will explain about a simple ADO.NET program with VS using windows forms.



Simple ADO.NET program with Visual Studio .NET IDE

You just saw an example of working with an access 2000 database in my previous article. For variety you'll see one example of a SQL server database. In this example, you'll take a different approach. Instead of writing a console-based application, this time you'll create a windows forms application and use the VS .NET IDE.

This sample application uses the SQL server Puran database. You read data from the Student table.

Begin your first ADO.NET application by launching VS.NET and creating a new project using File > New > Project. Choose the C# Windows Application template shown in Figure and type in your project's name.

project.gif

Figure: Creating a new C# windows application

Displaying data in a DataGrid control is the simplest task. In this sample, you'll show the Student table data in a DataGrid control.

Clicking OK bring up the blank Form View. Drag a DataGrid control from the Toolbox > Window forms onto the form and size it. Also drag a button onto the form and assign it a text property of fill (see figure below).

form.gif

Figure: Windows form controls for your first ADO .NET application

Now you are ready to write some ADO.NET code. In the first example you'll fill your DataGrid using the SQL data provider. This is certainly the most efficient choice of data providers for talking to a SQL server database. First you need to make sure you've imported all the proper namespaces for using ADO.NET and this particular data provider this is code you'll need at the top of the form code to bring in ADO.NET and the SQL server data provider


using
System.Data;   // dataset and related classes
using
System.Data.SqlClient;   // sql server data provider classes

Next, double-click on the Fill button to create the code for the event handler for this button. You'll implement your ADO.NET routine inside this method. In order to retrieve data form the database, you first need to connect to the database using a Connection object. The code in listing below sets up the Connection object to the Puran database. The server in this example is local host.

Listing: Constructing the Sql Server connection object


private
void button1_Click(object sender, EventArgs e)
        {
           
//Create a Connection object
            string ConnectionString = "initial catalog=puran; data source=MEHRAS\\SQLSERVER2005; integrated security=sspi";
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConnectionString;
        }


Note: If you using a remote server, than you need to pass the server name, user ID and password in the connection string.

We have not yet connection to the database. This is done through the SqlDataAdapter object. The DataAdapter is the bridge between the DataSource (sql) and the DataSet (memory). The DataAdapter will be constructed with two elements in this example: a SQL SELECT command to tell the DataAdapter which data to extract into the Dataset and the Connection object to tell the DataAdapter how to connect into the data source. In this example, you select all the date from the Student table. The DataAdapter is constructed below in listing. As you can see from listing the sql string is a SELECT statement.

Listing: Constructing the Data Adapter

// Creating a SQL string and data adapter object

            string sql = "select * from Student";
            SqlDataAdapter myAdapter = new SqlDataAdapter(sql, myConnection);


Now you are ready to use the DataAdapter to transfer the desired data from the Student table into a DataSet. To transfer the data, simply construct the DataSet and call the Fill method of the DataAdapter on the DataSet to fill it with the Student table.

See Listing.

Listing: Constructing the Dataset and filling it

// Creating a SQL string and data adapter object
            string sql = "select * from Student";
            SqlDataAdapter myAdapter = new SqlDataAdapter(sql, myConnection);
           
// Construct the dataset and fill it
            DataSet myDataSet = new DataSet("Student");
            myAdapter.Fill(myDataSet, "Student");


Finally, you want to display the data from the Student table to the DataGrid control. You can do this simply binding the DataSet to the Data Grid through its DefaultViewManager by using DataGrid's DataSource property. You just set DataSource property of the Dataset's DefaultViewManager. For example:

// Bind the List box to the Dataset
            dataGrid1.DataSource = myDataSet.DefaultViewManager;


The final listing of the event handler method looks like listing below.

Listing: Binding the Dataset to the DataGrid Control

using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using System.Drawing;

using
System.Linq;
using
System.Text;
using
System.Windows.Forms;
using
System.Data.SqlClient;

namespace
Ado_tesst1
{
    public partial class Form1 :
Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
           
//Create a Connection object
            string ConnectionString = "initial catalog=puran; data source=MEHRAS\\SQLSERVER2005; integrated security=sspi";
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConnectionString;
           
// Creating a SQL string and data adapter object
            string sql = "select * from Student";
            SqlDataAdapter myAdapter = new SqlDataAdapter(sql, myConnection);
           
// Construct the dataset and fill it
            DataSet myDataSet = new DataSet("Student");
            myAdapter.Fill(myDataSet, "Student");
           
// Bind the List box to the Dataset
            dataGrid1.DataSource = myDataSet.DefaultViewManager;
        }
    }
}


Now compile and run the project. Click the Fill button. The output of the program looks like figure below.

output.gif

Figure: Output of ADO .NET application in a Data Grid control

CAUTION: Make sure your SQL server is up and running. If SQL server is not running you'll get an exception look like figure below.

Figure-3.18.gif

Figure: Exception when SQL server is not running

Conclusion

Hope this article would have helped you in understanding writing a Simple ADO.NET program with Visual Studio .NET IDE. See my other articles on the website on ADO.NET.