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