代码之家 › 专栏 › 技术社区 › Var

如何从c#列表验证是否插入关系记录

testing sql c#

Var · 技术社区 · 6 年前

我有 List<Customers> n 十

public class Customers
{
  public int CustomerId {get;set;}
  public int CustomerTableXYZUniqueId {get;set;}
  public List<orders> orders{ get; set; }
}

public class Orders
{
  public int OrderId {get;set;}
  public int CustomerId {get;set;}
  public int OrderTableXYZUniqueId {get;set;}
  public List<orders> orders{ get; set; }
}

父表:客户

现在在C#中,我需要执行一个SQL查询,这将保证所有30行记录都已输入。

parent Table : customer
child Table : orders

也许是这样,但不确定:

select * from customer as poh  join
orders as poL customer.customerId  = orders.customerId 
where customer.CustomerTableXYZUniqueId in ( "172772,18282881,28282818")
and orders.orderTableXYZUniqueId in ('37371', "182882");

我想做的是,我将List发送到某个POST-API调用,当API调用返回success时,我想通过c运行某种sql脚本,它将确保输入的记录具有精确的字段值。

在这个例子中,我选择了字段属性是Customer/OderXYZUniqueId,

编辑2:

var count1 = SQL query with in/join statement.
var count2 = ( how do we calculate the count here)

if(count1 == count2)
{
 // test pass here
}

0 回复 | 直到 6 年前

StepUp 6 年前

您可以按某个唯一列查找所有记录:

select * from customer where CustomerTableXYZUniqueId in ( "172772,18282881,28282818")

第二个:

select * from orders 
where orderTableXYZUniqueId in ('37371', "182882");

或者尝试添加一些列,比如 DateCreated 并在插入时编写C代码 Date.Now

MikNiller 6 年前

select * from customer as poh 
inner join orders as poL on poh.customerId  = pol.customerId 
where poh.CustomerTableXYZUniqueId in ( '172772','18282881','28282818')

假设您的唯一id是字符串,否则将

这将为您提供一套三个客户id的所有订单

如果您需要绝对确定订单id也匹配,那么您还需要添加该条件

select * from customer as poh 
inner join orders as poL on poh.customerId  = pol.customerId 
where poh.CustomerTableXYZUniqueId in ( '172772','18282881','28282818')
and pol.orderTableXYZUniqueId in ('37371', '182882')

如果您只需要确保在测试中插入40行,并且没有其他人在测试运行的同时插入数据,那么另一种方法可以是在运行测试之前获取总计数,并将其与之后的总计数进行比较。

select count(*) from customer as poh 
inner join orders as poL on poh.customerId  = pol.customerId

如果你想要一个新客户和订单的总数,那么我建议

select count(*) as cnt from  from customer as poh 
where poh.CustomerTableXYZUniqueId in ( '172772','18282881','28282818')
union all 
select count(*) as cnt from orders as pol where pol.orderTableXYZUniqueId in ('37371', '182882')

推荐文章

SpaceCowboy74 Mohammad Aghazadeh · 使用Fluent Validation比较数组中的值

1 年前