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