Adding records through batch Webservice call

Hello everyone,

I am either missing something, or it is actually not so straight forward.

How do I add batch of records through single Webservice call? - Important
requirement, newly added records have ids and some other values assigned on
the server side, so they have to be reconciled (merged back).

The I experience that in order to send data, and be able to merge results
the back, I have to create some, temporary unique id on the client side. But
when the batch (DataTable) of added records comes back, unique ids differ
and automatic reconciliation using Merge() method is no longer possible.

I can, of course, deleted on the client side newly added records, and add
those returned back by the webmethod call, but may be there exists more
"elegant" solution.

Thank you,

Tomasz

Hi Tomasz,

According to your description, I understood you want to add a batch of
records through webservice, but you encountered an issue on id column of
datatable. The unique id received by webservice is different from the id
when you send it in client side.
If there is anything I misunderstand, please don't hesitate to correct me.
Thanks.

Would you mind providing us some code snippet of your program both in
client side and your server side? I have tried to reproduce this issue.
I create a webservice to merge datatable such as
[WebMethod]
public DataTable mergettable(DataTable dt)
{
DataTable table = new DataTable("Items");
DataColumn c1 = new
DataColumn("id",Type.GetType("System.Int32"), "");
DataColumn c2 = new
DataColumn("Item",Type.GetType("System.Int32"), "");
table.Columns.Add(c1);
table.Columns.Add(c2);
DataColumn[] keyCol = new DataColumn[1];
keyCol[0] = c1;
table.PrimaryKey = keyCol;
for (int i = 0; i < 10; i++)
{
DataRow row = table.NewRow();
row["id"] = i;
row["Item"] = i;
table.Rows.Add(row);
}
dt.Merge(table);
return dt;
}

Then I create a datatable and call webservice in client side.
private DataTable DemonstrateMergeTable()
{
DataTable table = new DataTable("Items");
DataColumn c1 = new
DataColumn("id",Type.GetType("System.Int32"), "");
DataColumn c2 = new
DataColumn("Item",Type.GetType("System.Int32"), "");
table.Columns.Add(c1);
table.Columns.Add(c2);
DataColumn[] keyCol = new DataColumn[1];
keyCol[0] = c1;
table.PrimaryKey = keyCol;

for (int i = 0; i < 10; i++)
{
DataRow row = table.NewRow();
row["id"] = i;
//row["Item"] = i;
table.Rows.Add(row);
}
return table;
}

private void button1_Click(object sender, EventArgs e)
{
DataTable dt = DemonstrateMergeTable();
localhost.Service1 lh = new localhost.Service1();
DataTable dt1= lh.mergettable(dt);
}

But it works fine on my machine.
ID has not been changed after I send it through webservice.
The webservice method(mergettable()) merged this two datatable.
Is there anything I misunderstand on your issue.
Please feel free to reply me here and we are glad to work with you.

Have a great day!
Wen Yuan

Hi WenYuan,

Thank you for a prompt response.
Below I am attaching an example more closely resembling the scenario I have
tried to describe.
The problem is that server side procedures adding new records must be able
to assign new ids, overwriting temporary client-side assigned ids. That is
the constraint I have to leave with.

Tomasz

static void Main(string[] args)
{
// create test table
DataTable table = new DataTable("items");
table.Columns.Add("id", typeof(Guid));
table.Columns.Add("item", typeof(string));
table.PrimaryKey = new DataColumn[] {table.Columns["id"]};

// create some exising records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}
table.AcceptChanges();

// add some new records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}

// update changes
DataTable changes = table.GetChanges();
Console.WriteLine(changes.Rows.Count);
changes = UpdateChanges(changes); // simulate WebMethod call

// reconcile changes after update
table.Merge(changes);

// instead of expected 20 records (10 existing + 10 new)
// table has now 30...
Console.WriteLine(table.Rows.Count);
Console.Read();
}

// [WebMethod]
public static DataTable UpdateChanges(DataTable changes)
{
// simulate DataAdapter.Update() method call
DataTable addedRows = changes.GetChanges(DataRowState.Added);

foreach (DataRow addedRow in addedRows.Rows) {
// adding records server assigns unique ids!
addedRow["id"] = Guid.NewGuid();
addedRow.AcceptChanges();
}

return addedRows;
}

Hi Tomasz,
Thanks for your reply.
I have debugged the sample code.
I think root cause of this issue is that you have assigned new unique ids
in the WebMethod.
Because that Column ID is the Primary Key of table, each row with a unique
id will be added to table after merge() method.

In your specail case, we would like to suggest you can use following
statement to generate the table after you have merged DataTable(named
table) and DataTable(named changes).
table = table.GetChanges(DataRowState.Unchanged);

