==================================================
-- sql代码 示例：
CREATE TYPE dbo.Content AS TABLE
(
ID int, --ID
OutCashAccountNo nvarchar(100), --出账账号
OutName nvarchar(50),
InCashAccountNo nvarchar(100), --进账账号
InName nvarchar(50),
CashType int,
Amount decimal(36,16), --金额
IsSuccess int, --是否成功
YuanYin nvarchar(200), --成功或失败原因
RemarkContent nvarchar(300), --ID+YuanYin//199_交易失败嘎嘎
RunWater nvarchar(300), --ID + GUID//RunWater 19982a21f02-d184-499f-a69f-32af77851f83
AddTime DateTime, --FinanceLog 表 AddTime
Type int, --FinanceLog 表 Type
OperationType int --FinanceLog 表 OperationType
)
go
--更新DebtAssignmentCashFlow
create procedure [dbo].[updateDebtAssignmentCashFlow2]
@InsertData Content READONLY --readonly
as
begin
update DebtAssignmentCashFlow set CashType=3
from DebtAssignmentCashFlow as d
inner join @InsertData as t
on d.ID=t.ID
end
go
======================================================================================
--C# 代码示例：
--调用
DataTable data2 = new DataTable();
data2 = Extensions.ToDataTable(newList);
int k = updateDebtAssignmentCashFlow2(data2);
/// <summary>
/// 批量更新DebtAssignmentCashFlow
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private static int updateDebtAssignmentCashFlow2(DataTable dt)
{
bool flg = true;
int c = 0;
try
{
SqlConnection con = new SqlConnection(connString);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "updateDebtAssignmentCashFlow2";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@InsertData", SqlDbType.Structured);
cmd.Parameters[0].Value = dt;
c = cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
EF.BLL.LogHelper.Instance.Log.Info(string.Format("[error]:{0},表DebtAssignmentCashFlow批量更新失败！", ex));
flg = false;
c = -1;
}
return c;
}
如果datable 没有做列的映射的话，列的顺序与自定义表类型的顺序不一致会导致插入失败
这个时候就需要 DataTable 调整列顺序：
dt.Columns["BatchNo"].SetOrdinal(0); dt.Columns["CardCode"].SetOrdinal(1); dt.Columns["PhoneNumber"].SetOrdinal(2);
把 BatchNo 列设置为第一列