void setColumn_IsAutoIncrement( IDatabaseService db , EJ.DBColumn column ,string table, bool isAutoIncrement)
{
table = table.ToLower();
/*
先创建一张表，再创建一个序列，然后将表主键ID的默认值设置成这个序列的NEXT值
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';可以查看所有SEQUENCE
*/
if (isAutoIncrement)
{
db.ExecSqlString($"DROP SEQUENCE IF EXISTS {table}_{column.Name.ToLower()}_seq");
db.ExecSqlString($@"
CREATE SEQUENCE {table}_{column.Name.ToLower()}_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
alter table ""{table}"" alter column ""{column.Name.ToLower()}"" set default nextval('{table}_{column.Name.ToLower()}_seq');
");
}
else
{
//查找是哪个sequence
//select column_name,data_type,column_default,is_nullable,character_maximum_length,character_octet_length from information_schema.columns where table_name = 'tbl_role';
var seqName = db.ExecSqlString($"select column_default from information_schema.columns where table_name = '{table.ToLower()}' and column_name='{column.Name.ToLower()}'").ToSafeString();
Match m = Regex.Match(seqName, @"nextval\(\'(?<n>(\w)+)\'");
if (m != null && m.Length > 0)
{
seqName = m.Groups["n"].Value;
db.ExecSqlString($"alter table \"{table}\" ALTER COLUMN \"{column.Name.ToLower()}\" DROP DEFAULT");
db.ExecSqlString($"DROP SEQUENCE IF EXISTS {seqName}");
}
}
}
public static string GetSqlServerType(EJ.DBColumn column)
{
if (column.dbType == "double")
{
return("float");
}
return(column.dbType);
}
/// <summary>
/// 绑定显示数据
/// </summary>
public void DataBind()
{
if (this.DataSource != null)
{
txtTitle.Text = this.DataSource.Table.Name + " " + DataSource.Table.caption;
gridColumns.RowDefinitions.Clear();
gridColumns.Children.Clear();
for (int i = 0; i < DataSource.Columns.Length; i++)
{
RowDefinition rowdef = new RowDefinition();
rowdef.Height = GridLength.Auto;
gridColumns.RowDefinitions.Add(rowdef);
EJ.DBColumn Column = DataSource.Columns[i];
TextBox t1 = new MyTextBox();
t1.Tag = Column.id.GetValueOrDefault();
if (Column.caption != null && Column.caption.Contains(","))
{
t1.Text = Column.caption.Substring(0, Column.caption.IndexOf(","));
}
else if (Column.caption != null && Column.caption.Contains("，"))
{
t1.Text = Column.caption.Substring(0, Column.caption.IndexOf("，"));
}
else
{
t1.Text = Column.caption;
}
setTextBoxStyle(t1, 0);
Grid.SetRow(t1, i);
gridColumns.Children.Add(t1);
TextBox t2 = new MyTextBox();
t2.Text = Column.Name;
setTextBoxStyle(t2, 1);
Grid.SetRow(t2, i);
gridColumns.Children.Add(t2);
TextBox t3 = new MyTextBox();
t3.Text = Column.dbType;
setTextBoxStyle(t3, 2);
Grid.SetRow(t3, i);
gridColumns.Children.Add(t3);
TextBox t4 = new MyTextBox();
t4.Text = Column.length;
setTextBoxStyle(t4, 3);
Grid.SetRow(t4, i);
gridColumns.Children.Add(t4);
}
}
}
string getSqlType(EJ.DBColumn column)
{
//serial不是一种自增长类型，只是一个宏，所以不要用serial
string dbtype = column.dbType.ToLower();
int index = Design.ColumnType.SupportTypes.IndexOf(dbtype);
if (index < 0 || ColumnType[index] == null)
throw new Exception($"不支持字段类型{dbtype}");
return ColumnType[index];
}
public void CreatTable()
{
EJ.DBTable table = new EJ.DBTable()
{
Name = "Table1",
id = 1,
};
EJ.DBColumn[] columns = new EJ.DBColumn[3];
columns[0] = new EJ.DBColumn()
{
Name = "Id",
IsPKID = true,
IsAutoIncrement = true,
dbType = "int",
};
columns[1] = new EJ.DBColumn()
{
Name = "Name",
dbType = "varchar",
length = "50"
};
columns[2] = new EJ.DBColumn()
{
Name = "Age",
dbType = "int",
defaultValue = "10"
};
EJ.IDXIndex[] idxConfigs = new EJ.IDXIndex[1];
idxConfigs[0] = new EJ.IDXIndex()
{
Keys = "Name,Age",
IsUnique = true,
};
var invokeDB = EntityDB.DBContext.CreateDatabaseService($"data source=\"{AppDomain.CurrentDomain.BaseDirectory}\\test.sqlite\"", EntityDB.DatabaseType.Sqlite);
var action = new EF_CreateTable_Action(table, columns, idxConfigs);
invokeDB.DBContext.BeginTransaction();
try
{
action.Invoke(invokeDB);
}
catch
{
throw;
}
finally
{
invokeDB.DBContext.RollbackTransaction();
}
}
public List <EJ.DBColumn> GetCurrentColumns(IDatabaseService db, string tablename)
{
List <EJ.DBColumn> result = new List <EJ.DBColumn>();
var dbnameMatch = System.Text.RegularExpressions.Regex.Match(db.ConnectionString, @"database=(?<dname>(\w)+)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
var dbname = dbnameMatch.Groups["dname"].Value;
var table = db.SelectTable($"select * from information_schema.COLUMNS where TABLE_SCHEMA='{dbname}' and TABLE_NAME='{tablename}'");
foreach (var row in table.Rows)
{
EJ.DBColumn column = new EJ.DBColumn();
column.Name = row["COLUMN_NAME"].ToSafeString();
column.CanNull = row["IS_NULLABLE"].ToSafeString() == "YES";
column.dbType = row["DATA_TYPE"].ToSafeString().ToLower();
int typeindex = -1;
for (int i = 0; i < Database.MySql.MySqlTableService.ColumnType.Count; i++)
{
if (string.Equals(Database.MySql.MySqlTableService.ColumnType[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
for (int i = 0; i < Way.EntityDB.Design.ColumnType.SupportTypes.Count; i++)
{
if (string.Equals(Way.EntityDB.Design.ColumnType.SupportTypes[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
column.dbType = "[未识别]" + column.dbType;
}
}
column.defaultValue = row["COLUMN_DEFAULT"].ToSafeString();
column.IsAutoIncrement = row["EXTRA"].ToSafeString().Contains("auto_increment");
column.IsPKID = row["COLUMN_KEY"].ToSafeString().Contains("PRI");
column.length = row["CHARACTER_MAXIMUM_LENGTH"].ToSafeString();
column.ChangedProperties.Clear();
result.Add(column);
}
return(result);
}
public static void HandleChange(IDatabaseService database, string tablename, EJ.DBColumn originalColumn)
{
tablename = tablename.ToLower();
var column = (EJ.DBColumn)originalColumn.Clone();
column.BackupChangedProperties.ImportData(originalColumn.BackupChangedProperties);
if (column.BackupChangedProperties.ContainsKey("Name"))
{
Rename(database, tablename, column);
}
if (column.BackupChangedProperties.ContainsKey("IsAutoIncrement"))
{
if (column.IsAutoIncrement == false)
{
//去掉自增长
CancelAutoIncrement(database, tablename, column);
}
else
{
//设为自增长
BeAutoIncrement(database, tablename, column);
}
}
if (column.BackupChangedProperties.ContainsKey("IsPKID") && column.IsPKID == false)
{
CancelPrimaryKey(database, tablename, column);
}
bool tosetDefaultValue = false;
if (column.BackupChangedProperties.ContainsKey("defaultValue") || column.BackupChangedProperties.ContainsKey("dbType"))
{
tosetDefaultValue = true;
CancelDefaultValue(database, tablename, column);
}
if (column.BackupChangedProperties.ContainsKey("dbType") || column.BackupChangedProperties.ContainsKey("length") || column.BackupChangedProperties.ContainsKey("CanNull"))
{
Modify(database, tablename, column);
}
if (tosetDefaultValue && !string.IsNullOrEmpty(column.defaultValue))
{
SetDefaultValue(database, tablename, column);
}
if (column.BackupChangedProperties.ContainsKey("IsPKID") && column.IsPKID == true)
{
BePrimaryKey(database, tablename, column);
}
}
public static string GetSqlServerType(EJ.DBColumn column)
{
string dbtype = column.dbType.ToLower();
int index = Design.ColumnType.SupportTypes.IndexOf(dbtype);
if (index < 0 || ColumnType[index] == null)
{
throw new Exception($"不支持字段类型{dbtype}");
}
return(ColumnType[index]);
}
string getSqliteType(EJ.DBColumn column)
{
var dbtype = getSqlType(column);
if (!column.length.IsNullOrEmpty())
{
dbtype += "(" + column.length + ")";
}
if (dbtype.Contains("TEXT") || dbtype.Contains("CHAR"))
{ // COLLATE NOCASE查询时，不区分大小写
dbtype += " COLLATE NOCASE";
}
return(dbtype);
}
string getSqlType(EJ.DBColumn column)
{
if (column.IsAutoIncrement == true)
{
return("INTEGER");//只有INTEGER可以自增长
}
//serial不是一种自增长类型，只是一个宏，所以不要用serial
string dbtype = column.dbType.ToLower();
int index = Design.ColumnType.SupportTypes.IndexOf(dbtype);
if (index < 0 || ColumnType[index] == null)
{
throw new Exception($"不支持字段类型{dbtype}");
}
return(ColumnType[index]);
}
public static void HandleDelete(IDatabaseService database, string tablename, EJ.DBColumn originalColumn)
{
tablename = tablename.ToLower();
var column = (EJ.DBColumn)originalColumn.Clone();
column.BackupChangedProperties.ImportData(originalColumn.BackupChangedProperties);
if (column.BackupChangedProperties["Name"] != null)
{
column.Name = column.BackupChangedProperties["Name"].OriginalValue.ToString();
column.BackupChangedProperties.Remove("Name");
}
if (column.BackupChangedProperties["IsPKID"] != null && (bool)column.BackupChangedProperties["IsPKID"].OriginalValue == true)
{
CancelPrimaryKey(database, tablename, column);
}
else if (column.IsPKID == true)
{
CancelPrimaryKey(database, tablename, column);
}
Delete(database, tablename, column);
}
public ColumnInfo(EJ.DBColumn column)
{
_column = column;
_originalType = column.dbType;
}
public List <EJ.DBColumn> GetCurrentColumns(IDatabaseService db, string tablename)
{
var dbnameMatch = System.Text.RegularExpressions.Regex.Match(db.ConnectionString, @"database=(?<dname>(\w)+)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
var dbname = dbnameMatch.Groups["dname"].Value;
MySqlService mySqlService = (MySqlService)db;
List <EJ.DBColumn> result = new List <EJ.DBColumn>();
var table = db.SelectTable($"select * from information_schema.COLUMNS where TABLE_SCHEMA='{dbname}' and TABLE_NAME='{tablename}'");
//获取主键
var pkcolumnName = db.ExecSqlString(@"SELECT
C.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
c.CONSTRAINT_SCHEMA = '" + dbname + @"'
AND c.CONSTRAINT_NAME = 'PRIMARY'
AND c.TABLE_NAME = '" + tablename + @"'
")?.ToString();
foreach (var row in table.Rows)
{
EJ.DBColumn column = new EJ.DBColumn();
column.Name = row["COLUMN_NAME"].ToSafeString();
column.CanNull = row["IS_NULLABLE"].ToSafeString() == "YES";
column.dbType = row["DATA_TYPE"].ToSafeString().ToLower();
column.caption = row["COLUMN_COMMENT"]?.ToString();
int typeindex = -1;
for (int i = 0; i < Database.MySql.MySqlTableService.ColumnType.Count; i++)
{
if (string.Equals(Database.MySql.MySqlTableService.ColumnType[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else if (column.dbType.EndsWith("int"))
{
column.dbType = "int";
}
else if (column.dbType == "enum" || column.dbType == "set")
{
column.dbType = "varchar";
}
else
{
for (int i = 0; i < Way.EntityDB.Design.ColumnType.SupportTypes.Count; i++)
{
if (string.Equals(Way.EntityDB.Design.ColumnType.SupportTypes[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
column.dbType = "[未识别]" + column.dbType;
}
}
column.defaultValue = row["COLUMN_DEFAULT"].ToSafeString();
column.IsAutoIncrement = row["EXTRA"].ToSafeString().Contains("auto_increment");
if (!string.IsNullOrEmpty(pkcolumnName))
{
column.IsPKID = column.Name == pkcolumnName;
}
if (column.dbType.Contains("char"))
{
column.length = row["CHARACTER_MAXIMUM_LENGTH"].ToSafeString();
}
column.ChangedProperties.Clear();
result.Add(column);
}
return(result);
}
static void Test(EJ.Databases dbconfig)
{
IDatabaseDesignService dbservice = EntityDB.Design.DBHelper.CreateDatabaseDesignService((EntityDB.DatabaseType)(int) dbconfig.dbType);
EntityDB.IDatabaseService db = null;
dbservice.Drop(dbconfig);
try
{
dbservice.Create(dbconfig);
db = EntityDB.DBContext.CreateDatabaseService(dbconfig.conStr, (EntityDB.DatabaseType)(int) dbconfig.dbType);
List <EJ.DBColumn> allColumns = new List <EJ.DBColumn>();
List <EntityDB.Design.IndexInfo> allindexes = new List <EntityDB.Design.IndexInfo>();
#region CreateTable
if (true)
{
EJ.DBTable tableUser = new EJ.DBTable();
tableUser.Name = "User";
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Name",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
CreateTableAction _CreateTableAction = new CreateTableAction(tableUser, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
DeleteTableAction _delaction = new DeleteTableAction(tableUser.Name);
_delaction.Invoke(db);
//再次创建
_CreateTableAction.Invoke(db);
}
#endregion
allColumns.Clear();
allindexes.Clear();
allColumns.Add(new EJ.DBColumn()
{
IsPKID = true,
CanNull = false,
Name = "Id",
dbType = "int",
IsAutoIncrement = true,
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C1",
dbType = "varchar",
length = "50",
defaultValue = "a'b,c"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C2",
dbType = "varchar",
length = "50",
defaultValue = "abc"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "C3",
dbType = "int",
defaultValue = "8"
});
allColumns.Add(new EJ.DBColumn()
{
Name = "Text1",
dbType = "text",
});
//索引
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C1" },
IsUnique = true,
});
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "C2", "C3" },
});
EJ.DBTable table = new EJ.DBTable();
table.Name = "Test";
#region CreateTable
if (true)
{
CreateTableAction _CreateTableAction = new CreateTableAction(table, allColumns.ToArray(), allindexes.ToArray());
_CreateTableAction.Invoke(db);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region 测试自增长id
if (true)
{
Way.EntityDB.CustomDataItem dataitem = new EntityDB.CustomDataItem("test", "id", null);
dataitem.SetValue("c1", "C1");
dataitem.SetValue("c2", "C2");
dataitem.SetValue("c3", 3);
db.Insert(dataitem);
if (dataitem.GetValue("id") == null)
{
throw new Exception("测试自增长id失败");
}
db.ExecSqlString("delete from test");
}
#endregion
#region ChangeTable1
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[2];
newcolumns[0] = (new EJ.DBColumn()
{
Name = "N0",
dbType = "varchar",
length = "30",
defaultValue = "t'b"
});
newcolumns[1] = (new EJ.DBColumn()
{
Name = "N1",
dbType = "int",
defaultValue = "18"
});
EJ.DBColumn[] changedColumns = new EJ.DBColumn[2];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "C3");
changedColumns[0].Name = "C3_changed";
changedColumns[0].dbType = "varchar";
changedColumns[0].defaultValue = "1'a";
changedColumns[0].CanNull = false;
changedColumns[0].length = "88";
changedColumns[1] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[1].IsAutoIncrement = false;
changedColumns[1].IsPKID = false;
changedColumns[1].CanNull = true;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[1];
deletecolumns[0] = allColumns.FirstOrDefault(m => m.Name == "C2");
allColumns.Remove(deletecolumns[0]);
allindexes.Clear();
allindexes.Add(new EntityDB.Design.IndexInfo()
{
ColumnNames = new string[] { "N0", "C3_changed" },
IsUnique = true,
IsClustered = true
});
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test2", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test2";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
#region ChangeTable2
if (true)
{
EJ.DBColumn[] newcolumns = new EJ.DBColumn[0];
EJ.DBColumn[] changedColumns = new EJ.DBColumn[1];
changedColumns[0] = allColumns.FirstOrDefault(m => m.Name == "Id");
changedColumns[0].IsAutoIncrement = true;
changedColumns[0].IsPKID = true;
changedColumns[0].CanNull = false;
EJ.DBColumn[] deletecolumns = new EJ.DBColumn[0];
var otherColumns = (from m in allColumns
where changedColumns.Contains(m) == false
select m).ToArray();
new ChangeTableAction(table.Name, "Test3", newcolumns, changedColumns, deletecolumns, otherColumns, allindexes.ToArray())
.Invoke(db);
table.Name = "Test3";
allColumns.AddRange(newcolumns);
foreach (var c in allColumns)
{
c.ChangedProperties.Clear();
c.BackupChangedProperties.Clear();
}
checkColumns(dbservice, db, table.Name, allColumns, allindexes);
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (db != null)
{
db.DBContext.Dispose();
}
}
}
public List <EJ.DBColumn> GetCurrentColumns(IDatabaseService db, string tablename)
{
List <EJ.DBColumn> result = new List <EJ.DBColumn>();
var createTableSql = db.ExecSqlString($"select sql from sqlite_master where type='table' and name='{tablename}'");
if (createTableSql != null)
{
var sql = createTableSql.ToString();
var match = Regex.Match(sql, @"create( )+table( )+(\w|\[|\])+( )+", RegexOptions.IgnoreCase);
sql = sql.Substring(match.Index + match.Length).Trim();
if (sql.StartsWith("("))
{
sql = sql.Substring(1, sql.Length - 2);
}
List <string> fieldStrings = new List <string>();
StringBuilder itemString = new StringBuilder();
bool stringBegined = false;
for (int i = 0; i < sql.Length; i++)
{
char c = sql[i];
if (c == ',' && stringBegined == false)
{
fieldStrings.Add(itemString.ToString().Trim());
itemString = new StringBuilder();
continue;
}
else if (c == '\'')
{
if (stringBegined == false)
{
stringBegined = true;
}
else
{
if (i + 1 < sql.Length && sql[i + 1] == '\'')
{
itemString.Append('\'');
i++;
}
else
{
stringBegined = false;
}
}
}
itemString.Append(c);
}
if (itemString.ToString().Trim().Length > 0)
{
fieldStrings.Add(itemString.ToString().Trim());
}
foreach (var itemstring in fieldStrings)
{
if (itemstring.Length > 0)
{
sql = itemstring;
match = Regex.Match(sql, @"(\w|\[|\])+( )+", RegexOptions.IgnoreCase);
EJ.DBColumn column = new EJ.DBColumn();
column.Name = match.Value.Trim();
if (column.Name.StartsWith("["))
{
column.Name = column.Name.Substring(1, column.Name.Length - 2);
}
sql = sql.Substring(match.Index + match.Length);
match = Regex.Match(sql, @"(\w)+", RegexOptions.IgnoreCase);
column.dbType = match.Value;
int typeindex = -1;
for (int i = 0; i < Database.Sqlite.SqliteTableService.ColumnType.Count; i++)
{
if (string.Equals(Database.Sqlite.SqliteTableService.ColumnType[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
for (int i = 0; i < Way.EntityDB.Design.ColumnType.SupportTypes.Count; i++)
{
if (string.Equals(Way.EntityDB.Design.ColumnType.SupportTypes[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
column.dbType = "[未识别]" + column.dbType;
}
}
sql = sql.Substring(match.Index + match.Length);
if (sql.StartsWith("("))
{
column.length = sql.Substring(0, sql.IndexOf(")"));
column.length = column.length.Substring(1);
sql = sql.Substring(sql.IndexOf(")") + 1);
}
match = Regex.Match(sql, @"DEFAULT( )+", RegexOptions.IgnoreCase);
if (match != null && match.Length > 0)
{
column.defaultValue = sql.Substring(match.Index + match.Length).Trim();
if (column.defaultValue.StartsWith("'"))
{
column.defaultValue = column.defaultValue.Substring(1, column.defaultValue.LastIndexOf("'") - 1);
sql = sql.Replace("'" + column.defaultValue + "'", "");
column.defaultValue = column.defaultValue.Replace("''", "'");
}
else
{
column.defaultValue = Regex.Match(column.defaultValue, @"(\w)+").Value;
}
}
column.CanNull = !sql.ToUpper().Contains("NOT NULL");
column.IsPKID = sql.ToUpper().Contains("PRIMARY KEY");
column.IsAutoIncrement = sql.ToUpper().Contains("AUTOINCREMENT");
column.ChangedProperties.Clear();
result.Add(column);
}
}
}
return(result);
}
public static void HandleNewColumn(IDatabaseService database, string tablename, EJ.DBColumn originalColumn)
{
tablename = tablename.ToLower();
var column = (EJ.DBColumn)originalColumn.Clone();
column.BackupChangedProperties.ImportData(originalColumn.BackupChangedProperties);
string sqltype = MySqlTableService.GetSqlType(column.dbType);
if (column.length.IsNullOrEmpty() == false)
{
if (sqltype.Contains("("))
{
sqltype = sqltype.Substring(0, sqltype.IndexOf("("));
}
sqltype += "(" + column.length + ")";
}
string sql = "alter table `" + tablename + "` add `" + column.Name.ToLower() + "` " + sqltype;
if (column.IsAutoIncrement == true)
{
sql += " AUTOINCREMENT";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sql += " NOT";
}
sql += " NULL ";
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
if (column.dbType == "bit")
{
sql += " default " + defaultValue;
}
else
{
sql += " default '" + defaultValue.Replace("'", "''") + "'";
}
}
database.ExecSqlString(sql);
if (column.IsPKID == true)
{
database.ExecSqlString(string.Format("Alter table `{0}` add primary key(`{1}`)", tablename, column.Name.ToLower()));
}
}
public static void HandleNewColumn(IDatabaseService database, string tablename, EJ.DBColumn originalColumn)
{
tablename = tablename.ToLower();
var column = (EJ.DBColumn)originalColumn.Clone();
column.BackupChangedProperties.ImportData(originalColumn.BackupChangedProperties);
var dbtype = SqlServerTableService.GetSqlServerType(column);
string sql = "alter table [" + tablename + "] add [" + column.Name.ToLower() + "] [" + dbtype + "]";
if (dbtype.IndexOf("char") >= 0)
{
if (!string.IsNullOrEmpty(column.length))
{
sql += " (" + column.length + ")";
}
else
{
sql += " (50)";
}
}
else
{
if (!string.IsNullOrEmpty(column.length))
{
sql += " (" + column.length + ")";
}
}
if (column.IsAutoIncrement == true)
{
sql += " IDENTITY (1, 1)";
}
if (column.CanNull == false || column.IsPKID == true || column.IsAutoIncrement == true)
{
sql += " NOT";
}
sql += " NULL ";
if (!string.IsNullOrEmpty(column.defaultValue))
{
string defaultValue = column.defaultValue.Trim();
sql += " default '" + defaultValue.Replace("'", "''") + "' with values";
}
database.ExecSqlString(sql);
if (column.IsPKID == true)
{
database.ExecSqlString("alter table [" + tablename + "] add constraint pk_" + tablename + "_" + column.Name.ToLower() + " primary key ([" + column.Name.ToLower() + "])");
}
}
public List <EJ.DBColumn> GetCurrentColumns(IDatabaseService db, string tablename)
{
List <string> pkfields = new List <string>();
using (var sp_helpResult = db.SelectDataSet("sp_help [" + tablename + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (WayDataRow drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
string index_description = drow["index_description"].ToString();
if (index_description.Contains("primary key") == true)
{
//去除空格
string flag = existColumnString.Split(',').ToSplitString();
pkfields.AddRange(flag.Split(',').OrderBy(m => m).ToArray());
}
}
}
}
}
List <EJ.DBColumn> result = new List <EJ.DBColumn>();
var table = db.SelectTable($"select name,length,xtype,cdefault,isnullable,columnproperty(id,name,'IsIdentity') as IsAutoIncrement from syscolumns where ID=OBJECT_ID('{tablename}') ");
foreach (var row in table.Rows)
{
EJ.DBColumn column = new EJ.DBColumn();
column.Name = row["name"].ToSafeString();
column.dbType = db.ExecSqlString($"select name from SYSTYPES where xtype={row["xtype"]}").ToSafeString();
if (column.dbType.Contains("varchar"))
{
column.dbType = "varchar";
}
else if (column.dbType.Contains("datetime"))
{
column.dbType = "datetime";
}
int typeindex = -1;
for (int i = 0; i < Way.EntityDB.Design.ColumnType.SupportTypes.Count; i++)
{
if (string.Equals(Way.EntityDB.Design.ColumnType.SupportTypes[i], column.dbType, StringComparison.CurrentCultureIgnoreCase))
{
typeindex = i;
break;
}
}
if (typeindex >= 0)
{
column.dbType = EntityDB.Design.ColumnType.SupportTypes[typeindex];
}
else
{
column.dbType = "[未识别]" + column.dbType;
}
column.defaultValue = row["cdefault"].ToSafeString();
if (!column.defaultValue.IsNullOrEmpty())
{
try
{
column.defaultValue = db.ExecSqlString($@"SELECT TEXT FROM syscomments where id='{column.defaultValue}'").ToSafeString().Trim();
if (column.defaultValue.StartsWith("("))
{
column.defaultValue = column.defaultValue.Substring(1, column.defaultValue.Length - 2).Replace("''", "'");
if (column.defaultValue.StartsWith("'"))
{
column.defaultValue = column.defaultValue.Substring(1, column.defaultValue.Length - 2);
}
}
}
catch
{
column.defaultValue = "";
}
}
column.CanNull = row["isnullable"].ToSafeString() == "1";
column.IsAutoIncrement = row["IsAutoIncrement"].ToSafeString() == "1";
//
column.IsPKID = pkfields.Any(m => string.Equals(m, column.Name, StringComparison.CurrentCultureIgnoreCase));
column.length = row["length"].ToString();
if (column.dbType.Contains("char") && column.length == "-1")
{
column.length = "50";
}
if (column.dbType.Contains("int") || column.dbType.Contains("date") || column.dbType.Contains("bit"))
{
column.length = "";
}
if (column.dbType.Contains("bit"))
{
if (column.defaultValue == "(0)")
{
column.defaultValue = "0";
}
else if (column.defaultValue == "(1)")
{
column.defaultValue = "1";
}
}
column.ChangedProperties.Clear();
result.Add(column);
}
return(result);
}
/// <summary>
/// 绑定显示数据
/// </summary>
public void DataBind()
{
if (this.DataSource != null)
{
txtTitle.Text = this.DataSource.Table.Name + " " + DataSource.Table.caption;
gridColumns.RowDefinitions.Clear();
gridColumns.Children.Clear();
for (int i = 0; i < DataSource.Columns.Length; i++)
{
RowDefinition rowdef = new RowDefinition();
rowdef.Height = GridLength.Auto;
gridColumns.RowDefinitions.Add(rowdef);
EJ.DBColumn Column = DataSource.Columns[i];
TextBox t1 = new MyTextBox();
t1.Tag = Column.id.GetValueOrDefault();
var caption = Column.caption;
if (caption == null)
{
caption = "";
}
var text = caption.Split('\n')[0].Trim();
string tooltip = null;
if (!string.IsNullOrEmpty(Column.EnumDefine))
{
caption += "\r\n\r\n" + Column.EnumDefine;
}
if (text != caption)
{
text += "...";
tooltip = caption;
}
t1.Text = text;
t1.ToolTip = tooltip;
setTextBoxStyle(t1, 0);
Grid.SetRow(t1, i);
gridColumns.Children.Add(t1);
if (string.IsNullOrEmpty(Column.ClassName))
{
TextBox t2 = new MyTextBox();
t2.Text = Column.Name;
setTextBoxStyle(t2, 1);
Grid.SetRow(t2, i);
gridColumns.Children.Add(t2);
}
else
{
StackPanel panel = new StackPanel();
panel.Orientation = Orientation.Horizontal;
Grid.SetRow(panel, i);
Grid.SetColumn(panel, 1);
gridColumns.Children.Add(panel);
TextBox tClass = new MyTextBox();
tClass.TextAlignment = TextAlignment.Left;
tClass.Text = Column.ClassName + ".";
tClass.Foreground = new SolidColorBrush((Color)ColorConverter.ConvertFromString("#2b91af"));
tClass.FontWeight = FontWeights.Bold;
setTextBoxStyle(tClass, -1);
panel.Children.Add(tClass);
TextBox t2 = new MyTextBox();
t2.TextAlignment = TextAlignment.Left;
t2.Text = Column.Name;
setTextBoxStyle(t2, -2);
panel.Children.Add(t2);
}
TextBox t3 = new MyTextBox();
if (Column.CanNull == true)
{
t3.Text = $"{Column.dbType}?";
}
else
{
t3.Text = Column.dbType;
}
setTextBoxStyle(t3, 2);
Grid.SetRow(t3, i);
gridColumns.Children.Add(t3);
TextBox t4 = new MyTextBox();
t4.Text = Column.length;
setTextBoxStyle(t4, 3);
Grid.SetRow(t4, i);
gridColumns.Children.Add(t4);
}
}
}