LiuLyndon Look For: [MVC] WebAPI C.R.U.D

此篇介紹如何使用 MVC API 進行 CRUD

INSERT (C)：INSERT 是將資料插入到資料庫物件中的指令
SELECT (R) ：SELECT是SQL資料操縱語言(DML)中用於查詢表格內欄位資料的指令
UPDATE (U)：UPDATE 指令是依給定條件，將符合條件的資料表中的資料更新為新的數值
DELETE (D)：DELETE 指令為自資料庫物件中刪除資料的指令
Step 1.

Step 2. 在 App_Data 中創建資料庫

Step 3. 並建立一個 UserInfo table

Step 3. 建立 User Controller API，選取讀取/寫入動作 Web API


        // GET: api/User
        public IEnumerable<string> Get()
        {
            return new string[] { "value1", "value2" };
        }

        // GET: api/User/5
        public string Get(int id)
        {
            return "value";
        }

        // POST: api/User
        public void Post([FromBody]string value)
        {
        }

        // PUT: api/User/5
        public void Put(int id, [FromBody]string value)
        {
        }

        // DELETE: api/User/5
        public void Delete(int id)
        {
        }

Step 4. 使用 Dapper 建立 CRUD


namespace MVCAPICRUD.Models
{
    public class UserInfo
    {
        public int? id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
}



using Dapper;
using MVCAPICRUD.Models;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Http;

namespace MVCAPICRUD.Controllers
{
    public class UserController : ApiController
    {
        private string ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Demo\MVCAPICRUD\MVCAPICRUD\App_Data\Database.mdf;Integrated Security=True";

        // GET: api/User
        public IEnumerable<UserInfo> Get()
        {
            List<UserInfo> lUSerInfo;

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                lUSerInfo = con.Query<UserInfo>(
                   "Select * from UserInfo",
                   commandTimeout: 0).ToList();
            }

            return lUSerInfo;
        }

        // GET: api/User/5
        public UserInfo Get(int id)
        {
            UserInfo userInfo;

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                DynamicParameters Param = new DynamicParameters();
                Param.Add("@id", id, dbType: DbType.Int32);

                userInfo = con.Query<UserInfo>(
                   "SELECT * FROM UserInfo WHERE id = @id",
                   Param,
                   commandTimeout: 0).FirstOrDefault();
            }

            return userInfo;
        }

        // POST: api/User
        public void Post([FromBody]UserInfo value)
        {
            int count = 0;

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                count = con.Execute(
                   "INSERT INTO UserInfo VALUES (@Name, @Age)",
                   value);
            }
        }

        // PUT: api/User/5
        public void Put([FromBody]UserInfo value)
        {
            int count = 0;

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                count = con.Execute(
                   "UPDATE UserInfo SET Name = @Name, Age = @Age Where id = @id",
                   value);
            }
        }

        // DELETE: api/User/5
        public void Delete(int id)
        {
            int count = 0;

            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                DynamicParameters Param = new DynamicParameters();
                Param.Add("@id", id, dbType: DbType.Int32);

                count = con.Execute(
                   "DELETE FROM UserInfo WHERE id = @id",
                   Param,
                   commandTimeout: 0);
            }
        }
    }
}