您的位置: 首页  >  技术问答  >  WEB API实体框架父子过帐到SQL数据库

WEB API实体框架父子过帐到SQL数据库

分类: 技术问答 • 2022-09-03 13:59:06
问题描述：

我正在写第一个基于第三方将发送的数据的第一个WEB-API服务。下面是他们将派出布局：WEB API实体框架父子过帐到SQL数据库

<StandardTitleOrderRequest> 
<Authentication> 
    <UserName>{$USERNAME}</UserName> 
    <Password>{$PASSWORD}</Password> 
</Authentication> 
<Borrowers> 
    <Borrower> 
     <FirstName>{$BORROWER_FIRST_NAME}</FirstName> 
     <MiddleName>{$BORROWER_MIDDLE_NAME}</MiddleName> 
     <LastName>{$BORROWER_LAST_NAME}</LastName> 
     <SSN>{$BORROWER_SSN}</SSN> 
    </Borrower> 
</Borrowers>- 
<Property> 
    <Address>{$SUBJECT_ADDRESS_STREET}</Address> 
    <City>{$SUBJECT_ADDRESS_CITY}</City> 
    <State>{$SUBJECT_ADDRESS_STATE}</State> 
    <Zip>{$SUBJECT_ADDRESS_ZIP}</Zip> 
    <County>{$SUBJECT_ADDRESS_COUNTY}</County> 
</Property>- 
<Contact> 
    <Name>{$CONTACT_NAME}</Name> 
    <Phone>{$CONTACT_PHONE}</Phone> 
    <Fax>{$CONTACT_FAX}</Fax> 
    <Email>{$CONTACT_EMAIL}</Email> 
</Contact>- 
<OrderInfo> 
    <LoanNumber>{$LOAN_NUMBER}</LoanNumber> 
    <LoanAmount>{$LOAN_AMOUNT}</LoanAmount> 
    <ToBeInsured/> 
    <FileNumber>{$FileID}</FileNumber> 
      <OrderId>{$ORDER_ID}</OrderId > 
    <CostCenter>{$BRANCH_NAME}</CostCenter> 
    <Product>{$PRODUCT_NUMBER}</Product> 
    <Notes>{$ORDER_NOTES}</Notes> 
    <ResponseURL>{$RESPONSE_URL}</ResponseURL> 
    <PID></PID> 
    <PayOffLoanNumber></PayOffLoanNumber> 
    <ClientCode>{$CLIENT_ID}</ClientCode> 
</OrderInfo>

我已经创建了以下4个表（订单，借款人，联系人，属性）将订单是共同的每个表的数据库。该订单将有多个借款人和1个联系人和1个财产。我有一个基本的web api内置（使用Fiddler）将允许我GET/POST/PUT/DELETE命令或联系人或借用者或财产。我不确定如何添加发布订单的逻辑，然后添加该订单的每个子元素（借入者，联系人，属性）。我正在使用VS 2017和EF 6.

我假设（也许是错误的），我需要将外键添加到我的表中，相互参考，然后更新数据模型，但是当我这样做时，我开始得到各种错误。

第三方将一次发送1个事务，因此传入的数据不会批量传输，并将以XML形式传输。

我已经尝试过在整个web api服务上查看各种教程，但其中90％只处理单个表POST。关于接收XML数据并发布给父母和一个或多个孩子的具体细节并没有涉及。另外我的应用程序目前只接受JSON格式的传入数据。下面是在OrdersController这将是后到数据库的基础上（POST https://localhost:xxxx/api/Orders)am代码寻找一些方向

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Net; 
using System.Net.Http; 
using System.Web.Http; 
using OrdersDataAccess; 

