public void CreateTable(SqlHelper sqlhelper, DbTransaction tran, string className, string tableName) { var columns = IntrospectionManager.GetColumns(className); var columnsArr = columns.Select(x => { var memberType = IntrospectionManager.GetMemberType(className, x.Key); var columnType = IntrospectionManager.GetColumnType(className, x.Key); var length = IntrospectionManager.GetColumnLength(className, x.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, x.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, x.Key); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, x.Key); var nullAbleSql = isNullAble ? "" : "not null"; nullAbleSql = isPk || isAutoGrow ? "not null" : nullAbleSql; var autoGrowSql = isAutoGrow ? "identity(1,1)" : ""; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (isPk) { return(string.Format("[{0}] {1} primary key {2}", x.Value, lastColumnType, autoGrowSql)); } return(string.Format("[{0}] {1} {2} {3}", x.Value, lastColumnType, autoGrowSql, nullAbleSql)); }); var columnsSql = string.Join(",", columnsArr); var createTable = "create table [" + tableName + "](" + columnsSql + ")"; if (tran == null) { sqlhelper.ExecuteNonQuery(createTable); } else { sqlhelper.ExecuteNonQuery(tran, createTable); } }
public string DropOldTable(SqlHelper sqlHelper, DbTransaction tran, string className, string tableName, string oldTableName) { var columns = IntrospectionManager.GetColumns(className); var columnsArr = string.Join(",", columns.Values.Select(x => "\"" + x + "\"")); sqlHelper.ExecuteNonQuery(tran, string.Format("insert into \"{0}\"({2}) select {2} from \"{1}\"", tableName, oldTableName, columnsArr)); sqlHelper.ExecuteNonQuery(tran, string.Format("drop table \"{0}\";", oldTableName)); return(""); }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { #region Alter var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var needRebuild = false; foreach (var column in columns) { #region Alter var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName, lastColumnType, length, isNullAble, isPk, isRealPk, isAutoGrow, isRealAutoGrow)) { continue; } needRebuild = true; break; } else { needRebuild = true; break; } #endregion } if (needRebuild) { ModiyTable(sqlhelper, className, tableName); } #endregion } else { CreateTable(sqlhelper, null, className, tableName); } }
public void ModiyTable(SqlHelper sqlhelper, DbTransaction tran, string className, string newTableName, string oldTableName) { var columns = IntrospectionManager.GetColumns(className); var columnsArr = string.Join(",", columns.Values.Select(x => "[" + x + "]")); CreateTable(sqlhelper, tran, className, newTableName); var sql = string.Format("insert into [{0}]({1}) select {1} from [{2}];", newTableName, columnsArr, oldTableName); sqlhelper.ExecuteNonQuery(tran, sql); sqlhelper.ExecuteNonQuery(tran, "drop table [" + oldTableName + "]"); }
public void CreateTable(SqlHelper sqlhelper, string className, string tableName) { var columns = IntrospectionManager.GetColumns(className); var columnsArr = columns.Select(x => { var memberType = IntrospectionManager.GetMemberType(className, x.Key); var columnType = IntrospectionManager.GetColumnType(className, x.Key); var length = IntrospectionManager.GetColumnLength(className, x.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, x.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, x.Key); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, x.Key); var nullAbleSql = isNullAble ? "" : "not null"; nullAbleSql = isPk || isAutoGrow ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (isPk) { return(string.Format("\"{0}\" {1} primary key", x.Value, lastColumnType)); } return(string.Format("\"{0}\" {1} {2}", x.Value, lastColumnType, nullAbleSql)); }); var columnsSql = string.Join(",", columnsArr); var createTable = "create table \"" + tableName + "\"(" + columnsSql + ")"; sqlhelper.ExecuteNonQuery(createTable); var autoGrowName = IntrospectionManager.GetAutoGrowColumnName(className); if (!string.IsNullOrWhiteSpace(autoGrowName)) { var getconstraint = string.Format( "select sequence_name,increment_by,last_number from user_sequences where upper(sequence_name)=upper('{0}')", "SEQ_" + tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); if (!constraints.Any()) { var addconstraint = string.Format("create sequence \"SEQ_{0}\" increment by 1 start with 1 nomaxvalue nocycle", tableName); sqlhelper.ExecuteNonQuery(addconstraint); } var createtriggers = string.Format( "create or replace trigger \"TRIGGER_{0}_{1}\" before insert on \"{0}\" for each row " + "declare newid number(18,0);" + " begin " + "select \"{2}\".nextval into newid from dual; " + ":new.\"{1}\" := newid; " + "end;", tableName, autoGrowName, "SEQ_" + tableName); sqlhelper.ExecuteNonQuery(createtriggers); } }
public static string Generate() { var sqlHelpers = IntrospectionManager.GetSqlHelpers(); var csharpCode = new StringBuilder(); csharpCode.Append("using System;" + Environment.NewLine); csharpCode.Append("using DataVeryLite.Util;" + Environment.NewLine); var defaultNameSpace = Assembly.GetCallingAssembly().FullName.Split(',')[0]; csharpCode.AppendFormat("namespace {0}.{1}" + Environment.NewLine, defaultNameSpace, "Model"); csharpCode.Append("{" + Environment.NewLine); foreach (var sqlHelper in sqlHelpers.Values) { var dataBaseName = sqlHelper.DataBaseName; var dataBaseType = sqlHelper.DataBaseType; var key = sqlHelper.Key; csharpCode.AppendFormat("namespace {0}" + Environment.NewLine, dataBaseName); csharpCode.Append("{" + Environment.NewLine); csharpCode.AppendFormat("[DataVeryLite.DataBase(Name = \"{0}\",Key = \"{1}\")]" + Environment.NewLine, dataBaseName, key); csharpCode.AppendFormat("public class {0}:{1}<{2}>" + Environment.NewLine, dataBaseName, ProviderManager.GetEntityPool(dataBaseType), dataBaseName); csharpCode.Append("{" + Environment.NewLine); csharpCode.Append("}" + Environment.NewLine); List <dynamic> tables = sqlHelper.GetTables(); foreach (var table in tables) { var idValue = sqlHelper.GetPrimaryKey(table.name); csharpCode.AppendFormat("[DataVeryLite.Table(Name = \"{0}\",EntityPool = typeof({1}))]" + Environment.NewLine, table.name, dataBaseName); csharpCode.AppendFormat("public partial class {0} : {1}" + Environment.NewLine, table.name, ProviderManager.GetEntity(dataBaseType)); csharpCode.Append("{" + Environment.NewLine); List <dynamic> columns = sqlHelper.GetColumns(table.name); foreach (var column in columns) { if (column.name == idValue) { csharpCode.AppendFormat("[DataVeryLite.Column(Name = \"{0}\",IsPrimaryKey = true)]" + Environment.NewLine, column.name); } else { csharpCode.AppendFormat("[DataVeryLite.Column(Name = \"{0}\")]" + Environment.NewLine, column.name); } csharpCode.AppendFormat("public {0} {1} {2} get; set; {3}", sqlHelper.GetColumnType(table.name, column.name), column.name, "{", "}"); } csharpCode.Append("}" + Environment.NewLine); } csharpCode.Append("}" + Environment.NewLine); } csharpCode.Append("}" + Environment.NewLine); return(csharpCode.ToString()); }
/// <summary> /// Sql CRUD provider and helper /// </summary> /// <param name="key">ConnectionString key in config</param> /// <param name="driverPath">Your driver path,the default path is strat up dir</param> public SqlHelper(string key, string driverPath = null) { if (!IntrospectionManager.IsExistsKey(key)) { var exception = new ConnectionStringKeyNotExists(key); throw exception; } _key = key; _dbConnectionString = IntrospectionManager.GetConnectionString(key); _providerName = IntrospectionManager.GetProviderName(key); setDatabaseType(); setDatabaseName(); if (driverPath == null) { setDriverAssembly(AppDomain.CurrentDomain.BaseDirectory); } else { setDriverAssembly(driverPath); } if (_dataBaseType == DataBaseNames.Sqlite) { var builder = new DbConnectionStringBuilder(); builder.ConnectionString = _dbConnectionString; object database = ""; builder.TryGetValue("Data Source", out database); if (!System.IO.File.Exists(database.ToString())) { string accessPath = AppDomain.CurrentDomain.BaseDirectory + database.ToString(); if (System.IO.File.Exists(accessPath)) { _dbConnectionString = "Data Source=" + accessPath; } } } if (_driverAssembly != null) { string info = string.Format("[Prvider:'{0}'][Key:'{1}'][Db:'{2}'] {3}", _dataBaseType, _key, _dataBaseName, "Create sqlhelper instance sucessed!"); LogHelper.LogInfo(info); } else { string info = string.Format("[Prvider:'{0}'][Key:'{1}'][Db:'{2}'] {3}", _dataBaseType, _key, _dataBaseName, "Create sqlhelper instance fail!"); LogHelper.LogError(info); } }
private void setDriverAssembly(string path) { var driver = IntrospectionManager.GetDriver(_providerName); if (driver != null) { _driverAssembly = driver; return; } try { _driverAssembly = ProviderManager.GetProvider(_dataBaseType).GetAssembly(path + "\\Drivers\\"); } catch (Exception) { string info = string.Format("[Provider:'{0}'][Key:'{1}'][Db:'{2}'] {3}", _dataBaseType, _key, _dataBaseName, "Can't found driver!"); LogHelper.LogWarning(info); } }
public void CreateTable(SqlHelper sqlhelper, DbTransaction tran, string className, string tableName) { var columns = IntrospectionManager.GetColumns(className); var columnsArr = columns.Select(x => { var memberType = IntrospectionManager.GetMemberType(className, x.Key); var columnType = IntrospectionManager.GetColumnType(className, x.Key); var length = IntrospectionManager.GetColumnLength(className, x.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, x.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, x.Key); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, x.Key); var nullAbleSql = isNullAble ? "" : "not null"; nullAbleSql = isPk || isAutoGrow ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (memberType == typeof(long) || memberType == typeof(ulong)) { lastColumnType = isAutoGrow ? "bigserial" : lastColumnType; } else { lastColumnType = isAutoGrow ? "serial" : lastColumnType; } if (isPk) { return(string.Format("{0} {1} primary key", "\"" + x.Value + "\"", lastColumnType)); } return("\"" + x.Value + "\" " + lastColumnType + " " + nullAbleSql); }); var columnsSql = string.Join(",", columnsArr); var createTable = "create table \"" + tableName + "\"(" + columnsSql + ")"; if (tran == null) { sqlhelper.ExecuteNonQuery(createTable); } else { sqlhelper.ExecuteNonQuery(tran, createTable); } }
public void ModiyTable(SqlHelper sqlhelper, string className, string tableName) { DbConnection conn = sqlhelper.GetConn(); conn.Open(); DbTransaction tran = conn.BeginTransaction(); try { var columns = IntrospectionManager.GetColumns(className); var columnsArr = string.Join(",", columns.Values.Select(x => "[" + x + "]")); var newTableName = tableName + DateTime.Now.ToString("yyyyMMdd_HHmmss"); CreateTable(sqlhelper, tran, className, newTableName); var autoGrowName = IntrospectionManager.GetAutoGrowColumnName(className); var sql = ""; if (!string.IsNullOrWhiteSpace(autoGrowName)) { sql = "set identity_insert [" + newTableName + "] on;"; } sql += string.Format("insert into [{0}]({1}) select {1} from [{2}];", newTableName, columnsArr, tableName); if (!string.IsNullOrWhiteSpace(autoGrowName)) { sql += "set identity_insert [" + newTableName + "] off;"; } sqlhelper.ExecuteNonQuery(tran, sql); sqlhelper.ExecuteNonQuery(tran, "drop table [" + tableName + "]"); sqlhelper.ExecuteNonQuery(tran, "sp_rename '" + newTableName + "','" + tableName + "'"); tran.Commit(); } catch (Exception ex) { tran.Rollback(); LogHelper.LogError(ex.ToString()); } finally { conn.Close(); } }
private void ModiyTable(SqlHelper sqlhelper, string className, string tableName) { var newTableName = tableName + DateTime.Now.ToString("yyyyMMdd_HHmmss"); DbConnection conn = sqlhelper.GetConn(); conn.Open(); DbTransaction tran = conn.BeginTransaction(); try { CreateTable(sqlhelper, tran, className, newTableName); //var columns = IntrospectionManager.GetColumns(className); //var columnsArr = string.Join(",", columns.Values.Select(x => "\"" + x + "\"")); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var columnsArr = string.Join(",", cols.Select(x => "\"" + x.name + "\"")); sqlhelper.ExecuteNonQuery(tran, string.Format("insert into \"{0}\"({2}) select {2} from \"{1}\"", newTableName, tableName, columnsArr)); sqlhelper.ExecuteNonQuery(tran, "drop table \"" + tableName + "\""); sqlhelper.ExecuteNonQuery(tran, string.Format("alter table \"{0}\" rename to \"{1}\"", newTableName, tableName)); tran.Commit(); } catch (Exception ex) { tran.Rollback(); LogHelper.LogError(ex.ToString()); } finally { conn.Close(); } //var pkName = Warp.ShieldLogSql(() => GetPrimaryKey(sqlhelper, "", tableName)); var autoGrowName = IntrospectionManager.GetAutoGrowColumnName(className); if (!string.IsNullOrWhiteSpace(autoGrowName)) { sqlhelper.ExecuteNonQuery(string.Format("alter sequence \"{0}_{1}_seq\" rename to \"{2}_{1}_seq\"", newTableName, autoGrowName, tableName)); } }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { #region Alter var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var needRebuild = false; foreach (var column in columns) { var columnName = IntrospectionManager.GetColumnName(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); if (isAutoGrow != isRealAutoGrow) { needRebuild = true; } } if (needRebuild) { ModiyTable(sqlhelper, className, tableName); return; } foreach (var column in columns) { #region Alter var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var autoGrowSql = isAutoGrow ? "identity(1,1)" : ""; var nullAbleSql = isNullAble ? " null" : " not null"; nullAbleSql = isPk || isRealAutoGrow ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName, lastColumnType, length, isNullAble, isPk, isRealPk, isAutoGrow, isRealAutoGrow)) { continue; } if (isPk != isRealPk) { var getconstraint = string.Format("select constraint_name name from information_schema.key_column_usage where table_name='{0}'", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); foreach (var constraint in constraints) { var dropconstraint = "alter table [" + tableName + "] drop constraint [" + constraint.name + "]"; sqlhelper.ExecuteNonQuery(dropconstraint); } } if (isPk && isPk != isRealPk) { var alterSql = string.Format("alter table [{0}] alter column [{1}] {2} not null", tableName, columnName, lastColumnType); sqlhelper.ExecuteNonQuery(alterSql); alterSql = string.Format("alter table [{0}] add constraint [{1}] primary key([{2}])", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } else { var alterSql = string.Format("alter table [{0}] alter column [{1}] {2} {3}", tableName, columnName, lastColumnType, nullAbleSql); sqlhelper.ExecuteNonQuery(alterSql); } } else { if (isPk != isRealPk) { var getconstraint = string.Format("select name from sysobjects so " + "join sysconstraints sc " + "on so.id = sc.constid " + "where object_name(so.parent_obj) = '{0}'" + " and so.xtype = 'PK'", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); foreach (var constraint in constraints) { var dropconstraint = "alter table [" + tableName + "] drop constraint [" + constraint.name + "]"; sqlhelper.ExecuteNonQuery(dropconstraint); } } var alterSql = string.Format("alter table [{0}] add [{1}] {2} {3} {4}", tableName, columnName, lastColumnType, nullAbleSql, autoGrowSql); sqlhelper.ExecuteNonQuery(alterSql); if (isPk != isRealPk) { alterSql = string.Format("alter table [{0}] add constraint [{1}] primary key([{2}])", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } } #endregion } #endregion } else { CreateTable(sqlhelper, null, className, tableName); } }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var needRebuild = false; foreach (var column in columns) { var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName, lastColumnType, length, isNullAble, isPk, isRealPk)) { continue; } needRebuild = true; break; } else { needRebuild = true; break; } } if (needRebuild) { DbConnection conn = sqlhelper.GetConn(); conn.Open(); DbTransaction tran = conn.BeginTransaction(); try { var oldName = RenameTable(sqlhelper, tran, tableName); CreateTable(sqlhelper, tran, className, tableName); DropOldTable(sqlhelper, tran, className, tableName, oldName); tran.Commit(); } catch (Exception ex) { tran.Rollback(); LogHelper.LogError(ex.ToString()); } finally { conn.Close(); } } } else { CreateTable(sqlhelper, null, className, tableName); } }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var realAutoGrowName = GetRealAutoGrowName(sqlhelper, tableName); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); foreach (var column in columns) { var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var autoGrowSql = isAutoGrow ? "auto_increment" : ""; var nullAbleSql = isNullAble ? " null" : " not null"; nullAbleSql = isPk ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName, lastColumnType, length, isNullAble, isPk, isRealPk, isAutoGrow, isRealAutoGrow)) { continue; } if (isPk != isRealPk) { if (realAutoGrowName != null) { var dropautogrow = string.Format("alter table `{0}` change `{1}` `{1}` {2} not null", tableName, realAutoGrowName, lastColumnType); sqlhelper.ExecuteNonQuery(dropautogrow); } var getconstraint = string.Format( "select constraint_name name from information_schema.key_column_usage where table_name='{0}'", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); if (constraints.Any()) { var dropconstraint = "alter table `" + tableName + "` drop primary key"; sqlhelper.ExecuteNonQuery(dropconstraint); } } if (isPk && isPk != isRealPk) { var alterSql = string.Format("alter table `{0}` add primary key(`{1}`)", tableName, columnName); sqlhelper.ExecuteNonQuery(alterSql); alterSql = string.Format("alter table `{0}` modify column `{1}` {2} not null {3}", tableName, columnName, lastColumnType, autoGrowSql); sqlhelper.ExecuteNonQuery(alterSql); } else { if (isRealPk) { if (realAutoGrowName != null && isRealAutoGrow) { var dropautogrow = string.Format("alter table `{0}` change `{1}` `{1}` {2} not null", tableName, realAutoGrowName, lastColumnType); sqlhelper.ExecuteNonQuery(dropautogrow); } var alterSql1 = string.Format("alter table `{0}` drop primary key", tableName); sqlhelper.ExecuteNonQuery(alterSql1); } var alterSql = string.Format("alter table `{0}` modify column `{1}` {2} {3}", tableName, columnName, lastColumnType, nullAbleSql); sqlhelper.ExecuteNonQuery(alterSql); } } else { if (isPk != isRealPk) { if (realAutoGrowName != null) { var dropautogrow = string.Format("alter table `{0}` change `{1}` `{1}` {2} not null", tableName, realAutoGrowName, lastColumnType); sqlhelper.ExecuteNonQuery(dropautogrow); } var getconstraint = string.Format( "select constraint_name name from information_schema.key_column_usage where table_name='{0}'", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); if (constraints.Any()) { var dropconstraint = "alter table `" + tableName + "` drop primary key"; sqlhelper.ExecuteNonQuery(dropconstraint); } } var alterSql = string.Format("alter table `{0}` add `{1}` {2} {3} {4}", tableName, columnName, lastColumnType, nullAbleSql, autoGrowSql); sqlhelper.ExecuteNonQuery(alterSql); if (isPk != isRealPk) { alterSql = string.Format("alter table `{0}` add primary key(`{1}`)", tableName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } } } } else { var columns = IntrospectionManager.GetColumns(className); var columnsArr = columns.Select(x => { var memberType = IntrospectionManager.GetMemberType(className, x.Key); var columnType = IntrospectionManager.GetColumnType(className, x.Key); var length = IntrospectionManager.GetColumnLength(className, x.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, x.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, x.Key); var autoGrowSql = isAutoGrow ? "auto_increment" : ""; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (isPk) { return(string.Format("`{0}` {1} primary key {2}", x.Value, lastColumnType, autoGrowSql)); } return(string.Format("`{0}` {1} {2}", x.Value, lastColumnType, autoGrowSql)); }); var columnsSql = string.Join(",", columnsArr); var createTable = "create table `" + tableName + "`(" + columnsSql + ")"; sqlhelper.ExecuteNonQuery(createTable); } }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { #region Alter var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); foreach (var column in columns) { #region Alter var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var nullAbleSql = isNullAble ? " null" : " not null"; nullAbleSql = isPk || isRealAutoGrow ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName, lastColumnType, length, isNullAble, isPk, isRealPk, isAutoGrow, isRealAutoGrow)) { continue; } if (isPk != isRealPk) { var getconstraint = string.Format("select constraint_name name from user_cons_columns where constraint_name=(select constraint_name from user_constraints where upper(table_name)=upper('{0}') and constraint_type='P')", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); foreach (var constraint in constraints) { var dropconstraint = "alter table \"" + tableName + "\" drop constraint \"" + constraint.NAME + "\""; sqlhelper.ExecuteNonQuery(dropconstraint); } } if (isPk && isPk != isRealPk) { var alterSql = string.Format("alter table \"{0}\" modify(\"{1}\" {2})", tableName, columnName, lastColumnType); sqlhelper.ExecuteNonQuery(alterSql); alterSql = string.Format("alter table \"{0}\" add constraint \"{1}\" primary key(\"{2}\")", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } else { var colsNew = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var isNullableChange = !colsNew.Any(x => x.name.ToUpper() == columnName.ToUpper() && x.isnullable == isNullAble.ToYN()); var alterSql = string.Format("alter table \"{0}\" modify(\"{1}\" {2})", tableName, columnName, lastColumnType); sqlhelper.ExecuteNonQuery(alterSql); if (!isPk && isNullableChange) { alterSql = string.Format("alter table \"{0}\" modify(\"{1}\" {2})", tableName, columnName, nullAbleSql); sqlhelper.ExecuteNonQuery(alterSql); } } if (isAutoGrow != isRealAutoGrow) { var getconstraint = string.Format( "select sequence_name,increment_by,last_number from user_sequences where upper(sequence_name)=upper('{0}')", "SEQ_" + tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); if (!constraints.Any()) { var addconstraint = string.Format("create sequence \"SEQ_{0}\" increment by 1 start with 1 nomaxvalue nocycle", tableName); sqlhelper.ExecuteNonQuery(addconstraint); } var gettriggers = string.Format("select trigger_name from user_triggers where table_name='{0}' and trigger_name='{1}'", tableName, "TRIGGER_" + tableName + "_" + columnName); var triggers = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(gettriggers)); if (isAutoGrow && !triggers.Any()) { var createtriggers = string.Format("create or replace trigger \"TRIGGER_{0}_{1}\" before insert on \"{0}\" for each row " + "declare newid number(18,0);" + " begin " + "select \"{2}\".nextval into newid from dual; " + ":new.\"{1}\" := newId; " + "end;", tableName, columnName, "SEQ_" + tableName); sqlhelper.ExecuteNonQuery(createtriggers); } else if (!isAutoGrow && triggers.Any()) { var createtriggers = string.Format("drop trigger \"TRIGGER_{0}_{1}\" ", tableName, columnName); sqlhelper.ExecuteNonQuery(createtriggers); } } } else { if (isPk != isRealPk) { var getconstraint = string.Format("select constraint_name name from user_cons_columns where constraint_name=(select constraint_name from user_constraints where upper(table_name)=upper('{0}') and constraint_type='P')", tableName); var constraints = Warp.ShieldLogSql(() => sqlhelper.ExecuteDynamic(getconstraint)); foreach (var constraint in constraints) { var dropconstraint = "alter table \"" + tableName + "\" drop constraint \"" + constraint.NAME + "\""; sqlhelper.ExecuteNonQuery(dropconstraint); } } var alterSql = string.Format("alter table \"{0}\" add (\"{1}\" {2} {3} )", tableName, columnName, lastColumnType, nullAbleSql); sqlhelper.ExecuteNonQuery(alterSql); if (isPk != isRealPk) { alterSql = string.Format("alter table \"{0}\" add constraint \"{1}\" primary key(\"{2}\")", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } } #endregion } #endregion } else { CreateTable(sqlhelper, className, tableName); } }
public void SyncDbInfo(string key, string className, string tableName) { var sqlhelper = IntrospectionManager.GetSqlHelperByKey(key); if (IsNeedAddTable(sqlhelper, tableName)) { var columns = IntrospectionManager.GetColumnAttributes(className).OrderByDescending(x => x.Value.IsPrimaryKey); var cols = Warp.ShieldLogSql(() => GetColumns(sqlhelper, sqlhelper.DataBaseName, tableName)); var needRebuild = false; foreach (var column in columns) { var columnName = IntrospectionManager.GetColumnName(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); if (isAutoGrow != isRealAutoGrow) { needRebuild = true; } } if (needRebuild) { DbConnection conn = sqlhelper.GetConn(); conn.Open(); DbTransaction tran = conn.BeginTransaction(); try { var newTableName = tableName + DateTime.Now.ToString("yyyyMMdd_HHmmss"); ModiyTable(sqlhelper, tran, className, newTableName, tableName); ModiyTable(sqlhelper, tran, className, tableName, newTableName); tran.Commit(); } catch (Exception ex) { tran.Rollback(); LogHelper.LogError(ex.ToString()); } finally { conn.Close(); } return; } foreach (var column in columns) { var memberType = IntrospectionManager.GetMemberType(className, column.Key); var columnType = IntrospectionManager.GetColumnType(className, column.Key); var columnName = IntrospectionManager.GetColumnName(className, column.Key); var length = IntrospectionManager.GetColumnLength(className, column.Key); var isAutoGrow = IntrospectionManager.GetColumnIsAutoGrow(className, column.Key); //var isRealAutoGrow = IsRealAutoGrow(sqlhelper, tableName, columnName); var isNullAble = IntrospectionManager.GetColumnIsNullAble(className, column.Key); var isPk = IntrospectionManager.GetColumnIsPrimaryKey(className, column.Key); var isRealPk = IsRealPkInDb(sqlhelper, tableName, columnName); var autoGrowSql = isAutoGrow ? "autoincrement" : ""; var nullAbleSql = isNullAble ? " null" : " not null"; nullAbleSql = isPk ? " not null" : nullAbleSql; var lastColumnType = GetColumnTypeByMebmberType(memberType, columnType.ToLower(), length); if (cols.Any(x => x.name.ToUpper() == columnName.ToUpper())) { if (!IsNeedModiy(cols, memberType, columnName.ToUpper(), lastColumnType, length, isNullAble, isPk, isRealPk)) { continue; } if (isPk != isRealPk) { var constrintName = GetPrimaryKeyContrantName(sqlhelper, "", tableName); var dropconstraint = "alter table [" + tableName + "] drop constraint [" + constrintName + "]"; sqlhelper.ExecuteNonQuery(dropconstraint); } if (isPk && isPk != isRealPk) { var alterSql = string.Format("alter table [{0}] alter column [{1}] {2} not null", tableName, columnName, lastColumnType); sqlhelper.ExecuteNonQuery(alterSql); alterSql = string.Format("alter table [{0}] add constraint [{1}] primary key([{2}])", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } else { var alterSql = string.Format("alter table [{0}] alter column [{1}] {2} {3}", tableName, columnName, lastColumnType, nullAbleSql); sqlhelper.ExecuteNonQuery(alterSql); } } else { if (isPk) { var constrintName = GetPrimaryKeyContrantName(sqlhelper, "", tableName); var dropconstraint = "alter table [" + tableName + "] drop constraint [" + constrintName + "]"; sqlhelper.ExecuteNonQuery(dropconstraint); } var alterSql = string.Format("alter table [{0}] add [{1}] {2} {3} {4}", tableName, columnName, lastColumnType, nullAbleSql, autoGrowSql); sqlhelper.ExecuteNonQuery(alterSql); if (isPk) { alterSql = string.Format("alter table [{0}] add constraint [{1}] primary key([{2}])", tableName, "pk_" + tableName + "_" + columnName, columnName); sqlhelper.ExecuteNonQuery(alterSql); } } } } else { CreateTable(sqlhelper, null, className, tableName); } }