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