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 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);
}
}
//Заполняем m_schema из БД
public void FillFromADOX(string pathDB, bool withData)
{
ConnectionClass conn = null;
try
{
conn = new ConnectionClass();
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathDB, "", "", 0);
m_catalogADOX.ActiveConnection = conn;
m_schema.tables = new jtables[m_catalogADOX.Tables.Count];
for (int i = 0; i < m_catalogADOX.Tables.Count; i++)
{
if (m_catalogADOX.Tables[i].Name.Substring(0, 4) != "MSys")
{
jtables table = new jtables();
table.name = m_catalogADOX.Tables[i].Name;
table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count];
for (int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++)
{
jcolumns column = new jcolumns();
column.name = m_catalogADOX.Tables[i].Columns[j].Name;
column.type = m_catalogADOX.Tables[i].Columns[j].Type;
column.precision = m_catalogADOX.Tables[i].Columns[j].Precision;
column.definedSize = m_catalogADOX.Tables[i].Columns[j].DefinedSize;
column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value;
column.nullable = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value;
column.fixedLength = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value;
//tab.columns.SetValue(col, j);
table.columns[j] = column;
}
ArrayList arrIndex = new ArrayList();
//index
table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count];
for (int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++)
{
if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey")
{
string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
if (arrIndex.Contains(nameIndex))
{
continue;
}
jindexs index = new jindexs();
//index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки
index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
index.clustered = m_catalogADOX.Tables[i].Indexes[j].Clustered;
index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey;
index.unique = m_catalogADOX.Tables[i].Indexes[j].Unique;
index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls;
//tab.indexs.SetValue(ind, j);
table.indexs[j] = index;
arrIndex.Add(index.name);
}
}
//keys
table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count];
for (int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++)
{
if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey")
{
jkeys key = new jkeys();
key.name = m_catalogADOX.Tables[i].Keys[j].Name;
key.column = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name;
key.type = m_catalogADOX.Tables[i].Keys[j].Type;
table.keys[j] = key;
}
}
//data
string tableName = m_catalogADOX.Tables[i].Name;
if (withData && tableName == "vars")
{
RecordsetClass rs = new RecordsetClass();
try
{
//int adCmdText = 1;
int adCmdTable = 2;
rs.Open(tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);
if (rs.RecordCount != 0)
{
table.rows = new jrows[rs.RecordCount];
int rc = 0;
rs.MoveFirst();
while (!rs.EOF)
{
jrows row = new jrows();
row.jcolvalues = new jcolvalue[rs.Fields.Count];
for (int c = 0; c < rs.Fields.Count; c++)
{
jcolvalue colvalue = new jcolvalue();
colvalue.name = rs.Fields[c].Name;
colvalue.colvalue = Convert.ToString(rs.Fields[c].Value);
row.jcolvalues[c] = colvalue;
}
table.rows[rc] = row;
rc++;
rs.MoveNext();
}
}
}
finally
{
rs.Close();
}
//version
try
{
int adCmdText = 1;
rs.Open("SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText);
if (rs.RecordCount != 0)
{
rs.MoveFirst();
while (!rs.EOF)
{
m_schema.version = Convert.ToInt32(rs.Fields[0].Value);
rs.MoveNext();
}
}
}
finally
{
rs.Close();
}
}
//m_schema.tables.SetValue(table, i);
m_schema.tables[i] = table;
}
}
m_isSchema = true;
}
finally
{
if (conn != null)
{
conn.Close();
}
m_catalogADOX.ActiveConnection = null;
}
}
//Заполняем m_schema из БД
public void FillFromADOX( string pathDB, bool withData)
{
ConnectionClass conn = null;
try
{
conn = new ConnectionClass();
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathDB,"","",0);
m_catalogADOX.ActiveConnection = conn;
m_schema.tables = new jtables[m_catalogADOX.Tables.Count];
for(int i = 0; i < m_catalogADOX.Tables.Count; i++)
{
if (m_catalogADOX.Tables[i].Name.Substring(0,4) != "MSys")
{
jtables table = new jtables();
table.name = m_catalogADOX.Tables[i].Name;
table.columns = new jcolumns[m_catalogADOX.Tables[i].Columns.Count];
for(int j = 0; j < m_catalogADOX.Tables[i].Columns.Count; j++)
{
jcolumns column = new jcolumns();
column.name = m_catalogADOX.Tables[i].Columns[j].Name;
column.type = m_catalogADOX.Tables[i].Columns[j].Type;
column.precision = m_catalogADOX.Tables[i].Columns[j].Precision;
column.definedSize = m_catalogADOX.Tables[i].Columns[j].DefinedSize;
column.autoincrement = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Autoincrement"].Value;
column.nullable = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Nullable"].Value;
column.fixedLength = (bool)m_catalogADOX.Tables[i].Columns[j].Properties["Fixed Length"].Value;
//tab.columns.SetValue(col, j);
table.columns[j] = column;
}
ArrayList arrIndex = new ArrayList();
//index
table.indexs = new jindexs[m_catalogADOX.Tables[i].Indexes.Count];
for(int j = 0; j < m_catalogADOX.Tables[i].Indexes.Count; j++)
{
if (m_catalogADOX.Tables[i].Indexes[j].Name != "PrimaryKey")
{
string nameIndex = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
if ( arrIndex.Contains(nameIndex))
continue;
jindexs index = new jindexs();
//index.name = m_catalogADOX.Tables[i].Indexes[j].Name;//глюки
index.name = m_catalogADOX.Tables[i].Indexes[j].Columns[0].Name;
index.clustered = m_catalogADOX.Tables[i].Indexes[j].Clustered;
index.primaryKey = m_catalogADOX.Tables[i].Indexes[j].PrimaryKey;
index.unique = m_catalogADOX.Tables[i].Indexes[j].Unique;
index.indexNulls = m_catalogADOX.Tables[i].Indexes[j].IndexNulls;
//tab.indexs.SetValue(ind, j);
table.indexs[j] = index;
arrIndex.Add( index.name);
}
}
//keys
table.keys = new jkeys[m_catalogADOX.Tables[i].Keys.Count];
for(int j = 0; j < m_catalogADOX.Tables[i].Keys.Count; j++)
{
if (m_catalogADOX.Tables[i].Keys[j].Name == "PrimaryKey")
{
jkeys key = new jkeys();
key.name = m_catalogADOX.Tables[i].Keys[j].Name;
key.column = m_catalogADOX.Tables[i].Keys[j].Columns[0].Name;
key.type = m_catalogADOX.Tables[i].Keys[j].Type;
table.keys[j] = key;
}
}
//data
string tableName = m_catalogADOX.Tables[i].Name;
if (withData && tableName == "vars")
{
RecordsetClass rs = new RecordsetClass();
try
{
//int adCmdText = 1;
int adCmdTable = 2;
rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);
if (rs.RecordCount != 0)
{
table.rows = new jrows[rs.RecordCount];
int rc = 0;
rs.MoveFirst();
while (!rs.EOF)
{
jrows row = new jrows();
row.jcolvalues = new jcolvalue[rs.Fields.Count];
for (int c = 0; c < rs.Fields.Count; c++)
{
jcolvalue colvalue = new jcolvalue();
colvalue.name = rs.Fields[c].Name;
colvalue.colvalue = Convert.ToString(rs.Fields[c].Value);
row.jcolvalues[c] = colvalue;
}
table.rows[rc] = row;
rc++;
rs.MoveNext();
}
}
}
finally
{
rs.Close();
}
//version
try
{
int adCmdText = 1;
rs.Open( "SELECT varvalue FROM vars WHERE name='VersionDB'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdText);
if (rs.RecordCount != 0)
{
rs.MoveFirst();
while (!rs.EOF)
{
m_schema.version = Convert.ToInt32(rs.Fields[0].Value);
rs.MoveNext();
}
}
}
finally
{
rs.Close();
}
}
//m_schema.tables.SetValue(table, i);
m_schema.tables[i] = table;
}
}
m_isSchema = true;
}
finally
{
if (conn != null)
conn.Close();
m_catalogADOX.ActiveConnection = null;
}
}