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);
}
}
public void UpgradeDatabase()
{
var cat = new Catalog();
try
{
_myAccessConn.Open();
cat.ActiveConnection = _myAccessConn;
var mytable = new ADOX.Table {Name = "tblCustomerSettings"};
var myColumn = new ADOX.Column
{
Name = "CustomerSettingID",
Type = DataTypeEnum.adInteger,
ParentCatalog = cat
};
myColumn.Properties["Autoincrement"].Value = true;
mytable.Columns.Append(myColumn);
mytable.Columns.Append("CustomerID", DataTypeEnum.adInteger);
mytable.Columns.Append("BillingMethodID", DataTypeEnum.adInteger);
mytable.Columns.Append("MobileCarrierID",DataTypeEnum.adInteger);
cat.Tables.Append(mytable);
var mykey = new ADOX.Key
{
Name = "PrimaryKey",
Type = KeyTypeEnum.adKeyPrimary,
RelatedTable = "tblCustomerSettings"
};
mykey.Columns.Append("CustomerSettingID");
cat.Tables["tblCustomerSettings"].Keys.Append(mykey);
//Add data for new table
InitializeCustomerSettings(_myAccessConn);
//Upddate the Database Version
UpdateDBVersion(_myAccessConn);
foreach (Table tbl in cat.Tables)
{
var myname = tbl.Name;
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
}
finally
{
_myAccessConn.Close();
}
}
private void CreateDBtablesColumn()
{
try
{
string ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5", Environment.CurrentDirectory + "\\Table\\StudentsDB.mdb");
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table locTable = new ADOX.Table();
ADOX.Key locKey = new ADOX.Key();
ADOX.Column locCol = new Column();
//Create the Locations table and it's fields
locTable.Name = "Результати";
locCol.Name = "id";
//locCol.Attributes = ColumnAttributesEnum.adColNullable;
locCol.Type = ADOX.DataTypeEnum.adInteger;
locTable.Columns.Append(locCol);
locTable.Columns.Append("Учбовий квиток", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Курс", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Імя", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Прізвище", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("По батькові", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Дата народження", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Стать", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Вік", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Зріст", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Вага", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС0", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС1", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС2", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС3", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС4", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТС5", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД0", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД1", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД2", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД3", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД4", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("АТД5", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС0", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС1", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС2", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС3", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС4", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("ЧСС5", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Cl", DataTypeEnum.adVarWChar, 255);
locTable.Columns.Append("Dist", DataTypeEnum.adVarWChar, 255);
locKey.Name = "Primary Key";
locKey.Columns.Append("id");
locKey.Type = KeyTypeEnum.adKeyPrimary;
cat.Create(ConnectionString);
// Must create database file before applying autonumber to column
locCol.ParentCatalog = cat;
if (locCol.Name != "id")
{
locCol.Attributes = ColumnAttributesEnum.adColFixed;
//locCol.Attributes = ColumnAttributesEnum.adColNullable;
}
locCol.Properties["AutoIncrement"].Value = true;
//Exception triggered here
cat.Tables.Append(locTable);
}
catch
{
MessageBox.Show("Не вдається свторити Базу даних.\nДані не збережено!");
}
}