描述如:列表进行删除(只是页面移除,未更新到数据库).然后保存时处理删除和新增.方便好用

 

 

 

/// <summary>
/// 股东出资情况 保存
/// </summary>
/// <param name="dt"></param>
/// <param name="projectId"></param>
/// <param name="user"></param>
/// <returns></returns>
public bool Shareholder_FundAddOrUpdate(DataTable dt, string projectId, UserSessionEntity user)
{
//思路
//1.把老数据全部查询出来
var strSql = $"select * from Shareholder_Fund where projectId='{ComHelper.SQLPar(projectId)}' and isDel=1";
DataTable data = DbHelperMySQL.QueryTable(strSql);
//2.老数据处理到放到一个集合里面
Dictionary<string, DataRow> rowDic = data?.AsEnumerable().ToDictionary(k => k["ID"].ToString(), v => v);
Dictionary<string, DataRow> tRowDic = new Dictionary<string, DataRow>();
//3.列表新数据处理到放到一个集合里面
tRowDic = dt?.AsEnumerable().Where(a => !string.IsNullOrWhiteSpace(a["ID"]?.ToString())).ToDictionary(k => k["ID"].ToString(), v => v);
//4.遍历老数据集合
foreach (var item in rowDic)
{
//5.判断老数据是否还存在
if (!tRowDic.ContainsKey(item.Key))
{
//6.不存在改变是否删除值的状态
item.Value["IsDel"] = 0;
}
}

//7.循环新列表
foreach (DataRow row in dt.Rows)
{
//8.判断新表格数据.是否在老数据里面存在
if (!rowDic.ContainsKey(row["ID"]?.ToString()))
{
//插入
var newRow = data.NewRow();
newRow["ID"] = row["ID"];
newRow["ProjectId"] = projectId;
newRow["Shareholder_Name"] = row["Shareholder_Name"];
newRow["Partner_Abbreviation"] = row["Partner_Abbreviation"];
newRow["Subscribed_Capital"] = row["Subscribed_Capital"] == DBNull.Value ? 0 : double.Parse(row["Subscribed_Capital"].ToString()) * 10000;
newRow["Paidin_Capital"] = row["Paidin_Capital"] == DBNull.Value ? 0 : double.Parse(row["Paidin_Capital"].ToString()) * 10000;
newRow["Loan_PrincipalTotal"] = row["Loan_PrincipalTotal"] == DBNull.Value ? 0 : double.Parse(row["Loan_PrincipalTotal"].ToString()) * 10000;
newRow["Return_PrincipalTotal"] = row["Return_PrincipalTotal"] == DBNull.Value ? 0 : double.Parse(row["Return_PrincipalTotal"].ToString()) * 10000;
newRow["Borrowings_Total"] = row["Borrowings_Total"] == DBNull.Value ? 0 : double.Parse(row["Borrowings_Total"].ToString()) * 10000;
newRow["OrderNo"] = row["LAY_TABLE_INDEX"];
newRow["IsDel"] = 1;
newRow["CreateTime"] = DateTime.Now;
newRow["CreateUser"] = user.uid;
newRow["UpdateTime"] = DateTime.Now;
newRow["UpdateUser"] = user.uid;
newRow["Remarks"] = row["Remarks"];
data.Rows.Add(newRow);
}
else
{
//9.获取指定键相关的值.
if (rowDic.TryGetValue(row["ID"].ToString(), out DataRow sRow))
{
//修改完值后.OUT 返回新的数据行
sRow["Shareholder_Name"] = row["Shareholder_Name"];
sRow["Partner_Abbreviation"] = row["Partner_Abbreviation"];
sRow["Subscribed_Capital"] = row["Subscribed_Capital"] == DBNull.Value ? 0 : double.Parse(row["Subscribed_Capital"].ToString()) * 10000; ;
sRow["Paidin_Capital"] = row["Paidin_Capital"] == DBNull.Value ? 0 : double.Parse(row["Paidin_Capital"].ToString()) * 10000; ;
sRow["Loan_PrincipalTotal"] = row["Loan_PrincipalTotal"] == DBNull.Value ? 0 : double.Parse(row["Loan_PrincipalTotal"].ToString()) * 10000;
sRow["Return_PrincipalTotal"] = row["Return_PrincipalTotal"] == DBNull.Value ? 0 : double.Parse(row["Return_PrincipalTotal"].ToString()) * 10000;
sRow["Borrowings_Total"] = row["Borrowings_Total"] == DBNull.Value ? 0 : double.Parse(row["Borrowings_Total"].ToString()) * 10000;
sRow["OrderNo"] = row["LAY_TABLE_INDEX"];
sRow["Remarks"] = row["Remarks"];
sRow["UpdateTime"] = DateTime.Now;
sRow["UpdateUser"] = user.uid;
}
}

}
if (data == null || data.Rows.Count < 1)
{
return true;
}
return DbHelperMySQL.MultiUpdateData(data, strSql);
}

 

/// <summary>
/// 批量更新或插入数据
/// </summary>
/// <param name="data"></param>
/// <param name="sqlStr"></param>
/// <param name="setAllValues"></param>
/// <returns></returns>
public static bool MultiUpdateData(DataTable data, string sqlStr, bool setAllValues = true)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{

using (MySqlCommand cmd = new MySqlCommand(sqlStr, connection))
{
try
{
connection.Open();
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = new MySqlCommand(sqlStr, connection);
MySqlCommandBuilder custCB = new MySqlCommandBuilder(myDataAdapter);
custCB.ConflictOption = ConflictOption.OverwriteChanges;
custCB.SetAllValues = setAllValues;
int count = myDataAdapter.Update(data);
data.AcceptChanges();
myDataAdapter.Dispose();
return count > 0;
}
catch
{
connection.Close();
return false;
}
}
}
}