xml vs mdb

[Ed Jobe]

I'm working on a VB.Net app and I'm still new to the framework. My app will store data that will be structured in a dataset as a table and a related table with subrecords. Thedata from these records will be modeled as custom objects. I need to persist the data but am having trouble deciding on whether to use the familiar mdb or move to xml. One consideration is that I want to write data as a record is changed, not when the program closes or by asking the user to save ALL their changes. I know that an mdb functions this way, but how about xml? I'm also thinking that a db has compression and xml does not? What are your thoughts about xml replacing an mdb as a database?

Related to this, I'm having difficulty finding an answer to this question, Is there any way to "create" an mdb using .Net? It seems that a connection requires an existing file. Once you have a connection, you can alter the db any way you want, but can you create a new one? I've developed tons of Access apps but now I want this app to stand alone from Access and not rely on it to create the db.

[mtuersley]

Hey Ed!

First, xml and .net work just like an mdb - records are saved/added/deleted as you want them to be - at edit time, by user picking button, whatever. While it can be difficult to get used to, ado.net is simpler once you drop the vb6 ado trappings =) In a nutshell, whether your backend is mdb, xml or sql, they all come in as a dataset object. Then you use the datatables, dataviews, and other data-objects to manipulate/sort/order/etc. the data. If you opt for xml, look into serialized xml files. You can find some good examples at htp://www.codeproject.com if you haven't been there, yet.

As to creating the mdb on-the-fly, I can't say. I've escaped the Access world and have only used xml, msde [not a bad idea - its free!] or full sql with .net apps. Your mention of a connection might be your problem...you'll need to create a dataset with its datatables first, then probably save/export it to an mdb format. Again, its not like ado6 where you made a connection and built the mdb through calls passing along the connection. .NET is wham-bam-we're-outta-here and all data manipulation is done in the 'virtual copy' your app is holding.

HTH,

Mike

[richard.binning]

Hey Ed!

<snip>

I wrote an app for archiving Email to a database. Creating the mdb file is possible, if you are still interested, I'll dig it up and post it. Since then I switched the output to xml. This was not .net, but it worked directly through VBA in Outlook 2000 so it shouldn't be that difficult to replicate.

RLB

[Ed Jobe]

Thanks Mike and Richard. That makes it a little clearer Mike. Even in the code samples I saw, they created the connection object first, but I guess you don't have to at all if you don't want to save your data, heh, heh. I still have questions about file size though. Maybe performance will not be an issue. I don't expect the db to be too large. Its not a buisiness app. Maybe I'll go with xml just for the sake of gaining some experience with it. Thanks. If you find your code Richard, it would be interesting to look at, even just for the sake of having it posted on the forum.

[richard.binning]

Thanks Mike and Richard...
<snip>
...If you find your code Richard, it would be interesting to look at, even just for the sake of having it posted on the forum.
:mrgreen: Found it.

I used the Scripting Runtime for FileSystemObjects to check existence of the file and then create it using SQL statements. I updated it during development to utilize Access 2000 instead of 97 so I simply commented out the Jet 3.5 code.

Here you go:



Function CreateAccessDatabase(strDBPath)
Set fso = CreateObject("Scripting.FileSystemObject")
If Not (fso.FileExists(strDBPath)) Then
Dim catNewDB, Database
Set catNewDB = CreateObject("ADOX.Catalog")
Set Database = CreateObject("ADODB.Connection")
'Check for existence of file
'catNewDB.create "Provider=Microsoft.Jet.OLEDB.3.51.;Data Source=" & strDBPath
catNewDB.create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
'Database.Open ("Provider=Microsoft.Jet.OLEDB.3.51.;Data Source=" & strDBPath)
Database.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath)
sql = "CREATE TABLE Attachments (Attach_ID COUNTER, ID_Email NUMBER, Attach1Name TEXT(250), Attach2Name TEXT(250), Attach3Name TEXT(250)," & _
"Attach4Name TEXT(250), Attach5Name TEXT(250), Attach6Name TEXT(250), Attach7Name TEXT(250), Attach8Name TEXT(250), Attach9Name TEXT(250)," & _
"Attach10Name TEXT(250), Attach11Name TEXT(250), Attach12Name TEXT(250), Attach13Name TEXT(250), Attach14Name TEXT(250), Attach15Name TEXT(250)," & _
"Attach16Name TEXT(250), Attach17Name TEXT(250), Attach18Name TEXT(250), Attach19Name TEXT(250), Attach20Name TEXT(250), Attach21Name TEXT(250))"
Set rs = Database.Execute(sql)
sql = "CREATE TABLE ProjectInformationData (Email_Id COUNTER, Name_From TEXT(250), Name_Recipient TEXT(250), Msg_Subject TEXT(250)," & _
"Msg_Body MEMO, Msg_Sent DATETIME, Msg_Received DATETIME, Attachment YESNO)"
Set rs = Database.Execute(sql)
Database.Close
Set catNewDB = Nothing
Set Database = Nothing
MsgBox "File Created"
Else
MsgBox "Database exists, will append data"
End If
Set fso = Nothing
End Function

[mtuersley]

Thanks, Ed, hopefully its a little clearer.

What I meant by the connection was you won't need it if the db doesn't exist. In ado6, you had to build the db structure at the actual db while in ado.net you build it virtually then kick it out "as" a db. Little bit different approach. If you are dealing with an existing db, then you create the connection first just like before.

Far as the size goes, you definitely want to use the serialized xml and it will be comparable to an mdb. The really nifty thing is you can compare two serialized xml files AND merge them through code. So, if you update your app then you can just send the revised xml file and have it compare/merge. I do this with an online code snippet 'engine' we use internally between techs. The techs can view online or have it installed on their laptops in cases such as they are in the field and do not have net access. In such as situation, they can still add code to their copy of the 'db' and upload it later for inclusion in the master - all without anyone intervening.

[Ed Jobe]

That sounds interesting. One of the things I want to implement is importing/exporting partial sets of data. This is a history research app and so users could do research on a subject and export that node out and share it with someone who would import that node into their db.

[mtuersley]

Check out this site for an example of the XML difference: http://apps.gotdotnet.com/xmltools/xmldiff/ There is an MS patch for it as well as sample code for implementing it and have fun =)

[Ed Jobe]

Thanks Mike. I will.