ADO.net
- DataSet vs. DataReader
When a database query is being executed, DataReader reads the rows one by one and populates the result, let's say on the web page making the request.
Important feature: Data is displayed as soon as partial results become available.
-Works as read-only. Can't update the DB.
-Always maintains connection.
DataSet [foreach(...)]
-Important feature: Data is not displayed until the whole result of the query is received. Once we get the whole result, we can start displaying the rows of data.
-Shouldn't be considered as a local copy of a DB. Can hold data from different tables and relate them.
-Enables operations like binding.
-Provides flexibility to create a datatable on the memory, on-the-fly.
DataAdapter
is used to retrieve data from a DB. Can also update the DB back.
- Examples: (DataSet File name: DataSet1.xsd)
DataReader example:
private void Query_Click(object sender, EventArgs e)
{
// NO SqlDataAdapter IS NEEDED!!!
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "Data Source=hc\\sqlexpress; integrated security=SSPI; Initial Catalog=ContructionProject";
objConn.Open();
SqlCommand objCmd = new SqlCommand("myStoredP_1", objConn);
objCmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader myreader = objCmd.ExecuteReader();
while (myreader.Read())
{
listBox1.Items.Add(myreader[0].ToString()+" " + myreader[1].ToString());
}
objConn.Close();
}
DataSet example with Fill() method:
private void myDataSet_Query_Click(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection();
objConn.ConnectionString = "Data Source=hc\\sqlexpress; integrated security=SSPI; Initial Catalog=ContructionProject";
objConn.Open();
SqlCommand objCmd = new SqlCommand("myStoredP_1", objConn);
objCmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataAdapter a = new SqlDataAdapter(objCmd);
DataSet1 s = new DataSet1();
a.Fill(s, s.myStoredP_1.TableName);
foreach (DataSet1.myStoredP_1Row dr in s.myStoredP_1.Rows)
{
MessageBox.Show(dr.FName.ToString());
}
//foreach (DataRow dr in s.Tables[0].Rows)
//{
// MessageBox.Show(dr[1].ToString() + dr[0].ToString());
//} /*This foreach loop works as well*/
objConn.Close();
}
DataSet example with GetData() method:
private void DataSet_GetData_Click(object sender, EventArgs e)
{
// NO NEED TO USE SQLCOMMAND OR CONNECTION STRING!!
DataSet1 dseg = new DataSet1();
DataSet1TableAdapters.myStoredP_1TableAdapter egAdap = new DataSet1TableAdapters.myStoredP_1TableAdapter();
DataSet1.myStoredP_1DataTable dt = egAdap.GetData();
foreach (DataSet1.myStoredP_1Row dr in dt.Rows)
{
listBox1.Items.Add(dr.ID + " " + dr.FName);
}
}
* ADO.net does not support bulk inserts to the DB!
* System.Data namespaces: SqlClient, OleDb, ODBC, OracleClient
* Use of ODBC is discouraged because it is slow, unsecure and has limited functionality.
* Oracle: No multiple SELECT statements!
