user1697111 user1697111 - 1 year ago 158 Question

DataGridView to XML with preserved datatypes

I am trying to save and load XML string from and to a DataGridView.

With following code I can do that but datatypes of columns is lost and whole grid is then filled with only strings. In this situation formatting and sorting don't work good anymore.

Is here something I can do to keep original datatypes during showed proccess?

I would like solution which woud be usable for my other projects to.

Imports System
Imports System.Text
Imports System.Xml
Imports System.IO

Public Class Form1

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

dgv.Columns.Add("col0", "col0")
dgv.Columns(0).ValueType = Type.GetType("Integer")
dgv.Columns.Add("col1", "col1")
dgv.Columns.Add("col2", "col2")
dgv.Columns(2).ValueType = Type.GetType("Double")
dgv.Columns(2).DefaultCellStyle.Format = "N2"

dgv.Rows.Add({CInt("1"), "John", CDbl("0,11")})
dgv.Rows.Add({CInt("2"), "Mary", CDbl("2,8")})
dgv.Rows.Add({CInt("3"), "Mike", CDbl("10,125")})
dgv.Rows.Add({CInt("4"), "Suzy", CDbl("2")})
End Sub

Private Sub dgv_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgv.Leave

If dgv.Rows.Count - 1 > 0 Then
Dim dsa As DataSet = New DataSet()
dsa.DataSetName = "xdgv"
Dim dxs As DataTable = GetDataTableFromDGV(dgv)
''save xml from dgv content to textbox
TextBox1.Text = GenerateXML(dsa)
End If
End Sub

Private Function GenerateXML(ByVal xds As DataSet) As String

Dim obj As New StringWriterUtf8()
Dim xmlstring As String
xds.WriteXml(obj, XmlWriteMode.IgnoreSchema)
xmlstring = obj.ToString()

Return xmlstring
End Function

Private Function GetDataTableFromDGV(ByVal dgv As DataGridView) As DataTable

Dim dt = New DataTable()

Dim t As Integer = 0
For Each column As DataGridViewColumn In dgv.Columns
If column.Visible Then
t += 1
End If

Dim cellValues As Object() = New Object(dgv.Columns.Count - 1) {}
For Each row As DataGridViewRow In dgv.Rows
If Not row.IsNewRow Then
For i As Integer = 0 To row.Cells.Count - 1
cellValues(i) = If(row.Cells(i).Value Is Nothing, String.Empty, row.Cells(i).Value)
End If

Return dt
End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

''load xml from textbox back to dgv

If Not String.IsNullOrEmpty(TextBox1.Text) Then
Dim Stream As StringReader = New StringReader(TextBox1.Text)
Dim xreader As XmlTextReader = New XmlTextReader(Stream)
Dim ds As DataSet = New DataSet
For Each table As DataTable In ds.Tables
For Each row As DataRow In table.Rows
Next row
Next table
ds = Nothing
Catch ex As Exception
MessageBox.Show("xmlFoo: " + ex.Message)
End Try
End If
End Sub
End Class

Public Class StringWriterUtf8
Inherits System.IO.StringWriter
Public Overrides ReadOnly Property Encoding() As Encoding
Return Encoding.UTF8
End Get
End Property
End Class

For sucessfully running this code you would need new project with Form1 on it which contains DataGridView="dgv", Text box="TextBox1" and Button="Button1".

Answer Source

Here's a new solution which will require some reworking on your behalf. Instead of populating the datagridview, populate a datatable and use this to do the binding and serialization.

Here is some example code which shows the table being created, bounding to the gridview, serialized and deserialized with its types intact.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim dt As New DataTable("Src")
    dt.Columns.Add("col0", GetType(Integer))
    dt.Columns.Add("col1", GetType(String))
    dt.Columns.Add("col2", GetType(Double))

    dt.Rows.Add({CInt("1"), "John", CDbl("0,11")})
    dt.Rows.Add({CInt("2"), "Mary", CDbl("2,8")})
    dt.Rows.Add({CInt("3"), "Mike", CDbl("10,125")})
    dt.Rows.Add({CInt("4"), "Suzy", CDbl("2")})

    dgv.DataSource = dt
    dgv.Columns(2).DefaultCellStyle.Format = "N2"

    Dim file = "c:\temp\test.xml"
    dt.WriteXml(file, XmlWriteMode.WriteSchema)

    Dim dt2 = New DataTable

    dgv.DataSource = dt2
End Sub

Your modified GenerateXml function:

Private Function GenerateXML(ByVal dt As DataTable) As String
    Dim obj As New StringWriterUtf8()
    Dim xmlstring As String
    dt.WriteXml(obj, XmlWriteMode.WriteSchema)
    xmlstring = obj.ToString()
    TextBox1.Text = xmlstring
    Return xmlstring
End Function

Independent Schemas

    Dim file = "c:\temp\test.xml"
    Dim schema = "c:\temp\test.xsd"
    dt.WriteXml(file, XmlWriteMode.IgnoreSchema)

    Dim dt2 = New DataTable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download