/// <summary> /// /// </summary> /// <param name="colName"></param> /// <param name="catalog"></param> /// <param name="dataType"></param> /// <returns></returns> ADOX.ColumnClass tableField(string colName, ADOX.CatalogClass catalog, ADOX.DataTypeEnum dataType) { ADOX.ColumnClass column = new ADOX.ColumnClass(); column.Name = colName; // The name of the column column.ParentCatalog = catalog; column.Type = dataType; return(column); }
private void ALTERColumnADOX(ConnectionClass conn, string tableName, string columnName, ADOX.DataTypeEnum columnType, int columnPrecision, int columnDefSize, jcolumns cols) { //НЕ РАБОТАЕТ!!! ADOX c Jet 4.0 непозволяет изменять параметры колонок //m_catalogADOX.Tables[tableName].Columns[columnName].Precision = col_prec; //m_catalogADOX.Tables[tableName].Columns.Refresh(); //string strSQL = "ALTER TABLE "+tableName+" ALTER COLUMN "+columnName+" XXX"; try { string tempColumnName = String.Format("x{0}", columnName); m_catalogADOX.Tables[tableName].Columns[columnName].Name = tempColumnName; m_catalogADOX.Tables[tableName].Columns.Refresh(); ColumnClass columnADOX = new ColumnClass(); columnADOX.ParentCatalog = m_catalogADOX; columnADOX.Name = columnName; columnADOX.Type = columnType; columnADOX.DefinedSize = columnDefSize; columnADOX.Precision = columnPrecision; columnADOX.Properties["Autoincrement"].Value = (object)cols.autoincrement; columnADOX.Properties["Nullable"].Value = (object)cols.nullable; columnADOX.Properties["Fixed Length"].Value = (object)cols.fixedLength; AddColumnADOX(tableName, columnADOX, columnType, columnDefSize); string strSQL = String.Format("UPDATE {0} SET {1} = {2}", tableName, columnName, tempColumnName); int adCmdText = 1; object recCount = new object(); conn.Execute(strSQL, out recCount, adCmdText); //проверка не является ли поле ключевым //m_catalogADOX.Tables[tableName].Keys.Refresh(); for (int i = 0; i < m_catalogADOX.Tables[tableName].Keys.Count; i++) { ADOX.Key keyADOX = m_catalogADOX.Tables[tableName].Keys[i]; for (int j = 0; j < keyADOX.Columns.Count; j++) { if (tempColumnName == keyADOX.Columns[j].Name) { m_catalogADOX.Tables[tableName].Keys.Delete(keyADOX.Name); m_catalogADOX.Tables[tableName].Keys.Refresh(); } } } m_catalogADOX.Tables[tableName].Columns.Delete(tempColumnName); m_catalogADOX.Tables[tableName].Columns.Refresh(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
private void AddColumnADOX(string tableName, ColumnClass columnClass, ADOX.DataTypeEnum columnType, int columnDefSize) { m_catalogADOX.Tables[tableName].Columns.Append(columnClass, columnType, columnDefSize); }
//Реструктуризация БД по схеме public bool RestructDB(string pathDB) { if (!m_isSchema) { return(false); } File.Copy(pathDB, pathDB + ".old", true); bool isRestructed = false; ConnectionClass conn = null; try { conn = new ConnectionClass(); conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB, "", "", 0); m_catalogADOX.ActiveConnection = conn; for (int i = 0; i < m_schema.tables.Length; ++i) { string tableName; try { tableName = m_catalogADOX.Tables[m_schema.tables[i].name].Name; } catch { AddTableADOX(m_schema.tables[i].name); tableName = m_schema.tables[i].name; isRestructed = true; } jcolumns[] cols = m_schema.tables[i].columns; for (int j = 0; j < cols.Length; ++j) { string columnName = cols[j].name; ADOX.DataTypeEnum columnType = cols[j].type; int columnPrecision = cols[j].precision; int columnDefSize = cols[j].definedSize; try { string temp = m_catalogADOX.Tables[tableName].Columns[columnName].Name; } catch { ColumnClass columnADOX = new ColumnClass(); columnADOX.ParentCatalog = m_catalogADOX; columnADOX.Name = columnName; columnADOX.Type = columnType; columnADOX.DefinedSize = columnDefSize; columnADOX.Precision = columnPrecision; columnADOX.Properties["Autoincrement"].Value = (object)cols[j].autoincrement; columnADOX.Properties["Nullable"].Value = (object)cols[j].nullable; columnADOX.Properties["Fixed Length"].Value = (object)cols[j].fixedLength; AddColumnADOX(tableName, columnADOX, columnType, columnDefSize); isRestructed = true; } ADOX.DataTypeEnum temp_type = m_catalogADOX.Tables[tableName].Columns[columnName].Type; int temp_precision = m_catalogADOX.Tables[tableName].Columns[columnName].Precision; int temp_definedSize = m_catalogADOX.Tables[tableName].Columns[columnName].DefinedSize; if (columnType != temp_type || columnPrecision != temp_precision || columnDefSize != temp_definedSize || cols[j].autoincrement != (bool)m_catalogADOX.Tables[tableName].Columns[columnName].Properties["Autoincrement"].Value || cols[j].nullable != (bool)m_catalogADOX.Tables[tableName].Columns[columnName].Properties["Nullable"].Value || cols[j].fixedLength != (bool)m_catalogADOX.Tables[tableName].Columns[columnName].Properties["Fixed Length"].Value ) { ALTERColumnADOX(conn, tableName, columnName, columnType, columnPrecision, columnDefSize, cols[j]); isRestructed = true; } } //index jindexs[] ind = m_schema.tables[i].indexs; if (ind != null) { for (int j = 0; j < ind.Length; j++) { try { string name = m_catalogADOX.Tables[m_schema.tables[i].name].Indexes[ind[j].name].Name; } catch { IndexClass indexADOX = new IndexClass(); indexADOX.Name = ind[j].name; indexADOX.Clustered = ind[j].clustered; indexADOX.IndexNulls = ind[j].indexNulls; indexADOX.PrimaryKey = ind[j].primaryKey; indexADOX.Unique = ind[j].unique; indexADOX.Columns.Append(ind[j].name, ADOX.DataTypeEnum.adInteger, 0); //m_catalogADOX.Tables[m_schema.tables[i].name].Indexes.Refresh(); try { m_catalogADOX.Tables[m_schema.tables[i].name].Indexes.Append(ind[j].name, ind[j].name); } catch {} //catch (System.Runtime.InteropServices.COMException e) //{ //System.Windows.Forms.MessageBox.Show(e.Message); //} isRestructed = true; } } } //keys jkeys[] key = m_schema.tables[i].keys; if (key != null) { for (int j = 0; j < key.Length; j++) { try { string name = m_catalogADOX.Tables[m_schema.tables[i].name].Keys[key[j].name].Name; } catch { KeyClass keyADOX = new KeyClass(); keyADOX.Name = key[j].name; keyADOX.Type = key[j].type; m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Append( key[j].name, key[j].type, m_catalogADOX.Tables[m_schema.tables[i].name].Columns[key[j].column], "", ""); //key[j].name, key[j].type, key[j].column, "", ""); //Без этой записи на win2000 выдавала глюк!!! Первая строка в vars отказывалась записыватся m_catalogADOX.Tables[m_schema.tables[i].name].Keys.Refresh(); } } } //Data jrows[] rows = m_schema.tables[i].rows; if (rows != null && m_schema.tables[i].name == "vars") { FillDataRestruct(conn, m_schema.tables[i].name, rows); } } } finally { if (conn != null) { conn.Close(); } //System.Runtime.InteropServices.Marshal.ReleaseComObject(m_catalogADOX); //m_catalogADOX = new CatalogClass(); } return(isRestructed); }
/// <summary> /// Преобразование общего типа к access type, только однозначные отношения!!! /// </summary> /// <param name="dbcType"></param> /// <returns></returns> public static ColumnType TypeJetToDbsm(DataTypeEnum dbcType) { switch (dbcType) { case DataTypeEnum.adVarBinary: // ACCESS - binary; MSSQL - binary(); return(ColumnType.BinaryVaring); case DataTypeEnum.adBoolean: // ACCESS - boolean; MSSQL - bit; ANSI BOOLEAN return(ColumnType.Boolean); case DataTypeEnum.adUnsignedTinyInt: // ACCESS - byte; MSSQL - tinyint; ANSI <none> return(ColumnType.TinyInt); case DataTypeEnum.adWChar: // ACCESS - char; MSSQL - char; ANSI - CHARACTER return(ColumnType.NCharacter); case DataTypeEnum.adDate: // ACCESS - datetime; MSSQL - datetime; ANSI - TIMESTAMP return(ColumnType.Timestamp); case DataTypeEnum.adDouble: // ACCESS - double; MSSQL - float(53); ANSI - DOUBLEPRECISION, FLOAT(53) return(ColumnType.DoublePrecision); case DataTypeEnum.adSingle: // ACCESS - single; MSSQL - real,float(24); ANSI - REAL, FLOAT(24) return(ColumnType.Real); case DataTypeEnum.adGUID: // ACCESS - guid; MSSQL - uniqueidentifier; ANSI - <none> return(ColumnType.Guid); case DataTypeEnum.adLongVarBinary: // ACCESS - image(OLE object); MSSQL - images; ANSI - BLOB SUB_TYPE -1 return(ColumnType.BlobSubtypeImage); case DataTypeEnum.adInteger: // ACCESS - integer; MSSQL - int; ANSI - Integer return(ColumnType.Integer); case DataTypeEnum.adLongVarWChar: // ACCESS - memo; MSSQL - ntext; ANSI - BLOB SIB_TYPE 1 return(ColumnType.BlobSubtype1); case DataTypeEnum.adCurrency: // ACCESS - money; MSSQL - money; ANSI - return(ColumnType.Money); case DataTypeEnum.adNumeric: // ACCESS - numeric; MSSQL - decimal(p,s),numeric(p,s); ANSI - DECIMAL(p,s) return(ColumnType.Decimal); case DataTypeEnum.adVarWChar: // ACCESS - char(n); MSSQL - char(n); ANSI - CHARACTER(n) return(ColumnType.NCharacterVaring); case DataTypeEnum.adSmallInt: return(ColumnType.SmallInt); #region Unused types // case ADOX.DataTypeEnum.adBinary: // return DbsmType.BinaryLargeObject; // case ADOX.DataTypeEnum.adBigInt: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adBSTR: // return DbsmType.CharacterVaring; // case ADOX.DataTypeEnum.adChapter: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adChar: // return DbsmType.Character; // case ADOX.DataTypeEnum.adDBDate: // return DbsmType.Date; // case ADOX.DataTypeEnum.adDBTime: // return DbsmType.Time; // case ADOX.DataTypeEnum.adDBTimeStamp: // return DbsmType.TimeStamp; // case ADOX.DataTypeEnum.adDecimal: // return DbsmType.Decimal; // case ADOX.DataTypeEnum.adError: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adFileTime: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adIDispatch: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adIUnknown: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adLongVarChar: // return DbsmType.BinaryLargeObject; // case ADOX.DataTypeEnum.adNumeric: // return DbsmType.Numeric; // case ADOX.DataTypeEnum.adPropVariant: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adTinyInt: // return DbsmType.Character; // case ADOX.DataTypeEnum.adUnsignedBigInt: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adUnsignedInt: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adUnsignedSmallInt: // return DbsmType.SmallInt; // case ADOX.DataTypeEnum.adUserDefined: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adVarChar: // return DbsmType.CharacterVaring; // case ADOX.DataTypeEnum.adVariant: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adVarNumeric: // return DbsmType.Numeric; // case ADOX.DataTypeEnum.adEmpty: // return DbsmType.Empty; #endregion default: throw new ArgumentException("Unsupported data type for " + JetDriver.DriverName); } }
/// <summary> /// Преобразование общего типа к access type, только однозначные отношения!!! /// </summary> /// <param name="dbcType"></param> /// <returns></returns> public static ColumnType TypeJetToDbsm(DataTypeEnum dbcType) { switch (dbcType) { case DataTypeEnum.adVarBinary: // ACCESS - binary; MSSQL - binary(); return ColumnType.BinaryVaring; case DataTypeEnum.adBoolean: // ACCESS - boolean; MSSQL - bit; ANSI BOOLEAN return ColumnType.Boolean; case DataTypeEnum.adUnsignedTinyInt: // ACCESS - byte; MSSQL - tinyint; ANSI <none> return ColumnType.TinyInt; case DataTypeEnum.adWChar: // ACCESS - char; MSSQL - char; ANSI - CHARACTER return ColumnType.NCharacter; case DataTypeEnum.adDate: // ACCESS - datetime; MSSQL - datetime; ANSI - TIMESTAMP return ColumnType.Timestamp; case DataTypeEnum.adDouble: // ACCESS - double; MSSQL - float(53); ANSI - DOUBLEPRECISION, FLOAT(53) return ColumnType.DoublePrecision; case DataTypeEnum.adSingle: // ACCESS - single; MSSQL - real,float(24); ANSI - REAL, FLOAT(24) return ColumnType.Real; case DataTypeEnum.adGUID: // ACCESS - guid; MSSQL - uniqueidentifier; ANSI - <none> return ColumnType.Guid; case DataTypeEnum.adLongVarBinary: // ACCESS - image(OLE object); MSSQL - images; ANSI - BLOB SUB_TYPE -1 return ColumnType.BlobSubtypeImage; case DataTypeEnum.adInteger: // ACCESS - integer; MSSQL - int; ANSI - Integer return ColumnType.Integer; case DataTypeEnum.adLongVarWChar: // ACCESS - memo; MSSQL - ntext; ANSI - BLOB SIB_TYPE 1 return ColumnType.BlobSubtype1; case DataTypeEnum.adCurrency: // ACCESS - money; MSSQL - money; ANSI - return ColumnType.Money; case DataTypeEnum.adNumeric: // ACCESS - numeric; MSSQL - decimal(p,s),numeric(p,s); ANSI - DECIMAL(p,s) return ColumnType.Decimal; case DataTypeEnum.adVarWChar: // ACCESS - char(n); MSSQL - char(n); ANSI - CHARACTER(n) return ColumnType.NCharacterVaring; case DataTypeEnum.adSmallInt: return ColumnType.SmallInt; #region Unused types // case ADOX.DataTypeEnum.adBinary: // return DbsmType.BinaryLargeObject; // case ADOX.DataTypeEnum.adBigInt: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adBSTR: // return DbsmType.CharacterVaring; // case ADOX.DataTypeEnum.adChapter: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adChar: // return DbsmType.Character; // case ADOX.DataTypeEnum.adDBDate: // return DbsmType.Date; // case ADOX.DataTypeEnum.adDBTime: // return DbsmType.Time; // case ADOX.DataTypeEnum.adDBTimeStamp: // return DbsmType.TimeStamp; // case ADOX.DataTypeEnum.adDecimal: // return DbsmType.Decimal; // case ADOX.DataTypeEnum.adError: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adFileTime: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adIDispatch: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adIUnknown: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adLongVarChar: // return DbsmType.BinaryLargeObject; // case ADOX.DataTypeEnum.adNumeric: // return DbsmType.Numeric; // case ADOX.DataTypeEnum.adPropVariant: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adTinyInt: // return DbsmType.Character; // case ADOX.DataTypeEnum.adUnsignedBigInt: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adUnsignedInt: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adUnsignedSmallInt: // return DbsmType.SmallInt; // case ADOX.DataTypeEnum.adUserDefined: // return DbsmType.Integer; // case ADOX.DataTypeEnum.adVarChar: // return DbsmType.CharacterVaring; // case ADOX.DataTypeEnum.adVariant: // return DbsmType.BigInt; // case ADOX.DataTypeEnum.adVarNumeric: // return DbsmType.Numeric; // case ADOX.DataTypeEnum.adEmpty: // return DbsmType.Empty; #endregion default: throw new ArgumentException("Unsupported data type for " + JetDriver.DriverName); } }