Database Connection in .NET

We use Access as database in our sample code. If you use other dababase, the only difference is the database string. Both imports are needed in your aspx code.

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>

Sample I. open and close database, directly use SQL to read data.

-------------------------------------

	string strResultsHolder = "";	
	string strSQL = "SELECT employID, firstnamel Lastname FROM employee";

	String strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
	strConnection += @"Data Source=c:\employeeDB.mdb";
	OleDbConnection objConnection = new OleDbConnection(strConnection);

	OleDbCommand objCommand = new OleDbCommand(strSQL, objConnection);
	OleDbDataReader objDataReader = null;//dataReader hold the data
	
	objConnection.Open();
	objDataReader = objCommand.ExecuteReader();//read data to DataReader
	while (objDataReader.Read() == true)
	{
		strResultsHolder += objDataReader["firstname"];
		strResultsHolder += "&nbsp;";
		strResultsHolder += objDataReader["lastname"];
		strResultsHolder += "<br/>";
	}
	
	objDataReader.Close(); 
	objConnection.Close();
	
	//Another way, use datagrid control to hold data, no connection Close is needed
	//datagrid.DataSource = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
	//datagrid.DataBind();

---------------------------
- Use symbol @, we do not need use escape charactor '\'.

 

Sample II. open database, read data in DataSet, close database. You can freely use this disconnected dataset.

--------------------------------------------

	string strSQL = "SELECT firstname,lastname FROM employee";

	String strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
	strConnection += @"Data Source=c:\employeeDB.mdb";
	OleDbConnection objConnection = new OleDbConnection(strConnection);
    
	DataSet objDataSet = new DataSet();
	OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, objConnection);
	
	//fetch the data to dataset, and give it a name we will use later
	objAdapter.Fill(objDataSet, "emp");		

	//retrieve data from dataset, objDataSet.Tables["emp"], 
	//copy to datagrid control
	datagrid.DataSource=objDataSet.Tables["emp"].DefaultView;
	datagrid.DataBind();

--------------------------------------------