I would like to help you on code review.
In Main() method.
static void Main(string[] args)
{
......
for (int i = 0; i < 10; i++) {..}
table.AcceptChanges();
//There are 10 records in table and the RowState of each record is
unchanged

for (int i = 0; i < 10; i++) {...}
//There are 10 records (unchanged) and 10 records (added ) in table.

DataTable changes = table.GetChanges();
//Table.GetChanges() method will create a new datatable include 10 records
(added)

......
changes = UpdateChanges(changes);
//WebMethod return a table which has 10 records (unchanged)

table.Merge(changes);
//table is include 20 records (10 unchanged and 10 added)
//changes is include 10 records (10 for unchanged)
//For this reason, after merge() method be called, there will be 30 records
(20 for unchanged and 10 for added)in table

//**************
table = table.GetChanges(DataRowState.Unchanged);
//**************

Console.WriteLine(table.Rows.Count);
Console.Read();
}

Please test the above and let me know whether it is what you need. If there
is any question, please feel free to reply here and I am glad to work with
you.

Have a great day!
Wen Yuan

Hi Tomasz

I haven't heard back from you two days.
How are things going?
I would appreciate it if you could post here to let me know the status of
the issue.
If you have any questions or concerns, please don't hesitate to let me know.
I look forward to hearing from you, and I am happy to be of assistance.

Have a great day!
Wen Yuan

Hi Wen,

I think, for the performance reasons, a better approach is to delete changed
records first and than merge, rather than recreate the table and clone all
rows using: table = table.GetChanges(DataRowState.Unchanged);

Example:

// flag added or changed rows for deletion
DataRow[] changedRows = table.Select("", "", DataViewRowState.Added |
DataViewRowState.ModifiedCurrent);
foreach (DataRow changedRow in changedRows) {
changedRow.Delete();
}
table.AcceptChanges(); // deletes all changed rows

// reconcile changes after update
table.Merge(changes);

I just thought there was more "elegant, standard solution.

Complete example below.

Cheers,

Tomasz

static void Main(string[] args)
{
// create test table
DataTable table = new DataTable("items");
table.Columns.Add("id", typeof(Guid));
table.Columns.Add("item", typeof(string));
table.PrimaryKey = new DataColumn[] {table.Columns["id"]};

// create some exising records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}
table.AcceptChanges();

// add some new records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}

// update changes
DataTable changes = table.GetChanges();
Console.WriteLine(changes.Rows.Count);
changes = UpdateChanges(changes); // simulate WebMethod call

// flag added or changed rows for deletion
DataRow[] changedRows = table.Select("", "", DataViewRowState.Added |
DataViewRowState.ModifiedCurrent);
foreach (DataRow changedRow in changedRows) {
changedRow.Delete();
}
table.AcceptChanges(); // delete all changed rows

// reconcile changes after update
table.Merge(changes);

Console.WriteLine(table.Rows.Count);
Console.Read();
}

// [WebMethod]
public static DataTable UpdateChanges(DataTable changes)
{
// simulate DataAdapter.Update() method call
DataTable addedRows = changes.GetChanges(DataRowState.Added);

foreach (DataRow addedRow in addedRows.Rows) {
// adding records server assigns unique ids!
addedRow["id"] = Guid.NewGuid();
addedRow.AcceptChanges();
}

return addedRows;
}

Correcting myself: actually, only added rows have to be deleted before
changes can be reconciled:

// flag added rows for deletion
DataRow[] changedRows = table.Select("", "", DataViewRowState.Added);
foreach (DataRow changedRow in changedRows) {
changedRow.Delete();
}
table.AcceptChanges(); // removes all added or deleted rows

Tomasz

Compete example:

static void Main(string[] args)
{
// create test table
DataTable table = new DataTable("items");
table.Columns.Add("id", typeof(Guid));
table.Columns.Add("item", typeof(string));
table.PrimaryKey = new DataColumn[] {table.Columns["id"]};

// create some exising records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}
table.AcceptChanges();

// add some new records
for (int i = 0; i < 10; i++) {
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}

// update changes
DataTable changes = table.GetChanges();
Console.WriteLine(changes.Rows.Count);
changes = UpdateChanges(changes); // simulate WebMethod call

// flag added rows for deletion
DataRow[] changedRows = table.Select("", "", DataViewRowState.Added);
foreach (DataRow changedRow in changedRows) {
changedRow.Delete();
}
table.AcceptChanges(); // removes all added or deleted rows

// reconcile changes after update
table.Merge(changes);

// table still has 20 rows
Console.WriteLine(table.Rows.Count);
Console.Read();
}

