Jesper程式學習筆記

 

先到Nuget安裝Entity Framework

 

準備好DB內的Table Schema

 

製作相對應的Model

使用EF有幾個重點

1.Table一定要有PK

2.若是複合主鍵則需明確定義不同的Order =>即 [Column(Order = 0)]

(關於DB與.Net間的資料類型對應可參考 SQL Server 資料類型對應)

public class MySampleTable
{
    [Key]
    [Column(Order = 0)]  //當 Table 是複合主鍵時必定義 Order
    public int ID { get; set; }
    public string NAME { get; set; }
    public DateTime BIRTHDAY { get; set; }
    public double HEIGHT { get; set; }
    public double WEIGHT { get; set; }
}

 

製作DbContext (命名慣例為DbName+Context)

假設你有一個資料庫叫Sample => SampleContext

public class SampleContext : DbContext
{
    public DbSet<MySampleTable> MySampleTable { get; set; }
    public SampleContext(string connStr) : base(new SqlConnection(connStr), true)
    {
        Database.SetInitializer<SampleContext>(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();  //取消自動轉換 Table Name 單複數

        var instance = System.Data.Entity.SqlServer.SqlProviderServices.Instance;
    }
}

 

製作Repository

EF支援你使用

1.LINQ to SQL

2.Inline SQL

3.呼叫Stored Procedure

以下各建立一個範例 (呼叫SP可參考Inline SQL寫法)

public class SampleRerpository
{
    public SampleContext ContextInit()
    {
        //通常連線字串會放在config中
        return new SampleContext("Data Source=主機名稱;user id=帳號;password=密碼;Initial Catalog=資料庫名稱;Application Name=站台名稱");
    }

    public List<MySampleTable> GetTable()
    {
        using (SampleContext dbContext = ContextInit())
        {
            IQueryable<MySampleTable> result = dbContext.MySampleTable;

            result = result.Take(10);

            return result.ToList();
        }
    }

    /// <summary>
    /// 假設你想寫Inline SQL
    /// </summary>
    /// <returns></returns>
    public List<MySampleTable> GetTableUsingSql()
    {
        var sql = @"SELECT TOP (10) [ID],[NAME],[BIRTHDAY],[HEIGHT],[WEIGHT]
                    FROM [dbo].[MySampleTable]";

        using (SampleContext dbContext = ContextInit())
        {
            return dbContext.Database.SqlQuery<MySampleTable>(sql).ToList();
        }
    }
}

 

呼叫Repository

public class HomeController : Controller
{
    private SampleRerpository _repo = new SampleRerpository();

    public ActionResult Sample()
    {
        var data = _repo.GetTable();

        return View(data);
    }
}

 

呈現結果

@model IEnumerable<Repository.Models.MySampleTable>
@{
    ViewBag.Title = "Hello World!";
}
<h2>@ViewBag.Title</h2>

<p>
    <br />
    <table class="table">
        <tr>
            <th>ID</th>
            <th>NAME</th>
            <th>BIRTHDAY</th>
            <th>HEIGHT</th>
            <th>WEIGHT</th>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.ID</td>
                <td>@item.NAME</td>
                <td>@item.BIRTHDAY</td>
                <td>@item.HEIGHT</td>
                <td>@item.WEIGHT</td>
            </tr>
        }
    </table>
</p>

 

最終結果