sql script:
--查询lukfookhr 的所有表 exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.88.1,1435;User ID=geovindu;Password=000;').lukfookhr.information_schema.TABLES exec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure --查询数据数据库 SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.88.1,1435;User ID=geovindu;Password=000;').lukfookhr.sys.databases --dbo.BillingDetails --BillingDetailId int identity --Owner --Number --BankName --Swift --CardType int --ExpiryMonth --ExpiryYear --Discriminator IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BillingDetails') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE BillingDetails GO create table BillingDetails ( BillingDetailId INT IDENTITY(1,1) PRIMARY KEY, [Owner] nvarchar(50) not null, Number nvarchar(50) null, --BankName nvarchar(50) null, --Swift nvarchar(50) null, --CardType int null, --ExpiryMonth nvarchar(50) null, --ExpiryYear nvarchar(50) null, Discriminator nvarchar(50) null ) go --BankAccount BankName Swift IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].BankAccount') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE BankAccount GO create table BankAccount ( Id INT IDENTITY(1,1) PRIMARY KEY, BankName nvarchar(50) null, Swift nvarchar(50) null ) go --CreditCard CardType ExpiryMonth ExpiryYear IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].CreditCard') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE CreditCard GO create table CreditCard ( Id INT IDENTITY(1,1) PRIMARY KEY, CardType nvarchar(50) null, ExpiryMonth nvarchar(50) null, ExpiryYear nvarchar(50) null ) go --dbo.Categories --CategoryId int --CategoryName 50 IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Categories') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Categories GO create table Categories ( CategoryId INT IDENTITY(1,1) PRIMARY KEY, CategoryName nvarchar(50) not null ) go --dbo.Products --ProductId int identity --ProductName 50 --CategoryId int dbo.Categories CategoryId IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Products GO create table Products ( ProductId INT IDENTITY(1,1) PRIMARY KEY, ProductName nvarchar(50) not null, CategoryId int FOREIGN KEY REFERENCES Categories(CategoryId) ) go --dbo.Customers --Id int identity --Name 50 --Email 20 --CreatedTime date --ModifiedTime date IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Customers') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Customers GO create table Customers ( Id INT IDENTITY(1,1) PRIMARY KEY, [Name] nvarchar(50) not null, Email nvarchar(20) null, CreatedTime date null, ModifiedTime date null ) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('geovindu','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('test','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('江城','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('保中','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('王二','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('李三','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('张四','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('赵五','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('陈六','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('刘七','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('徐八','geovindu@163.com' ,getdate(),getdate()) go insert into Customers([Name],Email,CreatedTime,ModifiedTime) values('何九','geovindu@163.com' ,getdate(),getdate()) go select * from Customers go --dbo.Orders --Id int identity --Quantity int -- Code 400 --Price Decimal 18,4 --CustomerId int dbo.Customers CustomerId --CreatedTime --ModifiedTime IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Orders') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Orders GO create table Orders ( Id INT IDENTITY(1,1) PRIMARY KEY, Quantity int null, Code nvarchar(400) null, Price decimal(18,4) null, CustomerId int FOREIGN KEY REFERENCES Customers(Id), CreatedTime date null, ModifiedTime date null ) go insert into Orders(Quantity,Code,Price,CustomerId,CreatedTime,ModifiedTime) values(10,N'4008',250.00,1,getdate(),getdate()) go select * from Orders go --dbo.Students --Id int identity --Name --Age byte --CreatedTime date -- ModifiedTime date IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Students') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Students GO create table Students ( Id INT IDENTITY(1,1) PRIMARY KEY, [Name] nvarchar(50) not null, Age tinyint null, CreatedTime date null, ModifiedTime date null ) go insert into Students([Name],Age,CreatedTime,ModifiedTime) values('sibodu',12,getdate(),getdate()) go select * from Students go --StudentContact ContactNumber IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].StudentContact') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE StudentContact GO create table StudentContact ( Id INT IDENTITY(1,1) PRIMARY KEY, StudentId int FOREIGN KEY REFERENCES Students(Id), ContactNumber nvarchar(50) null ) go -- dbo.Courses --Id int identity --Name --MaximumStrength int --CreatedTime --ModifiedTime -- IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Courses') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Courses GO create table Courses ( Id INT IDENTITY(1,1) PRIMARY KEY, [Name] nvarchar(50) not null, MaximumStrength int null, CreatedTime date null, ModifiedTime date null ) go insert into Courses([Name],MaximumStrength,CreatedTime,ModifiedTime) values('语文',25,getdate(),getdate()) go select * from Courses go -- DropForeignKey("dbo.Orders", "CustomerId", "dbo.Customers"); -- DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");
using System; using System.ComponentModel.DataAnnotations; namespace EntityFramework6.Enity { /// <summary> /// /// </summary> public abstract class BaseEntity { /// <summary> /// /// </summary> [Display(Name = "ID")] public int Id { get; set; } /// <summary> /// /// </summary> [Display(Name = "創建時間")] public DateTime CreatedTime { get; set; } /// <summary> /// /// </summary> [Display(Name = "修改時間")] public DateTime ModifiedTime { get; set; } } } using EntityFramework6.Enity; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; namespace EntityFramework6.Entity { /// <summary> /// /// </summary> public class Course : BaseEntity { /// <summary> /// 課程名称 /// </summary> [Display(Name = "課程名称")] public string Name { get; set; } /// <summary> /// 課時時長 /// </summary> [Display(Name = "課時時長")] public int MaximumStrength { get; set; } /// <summary> /// 学生 /// </summary> [Display(Name = "学生")] public virtual ICollection<Student> Students { get; set; } } } using EntityFramework6.Enity; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Data.Entity.Core.Metadata; //using Microsoft.EntityFrameworkCore.Metadata; namespace EntityFramework6.Entity { /// <summary> /// /// </summary> public class Student : BaseEntity { [Display(Name = "姓名")] public string Name { get; set; } [Display(Name = "年龄")] public byte Age { get; set; } /// <summary> /// 课程 /// </summary> [Display(Name = "课程")] public virtual ICollection<Course> Courses { get; set; } /// <summary> /// /// </summary> [Display(Name = "联系方式")] public virtual StudentContact Contact { get; set; } } } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using EntityFramework6.Enity; using System.ComponentModel.DataAnnotations; using System.Data.Entity.Core.Metadata; using System.ComponentModel.DataAnnotations.Schema; //using Microsoft.EntityFrameworkCore.Metadata; namespace EntityFramework6.Entity { /// <summary> /// /// </summary> public class StudentContact { /// <summary> /// /// </summary> [Display(Name = "ID")] public int Id { get; set; } /// <summary> /// 联系电话 /// </summary> [Display(Name = "联系电话")] public string ContactNumber { get; set; } /// <summary> /// 学生ID /// </summary> [Display(Name = "学生ID")] [ForeignKey("Student")] public int StudentId { get; set; } /// <summary> /// 学生 /// </summary> [Display(Name = "学生")] public virtual Student Student {get;set;} } } using System; using System.Collections.Generic; using System.Linq.Expressions; using System.ComponentModel.DataAnnotations; namespace EntityFramework6.Enity { /// <summary> /// 顾客 /// </summary> public partial class Customer : BaseEntity { /// <summary> /// /// </summary> [Display(Name = "标题")] public string Name { get; set; } /// <summary> /// /// </summary> [Display(Name = "邮件")] public string Email { get; set; } [Display(Name = "訂單")] public virtual ICollection<Order> Orders { get; set; } } } using System; using System.ComponentModel.DataAnnotations; namespace EntityFramework6.Enity { /// <summary> /// /// </summary> public class Order : BaseEntity { [Display(Name = "数量")] public int Quantity { get; set; } [Display(Name = "编码")] public string Code { get; set; } [Display(Name = "价格")] public decimal Price { get; set; } [Display(Name = "客户ID")] public int CustomerId { get; set; } [Display(Name = "客户")] public virtual Customer Customer { get; set; } } } using System.ComponentModel.DataAnnotations; namespace EntityFramework6.Entity { /// <summary> /// /// </summary> public class Product { /// <summary> /// /// </summary> [Display(Name = "id")] public int ProductId { get; set; } /// <summary> /// /// </summary> [Display(Name = "产品名称")] public string ProductName { get; set; } [Display(Name = "类型ID")] public int CategoryId { get; set; } [Display(Name = "类型")] public virtual Category Category { get; set; } } }
map:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; using EntityFramework6.Entity; //https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx namespace EntityFramework6.Map { /// <summary> /// /// </summary> public class StudentContactMap:EntityTypeConfiguration<StudentContact> { public StudentContactMap() { //table ToTable("StudentContact"); //key HasKey(t => t.Id); //fields Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); Property(x=>x.StudentId) .HasColumnName("StudentId") .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None); Property(x => x.ContactNumber); //relationship one-to-one 一对一 HasRequired(s => s.Student).WithOptional(l => l.Contact); } } } using EntityFramework6.Entity; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; //https://entityframework.net/one-to-one-relationship //https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx //https://github.com/dotnet/ef6 //https://docs.microsoft.com/en-us/ef/ef6/ namespace EntityFramework6.Map { /// <summary> /// geovindu /// </summary> public class StudentMap : EntityTypeConfiguration<Student> { /// <summary> /// /// </summary> public StudentMap() { //table ToTable("Students"); //key HasKey(t => t.Id); //fields Property(x => x.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); Property(x => x.Name).HasColumnType("VARCHAR").HasMaxLength(50); Property(x => x.Age); Property(x => x.CreatedTime); Property(x => x.ModifiedTime); //relationship Many-to-Many Relationishhip 多对多 HasMany(t => t.Courses).WithMany(c => c.Students) .Map(t => t.ToTable("SutdentCourcese") .MapLeftKey("StudentId") .MapRightKey("CourseId")); //realationship One-to-One 一对一 HasOptional(x => x.Contact) .WithOptionalDependent(l=>l.Student); } } } using EntityFramework6.Entity; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; namespace EntityFramework6.Map { /// <summary> /// geovindu /// </summary> public class CourseMap : EntityTypeConfiguration<Course> { public CourseMap() { //table ToTable("Courses"); //key HasKey(k => k.Id); //property Property(t => t.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity); Property(t => t.Name).HasColumnType("VARCHAR").HasMaxLength(50); Property(t => t.MaximumStrength); Property(t => t.CreatedTime); Property(t => t.ModifiedTime); } } } using EntityFramework6.Enity; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; namespace EntityFramework6.Map { /// <summary> /// geovindu,Geovin Du /// /// </summary> public class OrderMap : EntityTypeConfiguration<Order> { /// <summary> /// /// </summary> public OrderMap() { //table ToTable("Orders"); //key HasKey(t => t.Id) .Property(p => p.Id) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); //fields Property(t => t.Quantity); Property(t => t.Price).HasPrecision(18,4); Property(t => t.CustomerId); Property(t => t.CreatedTime); Property(t => t.ModifiedTime); Property(t => t.Code).HasMaxLength(400); //relationship One-to-Many Relationship 一对多 HasRequired(t => t.Customer) .WithMany(c => c.Orders) .HasForeignKey(t => t.CustomerId) .WillCascadeOnDelete(false); } } } using EntityFramework6.Enity; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; namespace EntityFramework6.Map { /// <summary> /// /// </summary> public class CustomerMap : EntityTypeConfiguration<Customer> { public CustomerMap() { //table ToTable("Customers"); //key HasKey(t => t.Id); //properties Property(t => t.Name).HasColumnType("VARCHAR").HasMaxLength(50).IsRequired(); Property(t => t.Email).HasColumnType("VARCHAR").HasMaxLength(20).IsRequired(); Property(t => t.CreatedTime).IsRequired(); Property(t => t.ModifiedTime).IsRequired(); } } } using EntityFramework6.Entity; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; namespace EntityFramework6.Map { /// <summary> /// /// </summary> public class CategoryMap : EntityTypeConfiguration<Category> { public CategoryMap() { ToTable("Categories"); HasKey(k => k.CategoryId); Property(p => p.CategoryName).HasColumnType("VARCHAR").HasMaxLength(50); // HasMany(p => p.Products).WithRequired(c => c.Category).HasForeignKey(k => k.CategoryId); } } } using EntityFramework6.Entity; using System.Data.Entity.ModelConfiguration; using System.ComponentModel.DataAnnotations.Schema; namespace EntityFramework6.Map { /// <summary> /// /// </summary> public class ProductMap : EntityTypeConfiguration<Product> { public ProductMap() { ToTable("Products"); HasKey(k => k.ProductId); Property(p => p.ProductName).HasColumnType("VARCHAR").HasMaxLength(50); } } }
using EntityFramework6.Enity; using System; using System.Linq; using System.Data; using System.Data.SqlClient; using System.Data.Entity.ModelConfiguration; namespace EntityFramework6 { /// <summary> /// geovindu,Geovin Du /// /// </summary> public class Program { static void Main(string[] args) { using (var ctx = new EfDbContext()) { ctx.Database.Log = Console.WriteLine; // var customers = ctx.Customers; //var customer = customers.FirstOrDefault(d => d.Id == 1); // customers.FindAsync(1).Result; //Console.WriteLine(customer.Name); //bool contains = customers.Where(c => c.Name == "geovindu").Contains(customer); //添加 //var addcustomer = new Customer() //{ // Name = "涂聚文", // Email = "463588883@qq.com", // CreatedTime = DateTime.Now, // ModifiedTime = DateTime.Now //}; //ctx.Customers.Add(addcustomer); //int k=ctx.SaveChanges(); //修改 // var editcustomer = new Customer() // { // Id =1, // Name="涂年生", // Email="463588883@qq.com", // CreatedTime=DateTime.Now, // ModifiedTime=DateTime.Now // }; // ctx.Entry(editcustomer).State=System.Data.Entity.EntityState.Modified; //int k=ctx.SaveChanges(); // if (k > 0) // Console.WriteLine("ok,eidt"); //删除 //var delcustomer = new Customer() //{ // Id= 3 //}; //ctx.Entry(delcustomer).State = System.Data.Entity.EntityState.Deleted; //int k = ctx.SaveChanges(); //if (k > 0) // Console.WriteLine("ok del"); //查询集合 var customers = ctx.Customers; //var names = customers // .Select(x => x.Name) // //.Take(10) // .OrderBy(d=>1) // .Skip(5) // .ToList(); var names = customers .Select(x => x.Name) .Take(10) .OrderBy(d => 1) //.Skip(5) .ToList(); foreach (var name in names) { Console.WriteLine(name+"\n\t"); } //原始查询 var cus = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers").ToList(); foreach (var cs in cus) { Console.WriteLine("name:"+cs.Name + "\n\t"); } var id = 1; var par = new SqlParameter[] { new SqlParameter(){ ParameterName="@id",SqlDbType=SqlDbType.Int,Value=id }, }; var cust = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers where id=@id", par).ToList(); } Console.ReadKey(); } } }