How to Insert the XML File values to DataBase:
The .Net technology is widely supported XML file format. The .Net Framework provides the Classes for read, write, and other operations in XML formatted files. Moreover the Dataset in ADO.NET uses XML format as its internal storage format.
Here we are going to insert the values of an XML file to a Database Table using SQL insert command. Here the Dataset using an XmlReader for read the content of the XML file - Product.XML . Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset. Also establish a connection to the Database using a constring . After getting the data from XML file to the Dataset , we can loop through the dataset values and use insert command to add the values to the Product table in the DataBse.
There are Various Steps:
-
Design a New window Form
-
Create Data base in Ms Access
-
Create Xml File
<?xml version="1.0" encoding="utf-8" ?>
<Table>
<User>
<UseName> Ram</UseName>
<UserPassword> 12345</UserPassword>
</User>
<User>
<UseName> Shyam</UseName>
<UserPassword> 12345</UserPassword>
</User>
<User>
<UseName>Anil</UseName>
<UserPassword> 12345</UserPassword>
</User>
<User>
<UseName> Mohan</UseName>
<UserPassword> 45</UserPassword>
</User>
<User>
<UseName> Sachin</UseName>
<UserPassword> 345</UserPassword>
</User>
</Table>
-
To save Xml file in to Bin>Deb>Product.xml.
-
coding for window form
Module1.vb
Imports System.Data.OleDb
Module Module1
Public constring As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\My_db6.accdb")
End Module
Form1.vb
Imports System.Xml
Imports System.Data.OleDb
Public Class Form1
Dim Ds As DataSet
Dim SqlString As String
Dim Da As OleDbDataAdapter
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim command As OleDbCommand
Dim adpter As New OleDbDataAdapter
Dim ds As New DataSet
Dim xmlFile As XmlReader
Dim sql As String
Dim UserName As String
Dim UserPassowrd As Integer
Try
xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings())
ds.ReadXml(xmlFile)
Dim i As Integer
constring.Open()
For i = 0 To ds.Tables(0).Rows.Count - 1
UserName = ds.Tables(0).Rows(i).Item(0)
UserPassowrd = Convert.ToDouble(ds.Tables(0).Rows(i).Item(1))
sql = "insert into Info2 values('" & UserName & "','" & UserPassowrd & "')"
command = New OleDbCommand(sql, constring)
adpter.InsertCommand = command
adpter.InsertCommand.ExecuteNonQuery()
Next
constring.Close()
MessageBox.Show("xml file values successfuly inserted")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
constring.Open()
Ds = New DataSet
SqlString = "select * from info2"
Da = New OleDbDataAdapter(SqlString, constring)
Da.Fill(Ds, "info2")
DataGridView1.DataSource = Ds.Tables(0)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Output:
