65.9K
CodeProject is changing. Read more.
Home

Custom XML file(s) named from a database fields

starIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

1.00/5 (3 votes)

Mar 5, 2008

MIT

1 min read

viewsIcon

17991

Custom XML file(s) named from a database fields (Sql Server 2005 DB)

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   ' strConn is your own connection string
Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strConn)
Dim dataset As New DataSet
Dim strSelect As String
cn.Open()

' bring data into dataset 

strSelect = "SELECT * FROM sent_view"
Dim dscommand As New SqlClient.SqlDataAdapter(strSelect, cn)
dscommand.Fill(dataset, "sent_view") ' incarc datasetul

'checking if data are loaded into dataset and count it
 
Dim con As Integer
con = Me.BindingContext(dataset, "sent_view").Count

If con = 0 Then
'MessageBox.Show("There is no data to send !")
End If






' starting to write line by line 

Dim i As Integer = 0
For i = 0 To dataset.Tables("sent_view").Rows.Count - 1



'  writing the crtNo value into xml's name
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.