Q: dump dataset to a table in SQL server

[Guest]

Hello,

Here is part of my code to retrieve some data from FoxPro and populate
DataGrid. It works fine. What is the best and easiest way to import data from
Dataset to a table in SQL Server.

' populate dataAdapter and dataset
cmdSelect = Conn.CreateCommand
cmdSelect.CommandText = sqlStr
Da.SelectCommand = cmdSelect
Da.Fill(Ds, "ReturnedRows")
retValue = Ds.Tables(0).Rows.Count()
Response.Write("Row Count: " & retValue)

' populate datagrid
DataGrid1.DataSource = Ds
DataGrid1.DataMember = "ReturnedRows"
DataGrid1.DataBind()

Thanks,
Jim.

 

[Guest]

The DataSet exposes a GetXml() method, which serializes the DataSet to an xml
string which can subsequently be sent to SQL Server with a few caveats
related to the amount of data in the DataSet....however...

Depending on what you ultimately want to accomplish with the data you may
not want to use the dataset as a data transformation service. You may be
better served setting up a DTS package that imports data from VFP into SQL
Server. DataSets aren't a data transfer service for large amounts of data
espacially when you have access to DTS in SQL Server.

The DataSet is great for disconnected access to data and binding to a
DataGrid like you mention. You can also leverage the DataSet as a container
for consolidating disparate data sources (ie, VFP and SQL Server) but if your
ultimate goal is to move data from VFG to SQL Server use DTS.

JP