/// <summary>
/// 支持单主键插入新增数据
/// </summary>
/// <param name="sql"></param>
/// <param name="dt"></param>
/// <returns></returns>
public int SaveNewList(string sql, DataTable dt)
{
if (OpenConnect() == false)
{
return(-1);
}
DataTable insertTab = null;
DataTable newDt = null;
try
{
newDt = new DataTable();
SqlCommand selectCMD = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
sda.Fill(newDt);
DataColumn[] pcols = newDt.PrimaryKey;
if (pcols.Length != 1)
{
return(0);
}
var queryNew =
from rNew in dt.AsEnumerable()
join rOld in newDt.AsEnumerable()
on rNew.Field <Int64>(pcols[0].ColumnName) equals rOld.Field <Int64>(pcols[0].ColumnName)
into JoinedTable
from Addtional in JoinedTable.DefaultIfEmpty()
select new {
newData = rNew, oldData = Addtional
};
insertTab = newDt.Clone();
foreach (var obj in queryNew)
{
if (obj != null && obj.newData != null && obj.oldData == null)
{
insertTab.Rows.Add(obj.newData.ItemArray);
}
}
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
//执行更新
return(sda.Update(insertTab));
//使DataTable保存更新
//dt.AcceptChanges();
}
catch (Exception ce)
{
Log("错误", "保存新增数据错误", string.Format("{0}:{1}", sql, ce.Message));
return(-1);
}
}
/// <summary>
/// 执行非删除以外的所有更新，数据表需要指定唯一的key
/// </summary>
/// <param name="sql"></param>
/// <param name="dt"></param>
/// <returns></returns>
public int UpdateOrNewList(string sql, DataTable dt)
{
object lockobj = new object();
lock (lockobj)
{
if (OpenConnect() == false)
{
return(-1);
}
DataTable insertTab = null;
DataTable updateTab = null;
DataTable oldDt = null;
try
{
oldDt = new DataTable();
SqlCommand selectCMD = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
//上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
sda.Fill(oldDt);
DataColumn[] pcols = oldDt.PrimaryKey;
if (pcols.Length != 1)
{
return(0);
}
var queryNew =
from rNew in dt.AsEnumerable()
join rOld in oldDt.AsEnumerable()
on rNew.Field <Int64?>(pcols[0].ColumnName) equals rOld.Field <Int64?>(pcols[0].ColumnName)
into JoinedTable
from Addtional in JoinedTable.DefaultIfEmpty()
select new
{
newData = rNew,
oldData = Addtional
};
insertTab = oldDt.Clone();
updateTab = oldDt.Clone();
foreach (var obj in queryNew)
{
if (obj != null && obj.newData != null && obj.oldData == null)
{
insertTab.Rows.Add(obj.newData.ItemArray);
}
if (obj != null && obj.newData != null && obj.oldData != null)
{
updateTab.Rows.Add(obj.newData.ItemArray);
}
}
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
//执行更新
oldDt.AcceptChanges();
DataTable dtNew = oldDt.Clone();
for (int i = 0; i < updateTab.Rows.Count; i++)
{
DataRow dr = updateTab.Rows[i];
for (int j = 0; j < oldDt.Rows.Count; j++) //遍历原表中所有记录
{
if (dr[pcols[0].ColumnName].Equals(oldDt.Rows[j][pcols[0]])) //主键相等
{
oldDt.Rows[j].SetModified();
//oldDt.Rows[j].ItemArray = dr.ItemArray;//用新表中替代
for (int c = 0; c < oldDt.Columns.Count; c++)
{
if (oldDt.Columns[c].ColumnName.Equals(pcols[0].ColumnName))//主键跳过
{
continue;
}
oldDt.Rows[j][oldDt.Columns[c].ColumnName] = dr[oldDt.Columns[c].ColumnName];
}
break;
}
}
}
int SameRet = sda.Update(oldDt);
int NewRet = sda.Update(insertTab);
return(SameRet + NewRet);
//使DataTable保存更新
//dt.AcceptChanges();
}
catch (Exception ce)
{
Log("错误", "新增/更新数据错误", string.Format("{0}:{1}", sql, ce.StackTrace));
return(-1);
}
}
}