CLR - Oledb (continued)

[stanl]

The code relates to the .accdb I uploaded in the previous post on this topic. Removed the lines associated with connection builder as it was not needed. Tried to write out the schema as xml. The script works (I attached both the tables and columns xml), but the output was only the schema of the schema, not the actual tables or columns in oProvider.  Instead of WriteXmlSchema(cXML), perhaps I should have used just WriteXml() but that method is over-loaded [i.e. over my head] and didn't appear to have a filepath parameter. Trying to replicate the getschema() as done with COM that returns an xml recordset. As of my current reading, doing things that are simple in COM, like moving a recordset into Excel require an MVP in .NET - even Powershell has custom cmdlets that are hairy.

Code (WINBATCH) Select Expand


;Winbatch 2013 - CLR - OLEDB {baby steps}
;
;
;Stan Littlefield July 7, 2013
;
;Updated July 10,2013 : added code to send getschema() results to xml
;////////////////////////////////////////////////////////////////////////////////////////////////////////
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
;cXML = Dirscript():"tables.xml" ;use for tables schema
cXML = Dirscript():"columns.xml"
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;'
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()
oTables = ObjectClrNew("System.Data.DataTable")
oTables = oProvider.GetSchema("Columns")
oTables.WriteXmlSchema(cXML)
oTables = 0
oProvider.Dispose()
oProvider=0
Exit



[stanl]

My bad - if you do modify
;oTables.WriteXmlSchema(cXML) as
oTables.WriteXml(cXML)
then you do get the xml in a simple format (not rs:xml). And, as opposed to COM, it will overwrite the file if it already exists rather than error.