namespace waEtitle.Controllers 
{ 
public class OrdersController : ApiController 
{ 
    /// <summary> 
    /// Get Order Information 
    /// </summary> 
    /// <returns></returns> 
    /// 
    public IEnumerable<Order> Get() 
    { 
     using (FirstCloseEntities entities = new FirstCloseEntities()) 
     { 
      return entities.Orders.ToList(); 
     } 
    } 
    public HttpResponseMessage Get(int id) 
    { 
     using (FirstCloseEntities entities = new FirstCloseEntities()) 
     { 
      var entity = entities.Orders.FirstOrDefault(o => o.OrderID == id); 

      if (entity != null) 
      { 
       return Request.CreateResponse(HttpStatusCode.OK, entity); 
      } 
      else 
      { 
       return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with id = " + id.ToString() + " not found."); 
      } 
     } 
    } 

    public HttpResponseMessage Post([FromBody] Order order) 
    { 
     try 
     { 
      using (FirstCloseEntities entities = new FirstCloseEntities()) 
      { 
       entities.Orders.Add(order); 
       entities.SaveChanges(); 
       var message = Request.CreateResponse(HttpStatusCode.Created, order); 
       message.Headers.Location = new Uri(Request.RequestUri + order.OrderID.ToString()); 

       return message; 
      } 
     } 
     catch (Exception ex) 
     { 
      return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex); 
     } 
    } 

    public HttpResponseMessage Delete(int ID) 
    { 
     try 
     { 
      using (FirstCloseEntities entities = new FirstCloseEntities()) 
      { 
       var entity = entities.Orders.FirstOrDefault(c => c.OrderID == ID); 
       if (entity == null) 
       { 
        return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with ID = " + ID.ToString() + " not found to delete."); 
       } 
       else 
       { 
        entities.Orders.Remove(entity); 
        entities.SaveChanges(); 
        return Request.CreateResponse(HttpStatusCode.OK); 
       } 
      } 
     } 
     catch (Exception ex) 
     { 
      return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex); 
     } 
    } 

    public HttpResponseMessage Put(int id, [FromBody]Order order) 
    { 
     try 
     { 
      using (FirstCloseEntities entities = new FirstCloseEntities()) 
      { 
       var entity = entities.Orders.FirstOrDefault(c => c.OrderID== id); 
       if (entity == null) 
       { 
        return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with Id = " + id.ToString() + " not found to update."); 
       } 
       else 
       { 
        entity.LoanNumber = order.LoanNumber; 
        entity.LoanAmount = order.LoanAmount; 
        entity.ToBeInsured = order.ToBeInsured; 
        entity.FileNumber = order.FileNumber; 
        entity.CostCenter = order.CostCenter; 
        entity.Product = order.Product; 
        entity.Notes = order.Notes; 
        entity.ResponseURL = order.ResponseURL; 
        entity.PID = order.PID; 
        entity.PayOffLoanNumber = order.PayOffLoanNumber; 
        entity.ClientCode = order.ClientCode; 

        entities.SaveChanges(); 
        return Request.CreateResponse(HttpStatusCode.OK, entity); 
       } 

      } 

     } 
     catch (Exception ex) 
     { 
      return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex); 
     } 

    } 

}

}

表：

/* ==Scripting Parameters== 

    Source Server Version : SQL Server 2008 R2 (10.50.1617) 
    Source Database Engine Edition : Microsoft SQL Server Enterprise Edition 
    Source Database Engine Type : Standalone SQL Server 

    Target Server Version : SQL Server 2017 
    Target Database Engine Edition : Microsoft SQL Server Standard Edition 
    Target Database Engine Type : Standalone SQL Server 
*/ 

