Programming the ASP.NET 2.0 SqlDataSource Control - Using the SqlDataSourceMode enumeration
(Page 3 of 4 )
Using the SqlDataSourceMode enumeration makes a huge difference for us. If your SqlDataSource control is used to retrieve data for display purposes only, you should set the SqlDataSource.DataSourceMode property to SqlDataSourceMode.DataReader. If you need to retrieve data and perform operations such as updating, deleting, sorting and filtering then you would set the SqlDataSource.DataSourceMode property to SqlDataSourceMode.DataSet.
Take a look at our code again.
if (SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataReader)
{
foreach (DbDataRecord record in iteratorObject)
{
HtmlTableRow row = new HtmlTableRow();
for (int i = 0; i < record.FieldCount; i++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record[i].ToString() + "</b>";
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
}
else if (SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataSet)
{
foreach (DataRowView record in iteratorObject)
{
HtmlTableRow row = new HtmlTableRow();
for (int i = 0; i < record.Row.Table.Columns.Count; i++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record[i].ToString() + "</b>";
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
}
The if statement evaluates to true if the SqlDataSource1.DataSourceMode is equal to SqlDataSourceMode.DataReader. If it is, we iterate through the returned Data Reader object's collection of DbDataRecord objects. The returned DbDataRecord objects represents the table's rows that are returned using a Data Reader object. The else if statement evaluates to true if the SqlDataSource1.DataSourceMode is equal to SqlDataSourceMode.DataSet, which executes a different code because the returned object, in this case, is a DataView object and its collection of the database table's rows are not the same as the DataReader object's collection of database table's rows.
The DataView object has a collection of DataRowView that represents the returned rows. Note that both the DataReader and the DataView implement the IEnumerable interface and that's why we can enumerate through its collection of rows. Also note that the DataReader returned, if the SqlDataSourceMode.DataReader is used, depends on the ADO.NET Data Provider used. By default the SQL Server Data Provider is used and this means that a SqlDataReader object is the return type of the SqlDataSource.Select() method.
If we don't test for the value of the SqlDataSource.DataSourceMode property we would get an exception, shown in the next screenshot. If the SqlDataSource.DataSourceMode is set to SqlDataSourceMode.DataReader our code implements logic to manipulate a returned DataView object as shown next.
SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader;
foreach (DataRowView record in iteratorObject)
{
HtmlTableRow row = new HtmlTableRow();
for (int i = 0; i < record.Row.Table.Columns.Count; i++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record[i].ToString() + "</b>";
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
The exception is shown in the following screenshot.
And the same InvalidCastException is raised in the opposite situation, where you will have code that manipulates the DataReader object but the returned object is DataView as shown next.
foreach (DbDataRecord record in iteratorObject)
{
HtmlTableRow row = new HtmlTableRow();
for (int i = 0; i < record.FieldCount; i++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record[i].ToString() + "</b>";
row.Cells.Add(cell);
}
table.Rows.Add(row);
}
The default value for the SqlDataSource.DataSourceMode is DataSet so if you tried to execute the above code without the if statement you would get the exception shown in the next screenshot.
Next: How did we obtain the rows? >>
More ASP.NET Articles
More By Michael Youssef
/ 12 
