SQLXML = Disappointed Geoff
The most common way I hit a database from ADO.net is to call a storedprocedure, get back a SQLDataReader, and use that to fill some custom I've written. Sometime after that, it's quite common for this object to get XMLserialized.
What if, I thought to myself, the stored procedure actually returned me a nice set of xml that I can use to deserialize my object, instead of calling methods on the SQLDataReader to set each private member of the class?
It's an interesting idea, filled with lots of issues, performance problems especially. Ignoring all practicalities, I thought I'd have a little play.
The way I see it, there are three main options:
- Can SQL 2000 return me directly the XML I desire, in the shape that matches the serialized form of my object, and can it be easily done based on an XML schema, rather than hand crafting the shaping of the recordset?
- Can Yukon do it better?
- What about converting a recordset clientside?
Well, sadly, I know very little about XML in SQL 2000. I know even less about it in Yukon, except that I know xml support has been greatly improved. So client side is where I decided to look.
The first thing I thought of was the SqlXml library in .net. I've never played with it before, so I had a look.
I'm sad.
Sad and extremely disappointed.
I would have thought that since it's sql server specific, that it would just be a part of the System.Data.SqlClient namespace. Nope.
I would have thought that it would work in much the same way. Nope.
Using the normal SqlClient namespace, I can write code like this:
Dim oConnection As New SqlConnection(sConnectionString)
Dim oCommand As SqlCommand = oConnection.CreateCommand
Dim oReader As SqlDataReader
oConnection.Open()
oCommand.CommandText = "prSomeProc"
oCommand.CommandType = CommandType.StoredProcedure
oCommand.Parameters.Add("@SomeParam", SqlDbType.NVarChar, 50).Value = sSomeValue
oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection)
This is pretty nice. I have a connection, a command object, and some parameters. I tell it that it's running a stored procedure, and all is good.
In SqlXml, it's nowhere near this nice. My first try was like this:
Dim oCommand As New SqlXmlCommand(sConnectionString)
Dim oParam As SqlXmlParameter
Dim oReader As System.Xml.XmlReader
oCommand.CommandText = "prSomeProc"
oCommand.CommandType = SqlXmlCommandType.Sql
oCommand.ClientSideXml = True
oParam = oCommand.CreateParameter
oParam.Name = "@SomeParam"
oParam.Value = sSomeValue
oReader = oCommand.ExecuteXmlReader
This looks fairly similar to my earlier example, but it has some differences. There's no way of specifying that it's a stored procedure being called. There's no parameters collection - you can only ask it for a new one. You can't specify the data type of the parameter.
And does it work? Nope.
When I ran it it complained that the stored procedure required a parameter '@SomeParam'. Umm, excuse me? I supplied it, thankyou very much.
It ends up that even though I've added the parameter, it's not applied because the SqlXml engine is too dumb to realise that they're needed. I had to modify it to this:
Dim oCommand As New SqlXmlCommand(sConnectionString)
Dim oParam As SqlXmlParameter
Dim oReader As System.Xml.XmlReader
oCommand.CommandText = "exec prSomeProc @SomeParam=?"
oCommand.CommandType = SqlXmlCommandType.Sql
oCommand.ClientSideXml = True
oParam = oCommand.CreateParameter
oParam.Name = "@SomeParam"
oParam.Value = sSomeValue
oReader = oCommand.ExecuteXmlReader
I actually had to specify in the command text that a parameter was part of it.
And so did it now run? Nope. The problem here is that the SqlXml library, who's whole purpose of existence is to manipulate recordsets into XML, didn't want to run because I hadn't told it to actually create some XML.
Here's the final version:
Dim oCommand As New SqlXmlCommand(sConnectionString)
Dim oParam As SqlXmlParameter
Dim oReader As System.Xml.XmlReader
oCommand.CommandText = "EXEC prSomeProc @SomeParam=? FOR XML RAW"
oCommand.CommandType = SqlXmlCommandType.Sql
oCommand.ClientSideXml = True
oParam = oCommand.CreateParameter
oParam.Name = "@SomeParam"
oParam.Value = sSomeValue
oReader = oCommand.ExecuteXmlReader
So this actually worked. I could use the returned XmlReader and have a look at the XML. The next step was to see if I could get the XML generated to match a specific schema - say, the schema that matches the XML output of the XMLSerializer when I serialize the appropriate custom object involved.
This is where I stopped. You can specify a schema, and even a transform, but only by providing the file location of the .xsd and .xslt files. You can't pass it anything that is currently in memory.
This is utter bullshit. Specifying the commandtext is extremely limited. Parameters are inflexible. Manipulation is limited to the file system.
Go to hell SqlXml, I say, go directly to hell, do not pass go, do not collect $200.