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

[Brendan]

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.

[Ринат]

This method is good enough,y u require alternative to it

[Dess | Tech Support Engineer, Principal]

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

[Brendan]

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.

[Dess | Tech Support Engineer, Principal]

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

[Brendan]

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

[IT]

Excelent Brendan !