private void CompareDBIndex(string tableName, DBIndexCollection modelIndexs,
List <string> addIndexs, List <string> deleteIndexs)
{
Dictionary <string, DBIndexInfo> dbIndexs = GetDBIndex(tableName);
if (modelIndexs != null)
{
foreach (DBIndex item in modelIndexs)
{
string indexName = item.Name.ToUpper();
if (dbIndexs.ContainsKey(indexName))
{
//已存在的索引，判断有否修改
DBIndexInfo dbIndexInfo = dbIndexs[indexName];
dbIndexInfo.IsExist = true;
bool hasChange = false;
if (dbIndexInfo.IndexFields.Count != item.DbIndexFields.Count ||
dbIndexInfo.IsUnique != item.IsUnique)
{
hasChange = true;
}
else
{
for (int i = 0; i < item.DbIndexFields.Count; i++)
{
if (string.Compare(item.DbIndexFields[i].Name, dbIndexInfo.IndexFields[i].Name, true) != 0 ||
item.DbIndexFields[i].IndexOrderWay != dbIndexInfo.IndexFields[i].IndexOrderWay)
{
hasChange = true;
break;
}
}
}
if (hasChange)
{
//索引变更，则先删除，再新增
deleteIndexs.Add(string.Format("drop index {0}", item.Name));
addIndexs.Add(CreateIndexSql(item, tableName));
}
}
else
{
//新增的索引
addIndexs.Add(CreateIndexSql(item, tableName));
}
}
}
foreach (KeyValuePair <string, DBIndexInfo> item in dbIndexs)
{
if (!item.Value.IsExist)
{
//模型已不存在次索引，需要删除
deleteIndexs.Add(string.Format("drop index {0}", item.Key));
}
}
}
protected void BuildDataSetStructure()
{
BuildDataSet();
if (BillType == Template.BillType.Bill || BillType == Template.BillType.Master)
{
DataTable masterTable = this.DataSet.Tables[0];
//DataSourceHelper.AddAttachmentSrcColumn(masterTable);
if (!masterTable.ExtendedProperties.ContainsKey(TableProperty.DBIndex))
{
masterTable.ExtendedProperties.Add(TableProperty.DBIndex, new DBIndexCollection());
}
DBIndexCollection dbIndexs = (DBIndexCollection)masterTable.ExtendedProperties[TableProperty.DBIndex];
dbIndexs.Add(new DBIndex(string.Format("{0}_ID_IDX", masterTable.TableName.ToUpper()), new DBIndexFieldCollection()
{
new DBIndexField("INTERNALID")
}, true));
for (int i = 1; i < this.DataSet.Tables.Count; i++)
{
DataTable table = this.DataSet.Tables[i];
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingAttachment))
{
if (Convert.ToBoolean(table.ExtendedProperties[TableProperty.UsingAttachment]))
{
DataSourceHelper.AddAttachmentSrcColumn(table);
}
}
// 自动构建行项审核需要的数据列
if (table.ExtendedProperties.ContainsKey(TableProperty.UsingApproveRow))
{
if (Convert.ToBoolean(table.ExtendedProperties[TableProperty.UsingApproveRow]))
{
DataSourceHelper.AddApproveRowFixColumn(table);
}
}
}
try
{
if (this.FuncPermission.UseSynchroData)
{
//如果启用数据同步功能，则添加同步到的目标站点的虚拟子表
//if (this.DataSet.Tables.Contains(LibFuncPermission.SynchroDataSettingTableName) == false && LibTemplate.HasAxpLinkSite && LibTemplate.HasSyncDataTable)
if (this.DataSet.Tables.Contains(LibFuncPermission.SynchroDataSettingTableName) == false)
{
DataTable dt = DataSourceHelper.AddSyncDataSettingTable(this.DataSet, LibFuncPermission.SynchroDataSettingTableName);
dt.ExtendedProperties.Add(TableProperty.IsVirtual, true); //设定同步配置数据表为虚表
dt.ExtendedProperties.Add(TableProperty.AllowCopy, false); //设定同步配置数据表不可复制
dt = DataSourceHelper.AddSyncDataHistoryTable(this.DataSet, LibFuncPermission.SynchroDataHisTableName);
dt.ExtendedProperties.Add(TableProperty.IsVirtual, true); //设定同步历史数据表为虚表
dt.ExtendedProperties.Add(TableProperty.AllowCopy, false); //设定同步历史数据表不可复制
}
}
}
catch { }
}
}
protected override void BuildDataSet()
{
this.DataSet = new DataSet();
DataTable masterTable = new DataTable(masterTableName);
DataSourceHelper.AddColumn(new DefineField(masterTable, "NEWSID", "消息代码", FieldSize.Size50));
DataSourceHelper.AddColumn(new DefineField(masterTable, "USERID", "用户账号", FieldSize.Size20));
DataSourceHelper.AddColumn(new DefineField(masterTable, "TITLE", "主题", FieldSize.Size200)
{
DataType = LibDataType.NText, ControlType = LibControlType.NText
});
DataSourceHelper.AddColumn(new DefineField(masterTable, "MAINCONTENT", "主要内容", FieldSize.Size1000)
{
DataType = LibDataType.Binary, ControlType = LibControlType.NText
});
DataSourceHelper.AddColumn(new DefineField(masterTable, "INFOID", "附带信息", FieldSize.Size100)
{
ReadOnly = true
});
DataSourceHelper.AddColumn(new DefineField(masterTable, "CREATETIME", "时间")
{
DataType = LibDataType.Int64, ControlType = LibControlType.DateTime
});
DataSourceHelper.AddColumn(new DefineField(masterTable, "PERSONID", "发送人", FieldSize.Size20)
{
ControlType = LibControlType.IdName,
RelativeSource = new RelativeSourceCollection()
{
new RelativeSource("com.Person")
{
RelFields = new RelFieldCollection()
{
new RelField("PERSONNAME", LibDataType.NText, FieldSize.Size50, "发送人名称")
}
}
}
});
DataSourceHelper.AddColumn(new DefineField(masterTable, "ISREAD", "已读")
{
DataType = LibDataType.Boolean, ControlType = LibControlType.YesNo
});
DBIndexCollection dbList = new DBIndexCollection();
dbList.Add(new DBIndex("NEWS_CREATETIME_IDX", new DBIndexFieldCollection()
{
new DBIndexField("CREATETIME")
}));
masterTable.ExtendedProperties.Add(TableProperty.DBIndex, dbList);
masterTable.PrimaryKey = new DataColumn[] { masterTable.Columns["NEWSID"] };
this.DataSet.Tables.Add(masterTable);
}
public void CreateTable(DataTable table)
{
string tableName = table.TableName;
StringBuilder builder = new StringBuilder();
builder.AppendFormat("CREATE TABLE {0}(", tableName);
foreach (DataColumn col in table.Columns)
{
FieldType fieldType = col.ExtendedProperties.ContainsKey(FieldProperty.FieldType) ? (FieldType)col.ExtendedProperties[FieldProperty.FieldType] : FieldType.None;
if (fieldType == FieldType.None)
{
builder.AppendFormat("{0},", GetFieldInfo(col));
}
}
string pkStr = GetPkStr(table);
builder.AppendLine(string.Format("constraints PK_{0} primary key({1})", tableName, pkStr));
builder.Append(")");
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(builder.ToString());
List <string> sqlList = new List <string>();
DBIndexCollection dbIndexes = table.ExtendedProperties.ContainsKey(TableProperty.DBIndex) ? (DBIndexCollection)table.ExtendedProperties[TableProperty.DBIndex] : null;
if (dbIndexes != null)
{
foreach (DBIndex item in dbIndexes)
{
sqlList.Add(CreateIndexSql(item, tableName));
}
}
if (sqlList.Count > 0)
{
foreach (var sql in sqlList)
{
dataAccess.ExecuteNonQuery(sql);
}
}
}
public void UpdateTable(DataTable table, bool isDelete)
{
LibDataAccess dataAccess = new LibDataAccess();
decimal count = (decimal)dataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) FROM all_tables WHERE TABLE_NAME = {0}", LibStringBuilder.GetQuotString(table.TableName)));
if (count == 0)
{
CreateTable(table);
}
else
{
DataTable dtSchema = null;
using (DbConnection conn = dataAccess.CreateConnection())
{
conn.Open();
dtSchema = conn.GetSchema("Columns", new string[] { null, table.TableName });
}
if (dtSchema != null)
{
Dictionary <string, DbFieldInfo> defaultDic = GetDbFieldInfo(dataAccess, table.TableName);
//对删除的非聚集索引进行处理
StringBuilder tempBuilder = new StringBuilder();
List <string> deleteIndexList = new List <string>();
List <string> addIndexList = new List <string>();
DBIndexCollection dbIndex = table.ExtendedProperties[TableProperty.DBIndex] as DBIndexCollection;
CompareDBIndex(table.TableName, dbIndex, addIndexList, deleteIndexList);
if (deleteIndexList.Count > 0)
{
foreach (var item in deleteIndexList)
{
dataAccess.ExecuteNonQuery(item);
}
}
//再对列进行处理
Dictionary <string, bool> dic = new Dictionary <string, bool>();
foreach (DataColumn col in table.Columns)
{
FieldType fieldType = FieldType.None;
if (col.ExtendedProperties.ContainsKey(FieldProperty.FieldType))
{
fieldType = (FieldType)col.ExtendedProperties[FieldProperty.FieldType];
}
if (fieldType == FieldType.None)
{
dic.Add(col.ColumnName, false);
}
}
foreach (DataRow row in dtSchema.Rows)
{
string columnName = row["COLUMN_NAME"].ToString();
bool isFind = false;
foreach (DataColumn col in table.Columns)
{
if (!dic.ContainsKey(col.ColumnName))
{
continue;
}
if (string.Compare(col.ColumnName, columnName, true) == 0)
{
StringBuilder strBuilder = new StringBuilder();
string dataType = row["DATATYPE"].ToString();
bool hasDiff = HasFieldChanged(col, dataType, defaultDic);
if (hasDiff)
{
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} modify({1})';", table.TableName, GetFieldInfo(col, true)));
}
isFind = true;
dic[col.ColumnName] = true;
break;
}
}
if (isDelete && !isFind)
{
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} drop column {1}';", table.TableName, columnName));
}
}
foreach (var item in dic)
{
if (!item.Value)
{
DataColumn col = table.Columns[item.Key];
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} add({1})';", table.TableName, GetFieldInfo(col, true)));
}
}
if (tempBuilder.Length > 0)
{
StringBuilder testBuild = new StringBuilder();
testBuild.AppendLine("begin");
testBuild.Append(tempBuilder.ToString());
testBuild.AppendLine("end;");
dataAccess.ExecuteNonQuery(testBuild.ToString());
}
//如果主键异动，先删除聚集索引
bool isPkChange = IsPkChange(table.TableName, table.PrimaryKey);
//先执行对于具有唯一性约束的字段的数据更新处理Sql
if (string.IsNullOrEmpty(this.UniqueDataSql) == false && (isPkChange || addIndexList.Count > 0))
{
dataAccess.ExecuteNonQuery(this.UniqueDataSql);
}
//对主键的进行标识
if (isPkChange)
{
string pkStr = GetPkStr(table);
if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select constraint_name from dba_constraints where constraint_name = 'PK_{0}'", table.TableName)))))
{
dataAccess.ExecuteNonQuery(string.Format("alter table {0} drop constraint PK_{0}", table.TableName));
}
//一般情况下如果删除主键约束，索引会自动删除。但是目前有存在未删除的情况，所以确保删除
if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select * from user_ind_columns where INDEX_NAME='PK_{0}'", table.TableName)))))
{
dataAccess.ExecuteNonQuery(string.Format("drop index PK_{0}", table.TableName));
}
dataAccess.ExecuteNonQuery(string.Format("alter table {0} add constraint PK_{0} primary key ({1})", table.TableName, pkStr));
}
//对表的新增非聚集索引进行处理
foreach (var item in addIndexList)
{
dataAccess.ExecuteNonQuery(item);
}
}
}
}
