SQL 2005/8: Xml Type - Passing sets of data
Source Code
The following post is a bit of a follow-up to the post I did last month
about TVP's in SQL 2008, what
we're going to do is send a set of data to an xml parameter (introduced in SQL 2005)
and insert it into a table. (Previously we sent a set of data using TVP's in 2008)
Like last month, we create a table, called friends:
CREATE TABLE [dbo].[friends]( [friendID] [int] IDENTITY(1,1) NOT NULL, [firstname] [varchar](50) NOT NULL, [lastname] [varchar](50) NOT NULL, CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED ( [friendID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The stored procedure is going to look something like this:
CREATE PROCEDURE [dbo].[addFriends] @friends XML AS BEGIN INSERT INTO friends(firstname, lastname) SELECT item.value('@firstname', 'varchar(50)') firstname, item.value('@lastname', 'varchar(50)') lastname FROM @friends.nodes('//friend') t (item) END
Next we're going to define an xml set named friends.xml:
<?xml version="1.0" encoding="utf-8" ?> <friends> <friend firstname="w" lastname="x" /> <friend firstname="y" lastname="z" /> </friends>
Using the XML Schema Definition Tool (Xsd.exe), we generate an xsd file (XML Schema Definition), from the command prompt run: xsd friends.xml - which outputs friends.xsd.
Next we run the command xsd friends.xsd /c, which generates a C# class called friends, which we add to our project. We then add some items to our friends class, which we serialize and send to our stored procedure, observe:
using (SqlConnection connection = new SqlConnection(@"some connectionstring")) { using (SqlCommand command = new SqlCommand("addFriends", connection) { CommandType = CommandType.StoredProcedure }) { friends f = new friends(); f.Items = new friendsFriend[] { new friendsFriend { firstname = "Julie", lastname = "Truter" }, new friendsFriend { firstname = "Roland", lastname = "Cooper" } }; StringWriter sw = new StringWriter(); XmlSerializer xml = new XmlSerializer(typeof(friends)); xml.Serialize(sw, f); connection.Open(); command.Parameters.Add("@friends", SqlDbType.Xml).Value = sw.ToString(); command.ExecuteNonQuery(); } }
We're not quite done yet though, you will notice that you can practically send any xml string to the stored procedure, without any exception being raised - which is a bit of a dodgy situation.
This is where our xsd file we generated previously comes into play, we define an xml schema collection in SQL, using the output from our xsd file:
CREATE XML SCHEMA COLLECTION friendCollection AS '<?xml version="1.0" encoding="utf-8"?> <xs:schema id="friends" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="friends" msdata:IsDataSet="true" msdata:Locale="en-US"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="friend"> <xs:complexType> <xs:attribute name="firstname" type="xs:string" /> <xs:attribute name="lastname" type="xs:string" /> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema>'
Do a slight alteration to the addFriends stored procedure (notice XML(friendCollection)). You will notice that only xml that conforms to our schema, can be passed to our stored procedure - anything else will throw an exception:
ALTER PROCEDURE [dbo].[addFriends] @friends XML(friendCollection) AS BEGIN INSERT INTO friends(firstname, lastname) SELECT item.value('@firstname', 'varchar(50)') firstname, item.value('@lastname', 'varchar(50)') lastname FROM @friends.nodes('//friend') t (item) END
Posted by - Christoff Truter
Date - 2010-02-12 19:04:23
Comments - 0
Date - 2010-02-12 19:04:23
Comments - 0
Overload Resolution of dynamic/object types
Last year sometime, I started doing some research about adding traditional overloads to PHP (you heard
me, hehe). Basically I created a mechanism that allow devs to overload methods in PHP - which
is quite interesting considering that we're talking about a loosely typed language - every
variable essentially dynamic by default, minus type hinting
Sooo I remembered that C# 4.0 added similar logic to its codebase and decided to have a look
at how C# resolves dynamic types within overloads.
Incidently the same can be said about
the object type prior to C# 4.0, for intents and purposes the dynamic type is equivalent to object. eg. substitute dynamic for object prior to C# 4.0
Dynamic types does however get resolved at runtime, eg. late-binding (resolved by what the caller sent to it)
Lets have a quick look at my concerns, consider the following snippet:
using System; namespace ConsoleApp { class Program { static void f(Int32 x) { } static void f(dynamic x) {} static void f(Int32 x, dynamic y) {} static void f(dynamic x, Int32 y) {} static void Main(string[] args) { f(10); // Works f(10, 10); // Ambiguous - obvious } } }
- Passing value 10 to method f, will resolve to (Int32 x) - we assume that (Int32 x)
defines an exclusion from (dynamic x)?
- Passing values 10, 10 to method f, won't resolve - since its impossible to resolve, both methods are equally callable.
It seems pretty straightforward and we can see some kind of pattern... or can we?
Things get interesting as soon as we add a third parameter:
static void f(Int32 x, dynamic y, Int32 z) {} static void f(dynamic x, Int32 y, dynamic z) {}
Building on our our previous assumptions, let's assume that x & z defines exclusions, by these assumptions, passing values 10, 10, 10 should resolve (Int32 x, dynamic y, Int32 z).
Instead, the compiler informs us that we made an ambiguous call - nullifying my previous assumptions.
Which brings me to what really happens
static void f(Int32 x, dynamic y, Int32 z) {} static void f(dynamic x, dynamic y, Int32 z) {}
Passing values 10, 10, 10 will resolve correctly to (Int32 x, dynamic y, Int32 z), providing further clues to internal processes.
Resolving these overloads by hand, will look something like this:
- Cancel out matching types
- Cancel out least matching types eg if x is Int32 -> (Int32 > dynamic)
- In the end if we're not left with only one resolveable method, the methods are ambiguous
At the end of the day, I can't fully agree with the resolution of dynamic/object types (regarding overloads) in C# - it is however a very safe design (good language design?).
But currently I would lean towards overload resolution like the following:
- Cancel out matching types
- Cancel out least matching types eg if x is Int32 -> (Int32 > dynamic)
- Cancel out methods containing less matching types eg. the second method only contains one definite match, but our first method contains two matches - hence more resolveable.
- In the end if we're not left with only one resolveable method, the methods are ambiguous
What do you guys and girls think? Any thoughts?
Posted by - Christoff Truter
Date - 2010-02-09 17:16:52
Comments - 0
Date - 2010-02-09 17:16:52
Comments - 0
First 11 12 13 14 15 16 17 18 19 20 Last / 42 Pages (83 Entries)