private void Export_GetConditionString(MySqlDataReader rdr, MySqlTable table, StringBuilder sb)
{
bool isFirst = true;
for (int i = 0; i < rdr.FieldCount; i++)
{
string colName = rdr.GetName(i);
var col = table.Columns[colName];
if (col.IsPrimaryKey)
{
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append(" and ");
}
sb.Append("`");
sb.Append(colName);
sb.Append("`=");
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
}
}
private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < rdr.FieldCount; i++)
{
string columnName = rdr.GetName(i);
if (table.Columns[columnName].IsGeneratedColumn)
{
continue;
}
if (sb.Length == 0)
{
sb.AppendFormat("(");
}
else
{
sb.AppendFormat(",");
}
object ob = rdr[i];
var col = table.Columns[columnName];
//sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
sb.Append(QueryExpress.ConvertToSqlFormat(ob, true, true, col, ExportInfo.BlobExportMode));
}
sb.AppendFormat(")");
return(sb.ToString());
}
private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < rdr.FieldCount; i++)
{
if (sb.Length == 0)
{
sb.AppendFormat("(");
}
else
{
sb.AppendFormat(",");
}
string columnName = rdr.GetName(i);
object ob = rdr[i];
var col = table.Columns[columnName];
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
sb.AppendFormat(")");
return(sb.ToString());
}
private static void Export_GetUpdateString(MySqlDataReader rdr, MySqlTable table, StringBuilder sb)
{
var isFirst = true;
for (var i = 0; i < rdr.FieldCount; i++)
{
var colName = rdr.GetName(i);
var col = table.Columns[colName];
if (!col.IsPrimaryKey)
{
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append(",");
}
sb.Append("`");
sb.Append(colName);
sb.Append("`=");
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
}
}
void Export_RowsData_OnDuplicateKeyUpdate(string tableName, string selectSQL)
{
MySqlTable table = _database.Tables[tableName];
bool allPrimaryField = true;
foreach (var col in table.Columns)
{
if (!col.IsPrimaryKey)
{
allPrimaryField = false;
break;
}
}
Command.CommandText = selectSQL;
MySqlDataReader rdr = Command.ExecuteReader();
while (rdr.Read())
{
if (stopProcess)
{
return;
}
_currentRowIndexInAllTable = _currentRowIndexInAllTable + 1;
_currentRowIndexInCurrentTable = _currentRowIndexInCurrentTable + 1;
StringBuilder sb = new StringBuilder();
if (allPrimaryField)
{
sb.Append(Export_GetInsertStatementHeader(RowsDataExportMode.InsertIgnore, tableName, rdr));
sb.Append(Export_GetValueString(rdr, table));
}
else
{
sb.Append(Export_GetInsertStatementHeader(RowsDataExportMode.Insert, tableName, rdr));
sb.Append(Export_GetValueString(rdr, table));
sb.Append(" ON DUPLICATE KEY UPDATE ");
Export_GetUpdateString(rdr, table, sb);
}
sb.Append(";");
Export_WriteLine(sb.ToString());
textWriter.Flush();
}
rdr.Close();
}
private static string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
{
var sb = new StringBuilder();
for (var i = 0; i < rdr.FieldCount; i++)
{
sb.AppendFormat(sb.Length == 0 ? "(" : ",");
var columnName = rdr.GetName(i);
var col = table.Columns[columnName];
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
sb.AppendFormat(")");
return(sb.ToString());
}
void Export_RowsData_Update(string tableName, string selectSQL)
{
MySqlTable table = _database.Tables[tableName];
bool allPrimaryField = true;
foreach (var col in table.Columns)
{
if (!col.IsPrimaryKey)
{
allPrimaryField = false;
break;
}
}
if (allPrimaryField)
{
return;
}
bool allNonPrimaryField = true;
foreach (var col in table.Columns)
{
if (col.IsPrimaryKey)
{
allNonPrimaryField = false;
break;
}
}
if (allNonPrimaryField)
{
return;
}
Command.CommandText = selectSQL;
MySqlDataReader rdr = Command.ExecuteReader();
while (rdr.Read())
{
if (stopProcess)
{
return;
}
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE `");
sb.Append(tableName);
sb.Append("` SET ");
Export_GetUpdateString(rdr, table, sb);
sb.Append(" WHERE ");
Export_GetConditionString(rdr, table, sb);
sb.Append(";");
Export_WriteLine(sb.ToString());
textWriter.Flush();
}
rdr.Close();
}
void Export_RowsData_Insert_Ignore_Replace(string tableName, string selectSQL)
{
MySqlTable table = _database.Tables[tableName];
Command.CommandText = selectSQL;
MySqlDataReader rdr = Command.ExecuteReader();
string insertStatementHeader = null;
var sb = new StringBuilder((int)ExportInfo.MaxSqlLength);
while (rdr.Read())
{
if (stopProcess)
{
return;
}
_currentRowIndexInAllTable = _currentRowIndexInAllTable + 1;
_currentRowIndexInCurrentTable = _currentRowIndexInCurrentTable + 1;
if (insertStatementHeader == null)
{
insertStatementHeader = Export_GetInsertStatementHeader(ExportInfo.RowsExportMode, tableName, rdr);
}
string sqlDataRow = Export_GetValueString(rdr, table);
if (sb.Length == 0)
{
sb.AppendLine(insertStatementHeader);
sb.Append(sqlDataRow);
}
else if ((long)sb.Length + (long)sqlDataRow.Length < ExportInfo.MaxSqlLength)
{
sb.AppendLine(",");
sb.Append(sqlDataRow);
}
else
{
sb.AppendFormat(";");
Export_WriteLine(sb.ToString());
textWriter.Flush();
sb = new StringBuilder((int)ExportInfo.MaxSqlLength);
sb.AppendLine(insertStatementHeader);
sb.Append(sqlDataRow);
}
}
rdr.Close();
if (sb.Length > 0)
{
sb.Append(";");
}
Export_WriteLine(sb.ToString());
textWriter.Flush();
sb = null;
}
private void Export_GetConditionString(MySqlDataReader rdr, MySqlTable table, StringBuilder sb)
{
bool isFirst = true;
for (int i = 0; i < rdr.FieldCount; i++)
{
string colName = rdr.GetName(i);
var col = table.Columns[colName];
if (col.IsPrimaryKey)
{
if (isFirst)
isFirst = false;
else
sb.Append(" and ");
sb.Append("`");
sb.Append(colName);
sb.Append("`=");
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
}
}
private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < rdr.FieldCount; i++)
{
if (sb.Length == 0)
sb.AppendFormat("(");
else
sb.AppendFormat(",");
string columnName = rdr.GetName(i);
object ob = rdr[i];
var col = table.Columns[columnName];
sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
}
sb.AppendFormat(")");
return sb.ToString();
}
