.NET Applications
XML Web Services
SQL Server Database
Web Traffic Statistics
Much More... |
| |
|
| Windows
2003 Server
2.4 GHz Pentium 4
1024 MB RAM
80 GB Hard Drive
1000 GB/month
Fully Managed
Free Setup!
$268.00/month |
| |
|
10 or more Domains
Windows Services
Custom Plans |
| |
|
|
|
More ASP.NET Tutorials
Also see: Binding a Datareader to a Datagrid using SQL Server and Visual Basic.NET.
First, Import the System.Data.SqlClient namespace which gives us access to the classes necessary to retrieve data from a SQL Server database.
| using System.Data.SqlClient; |
Next define the connection string and SQL query. For this example, we will be connecting to the Northwind database on the local SQL Server instance. Northwind is a sample database that ships with SQL Server.
string connString = "Server=localhost;Database=Northwind;Integrated Security=SSPI";
string sql = "SELECT * FROM Products"; |
Based on the two strings above, create a SqlConnection and SqlCommand object. These will be used to connect to the database and subsequently issue a command to retrieve data from the Products table in the Northwind database.
SqlConnection conn = new SqlConnection( connString );
SqlCommand command = new SqlCommand( sql, conn ); |
A DataReader can then be created by calling the ExecuteReader method of the SqlCommand object. Of course, we must open the connection first before we can execute any commands.
|
conn.Open();
SqlDataReader reader = command.ExecuteReader();
|
The last step is to assign the DataReader object to the DataSource property of the DataGrid and then call the DataBind method.
|
this.DataGrid1.DataSource = reader;
this.DataGrid1.DataBind();
conn.Close();
|
The complete Page_Load event of the Web form should look like this:
|
private void Page_Load(object sender, System.EventArgs e)
{
string connString = "Server=localhost;Database=Northwind;Integrated Security=SSPI";
string sql = "SELECT * FROM Products";
SqlConnection conn = new SqlConnection( connString );
SqlCommand command = new SqlCommand( sql, conn );
conn.Open();
SqlDataReader reader = command.ExecuteReader();
this.DataGrid1.DataSource = reader;
this.DataGrid1.DataBind();
conn.Close();
}
|
When things go wrong
We can solidify our code above by using structured exception handling which will enable us to catch any errors raised. For instance, consider what would happen if the SQL command fails. An exception would be thrown and the connection would never be closed as any statements after command.ExecuteReader() will not be executed.
|
string connString = "Server=localhost;Database=Northwind;Integrated Security=SSPI";
string sql = "SELECT * FROM Products";
SqlConnection conn = new SqlConnection( connString );
SqlCommand command = new SqlCommand( sql, conn );
try
{
conn.Open();
SqlDataReader reader = command.ExecuteReader();
this.DataGrid1.DataSource = reader;
this.DataGrid1.DataBind();
}
catch ( Exception ex )
{
// exception handling code goes here
}
finally
{
conn.Close();
}
|
If an exception occurs within code inside a try block, execution will jump to the catch clause. Here, we can do something simple such as redirecting to an error page or something more complicated such as emailing the administrator that an error has occurred (or most likely a combination of both). It all depends on what you want your application to do when it encounters an error. The finally clause executes unconditionally whether an exception happens or not, hence the connection is always closed. |
Do you have an ASP.NET Tutorial you would like to see here? Contact
Us!
|
|