GUIDs and DataSource Controls
The DataSource controls built-into ASP.NET 2.0 make it much easier to bind data to a variety of controls. However, as you start to customize your applications you may experience a few issues (that are typically easy to resolve). I came across one such issue when I first used the SqlDataSource to create some admin pages awhile back. The database I was integrating with had primary keys of type UniqueIdentifier (GUID). The insert stored procedures accepted the key as an OUTPUT parameter and when I tried to insert a row an error was raised saying that an invalid cast occurred on the key parameter (Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query). At first I was a bit puzzled because the parameter I specified was of type Object (the closest thing available to GUID for asp:Parameter types) and I figured that everything would automatically be figured out behind the scenes. The StrategicPriorityID parameter shown below demonstrates how I originally defined the code in the .aspx page:
<InsertParameters>
<asp:Parameter Direction="InputOutput" Name="StrategicPriorityID" />
<asp:Parameter Name="Abbreviation" Type="String" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="FiscalYear" Type="Int32" />
<asp:Parameter Name="DateTimeStamp" Type="DateTime" />
</InsertParameters>
<asp:Parameter Direction="InputOutput" Name="StrategicPriorityID" />
<asp:Parameter Name="Abbreviation" Type="String" />
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="FiscalYear" Type="Int32" />
<asp:Parameter Name="DateTimeStamp" Type="DateTime" />
</InsertParameters>
After a little debugging I realized that I had to explicitly assign the parameter's type to a GUID in order for things to work properly. You can't do this with the <asp:Parameter> tag, but you can tie into the SqlDataSource control's Inserting event and access the parameters collection. This event is raised right before the insert operation is performed.
protected void sdsPriority_Inserting(object sender,
SqlDataSourceCommandEventArgs e) {
SqlParameter insertedKey =
e.Command.Parameters["@StrategicPriorityID"] as SqlParameter;
insertedKey.SqlDbType = SqlDbType.UniqueIdentifier;
SetDateTimeParamValue(e);
}
private void SetDateTimeParamValue(SqlDataSourceCommandEventArgs e)
{
SqlParameter date =
e.Command.Parameters["@DateTimeStamp"] as SqlParameter;
date.Value = DateTime.Now;
}
SqlDataSourceCommandEventArgs e) {
SqlParameter insertedKey =
e.Command.Parameters["@StrategicPriorityID"] as SqlParameter;
insertedKey.SqlDbType = SqlDbType.UniqueIdentifier;
SetDateTimeParamValue(e);
}
private void SetDateTimeParamValue(SqlDataSourceCommandEventArgs e)
{
SqlParameter date =
e.Command.Parameters["@DateTimeStamp"] as SqlParameter;
date.Value = DateTime.Now;
}
Looking at the code shown above you can see that I first access the parameters collection to get to the appropriate parameter using the Command property available on the SqlDataSourceCommandEventArgs object. The parameter will have the "@" character automatically added in front of its name . Once the parameter is found I then set the SqlDbType to a GUID. This code also shows how a DateTimeStamp parameter can dynamically be assigned the current date and time in cases where the stored procedure expects the value to be passed into it. The key to fixing this issue and many others that are associated with DataSource controls is to handle the appropriate event. You can do just about anything you need to using events.
My good friend Michael Palermo recently posted another good tip for working with DataSource controls that deals with accessing the underlying data source of the ObjectDataSource control. You can view it here.