In many scenarios developers would like to combine the great simplicity of Asp.Net 2.0 declarative databinding with the same level of control over data as they get with good old traditional databinding. Sometimes the developers need to manipulate on the data in the datasource and thus to access it.
If the data come from a SQL Server database, you will use the SqlDataSource control. Setting the DataSourceMode property to DataSet will result in creating a DataSet object in the server memory. The object will be loaded with the data after running the Select method. The Select method returns a DataView populated with the rows returned from the database. Having obtained the DataView, you can easily convert it to a DataTable with the DataView.ToTable method.
In the case of declarative databinding the Select method is called automatically by controls that are bound to the SqlDataSource and it is not obvious where is your chance to catch the datasource, convert it to a DataTable and do something on it.
If you need to access the dataset prior to databinding, you don’t have any choice but to call the Select manually, which contradicts the whole idea of declarative databinding. Too bad.
In some scenarios however you can first databind and then operate on the DataTable. One example is when you need to save the table for postbacks. In this case you can catch the table in the ItemDataBound or RowDataBound event of the control bound to the SqlDataSource. The event reaches the DataView via the DataItem property exposed by the data items:
bool firstTime = true;
System.Data.DataTable dt;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
if (this.firstTime)
{
System.Data.DataView dv =
(e.Row.DataItem as System.Data.DataRowView).DataView;
this.dt = dv.ToTable();
this.Label1.Text = dt.Rows.Count.ToString();
this.firstTime = false;
}
}
I found this remarkable info in a newsgroup post from David Sceppa from Microsoft:
You can build a SqlCommand to query the table and retrieve this schema information from the SqlDataReader through its GetSchemaTable method. This method returns a DataTable of schema information. Each row in the DataTable corresponds to a column in the resultset for the original query. The DataTable of schema information includes columns for column name, data type, etc. There's a column called ProviderType that contains an integer, which you can cast to SqlDbType.
Here's some code that should help:
Visual Basic .NET:
Dim strConn, strSQL As String
strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _
"Trusted_Connection=Yes;"
Dim cn As New SqlConnection(strConn)
cn.Open()
strSQL = "SELECT OrderDate FROM Orders"
Dim cmd As New SqlCommand(strSQL, cn)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim tbl As DataTable = rdr.GetSchemaTable()
rdr.Close()
cn.Close()
Dim intOrderDateType As Integer
intOrderDateType = CType(tbl.Rows(0)("ProviderType"), Integer)
Dim typOrderDateType As SqlDbType
typOrderDateType = CType(intOrderDateType, SqlDbType)
Console.WriteLine("ProviderType = " & intOrderDateType)
Console.WriteLine("SqlDbType = " & typOrderDateType.ToString())
Visual C# .NET:
string strConn, strSQL;
strConn = @"Data Source=(local)\NetSDK;Initial Catalog=Northwind;" +
"Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
strSQL = "SELECT OrderDate FROM Orders";
SqlCommand cmd = new SqlCommand(strSQL, cn);
SqlDataReader rdr;
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable tbl = rdr.GetSchemaTable();
rdr.Close();
cn.Close();
int intOrderDateType;
intOrderDateType = (int) tbl.Rows[0]["ProviderType"];
SqlDbType typOrderDateType;
typOrderDateType = (SqlDbType) intOrderDateType;
Console.WriteLine("ProviderType = " + intOrderDateType);
Console.WriteLine("SqlDbType = " + typOrderDateType.ToString());
For years OWC (stands for Office Web Components) has been serving to web developers as a power graphic engine. Free for non-interactive scenarios, it provides Excel-style charts.
Now it is on the way out. OWC is being deprecated and will not receive any functionality updates (http://blogs.msdn.com/excel/archive/2006/07/17/668544.aspx). Instead Microsoft will be offering a technology called Excel Services which is a part of Microsoft Office SharePoint Server 2007. It has been designed to scale and perform on a server. But it as not going to be a replacement for OWC from the functionality perspective and it is not free anymore.
Although technically nothing stop me from continuing using OWC in new projects and recommend it to others, I don't feel comfortable relying on a deprecated technology. Looks like I will rather need to shop for a third party component.
The purpose of this blog is to share tips, tricks, patterns, thoughts on all aspects of asp.net development.
Stay tuned!