void LoadOrderItem(DataSet dataSet, DB.TableInfo tableInfo, SqlConnection conn)
{
try
{
List <DB.RunningSet> Runnings = new List <DB.RunningSet>();
if (tableInfo.Childs != null)
{
foreach (var child in tableInfo.Childs)
{
Runnings.Add(new DB.RunningSet(child));
}
}
foreach (var run in Runnings)
{
DB.ChildInfo info = run.childInfo;
run.adapter = new SqlDataAdapter("Select * From [" + info.Name + "]", conn);
run.table = new DataTable(info.Name);
dataSet.Tables.Add(run.table);
run.childType = DB.GetKeyType(info.PrimaryKeys[0].DbType);
run.adapter.Fill(dataSet, info.Name);
run.relation = new DataRelation(info.ForeignKeyName, dataSet.Tables["Order"].Columns[info.FatherKey], run.table.Columns[info.ChildKey]);
dataSet.Relations.Add(run.relation);
}
}
catch { }
}
private bool BuildChildInfo(string fatherName, string childName, Dictionary <string, List <DB.SqlColumnStruct> > dicStruct, Dictionary <string, DB.TableInfo> dicTableInfo,
List <DB.ForeignKey> listForeignKey, out DB.TableInfo fatherInfo)
{
if (dicTableInfo.TryGetValue(fatherName, out fatherInfo))
{
if (fatherInfo.Childs == null)
{
fatherInfo.Childs = new List <DB.ChildInfo>();
}
DB.ChildInfo child = new DB.ChildInfo(childName);
dicStruct.TryGetValue(childName, out child.Struct);
var fks = from fk in listForeignKey where fk.FatherTable == fatherName && fk.ChildTable == childName select fk;
if (fks.Count() > 0)
{
DB.ForeignKey fk = fks.First();
child.ForeignKeyName = fk.KeyName;
child.FatherKey = fk.FatherKey;
child.ChildKey = fk.ChildKey;
child.DeleteAction = fk.DeleteAction;
child.UpdateAction = fk.UpdateAction;
var keys = from ke in child.Struct where ke.IsPrimaryKey select ke;
if (keys.Count() > 0)
{
child.PrimaryKeys = new List <DB.SqlColumnStruct>();
foreach (var key in keys)
{
child.PrimaryKeys.Add(key);
}
}
}
fatherInfo.Childs.Add(child);
}
return(true);
}
private bool SameKeyExist(DataRow source, List <DataRow> dests, DB.ChildInfo info, out DataRow dest)
{
dest = null;
if (dests == null)
{
return(false);
}
if (dests.Count() == 0)
{
return(false);
}
List <DB.SqlColumnStruct> Keys = info.PrimaryKeys;
if (Keys.Count() == 0)
{
return(false);
}
try
{
DB.SqlColumnStruct key = Keys.First();
object sourceKey = source[key.Name];
IEnumerable <DataRow> rows;
switch (key.DbType)
{
case SqlDbType.UniqueIdentifier:
Guid guidKey = (Guid)sourceKey;
rows = from r in dests where guidKey == (Guid)r[key.Name] select r;
break;
case SqlDbType.Int:
int intKey = (int)sourceKey;
rows = from r in dests where intKey == (int)r[key.Name] select r;
break;
case SqlDbType.BigInt:
Int64 int64Key = (Int64)sourceKey;
rows = from r in dests where int64Key == (Int64)r[key.Name] select r;
break;
case SqlDbType.TinyInt:
byte byteKey = (byte)sourceKey;
rows = from r in dests where byteKey == (byte)r[key.Name] select r;
break;
case SqlDbType.SmallInt:
Int16 int16Key = (Int16)sourceKey;
rows = from r in dests where int16Key == (Int16)r[key.Name] select r;
break;
default: return(false);
}
if (rows.Count() == 0)
{
return(false);
}
if (Keys.Count() == 1)
{
dest = rows.First();
return(true);
}
if (Keys.Count() > 2)
{
return(false);
}
var key2 = Keys.Last();
var sourceKey2 = source[key2.Name];
IEnumerable <DataRow> rows2;
switch (key2.DbType)
{
case SqlDbType.UniqueIdentifier:
Guid guidKey = (Guid)sourceKey2;
rows2 = from r in rows where guidKey == (Guid)r[key2.Name] select r;
break;
case SqlDbType.Int:
int intKey = (int)sourceKey2;
rows2 = from r in rows where intKey == (int)r[key2.Name] select r;
break;
case SqlDbType.BigInt:
Int64 int64Key = (Int64)sourceKey2;
rows2 = from r in rows where int64Key == (Int64)r[key2.Name] select r;
break;
case SqlDbType.TinyInt:
byte byteKey = (byte)sourceKey2;
rows2 = from r in rows where byteKey == (byte)r[key2.Name] select r;
break;
case SqlDbType.SmallInt:
Int16 int16Key = (Int16)sourceKey2;
rows2 = from r in rows where int16Key == (Int16)r[key2.Name] select r;
break;
default: return(false);
}
if (rows2.Count() == 0)
{
return(false);
}
dest = rows2.First();
return(true);
}
catch (Exception ex)
{
Message("程式或資料錯誤！<SameKeyExist>[" + source.Table.TableName + "] 原因:" + ex.Message);
throw ex;
}
}
private bool UpdateRealDataByMd5Result(string tableName, string msgDest, SqlConnection serverConnDest, DB.TableInfo tableInfoSource, DB.TableInfo tableInfoDest,
DataSet dataSetSource, DataSet dataSetDest, Dictionary <Guid, DB.Md5Result> dicMd5ResultSource)
{
DataTable dataTableSource = dataSetSource.Tables[tableName];
DataTable dataTableDest = dataSetDest.Tables[tableName];
var changedMd5Result = (from mr in dicMd5ResultSource.Values where mr.Status != DB.RowStatus.Unchanged select mr).ToList();
bool notAllowCloudToLocal = !AllowCloudToLocal(tableName);
int totalCount = changedMd5Result.Count();
int addCount = 0;
var Runnings = new List <DB.RunningSet>();
LOOP: // 要Update的太大時,分筆寫出
if (changedMd5Result == null)
{
return(true);
}
List <DB.Md5Result> listChanged = new List <DB.Md5Result>();
if (notAllowCloudToLocal) // 不從雲端更新本地的檔案, dataSetDest值沒算過MD5,所以沒有被載入
{
if (tableInfoDest.PrimaryKeys == null || tableInfoDest.PrimaryKeys.Count == 0 || tableInfoDest.PrimaryKeys.Count > 1)
{
Message("規定從本地=>雲端的[" + tableName + "]資料表, 只支援一個PrimaryKey! 本表同步停止!(UpdateRealDataByMd5Result)");
return(false);
}
string pkName = tableInfoDest.PrimaryKeys[0].Name;
DB.SqlColumnStruct pkDefine = tableInfoDest.PrimaryKeys[0];
DB.PrimaryKeyType keyType = DB.GetKeyType(pkDefine.DbType);
if (keyType != DB.PrimaryKeyType.IntCombined && keyType != DB.PrimaryKeyType.UniqueIdentifier)
{
Message("規定從本地=>雲端的[" + tableName + "]資料表, PrimaryKey只支援整數類或UniqueIdentifier! 本表同步停止!(UpdateRealDataByMd5Result)");
return(false);
}
if (dataTableDest == null)
{
dataTableDest = new DataTable(tableName);
dataSetDest.Tables.Add(dataTableDest);
}
if (changedMd5Result.Count() > 0)
{
int i = 0;
StringBuilder sb = new StringBuilder("Select * From [" + tableName + "]"); // 寫Where
foreach (var r in changedMd5Result)
{
if (i == 0)
{
sb.Append(" Where ");
}
else
{
sb.Append(" Or ");
}
sb.Append(pkName); sb.Append("=");
sb.Append(GuidPrimaryKeyToString(r.PrimaryKey, pkDefine));
listChanged.Add(r);
if (++i >= 100)
{
break;
}
}
foreach (var r in listChanged)
{
changedMd5Result.Remove(r);
}
//foreach(var re in changedMd5Result)
// tableInfoDest.
SqlDataAdapter adapterNow = new SqlDataAdapter(sb.ToString(), serverConnDest);
adapterNow.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapterNow.Fill(dataSetDest, tableName);
if (tableInfoDest.Childs != null)
{
if (Runnings.Count == 0)
{
foreach (var child in tableInfoDest.Childs)
{
Runnings.Add(new DB.RunningSet(child));
}
foreach (var run in Runnings)
{
DB.ChildInfo info = run.childInfo;
run.table = new DataTable(info.Name);
dataSetDest.Tables.Add(run.table);
run.childType = DB.GetKeyType(info.PrimaryKeys[0].DbType);
}
}
foreach (var run in Runnings)
{
DB.ChildInfo info = run.childInfo;
string pkName1 = info.ChildKey;
var pkDefine1 = (from st in info.Struct where st.Name == pkName1 select st).First();
DB.PrimaryKeyType keyType1 = DB.GetKeyType(pkDefine1.DbType);
if (keyType1 != DB.PrimaryKeyType.IntCombined && keyType1 != DB.PrimaryKeyType.UniqueIdentifier)
{
Message("規定從本地=>雲端的[" + tableName + "]資料表, PrimaryKey只支援整數類或UniqueIdentifier! 本表同步停止!(UpdateRealDataByMd5Result)");
return(false);
}
int i1 = 0;
StringBuilder sb1 = new StringBuilder("Select * From [" + info.Name + "]");
foreach (var r in listChanged)
{
if (i1 == 0)
{
sb1.Append(" Where ");
}
else
{
sb1.Append(" Or ");
}
sb1.Append(pkName1); sb1.Append("=");
sb1.Append(GuidPrimaryKeyToString(r.PrimaryKey, pkDefine1));
if (++i1 >= 100)
{
break;
}
}
run.adapter = new SqlDataAdapter(sb1.ToString(), serverConnDest);
run.adapter.Fill(dataSetDest, info.Name);
if (run.relation == null) // 第一次Fill以後才有Cloums,才能加Relation
{
run.relation = new DataRelation(info.ForeignKeyName, dataTableDest.Columns[info.FatherKey], run.table.Columns[info.ChildKey]);
dataSetDest.Relations.Add(run.relation);
}
}
}
}
}
else
{ // 可以二邊同步的檔案都小,全部一起來
listChanged = changedMd5Result;
changedMd5Result = null;
}
foreach (var re in listChanged)
{ // 自Source取資料更新Dest端真實檔案
try
{
DataRow sourceRow = GetRowFromGuidPrimaryKey(re.PrimaryKey, dataTableSource, tableInfoSource);
DataRow destRow = GetRowFromGuidPrimaryKey(re.PrimaryKey, dataTableDest, tableInfoDest);
var colStruct = tableInfoSource.Struct; // 雲端和本地一定相同,前面己經比對過,所以用同一colStruct
switch (re.Status)
{
case DB.RowStatus.New:
case DB.RowStatus.Changed:
if (sourceRow == null)
{
continue; // 應該不可能
}
if (destRow == null)
{
destRow = dataTableDest.NewRow();
CopyRow(sourceRow, destRow, colStruct);
dataTableDest.Rows.Add(destRow);
if (!ProcessChildsRows(tableName, msgDest, dataSetDest, sourceRow, destRow, tableInfoSource, tableInfoDest))
{
return(false);
}
// 新增在Update時要先放主表,再插入副表
}
else
{ // 分三部分, Source有Dest沒有要新增, Source沒有Dest有要Delete, 二個都有的要覆蓋Dest(覆蓋不好做)
CopyRow(sourceRow, destRow, colStruct);
if (!ProcessChildsRows(tableName, msgDest, dataSetDest, sourceRow, destRow, tableInfoSource, tableInfoDest))
{
return(false);
}
}
break;
case DB.RowStatus.Deleted:
if (destRow != null)
{
if (tableInfoDest.Childs != null)
{
foreach (var child in tableInfoDest.Childs)
{
var childRows = destRow.GetChildRows(child.ForeignKeyName); // 前面CalcMd5應該己經建立Relation
DataTable tab = dataSetDest.Tables[child.Name];
foreach (var childRow in childRows)
{
tab.Rows.Remove(childRow); // 先刪子表,再刪總表, 規定Delete Cascade, 所以先將Deleted的ChildRow Remove就好
}
}
}
destRow.Delete();
}
break;
default:
Message("處理 =>" + msgDest + "[" + tableName + "]時出錯,本表同步停止,原因:" + re.Status.ToString() + "我不認得");
return(false); // 不可能,出錯了
}
}
catch (Exception ex)
{
Message("處理 =>" + msgDest + "[" + tableName + "]時出錯,本表同步停止,原因:" + ex.Message);
return(false);
}
}
addCount += listChanged.Count;
ShowStatus("更新" + msgDest + "[" + tableName + "]資料 " + addCount.ToString() + "/" + totalCount.ToString() + "筆!");
if (!UpdateData(msgDest, tableName, dataSetDest, serverConnDest, tableInfoDest))
{
return(false);
}
if (changedMd5Result != null && changedMd5Result.Count() > 0)
{
goto LOOP;
}
return(true);
}
public RunningSet(DB.ChildInfo info)
{
childInfo = info;
}