ASP.Net MVC Model ─ 使用Dapper

2019-08-05

1586
0
ASP.Net MVC

ASP.Net MVC Model 使用Dapper 實作 By jessie

MVC的Model層的資料存取除了可使用先前介紹過的Entity Framework之外，還可以使用Dapper來實作。

【安裝】

透過Nuget將Dapper套件加入至專案中。

範例操作:
Step1:

建一個對應到STORE資料表Entity的Class

public class STORE_Entity
    {
        /// <summary>
        /// 資料編號(自動編號)
        /// </summary>
        public int PK { get; set; }

        /// <summary>
        /// 店家名稱
        /// </summary>
        public string NAME { get; set; }

        /// <summary>
        /// 電話
        /// </summary>
        public string TEL { get; set; }

        /// <summary>
        /// 備註
        /// </summary>
        public string MEMO { get; set; }

        /// <summary>
        /// 建立時間
        /// </summary>
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd HH:mm:ss}")]
        public DateTime CREATE_TIME { get; set; }
    }

Step2:

在web.config中加入連線字串

<connectionStrings>
    <add name="connStr_DRINK" providerName="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=DRINK; Persist Security Info=True;User ID=帳號;Password=密碼" />
</connectionStrings>

Step3:

範例─實作店家查詢功能,

STORE_DataAccess.cs(資料庫存取)

/// <summary>
        /// 取得店家資料列表
        /// </summary>
        /// <param name="KeyWord">關鍵字</param>
        /// <returns></returns>
        public IEnumerable<STORE_Entity> GetSTOREList(string KeyWord)
        {
            string strSQL = @" select [PK], [NAME] ,[TEL], [REMARK] ,[CREATE_TIME] from [STORE] ";
            if (!string.IsNullOrEmpty(KeyWord)) //關鍵字
            {
                strSQL += " where [NAME] like '%'+@NAME+'%' ";
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr_DRINK"].ConnectionString))
            {
                var result = conn.Query<STORE_Entity>(strSQL,
                    new
                    {
                        QueryDate = DateTime.Today,
                        NAME = KeyWord
                    });
                return result;
            }

        }

STORE_ViewModel.cs(給前端view用的Model)

public class STORE_ViewModel
    {
        /// <summary>
        /// 關鍵字
        /// </summary>
        public string KeyWord { get; set; }

        /// <summary>
        /// 多筆店家資料
        /// </summary>
        public List<STORE_Entity> STOREList { get; set; }
    }

Controller(QueryController.cs)

public ActionResult Query()
        {
            STORE_DataAccess da = new STORE_DataAccess();
            STORE_ViewModel result = new STORE_ViewModel();
            result.STOREList = da.GetSTOREList("").ToList();
            return View(result);
        }

        [HttpPost]
        public ActionResult Query(STORE_ViewModel PostData)
        {
            STORE_DataAccess da = new STORE_DataAccess();
            PostData.STOREList = da.GetSTOREList(PostData.KeyWord).ToList();
            return View(PostData);
        }

Query.cshtml (前端View)

@model Drink.Models.STORE_ViewModel

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Query</title>
</head>
<body>
    <div>
        @using (Html.BeginForm("Query", "Store", FormMethod.Post))
        {
            @:請輸入關鍵字：
            @Html.TextBoxFor(m => m.KeyWord)
            <input type="submit" value="查詢">
            <table border="1" cellspacing="0" cellpadding="0">
                <thead>
                    <tr>
                        <th>資料編號</th>
                        <th>名稱</th>
                        <th>電話</th>
                        <th>備註</th>
                        <th>資料更新時間</th>
                    </tr>
                </thead>

                <tbody>
                    @if (Model.STOREList != null)
                    {
                        for (int i = 0; i < Model.STOREList.Count; i++)
                        {
                            <tr>
                                <td align="center">@Html.DisplayFor(m => m.STOREList[i].PK)</td>
                                <td align="center">@Html.DisplayFor(m => m.STOREList[i].NAME)</td>
                                <td align="center">@Html.DisplayFor(m => m.STOREList[i].TEL)</td>
                                <td align="center">@Html.DisplayFor(m => m.STOREList[i].REMARK)</td>
                                <td align="center">@Html.DisplayFor(m => m.STOREList[i].CREATE_TIME)</td>
                            </tr>
                        }
                    }
                    else
                    {
                        <tr>
                            <td align="center" colspan="5">查無資料</td>
                        </tr>
                    }
                </tbody>
            </table>
        }
    </div>
</body>
</html>

執行結果

參考資料:

ASP.NET MVC 的 Model 使用 Dapper

http://kevintsengtw.blogspot.com/2015/09/aspnet-mvc-model-dapper.html

ASP.Net MVC

回首頁