Goodmorning everyone, I am not satisfied with this solution, can you suggest something better ?
Code:
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim databaseName As String = "F:\Documents\Visual Studio 2015\Projects\xml.mdb"
Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName)
Dim ds As New Data.DataSet
ds.ReadXml("F:\Documenti\Visual Studio 2015\Projects\test.xml")
Dim dstable As DataTable = ds.Tables("Mytable")
DataGridView1.DataSource = dstable
' ----- Create Access Database file using ADOX
Dim cat As ADOX.Catalog = New ADOX.Catalog()
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseName & ";Jet OLEDB:Engine Type=5")
MessageBox.Show("Database Created Successfully")
con.Open()
Dim sql As String = "Create Table tab1 ("
For Each column As DataColumn In dstable.Columns
sql += "[" + column.ColumnName + "] " + "nvarchar(" + column.ToString.Length.ToString + ")" + "," ' <<< not very good
Next
sql = sql.TrimEnd(New Char() {","c}) + ")"
Dim cmd As New OleDb.OleDbCommand(sql, con)
Dim da As New OleDbDataAdapter(cmd)
cmd.ExecuteNonQuery()
Using adapter = New OleDbDataAdapter("SELECT * FROM tab1", con)
Using builder = New OleDbCommandBuilder(adapter)
adapter.InsertCommand = builder.GetInsertCommand()
adapter.Update(dstable)
End Using
End Using
con.Close()
MessageBox.Show("Table Created Successfully")
End Sub