WebService, SQL Server 2005 and Updating rows with relations?

[Asaf]

Hi,

I have a MSSQL 2005 test DB with two tables:

Table "T1Customers":

T1CustomersRowEnum (PK, int, Not Null)
T1CustomersFullName (nvarchar(50) null)

Table "T2Details":

T2DetailsRowEnum (PK, int, Not Null)
T1CustomersRowEnum (FK, int, not null)
T2DetailsAddress (nvarchar(50), null
Also at the SQL Server I have set a "Diagram" so "FK_T2Details_T1Customers"
in relation and "Cascade" for Update & Delete.
The Problem:
A client application is generating a DataSet on a file and send it to a Web
Service that will call the dataset file and make a batch update to the DB.
I have created a dataset XML with relation & "Cascade" for it.

The XML:

<DataSet1 xmlns="http://tempuri.org/DataSet1.xsd">
<xs:schema id="DataSet1" targetNamespace="http://tempuri.org/DataSet1.xsd"
xmlns:mstns="http://tempuri.org/DataSet1.xsd"
xmlns="http://tempuri.org/DataSet1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="DataSet1" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="T1Customers">
<xs:complexType>
<xs:sequence>
<xs:element name="T1CustomersRowEnum" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int"/>
<xs:element name="T1CustomersFullName" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="T2Details">
<xs:complexType>
<xs:sequence>
<xs:element name="T2DetailsRowEnum" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int"/>
<xs:element name="T1CustomersRowEnum" type="xs:int" minOccurs="0"/>
<xs:element name="T2DetailsAddress" minOccurs="0">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:T1Customers"/>
<xs:field xpath="mstns:T1CustomersRowEnum"/>
</xs:unique>
<xs:unique name="T2Details_Constraint1"
msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:T2Details"/>
<xs:field xpath="mstns:T2DetailsRowEnum"/>
</xs:unique>
<xs:keyref name="FK_T2Details_T1Customers" refer="Constraint1">
<xs:selector xpath=".//mstns:T2Details"/>
<xs:field xpath="mstns:T1CustomersRowEnum"/>
</xs:keyref>
</xs:element>
</xs:schema>
<T1Customers>
<T1CustomersRowEnum>0</T1CustomersRowEnum>
<T1CustomersFullName>ABC</T1CustomersFullName>
</T1Customers>
<T2Details>
<T2DetailsRowEnum>0</T2DetailsRowEnum>
<T1CustomersRowEnum>0</T1CustomersRowEnum>
<T2DetailsAddress>123</T2DetailsAddress>
</T2Details>
</DataSet1>

When trying to run below code I am getting the error:

The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_T2Details_T1Customers". The conflict occurred in database "Test2", table
"dbo.T1Customers", column 'T1CustomersRowEnum'. The statement has been
terminated
The code:

DataSet1 ds = new DataSet1();
ds.ReadXml(@"C:\NewData.xml", XmlReadMode.IgnoreSchema);

T1CustomersTableAdapter objT1CustomersTableAdapter = new
T1CustomersTableAdapter();
T2DetailsTableAdapter objT2DetailsTableAdapter = new
T2DetailsTableAdapter();

objT1CustomersTableAdapter.Update(ds.T1Customers);
objT2DetailsTableAdapter.Update(ds.T2Details);

The very same code & very same XML dataset on a "Windows Application"
project is working fine.

Why it is not working on a web service?

Thanks in advanced for any help.
Regards,
Asaf

[Steven Cheng[MSFT]]

Hi Asafl,

Thanks for posting.

From your description, you're building an ASP.NET webservice which accepts
some DataSet from the client consumer, and use ADO.NET TableAdapter to
update the DataSet into backend database. However, you found that it will
fail into some SQL related error , and the code used to work in normal
winform application, correct?

As you mentioned the code worked in winform application, so I think the
overall code logic should be ok. So far I think we can check the following
things:

1. When the DataSet is transfered to webservice server-side, we can check
whether the two table and records are correctly stored in it( the same as
when it is in winform application). particularly, we can check the
DataRow's Status in the DataTable, this will affect the processing when we
call Update method on DataSet.

2. At SQL Server side, we can also use profiler to monitor the processing
of the dataset's updating. From the error message, the error conerned with
constraint confliction. I think it is likely that the first parent record
is not correctly inserted so that when the sequential sub record is being
inserted, there occur the error.

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Asaf]

Hi Steven,

I have solved the problem using other programming methods.

Thanks for your help.
Asaf
"Steven Cheng[MSFT]" wrote:

Hi Asafl,

Thanks for posting.

From your description, you're building an ASP.NET webservice which accepts
some DataSet from the client consumer, and use ADO.NET TableAdapter to
update the DataSet into backend database. However, you found that it will
fail into some SQL related error , and the code used to work in normal
winform application, correct?

As you mentioned the code worked in winform application, so I think the
overall code logic should be ok. So far I think we can check the following
things:

1. When the DataSet is transfered to webservice server-side, we can check
whether the two table and records are correctly stored in it( the same as
when it is in winform application). particularly, we can check the
DataRow's Status in the DataTable, this will affect the processing when we
call Update method on DataSet.

2. At SQL Server side, we can also use profiler to monitor the processing
of the dataset's updating. From the error message, the error conerned with
constraint confliction. I think it is likely that the first parent record
is not correctly inserted so that when the sequential sub record is being
inserted, there occur the error.

Hope this helps.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights

[Steven Cheng[MSFT]]

Thanks for your followup Asaf,

Glad that you've found the way to go on.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)