Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Have you ever wanted to work with your Xml files as if they were databases?  Would you like to use SQL Select statements, instead of XPath, to retrieve, filter, and sort your Xml data?  What about using SQL Insert, Update, and Delete statements, in transactions, against your Xml data?  OK, I haven't either, but I had someone request the ability for my WilsonORMapper, and all that's necessary is an ADO.NET provider that knows how to work with Xml.  I like a good challenge, and couldn't find one already done, so I took a little of my spare time and created just such an ADO.NET provider that anyone can use.

So I'm hereby announcing the open-source release of v1.0 of the WilsonXmlDbClient.  The WilsonXmlDbClient is an ADO.NET provider that enables Xml to be worked with just like any other database in .NET. It supports the most common Select, Insert, Update, and Delete SQL syntax, as well as tranactions and parameters.  It works with your own ADO.NET data access code, as well as the WilsonORMapper, and it most likely will work with other ORMappers with very few changes to those mappers.  I was rejected by SourceForge, unlike my WilsonWebForm, for some reason they didn't specify, so this is on GotDotNet.

Your Xml file must be in a format that the .NET DataSet can read, and you must include a schema if you want strongly typed data and/or identity fields.  Its easy enough to create a DataSet and then write it out as Xml, so I'm not releasing a tool to do this for you.  The following SQL syntax is supported:

SELECT *|fieldList FROM tableName [WHERE whereClause] [ORDER BY sortClause] [LIMIT pageSize] [OFFSET skipRows]
SELECT @@Identity
INSERT [INTO] tableName (fieldList) VALUES (valueList)
UPDATE tableName SET updateList [WHERE whereClause]
DELETE [FROM] tableName [WHERE whereClause]

Fields and tables can optionally be delimited by [ and ], parameters must start with @, and you can optionally use ; for the statement terminator.  No group by, sql functions, table joins, or multiple recordsets are currently supported, and you cannot use the DataAdapter for persistence (use the Sql commands instead).  Also, note that my Sql parsing is pretty "raw" (maybe someone can join the workspace and fix this), so its certainly possible that things can go wrong if your data contains things I'm parsing.  It should also go without saying that this probably isn't going to work very well in a multi-user environment.

You can certainly use this ADO.NET provider in your own code, just like any other provider, but if you do want to use it with my ORMapper, then here's the CustomProvider syntax:

Wilson.ORMapper.CustomProvider customProvider = new Wilson.ORMapper.CustomProvider(
 "WilsonXmlDbClient", "Wilson.XmlDbClient.XmlDbConnection", "Wilson.XmlDbClient.XmlDbDataAdapter");
customProvider.StartDelimiter = "[";
customProvider.EndDelimiter = "]";
customProvider.IdentityQuery = "SELECT @@Identity;";
customProvider.SelectPageQuery = "SELECT * LIMIT {0} OFFSET {1}";

Published Saturday, September 18, 2004 11:43 AM by PaulWilson


# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Paul, you rock. I bow down to you.

Saturday, September 18, 2004 3:38 PM by css

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

I'm not worthy.


Saturday, September 18, 2004 8:06 PM by Wallym

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Paul.. I want to give you a big kiss here and now!

Sunday, September 19, 2004 4:24 PM by Mikael Söderström

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Hmmm, I think I'd prefer something else. :)

Sunday, September 19, 2004 5:06 PM by Paul Wilson

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Paul... Have you thought about incorporating this into the Data Access Block? The 3.* version on GotDotNet has a provider model which backends to Access, Sql Server, Oracle and more.. This would be a sweet addition to it..

Sunday, September 19, 2004 10:50 PM by Anil John

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Hi Anil:

I have no problem if someone else wants to add it to the MS DAAB, but I don't personally like the MS DAAB (too complicated) and I already have my own cross-database DAL that it works with as is (my WilsonORMapper). :)

Thanks, Paul

Monday, September 20, 2004 5:30 AM by Paul Wilson

# re: Open-Source ADO.NET XML Provider -- WilsonXmlDbClient v1.0

Could someone post a sample code, please? I open a xml file but no content will be displayed. I just recieve FieldCount, RecordsAffected, IsClosed, Depth and HasRows.

Monday, October 18, 2004 5:30 AM by Mick