Update SQL database from local DataTable

Question

Hi

My current work flow is pulling / setting data in a remote database. I dont want to have multiple transactions to the remote database so have constructed an identical local DataTable version. My intention is to make a local copy of the filtered dataset from the remote database, update the values as required then send the updates back to update the filtered dataset.

Workflow

1. Get filtered dataset from SQL Server and populate local DataTable
2. update local DataTable with change values
3. On validation, send the local Datatable back to SQL Server as an update

How can i collect a dataset from SQL Server into a local DataTable?
How can i send the DataTable back to SQL Server as an update?
Is it possible to send a complete dataset between databases?

Thanks

Answer 1

im working in SQL Server environment for the remote server and locally in vb.net, a DataTable()

Yes, you should be able to edit the tags. And you should add tags for dotnet, to get help from those people, since if I understand this correctly this is a .NET problem. I'm an SQL guy, but I will try to share with what I know.

To fill the DataSet, you use DataAdapter.Fill. I don't seem to have a sample in Visual Basic around, but here is an example in PowerShell:

$cn = New-Object SqlConnection $connstr;
$cn.Open();
$cmd = New-Object SqlCommand(
       "SELECT TOP 1000 * FROM dbo.Customers", $cn);
$cmd.CommandType = [CommandType]::Text;
$da = New-Object SqlDataAdapter($cmd);
$dt = New-Object DataTable;
$nrows = $da.Fill($dt);

The flow is the same in Visual Basic. Only the syntax is different. Here I'm filling a DataTable, but you can also use a DataSet.

To write back the data, the best is to use a table-valued parameter, and in this case I actually have an article that discusses this in detail, Using Table-Valued Parameters in SQL Server and .NET.. Short summary, you can pass the DataTable directly to the TVP. (Most of the article discusses other means that are better for the cases when you don't already have a DataTable, so you only need to read the first couple of pages.)

Answer 2

You can consider Azure SQL Data Sync as a free tool that can help you achieve your goal. You can configure bi-directional Sync between the remote (Azure SQL - hub database) and the local database (member database).

SQL Data Sync will sync all rows on all tables you add to a "sync group", it does not allow you to select a subset of rows of specific tables, but in the end you just update the rows you want to update locally, and only those rows will be send to the hub database. However, you will receive on the local database (member database) all rows updated on the hub database too.