关于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();
        }

修改/增加/逻辑删除

//新增 
//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();