How to best get an untyped DS into an existing SQL database?

[jwarmuth]

I've been reading up on how to do this and it appears I have a few options so i'd like to get some experienced programmers feedback.

I have a function that retrieves XML data, processess it for formatting issues and puts the data into a dataset I've created (which is returned at the end of the function).

Now I need to get that data into a database I've created on my SQL server. All the columns have equal partner columns in the DB except for a unique ID key that autoincrements.

Here's teh code block defining my dataset and table:

                'create the dataset and table
                Dim ds_formattedXML As DataSet = New DataSet
                Dim dt_formattedXML As DataTable = New DataTable("stockQuote")
                ds_formattedXML.Tables.Add(dt_formattedXML)

                'define table column types
                dt_formattedXML.Columns.Add("symbol", Type.GetType("System.String"))
                dt_formattedXML.Columns.Add("last", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("date", Type.GetType("System.DateTime"))
                dt_formattedXML.Columns.Add("time", Type.GetType("System.DateTime"))
                dt_formattedXML.Columns.Add("change", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("open", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("high", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("low", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("volume", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("mktcap", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("previousClose", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("percentageChange", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("annRangeUpper", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("annRangeLower", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("earns", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("pe", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("name", Type.GetType("System.String"))

Suggestions on how to get that datasets' data into my sql db?

One question I have is, should I write an XML schema? Would there be any benefit, or can I just send the data straight through a data adapter into the db?

Jeff W.
MCSE, CNE

 

[ThatRickGuy]

Open a dataset w/ the table you want to update. make sure you have the update code in place.

Open the dataset from the XML you want to insert into the SQL Server database.

loop throw each row in the data table from the XML and add a row to the data table from SQL Server.

done.

-Rick

----------------------

http://www.ringdev.com

I beleive in killer coding ninja monkies.