private void ReloadParts(SqlConnection connection) { _partsById.Clear(); _partsByQualifier.Clear(); var command = new SqlCommand(String.Format( "select {0}, {1} from {3} where {2} IS NULL", IdColumn, NameColumn, ParentIdColumn, _table ), connection); var unexploredParts = new Dictionary<long, DBPart>(); using (var result = command.ExecuteReader()) { if (!result.HasRows) return; while (result.Read()) { var part = new DBPart(result.GetInt64(0), result.GetString(1), null); unexploredParts.Add(part.Id, part); _partsByQualifier.Add(part.ToString().ToLower(), part); _partsById.Add(part.Id, part); } } var innerCommand = new SqlCommand(String.Format( "select {0}, {1} from {3} where {2} = @parentId", IdColumn, NameColumn, ParentIdColumn, _table ), connection); while (unexploredParts.Count > 0) { var unexploredPart = unexploredParts.First(); unexploredParts.Remove(unexploredPart.Key); innerCommand.Parameters.Clear(); innerCommand.Parameters.Add(new SqlParameter("parentId", unexploredPart.Key)); using(var innerResult = innerCommand.ExecuteReader()) { if (!innerResult.HasRows) continue; while (innerResult.Read()) { var part = new DBPart(innerResult.GetInt64(0), innerResult.GetString(1), unexploredPart.Value); unexploredParts.Add(part.Id, part); _partsByQualifier.Add(part.ToString().ToLower(), part); _partsById.Add(part.Id, part); } } } }
public DBPart Insert(Part part, Connector connector) { var connection = connector.Connection; if (connection.State != ConnectionState.Open) connection.Open(); var partsToCreate = new List<Part>(); while (part != null && GetPart(part.ToString()) == null) { partsToCreate.Add(part); part = part.Parent; } partsToCreate.Reverse(); var command = new SqlCommand(String.Format( "delete from {0} where {1} = @name and {2} = @parentId; insert into {0}({1}, {2}) values(@name, @parentId); select IDENT_CURRENT('{0}')", _table, NameColumn, ParentIdColumn ), connection ); var lastCreatedPartId = part == null ? null : ((int?)GetPart(part.ToString()).Id); var lastCreatedPart = lastCreatedPartId == null ? null : _partsById[lastCreatedPartId.Value]; foreach(var partToCreate in partsToCreate) { command.Parameters.Clear(); command.Parameters.Add(new SqlParameter("name", SqlDbType.VarChar) { Value = partToCreate.Name }); command.Parameters.Add(new SqlParameter("parentId", SqlDbType.BigInt) { Value = (Object)lastCreatedPartId ?? DBNull.Value }); var newPart = new DBPart(Int32.Parse(command.ExecuteScalar().ToString()), partToCreate.Name, lastCreatedPart); _partsById.Add(newPart.Id, newPart); _partsByQualifier.Add(newPart.ToString().ToLower(), newPart); lastCreatedPart = newPart; } return lastCreatedPart; }