我正在嘗試創建一個存儲過程來添加或更新在 c# 中執行的對象集合,但不確定為什么它在 c# 中無法正確執行而在 sql 中運行良好。我不確定如何生成一些有用的錯誤消息來幫助調試。任何指導我走向正確方向的提示或指針都非常感謝。這是示例代碼:SQL 腳本:CREATE TYPE CodeListAS TABLE( Code varchar(255) NOT NULL UNIQUE, Name varchar(max) NOT NULL, GeneratedDate date NULL);GOPRINT 'Created CodeList Type';GOCREATE PROCEDURE AddOrUpdateCodes @List AS CodeList READONLYASBEGIN SET NOCOUNT ON; MERGE dbo.Code AS tgt USING @List AS src ON tgt.Code = src.Code WHEN MATCHED THEN UPDATE SET Name = src.Name, GeneratedDate = src.GeneratedDate WHEN NOT MATCHED THEN INSERT (Code, Name, GeneratedDate) VALUES (src.Code, src.Name, src.GeneratedDate);ENDGOPRINT 'Created AddOrUpdateCodes Stored Procedure';GODECLARE @List AS CodeList;INSERT INTO @List (Code, Name, GeneratedDate) VALUES ('SQLTEST', 'SQLTEST', '2018-07-30')EXEC AddOrUpdateCodes @ListGOSELECT * FROM Symbol;C# 代碼:public int AddOrUpdateCodes(List<Code> codes){ using (var entity = new CodeEntities()) { var dataTable = new DataTable("CodeList"); using (var reader = ObjectReader.Create(codes, "Code", "Name", "GeneratedDate")) { dataTable.Load(reader); } var sqlParameter = new SqlParameter("@List", dataTable); sqlParameter.SqlDbType = SqlDbType.Structured; sqlParameter.TypeName = "dbo.CodeList"; var result = entity.Database.ExecuteSqlCommand("AddOrUpdateCodes", sqlParameter); entity.SaveChanges(); return result; }}
1 回答

九州編程
TA貢獻1785條經驗 獲得超4個贊
DbContext.Database.ExecuteSqlCommand() 方法有助于對數據庫執行給定的 DDL/DML 命令。嘗試使用這一行傳遞 SqlParameters:
var result = entity.Database.ExecuteSqlCommand("AddOrUpdateCodes @List",
sqlParameter);
entity.SaveChanges();
return result;
- 1 回答
- 0 關注
- 246 瀏覽
添加回答
舉報
0/150
提交
取消