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

JKennedy · Accepted Answer · 2014-07-21 15:15:45Z

2

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;
        }
    }

answered Jul 21, 2014 at 15:15

JKennedy

18.9k21 gold badges129 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update Sql Table from a Dataset using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related