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); }