关于C# CURD的几种写法

1.采用LINQ TO SQL

1）首先定义实体类即你用到的字段名(查询需要)
2）写类似于
查询：

     public List<实体类> GetRosterData(string companycode)
        {
            var rodata = from com in 哪个表？
                                                where com.Company == companycode
                                                    select new 实体类
                                                    {
                                                        //给字段赋值，用作后续处理
                                                        Department = com.Department,
                                                        EntryTime = com.EntryTime,
                                                        Name = com.Name,
                                                        Base = com.Base,
                                                        JobNumber = com.JobNumber,
                                                        Post = com.Post,
                                                    };
            return rodata.ToList();
        }

     
//多表联查 左连接
 public IQueryable<实体> GetSampleMouldDailiesNew()
        {
            var result = from item in _erpData.SampleMouldDaily
                         join main in _erpData.SampleMain
                             on item.MainId equals main.Id into qq
                         from itemmMain in qq.DefaultIfEmpty()
                         where item.Deleted == false
                         && item.ScheduleDate >= new DateTime(2023, 1, 1)
                         select new SampleMouldDailyModel()
                         {
                             Id = item.Id,
                             ProductType = item.MainId == null ? 10 : itemmMain.ProductType,
                             SerialNumber = item.SerialNumber,
                             Saler = item.Saler,
                             ScheduleDate = item.ScheduleDate,
                             Type = item.Type,
                             LineNum = item.LineNum,
                             Customer = item.Customer,
                             PaiXu = item.PaiXu,
                             MainId = item.MainId,
                             MouldingStyleDesc = item.MouldingStyleDesc,
                             IsExistMsd = item.IsExistMSD,
                             Mould = item.Mould,
                             HotStampingFoil = item.HotStampingFoil,
                             IsExistHsf = item.IsExistHSF,
                             Length = item.Length,
                             Number = item.Number,
                             ComplateNumber = item.ComplateNumber,
                             FinishedState = item.FinishedState,
                             ComplateDate = item.ComplateDate,
                             ProcessCredentials = item.ProcessCredentials,
                             MaterialColour = item.MaterialColour,
                             Remark = item.Remark,
                             Deleted = item.Deleted,
                             CreatedBy = item.CreatedBy,
                             CreatedOn = item.CreatedOn,
                             LastUpdateBy = item.LastUpdateBy,
                             LastUpdateOn = item.LastUpdateOn,
                             FinishedLine = item.FinishedLine,
                             MouldNumber = item.MouldNumber,
                             UnfinishedReason = item.UnfinishedReason,
                             LineReamrk = item.LineReamrk,
                             MouldPlace = item.MouldPlace,
                     
                             Image = item.Image == null ? null : item.Image.ToArray(),
                             NcOrgCode = itemmMain.NcOrgCode == null || itemmMain.NcOrgCode == "" ? "C02001" : itemmMain.NcOrgCode,
                         };
            return result;
        }

修改/增加/逻辑删除

//新增 
//new目标表对象，直接赋值 参数为自定义实体类对象赋值
     public void AddHrBasicTables(HRBasicTable newData)
        {
            var ManpowerIndi = new ManpowerIndis()
            {
                Company = newData.Company,
                RecruitingPersonnel = newData.RecruitingPersonnel,
                PositionType = newData.PositionType,
                EntryTime = newData.EntryTime,
                HRUserId = newData.HRUserId,
                Deleted = false,
                Year = DateTime.Now.Year.ToString()
            };
            //插入数据
            _dd.ManpowerIndicators.InsertOnSubmit(ManpowerIndicator);
            //提交
            _dd.SubmitChanges();
        }

//修改 通过sid，制定修改哪一个
        public void SaveDataDetail(int hidSID, DateTime? newDate, string newHrName,string newLevel,string oldLevel)
        {
            var result = (from rd in 表
                          where rd.SID == hidSID
                select rd).FirstOrDefault();
            if (result != null && oldLevel == "P0" && newLevel != oldLevel)
            {
                result.HRName = newHrName;
                result.TripartiteTime = newDate;
                result.Level = newLevel;
                result.ChangeDate = DateTime.Now;
            }
            else
            {
                result.HRName = newHrName;
                result.TripartiteTime = newDate;
                result.Level = newLevel;
            }
            //提交修改
            _dd.SubmitChanges();
        }
//删除 
//与修改类似，直接逻辑删除，将Deleted置为true即可

2.原生sql server写法

1）查询需要定义实体类并赋值

public List<实体类> GetReachData(string year,string month,int? stage,string name)
        {
            var sql = string.Format(@"SELECT 
                                COUNT(*) AS Num,
                              
                                rna.Stage
                            FROM 
		                            PerformanceAchievement  AS rna
                            WHERE 
                                rna.Deleted = 0 
                                AND YEAR(rna.Time) = {0}
                                AND rna.Stage = {1}
                                AND rna.RecruiterName like '%{2}%'", year, stage,name);
            //参数
            if (month != null)
            {
                sql += string.Format(" AND MONTH(rna.Time) = {0}", month);
            }
            //排序
            sql += " GROUP BY \r\n                                rna.Stage";
            //查询
            var result = _Data.ExecuteQuery<实体类>(sql).ToList();
            return result;
        }

2.修改、删除、新增用原生SQL语法

//有参数，加string.Format，没有直接@,注意提交 
var sql = string.Format(@"INSERT INTO ShandongFrameKanban 
                                (WorkShift, Monitor, Line, PlanQuantity, FinishedQuantity, ChangeMouldTimes, ChangeMouldingStyleTimes, ArchieveRate, Yield, DensityRate, SpeedRate,CreateDate)
                                VALUES ('{0}', '{1}','{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}')", 
                                workShift, monitor, lines, planQuantity.ToString(), finishedQuantity.ToString(), changeMouldTimes.ToString(), 
                                changeMouldingStyleTimes.ToString(), archieveRate == null ? string.Empty : $"{(archieveRate * 100):F0}%", yield == null ? string.Empty : $"{((decimal)(yield * 100)):F0}%",
                                $"{densityRate:F0}%", $"{(speedArchieveRate * 100):F0}%",DateTime.Now);
                    _data.ExecuteCommand(sql);
                    _data.SubmitChanges();