如果传递给存储过程的值数目相同，则抛出错误

Question

我有一个运行MERGEupsert的存储过程。它使用表值参数来获取信息。然后我从ASP调用它。. NET 5项目，我使用Dapper ORM调用存储过程。

当数据库存储过程和模型传递具有相同数量的字段时，它工作得很好，但这意味着如果我需要更新一个数据库并更新ASP。. NET项目的数据库调用将开始抛出一个错误，或者如果我不得不降级ASP. NET的当前版本。. NET项目，但不能降级数据库也会导致任何错误。

有谁知道解决这个问题的办法吗?

基本上，我在寻找一种方法，他们不太依赖于一个特定的版本来工作。

CREATE TYPE [dbo].ItemType AS TABLE
(
[Id]                VARCHAR(36)    NOT NULL,
[Name]              VARCHAR(100)   NULL,
[FullName]          VARCHAR(100)   NULL,
[Description]       VARCHAR(4095)  NULL,
[QuantityOnHand]    DECIMAL(18, 5) NULL,
[IsActive]          BIT            NULL,
[TimeCreated]       DATETIME2      NULL,
[TimeModified]      DATETIME2      NULL,
[AverageCost]       DECIMAL(18, 5) NULL,
[SellingPrice]      DECIMAL(18, 5) NULL,
[ExtCaseText]       VARCHAR(255)   NULL,
[ExtCaseValue]      INT            NULL ,
[Barcode]           VARCHAR(50)    NULL,
[Brand]             VARCHAR(450)   NULL,
[PurchaseCost]      DECIMAL(18, 5) NULL,
[OurBrand]          BIT            NULL,
[Vendor]            VARCHAR(450)   NULL 
)
CREATE PROCEDURE [dbo].UpsertItem
@UpdateRecords dbo.ItemType READONLY,
@LastModifiedSync DATETIME2 OUTPUT
AS
BEGIN
MERGE INTO 
qbItems AS Target
USING 
@UpdateRecords AS Source ON Target.Id = Source.Id
WHEN MATCHED THEN
UPDATE  
SET Target.Id = Source.Id,
Target.[Name] = Source.[Name],
Target.[FullName] = Source.[FullName],
Target.[Description] = Source.[Description],
Target.QuantityOnHand = Source.QuantityOnHand,
Target.IsActive = Source.IsActive,
Target.[TimeCreated] = Source.[TimeCreated],
Target.TimeModified = Source.TimeModified,
Target.AverageCost = Source.AverageCost,
Target.SellingPrice = Source.SellingPrice,
Target.ExtCaseText = Source.ExtCaseText,
Target.ExtCaseValue = Source.ExtCaseValue,
Target.Barcode = Source.Barcode,
Target.Brand = Source.Brand,
Target.OurBrand = Source.OurBrand,
Target.Vendor = Source.Vendor,
Target.PurchaseCost = Source.PurchaseCost

WHEN NOT MATCHED THEN           
INSERT (Id, [Name], [FullName], [Description],
QuantityOnHand, IsActive, [TimeCreated], TimeModified,
AverageCost, SellingPrice, ExtCaseText, ExtCaseValue,
Barcode, Brand, OurBrand, Vendor, PurchaseCost)
VALUES (Source.Id, Source.[Name], Source.[FullName], Source.[Description],
Source.QuantityOnHand, Source.IsActive, Source.[TimeCreated], Source.TimeModified,       
Source.AverageCost, Source.SellingPrice, Source.ExtCaseText, Source.ExtCaseValue,
Source.Barcode, Source.Brand, Source.OurBrand, Source.Vendor, Source.PurchaseCost);
SELECT @LastModifiedSync = [TimeModified] 
FROM qbItems 
ORDER BY TimeModified ASC;
END

c#代码调用过程:

public bool Create(ref List<qbItem> items)
{
// LastMod = new DateTime();
if (items.Count <= 0) 
return true;
try
{
var p = new DynamicParameters();
p.Add("UpdateRecords", items.Where(x => x.FullName != null).OrderBy(x => x.TimeModified).ToDataTable().AsTableValuedParameter("ItemType"));
p.Add("LastModifiedSync", dbType: DbType.DateTime, direction: ParameterDirection.Output);
p.Add("affectedRows", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
using var connection = new SqlConnection(_dbConnection.DefaultConnection);
var affectedRows = connection.Execute("[dbo].[UpsertItem]", p,
commandType: CommandType.StoredProcedure);
//LastMod = items.OrderByDescending(t => t.TimeModified).Select(x => x.TimeModified).First();
return true;
}
catch (Exception e)
{
logger.LogError(e.ToString());
return false;
}
}
public static DataTable ToDataTable<T>(this IEnumerable<T> data)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}

如果有任何需要帮助的信息，请评论。

Answer 1

如何让你的c#查询数据库的定义ItemType和调整数据表基于它

select *
from information_schema.domains
where data_type = 'table type'

你可以用它来告诉你db端类型;也许列名称和类型的列表就可以了

然后你可以像现在一样构建你的数据表，然后通过计算要添加哪些列和要删除哪些列来调整它

var incs = table.Columns.Cast<DataColumn>().Select(c => c.ColumnName);
var indb = (query the info schema and make a list)

然后你可以计算出哪些列要删除哪些列要添加:

var toRemove = incs.Except(indb);
var toAdd = indb.Except(incs);

并相应地调整数据表

foreach(.. in toRemove)
dt.Columns.Remove(..)

Add可能有点麻烦，因为要把db类型映射到c#类型，但也不是不可能的