ADO.net - DataSet vs. DataReader

ADO.net

• DataSet vs. DataReader

DataReader [while(r.Read())]

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!