EF 数据库增删改查

                                          EF 数据库增删改查

一、准备工作

1、SQL数据库，建一个数据库，数据库名称为WareHouse。

2、VS端，新建控制台应用程序，添加EF，连接数据库，添加连接字符串。

 

 二、操作数据库（CRUD）

public class EFCodeFirstDbContext : DbContext
    {

        public EFCodeFirstDbContext() : base("name=MyStrConn")
        {
        }
        public DbSet<LocationInfo> LocationInfos { get; set; }
    }

 

 public class LocationInfo
    {
        [Key]
        public int LocationId { get; set; }//库位号
        public string Type { get; set; }//种类
        public int Number { get; set; }//数量
        public string Material { get; set; }//材质
        public string RFIDNumber { get; set; }//料盘的RFID号

        public LocationInfo(int locationId,string type,int number,string material,string rfidNumber)
        {
            this.LocationId = locationId;
            this.Type = type;
            this.Number = number;
            this.Material = material;
            this.RFIDNumber = rfidNumber;
        }
        //定义无参数的构造函数主要是因为在通过DbSet获取对象进行linq查询时会报错
        //The class 'EFCodeFirstModels.Student' has no parameterless constructor.
        public LocationInfo() { }


    }

 

1、增（Create）

class Program
    {
        
        static void Main(string[] args)
        {
            //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放
            using (var db = new EFCodeFirstDbContext())
            {
                //增
                LocationInfo locationInfo1 = new LocationInfo(1, "empty", 0, "empty", "00010000");
                LocationInfo locationInfo2 = new LocationInfo(2, "empty", 0, "empty", "00020000");
                LocationInfo locationInfo3 = new LocationInfo(3, "empty", 0, "empty", "00030000");
                LocationInfo locationInfo4 = new LocationInfo(4, "empty", 0, "empty", "00040000");
                LocationInfo locationInfo5 = new LocationInfo(5, "empty", 0, "empty", "00050000");
                LocationInfo locationInfo6 = new LocationInfo(6, "empty", 0, "empty", "00060000");
                LocationInfo locationInfo7 = new LocationInfo(7, "empty", 0, "empty", "00070000");
                LocationInfo locationInfo8 = new LocationInfo(8, "empty", 0, "empty", "00080000");
                LocationInfo locationInfo9 = new LocationInfo(9, "empty", 0, "empty", "00090000");
                LocationInfo locationInfo10 = new LocationInfo(10, "empty", 0, "empty", "000A0000");
                db.LocationInfos.Add(locationInfo1);
                db.LocationInfos.Add(locationInfo2);
                db.LocationInfos.Add(locationInfo3);
                db.LocationInfos.Add(locationInfo4);
                db.LocationInfos.Add(locationInfo5);
                db.LocationInfos.Add(locationInfo6);
                db.LocationInfos.Add(locationInfo7);
                db.LocationInfos.Add(locationInfo8);
                db.LocationInfos.Add(locationInfo9);
                db.LocationInfos.Add(locationInfo10);
                db.SaveChanges();
               
            }
            Console.ReadLine();
        }
    }

 

2、查（Retrieve）

查所有数据

 class Program
    {
        static void Main(string[] args)
        {
            //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放
            using (var db = new EFCodeFirstDbContext())
            {
                //查
                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in db.LocationInfos)
                {
                    stringBuilder.AppendFormat(
                        "库位号：{0}\t物料类型：{1}\t物料数量：{2}\t物料材质：{3}\tRFID号：{4}\n", 
                        item.LocationId, item.Type, item.Number, item.Material, item.RFIDNumber);
                }
                Console.WriteLine(stringBuilder);
            }
            Console.ReadLine();
        }
    }

 

查某条数据

 

class Program
    {
        static void Main(string[] args)
        {
            //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放
            using (var db = new EFCodeFirstDbContext())
            {
                //查
                var query = from q in db.LocationInfos
                            where q.LocationId == 5
                            select q;
                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in query)
                {
                    stringBuilder.AppendFormat(
                        "库位号：{0}\t物料类型：{1}\t物料数量：{2}\t物料材质：{3}\tRFID号：{4}\n", 
                        item.LocationId, item.Type, item.Number, item.Material, item.RFIDNumber);
                }
                Console.WriteLine(stringBuilder);
            }
            Console.ReadLine();
        }
    }

 

3、改（Update）

class Program
    {
        static void Main(string[] args)
        {
            //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放
            using (var db = new EFCodeFirstDbContext())
            {
                //改
                LocationInfo locationInfo = new LocationInfo() { LocationId=5};

                locationInfo = db.LocationInfos.Attach(locationInfo);
                locationInfo.Type = "bottle";
                locationInfo.Number = 4;
                locationInfo.Material = "Sn";
                locationInfo.RFIDNumber = "00050101";
                db.SaveChanges();
            }
            Console.ReadLine();
        }
    }

 

4、删（Delete）

class Program
    {
        static void Main(string[] args)
        {
            //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放
            using (var db = new EFCodeFirstDbContext())
            {
                //删
                LocationInfo locationInfo = db.LocationInfos.Where(p=>p.LocationId==9).FirstOrDefault();
                db.LocationInfos.Remove(locationInfo);
                db.SaveChanges();
            }
            Console.ReadLine();
        }
    }

由于刚接触数据库，对很多概念不是特别清晰，操作数据库的类别分的不是很好，后面再详细学习！