//ВНИМАНИЕ!!!: Сделано только для таблицы vars!
private void FillDataRestruct( ConnectionClass conn, string tableName, jrows[] rows)
{
ArrayList al = new ArrayList();
RecordsetClass rs = new RecordsetClass();
try
{
int adCmdTable = 2;
rs.Open( tableName, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, adCmdTable);
if (rs.RecordCount != 0)
{
rs.MoveFirst();
while (!rs.EOF)
{
al.Add(rs.Fields[0].Value);
rs.MoveNext();
}
}
}
finally
{
rs.Close();
}
int adCmdText = 1;
object recCount = new object();
for ( int i = 0; i < rows.Length; ++i )
{
jcolvalue[] cols = rows[i].jcolvalues;
StringBuilder sbCol = new StringBuilder();
StringBuilder sbVal = new StringBuilder();
bool insert = true;
for ( int j = 0; j < cols.Length; ++j )
{
if (cols[j].name == "name" && al.Contains(cols[j].colvalue))
{
insert = false;
break;
}
sbCol.Append(cols[j].name);
sbVal.Append('"'+cols[j].colvalue+'"');
if (j != (cols.Length-1))
{
sbCol.Append(",");
sbVal.Append(",");
}
}
if (insert)
{
string strSQL = String.Format("INSERT INTO {0} ({1}) VALUES({2})", tableName, sbCol.ToString(), sbVal.ToString());
//System.Windows.Forms.MessageBox.Show(strSQL);
conn.Execute( strSQL, out recCount, adCmdText);
}
}
}
//Заполняем 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;
}
}