none
XML Database in VB.NET?

    Question

  • Hi all,

    I have a program that currently uses an Access database to store a number of program settings.  The program uses ADO.Net and SQL to read from and write to the database.  The problem is, my beta testers complained about having to install the Access Database Engine just to be able to use the application.  To fix this, I would like to replace the Access database with an XML file.  Is there a way to do this and still be able to keep the same functionality as the Access database?  I am especially concerned because all the tutorials I can find only store string values and I have other settings stored that aren't strings.

    Thanks,

    Jason

    Wednesday, April 19, 2017 2:40 AM

All replies

  • Hi Jason,

    Based on your description, you want to replace the access DataBase with an XML file. How much data are you talking about? The only question is whether or not the number of records is small enough that performance will be acceptable.

    please refer to this discussionto get more detailed info.

    http://www.vbforums.com/showthread.php?829489-can-I-replace-Access-database-with-XML

    I also find this sample that you can refer to.

    https://www.codeproject.com/Articles/4917/Using-xml-files-to-replace-small-databases

    Hope it is helpful to you.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 19, 2017 9:46 AM
    Moderator
  • @Jason,

    Frank, a regular contributor of this forum has tons of samples shown here, so I find it strange that Cherry shows links to other forums. 

    I'm sure Frank will give you the links. Frank likes XML files, I only if you keep in mind the following things.

    1. Changes in the XML file are as long as the complete XML file is not saved only done in memory
    2. You can only use an XML file for single user use
    3. Take care that if you save the XML file first to set the old XML file to a temp file because otherwise if power goes down meanwhile everything is gone

    For the rest there are no differences. If you use the DataSet.WriteXML with the right options everything is then the same as with Access. Therefore you can in fact use all ADO.Net samples done with datasets.

    Not meant for it, but in fact you can see how to use it on this sample on our website. (without the temp save).

    http://www.vb-tips.com/DataSetImage.aspx


    Success
    Cor




    Wednesday, April 19, 2017 10:45 AM
  • If you use a local SQL database file instead, you can package the database engine with the application so the users won't be aware of it.

    https://msdn.microsoft.com/en-us/library/ms233763(v=vs.140).aspx

    -EDIT-

    An XML file will not "keep the same functionality" as a database.  The XML file would be a static view of the data; it would not supply auto id values, support keys or indices, or enforce any relations or constraints.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"



    Wednesday, April 19, 2017 11:57 AM
    Moderator

  • I am especially concerned because all the tutorials I can find only store string values and I have other settings stored that aren't strings.

    Jason,

    What are the other settings? If they can be represented as a string (and most things can, but it's not always a great idea) then XML would work.

    If you write/read the XML yourself then be cautious about how you handle null values. Other than that it's not hard at all. Serialization handles that for you.

    You might also consider using binary serialization - no conversions are needed then.

    In any case though, this sounds like a fairly simple class - at least from my interpretation of your description it is. Let me know what all is in there and we'll go from that. :)


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Wednesday, April 19, 2017 12:45 PM
  • If the amount of data is small and each user has their own "" then here is an example of how this might be done using XML.  It only has one setting that is numeric which is a forms location.

    Public Class Form1
    
        Dim theSettings As New SomeProgSettings(True)
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            theSettings.SetLocation(Me)
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Me.Location = theSettings.GetLocation
        End Sub
    End Class
    
    Public Class SomeProgSettings
    
        Const filename As String = "_settings.xml"
        Private path As String
        Protected fileXE As XElement
        Private Shared settingsProto As XElement = <settings>
                                                       <loc x="0" y="0"></loc>
                                                   </settings>
    
        Public Sub New(Optional settingsProtoChanged As Boolean = False)
            Me.path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
            Me.path = IO.Path.Combine(path, filename)
            Try
                If settingsProtoChanged OrElse Not IO.File.Exists(Me.path) Then
                    Me.fileXE = New XElement(settingsProto)
                    Me.SaveSettings()
                Else
                    Me.fileXE = XElement.Load(Me.path)
                End If
            Catch ex As Exception
    
            End Try
        End Sub
    
        Public Sub SaveSettings()
            Try
                Me.fileXE.Save(Me.path)
            Catch ex As Exception
    
            End Try
        End Sub
    
        Public Sub SetLocation(form As Windows.Forms.Form)
            Me.fileXE.<loc>.@x = form.Location.X.ToString
            Me.fileXE.<loc>.@y = form.Location.Y.ToString
        End Sub
    
        Public Function GetLocation() As Drawing.Point
            Dim rv As New Drawing.Point(Integer.Parse(Me.fileXE.<loc>.@x), Integer.Parse(Me.fileXE.<loc>.@y))
            Return rv
        End Function
    
    End Class
    


    "Those who use Application.DoEvents() have no idea what it does and those who know what it does never use it." - MSDN User JohnWein    Multics - An OS ahead of its time.

    Wednesday, April 19, 2017 12:47 PM

  • The XML file would be a static view of the data; it would not supply auto id values, support keys or indices, or enforce any relations or constraints.


    The first is true, the second not. A dataset uses relations and constraints.  What you mean with a static view of data is for me something I don't understand

    In my sample you can see I've used this.

    Here about constraints

    https://msdn.microsoft.com/en-us/library/st1t2c35(v=vs.110).aspx


    Success
    Cor

    Wednesday, April 19, 2017 5:12 PM
  • Frank,

    My current database has two tables with a total of 41 settings.  Most are strings , but I also have some boolean values and most of the values are initially set to Null.  I need to be able to have the application read from and write to the database just like I do with the Access one.  Is XML appropriate for my needs, or is there a better option that I should start a new thread about?

    Jason

    Wednesday, April 19, 2017 6:28 PM
  • Frank,

    My current database has two tables with a total of 41 settings.  Most are strings , but I also have some boolean values and most of the values are initially set to Null.  I need to be able to have the application read from and write to the database just like I do with the Access one.  Is XML appropriate for my needs, or is there a better option that I should start a new thread about?

    Jason

    Starting a new thread is up to you (or a moderator).

    *****

    Are there relations set with the two DataTables or are they two stand-alone tables?


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Wednesday, April 19, 2017 6:37 PM
  • They're standalone tables.  The first one contains information about users and GUI configuration.  The second one contains information about files.

    Jason

    Wednesday, April 19, 2017 6:45 PM
  • They're standalone tables.  The first one contains information about users and GUI configuration.  The second one contains information about files.

    Jason

    Then this is a candidate for what I have in mind - a small class library that uses binary serialization for persistence; a binary file rather than XML.

    I need to be able to have the application read from and write to the database just like I do with the Access one.

    The persistence will be part and parcel to how it's set up. Null and multi-line (in the case of text) are all taken care of but tell me more about what you mean please?

    Per table, you only have one single row I guess?

    Do know that this is all very specific. I can set it all up but I'll need to know the details of each and every member.

    *****

    If you're comfortable with what you have, you might set it up to use two local Datatables and then create a new DataSet. With the DataSet, you can save it to XML:

    https://msdn.microsoft.com/en-us/library/hb27k0tf(v=vs.110).aspx

    ... and then read it back in:

    https://msdn.microsoft.com/en-us/library/360dye2a(v=vs.110).aspx

    I think that uses serialization so it has all the benefits, but it's all in text rather than binary.


    "A problem well stated is a problem half solved.” - Charles F. Kettering


    Wednesday, April 19, 2017 7:27 PM