public override string CreateTableSQL(IDataTable table) { String sql = base.CreateTableSQL(table); if (String.IsNullOrEmpty(sql) || table.PrimaryKeys == null || table.PrimaryKeys.Length < 2) { return(sql); } // 处理多主键 String[] names = new String[table.PrimaryKeys.Length]; for (int i = 0; i < table.PrimaryKeys.Length; i++) { names[i] = table.PrimaryKeys[i].Name; } IDataIndex di = ModelHelper.GetIndex(table, names); if (di == null) { di = table.CreateIndex(); di.PrimaryKey = true; di.Unique = true; di.Columns = names; } // Access里面的主键索引名必须叫这个 di.Name = "PrimaryKey"; sql += ";" + Environment.NewLine; sql += CreateIndexSQL(di); return(sql); }
//protected override void CreateTable(StringBuilder sb, IDataTable table, Boolean onlySql) //{ // base.CreateTable(sb, table, onlySql); // if (!onlySql) // { // IDatabase entityDb = null; // foreach (IDataColumn dc in table.Columns) // { // // 如果实体存在默认值,则增加 // if (!String.IsNullOrEmpty(dc.Default)) // { // var tc = Type.GetTypeCode(dc.DataType); // String dv = dc.Default; // // 特殊处理时间 // if (tc == TypeCode.DateTime) // { // if (entityDb != null && dv == entityDb.DateTimeNow) dc.Default = Database.DateTimeNow; // } // // 特殊处理Guid // else if (tc == TypeCode.String || dc.DataType == typeof(Guid)) // { // if (entityDb != null && dv == entityDb.NewGuid) dc.Default = Database.NewGuid; // } // PerformSchema(sb, onlySql, DDLSchema.AddDefault, dc); // // 还原 // dc.Default = dv; // } // } // } //} public override String CreateTableSQL(IDataTable table) { var sql = base.CreateTableSQL(table); var pks = table.PrimaryKeys; if (String.IsNullOrEmpty(sql) || pks.Length < 2) { return(sql); } // 处理多主键 var names = pks.Select(e => e.ColumnName).ToArray(); var di = ModelHelper.GetIndex(table, names); if (di == null) { di = table.CreateIndex(); di.PrimaryKey = true; di.Unique = true; di.Columns = names; } // Access里面的主键索引名必须叫这个 di.Name = "PrimaryKey"; sql += ";" + Environment.NewLine; sql += CreateIndexSQL(di); return(sql); }
private void btnAddIndex_Click(Object sender, EventArgs e) { IDataTable table = GetSelectedTable(); if (table == null) { return; } IDataIndex di = table.CreateIndex(); table.Indexes.Add(di); dgvIndex.DataSource = null; dgvIndex.DataSource = table.Indexes; pgColumn.SelectedObject = di; }
/// <summary>给非主键的自增字段建立唯一索引</summary> /// <param name="table"></param> protected virtual void CreateUniqueIndexForIdentity(IDataTable table) { foreach (var dc in table.Columns) { if (dc.Identity && !dc.PrimaryKey) { var di = table.GetIndex(dc.ColumnName); if (di == null) { di = table.CreateIndex(); di.Columns = new String[] { dc.ColumnName }; di.Computed = true; } // 不管是不是原来有的索引,都要唯一 di.Unique = true; } } }
/// <summary>复制数据表到另一个数据表,复制所有数据列、索引和关系</summary> /// <param name="src"></param> /// <param name="des"></param> /// <param name="resetColumnID">是否重置列ID</param> /// <returns></returns> public static IDataTable CopyAllFrom(this IDataTable src, IDataTable des, Boolean resetColumnID = false) { src.CopyFrom(des); src.Columns.AddRange(des.Columns.Select(i => src.CreateColumn().CopyFrom(i))); src.Indexes.AddRange(des.Indexes.Select(i => src.CreateIndex().CopyFrom(i))); src.Relations.AddRange(des.Relations.Select(i => src.CreateRelation().CopyFrom(i))); // 重载ID //if (resetColumnID) src.Columns.ForEach((it, i) => it.ID = i + 1); if (resetColumnID) { for (int i = 0; i < src.Columns.Count; i++) { src.Columns[i].ID = i + 1; } } return(src); }
/// <summary>给所有关系字段建立索引</summary> /// <param name="table"></param> protected virtual void CreateIndexForRelation(IDataTable table) { foreach (var dr in table.Relations) { // 跳过主键 var dc = table.GetColumn(dr.Column); if (dc == null || dc.PrimaryKey) { continue; } if (table.GetIndex(dr.Column) == null) { var di = table.CreateIndex(); di.Columns = new String[] { dr.Column }; // 这两个的关系,唯一性 di.Unique = dr.Unique; di.Computed = true; table.Indexes.Add(di); } } }
/// <summary>获取索引</summary> /// <param name="table"></param> /// <param name="indexes">索引</param> /// <param name="indexColumns">索引列</param> /// <returns></returns> protected virtual List <IDataIndex> GetIndexes(IDataTable table, DataTable indexes, DataTable indexColumns) { if (indexes == null) { return(null); } var drs = indexes.Select($"{_.TalbeName}='{table.TableName}'"); if (drs == null || drs.Length < 1) { return(null); } var list = new List <IDataIndex>(); foreach (var dr in drs) { if (!TryGetDataRowValue(dr, _.IndexName, out String name)) { continue; } var di = table.CreateIndex(); di.Name = name; if (TryGetDataRowValue(dr, _.ColumnName, out name) && !String.IsNullOrEmpty(name)) { di.Columns = name.Split(','); } else if (indexColumns != null) { String orderby = null; // Oracle数据库用ColumnPosition,其它数据库用OrdinalPosition if (indexColumns.Columns.Contains(_.OrdinalPosition)) { orderby = _.OrdinalPosition; } else if (indexColumns.Columns.Contains(_.ColumnPosition)) { orderby = _.ColumnPosition; } var dics = indexColumns.Select($"{_.TalbeName}='{table.TableName}' And {_.IndexName}='{di.Name}'", orderby); if (dics != null && dics.Length > 0) { var ns = new List <String>(); foreach (var item in dics) { if (TryGetDataRowValue(item, _.ColumnName, out String dcname) && !dcname.IsNullOrEmpty() && !ns.Contains(dcname)) { ns.Add(dcname); } } if (ns.Count < 1) { DAL.WriteLog("表{0}的索引{1}无法取得字段列表!", table, di.Name); } di.Columns = ns.ToArray(); } } if (TryGetDataRowValue(dr, "UNIQUE", out Boolean b)) { di.Unique = b; } if (TryGetDataRowValue(dr, "PRIMARY", out b)) { di.PrimaryKey = b; } else if (TryGetDataRowValue(dr, "PRIMARY_KEY", out b)) { di.PrimaryKey = b; } FixIndex(di, dr); list.Add(di); } return(list != null && list.Count > 0 ? list : null); }
/// <summary>读取</summary> /// <param name="table"></param> /// <param name="reader"></param> /// <returns></returns> public static IDataTable ReadXml(this IDataTable table, XmlReader reader) { // 读属性 if (reader.HasAttributes) { reader.MoveToFirstAttribute(); ReadXml(reader, table); } reader.ReadStartElement(); // 读字段 reader.MoveToElement(); // 有些数据表模型没有字段 if (reader.NodeType == XmlNodeType.Element && reader.Name.EqualIgnoreCase("Table")) { return(table); } while (reader.NodeType != XmlNodeType.EndElement) //while (reader.NodeType == XmlNodeType.Element) { switch (reader.Name) { case "Columns": reader.ReadStartElement(); while (reader.IsStartElement()) { var dc = table.CreateColumn(); var v = reader.GetAttribute("DataType"); if (v != null) { dc.DataType = v.GetTypeEx(false); v = reader.GetAttribute("Length"); if (v != null && Int32.TryParse(v, out var len)) { dc.Length = len; } dc = FixDefaultByType(dc, null); // 清空默认的原始类型,让其从xml读取 dc.RawType = null; } (dc as IXmlSerializable).ReadXml(reader); table.Columns.Add(dc); } reader.ReadEndElement(); // 修正可能的主字段 if (!table.Columns.Any(e => e.Master)) { var f = table.Columns.FirstOrDefault(e => e.Name.EqualIgnoreCase("Name", "Title")); if (f != null) { f.Master = true; } } break; case "Indexes": reader.ReadStartElement(); while (reader.IsStartElement()) { var di = table.CreateIndex(); (di as IXmlSerializable).ReadXml(reader); di.Fix(); table.Indexes.Add(di); } reader.ReadEndElement(); break; case "Relations": reader.ReadStartElement(); reader.Skip(); reader.ReadEndElement(); break; default: // 这里必须处理,否则加载特殊Xml文件时将会导致死循环 reader.Read(); break; } } if (reader.NodeType == XmlNodeType.EndElement) { reader.ReadEndElement(); } // 修正 table.Fix(); return(table); }
/// <summary>给非主键的自增字段建立唯一索引</summary> /// <param name="table"></param> protected virtual void CreateUniqueIndexForIdentity(IDataTable table) { foreach (var dc in table.Columns) { if (dc.Identity && !dc.PrimaryKey) { var di = table.GetIndex(dc.Name); if (di == null) { di = table.CreateIndex(); di.Columns = new String[] { dc.Name }; di.Computed = true; } // 不管是不是原来有的索引,都要唯一 di.Unique = true; } } }
/// <summary>给所有关系字段建立索引</summary> /// <param name="table"></param> protected virtual void CreateIndexForRelation(IDataTable table) { foreach (var dr in table.Relations) { // 跳过主键 var dc = table.GetColumn(dr.Column); if (dc == null || dc.PrimaryKey) continue; if (table.GetIndex(dr.Column) == null) { var di = table.CreateIndex(); di.Columns = new String[] { dr.Column }; // 这两个的关系,唯一性 di.Unique = dr.Unique; di.Computed = true; table.Indexes.Add(di); } } }
public override string CreateTableSQL(IDataTable table) { String sql = base.CreateTableSQL(table); if (String.IsNullOrEmpty(sql) || table.PrimaryKeys == null || table.PrimaryKeys.Length < 2) return sql; // 处理多主键 String[] names = new String[table.PrimaryKeys.Length]; for (int i = 0; i < table.PrimaryKeys.Length; i++) { names[i] = table.PrimaryKeys[i].Name; } IDataIndex di = ModelHelper.GetIndex(table, names); if (di == null) { di = table.CreateIndex(); di.PrimaryKey = true; di.Unique = true; di.Columns = names; } // Access里面的主键索引名必须叫这个 di.Name = "PrimaryKey"; sql += ";" + Environment.NewLine; sql += CreateIndexSQL(di); return sql; }
/// <summary>读取</summary> /// <param name="table"></param> /// <param name="reader"></param> /// <returns></returns> public static IDataTable ReadXml(this IDataTable table, XmlReader reader) { // 读属性 if (reader.HasAttributes) { reader.MoveToFirstAttribute(); ReadXml(reader, table); } reader.ReadStartElement(); // 读字段 reader.MoveToElement(); // 有些数据表模型没有字段 if (reader.NodeType == XmlNodeType.Element && reader.Name.EqualIgnoreCase("Table")) { return(table); } while (reader.NodeType != XmlNodeType.EndElement) //while (reader.NodeType == XmlNodeType.Element) { switch (reader.Name) { case "Columns": reader.ReadStartElement(); var id = 1; while (reader.IsStartElement()) { var dc = table.CreateColumn(); dc.ID = id++; var v = reader.GetAttribute("DataType"); if (v != null) { dc.DataType = v.GetTypeEx(); v = reader.GetAttribute("Length"); var len = 0; if (v != null && Int32.TryParse(v, out len)) { dc.Length = len; } // 含有ID表示是旧的,不需要特殊处理,否则一些默认值会不对 v = reader.GetAttribute("ID"); if (v == null) { dc = Fix(dc, dc); } } (dc as IXmlSerializable).ReadXml(reader); table.Columns.Add(dc); } reader.ReadEndElement(); // 修正可能的主字段 if (!table.Columns.Any(e => e.Master)) { var f = table.Columns.FirstOrDefault(e => e.Name.EqualIgnoreCase("Name", "Title")); if (f != null) { f.Master = true; } } break; case "Indexes": reader.ReadStartElement(); while (reader.IsStartElement()) { var di = table.CreateIndex(); (di as IXmlSerializable).ReadXml(reader); di.Fix(); table.Indexes.Add(di); } reader.ReadEndElement(); break; case "Relations": reader.ReadStartElement(); while (reader.IsStartElement()) { var dr = table.CreateRelation(); (dr as IXmlSerializable).ReadXml(reader); if (table.GetRelation(dr) == null) { table.Relations.Add(dr); } } reader.ReadEndElement(); break; default: // 这里必须处理,否则加载特殊Xml文件时将会导致死循环 reader.Read(); break; } } //if (reader.NodeType != XmlNodeType.Element && reader.NodeType != XmlNodeType.EndElement) reader.Read(); //reader.ReadEndElement(); if (reader.NodeType == XmlNodeType.EndElement) { reader.ReadEndElement(); } return(table); }
public override string CreateTableSQL(IDataTable table) { var sql = base.CreateTableSQL(table); var pks = table.PrimaryKeys; if (String.IsNullOrEmpty(sql) || pks.Length < 2) return sql; // 处理多主键 var names = pks.Select(e => e.ColumnName).ToArray(); var di = ModelHelper.GetIndex(table, names); if (di == null) { di = table.CreateIndex(); di.PrimaryKey = true; di.Unique = true; di.Columns = names; } // Access里面的主键索引名必须叫这个 di.Name = "PrimaryKey"; sql += ";" + Environment.NewLine; sql += CreateIndexSQL(di); return sql; }
/// <summary>获取索引</summary> /// <param name="table"></param> /// <returns></returns> protected virtual List <IDataIndex> GetIndexes(IDataTable table) { if (_indexes == null) { return(null); } DataRow[] drs = _indexes.Select(String.Format("{0}='{1}'", _.TalbeName, table.TableName)); if (drs == null || drs.Length < 1) { return(null); } List <IDataIndex> list = new List <IDataIndex>(); foreach (DataRow dr in drs) { String name = null; if (!TryGetDataRowValue <String>(dr, _.IndexName, out name)) { continue; } IDataIndex di = table.CreateIndex(); di.Name = name; if (TryGetDataRowValue <string>(dr, _.ColumnName, out name) && !String.IsNullOrEmpty(name)) { di.Columns = name.Split(new String[] { "," }, StringSplitOptions.RemoveEmptyEntries); } else if (_indexColumns != null) { String orderby = null; // Oracle数据库用ColumnPosition,其它数据库用OrdinalPosition if (_indexColumns.Columns.Contains(_.OrdinalPosition)) { orderby = _.OrdinalPosition; } else if (_indexColumns.Columns.Contains(_.ColumnPosition)) { orderby = _.ColumnPosition; } DataRow[] dics = _indexColumns.Select(String.Format("{0}='{1}' And {2}='{3}'", _.TalbeName, table.TableName, _.IndexName, di.Name), orderby); if (dics != null && dics.Length > 0) { List <String> ns = new List <string>(); foreach (DataRow item in dics) { String dcname = null; if (TryGetDataRowValue <String>(item, _.ColumnName, out dcname) && !String.IsNullOrEmpty(dcname) && !ns.Contains(dcname)) { ns.Add(dcname); } } if (ns.Count < 1) { DAL.WriteLog("表{0}的索引{1}无法取得字段列表!", table, di.Name); } di.Columns = ns.ToArray(); } } Boolean b = false; if (TryGetDataRowValue <Boolean>(dr, "UNIQUE", out b)) { di.Unique = b; } if (TryGetDataRowValue <Boolean>(dr, "PRIMARY", out b)) { di.PrimaryKey = b; } else if (TryGetDataRowValue <Boolean>(dr, "PRIMARY_KEY", out b)) { di.PrimaryKey = b; } FixIndex(di, dr); list.Add(di); } return(list != null && list.Count > 0 ? list : null); }
/// <summary>获取索引</summary> /// <param name="table"></param> /// <returns></returns> protected virtual List<IDataIndex> GetIndexes(IDataTable table) { if (_indexes == null) return null; DataRow[] drs = _indexes.Select(String.Format("{0}='{1}'", _.TalbeName, table.Name)); if (drs == null || drs.Length < 1) return null; List<IDataIndex> list = new List<IDataIndex>(); foreach (DataRow dr in drs) { String name = null; if (!TryGetDataRowValue<String>(dr, _.IndexName, out name)) continue; IDataIndex di = table.CreateIndex(); di.Name = name; if (TryGetDataRowValue<string>(dr, _.ColumnName, out name) && !String.IsNullOrEmpty(name)) di.Columns = name.Split(new String[] { "," }, StringSplitOptions.RemoveEmptyEntries); else if (_indexColumns != null) { String orderby = null; // Oracle数据库用ColumnPosition,其它数据库用OrdinalPosition if (_indexColumns.Columns.Contains(_.OrdinalPosition)) orderby = _.OrdinalPosition; else if (_indexColumns.Columns.Contains(_.ColumnPosition)) orderby = _.ColumnPosition; DataRow[] dics = _indexColumns.Select(String.Format("{0}='{1}' And {2}='{3}'", _.TalbeName, table.Name, _.IndexName, di.Name), orderby); if (dics != null && dics.Length > 0) { List<String> ns = new List<string>(); foreach (DataRow item in dics) { String dcname = null; if (TryGetDataRowValue<String>(item, _.ColumnName, out dcname) && !String.IsNullOrEmpty(dcname) && !ns.Contains(dcname)) ns.Add(dcname); } if (ns.Count < 1) DAL.WriteDebugLog("表{0}的索引{1}无法取得字段列表!", table, di.Name); di.Columns = ns.ToArray(); } } Boolean b = false; if (TryGetDataRowValue<Boolean>(dr, "UNIQUE", out b)) di.Unique = b; if (TryGetDataRowValue<Boolean>(dr, "PRIMARY", out b)) di.PrimaryKey = b; else if (TryGetDataRowValue<Boolean>(dr, "PRIMARY_KEY", out b)) di.PrimaryKey = b; FixIndex(di, dr); list.Add(di); } return list != null && list.Count > 0 ? list : null; }
/// <summary>读取</summary> /// <param name="table"></param> /// <param name="reader"></param> /// <returns></returns> public static IDataTable ReadXml(this IDataTable table, XmlReader reader) { // 读属性 if (reader.HasAttributes) { reader.MoveToFirstAttribute(); //do //{ // switch (reader.Name) // { // case "ID": // table.ID = reader.ReadContentAsInt(); // break; // case "Name": // table.Name = reader.ReadContentAsString(); // break; // case "Alias": // table.Alias = reader.ReadContentAsString(); // break; // case "Owner": // table.Owner = reader.ReadContentAsString(); // break; // case "DbType": // table.DbType = (DatabaseType)Enum.Parse(typeof(DatabaseType), reader.ReadContentAsString()); // break; // case "IsView": // table.IsView = Boolean.Parse(reader.ReadContentAsString()); // break; // case "Description": // table.Description = reader.ReadContentAsString(); // break; // default: // break; // } //} while (reader.MoveToNextAttribute()); ReadXml(reader, table); } reader.ReadStartElement(); // 读字段 reader.MoveToElement(); while (reader.NodeType != XmlNodeType.EndElement) { switch (reader.Name) { case "Columns": reader.ReadStartElement(); while (reader.IsStartElement()) { var dc = table.CreateColumn(); (dc as IXmlSerializable).ReadXml(reader); table.Columns.Add(dc); } reader.ReadEndElement(); break; case "Indexes": reader.ReadStartElement(); while (reader.IsStartElement()) { var di = table.CreateIndex(); (di as IXmlSerializable).ReadXml(reader); table.Indexes.Add(di); } reader.ReadEndElement(); break; case "Relations": reader.ReadStartElement(); while (reader.IsStartElement()) { var dr = table.CreateRelation(); (dr as IXmlSerializable).ReadXml(reader); if (table.GetRelation(dr) == null) { table.Relations.Add(dr); } } reader.ReadEndElement(); break; default: break; } } //reader.ReadEndElement(); if (reader.NodeType == XmlNodeType.EndElement) { reader.ReadEndElement(); } return(table); }