Programming the ASP.NET 2.0 SqlDataSource Control - How did we obtain the rows?
(Page 4 of 4 )
To explain how we obtained the values of each of the returned rows I will remove the code that creates the HTML table's cells and rows from the above code. It looks like this:
if (this.SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataReader)
{
foreach (DbDataRecord record in iteratorObject)
{
for (int i = 0; i < record.FieldCount; i++)
{
string myField = record[i].ToString();
}
}
}
else if(this.SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataSet)
{
foreach (DataRowView record in iteratorObject)
{
for (int i = 0; i < record.Row.Table.Columns.Count; i++ )
{
string myField = record[i].ToString();
}
}
}
In the first foreach statement we get access to each row through an object of type DbDataRecord. The code we have written is flexible because we depend on the DbDataRecord.FieldCount property to tell us how many fields this row contains, and then using a for statement we access and retrieve the value of each field, through the indexer on the DbDataRecord, by passing the variable i to the indexer.
In the second foreach statement we have different logic to use. The for statement uses the DataRowView.Row.Table.Columns.Count property. In short, this property contains the number of columns of the table that this DataView object represents, so now we know how many fields we should access through this property.
Inside the for statement we have the same line of code as in the first foreach statement that accesses the value of the field through the indexer. This time the indexer works on another object which is a DataRowView object. Using this technique we can execute a T-SQL SELECT statement that selects any number of columns and still be able to retrieve them all.
We can also access the fields by name but we have to modify the code. Note in this case we must know in advance how many fields are returned because we will access the fields by their names as shown in the next alternative code sample.
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource SqlDataSource1 = new SqlDataSource();
this.Controls.Add(SqlDataSource1);
SqlDataSource1.ConnectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
SqlDataSource1.SelectCommand = "SELECT EmployeeID, FirstName,
LastName, Title FROM Employees";
IEnumerable iteratorObject = SqlDataSource1.Select
(DataSourceSelectArguments.Empty);
HtmlTable table = new HtmlTable();
if (SqlDataSource1.DataSourceMode ==
SqlDataSourceMode.DataReader)
{
foreach (DbDataRecord record in iteratorObject)
{
HtmlTableRow row = new HtmlTableRow();
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["EmployeeID"].ToString() +
"</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["FirstName"].ToString() +
"</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["LastName"].ToString() + "</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["Title"].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();
HtmlTableCell cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["EmployeeID"].ToString() +
"</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["FirstName"].ToString() +
"</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["LastName"].ToString() + "</b>";
row.Cells.Add(cell);
cell = new HtmlTableCell();
cell.Width = "60";
cell.InnerHtml = "<b>" + record["Title"].ToString() + "</b>";
row.Cells.Add(cell);
table.Rows.Add(row);
}
}
this.Controls.Add(table);
}
In the next article, we are going to perform update operations using the SqlDataSource control programmatically, and then in the next few articles we are going to use Data-Bound Controls to complete all these operations with "almost" no code.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 12 
