Accessing the Database
Now switch to Default.aspx.cs
Before we go any further, let’s make sure our Default.aspx.cs has this line included on top:
//We explicitly include System.Data.OleDb because, by default, ASP.NET assume we are going to be using SQL Server connection. In this case we are using Microsoft Access so we have to including the proper classes and libraries.
We are going to write the below code in Default.aspx.cs page within the page_load event.
//First let’s create a Connection to the Microsoft Access Database
string sqlConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=|DataDirectory|\\sampleDB.mdb";
//Now let’s set a variable strSQL to what we want to select from the database table
string strSQL = "select * from tblCustomer";
//Following line sets “conn" variable equal to OleDBConnection object by passing the connection string sqlConnectionString we created above
OleDbConnection conn = new OleDbConnection(sqlConnectionString);
//Create a command object for the query
OleDbCommand cmd = new OleDbCommand(strSQL,conn);
//Now open the Database Connection before populating your DataReader
//Show connection status to the user. lblStatus is the label we created in Default.aspx page
lblStatus.Text = "Status: <em>Connection Open</em>";
//Create and Populate the DataReader
OleDbDataReader reader = cmd.ExecuteReader();
//Read through the records in the database. We are using a “while" loop here.
string strCust = reader["fldName"].ToString();
//Please note, we are showing all of the customers in label lblCustomer
lblCustomer.Text += reader["fldID"].ToString() +": "+strCust + "<br>";
//Close the DataReader
//Close the Connection to the database
That is all. As you can see, it is very easy to run SQL script within your ASP.NET application. Please see the “Sample Output" screen shot.
Please click here to download the sample project for this article.