I’ve been doing some proof-of-concept testing for a web service that we’ll probably be using as the middle-tier of a web application we’re developing. When the web service creates the proxy class in Visual Studio, the web service class properties are all created as public fields instead of public properties. For the most part, who cares, right?
For some reason, the ASP .NET list controls (RadioButtonList and DropDownList) cannot find public fields when attempting to databind to an array of objects. Databinding to a DataGrid or DataRepeater doesn’t really offer much of a difficulty, but it really sucks not being able to bind directly to a list control.
Jan Tielens has show one possible way to overcome this problem. His solution compiles a new assembly in memory, at runtime, which “wraps” the proxy classes in a container class with public properties instead of fields. This is a fairly interesting solution, and taught me a lot about using reflection, but ultimately it failed as a viable solution for our needs.
One of the requirements of our project is that Option Strict be used—since Jan’s method effectively creates a class at runtime that doesn’t exist at design time, there’s no way (that I know of) to create an explicit reference to one of the wrapper classes because they don’t exist yet. This leads to compiler errors and such.
All was not lost however. I decided to use what I had learned from Jan Tielens to dynamically convert an array of classes into a bindable object to use as a datasource for my list controls.
Here is my first attempt:
' Specify the property name of a class, and this function will iterate through an array of said
' classes, creating an arraylist of property or field values.
'
Public Shared Function ArrayToArrayList(ByVal Arr As System.Array, _
ByVal PropertyOrFieldName As String) As ArrayList
Dim ret As New ArrayList
Dim T As Type = Arr.GetType.GetElementType
For Each o As Object In Arr
Dim PI As System.Reflection.PropertyInfo = T.GetProperty(PropertyOrFieldName)
Dim FI As System.Reflection.FieldInfo = T.GetField(PropertyOrFieldName)
If IsNothing(PI) And IsNothing(FI) Then Return Nothing
If Not IsNothing(PI) Then ret.Add(PI.GetValue(o, Nothing))
If Not IsNothing(FI) Then ret.Add(FI.GetValue(o))
Next
Return ret
End Function
There’s nothing overly difficult here. The function receives the name of the property that the developer is interested in, along with the array of objects containing the property. It finds the value of the property for each object, adds it to the arraylist, and returns the arraylist. Problem solved…
Except for one small thing. Databinding to the list controls entails more than just binding one field to the control: the list controls have a DataTextField and DataValueField properties, both of which need databinding support. What now?
At first I thought to simply extend what I had already written—but then I decided to write a new function from scratch that would return a datatable. Here’s what I came up with:
' Converts an array of like objects to a datatable object which can be bound to a list control.
Public Shared Function ArrayToDataSource(ByVal arr As System.Array, _
ByVal LC As ListControl) As DataTable
' Creates columns in the Datatable for the DataText and DataValue Fields
Dim DT As New DataTable
DT.Columns.Add(LC.DataTextField)
DT.Columns.Add(LC.DataValueField)
' Get the base type of the array.
Dim T As Type = arr.GetType.GetElementType
' For each item in the array, get the property values associated with
' specified field.
'
' Don't bother retrieving the property values if the Fields on the list control
' were left blank.
'
For Each o As Object In arr
Dim DR As DataRow = DT.NewRow
If Not LC.DataTextField.Length = 0 Then _
DR(LC.DataTextField) = GetFieldOrPropertyValue(o, LC.DataTextField)
If Not LC.DataValueField.Length = 0 Then _
DR(LC.DataValueField) = GetFieldOrPropertyValue(o, LC.DataValueField)
DT.Rows.Add(DR)
Next
Return DT
End Function
This function creates a datatable and adds two columns to it—one for the datatextfield, and one for the datavaluefield. It then loops through the array of objects and inserts the appropriate values into the table via a call to another function called GetFieldOrPropertyValue.
GetFieldOrPropertyValue receives an object and the name of some property which value you wish to retrieve. At this point I thought of still another frustration I have often had in doing databinding to list controls: I couldn’t databind to an object property of my source object. For example, suppose I had an array of classes called “Employee” with a property “ResidenceAddress” which was itself a class of type “AddressInfo”, and I was interested in binding the Employee.ResidenceAddress.State property to the list control. It seems apparent that we should be able to split the name of the property we’re interested in along the “.” Separator, and using reflection, retrieve each sub property until we reached the one we desired.
Here’s the source:
' Will get a field or property value from an object.
' The Name parameter can allude to properties of other objects which are properties of the
' source object. For instance, "Name" can be something like "Address.State". The function will
' recursively find the value of each element of the name string until it gets to the end.
'
Public Shared Function GetFieldOrPropertyValue(ByVal o As Object, ByVal Name As String) As Object
Dim PropertyNames() As String = Name.Split("."c) ' create an array of properties to look up
Dim result As Object = o ' Initialize res to be the source object
Dim PI As System.Reflection.PropertyInfo
Dim FI As System.Reflection.FieldInfo
For Each s As String In PropertyNames
' Get the type from res: this will change on each iteration.
Dim T As Type = result.GetType
PI = T.GetProperty(s)
FI = T.GetField(s)
' Set res to the value of the property we're lookin up.
' Using the above example, during the first iteration,
' res will be "Address", and the second time, it will
' be the "State".
'
If Not IsNothing(PI) Then result = PI.GetValue(result, Nothing)
If Not IsNothing(FI) Then result = FI.GetValue(result)
Next
' If we could not find a match, clear out res.
If IsNothing(PI) And IsNothing(FI) Then result = Nothing
Return result
End Function
I’m in the design phase of a new project and I’m doing proof-of-concept viability testing for several different n-tier scenarios. One of those scenarios entailed using an ASP .NET web application as the front end, making calls to a WebService as the Business rules layer, which in turn managed the conversion of SQL Server data into typed class objects. The idea here is that the Front End application should have no need to understand the underlying layout of the database.
One of the first issues I ran into is that the class objects that a web service returns to a calling app are really nothing more than glorified structures. They cannot contain methods for functions. They basically only have public fields. This may be old news to many of you, but I’m new to web services, so although it makes perfect sense in hindsight, it was quite a shocker at the beginning.
The second issue I ran into was the inability to databind a dropdownlist to an array of classes received from the webservice. I posted a question to Microsoft.public.dotnet.framework.aspnet.webservices newsgroup, and received a reply from Jan Tielens, which referred me to this article. Long story short, when the VS .NET IDE creates the webservice proxy , it creates any classes with public fields instead of public properties. In other words, given a class defined as:
Public Class Test
Public Property ThisIsATest as string
Return “This Is A Test of the Emergency Broadcast System.”
End Property
End Class
The Proxy class will contain a client-side definition such as:
Public Class Test
Public ThisIsATest as String
End Class
This wouldn’t be a problem, except that databinding a dropdownlist only works for Properties, not public fields. I wonder if any MS people could say if there is a technical reason for this, or if it’s simply an oversight?
Jan's article has a novel solution to the problem, however. It contains dll code which will receive an array of classes as an argument. It will retrieve the underlying type of the array elements, create a new proxy class which converts any public fields into public properties, and returns the new array to the calling function—all of this at runtime. From there, databinding is a cinch.
As slick as the above code is, I had to modify it somewhat to get it to work in the my web app environment. It contains a call to System.Reflection.Assembly.GetEntryAssembly which apparently only returns an assembly when the calling assembly is a windows forms application. In my case, it returned Nothing. I replaced this call with System.Reflection.Assembly.GetAssembly(typeToWrap) instead. I also rebuilt the code in VB .NET since that’s what we code in here. Jan, if you would like a version of the code in VB .NET, I would be happy to send it to you.
This was the final technical hurdle toward evaluating one possible implementation of our n-tier design. This is what I have working right now:
1) A UI page written for ASP .NET begins to load, making a call to a webservice for information to display.
2) The webservice then executes a query on the underlying database, which returns its results as an XML document.
3) The webservice then deserializes that XML document directly into a structured, hierarchical class structure, which is returned to the calling ASP .NET webpage.
4) The calling webpage then gets the array of items it wants to bind a dropdownlist to, but can’t because all of the properties have been converted to fields.
5) Instead, the code passes said array into Jan Tielen’s dll where some very interesting magic occurs.
6) Reflection is used to compile a new assembly, in memory, replacing the auto-generated fields with properties, and returning a typed array of nearly identical class objects, public fields replaced by public properties.
7) This new array is then databound to the dropdownlist easily.
How cool is that?
Thanks Jan! Your article was a great help—a fantastic piece of coding on your part! In addition, you've taught me how to dynamically add functionality to classes at runtime, and my mind is reeling with the possibilities!
I've recently begun using an XML file as a source of “instructions” for an installation program I'm building. Given that I'll be killing and rebuilding database objects, directories, files, etc., I need to be sure that the XML file fits a certain schema before I do anything. No problem--in the VS .NET IDE, right-click and select Create Schema. So far so good.
Validation of the Schema is fairly simple using the XMLValidatingReader. Again no problem.
Now I need to use XPath queries to get to various nodes in the document--problem.
When the document has a default namespace (in this case created by the VS .NET IDE), XPath queries fail. There is a workaround, and it's not very hard. Here are my comments from my code:
'
' Essentially, the issue boils down as follows:
' In order to validate the document against a schema, the document must contain a namespace.
' The VS .NET IDE automatically provides "http://tempuri.org/<<Xml File Name>>.xsd" as a default
' namespace. This is great because it makes validation a fairly simple process.
' The gotcha in all this is that the default namespace renders XPath Querying of the
' DOM nearly impossible. Since the nodes are in a namespace, XPath requires that you
' prefix the nodes you're interested in via the namespace prefix--but since this is the
' default namespace, there is no namespace prefix.
' Ths solution is to provide a namespace prefix via the XmlNameSpaceManger.
'
' This article wasn't very useful in this specific context, but it did contain some useful
' information regarding the XML specification. In short it provides the theoretical overview
' as to why this is a problem in the first place, and shows that future revisions of the
' XML specification may be more intuitive on this issue.
' http://www.topxml.com/people/bosley/defaultns.asp
'
' This article provides the specific solution to my specific problem. It shows how to work
' around the default namespace without removing it, thereby retaining the ability to validate
' the document, and the ability to query the document.
' http://weblogs.asp.net/wallen/archive/2003/04/02/4725.aspx
'
I'm sure I'll need to refer back to this code in the future, so I'll leave it here on my blog for everyone's benefit.
Chris
I'm working on an XML driven installation program that will destroy and create the stored procedures, roles, users, and logins associated wtih my SQL Server Reporting Services installation. I've created a user called ReportServer that is used by my reports to access the database. All of my report stored procedures are given a prefix “rp,” and ReportServer is granted Execute permissions on the “rp” procedures only.
One of the things I'm trying to prevent in the future is database bloat--whereby you have tables, views, etc. that are in the database and are barely used--and only then because some piece of a stored procedure somewhere was never updated to use the more modern objects instead. So my strategy with all of my database objects is to destroy and create. With stored procedures and views this is very simple as you don't have to worry about juggling the data while you do it. However, I'm also destroying my Logins and user roles and recreating them from scratch.
The problem I was having was that I couldn't use sp_droplogin to get rid of my users if that user happened to be logged in to the database at the same time. So, I called my friend Scott The SQL Guru and asked for a pointer on how to tell whether my target user was logged in or not, and how to force them out if they were logged in. He directed me to Master.dbo.sp_who, and KILL command.
I scripted sp_who out to Query Analyzer, stole the piece I needed to get the SPID for my user, kill each SPID, and was able to drop the login.
Here's the SQL:
DECLARE
@User varchar(20)
SET
@User = 'TargetUser'
IF EXISTS
(SELECT Name FROM sysusers WHERE name=@User) BEGIN
-- =============================================
-- Revoke dbaccess from User (User name in current Database, Windows User, or Sql Server login)
-- =============================================
PRINT 'Revoking Database Access for ' + @User
exec sp_revokedbaccess @name_in_db = @User
END
--
--Lookup Kill Command
--Get SPID of the user: sp_who, systems
--Kill spid
--
IF EXISTS
(SELECT name FROM Master.dbo.sysLogins WHERE name=@User) BEGIN
-- =============================================
-- Drop Sql Server login
-- =============================================
-- SQL Server Login cannot be removed if it is an active process
DECLARE @Result TABLE
(
ID
int IDENTITY,
SPID
int,
SQL
varchar(200)
)
INSERT INTO @Result (SPID, SQL)
select
SPID,
'Kill ' +
CAST(SPID as varchar(20))
from master.dbo.sysprocesses
WHERE loginame=@User
DECLARE @i int
SET @i = 1
WHILE (SELECT COUNT(*) FROM @Result) > 0 BEGIN
DECLARE @Statement varchar(500)
SET @Statement = (SELECT SQL FROM @Result WHERE ID = @i)
PRINT 'Executing: ' + @Statement
EXEC (@Statement)
DELETE FROM @Result WHERE ID = @i
SET @i = @i + 1
END
DECLARE @Err int
EXEC @Err = sp_droplogin @loginame =@User
END
GO