/// <summary>
/// Adds key to index of table.
/// </summary>
/// <param name="tableDescription">Table description.</param>
/// <param name="indexDefinition">Index definition.</param>
/// <param name="indexes">Database indexes.</param>
private void _AddKeyToTableIndex(TableDescription tableDescription,
TableIndex indexDefinition,
ADOX.Indexes indexes)
{
Debug.Assert(null != tableDescription);
Debug.Assert(null != indexDefinition);
Debug.Assert(null != indexes);
var index = new ADOX.Index();
ADOX.Columns columns = index.Columns;
switch (indexDefinition.Type)
{
case TableIndexType.Primary:
case TableIndexType.Simple:
{
string field = indexDefinition.FieldNames[0];
if (TableIndexType.Primary == indexDefinition.Type)
{
index.Name = INDEX_PRIMARY_KEY;
index.PrimaryKey = true;
index.Unique = true;
}
else // simple
index.Name = field;
FieldInfo info = tableDescription.GetFieldInfo(field);
Debug.Assert(null != info);
columns.Append(info.Name, _ConvertType(info.Type), info.Size);
break;
}
case TableIndexType.Multiple:
{
var sbKeyName = new StringBuilder();
foreach (string field in indexDefinition.FieldNames)
{
FieldInfo info = tableDescription.GetFieldInfo(field);
Debug.Assert(null != info);
columns.Append(info.Name, _ConvertType(info.Type), info.Size);
if (!string.IsNullOrEmpty(sbKeyName.ToString()))
sbKeyName.Append(SQL_KEY_SYMBOL);
sbKeyName.Append(field);
}
index.Name = sbKeyName.ToString();
break;
}
default:
{
Debug.Assert(false); // NOTE: not supported
break;
}
}
index.IndexNulls = ADOX.AllowNullsEnum.adIndexNullsAllow;
indexes.Append(index, null);
}
/// <summary>
/// Adds fields to table.
/// </summary>
/// <param name="tableDefinition">Table definition.</param>
/// <param name="tableDescription">Table Description.</param>
/// <param name="columns">Database columns.</param>
private void _AddFieldsToTable(ITableDefinition tableDefinition,
TableDescription tableDescription,
ADOX.Columns columns)
{
Debug.Assert(null != tableDefinition);
Debug.Assert(null != tableDescription);
Debug.Assert(null != columns);
ICollection<string> fields = tableDefinition.Fields;
foreach (string field in fields)
{
FieldInfo info = tableDescription.GetFieldInfo(field);
Debug.Assert(null != info);
columns.Append(info.Name, _ConvertType(info.Type), info.Size);
// make field not required
ADOX.Column column = columns[info.Name];
column.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
}
}
/// <summary>
/// Adds keys to index of table.
/// </summary>
/// <param name="tableDescription">Table description.</param>
/// <param name="tableDefinition">Table definition.</param>
/// <param name="indexes">Database indexes.</param>
private void _AddKeysToTableIndex(TableDescription tableDescription,
ITableDefinition tableDefinition,
ADOX.Indexes indexes)
{
ICollection<TableInfo> patternTables = _structureKeeper.GetPattern(ExportType.Access);
foreach (TableInfo tableInfo in patternTables)
{
if (tableInfo.Type != tableDefinition.Type)
continue; // skip
foreach(TableIndex indexDefinition in tableInfo.Indexes)
{
if (_IsIndexFieldSelected(indexDefinition.FieldNames, tableDefinition.Fields))
_AddKeyToTableIndex(tableDescription, indexDefinition, indexes);
}
break; // process done
}
}
public object TableDefinition(ADOX.Table tblDef)
{
int intLoop = 0;
string strTable = "";
string strIndex = "";
ADODB.Recordset recSchema = new ADODB.Recordset();
Console.WriteLine("Table: " + tblDef.Name.ToString());
recSchema.Open("SELECT * FROM [" + tblDef.Name +"]", conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
strTable = "CREATE TABLE " + strMySQLDBName + "." + MySQLName(tblDef.Name) + System.Environment.NewLine + "(";
for (intLoop = 0; intLoop < recSchema.Fields.Count; intLoop++)
{
strTable = strTable + ColDefinition(tblDef.Columns[recSchema.Fields[intLoop].Name]);
if (intLoop + 1 < tblDef.Columns.Count)
{
strTable = strTable + ", " + System.Environment.NewLine + " ";
}
}
foreach (ADOX.Index idxLoop in tblDef.Indexes)
{
strIndex = IndexDefinition(tblDef, idxLoop).ToString();
if (strIndex != "")
{
strTable = strTable + ", " + System.Environment.NewLine + strIndex;
}
}
strTable = strTable + ")";
strTable = strTable + " ENGINE =INNODB";
strTable = strTable + " CHARACTER SET utf8";
return strTable;
}
public object ColDefinition(ADOX.Column col)
{
string strDefine = null;
strDefine = MySQLName(col.Name) + " " + DataType(col);
if (col.Properties["Autoincrement"].Value.ToString() == "True")
{
strDefine += " NOT NULL AUTO_INCREMENT";
}
else
{
if (col.Properties["nullable"].Value.ToString() == "True")
{
strDefine += " NULL";
}
else
{
strDefine += " NOT NULL";
}
}
if (col.Properties["Default"].Value != null)
{
if (col.Type == ADOX.DataTypeEnum.adBoolean)
{
Console.WriteLine("\tColumn: " + col.Name.ToString() + " : " + col.Properties["Default"].Value.ToString());
if (col.Properties["Default"].Value.ToString() == "Yes" ||
col.Properties["Default"].Value.ToString() == "True" ||
col.Properties["Default"].Value.ToString() == "1" ||
col.Properties["Default"].Value.ToString() == "-1" )
strDefine += " DEFAULT -1";
else
strDefine += " DEFAULT 0";
}
else if (col.Type == ADOX.DataTypeEnum.adVarWChar)
{
String strDefValue = col.Properties["Default"].Value.ToString().Trim();
strDefValue = strDefValue.Replace("\"", "");
strDefValue = strDefValue.Replace("\'", "");
if ("NULL" == strDefValue.ToUpper())
strDefValue = "NULL";
strDefine += " DEFAULT '" + strDefValue + "'";
}
else
strDefine += " DEFAULT " + col.Properties["Default"].Value.ToString().ToUpper();
}
return strDefine;
}
public object IndexDefinition(ADOX.Table tblDef, ADOX.Index idxDef)
{
int intLoop = 0;
string strIndex = null;
ADOX.Column colDef = null;
if (idxDef.PrimaryKey == true)
{
strIndex = strIndex + "PRIMARY KEY ";
}
else if (idxDef.Unique)
{
strIndex = strIndex + "UNIQUE INDEX " + MySQLName(idxDef.Name);
}
else
{
strIndex = strIndex + "INDEX " + MySQLName(idxDef.Name);
}
strIndex = strIndex + "(";
for (intLoop = 0; intLoop < idxDef.Columns.Count; intLoop++)
{
colDef = idxDef.Columns[intLoop];
strIndex = strIndex + MySQLName(colDef.Name);
if (intLoop < idxDef.Columns.Count - 1)
{
strIndex = strIndex + ",";
}
}
strIndex = strIndex + ")";
return strIndex;
}
public object DataType(ADOX.Column colDef)
{
int intLength = 0;
int intPrecision = 0;
int intScale = 0;
string strNewType = null;
intLength = colDef.DefinedSize;
intPrecision = colDef.Precision;
intScale = colDef.NumericScale;
strNewType = colDef.Name;
switch (colDef.Type)
{
case ADOX.DataTypeEnum.adBigInt:
strNewType = "BIGINT";
break;
case ADOX.DataTypeEnum.adBoolean:
strNewType = "TINYINT(1)";
break;
case ADOX.DataTypeEnum.adDouble:
strNewType = "FLOAT";
break;
case ADOX.DataTypeEnum.adInteger:
strNewType = "INTEGER";
break;
case ADOX.DataTypeEnum.adNumeric:
strNewType = "NUMERIC (" + intPrecision + ", " + intScale + ")";
break;
case ADOX.DataTypeEnum.adSingle:
strNewType = "REAL";
break;
case ADOX.DataTypeEnum.adUnsignedTinyInt:
strNewType = "TINYINT";
break;
case ADOX.DataTypeEnum.adSmallInt:
strNewType = "SMALLINT";
break;
case ADOX.DataTypeEnum.adTinyInt:
strNewType = "TINYINT";
break;
case ADOX.DataTypeEnum.adVarChar:
strNewType = "VARCHAR (" + intLength + ")";
break;
case ADOX.DataTypeEnum.adVarWChar:
strNewType = "VARCHAR (" + intLength + ")";
break;
case ADOX.DataTypeEnum.adLongVarWChar:
strNewType = "LONGTEXT";
break;
case ADOX.DataTypeEnum.adDate:
strNewType = "DATETIME";
break;
case ADOX.DataTypeEnum.adCurrency:
strNewType = "FLOAT";
break;
default:
strNewType = "UNKNOWN";
break;
}
return strNewType;
}
public void CopyTable(ADOX.Table tblAccess)
{
ADODB.Recordset recMaster = new ADODB.Recordset();
ADODB.Recordset recLoop = new ADODB.Recordset();
int intLoop = 0;
string strInfile = "";
string strSQL = "SELECT ";
string strRecord;
string strLoadFilePath = strSourceDbPath.Replace("\\", "\\\\");
string strFileName = strTempPath + tblAccess.Name + ".txt";
StreamWriter sw = new StreamWriter(strFileName, false);
//create the infile
strInfile += "LOAD DATA LOCAL INFILE '" + strFileName + "' INTO TABLE " + strMySQLDBName + "." + tblAccess.Name + " ";
strInfile += "FIELDS TERMINATED BY ',' ";
strInfile += "ESCAPED BY '\\\\' ";
strInfile += "LINES TERMINATED BY 0x0d0a ";
strInfile += "(";
//loop through fields to enumerate them for the infile and build a select statement
for (intLoop = 0; intLoop < tblAccess.Columns.Count; intLoop++)
{
strInfile += MySQLName((tblAccess.Columns[intLoop].Name));
switch (tblAccess.Columns[intLoop].Type)
{
case ADOX.DataTypeEnum.adDate: //convert to MySQL datetime format
strSQL += "FORMAT([" + tblAccess.Columns[intLoop].Name + "], 'YYYY-MM-DD HH:MM:SS') as " + tblAccess.Columns[intLoop].Name;
break;
default:
strSQL += "[" + tblAccess.Columns[intLoop].Name + "]";
break;
}
if (intLoop < tblAccess.Columns.Count - 1)
{
strSQL += ",";
strInfile += ", ";
}
}
strInfile += ");";
strSQL += " FROM [" + tblAccess.Name + "]";
//open the "Master" recordset
recMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
recMaster.Open(strSQL, conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
//create the "Loop" recordset, this is a clone of the master, with the exception
//that the definedsize for text fields is lengthened. This is because the added
//escape characters could potentially exceed the field length in the master recordset
recLoop.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ADODB.Fields fdsLoop = recLoop.Fields;
ADODB.Fields fdsMaster = recMaster.Fields;
foreach (ADODB.Field fldIn in fdsMaster)
{
if (fldIn.Type.ToString().IndexOf("Char") > 0)
{
fdsLoop.Append(fldIn.Name,
fldIn.Type,
fldIn.DefinedSize + 30,
ADODB.FieldAttributeEnum.adFldIsNullable,
null);
}
else
{
fdsLoop.Append(fldIn.Name,
fldIn.Type,
fldIn.DefinedSize,
ADODB.FieldAttributeEnum.adFldIsNullable,
null);
}
}
recLoop.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
recLoop.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
while (!recMaster.EOF)
{
for (int columnIndex = 0; columnIndex < recMaster.Fields.Count; columnIndex++)
{
recLoop.Fields[columnIndex].Value = recMaster.Fields[columnIndex].Value;
if (recLoop.Fields[columnIndex].Value.ToString().Length > 0)
{
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf("\\", 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace("\\", "\\\\");
}
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(",", 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(",", "\\,");
}
if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(System.Environment.NewLine, 0) + 1) > 0)
{
recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(System.Environment.NewLine, " ");
}
}
}
strRecord = recLoop.GetString(ADODB.StringFormatEnum.adClipString, 1, ",", System.Environment.NewLine, "\\N");
recLoop.MovePrevious();
sw.Write(strRecord);
recMaster.MoveNext();
}
recMaster.Close();
recMaster.ActiveConnection = null;
try
{
recLoop.Close();
}
catch
{
}
sw.Close();
ExecuteSQL(strInfile);
File.Delete(strFileName);
recLoop = null;
}
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);
}
