Update Sql Table from a Dataset using C#

Question

I am trying to synchronize two SQL database tables. For example, synchronizing my local database up to an Azure database. My Method so far is to get the data from my local Database into a DataSet. Delete all entries from the Azure database and insert the DataSet into Azure.

I have successfully managed to get my local database data into a DataSet and delete the Azure data. But I cannot then Insert the DataSet into my Azure table. What is the best way to do this? Ideally I do not want to Insert the data by explicitly naming columns in my application as I would like to future proof it in case I add columns in the future.

So far I have:

    private static bool UploadDataSet(DataSet ds)
    {
        try
        {
            using (SqlDataAdapter updateData = new SqlDataAdapter("",AzureSqlConnection))
            {
                updateData.Update(ds, ds.Tables[0].TableName);
                Log.LogWriter(string.Format("Updating data in table {0}", ds.Tables[0].TableName), "Success");
                return true;
            }
        }
        catch (Exception ex)
        {
            Log.LogWriter(string.Format("Failed to update table {0}. Error {1}", ds.Tables[0].TableName, ex), "Failed");
            throw;
        }
    }

But this doesn't actually upload any rows into my Azure database. I'm guessing because RowState of the rows in my dataset are unchanged.

Ps. I know there is already a sync agent on Azure but this is still in preiview and not fit for production Enviroment

User1 User1 · Accepted Answer · 2014-07-21T15:15:45

Ok so I got there in the end using this link:

bulk-insert-into-sql

    private static bool UploadDataSet(DataSet ds)
    {
        try
        {
            SqlBulkCopy bulkCopy = new SqlBulkCopy(AzureSqlConnection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
            bulkCopy.DestinationTableName = ds.Tables[0].TableName;
            bulkCopy.WriteToServer(ds.Tables[0]);
            Log.LogWriter(string.Format("Updating data in table {0}", ds.Tables[0].TableName), "Success");
            return true;
        }
        catch (Exception ex)
        {
            Log.LogWriter(string.Format("Failed to update table {0}. Error {1}", ds.Tables[0].TableName, ex), "Failed");
            throw;
        }
    }

Update Sql Table from a Dataset using C#

1 Answers