示例#1
0
        public IEnumerable <Motor> GetAllMotors()
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionStrHelper.CnnVal("TestDB")))
            {
                var lookupMotors          = new Dictionary <string, MotorRecord>();
                var lookupParts           = new Dictionary <string, Part>();
                var allMotorsWithBomItems = connection.Query <MotorRecord, BOMItemRecord, Part, MotorRecord>
                                                (@"select m.*, bi.*, p.* from Motors m
                    inner join BOMItems bi on m.MotorNumber = bi.MotorNumber
                    inner join Parts p on bi.PartNumber = p.PartNumber",
                                                (m, bi, p) =>
                {
                    MotorRecord motor;
                    Part part;
                    if (!lookupMotors.TryGetValue(m.MotorNumber, out motor))
                    {
                        lookupMotors.Add(m.MotorNumber, motor = m);
                    }
                    if (!lookupParts.TryGetValue(p.PartNumber, out part))
                    {
                        lookupParts.Add(p.PartNumber, part = p);
                    }
                    if (motor.BOM == null)
                    {
                        motor.BOM = new List <BOMItemRecord>();
                    }
                    bi.PartItem = part;
                    motor.BOM.Add(bi);
                    return(motor);
                }, splitOn: "MotorNumber,PartNumber").AsQueryable();
                var resultInRecords = lookupMotors.Values;

                var result = MotorFactory.BulidMotor(resultInRecords);

                return(result);
            }
        }
示例#2
0
        public void InsertMotor(Motor motor)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionStrHelper.CnnVal("TestDB")))
            {
                string motorSql   = $@"insert into dbo.Motors (MotorNumber, MotorType, Displacement, Feature, Description) 
                                    values (@MotorNumber, @MotorType, @Displacement, @Feature, @Description)";
                string partsSql   = $@"insert into dbo.Parts (PartNumber, Designation, Description) 
                                    values (@PartNumber, @Designation, @Description)";
                string BomItemSql = $@"insert into dbo.BOMItems(MotorNumber, PartNumber, PositionNumber, Quantity) 
                                    values (@MotorNumber, @PartNumber, @PositionNumber, @Quantity)";

                connection.Execute(motorSql, MotorFactory.BulidMotorRecord(motor));

                var partsToInsert = motor.BOM.Select(b => b.PartItem).Distinct();
                connection.Execute(partsSql, partsToInsert);

                var bomItems = CreateDynItemsListFromBomItemsInMotor(new List <Motor> {
                    motor
                });
                connection.Execute(BomItemSql, bomItems);
            }
        }
示例#3
0
        public Motor GetMotorByMotorNumber(string motorNumber)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionStrHelper.CnnVal("TestDB")))
            {
                var queryResult = connection.Query <MotorRecord, BOMItemRecord, Part, MotorRecord>
                                      (@"select m.*, bi.*, p.* from Motors m
                    inner join BOMItems bi on m.MotorNumber = bi.MotorNumber
                    inner join Parts p on bi.PartNumber = p.PartNumber
                    where m.MotorNumber = @motorNumber",
                                      (m, bi, p) =>
                {
                    if (m.BOM == null)
                    {
                        m.BOM = new List <BOMItemRecord>();
                    }
                    m.BOM.Add(bi);
                    return(m);
                }, splitOn: "MotorNumber,PartNumber").AsQueryable();

                var result = MotorFactory.BulidMotor(queryResult.FirstOrDefault(), queryResult.FirstOrDefault().BOM, queryResult.FirstOrDefault().BOM.Select(bi => bi.PartItem));

                return(result);
            }
        }