public static HashSet <string> PreperInsertQuery(DataFromTable dataFromTables, Table tables)
{
string example = "INSERT ALL {0} SELECT* FROM dual";
string example1 = "INTO {0}({1}) VALUES({2})";
HashSet <string> query = new HashSet <string>();
int j = 0;
var result2 = Enumerable.Range(0, tables.columns.ToList().Count).Where(i => tables.columns.ToList()[i].DATA_TYPE == "DATE").ToList();
StringBuilder columns = new StringBuilder();
foreach (var column in tables.columns)
{
columns.Append("\"" + column.COLUMN_NAME + "\",");
}
columns = columns.Remove(columns.Length - 1, 1);
StringBuilder rows = new StringBuilder();
foreach (var row in dataFromTables.Table)
{
StringBuilder values = new StringBuilder();
int i = 0;
foreach (var record in row)
{
if (record != null)
{
if (!result2.Contains(i))
{
values.Append("'" + record + "',");
}
else
{
values.Append("TO_DATE('" + record + "','dd/mm/yyyy hh24:mi:ss'),");
}
}
else
{
values.Append("null,");
}
++i;
}
values = values.Remove(values.Length - 1, 1);
rows.Append(String.Format(example1, tables.TableName, columns, values));
++j;
if (j == 200)
{
query.Add(String.Format(example, rows));
rows.Clear();
j = 0;
}
}
query.Add(String.Format(example, rows));
return(query);
}
private HashSet <DataFromTable> GetDataFromTableList()
{
HashSet <DataFromTable> dataFromTables = new HashSet <DataFromTable>();
string example = "Select * From {0} ORDER BY {1}";
string example1 = "Select * From {0}";
foreach (var table in dbToXml.tables)
{
PrimaryKey result1 = dbToXml.primaryKeys.FirstOrDefault(kvp => kvp.TableName == table.TableName);
string select = "";
if (result1 != null)
{
select += String.Format(example, table.TableName, result1.ColumnName);
}
else
{
select += String.Format(example1, table.TableName);
}
OracleCommand sel = new OracleCommand
{
Connection = connection,
CommandText = select
};
DataFromTable dataFromTable = new DataFromTable
{
TableName = table.TableName
};
using (OracleDataReader reader = sel.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
List <string> vs = new List <string>();
for (int i = 0; i < reader.FieldCount; ++i)
{
if (!reader.IsDBNull(i))
{
vs.Add(Convert.ToString(reader.GetValue(i)));
}
else
{
vs.Add(null);
}
}
dataFromTable.Table.Add(vs);
}
}
}
dataFromTables.Add(dataFromTable);
}
return(dataFromTables);
}
