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.
5 Answers, 1 is accepted
0
Ринат
Top achievements
Rank 1
Rank 1
answered on 27 Apr 2019, 05:29 AM
This method is good enough,y u require alternative to it
0
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
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.
0
Brendan
Top achievements
Rank 1
Rank 1
answered on 29 Apr 2019, 02:09 PM
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.
0
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
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.
0
Brendan
Top achievements
Rank 1
Rank 1
answered on 30 Apr 2019, 03:43 PM
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 recordDim sXmlToSave As String = ""Using MS As MemoryStream = New MemoryStream() myGrid.SaveLayout(MS) sXmlToSave = Encoding.ASCII.GetString(MS.GetBuffer(), 0, CInt(MS.Length))End UsingsSql = "Insert Into UserXml Values (N" & sXmlToSave & ")" 'save to SQL xml field type. Need to prefix with 'N' for utf-16'retrieve settingsXml2Save= "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 recordUsing SR As StringWriter = New StringWriter() myDock.SaveToXml(SR) sXmlToSave = SR.ToString()End UsingsSql = "Insert Into UserXml Values (N" & sXmlToSave & ")" 'save to SQL xml field type. Need to prefix with 'N' for utf-16'retrieve settingsXml2Save= "Select xmlvalue From UserXml Where " etc...Using SR As StringReader = New StringReader(sXmlToSave) myDock.LoadFromXml(SR)End Using