/// <summary> /// 创建列 /// </summary> /// <param name="table"></param> /// <param name="column"></param> private static void CreateColumn(DBSession session, string tbName, DBColumn column) { string sql = string.Format("alter table {0} add {1} {2} {3} {4}", tbName, column.Name, column.DataType, string.IsNullOrWhiteSpace(column.Default) ? "" : "default " + column.Default, column.NotNull ? "NOT NULL" : ""); session.ExecuteNonQuery(sql); }
/// <summary> /// 创建表 /// </summary> /// <param name="dbTable"></param> private static void CreateTable(DBSession session, DBContext dbContext, DBTable dbTable) { StringBuilder sql_colums = new StringBuilder(); StringBuilder sql_pri = new StringBuilder(); //添加主键字段 foreach (var item in dbTable.PrimaryKey) { sql_colums.AppendFormat(",{0} {1} NOT NULL", item.Name, item.DataType); sql_pri.AppendFormat(",{0}", item.Name); } //添加字段 foreach (var item in dbTable.ColumnList) { sql_colums.AppendFormat(",{0} {1}", item.Name, item.DataType); if (!string.IsNullOrWhiteSpace(item.Default)) { sql_colums.AppendFormat(" default {0}", item.Default); } if (item.NotNull) { sql_colums.Append(" NOT NULL"); } } if (dbContext.DataType == DataBaseType.SQLServer)//添加sql server主键 { sql_colums.AppendFormat(",constraint PK_{0} primary key ({1})", dbTable.Name, sql_pri.ToString().TrimStart(',')); } //创建表 session.ExecuteNonQuery(string.Format("create table {0} ({1})", dbTable.Name, sql_colums.ToString().TrimStart(','))); if (dbContext.DataType == DataBaseType.Oracle)//添加Oracle主键 { session.ExecuteNonQuery(string.Format("alter table {0} add constraint PK_{0} primary key ({1})", dbTable.Name, sql_pri.ToString().TrimStart(','))); } }
/// <summary> /// 验证数据库表是否存在,如果不存在则创建表 /// </summary> /// <param name="table"></param> /// <param name="tbName"></param> /// <returns></returns> private static bool CheckTableAndCreate(DBTable table, string tbName) { bool isExist = false; using (DBSession session = DBSession.TryGet()) { try { session.ExecuteScalar <int>(string.Format("select count(1) from {0}", tbName)); isExist = true; } catch (Exception) { isExist = false; } //创建表 if (!isExist) { try { string createSql = table.CreateSql.ToUpper(). Replace(table.Name.ToUpper(), tbName.ToUpper()); string[] sqls = createSql.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); session.BeginTransaction(); foreach (string item in sqls) { if (!string.IsNullOrWhiteSpace(item.Trim())) { session.ExecuteNonQuery(item); } } session.Commit(); return(true); } catch (Exception ex) { session.Rollback(); throw ex; } } } return(true); }