Save Dock/Grid xml layout to SQL db - Best Practices

6 posts, 0 answers
  1. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 26 Apr 2019 Link to this post

    Is there a best practices method to saving a dock or grid layout to a SQL server 2016 column, instead of a file?  And should the column be xml type?

    I have seen two methods discussed by Telerik.  This first one creates an xml string with a utf-16 header.  Eg: <?xml version="1.0" encoding="utf-16"?>.  When 'Inserted Into' the db, and error results - XML parsing: line 1, character 39, unable to switch the encoding.

    Dim wr As StringWriter = New StringWriter()
    radDock1.SaveToXml(wr)
    Dim layout As String = wr.ToString() 

     

    The second method creates an xml string with a utf-8 header, which can be 'inserted into' the sql db without error.

    Using ms As MemoryStream = New MemoryStream()
        radGridView1.SaveLayout(ms)
        Dim layout As String = Encoding.ASCII.GetString(ms.GetBuffer(), 0, CInt(ms.Length))
    End Using

     

    Overall what is the best method/technique for saving this xml data to a sql server db?  Thanks.

  2. Ринат
    Ринат avatar
    1 posts
    Member since:
    Apr 2019

    Posted 27 Apr 2019 in reply to Brendan Link to this post

    This method is good enough,y u require alternative to it
  3. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3759 posts

    Posted 29 Apr 2019 Link to this post

    Hello, guys,     

    The layout of RadDock and RadGridView is saved to an XML file. If you need to store it to a SQL server, you can simply store the XML file to the server. 

    After some research in general programming forums, I have found the following threads that I believe they will be helpful for achieving your goal: 
    https://stackoverflow.com/questions/704064/what-is-the-best-way-to-save-xml-data-to-sql-server
    https://docs.microsoft.com/en-us/sql/relational-databases/import-export/examples-of-bulk-import-and-export-of-xml-documents-sql-server?view=sql-server-2017
    https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server?view=sql-server-2017

    I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  4. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 29 Apr 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Thanks Dess.  What I was getting at is the two methods I showed that were suggested by Telerik create either a utf-8 or utf-16 xml header, so I was wondering what is the recommended way of going about saving to an xml column in sql?  Does the file stream always need to be utf-8 to insert correctly?  If so, the stringwriter method seems to be incorrect.
  5. Dess | Tech Support Engineer, Sr.
    Admin
    Dess | Tech Support Engineer, Sr.  avatar
    3759 posts

    Posted 30 Apr 2019 Link to this post

    Hello, Brendan,     

    Feel free to save the layout to an XML file as it is demonstrated in the following help article: https://docs.telerik.com/devtools/winforms/controls/gridview/save-and-load-layout/save-and-load-layout

    According to stack overflow accepted answer, you can't directly import utf-8 into sql server, also MSDN states "SQL Server does not support code page 65001 (UTF-8 encoding)." Maybe you could translate the file to UTF-16. Please refer to the following thread which is quite useful on this matter: https://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server

    I hope this information helps.

    Regards,
    Dess | Tech Support Engineer, Sr.
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  6. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 30 Apr 2019 in reply to Dess | Tech Support Engineer, Sr. Link to this post

    Thanks Dess.  Here is what I ended up doing in the event others want to do the same by storing the layout data in a sql db instead of a file.

    '---abbreviated code---
    'save GRID layout record
    Dim sXmlToSave As String = ""
    Using MS As MemoryStream = New MemoryStream()
      myGrid.SaveLayout(MS)
      sXmlToSave = Encoding.ASCII.GetString(MS.GetBuffer(), 0, CInt(MS.Length))
    End Using
    sSql = "Insert Into UserXml Values (N" & sXmlToSave & ")"     'save to SQL xml field type.  Need to prefix with 'N' for utf-16
     
    'retrieve setting
    sXml2Save= "Select xmlvalue From UserXml Where " etc...
    Using XR As XmlReader = XmlReader.Create(New System.IO.StringReader(sXmlToSave))
       myGrid.LoadLayout(XR)
    End Using
     
    '----  Dock Layout Code ----
    'save dock layout record
    Using SR As StringWriter = New StringWriter()
      myDock.SaveToXml(SR)
      sXmlToSave = SR.ToString()
    End Using
    sSql = "Insert Into UserXml Values (N" & sXmlToSave & ")"     'save to SQL xml field type.  Need to prefix with 'N' for utf-16
     
    'retrieve setting
    sXml2Save= "Select xmlvalue From UserXml Where " etc...
    Using SR As StringReader = New StringReader(sXmlToSave)
      myDock.LoadFromXml(SR)
    End Using
Back to Top