文章

EF6 SqlServer 简单例子和支持的原生sql例子

总体结构如图:

总体结构

1. 使用NuGet添加EF6

2. 添加实体Model

    public class User_Model {
        public int ID { get; set; }
        public string S_UserID { get; set; }
        public int Sex { get; set; }
        public string Phone { get; set; }
        public int Age { get; set; }
        public DateTime? AddDateTime { get; set; }
        public DateTime? UpdDateTime { get; set; }
    }  

3. 创建Map(Model成员约束)

    public class User_Map : EntityTypeConfiguration<Model.User_Model> {
        public User_Map() {
            ToTable("3660_kangwl.Users");//自定义表名
            this.HasKey(u => u.ID);//主键
            this.Property(u => u.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);//自增张字段
            this.Property(u => u.AddDateTime).HasColumnName("Add_DateTime").IsRequired();
            this.Property(u => u.S_UserID).HasColumnName("S_User_ID");
            this.Property(u => u.UpdDateTime).HasColumnName("Update_DateTime").IsOptional();
        }
    }  

4. 创建数据库上下文 Context

    public class Smile : DbContext {
        public Smile() :base() {
            Database.Connection.ConnectionString =
                System.Configuration.ConfigurationManager.ConnectionStrings["smile"].ConnectionString;
            Database.SetInitializer(new NullDatabaseInitializer<Smile>());
        }
        public DbSet<Model.User_Model> User { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            modelBuilder.Configurations.Add(new User_Map());
            base.OnModelCreating(modelBuilder);
        }
    } 

5. 操作代码

   class Program
    {
        private static void Main(string[] args) {
            Smile smile = new Smile();
            var users = smile.User;
            foreach (User_Model user in users) {
                Console.WriteLine(user.S_UserID);
            }
            //由于加了实体与数据库字段映射
            //下面的sql查询用了as转换成映射字段,否则取不出来的
            //切记,切记
            //Add_DateTime(数据库字段),AddDateTime(映射字段)
            var rawSqlUsers =
                smile.Database.SqlQuery<Model.User_Model>(
                    "select id,s_user_id as S_UserID,Sex,Phone,Age,Add_DateTime as AddDateTime,update_datetime as UpdDateTime from users");
            foreach (User_Model user in rawSqlUsers) {
                Console.WriteLine(user.ID);
            }
            //command指令如下
            //参数id=@p0 
            int ret = smile.Database.ExecuteSqlCommand("update users set age=-1 where id=@p0", 2);
            Console.WriteLine(ret > 0);
            //两个参数
            int ret1 = smile.Database.ExecuteSqlCommand("update users set age=-1 where id=@p0 and s_user_id=@p1", 12,"kangwl");
            Console.WriteLine(ret > 0);
 
            var auser =
                smile.Database.SqlQuery<Model.User_Model>(
                    "select id,s_user_id as S_UserID,Sex,Phone,Age,Add_DateTime as AddDateTime,update_datetime as UpdDateTime from users where id=@p0",
                    12);
 
       
            Console.WriteLine(auser.First().Age);
 
            Console.Read();
        }
    }  

6. show 一下配置文件

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="smile" connectionString="server=smile.xxx.xxx;database=3660_smile;uid=3660_kangwl;pwd=xxxxx;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration> 
0 3

发表评论