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