USE [FirstCloseAPI] 
GO 
/****** Object: Table [dbo].[Borrowers] Script Date: 9/26/2017 1:50:18 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Borrowers](
    [borID] [int] IDENTITY(1,1) NOT NULL, 
    [OrderID] [int] NULL, 
    [FirstName] [varchar](max) NULL, 
    [MiddleName] [varchar](max) NULL, 
    [LastName] [varchar](max) NULL, 
    [SSN] [varchar](max) NULL, 
CONSTRAINT [PK_Borrowers] PRIMARY KEY CLUSTERED 
(
    [borID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
/****** Object: Table [dbo].[Contacts] Script Date: 9/26/2017 1:50:18 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Contacts](
    [conId] [int] IDENTITY(1,1) NOT NULL, 
    [OrderID] [int] NULL, 
    [Name] [varchar](max) NULL, 
    [Phone] [varchar](max) NULL, 
    [Fax] [varchar](max) NULL, 
    [Email] [varchar](max) NULL, 
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
    [conId] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
/****** Object: Table [dbo].[Orders] Script Date: 9/26/2017 1:50:18 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] NOT NULL, 
    [LoanNumber] [varchar](max) NULL, 
    [LoanAmount] [money] NULL, 
    [ToBeInsured] [bit] NULL, 
    [FileNumber] [varchar](max) NULL, 
    [CostCenter] [varchar](max) NULL, 
    [Product] [varchar](max) NULL, 
    [Notes] [varchar](max) NULL, 
    [ResponseURL] [varchar](max) NULL, 
    [PID] [int] NULL, 
    [PayOffLoanNumber] [varchar](max) NULL, 
    [ClientCode] [varchar](max) NULL, 
CONSTRAINT [PK_OrderHeader] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
/****** Object: Table [dbo].[Properties] Script Date: 9/26/2017 1:50:18 PM ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Properties](
    [proID] [int] IDENTITY(1,1) NOT NULL, 
    [OrderID] [int] NULL, 
    [Address] [varchar](max) NULL, 
    [City] [varchar](max) NULL, 
    [State] [varchar](max) NULL, 
    [Zip] [varchar](max) NULL, 
    [County] [varchar](max) NULL, 
CONSTRAINT [PK_Property] PRIMARY KEY CLUSTERED 
(
    [proID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
ALTER TABLE [dbo].[Borrowers] WITH NOCHECK ADD CONSTRAINT [FK_Borrowers_Orders] FOREIGN KEY([OrderID]) 
REFERENCES [dbo].[Orders] ([OrderID]) 
GO 
ALTER TABLE [dbo].[Borrowers] NOCHECK CONSTRAINT [FK_Borrowers_Orders] 
GO 
ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD CONSTRAINT [FK_Contacts_Orders] FOREIGN KEY([OrderID]) 
REFERENCES [dbo].[Orders] ([OrderID]) 
GO 
ALTER TABLE [dbo].[Contacts] NOCHECK CONSTRAINT [FK_Contacts_Orders] 
GO 
ALTER TABLE [dbo].[Properties] WITH NOCHECK ADD CONSTRAINT [FK_Properties_Orders] FOREIGN KEY([OrderID]) 
REFERENCES [dbo].[Orders] ([OrderID]) 
GO 
ALTER TABLE [dbo].[Properties] NOCHECK CONSTRAINT [FK_Properties_Orders] 
GO

目前我的GET正在返回

<Order> 
     <Borrowers /> 
    <ClientCode>cc1</ClientCode> 
     <Contacts /> 
    <CostCenter>900-111</CostCenter> 
    <FileNumber>11111</FileNumber> 
    <LoanAmount>1111.0000</LoanAmount> 
    <LoanNumber>111</LoanNumber> 
    <Notes>notes1</Notes> 
    <OrderID>1</OrderID> 
    <PID>1</PID> 
    <PayOffLoanNumber>po1</PayOffLoanNumber> 
    <Product>letter</Product> 
     <Properties /> 
    <ResponseURL>yahoo.com</ResponseURL> 
    <ToBeInsured>true</ToBeInsured> 
</Order>

正如你所见，没有接触或借款等

**

UPDATE: ---------------------------------------------------

**

我能得到我弄到（INT ID）命令工作，并通过改变程序返回正确的数据如：

public IEnumerable<Order> Get() 
{ 
    using (FirstCloseAPIEntities entities = new FirstCloseAPIEntities()) 
    { 
     var entity = entities.Orders.ToList(); 
     List<Borrower> borrowers = entities.Borrowers.ToList(); 
     List<Contact> contacts = entities.Contacts.ToList(); 
     List<Property> properties = entities.Properties.ToList(); 

     //return entities.Orders.ToList() ; 
     return entity; 
    } 
} 

public HttpResponseMessage Get(int id) 
{ 
    using (FirstCloseAPIEntities entities = new FirstCloseAPIEntities()) 
    { 
     var entity = entities.Orders.FirstOrDefault(o => o.OrderID == id); 
     List<Borrower> borrowers = entities.Borrowers.Where(b => b.OrderID == id).ToList(); 
     List<Contact> contacts = entities.Contacts.Where(c => c.OrderID == id).ToList(); 
     List<Property> properties = entities.Properties.Where(p => p.OrderID == id).ToList(); 

     if (entity != null) 
     { 
      return Request.CreateResponse(HttpStatusCode.OK, entity); 
     } 
     else 
     { 
      return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Order with id = " + id.ToString() + " not found."); 
     } 
    } 
}

但是，因为我请求为HTML我然后得到错误：

The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'

所以我不得不添加以下到的Global.asax.cs文件：

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore; 
GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

这给我回一个成功的响应，但是，不管我放在头的类型application/XML应用程序/ JSON我总是将响应作为JSON获得回应。

Content-Type: application/xml 
accept: application/xml 
accept-encoding: gzip, deflate 
accept-language: en-US,en;q=0.8 
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36 


Cache-Control: no-cache 
Content-Length: 707 
Content-Type: application/json; charset=utf-8 
Date: Tue, 26 Sep 2017 19:46:42 GMT 
Expires: -1 
Pragma: no-cache 
Server: Microsoft-IIS/10.0 
X-Aspnet-Version: 4.0.30319 
X-Powered-By: ASP.NET 
X-Sourcefiles: =?UTF-8?B?YzpcdXNlcnNcZG91Zy5oYW1pbHRvblxkb2N1bWVudHNcdmlzdWFsIHN0dWRpbyAyMDE3XFByb2plY3RzXHdzRXRpdGxlXHdzRXRpdGxlXGFwaVxPcmRlcnNcMQ==?= 
Raw 
JSON 


{ 
"OrderID": 1, 
"LoanNumber": "111", 
"LoanAmount": 1111, 
"ToBeInsured": true, 
"FileNumber": "11111", 
"CostCenter": "900-111", 
"Product": "letter", 
"Notes": "notes1", 
"ResponseURL": "yahoo.com", 
"PID": 1, 
"PayOffLoanNumber": "po1", 
"ClientCode": "cc1", 
"Borrowers": [ 
{ 
"borID": 1, 
"OrderID": 1, 
"FirstName": "Ura", 
"MiddleName": "O", 
"LastName": "Pepper", 
"SSN": "111-11-1111" 
}, 
{ 
"borID": 3, 
"OrderID": 1, 
"FirstName": "Ima", 
"MiddleName": "J", 
"LastName": "Pepper", 
"SSN": "222-22-2222" 
} 
], 
"Contacts": [ 
{ 
"conId": 1, 
"OrderID": 1, 
"Name": "Jackie the box", 
"Phone": "414-555-1243", 
"Fax": "414-222-1245", 
"Email": "[email protected]" 
} 
], 
"Properties": [ 
{ 
"proID": 1, 
"OrderID": 1, 
"Address": "123 Main Street", 
"City": "Anytown", 
"State": "WI", 
"Zip": "10012 
"County": "Westchester" 
} 
], 
}

所以我仍然困惑，如何让XML响应，然后最后一部分是能够把作为这篇文章的顶部显示将要提供的信息，并张贴于正确的表格。在我原来的职位的“更新”的言论

+0

“我假设（也许是错误的），我需要外键添加到我的表引用回对方，然后更新数据模型，但是当我这样做，我开始得到各种错误。”正确。什么错误？ –

+0

我有它使用JSON本地工作，但XML似乎是一个问题。切换源XML格式，标题为application/xml时，我会得到一个415不支持的媒体类型的消息（Content-Type和接受。） – uwdoug79

答

我的代码更改让我GET和POST化JSON数据我在本地主机上创建的Web API，因此我能够通过我的问题而努力。

我目前的其他问题都涉及到张贴数据作为XML而不是JSON并能够在网络API移动到一个已经存在的面向公众的网站。我会将这些作为单独的问题发布。

相关推荐