Introduction
A few months ago, i have searched on internet how to write manually a XML file in vb.net, and in each file, names after on field from database, based on the fields from SQL SERVER tables and i didn't find nothing. I needed to have control of the information wrote and to see which numerber file was sent . So, i asked a friend, which he asked a friend, which he asked onother friend and so on....and there were some good ideas, other bad.... some ideas from one, combined with some ideas from the others, and this is what i have made.
Background
For example :
I have :
Sql Server 2005 -> Table : "Sent_view"
Fields : CrtNo, Field1, Field2, Field3, Field4, Field5
Datas : 1 Field11 Field12 Field13 Field14 Field15
2 Field21 Field22 Field23 Field24 Field25
---------------------------------------------
15 Field151 Field152 Field153 Field154 Field155
Rows numbers : 15
I Need
- 15 xml files called like this : "crtNo".xml
1.xml
2.xml
------
15.xml
each in xml file having data recording to crtNo.
Using the code
The main ideas are :
- create a data set din VS based on "sent_view" table(view)
- bind to some names (Name1, Name2) the information from dataset cells
- write down the sintax for the XML file
Blocks of code should be set as style "Formatted" like this:
Dim strConnection As String = strConn
Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strConn)
Dim dataset As New DataSet
Dim strSelect As String
cn.Open()
strSelect = "SELECT * FROM sent_view"
Dim dscommand As New SqlClient.SqlDataAdapter(strSelect, cn)
dscommand.Fill(dataset, "sent_view")
Dim con As Integer
con = Me.BindingContext(dataset, "sent_view").Count
If con = 0 Then
End If
Dim i As Integer = 0
For i = 0 To dataset.Tables("sent_view").Rows.Count - 1
Dim id As Integer = Int32.Parse(dataset.Tables("sent_view").Rows(i).ItemArray(8).ToString)
Dim fis As System.IO.StreamWriter = New System.IO.StreamWriter(("c:\Files4Export\" + id.ToString + ".xml"))
' ===========================
' atach to each variable a value from dataset
Dim item_Field1 As String = dataset.Tables("sent_view").Rows(i).ItemArray(1).ToString
Dim item_Field2 As String = dataset.Tables("sent_view").Rows(i).ItemArray(2).ToString
Dim item_Field3 As String = dataset.Tables("sent_view").Rows(i).ItemArray(3).ToString
Dim item_Field4 As String = dataset.Tables("sent_view").Rows(i).ItemArray(4).ToString
Dim item_Field5 As String = dataset.Tables("sent_view").Rows(i).ItemArray(5).ToString
' ===========================
' here i will write in xml file, using fis.WriteLine() command
fis.WriteLine("<root>")
fis.WriteLine("<Field1>" & item_Field1 & "</Field1>")
fis.WriteLine("<Field2>" & item_Field2 & "</Field2>")
fis.WriteLine("<Field3>" & item_Field3 & "</Field3>")
fis.WriteLine("<Field4>" & item_Field4 & "</Field4>")
fis.WriteLine("<Field5>" & item_Field5 & "</Field5>")
fis.WriteLine("</root>")
' ================ write the xml file ==================
' dataset.WriteXmlSchema(fis)
' dataset.WriteXml(fis) ' write in files all datas from dataset
fis.Close()
Next i
cn.Close()
History
"No history, only future"
- If you have some better idea, please share it to me.