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); } } } }