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.
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).
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.
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: 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.