/// <summary> /// 判断主键是否有异动 /// </summary> /// <param name="tableName"></param> /// <param name="pks"></param> /// <returns></returns> private bool IsPkChange(string tableName, DataColumn[] pks) { bool change = false; LibDataAccess dataAccess = new LibDataAccess(); HashSet <string> dbPks = new HashSet <string>(); using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("select cu.COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '{0}'", tableName))) { while (reader.Read()) { string colName = reader["COLUMN_NAME"].ToString(); dbPks.Add(colName); } } if (pks.Length == dbPks.Count) { foreach (var item in pks) { if (!dbPks.Contains(item.ColumnName)) { change = true; break; } } } else { change = true; } //主键是否变更 return(change); }
/// <summary> /// 检测数据库中对象是否存在 /// </summary> /// <param name="name">对象名称</param> /// <param name="type">对象类别</param> /// <returns></returns> public bool ExistsObject(string name, DBObjectType type) { LibDataAccess dataAccess = new LibDataAccess(); string sql = ""; switch (type) { case DBObjectType.Table: sql = string.Format(@"select count(TABLE_NAME) FROM all_tables WHERE TABLE_NAME = '{0}'", name); break; case DBObjectType.Column: string[] nameArrar = name.Split('.'); sql = string.Format(@"select column_name from user_tab_columns where table_name=upper('{0}') and column_name=upper('{0}')", nameArrar[0], nameArrar[1]); break; default: break; } string _count = dataAccess.ExecuteScalar(sql).ToString(); return(_count == "0" ? false : true); }
/// <summary> /// 检测数据库中对象是否存在 /// </summary> /// <param name="name">对象名称</param> /// <param name="type">对象类别</param> /// <returns></returns> public bool ExistsObject(string name, DBObjectType type) { LibDataAccess dataAccess = new LibDataAccess(); string sql = ""; switch (type) { case DBObjectType.Table: sql = string.Format(@"SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type in (N'U')", name); break; case DBObjectType.Column: string[] nameArrar = name.Split('.'); sql = string.Format(@"SELECT name FROM sys.syscolumns WHERE name='{0}' and id=(SELECT MAX(id) FROM sys.objects WHERE object_id = OBJECT_ID(N'[{1}]') AND type in (N'U'))", nameArrar[1], nameArrar[0]); break; default: break; } string _name = dataAccess.ExecuteScalar(sql) as string; return(string.IsNullOrEmpty(_name) ? false : true); }
/// <summary> /// 获取表的索引 /// </summary> /// <param name="tableName"></param> /// <returns></returns> private Dictionary <string, DBIndexInfo> GetDBIndex(string tableName) { Dictionary <string, DBIndexInfo> dic = new Dictionary <string, DBIndexInfo>(); string sql = string.Format("select t.INDEX_NAME as IndexName,t.DESCEND as IsDesc,t.COLUMN_NAME as ColumnName,i.uniqueness as IsUnique from user_ind_columns t,user_indexes i where t.index_name = i.index_name and " + "t.table_name='{0}'", tableName); string pkIndex = string.Format("PK_{0}", tableName); LibDataAccess dataAccess = new LibDataAccess(); using (IDataReader reader = dataAccess.ExecuteDataReader(sql)) { while (reader.Read()) { string idxName = reader["IndexName"].ToString(); if (string.Compare(pkIndex, idxName, true) == 0) { continue; } bool isUnique = Convert.ToString(reader["IsUnique"]) == "UNIQUE"; IndexOrderWay orderWay = Convert.ToString(reader["IsDesc"]) == "ASC" ? IndexOrderWay.ASC : IndexOrderWay.DESC; string colName = reader["ColumnName"].ToString(); if (!dic.ContainsKey(idxName)) { DBIndexInfo indexs = new DBIndexInfo(); indexs.IsUnique = isUnique; dic.Add(idxName, indexs); } DBIndexInfo dbIndexs = dic[idxName]; DBIndexField indexFiled = new DBIndexField(colName, orderWay); dbIndexs.IndexFields.Add(indexFiled); } } return(dic); }
/// <summary> /// 判断主键是否有异动 /// </summary> /// <param name="tableName"></param> /// <param name="pks"></param> /// <returns></returns> private bool IsPkChange(string tableName, string[] pks) { bool change = false; HashSet <string> dbPks = new HashSet <string>(); LibDataAccess dataAccess = new LibDataAccess(); using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("EXEC sp_pkeys @table_name='{0}'", tableName))) { while (reader.Read()) { string colName = reader["COLUMN_NAME"].ToString(); dbPks.Add(colName); } } if (pks.Length == dbPks.Count) { foreach (var item in pks) { if (!dbPks.Contains(item)) { change = true; break; } } } else { change = true; } //主键是否变更 return(change); }
/// <summary> /// 获取表的索引 /// </summary> /// <param name="tableName"></param> /// <returns></returns> private Dictionary <string, DBIndexInfo> GetDBIndex(string tableName) { Dictionary <string, DBIndexInfo> dic = new Dictionary <string, DBIndexInfo>(); string sql = string.Format("SELECT idx.name as IndexName,idx.is_unique as IsUnique,idxCol.is_descending_key as IsDesc,col.name as ColumnName " + "FROM sys.indexes idx JOIN sys.index_columns idxCol " + "ON (idx.object_id = idxCol.object_id " + "AND idx.index_id = idxCol.index_id) " + "JOIN sys.tables tab ON (idx.object_id = tab.object_id) " + "JOIN sys.columns col ON (idx.object_id = col.object_id " + "AND idxCol.column_id = col.column_id) " + "where tab.name='{0}' and idx.is_primary_key<>1 " + "order by idx.index_id ", tableName); LibDataAccess dataAccess = new LibDataAccess(); using (IDataReader reader = dataAccess.ExecuteDataReader(sql)) { while (reader.Read()) { string idxName = reader["IndexName"].ToString(); bool isUnique = Convert.ToBoolean(reader["IsUnique"]); IndexOrderWay orderWay = Convert.ToBoolean(reader["IsDesc"]) ? IndexOrderWay.DESC : IndexOrderWay.ASC; string colName = reader["ColumnName"].ToString(); if (!dic.ContainsKey(idxName)) { DBIndexInfo indexs = new DBIndexInfo(); indexs.IsUnique = isUnique; dic.Add(idxName, indexs); } DBIndexInfo dbIndexs = dic[idxName]; DBIndexField indexFiled = new DBIndexField(colName, orderWay); dbIndexs.IndexFields.Add(indexFiled); } } return(dic); }
/// <summary> /// Drop the table from the database. /// </summary> /// <param name="tableName"></param> public void DropTable(string tableName) { string checkDelete = GetDropTable(tableName, false); LibDataAccess dataAccess = new LibDataAccess(); dataAccess.ExecuteNonQuery(checkDelete); }
public void CreateTable(TableSchema schema) { StringBuilder pkStr = new StringBuilder(); foreach (string item in schema.PRIMARY_KEY) { pkStr.AppendFormat("[{0}] ASC,", item); } StringBuilder columnStr = new StringBuilder(); StringBuilder defaultValueStr = new StringBuilder(); StringBuilder indexStr = new StringBuilder(); foreach (ColumnSchema item in schema.ColumnSchemaList) { columnStr.AppendLine(string.Format("{0},", item.ColumnTypeStr)); if (!string.IsNullOrEmpty(item.DefaultValueStr)) { defaultValueStr.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, item.DefaultValueStr, item.Name)); } } foreach (string item in schema.DBIndexStr) { indexStr.AppendLine(item); } if (columnStr.Length > 0) { columnStr.Remove(columnStr.Length - 1, 1); } if (pkStr.Length > 0) { pkStr.Remove(pkStr.Length - 1, 1); } StringBuilder strBuilder = new StringBuilder(); //strBuilder.AppendLine(string.Format("USE [{0}]", InitialCatalog)); strBuilder.AppendLine("SET ANSI_NULLS ON "); strBuilder.AppendLine("SET QUOTED_IDENTIFIER ON "); strBuilder.AppendLine("SET ANSI_PADDING ON "); strBuilder.AppendLine(string.Format("CREATE TABLE [dbo].[{0}]({1}", schema.Name, columnStr)); strBuilder.AppendLine(string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ", schema.Name)); strBuilder.AppendLine(string.Format("({0}) ", pkStr));//主键 strBuilder.AppendLine("WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF," + " ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"); strBuilder.AppendLine("SET ANSI_PADDING OFF "); //添加默认值 strBuilder.Append(defaultValueStr.ToString()); //添加索引 strBuilder.Append(indexStr); LibDataAccess dataAccess = new LibDataAccess(); dataAccess.ExecuteNonQuery(strBuilder.ToString()); }
/// <summary> /// Drop the table from the database. /// </summary> /// <param name="tableName"></param> public void DropTable(string tableName) { string drop = string.Format(@"declare num number; begin select count(TABLE_NAME) into num FROM all_tables WHERE TABLE_NAME = '{0}'; if num>0 then execute immediate 'drop table {0}'; end if; end;", tableName); LibDataAccess dataAccess = new LibDataAccess(); dataAccess.ExecuteNonQuery(drop); }
private Dictionary <string, DbFieldInfo> GetDbFieldInfo(LibDataAccess dataAccess, string tableName) { Dictionary <string, DbFieldInfo> dic = new Dictionary <string, DbFieldInfo>(); using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("select column_name,data_default,char_length from all_tab_columns where table_name='{0}'", tableName))) { do { while (reader.Read()) { if (!dic.ContainsKey(LibSysUtils.ToString(reader[0]))) { DbFieldInfo info = new DbFieldInfo(LibSysUtils.ToString(reader[1]), LibSysUtils.ToInt32(reader[2])); dic.Add(LibSysUtils.ToString(reader[0]), info); } } } while (reader.NextResult()); } return(dic); }
public void CreateTable(DataTable table) { string tableName = table.TableName; StringBuilder builder = new StringBuilder(); builder.AppendFormat("CREATE TABLE {0}(", tableName); foreach (DataColumn col in table.Columns) { FieldType fieldType = col.ExtendedProperties.ContainsKey(FieldProperty.FieldType) ? (FieldType)col.ExtendedProperties[FieldProperty.FieldType] : FieldType.None; if (fieldType == FieldType.None) { builder.AppendFormat("{0},", GetFieldInfo(col)); } } string pkStr = GetPkStr(table); builder.AppendLine(string.Format("constraints PK_{0} primary key({1})", tableName, pkStr)); builder.Append(")"); LibDataAccess dataAccess = new LibDataAccess(); dataAccess.ExecuteNonQuery(builder.ToString()); List <string> sqlList = new List <string>(); DBIndexCollection dbIndexes = table.ExtendedProperties.ContainsKey(TableProperty.DBIndex) ? (DBIndexCollection)table.ExtendedProperties[TableProperty.DBIndex] : null; if (dbIndexes != null) { foreach (DBIndex item in dbIndexes) { sqlList.Add(CreateIndexSql(item, tableName)); } } if (sqlList.Count > 0) { foreach (var sql in sqlList) { dataAccess.ExecuteNonQuery(sql); } } }
public void UpdateTable(DataTable table, bool isDelete) { LibDataAccess dataAccess = new LibDataAccess(); decimal count = (decimal)dataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) FROM all_tables WHERE TABLE_NAME = {0}", LibStringBuilder.GetQuotString(table.TableName))); if (count == 0) { CreateTable(table); } else { DataTable dtSchema = null; using (DbConnection conn = dataAccess.CreateConnection()) { conn.Open(); dtSchema = conn.GetSchema("Columns", new string[] { null, table.TableName }); } if (dtSchema != null) { Dictionary <string, DbFieldInfo> defaultDic = GetDbFieldInfo(dataAccess, table.TableName); //对删除的非聚集索引进行处理 StringBuilder tempBuilder = new StringBuilder(); List <string> deleteIndexList = new List <string>(); List <string> addIndexList = new List <string>(); DBIndexCollection dbIndex = table.ExtendedProperties[TableProperty.DBIndex] as DBIndexCollection; CompareDBIndex(table.TableName, dbIndex, addIndexList, deleteIndexList); if (deleteIndexList.Count > 0) { foreach (var item in deleteIndexList) { dataAccess.ExecuteNonQuery(item); } } //再对列进行处理 Dictionary <string, bool> dic = new Dictionary <string, bool>(); foreach (DataColumn col in table.Columns) { FieldType fieldType = FieldType.None; if (col.ExtendedProperties.ContainsKey(FieldProperty.FieldType)) { fieldType = (FieldType)col.ExtendedProperties[FieldProperty.FieldType]; } if (fieldType == FieldType.None) { dic.Add(col.ColumnName, false); } } foreach (DataRow row in dtSchema.Rows) { string columnName = row["COLUMN_NAME"].ToString(); bool isFind = false; foreach (DataColumn col in table.Columns) { if (!dic.ContainsKey(col.ColumnName)) { continue; } if (string.Compare(col.ColumnName, columnName, true) == 0) { StringBuilder strBuilder = new StringBuilder(); string dataType = row["DATATYPE"].ToString(); bool hasDiff = HasFieldChanged(col, dataType, defaultDic); if (hasDiff) { tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} modify({1})';", table.TableName, GetFieldInfo(col, true))); } isFind = true; dic[col.ColumnName] = true; break; } } if (isDelete && !isFind) { tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} drop column {1}';", table.TableName, columnName)); } } foreach (var item in dic) { if (!item.Value) { DataColumn col = table.Columns[item.Key]; tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} add({1})';", table.TableName, GetFieldInfo(col, true))); } } if (tempBuilder.Length > 0) { StringBuilder testBuild = new StringBuilder(); testBuild.AppendLine("begin"); testBuild.Append(tempBuilder.ToString()); testBuild.AppendLine("end;"); dataAccess.ExecuteNonQuery(testBuild.ToString()); } //如果主键异动,先删除聚集索引 bool isPkChange = IsPkChange(table.TableName, table.PrimaryKey); //先执行对于具有唯一性约束的字段的数据更新处理Sql if (string.IsNullOrEmpty(this.UniqueDataSql) == false && (isPkChange || addIndexList.Count > 0)) { dataAccess.ExecuteNonQuery(this.UniqueDataSql); } //对主键的进行标识 if (isPkChange) { string pkStr = GetPkStr(table); if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select constraint_name from dba_constraints where constraint_name = 'PK_{0}'", table.TableName))))) { dataAccess.ExecuteNonQuery(string.Format("alter table {0} drop constraint PK_{0}", table.TableName)); } //一般情况下如果删除主键约束,索引会自动删除。但是目前有存在未删除的情况,所以确保删除 if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select * from user_ind_columns where INDEX_NAME='PK_{0}'", table.TableName))))) { dataAccess.ExecuteNonQuery(string.Format("drop index PK_{0}", table.TableName)); } dataAccess.ExecuteNonQuery(string.Format("alter table {0} add constraint PK_{0} primary key ({1})", table.TableName, pkStr)); } //对表的新增非聚集索引进行处理 foreach (var item in addIndexList) { dataAccess.ExecuteNonQuery(item); } } } }
public void UpdateTable(TableSchema schema, bool isDelete) { DataTable table = null; LibDataAccess dataAccess = new LibDataAccess(); int count = (int)dataAccess.ExecuteScalar(string.Format("select count(*) from sysobjects where id = object_id({0})", LibStringBuilder.GetQuotString(schema.Name))); if (count == 0) { CreateTable(schema); } else { using (DbConnection conn = dataAccess.CreateConnection()) { conn.Open(); table = conn.GetSchema("Columns", new string[] { null, null, schema.Name, null }); } if (table != null) { List <string> sql = new List <string>(); List <string> sqlSecond = new List <string>();//第二批执行的sql语句,一般在数据列加完以后执行唯一约束、主键等设置 //如果主键异动,先删除聚集索引 bool isPkChange = IsPkChange(schema.Name, schema.PRIMARY_KEY); if (isPkChange) { sql.Add(GetDropPkConstraintSql(schema.Name)); } //对删除的非聚集索引进行处理 List <string> deleteIndexList = new List <string>(); List <string> addIndexList = new List <string>(); CompareDBIndex(schema.Name, schema.DBIndexs, addIndexList, deleteIndexList); foreach (var item in deleteIndexList) { sql.Add(item); } //再对列进行处理 Dictionary <string, bool> dic = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase); foreach (ColumnSchema col in schema.ColumnSchemaList) { dic.Add(col.Name, false); } foreach (DataRow row in table.Rows) { string columnName = row["COLUMN_NAME"].ToString(); string dataType = row["DATA_TYPE"].ToString(); int size = LibSysUtils.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]); int digit = LibSysUtils.ToInt32(row["NUMERIC_SCALE"]); string defualtValue = LibSysUtils.ToString(row["COLUMN_DEFAULT"]); bool isFind = false; foreach (ColumnSchema col in schema.ColumnSchemaList) { if (string.Compare(col.Name, columnName, true) == 0) { StringBuilder strBuilder = new StringBuilder(); if (string.Compare(col.ColumnType, dataType, true) != 0 || (col.Size > 0 && col.Size != size) || col.Digit != digit) { foreach (var item in schema.PRIMARY_KEY) { if (string.Compare(col.Name, item, true) == 0) { if (!isPkChange) { isPkChange = true; sql.Add(GetDropPkConstraintSql(schema.Name)); } break; } } //删除默认值约束 strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName)); //修改字段 strBuilder.AppendLine("ALTER TABLE [dbo].[{0}] ALTER column {1} "); //新建默认值约束 strBuilder.AppendFormat("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, col.DefaultValueStr, columnName); sql.Add(string.Format(strBuilder.ToString(), schema.Name, col.ColumnTypeStr)); } else if (string.Compare(string.Format(col.DefaultValue), defualtValue) != 0) { foreach (var item in schema.PRIMARY_KEY) { if (string.Compare(col.Name, item, true) == 0) { if (!isPkChange) { isPkChange = true; sql.Add(GetDropPkConstraintSql(schema.Name)); } break; } } //删除默认值约束 strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName)); //新建默认值约束 strBuilder.AppendFormat("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, col.DefaultValueStr, columnName); sql.Add(string.Format(strBuilder.ToString(), schema.Name, columnName)); } isFind = true; dic[columnName] = true; break; } } if (isDelete && !isFind) { //原先存在。现在不存在,则考虑删除 //有默认约束依赖该字段,先删除默认约束,再删除字段 //删除默认值约束 StringBuilder strBuilder = new StringBuilder(); strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName)); //删除字段 strBuilder.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] DROP [{1}] {2}", schema.Name, columnName)); sql.Add(strBuilder.ToString()); } } foreach (var item in dic) { if (!item.Value) { StringBuilder strBuilder = new StringBuilder(); ColumnSchema col = schema.ColumnSchemaList.Find(c => c.Name == item.Key); //新增列 strBuilder.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] ADD {1}", schema.Name, col.ColumnTypeStr)); //新增列默认值约束 strBuilder.Append(col.DefaultValueStr); sql.Add(strBuilder.ToString()); } } //第二批执行的是索引、主键、唯一约束等 //先添加对于具有唯一性约束的字段的数据更新处理Sql,以便其先执行 if (string.IsNullOrEmpty(this.UniqueDataSql) == false && (isPkChange || addIndexList.Count > 0)) { sqlSecond.Add(this.UniqueDataSql); } //对主键的进行标识 if (isPkChange) { sqlSecond.Add(GetAddPkConstraintSql(schema.Name, schema.PRIMARY_KEY)); } //对表的新增非聚集索引进行处理 foreach (var item in addIndexList) { sqlSecond.Add(item); } if (sql.Count > 0 || sqlSecond.Count > 0) { StringBuilder sqlBuilder = new StringBuilder(); foreach (var item in sql) { sqlBuilder.Append(item); } StringBuilder sqlBuilderSecond = new StringBuilder(); foreach (var item in sqlSecond) { sqlBuilderSecond.Append(item); } //提交更新 LibDBTransaction tran = dataAccess.BeginTransaction(); try { if (sqlBuilder.Length > 0) { dataAccess.ExecuteNonQuery(sqlBuilder.ToString()); } //第二批执行的是索引、主键、唯一约束等 if (sqlBuilderSecond.Length > 0) { dataAccess.ExecuteNonQuery(sqlBuilderSecond.ToString()); } tran.Commit(); } catch (Exception exp) { LibCommUtils.AddOutput(@"Update", string.Format("升级数据表异常。\r\nsql:{0}\r\n异常:{1}\r\nStackTrace:{2}", sqlBuilder.ToString(), exp.Message, exp.StackTrace)); tran.Rollback(); } } } } }