internal string CreateBulkInsertStatementWithParameter(ITableData data, string tableName, List <OdbcParameter> parameters)
{
QueryText.Clear();
TableName = tableName;
GetSourceAndDestColumnNames(data);
AppendBeginSql(tableName);
while (data.Read())
{
QueryText.Append("(");
string[] placeholder = new string[DestColumnNames.Count];
QueryText.Append(string.Join(",", placeholder.Select(s => s + "?")));
QueryText.AppendLine(")");
foreach (string destColumnName in DestColumnNames)
{
int colIndex = data.GetOrdinal(destColumnName);
string dataTypeName = data.GetDataTypeName(colIndex);
if (data.IsDBNull(colIndex))
{
parameters.Add(new OdbcParameter(destColumnName, DBNull.Value));
}
else
{
parameters.Add(new OdbcParameter(destColumnName, data.GetValue(colIndex)));
}
}
if (data.NextResult())
{
QueryText.Append(",");
}
}
AppendEndSql();
return(QueryText.ToString());
}
private void AppendEndSql()
{
if (IsAccessDatabase)
{
QueryText.AppendLine(") a;");
}
}
private void AppendBeginSql()
{
QueryText.AppendLine($@"INSERT INTO {TN.QuotatedFullName} ({string.Join(",", SourceColumnNames.Select(col => QB + col + QE))})");
if (IsAccessDatabase)
QueryText.AppendLine(" SELECT * FROM (");
else
QueryText.AppendLine("VALUES");
}
private void AppendEndSql()
{
if (IsAccessDatabase)
{
QueryText.AppendLine(") a;");
}
else if (ConnectionType == ConnectionManagerType.Oracle)
{
QueryText.AppendLine(")");
}
}
private void AppendBeginSql(string tableName)
{
QueryText.AppendLine($@"INSERT INTO {tableName} ({string.Join(",", SourceColumnNames)})");
if (IsAccessDatabase)
{
QueryText.AppendLine(" SELECT * FROM (");
}
else
{
QueryText.AppendLine("VALUES");
}
}
private void AppendValueListSql(List<string> values, bool lastItem)
{
if (IsAccessDatabase)
{
QueryText.AppendLine("SELECT " + string.Join(",", values) + $" FROM {AccessDummyTableName} ");
if (lastItem) QueryText.AppendLine(" UNION ALL ");
}
else
{
QueryText.Append("(" + string.Join(",", values) + $")");
if (lastItem) QueryText.AppendLine(",");
}
}
private void AppendBeginSql()
{
QueryText.AppendLine($@"INSERT INTO {TN.QuotatedFullName} ({string.Join(",", SourceColumnNames.Select(col => QB + col + QE))})");
if (IsAccessDatabase)
{
QueryText.AppendLine(" SELECT * FROM (");
}
else if (ConnectionType == ConnectionManagerType.Oracle)
{
QueryText.AppendLine($" SELECT {string.Join(",", SourceColumnNames.Select(col => QB + col + QE))} FROM (");
}
else
{
QueryText.AppendLine("VALUES");
}
}
private void AppendValueListSql(List <string> values, bool lastItem)
{
if (IsAccessDatabase)
{
QueryText.AppendLine("SELECT " + string.Join(",", values) + $" FROM {AccessDummyTableName} ");
if (lastItem)
{
QueryText.AppendLine(" UNION ALL ");
}
}
else if (ConnectionType == ConnectionManagerType.Oracle)
{
QueryText.Append("SELECT ");
for (int i = 0; i < values.Count; i++)
{
QueryText.Append($"{values[i]} {QB}{DestColumnNames[i]}{QE}");
if (i + 1 < values.Count)
{
QueryText.Append(",");
}
}
QueryText.AppendLine(" FROM DUAL");
if (lastItem)
{
QueryText.AppendLine(" UNION ALL ");
}
}
else
{
QueryText.Append("(" + string.Join(",", values) + $")");
if (lastItem)
{
QueryText.AppendLine(",");
}
}
}