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