I met problem when I write WebService for a database
The simplified database model like this:
Create table [CreditReport]
(
[CreditReportID] Integer Identity(1,1) NOT NULL, UNIQUE ([CreditReportID]),
[ReportType] Nvarchar(10) NULL,
[ReportNumber] Varchar(20) NULL,
Primary Key ([CreditReportID])
)
go
Create table [EnterpriseBasicInformation]
(
[EnterpriseBasicInformationID] Integer Identity(1,1) NOT NULL,
[CreditReportID] Integer NOT NULL,
[CreateBKandDate] Nvarchar(1000) NULL,
[DevelopmentProcedure] Nvarchar(1000) NULL,
[ShareholderChangeHistory] Nvarchar(1000) NULL,
Primary Key ([EnterpriseBasicInformationID])
)
go
Alter table [EnterpriseBasicInformation] add foreign key([CreditReportID]) references [CreditReport] ([CreditReportID]) on update cascade on delete cascade
go
I wrote a Webservice based on that database model and use that WebService as the datasource of an InfoPath sheet.
Suppose I use "DataSet1" as the name of my dataset used in WebService, I use two "SELECT * FROM ..." SQL statements to generate those two tables. I also added relationship between those two tables in DataSet1.xsd file.
The problems I met:
(1)"CreditReportID" and "EnterpriseBasicInformationID" are "Identity", those values should not be generated untill we insert those records into SQL database. But the InfoPath sheet based on that
WebService will automatically fill "CreditReportID" and "EnterpriseBasicInformationID" begining from "0"
(2)because "EnterpriseBasicInformation" is "child" of "CreditReport" table, "CreditReportID" in "EnterpriseBasicInformation" " table should equal the value in "CreditReport" table. But InfoPath sheet does not automatically fill that "CreditReportID" in "EnterpriseBasicInformation" based on the CreditReport section. Mannually fill "CreditReportID" is impossible because that value generated by SQL system when insert record.
BTW:I notice If I directly use SQL database as the datasource(Added those two tables both and set proper relationship). InfoPath sheet will work very well, it will handle "Identity" field and automaticlly fill the foreign key based on parent table.
(3)Should I use Graphic interface and "Drag and Drop" way to generate those SQLDataAdapter, SQLCommand, DataSet etc. or should write code all by myself? because I find I can not controll the detail if I use Graphic interface(So I am not sure where is the point when I meet problem) but write all code by myself is a exhausting work because there are lost of tables I need to handle.