public void AddRow(string tableName, IDataColumn[] columns)
{
if (columns.Length > 0) {
bool localConnection = false;
if (ConnectionState == System.Data.ConnectionState.Closed) {
localConnection = true;
OpenConnection();
}
try {
string command = "INSERT INTO " + tableName;
string columnsToAdd = columns[0].Name;
string valuesToAdd = "\'" + VerifyValueString(columns[0].Value.ToString()) + "\'";
for (int i = 1; i < columns.Length; i++) {
columnsToAdd += ", " + columns[i].Name;
valuesToAdd += ", \'" + VerifyValueString(columns[i].Value.ToString()) + "\'";
}
command += " (" + columnsToAdd + ") VALUES (" + valuesToAdd + ")";
using (SQLiteCommand comm = new SQLiteCommand(command, connection)) {
comm.ExecuteScalar();
}
} finally {
if (localConnection) {
CloseConnection();
}
}
}
}
public AddField(IDataColumn dc,bool isNew)
{
InitializeComponent();
IsNew = isNew;
combRawType.DataSource = PrimitiveType.TypeList;
//combRawType.DisplayMember = "Name";
//combRawType.ValueMember = "DataType";
DataColumn = dc;
//修改的话，直接绑定数据到文本框
if(!IsNew) BandText ();
}
// Bans a player
public static void BanPlayer(DatabaseConnection dbConnection, string ip, string bannedID, string bannedAccount,
string bannedMac, string bannerID, string bannerIP, string unbanDate, Enums.BanType banType)
{
IDataColumn[] columns = new IDataColumn[] {
dbConnection.Database.CreateColumn(true, "BannedPlayerID", bannedID),
dbConnection.Database.CreateColumn(false, "BannedPlayerAccount", bannedAccount),
dbConnection.Database.CreateColumn(false, "BannedPlayerIP", ip),
dbConnection.Database.CreateColumn(false, "BannedPlayerMac", bannedMac),
dbConnection.Database.CreateColumn(false, "BannerPlayerID", bannerID),
dbConnection.Database.CreateColumn(false, "BannerPlayerIP",bannerIP),
dbConnection.Database.CreateColumn(false, "BannedDate", DateTime.Now.ToString()),
dbConnection.Database.CreateColumn(false, "UnbanDate", unbanDate),
dbConnection.Database.CreateColumn(false, "BanType", ((int)banType).ToString())
};
dbConnection.Database.AddRow("bans", columns);
}
public override string DropDefaultSQL(IDataColumn field)
{
return(String.Empty);
}
public override String AlterColumnSQL(IDataColumn field, IDataColumn oldfield)
{
return(String.Format("Alter Table {0} Modify Column {1}", FormatName(field.Table.TableName), FieldClause(field, false)));
}
/// <summary>猜测表间关系</summary>
/// <param name="table"></param>
/// <param name="rtable"></param>
/// <param name="rname"></param>
/// <param name="column"></param>
/// <param name="name"></param>
/// <returns></returns>
public virtual Boolean GuessRelation(IDataTable table, IDataTable rtable, String rname, IDataColumn column, String name)
{
if (name.Length <= rtable.Name.Length || !name.StartsWith(rtable.Name, StringComparison.OrdinalIgnoreCase)) return false;
var key = name.Substring(rtable.Name.Length);
var dc = rtable.GetColumn(key);
// 猜测两表关联关系时，两个字段的类型也必须一致
if (dc == null || dc.DataType != column.DataType) return false;
// 建立关系
var dr = table.CreateRelation();
dr.Column = column.Name;
dr.RelationTable = rtable.Name;
dr.RelationColumn = dc.Name;
// 表关系这里一般是多对一，比如管理员的RoleID=>Role+Role.ID，对于索引来说，不是唯一的
dr.Unique = false;
// 当然，如果这个字段column有唯一索引，那么，这里也是唯一的。这就是典型的一对一
if (column.PrimaryKey || column.Identity)
dr.Unique = true;
else
{
var di = table.GetIndex(column.Name);
if (di != null && di.Unique) dr.Unique = true;
}
dr.Computed = true;
if (table.GetRelation(dr) == null) table.Relations.Add(dr);
// 给另一方建立关系
//foreach (IDataRelation item in rtable.Relations)
//{
// if (item.Column == dc.Name && item.RelationTable == table.Name && item.RelationColumn == column.Name) return dr;
//}
if (rtable.GetRelation(dc.Name, table.Name, column.Name) != null) return true;
dr = rtable.CreateRelation();
dr.Column = dc.Name;
dr.RelationTable = table.Name;
dr.RelationColumn = column.Name;
// 那么这里就是唯一的啦
dr.Unique = true;
// 当然，如果字段dc不是主键，也没有唯一索引，那么关系就不是唯一的。这就是典型的多对多
if (!dc.PrimaryKey && !dc.Identity)
{
var di = rtable.GetIndex(dc.Name);
// 没有索引，或者索引不是唯一的
if (di == null || !di.Unique) dr.Unique = false;
}
dr.Computed = true;
if (rtable.GetRelation(dr) == null) rtable.Relations.Add(dr);
return true;
}
public override string AlterColumnSQL(IDataColumn field, IDataColumn oldfield)
{
return String.Format("Alter Table {0} Modify Column {1}", FormatName(field.Table.Name), FieldClause(field, false));
}
protected override string GetFieldType(IDataColumn field)
{
if (field.DataType == typeof(Boolean)) return "enum('N','Y')";
return base.GetFieldType(field);
}
/// <summary>格式化标识列，返回插入数据时所用的表达式，如果字段本身支持自增，则返回空</summary>
/// <param name="field">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatIdentity(IDataColumn field, Object value) { return null; }
public virtual Boolean DropDefault(IDataColumn field)
{
return AddDefault(field, null);
}
/// <summary>
/// Raises an exception if two columns do not have the same
/// shape or are two much different.
/// </summary>
/// <param name="col">columns</param>
/// <param name="precision">precision</param>
/// <param name="exc">raises an exception if too different</param>
/// <returns>max difference</returns>
public double AssertAlmostEqual(IDataColumn col, double precision = 1e-5, bool exc = true)
{
var colt = (col is NumericColumn ? (col as NumericColumn).Column : col) as DataColumn <DType>;
if (colt is null)
{
throw new DataValueError(string.Format("Column types are different {0} != {1}",
GetType(), col.GetType()));
}
if (Length != colt.Length)
{
throw new DataValueError(string.Format("Column have different length {0} != {1}",
Length, colt.Length));
}
if (Kind.IsVector())
{
double oks = 0;
for (int i = 0; i < Length; ++i)
{
switch (Kind.ItemType().RawKind())
{
case DataKind.Boolean:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <bool>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <bool>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.Int32:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <int>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <int>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.UInt32:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <uint>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <uint>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.Int64:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <Int64>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <Int64>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.Single:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <float>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <float>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.Double:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <double>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <double>[])[i].DenseValues().ToArray(),
precision, exc);
break;
case DataKind.String:
oks += NumericHelper.AssertAlmostEqual((_data as VBufferEqSort <DvText>[])[i].DenseValues().ToArray(),
(colt._data as VBufferEqSort <DvText>[])[i].DenseValues().ToArray(),
precision, exc);
break;
default:
throw new DataTypeError($"Unable to handle kind '{Kind}'");
}
}
return(oks);
}
else
{
switch (Kind.RawKind())
{
case DataKind.Boolean:
return(NumericHelper.AssertAlmostEqual(_data as bool[], colt._data as bool[], precision, exc, Length, colt.Length));
case DataKind.Int32:
return(NumericHelper.AssertAlmostEqual(_data as int[], colt._data as int[], precision, exc, Length, colt.Length));
case DataKind.UInt32:
return(NumericHelper.AssertAlmostEqual(_data as uint[], colt._data as uint[], precision, exc, Length, colt.Length));
case DataKind.Int64:
return(NumericHelper.AssertAlmostEqual(_data as long[], colt._data as long[], precision, exc, Length, colt.Length));
case DataKind.Single:
return(NumericHelper.AssertAlmostEqual(_data as float[], colt._data as float[], precision, exc, Length, colt.Length));
case DataKind.Double:
return(NumericHelper.AssertAlmostEqual(_data as double[], colt._data as double[], precision, exc, Length, colt.Length));
case DataKind.String:
return(NumericHelper.AssertAlmostEqual(_data as DvText[], colt._data as DvText[], precision, exc, Length, colt.Length));
default:
throw new DataTypeError($"Unable to handle kind '{Kind}'");
}
}
}
/// <summary>扩展查询</summary>
protected virtual void BuildExtendSearch()
{
WriteLine("#region 扩展查询");
// 主键
IDataColumn pk = null;
if (Table.PrimaryKeys.Length == 1)
{
pk = Table.PrimaryKeys[0];
var name = pk.Name.ToLower();
WriteLine("/// <summary>根据{0}查找</summary>", pk.DisplayName);
WriteLine("/// <param name=\"{0}\">{1}</param>", name, pk.DisplayName);
WriteLine("/// <returns>实体对象</returns>");
WriteLine("public static {3} FindBy{0}({1} {2})", pk.Name, pk.DataType.Name, name, GenericType ? "TEntity" : Table.Name);
WriteLine("{");
{
if (pk.DataType.IsInt())
{
WriteLine("if ({0} <= 0) return null;", name);
}
else if (pk.DataType == typeof(String))
{
WriteLine("if ({0}.IsNullOrEmpty()) return null;", name);
}
WriteLine();
WriteLine("// 实体缓存");
WriteLine("if (Meta.Session.Count < 1000) return Meta.Cache.Find(e => e.{0} == {1});", pk.Name, name);
WriteLine();
WriteLine("// 单对象缓存");
WriteLine("//return Meta.SingleCache[{0}];", name);
WriteLine();
WriteLine("return Find(_.{0} == {1});", pk.Name, name);
}
WriteLine("}");
}
// 索引
foreach (var di in Table.Indexes)
{
// 跳过主键
if (di.Columns.Length == 1 && pk != null && di.Columns[0].EqualIgnoreCase(pk.Name, pk.ColumnName))
{
continue;
}
var cs = Table.GetColumns(di.Columns);
if (cs == null || cs.Length != di.Columns.Length)
{
continue;
}
// 只有整数和字符串能生成查询函数
if (!cs.All(e => e.DataType.IsInt() || e.DataType == typeof(String)))
{
continue;
}
WriteLine();
WriteLine("/// <summary>根据{0}查找</summary>", cs.Select(e => e.DisplayName).Join("、"));
foreach (var dc in cs)
{
WriteLine("/// <param name=\"{0}\">{1}</param>", dc.Name.ToLower(), dc.DisplayName);
}
// 返回类型
var rt = GenericType ? "TEntity" : Table.Name;
if (!di.Unique)
{
rt = "IList<{0}>".F(rt);
}
WriteLine("/// <returns>{0}</returns>", di.Unique ? "实体对象" : "实体列表");
WriteLine("public static {2} Find{3}By{0}({1})", cs.Select(e => e.Name).Join("And"), cs.Select(e => e.DataType.Name + " " + e.Name.ToLower()).Join(", "), rt, di.Unique ? "" : "All");
WriteLine("{");
{
var exp = new StringBuilder();
var wh = new StringBuilder();
foreach (var dc in cs)
{
if (exp.Length > 0)
{
exp.Append(" & ");
}
exp.AppendFormat("_.{0} == {1}", dc.Name, dc.Name.ToLower());
if (wh.Length > 0)
{
wh.Append(" && ");
}
wh.AppendFormat("e.{0} == {1}", dc.Name, dc.Name.ToLower());
}
if (di.Unique)
{
WriteLine("// 实体缓存");
WriteLine("if (Meta.Session.Count < 1000) return Meta.Cache.Find(e => {0});", wh);
// 单对象缓存
if (cs.Length == 1 && cs[0].Master)
{
WriteLine();
WriteLine("// 单对象缓存");
WriteLine("//return Meta.SingleCache.GetItemWithSlaveKey({0}) as {1};", cs[0].Name.ToLower(), rt);
}
WriteLine();
WriteLine("return Find({0});", exp);
}
else
{
WriteLine("// 实体缓存");
WriteLine("if (Meta.Session.Count < 1000) return Meta.Cache.FindAll(e => {0});", wh);
WriteLine();
WriteLine("return FindAll({0});", exp);
}
}
WriteLine("}");
}
WriteLine("#endregion");
}
/// <summary>根据类型修正字段的一些默认值</summary>
/// <param name="dc"></param>
/// <param name="oridc"></param>
/// <returns></returns>
static IDataColumn Fix(this IDataColumn dc, IDataColumn oridc)
{
if (dc?.DataType == null)
{
return(dc);
}
var isnew = oridc == null || oridc == dc;
switch (dc.DataType.GetTypeCode())
{
case TypeCode.Boolean:
dc.RawType = "bit";
dc.Nullable = false;
break;
case TypeCode.Byte:
case TypeCode.Char:
case TypeCode.SByte:
dc.RawType = "tinyint";
dc.Nullable = false;
break;
case TypeCode.DateTime:
dc.RawType = "datetime";
dc.Nullable = true;
break;
case TypeCode.Int16:
case TypeCode.UInt16:
dc.RawType = "smallint";
dc.Nullable = false;
break;
case TypeCode.Int32:
case TypeCode.UInt32:
dc.RawType = "int";
dc.Nullable = false;
break;
case TypeCode.Int64:
case TypeCode.UInt64:
dc.RawType = "bigint";
dc.Nullable = false;
break;
case TypeCode.Single:
dc.RawType = "real";
dc.Nullable = false;
break;
case TypeCode.Double:
dc.RawType = "float";
dc.Nullable = false;
break;
case TypeCode.Decimal:
dc.RawType = "money";
dc.Nullable = false;
break;
case TypeCode.String:
if (dc.Length >= 0 && dc.Length < 4000 || !isnew && oridc.RawType != "ntext")
{
var len = dc.Length;
if (len == 0)
{
len = 50;
}
dc.RawType = String.Format("nvarchar({0})", len);
// 新建默认长度50，写入忽略50的长度，其它长度不能忽略
if (len == 50)
{
dc.Length = 50;
}
else
{
dc.Length = 0;
}
}
else
{
// 新建默认长度-1，写入忽略所有长度
if (isnew)
{
dc.RawType = "ntext";
dc.Length = -1;
}
else
{
// 写入长度-1
dc.Length = 0;
oridc.Length = -1;
// 不写RawType
dc.RawType = oridc.RawType;
}
}
dc.Nullable = true;
break;
default:
break;
}
dc.DataType = null;
/*if (oridc.Table.DbType != DatabaseType.SqlServer)*/
dc.RawType = null;
return(dc);
}
/// <summary>生成每一项</summary>
protected override void BuildItem(IDataColumn column)
{
var dc = column;
var type = dc.Properties["Type"];
if (type.IsNullOrEmpty())
{
type = dc.DataType?.Name;
}
// 字段
WriteLine("private {0} _{1};", type, dc.Name);
// 注释
var des = dc.Description;
WriteLine("/// <summary>{0}</summary>", des);
// 附加特性
if (dc.Properties.TryGetValue("Attribute", out var att))
{
WriteLine("[{0}]", att.Replace("{name}", dc.Name));
}
if (!Option.Pure)
{
var dis = dc.DisplayName;
if (!dis.IsNullOrEmpty())
{
WriteLine("[DisplayName(\"{0}\")]", dis);
}
if (!des.IsNullOrEmpty())
{
WriteLine("[Description(\"{0}\")]", des);
}
}
WriteLine("[DataObjectField({0}, {1}, {2}, {3})]", dc.PrimaryKey.ToString().ToLower(), dc.Identity.ToString().ToLower(), dc.Nullable.ToString().ToLower(), dc.Length);
// 支持生成带精度的特性
if (!dc.ItemType.IsNullOrEmpty())
{
WriteLine("[BindColumn(\"{0}\", \"{1}\", \"{2}\", ItemType = \"{3}\")]", dc.ColumnName, dc.Description, dc.RawType, dc.ItemType);
}
else if (dc.Precision > 0 || dc.Scale > 0)
{
WriteLine("[BindColumn(\"{0}\", \"{1}\", \"{2}\", Precision = {3}, Scale = {4})]", dc.ColumnName, dc.Description, dc.RawType, dc.Precision, dc.Scale);
}
else
{
WriteLine("[BindColumn(\"{0}\", \"{1}\", \"{2}\"{3})]", dc.ColumnName, dc.Description, dc.RawType, dc.Master ? ", Master = true" : "");
}
if (Option.Interface)
{
WriteLine("{0} {1} {{ get; set; }}", type, dc.Name);
}
else
{
WriteLine("public {0} {1} {{ get => _{1}; set {{ if (OnPropertyChanging(\"{1}\", value)) {{ _{1} = value; OnPropertyChanged(\"{1}\"); }} }} }}", type, dc.Name);
}
}
/// <summary>格式化标识列，返回插入数据时所用的表达式，如果字段本身支持自增，则返回空</summary>
/// <param name="field">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatIdentity(IDataColumn field, Object value)
{
return(null);
}
protected void gvTable_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
GridView gv = sender as GridView;
Type type = null;
if (gv.DataSource is DataSet || gv.DataSource is DataTable)
{
String sql = txtSql.Text;
if (String.IsNullOrEmpty(sql))
{
return;
}
sql = sql.Trim();
if (String.IsNullOrEmpty(sql))
{
return;
}
DAL dal = GetDAL();
if (dal == null)
{
return;
}
String tableName = ddlTable.SelectedValue;
String fsql = String.Format("Select * From {0}", dal.Db.FormatName(tableName));
if (!sql.ToLower().StartsWith(fsql.ToLower()))
{
return;
}
IDataTable table = dal.Tables.Find(delegate(IDataTable item) { return(item.TableName == ddlTable.SelectedValue); });
if (table == null)
{
return;
}
// 更新表头
foreach (TableCell item in e.Row.Cells)
{
String name = item.Text;
if (String.IsNullOrEmpty(name))
{
continue;
}
//XField field = table.Fields.Find(delegate(XField elm) { return elm.Name == name; });
//if (field == null) continue;
IDataColumn field = null;
foreach (IDataColumn elm in table.Columns)
{
if (elm.ColumnName == name)
{
field = elm;
break;
}
}
if (field == null)
{
continue;
}
if (!String.IsNullOrEmpty(field.Description))
{
item.Text = field.Description;
}
}
}
else
{
IEnumerable ie = gv.DataSource as IEnumerable;
if (ie == null)
{
return;
}
IEnumerator iet = ie.GetEnumerator();
if (!iet.MoveNext())
{
return;
}
type = iet.Current.GetType();
// 更新表头
foreach (TableCell item in e.Row.Cells)
{
String name = item.Text;
if (String.IsNullOrEmpty(name))
{
continue;
}
PropertyInfo pi = type.GetProperty(name);
if (pi == null)
{
continue;
}
DescriptionAttribute att = AttributeX.GetCustomAttribute <DescriptionAttribute>(pi, false);
if (att == null)
{
continue;
}
if (!String.IsNullOrEmpty(att.Description))
{
item.Text = att.Description;
}
}
}
}
}
/// <summary>修正数据列</summary>
/// <param name="column"></param>
public virtual IDataColumn Fix(IDataColumn column)
{
if (column.Name.IsNullOrEmpty()) column.Name = GetName(column.ColumnName);
return column;
}
/// <summary>取得字段默认值</summary>
/// <param name="field"></param>
/// <param name="onlyDefine">仅仅定义</param>
/// <returns></returns>
protected override string GetFieldDefault(IDataColumn field, bool onlyDefine)
{
// Access不能通过DDL来操作默认值
return null;
}
public Boolean DropColumnDescription(IDataColumn field)
{
return AddColumnDescription(field, null);
}
public override String AddColumnDescriptionSQL(IDataColumn field)
{
var sql = String.Format("EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'{1}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{2}'", field.Table.TableName, field.Description, field.ColumnName);
return(sql);
}
protected override string GetFieldType(IDataColumn field)
{
String typeName = base.GetFieldType(field);
//if (typeName.StartsWith("VarChar")) return typeName.Replace("VarChar", "Text");
if (field.Identity) return null;
return typeName;
}
public override String DropColumnDescriptionSQL(IDataColumn field)
{
return(String.Format("EXEC dbo.sp_dropextendedproperty @name=N'MS_Description', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}'", field.Table.TableName, field.ColumnName));
}
protected override void FixField(IDataColumn field, DataRow dr)
{
// 修正原始类型
String rawType = null;
if (TryGetDataRowValue<String>(dr, "COLUMN_TYPE", out rawType)) field.RawType = rawType;
// 修正自增字段
String extra = null;
if (TryGetDataRowValue<String>(dr, "EXTRA", out extra) && extra == "auto_increment") field.Identity = true;
// 修正主键
String key = null;
if (TryGetDataRowValue<String>(dr, "COLUMN_KEY", out key)) field.PrimaryKey = key == "PRI";
// 注释
String comment = null;
if (TryGetDataRowValue<String>(dr, "COLUMN_COMMENT", out comment)) field.Description = comment;
// 布尔类型
if (field.RawType == "enum")
{
// MySql中没有布尔型，这里处理YN枚举作为布尔型
if (field.RawType == "enum('N','Y')" || field.RawType == "enum('Y','N')")
{
field.DataType = typeof(Boolean);
// 处理默认值
if (!String.IsNullOrEmpty(field.Default))
{
if (field.Default == "Y")
field.Default = "true";
else if (field.Default == "N")
field.Default = "false";
}
return;
}
}
base.FixField(field, dr);
}
public override String AlterColumnSQL(IDataColumn field, IDataColumn oldfield)
{
// 创建为自增，重建表
if (field.Identity && !oldfield.Identity)
{
//return DropColumnSQL(oldfield) + ";" + Environment.NewLine + AddColumnSQL(field);
return(ReBuildTable(field.Table, oldfield.Table));
}
// 类型改变，必须重建表
if (IsColumnTypeChanged(field, oldfield))
{
return(ReBuildTable(field.Table, oldfield.Table));
}
var sql = String.Format("Alter Table {0} Alter Column {1}", FormatName(field.Table.TableName), FieldClause(field, false));
var pk = DeletePrimaryKeySQL(field);
if (field.PrimaryKey)
{
// 如果没有主键删除脚本，表明没有主键
//if (String.IsNullOrEmpty(pk))
if (!oldfield.PrimaryKey)
{
// 增加主键约束
pk = String.Format("Alter Table {0} ADD CONSTRAINT PK_{0} PRIMARY KEY {2}({1}) ON [PRIMARY]", FormatName(field.Table.TableName), FormatName(field.ColumnName), field.Identity ? "CLUSTERED" : "");
sql += ";" + Environment.NewLine + pk;
}
}
else
{
// 字段声明没有主键，但是主键实际存在，则删除主键
//if (!String.IsNullOrEmpty(pk))
if (oldfield.PrimaryKey)
{
sql += ";" + Environment.NewLine + pk;
}
}
//// 需要提前删除相关默认值
//if (oldfield.Default != null)
//{
// var df = DropDefaultSQL(oldfield);
// if (!String.IsNullOrEmpty(df))
// {
// sql = df + ";" + Environment.NewLine + sql;
// // 如果还有默认值，加上
// if (field.Default != null)
// {
// df = AddDefaultSQLWithNoCheck(field);
// if (!String.IsNullOrEmpty(df)) sql += ";" + Environment.NewLine + df;
// }
// }
//}
// 需要提前删除相关索引
foreach (var di in oldfield.Table.Indexes)
{
// 如果包含该字段
if (di.Columns.Contains(oldfield.ColumnName, StringComparer.OrdinalIgnoreCase))
{
var dis = DropIndexSQL(di);
if (!String.IsNullOrEmpty(dis))
{
sql = dis + ";" + Environment.NewLine + sql;
}
}
}
// 如果还有索引，则加上
foreach (var di in field.Table.Indexes)
{
// 如果包含该字段
if (di.Columns.Contains(field.ColumnName, StringComparer.OrdinalIgnoreCase))
{
var cis = CreateIndexSQL(di);
if (!String.IsNullOrEmpty(cis))
{
sql += ";" + Environment.NewLine + cis;
}
}
}
return(sql);
}
protected override string GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
{
String str = null;
if (!field.Nullable) str = " NOT NULL";
if (field.Identity) str = " NOT NULL AUTO_INCREMENT";
return str;
}
/// <summary>取得字段默认值</summary>
/// <param name="field">字段</param>
/// <param name="onlyDefine">仅仅定义</param>
/// <returns></returns>
protected override String GetFieldDefault(IDataColumn field, Boolean onlyDefine)
{
// Access不能通过DDL来操作默认值
return(null);
}
protected override string GetFormatParam(IDataColumn field, DataRow dr)
{
String str = base.GetFormatParam(field, dr);
if (String.IsNullOrEmpty(str)) return str;
if (str == "(-1)" && field.DataType == typeof(String)) return String.Format("({0})", Database.LongTextLength);
if (field.DataType == typeof(Guid)) return "(36)";
return str;
}
public Boolean DropColumnDescription(IDataColumn field)
{
return(AddColumnDescription(field, null));
}
/// <summary>
/// 修正字段名，在FixName的基础上，去掉表名前缀
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public virtual String GetPropertyName(IDataColumn field)
{
String name = field.Name;
if (Config.AutoCutPrefix)
{
String s = CutPrefix(name);
if (field.Table.Columns.Exists(item => item.Name == s)) name = s;
//if (!Array.Exists<IDataColumn>(field.Table.Columns, delegate(IDataColumn item) { return item.Name == s; })) name = s;
String str = ClassName;
if (!s.Equals(str, StringComparison.OrdinalIgnoreCase) &&
s.StartsWith(str, StringComparison.OrdinalIgnoreCase) &&
s.Length > str.Length && Char.IsLetter(s, str.Length))
s = s.Substring(str.Length);
//if (!Array.Exists<IDataColumn>(field.Table.Columns, delegate(IDataColumn item) { return item.Name == s; })) name = s;
if (field.Table.Columns.Exists(item => item.Name == s)) name = s;
}
if (Config.AutoFixWord)
{
name = FixWord(name);
}
return name;
}
protected override void FixField(IDataColumn field, DataRow drColumn, DataRow drDataType)
{
base.FixField(field, drColumn, drDataType);
// 修正原始类型
String typeName = null;
if (TryGetDataRowValue<String>(drDataType, "TypeName", out typeName)) field.RawType = typeName;
}
public virtual Boolean DropDefault(IDataColumn field)
{
return(AddDefault(field, null));
}
public override string DropColumnDescriptionSQL(IDataColumn field)
{
return(String.Empty);
}
protected override void FixField(IDataColumn field, DataRow drColumn)
{
base.FixField(field, drColumn);
// 字段标识
Int64 flag = GetDataRowValue<Int64>(drColumn, "COLUMN_FLAGS");
Boolean? isLong = null;
Int32 id = 0;
if (Int32.TryParse(GetDataRowValue<String>(drColumn, "DATA_TYPE"), out id))
{
DataRow[] drs = FindDataType(field, "" + id, isLong);
if (drs != null && drs.Length > 0)
{
String typeName = GetDataRowValue<String>(drs[0], "TypeName");
field.RawType = typeName;
if (TryGetDataRowValue<String>(drs[0], "DataType", out typeName)) field.DataType = TypeX.GetType(typeName);
// 修正备注类型
if (field.DataType == typeof(String) && drs.Length > 1)
{
isLong = (flag & 0x80) == 0x80;
drs = FindDataType(field, "" + id, isLong);
if (drs != null && drs.Length > 0)
{
typeName = GetDataRowValue<String>(drs[0], "TypeName");
field.RawType = typeName;
}
}
}
}
//// 处理自增
//if (field.DataType == typeof(Int32))
//{
// //field.Identity = (flag & 0x20) != 0x20;
//}
}
private DataRow[] OnFindDataType(IDataColumn field, String typeName, Boolean?isLong)
{
if (String.IsNullOrEmpty(typeName))
{
throw new ArgumentNullException("typeName");
}
// 去掉类型中，长度等限制条件
if (typeName.Contains("("))
{
typeName = typeName.Substring(0, typeName.IndexOf("("));
}
DataTable dt = DataTypes;
if (dt == null)
{
return(null);
}
DataRow[] drs = null;
StringBuilder sb = new StringBuilder();
// 匹配TypeName，TypeName具有唯一性
sb.AppendFormat("TypeName='{0}'", typeName);
//drs = dt.Select(String.Format("TypeName='{0}'", typeName));
// 处理自增
if (field.Identity && dt.Columns.Contains("IsAutoIncrementable"))
{
sb.Append(" And IsAutoIncrementable=1");
}
drs = dt.Select(sb.ToString());
if (drs != null && drs.Length > 0)
{
//if (drs.Length > 1) throw new XDbMetaDataException(this, "TypeName具有唯一性！");
return(drs);
}
// 匹配DataType，重复的可能性很大
DataRow[] drs2 = null;
sb = new StringBuilder();
sb.AppendFormat("DataType='{0}'", typeName);
// 处理自增
if (field.Identity && dt.Columns.Contains("IsAutoIncrementable"))
{
sb.Append(" And IsAutoIncrementable=1");
}
drs = dt.Select(sb.ToString());
if (drs != null && drs.Length > 0)
{
if (drs.Length == 1)
{
return(drs);
}
sb.AppendFormat(" And ColumnSize>={0}", field.Length);
//if (field.DataType == typeof(String) && field.Length > Database.LongTextLength) sb.AppendFormat(" And IsLong=1");
// 如果字段的长度为0，则也算是大文本
if (field.DataType == typeof(String) && (field.Length > Database.LongTextLength || field.Length <= 0))
{
sb.AppendFormat(" And IsLong=1");
}
drs2 = dt.Select(sb.ToString(), "IsBestMatch Desc, ColumnSize Asc, IsFixedLength Asc, IsLong Asc");
if (drs2 == null || drs2.Length < 1)
{
return(drs);
}
if (drs2.Length == 1)
{
return(drs2);
}
return(drs2);
}
return(null);
}
//protected override void FixIndex(IDataIndex index, DataRow dr)
//{
// base.FixIndex(index, dr);
// Boolean b = false;
// if (TryGetDataRowValue<Boolean>(dr, "PRIMARY_KEY", out b)) index.PrimaryKey = b;
// if (TryGetDataRowValue<Boolean>(dr, "UNIQUE", out b)) index.PrimaryKey = b;
//}
protected override string GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
{
String str = base.GetFieldConstraints(field, onlyDefine);
if (field.Identity) str = " AUTOINCREMENT(1,1)" + str;
return str;
}
public virtual String AddColumnSQL(IDataColumn field)
{
return(String.Format("Alter Table {0} Add {1}", FormatName(field.Table.Name), FieldClause(field, true)));
}
public Boolean AddColumnDescription(IDataColumn field, String value)
{
try
{
using (ADOTabe table = GetTable(field.Table.Name))
{
if (table.Supported && table.Columns != null)
{
foreach (ADOColumn item in table.Columns)
{
if (item.Name == field.Name)
{
item.Description = value;
return true;
}
}
}
return false;
}
}
catch { return false; }
}
public virtual String AlterColumnSQL(IDataColumn field, IDataColumn oldfield)
{
return(String.Format("Alter Table {0} Alter Column {1}", FormatName(field.Table.Name), FieldClause(field, false)));
}
public virtual Boolean AddDefault(IDataColumn field, String value)
{
//if (field.DataType == typeof(DateTime))
//{
CheckAndGetDefault(field, ref value);
//}
try
{
using (ADOTabe table = GetTable(field.Table.Name))
{
if (table.Supported && table.Columns != null)
{
foreach (ADOColumn item in table.Columns)
{
if (item.Name == field.Name)
{
item.Default = value;
return true;
}
}
}
return false;
}
}
catch { return false; }
}
public virtual String DropColumnSQL(IDataColumn field)
{
return(String.Format("Alter Table {0} Drop Column {1}", FormatName(field.Table.Name), field.Name));
}
protected override DataRow[] FindDataType(IDataColumn field, string typeName, bool? isLong)
{
DataRow[] drs = base.FindDataType(field, typeName, isLong);
if (drs != null && drs.Length > 0) return drs;
//// 处理SByte类型
//if (typeName == typeof(SByte).FullName)
//{
// typeName = typeof(Byte).FullName;
// drs = base.FindDataType(field, typeName, isLong);
// if (drs != null && drs.Length > 0) return drs;
//}
DataTable dt = DataTypes;
if (dt == null) return null;
// 转为整数
Int32 n = 0;
if (!Int32.TryParse(typeName, out n)) return null;
try
{
if (isLong == null)
{
drs = dt.Select(String.Format("NativeDataType={0}", n));
if (drs == null || drs.Length < 1) drs = dt.Select(String.Format("ProviderDbType={0}", n));
}
else
{
drs = dt.Select(String.Format("NativeDataType={0} And IsLong={1}", n, isLong.Value));
if (drs == null || drs.Length < 1) drs = dt.Select(String.Format("ProviderDbType={0} And IsLong={1}", n, isLong.Value));
}
}
catch { }
return drs;
}
public virtual String DropColumnDescriptionSQL(IDataColumn field)
{
return(null);
}
/// <summary>格式化数据为SQL数据</summary>
/// <param name="field">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatValue(IDataColumn field, Object value)
{
Boolean isNullable = true;
Type type = null;
if (field != null)
{
type = field.DataType;
isNullable = field.Nullable;
}
else if (value != null)
type = value.GetType();
var code = Type.GetTypeCode(type);
if (code == TypeCode.String)
{
if (value == null) return isNullable ? "null" : "''";
//!!! 为SQL格式化数值时，如果字符串是Empty，将不再格式化为null
//if (String.IsNullOrEmpty(value.ToString()) && isNullable) return "null";
return "'" + value.ToString().Replace("'", "''") + "'";
}
else if (code == TypeCode.DateTime)
{
if (value == null) return isNullable ? "null" : "''";
var dt = Convert.ToDateTime(value);
if (dt < DateTimeMin || dt > DateTime.MaxValue) return isNullable ? "null" : "''";
if ((dt == DateTime.MinValue || dt == DateTimeMin) && isNullable) return "null";
return FormatDateTime(dt);
}
else if (code == TypeCode.Boolean)
{
if (value == null) return isNullable ? "null" : "";
return Convert.ToBoolean(value) ? "1" : "0";
}
else if (type == typeof(Byte[]))
{
Byte[] bts = (Byte[])value;
if (bts == null || bts.Length < 1) return isNullable ? "null" : "0x0";
return "0x" + BitConverter.ToString(bts).Replace("-", null);
}
else if (field.DataType == typeof(Guid))
{
if (value == null) return isNullable ? "null" : "''";
return String.Format("'{0}'", value);
}
else
{
if (value == null) return isNullable ? "null" : "";
// 转为目标类型，比如枚举转为数字
value = value.ChangeType(type);
if (value == null) return isNullable ? "null" : "";
return value.ToString();
}
}
public virtual String DropDefaultSQL(IDataColumn field)
{
return(null);
}
/// <summary>格式化数据为SQL数据</summary>
/// <param name="field"></param>
/// <param name="value"></param>
/// <returns></returns>
public override string FormatValue(IDataColumn field, object value)
{
//if (field.DataType == typeof(String))
//{
// if (value == null) return field.Nullable ? "null" : "``";
// if (String.IsNullOrEmpty(value.ToString()) && field.Nullable) return "null";
// return "`" + value + "`";
//}
//else
if (field.DataType == typeof(Boolean))
{
return (Boolean)value ? "'Y'" : "'N'";
}
return base.FormatValue(field, value);
}
/// <summary>检查字段改变。某些数据库（如SQLite）没有添删改字段的DDL语法，可重载该方法，使用重建表方法ReBuildTable</summary>
/// <param name="entitytable"></param>
/// <param name="dbtable"></param>
/// <param name="setting"></param>
/// <returns></returns>
protected virtual String CheckColumnsChange(IDataTable entitytable, IDataTable dbtable, NegativeSetting setting)
{
#region 准备工作
var onlySql = setting.CheckOnly;
var sql = String.Empty;
var sb = new StringBuilder();
var entitydic = new Dictionary <String, IDataColumn>(StringComparer.OrdinalIgnoreCase);
if (entitytable.Columns != null)
{
foreach (var item in entitytable.Columns)
{
entitydic.Add(item.Name.ToLower(), item);
}
}
var dbdic = new Dictionary <String, IDataColumn>(StringComparer.OrdinalIgnoreCase);
if (dbtable.Columns != null)
{
foreach (var item in dbtable.Columns)
{
dbdic.Add(item.Name.ToLower(), item);
}
}
#endregion
#region 新增列
foreach (IDataColumn item in entitytable.Columns)
{
if (!dbdic.ContainsKey(item.Name.ToLower()))
{
//AddColumn(sb, item, onlySql);
PerformSchema(sb, onlySql, DDLSchema.AddColumn, item);
if (!String.IsNullOrEmpty(item.Description))
{
PerformSchema(sb, onlySql, DDLSchema.AddColumnDescription, item);
}
//! 以下已经不需要了，目前只有SQLite会采用重建表的方式添加删除字段。如果这里提前添加了字段，重建表的时候，会导致失败。
//// 这里必须给dbtable加加上当前列，否则下面如果刚好有删除列的话，会导致增加列成功，然后删除列重建表的时候没有新加的列
//dbtable.Columns.Add(item.Clone(dbtable));
}
}
#endregion
#region 除列
var sbDelete = new StringBuilder();
for (int i = dbtable.Columns.Count - 1; i >= 0; i--)
{
var item = dbtable.Columns[i];
if (!entitydic.ContainsKey(item.Name.ToLower()))
{
if (!String.IsNullOrEmpty(item.Description))
{
PerformSchema(sb, onlySql, DDLSchema.DropColumnDescription, item);
}
PerformSchema(sbDelete, setting.NoDelete, DDLSchema.DropColumn, item);
}
}
if (sbDelete.Length > 0)
{
if (setting.NoDelete)
{
//不许删除列，显示日志
WriteLog("数据表中发现有多余字段，请手工执行以下语句删除：" + Environment.NewLine + sbDelete.ToString());
}
else
{
if (sb.Length > 0)
{
sb.AppendLine(";");
}
sb.Append(sbDelete.ToString());
}
}
#endregion
#region 修改列
// 开发时的实体数据库
var entityDb = DbFactory.Create(entitytable.DbType);
foreach (var item in entitytable.Columns)
{
IDataColumn dbf = null;
if (!dbdic.TryGetValue(item.Name, out dbf))
{
continue;
}
if (IsColumnChanged(item, dbf, entityDb))
{
PerformSchema(sb, onlySql, DDLSchema.AlterColumn, item, dbf);
}
if (IsColumnDefaultChanged(item, dbf, entityDb))
{
ChangeColmnDefault(sb, onlySql, item, dbf, entityDb);
}
if (item.Description + "" != dbf.Description + "")
{
// 先删除旧注释
//if (!String.IsNullOrEmpty(dbf.Description)) DropColumnDescription(sb, dbf, onlySql);
//if (!String.IsNullOrEmpty(dbf.Description)) PerformSchema(sb, onlySql, DDLSchema.DropColumnDescription, dbf);
if (dbf.Description != null)
{
PerformSchema(sb, onlySql, DDLSchema.DropColumnDescription, dbf);
}
// 加上新注释
if (!String.IsNullOrEmpty(item.Description))
{
PerformSchema(sb, onlySql, DDLSchema.AddColumnDescription, item);
}
}
}
#endregion
return(sb.ToString());
}
protected override DataRow[] FindDataType(IDataColumn field, string typeName, bool? isLong)
{
// MySql没有ntext，映射到text
if (typeName.EqualIgnoreCase("ntext")) typeName = "text";
// MySql的默认值不能使用函数，所以无法设置当前时间作为默认值，但是第一个Timestamp类型字段会有当前时间作为默认值效果
if (typeName.EqualIgnoreCase("datetime"))
{
String d = field.Default; ;
if (CheckAndGetDefault(field, ref d) && String.IsNullOrEmpty(d)) typeName = "timestamp";
}
DataRow[] drs = base.FindDataType(field, typeName, isLong);
if (drs != null && drs.Length > 1)
{
// 无符号/有符号
if (!String.IsNullOrEmpty(field.RawType))
{
Boolean IsUnsigned = field.RawType.ToLower().Contains("unsigned");
foreach (DataRow dr in drs)
{
String format = GetDataRowValue<String>(dr, "CreateFormat");
if (IsUnsigned && format.ToLower().Contains("unsigned"))
return new DataRow[] { dr };
else if (!IsUnsigned && !format.ToLower().Contains("unsigned"))
return new DataRow[] { dr };
}
}
// 字符串
if (typeName == typeof(String).FullName || typeName.EqualIgnoreCase("varchar"))
{
foreach (DataRow dr in drs)
{
String name = GetDataRowValue<String>(dr, "TypeName");
if ((name == "NVARCHAR" && field.IsUnicode || name == "VARCHAR" && !field.IsUnicode) && field.Length <= Database.LongTextLength)
return new DataRow[] { dr };
else if (name == "LONGTEXT" && field.Length > Database.LongTextLength)
return new DataRow[] { dr };
}
}
// 时间日期
if (typeName == typeof(DateTime).FullName || typeName.EqualIgnoreCase("DateTime"))
{
// DateTime的范围是0001到9999
// Timestamp的范围是1970到2038
// MySql的默认值不能使用函数，所以无法设置当前时间作为默认值，但是第一个Timestamp类型字段会有当前时间作为默认值效果
String d = field.Default; ;
CheckAndGetDefault(field, ref d);
//String d = CheckAndGetDefault(field, field.Default);
foreach (DataRow dr in drs)
{
String name = GetDataRowValue<String>(dr, "TypeName");
if (name == "DATETIME" && String.IsNullOrEmpty(field.Default))
return new DataRow[] { dr };
else if (name == "TIMESTAMP" && String.IsNullOrEmpty(d))
return new DataRow[] { dr };
}
}
}
return drs;
}
/// <summary>改变字段默认值。这里仅仅默认处理了时间日期，如果需要兼容多数据库，子类需要重载</summary>
/// <param name="sb"></param>
/// <param name="onlySql"></param>
/// <param name="entityColumn"></param>
/// <param name="dbColumn"></param>
/// <param name="entityDb"></param>
protected virtual void ChangeColmnDefault(StringBuilder sb, Boolean onlySql, IDataColumn entityColumn, IDataColumn dbColumn, IDatabase entityDb)
{
// 如果数据库存在默认值，则删除
if (!String.IsNullOrEmpty(dbColumn.Default))
{
PerformSchema(sb, onlySql, DDLSchema.DropDefault, dbColumn);
}
// 如果实体存在默认值，则增加
if (!String.IsNullOrEmpty(entityColumn.Default))
{
var tc = Type.GetTypeCode(entityColumn.DataType);
String dv = entityColumn.Default;
// 特殊处理时间
if (tc == TypeCode.DateTime)
{
if (dv == entityDb.DateTimeNow)
{
entityColumn.Default = Database.DateTimeNow;
}
}
// 特殊处理Guid
else if (tc == TypeCode.String || entityColumn.DataType == typeof(Guid))
{
if (dv == entityDb.NewGuid)
{
entityColumn.Default = Database.NewGuid;
}
}
// 如果字段类型是Guid，不需要设置默认值，则也说明是Guid字段
else if (tc == TypeCode.String)
{
if (dv == entityDb.NewGuid || String.IsNullOrEmpty(dv))
{
entityColumn.Default = Database.NewGuid;
}
}
PerformSchema(sb, onlySql, DDLSchema.AddDefault, entityColumn);
// 还原
entityColumn.Default = dv;
}
}
public override string FieldClause(IDataColumn field, bool onlyDefine)
{
String sql = base.FieldClause(field, onlyDefine);
// 加上注释
if (!String.IsNullOrEmpty(field.Description)) sql = String.Format("{0} COMMENT '{1}'", sql, field.Description);
return sql;
}
/// <summary>
/// 获取架构语句，该执行的已经执行。
/// 如果取不到语句，则输出日志信息；
/// 如果不是纯语句，则执行；
/// </summary>
/// <param name="sb"></param>
/// <param name="onlySql"></param>
/// <param name="schema"></param>
/// <param name="values"></param>
protected void PerformSchema(StringBuilder sb, Boolean onlySql, DDLSchema schema, params Object[] values)
{
String sql = GetSchemaSQL(schema, values);
if (!String.IsNullOrEmpty(sql))
{
if (sb.Length > 0)
{
sb.AppendLine(";");
}
sb.Append(sql);
//if (!onlySql) XTrace.WriteLine("修改表：" + sql);
}
else if (sql == null)
{
// 只有null才表示通过非SQL的方式处理，而String.Empty表示已经通过别的SQL处理，这里不用输出日志
// 没办法形成SQL，输出日志信息
StringBuilder s = new StringBuilder();
if (values != null && values.Length > 0)
{
foreach (Object item in values)
{
if (s.Length > 0)
{
s.Append(" ");
}
s.Append(item);
}
}
IDataColumn dc = null;
IDataTable dt = null;
if (values != null && values.Length > 0)
{
dc = values[0] as IDataColumn;
dt = values[0] as IDataTable;
}
switch (schema)
{
//case DDLSchema.CreateDatabase:
// break;
//case DDLSchema.DropDatabase:
// break;
//case DDLSchema.DatabaseExist:
// break;
//case DDLSchema.CreateTable:
// break;
//case DDLSchema.DropTable:
// break;
//case DDLSchema.TableExist:
// break;
case DDLSchema.AddTableDescription:
WriteLog("{0}({1},{2})", schema, dt.Name, dt.Description);
break;
case DDLSchema.DropTableDescription:
WriteLog("{0}({1})", schema, dt);
break;
case DDLSchema.AddColumn:
WriteLog("{0}({1})", schema, dc);
break;
//case DDLSchema.AlterColumn:
// break;
case DDLSchema.DropColumn:
WriteLog("{0}({1})", schema, dc.Name);
break;
case DDLSchema.AddColumnDescription:
WriteLog("{0}({1},{2})", schema, dc.Name, dc.Description);
break;
case DDLSchema.DropColumnDescription:
WriteLog("{0}({1})", schema, dc.Name);
break;
case DDLSchema.AddDefault:
WriteLog("{0}({1},{2})", schema, dc.Name, dc.Default);
break;
case DDLSchema.DropDefault:
WriteLog("{0}({1})", schema, dc.Name);
break;
//case DDLSchema.CreateIndex:
// break;
//case DDLSchema.DropIndex:
// break;
//case DDLSchema.BackupDatabase:
// break;
//case DDLSchema.RestoreDatabase:
// break;
default:
WriteLog("修改表：{0} {1}", schema.ToString(), s.ToString());
break;
}
//WriteLog("修改表：{0} {1}", schema.ToString(), s.ToString());
}
if (!onlySql)
{
try
{
SetSchema(schema, values);
}
catch (Exception ex)
{
WriteLog("修改表{0}失败！{1}", schema.ToString(), ex.Message);
}
}
}
protected override string GetFieldDefault(IDataColumn field, bool onlyDefine)
{
if (String.IsNullOrEmpty(field.Default)) return null;
if (field.DataType == typeof(Boolean))
{
if (field.Default == "true")
return " Default 'Y'";
else if (field.Default == "false")
return " Default 'N'";
}
else if (field.DataType == typeof(String))
{
// 大文本不能有默认值
if (field.Length <= 0 || field.Length >= Database.LongTextLength) return null;
}
//else if (field.DataType == typeof(DateTime))
//{
// String d = CheckAndGetDefaultDateTimeNow(field.Table.DbType, field.Default);
// if (d == "now()") d = "CURRENT_TIMESTAMP";
// return String.Format(" Default {0}", d);
//}
return base.GetFieldDefault(field, onlyDefine);
}
/// <summary>
/// Adds a new column. The length must be specified for the first column.
/// It must be the same for all columns.
/// </summary>
/// <param name="name">column name</param>
/// <param name="values">new column</param>
public int AddColumn(string name, IDataColumn values)
{
return(_data.AddColumn(name, values.Kind, values.Length, values));
}
public override string AddColumnDescriptionSQL(IDataColumn field)
{
// 返回String.Empty表示已经在别的SQL中处理
return String.Empty;
//if (String.IsNullOrEmpty(field.Description)) return null;
//return String.Format("Alter Table {0} Modify {1} Comment '{2}'", FormatKeyWord(field.Table.Name), FormatKeyWord(field.Name), field.Description);
}
public static bool GetIsNullable(this IDataColumn col)
=> col.Attributes.TryGetProperty <bool>("nullable", out var tmp) && tmp;
/// <summary>获取别名。过滤特殊符号，过滤_之类的前缀。另外，避免一个表中的字段别名重名</summary>
/// <param name="dc"></param>
/// <returns></returns>
public virtual String GetAlias(IDataColumn dc)
{
var name = dc.Name;
// 对于自增字段，如果强制使用ID，并且字段名以ID结尾，则直接取用ID
if (dc.Identity && UseID && name.EndsWith("ID", StringComparison.OrdinalIgnoreCase)) return "ID";
#region 先去掉表前缀
var dt = dc.Table;
if (dt != null && AutoCutTableName)
{
//if (name.StartsWith(dt.Name, StringComparison.OrdinalIgnoreCase))
// name = name.Substring(dt.Name.Length);
//else if (name.StartsWith(dt.Alias, StringComparison.OrdinalIgnoreCase))
// name = name.Substring(dt.Alias.Length);
var pfs = new HashSet<String>(StringComparer.OrdinalIgnoreCase);
if (!dt.Name.IsNullOrWhiteSpace()) pfs.Add(dt.Name);
// 如果包括下划线，再分割
if (dt.Name.Contains("_"))
{
foreach (var item in dt.Name.Split("_"))
{
if (item != null && item.Length >= 2 && !pfs.Contains(item)) pfs.Add(item);
}
}
if (!dt.Alias.IsNullOrWhiteSpace() && !pfs.Contains(dt.Alias))
{
pfs.Add(dt.Alias);
// 如果包括下划线，再分割
if (dt.Alias.Contains("_"))
{
foreach (var item in dt.Alias.Split("_"))
{
if (item != null && item.Length >= 2 && !pfs.Contains(item)) pfs.Add(item);
}
}
}
foreach (var item in pfs)
{
if (name.StartsWith(item, StringComparison.OrdinalIgnoreCase) && name.Length != item.Length) name = name.Substring(item.Length);
}
if (name[0] == '_') name = name.Substring(1);
}
#endregion
name = GetAlias(name);
if (dt != null)
{
var lastname = name;
var index = 0;
var cs = dt.Columns;
for (int i = 0; i < cs.Count; i++)
{
var item = cs[i];
if (item != dc && item.Name != dc.Name)
{
// 对于小于当前的采用别名，对于大于当前的，采用字段名，保证同名有优先级
if (lastname.EqualIgnoreCase(item.ID < dc.ID ? item.Alias : item.Name))
{
lastname = name + ++index;
// 从头开始
i = -1;
}
}
}
name = lastname;
}
return name;
}
/// <summary>创建参数</summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <param name="field">字段</param>
/// <returns></returns>
public virtual IDataParameter CreateParameter(String name, Object value, IDataColumn field = null)
{
var type = field?.DataType;
if (value == null && type == null)
{
throw new ArgumentNullException(nameof(field));
}
var dp = Factory.CreateParameter();
dp.ParameterName = FormatParameterName(name);
dp.Direction = ParameterDirection.Input;
try
{
if (type == null)
{
type = value?.GetType();
// 参数可能是数组
if (type != null && type != typeof(Byte[]) && type.IsArray)
{
type = type.GetElementTypeEx();
}
}
else if (!(value is IList))
{
value = value.ChangeType(type);
}
// 写入数据类型
switch (type.GetTypeCode())
{
case TypeCode.Boolean:
dp.DbType = DbType.Boolean;
break;
case TypeCode.Char:
case TypeCode.SByte:
case TypeCode.Byte:
dp.DbType = DbType.Byte;
break;
case TypeCode.Int16:
case TypeCode.UInt16:
dp.DbType = DbType.Int16;
break;
case TypeCode.Int32:
case TypeCode.UInt32:
dp.DbType = DbType.Int32;
break;
case TypeCode.Int64:
case TypeCode.UInt64:
dp.DbType = DbType.Int64;
break;
case TypeCode.Single:
dp.DbType = DbType.Double;
break;
case TypeCode.Double:
dp.DbType = DbType.Double;
break;
case TypeCode.Decimal:
dp.DbType = DbType.Decimal;
break;
case TypeCode.DateTime:
dp.DbType = DbType.DateTime;
break;
case TypeCode.String:
dp.DbType = DbType.String;
break;
default:
break;
}
dp.Value = value;
}
catch (Exception ex)
{
throw new Exception($"创建字段{name}/{type.Name}的参数时出错", ex);
}
return(dp);
}
/// <summary>修正数据列</summary>
/// <param name="column"></param>
public virtual IDataColumn Fix(IDataColumn column)
{
return column;
}
/// <summary>格式化数据为SQL数据</summary>
/// <param name="field">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatValue(IDataColumn field, Object value)
{
var isNullable = true;
Type type = null;
if (field != null)
{
type = field.DataType;
isNullable = field.Nullable;
}
else if (value != null)
{
type = value.GetType();
}
if (type == typeof(String))
{
if (value == null)
{
return(isNullable ? "null" : "''");
}
//!!! 为SQL格式化数值时，如果字符串是Empty，将不再格式化为null
//if (String.IsNullOrEmpty(value.ToString()) && isNullable) return "null";
return("'" + value.ToString().Replace("'", "''") + "'");
}
else if (type == typeof(DateTime))
{
if (value == null)
{
return(isNullable ? "null" : "''");
}
var dt = Convert.ToDateTime(value);
//if (dt <= DateTime.MinValue || dt >= DateTime.MaxValue) return isNullable ? "null" : "''";
if (isNullable && (dt <= DateTime.MinValue || dt >= DateTime.MaxValue))
{
return("null");
}
return(FormatDateTime(dt));
}
else if (type == typeof(Boolean))
{
if (value == null)
{
return(isNullable ? "null" : "");
}
return(Convert.ToBoolean(value) ? "1" : "0");
}
else if (type == typeof(Byte[]))
{
var bts = (Byte[])value;
if (bts == null || bts.Length < 1)
{
return(isNullable ? "null" : "0x0");
}
return("0x" + BitConverter.ToString(bts).Replace("-", null));
}
else if (field.DataType == typeof(Guid))
{
if (value == null)
{
return(isNullable ? "null" : "''");
}
return(String.Format("'{0}'", value));
}
if (value == null)
{
return(isNullable ? "null" : "");
}
// 枚举
if (!type.IsInt() && type.IsEnum)
{
type = typeof(Int32);
}
// 转为目标类型，比如枚举转为数字
value = value.ChangeType(type);
if (value == null)
{
return(isNullable ? "null" : "");
}
return(value.ToString());
}
/// <summary>
/// 英文名转为中文名
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public virtual String GetPropertyDescription(IDataColumn field)
{
if (!String.IsNullOrEmpty(field.Description) || !Config.UseCNFileName) return field.Description;
return Engine.ENameToCName(field.Name);
}
protected override Boolean IsColumnChanged(IDataColumn entityColumn, IDataColumn dbColumn, IDatabase entityDb)
{
return(base.IsColumnChanged(entityColumn, dbColumn, entityDb));
}
