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