C# webAPI +access+xadmin+layui 前端

1. 首先建立webAPI項目：

 2. 建立DAL類庫，使用者通路資料庫。

添加AccessHelper 封裝一個資料庫通路類：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public static class AccessHelper
    {

        private static string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\\Users\\hcy\\Documents\\TextBook.mdb";
        public static DataTable GetDataTableBySql(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connstr); //Jet OLEDB:Database Password=
            OleDbCommand cmd = conn.CreateCommand();

            cmd.CommandText = sql; // "select * from books";
            conn.Open();
            OleDbDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            if (dr.HasRows)
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    dt.Columns.Add(dr.GetName(i));
                }
                dt.Rows.Clear();
            }
            while (dr.Read())
            {
                DataRow row = dt.NewRow();
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    row[i] = dr[i];
                }
                dt.Rows.Add(row);
            }
            cmd.Dispose();
            conn.Close();
            return dt;
        }

        public static int ExecuteNonQuerySql(string sql) {
            int rows = 0;

             using (OleDbConnection connect = new OleDbConnection(connstr))
             {
                 using (OleDbCommand cmd = new OleDbCommand(sql, connect))
                 {
                    //if (para != null && para.Length > 0)
                    //    cmd.Parameters.AddRange(para);
                     if (connect.State == System.Data.ConnectionState.Closed) connect.Open();

                     rows = cmd.ExecuteNonQuery();
                 }
             }
            return rows;
        }
    }
}      

3. 添加一個控制器 用于存放資料

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web.Http;
using System.Web.Script.Serialization;

namespace SchoolWorkAPI.Controllers
{
    [RoutePrefix("api/TextBooks")]
    public class TextBooksController : ApiController
    {
        [HttpGet]
        [Route("test")]
        public IHttpActionResult Get()
        {


            //string sqlSelect = "select count(*) from EmpInfo";
            //object count = DAL.SqlHelper.ExecuteScalar(DAL.SqlHelper.Connstr, CommandType.Text, sqlSelect, null);
            return Ok("API OK, and result empCount:" + 0);
            
        }

        [HttpPost]
        [Route("getBooksList")]
        public HttpResponseMessage getBooksList([FromBody] PageHelper pageHelper)
        {
            ResultMsg resultMsg = new ResultMsg();

            string sqlTotal = "select * from books";
            DataTable dt_totals = DAL.AccessHelper.GetDataTableBySql(sqlTotal);

           // string sql = string.Format("select top 10  * from books where id not in (select top " + pageHelper.Page * 10 + " id from books order by id desc ) order by id desc");

            string sql= "SELECT * FROM (SELECT TOP "+pageHelper.Rows+" * FROM(SELECT TOP "+
                pageHelper.Page*pageHelper.Rows+" * FROM books ORDER BY id DESC) ORDER BY id) ORDER BY id DESC ";

          
            DataTable dt = DAL.AccessHelper.GetDataTableBySql(sql);
            ResultDataInfo resultDataInfo = new ResultDataInfo();
            resultDataInfo.data = DAL.DataTableTo.ToDataList<Models.TextBooksInfo>(dt);
            resultDataInfo.total = dt_totals.Rows.Count;
            resultMsg.data = resultDataInfo;
            resultMsg.code = ResultMsgCode.SUCCESS;
            resultMsg.msg = "success";
            return toJson(resultMsg);
        }


        [HttpPost]
        [Route("add")]
        public HttpResponseMessage addBooksList([FromBody] Models.TextBooksInfo info)
        {
            ResultMsg resultMsg = new ResultMsg();
            string sql = "select * from books";
            int result = DAL.AccessHelper.ExecuteNonQuerySql(sql);
            resultMsg.data = result;
            resultMsg.code = ResultMsgCode.SUCCESS;
            resultMsg.msg = "success";
            return toJson(resultMsg);
        }

        [HttpPost]
        [Route("update")]
        public HttpResponseMessage updateBooksList([FromBody] Models.TextBooksInfo info)
        {
            ResultMsg resultMsg = new ResultMsg();
            string sql = "select * from books";
            int result = DAL.AccessHelper.ExecuteNonQuerySql(sql);
            resultMsg.data = result;
            resultMsg.code = ResultMsgCode.SUCCESS;
            resultMsg.msg = "success";
            return toJson(resultMsg);
        }

        [HttpPost]
        [Route("del")]
        public HttpResponseMessage delBooksList([FromBody] Models.TextBooksInfo info)
        {
            ResultMsg resultMsg = new ResultMsg();
            string sql = "select * from books";
            int result = DAL.AccessHelper.ExecuteNonQuerySql(sql);
            resultMsg.data = result;
            resultMsg.code = ResultMsgCode.SUCCESS;
            resultMsg.msg = "success";
            return toJson(resultMsg);
        }


        public static HttpResponseMessage toJson(Object obj)
        {
            String str;
            if (obj is String || obj is Char)
            {
                str = obj.ToString();
            }
            else
            {
                JavaScriptSerializer serializer = new JavaScriptSerializer();
                //serializer.RegisterConverters(new JavaScriptConverter[]{new DataTableConverter()});
                str = serializer.Serialize(obj);
            }
            HttpResponseMessage result = new HttpResponseMessage { Content = new StringContent(str, Encoding.GetEncoding("UTF-8"), "application/json") };
            return result;
        }

        public static HttpResponseMessage toJson2(DataTable dt) {
            String str=DataTableToJson(dt);//
            HttpResponseMessage result = new HttpResponseMessage { Content = new StringContent(str, Encoding.GetEncoding("UTF-8"), "application/json") };
            return result;
        }

        public static string DataTableToJson(DataTable table)
        {
            StringBuilder JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }
    }

    public class PostDataInfo
    {
        private object data;
        private string type;

        public object Data { get => data; set => data = value; }
        public string Type { get => type; set => type = value; }
    }

    public class ResultMsg
    {
        public int code { get; set; }
        public Object data { get; set; }
        public object msg { get; set; }
    }
    public class ResultMsgCode
    {
        /// <summary>
        /// 成功
        /// </summary>
        public static int SUCCESS = 200;
        /// <summary>
        /// 失敗
        /// </summary>
        public static int ERR = 201;
    }

    public class PageHelper {

        private int page;
        private int rows;

        public int Page { get => page; set => page = value; }
        public int Rows { get => rows; set => rows = value; }
    }

    public class ResultDataInfo {
        public int total { get; set; }
        public IList data { get; set; }
    }

}      

幾個類方法等介紹:

HttpResponseMessage 指定傳回類型

ResultMsg  指定傳回資料的架構在這個之内傳回，便于前端資料對接