// [WebMethod]
public static DataTable UpdateChanges(DataTable changes)
{
// simulate DataAdapter.Update() method call
DataTable addedRows = changes.GetChanges(DataRowState.Added);

foreach (DataRow addedRow in addedRows.Rows) {
// adding records server assigns unique ids!
addedRow["id"] = Guid.NewGuid();
addedRow.AcceptChanges();
}

return addedRows;
}

Hi Tomasz,
Thanks for your reply.

I agree with you. It's a better approach to deleting changed records first
and than merge, rather than recreating the table and clone all.
But I have something unclear, would you mind clarifying it for me.
If we want to add batch of records through single Webservice call, we would
like to pass the number of rows we want add to webservice and the webserice
return the table.
For example:
Public Datatable createDataTable(string tableName)
{
DataTable table = new DataTable(tableName);
table.Columns.Add("id", typeof(Guid));
table.Columns.Add("item", typeof(string));
return table;
}

static void Main(string[] args)
{
// create test table
DataTable table = createDataTable("items");
// create some exising records
for (int i = 0; i < 10; i++)
{
DataRow row = table.NewRow();
row["id"] = Guid.NewGuid();
row["Item"] = i;
table.Rows.Add(row);
}
table.AcceptChanges();
DataTable addedTable = AddRows(10, "items");
// reconcile changes after update
table.Merge(addedTable);
Console.WriteLine(table.Rows.Count);
Console.Read();
}

// [WebMethod]
public static DataTable AddRows(int rowCount,string tableName)
{
DataTable addedRows = createDataTable(tableName);
for(int i=0;i<rowCount;i++)
{
DataRow row = addedRows.NewRow();
row["id"] = Guid.NewGuid();
addedRows.Rows.Add(row);
}
addedRow.AcceptChanges();
return addedRows;
}

Would you mind telling us the scenario in your application, I can not
understand why you have to add new rows in your client and pass these rows
to webservice, then assign the value again.
If there is anything I misunderstand, please don't hesitate to correct me.
I'm glad to work you.

Have a great weekend!
Wen Yuan
Microsoft Online Community Support
v-wywang@microsoft.com

Hi Wen,

The need for this solution is dictated by a simple, not so unique
requirement: unique record id must be assigned on the server side.

Tomasz


"WenYuan Wang" <v-wywang@online.microsoft.com> wrote in message
news:g1K9FNjNHHA.2080@TK2MSFTNGHUB02.phx.gbl...
> Hi Tomasz,
> Thanks for your reply.
>
> I agree with you. It's a better approach to deleting changed records first
> and than merge, rather than recreating the table and clone all.
> But I have something unclear, would you mind clarifying it for me.
> If we want to add batch of records through single Webservice call, we
> would
> like to pass the number of rows we want add to webservice and the
> webserice
> return the table.
> For example:
> Public Datatable createDataTable(string tableName)
> {
> DataTable table = new DataTable(tableName);
> table.Columns.Add("id", typeof(Guid));
> table.Columns.Add("item", typeof(string));
> return table;
> }
>
> static void Main(string[] args)
> {
> // create test table
> DataTable table = createDataTable("items");
> // create some exising records
> for (int i = 0; i < 10; i++)
> {
> DataRow row = table.NewRow();
> row["id"] = Guid.NewGuid();
> row["Item"] = i;
> table.Rows.Add(row);
> }
> table.AcceptChanges();
> DataTable addedTable = AddRows(10, "items");
> // reconcile changes after update
> table.Merge(addedTable);
> Console.WriteLine(table.Rows.Count);
> Console.Read();
> }
>
> // [WebMethod]
> public static DataTable AddRows(int rowCount,string tableName)
> {
> DataTable addedRows = createDataTable(tableName);
> for(int i=0;i<rowCount;i++)
> {
> DataRow row = addedRows.NewRow();
> row["id"] = Guid.NewGuid();
> addedRows.Rows.Add(row);
> }
> addedRow.AcceptChanges();
> return addedRows;
> }
>
> Would you mind telling us the scenario in your application, I can not
> understand why you have to add new rows in your client and pass these rows
> to webservice, then assign the value again.
> If there is anything I misunderstand, please don't hesitate to correct me.
> I'm glad to work you.
>
> Have a great weekend!
> Wen Yuan
> Microsoft Online Community Support
> v-wywang@microsoft.com
>

Hi Tomasz,
Thanks for your reply.

I think I must have misunderstood your scenario. Could you clarify it for
me?
Do you mean you want to know what is the best way to add batch of records
through webservice (the unique record id must be assigned on the client
side first and then assigned on the server side again), isn't it?
I 'm Looking forward to hearing from you soon and it's my pleasure to be of
assistance.

Best Regards,
WenYuan