private void InsertFromStagingTable(EntityTable table)
{
EntitySQLBuilder <TBase> sql = new EntitySQLBuilder <TBase>(mapping);
string primaryKeyColumnName = table.GetPrimaryKeyColumn().Name;
sql.BuildInsertFromStagingToMainWithOutputIds(table);
using SqlCommand cmd = _connection.CreateCommand();
cmd.CommandText = sql.ToString();
cmd.CommandType = CommandType.Text;
cmd.Transaction = _transaction;
int totalPrimaryKeys = ReadPrimaryKeys(cmd, table, primaryKeyColumnName);
Logger.Debug($"{(totalPrimaryKeys == 0 ? "No" : totalPrimaryKeys.ToString())} {table.Name} were inserted");
}
public void BuildInsertFromStagingToMainWithOutputIds(EntityTable table)
{
List <Column> columns = table.Columns;
List <EntityColumn> entityColumns = table.EntityColumns;
Column primarKeyColumn = table.GetPrimaryKeyColumn();
if (columns.Count == 0)
{
return;
}
bool isFirstElement = true;
_sql.AppendLine($"create table #insertedIds ({primarKeyColumn.Name} {primarKeyColumn.GetSqlDbType().ToSqlString()});")
.AppendLine()
.Append("insert into " + table.Name + " (");
for (int i = 0; i < entityColumns.Count; i++)
{
if (!columns[i].IsPrimaryKey())
{
_sql.Append((isFirstElement ? "" : ", ") + columns[i].Name);
isFirstElement = false;
}
}
_sql.AppendLine(")")
.AppendLine()
.AppendLine($"output inserted.{primarKeyColumn.Name} into #insertedIds({primarKeyColumn.Name})")
.AppendLine();
_sql.AppendLine(new SelectStatement(columns.Where(c => !c.IsPrimaryKey()).ToList()).From(table.StagingTable, ColumnTypes.EntityColumn).ToString())
.AppendLine()
.AppendLine("select * from #insertedIds")
.AppendLine()
.AppendLine("drop table #insertedIds")
.AppendLine($"drop table {table.StagingTable.Name}")
.AppendLine();;
}
public void BuildSelectStatement()
{
foreach (EntityTable table in _mapping.Tables)
{
List <EntityColumn> foreignKeyColumns = table.GetForeignKeyColumns();
if (table.Type != _baseType)
{
foreach (EntityColumn column in foreignKeyColumns)
{
EntityTable foreignTable = _mapping.Tables.Where(t => t.Name == column.ForeignKeyTableMapping).First();
EntityColumn foreignColumn = foreignTable.GetPrimaryKeyColumn();
column.Restrictions.Add(new Restriction(column, $"select {foreignColumn.Name} from {foreignTable.StagingTable.Name}", Enums.RestrictionTypes.In));
}
}
_sql.AppendLine();
_sql.AppendLine(new SelectStatement(table).Into(table.StagingTable).ToString());
_sql.AppendLine();
_sql.AppendLine(new SelectStatement(table).From(table.StagingTable).ToString());
}
}