代码svn地址 (用户名:liu,密码;123)
这一篇中,我们要实现的基本就是这么个东西

在这里插入图片描述

这个页面就是我们创建的DataManagement.vue页面,我们的主要代码将写在这里面,这个表格我们将用Element-UI的table组件来实现

组件地址:https://element.eleme.cn/#/zh-CN/component/table

在这里插入图片描述

我们先用这个基础表格看看效果,点击表格下面的显示代码,我们把这些代码复制粘贴到我们的DataManagement.vue里,运行看看效果

在这里插入图片描述

这时有人可能会问,我地图没有啊,来,让我们运行nginx,我们的地图显示要依赖于由nginx发布的arcgis js文件和css文件的

一、数据库建表和后台构建

1,建表

可我们需要的不是静态数据,而是从数据库读取的数据,所以,我先要创建一个表,创建一个名为data的表,往里填点数据先

在这里插入图片描述

2,后台构建(新建Controller控制器文件和Model模型文件)

在后面已经使用sql suger来连接数据库,如果不想下面的写法可以跳到文章10使用sql suger的写法

我们要读取它,就需要在后台新建控制器文件了,上一篇也说了不少了。在Controllers文件夹里新建DataController.cs控制器文件,代码如下:

(1)新建控制器文件

using Newtonsoft.Json;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Text;

namespace API.Controllers
{
    /// <summary>
    /// 菜单栏
    /// </summary>
    public class DataController : ApiController
    {

        /// <summary>
        /// 获取菜单
        /// </summary>
        public String Get()
        {
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //建立DataTable对象(相当于建立前台的虚拟数据库中的数据表)
                DataTable dtable;
                //建立DataRowCollection对象(相当于表的行的集合)
                DataRowCollection coldrow;
                //建立DataRow对象(相当于表的列的集合)
                DataRow drow;
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                string sltStr = "select * from Data ";//查询的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                MySqlDataReader reader = sqlCmd.ExecuteReader();
                String jsonData = ToJson(reader);
                sqlCon.Close();
                if (jsonData.Trim().Equals("")) jsonData = "success";
                return jsonData;
            }
            catch (Exception ex)
            {
                return "fail";
            }

        }

        /// <summary>
        /// DataReader转换为Json串
        /// </summary>
        public static string ToJson(MySqlDataReader dataReader)
        {
            StringBuilder Builder = new StringBuilder();

            int rows = 0;
            while (dataReader.Read())
            {
                if (rows++ > 0) Builder.Append(",");

                // 行数据转Json
                Builder.Append("{");
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    if (i > 0) Builder.Append(",");

                    // 列名称
                    string strKey = dataReader.GetName(i);
                    strKey = "\"" + strKey + "\"";

                    // 列数据
                    Type type = dataReader.GetFieldType(i);
                    string strValue = dataReader[i].ToString();
                    strValue = String.Format(strValue, type).Trim();
                    if (type == typeof(string) || type == typeof(DateTime)) strValue = "\"" + strValue + "\"";

                    Builder.Append(strKey + ":" + strValue);
                }
                Builder.Append("}");
            }
            dataReader.Close();

            if (rows > 1) return "[" + Builder.ToString() + "]";
            else return Builder.ToString();
        }


        /// <summary>
        /// Post请求
        /// </summary>
        public void Post([FromBody] string value)
        {
        }

        /// <summary>
        /// Put请求
        /// </summary>
        public void Put(int id, [FromBody] string value)
        {
        }

        /// <summary>
        /// Delete请求
        /// </summary>
        public void Delete(int id)
        {
        }
    }
}

(2)新建模型文件

在后台的Models文件夹新建Data.cs类文件:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SqlSugar;

namespace API.Models
{
    /// <summary>
    /// 数据管理数据类
    /// </summary>
    public class Data
    {
      [SugarColumn(IsPrimaryKey = true)]
        /// <summary>
        /// id
        /// </summary>
        public int Id { get; set; }
        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// X坐标
        /// </summary>
        public double? X { get; set; }
        /// <summary>
        /// Y坐标
        /// </summary>
        public double? Y { get; set; }
        /// <summary>
        /// 值
        /// </summary>
        public double? Num { get; set; }
        /// <summary>
        /// 备注
        /// </summary>
        public string Note { get; set; }
    }
}

在后台的Models文件夹新建MessageModel.cs类文件:

namespace API.Models
{
    /// <summary>
    /// 通用返回信息类
    /// </summary>
    public class MessageModel
    {
        /// <summary>
        /// 状态码
        /// </summary>
        public int status { get; set; } = 200;
        /// <summary>
        /// 页码
        /// </summary>
        public int page { get; set; }
        /// <summary>
        /// 数据条数
        /// </summary>
        public int pageCount { get; set; }
        /// <summary>
        /// 操作是否成功
        /// </summary>
        public bool success { get; set; } = false;
        /// <summary>
        /// 返回信息
        /// </summary>
        public string msg { get; set; } = "服务器异常";
        /// <summary>
        /// 返回数据集合
        /// </summary>
        public object response { get; set; }

    }
}

在后台的Models文件夹新建PageModel.cs类文件:

using System.Collections.Generic;

namespace API.Models
{
    /// <summary>
    /// 通用分页信息类
    /// </summary>
    public class PageModel<T>
    {
        /// <summary>
        /// 当前页标
        /// </summary>
        public int page { get; set; } = 1;
        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount { get; set; } = 6;
        /// <summary>
        /// 数据总数
        /// </summary>
        public int dataCount { get; set; } = 0;
        /// <summary>
        /// 每页大小
        /// </summary>
        public int PageSize { set; get; }
        /// <summary>
        /// 返回数据
        /// </summary>
        public List<T> data { get; set; }

    }

}

(3)修改获取数据的方法(使其分页查询和可按Name查询)

将DataController.cs文件修改为:

using Newtonsoft.Json;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Text;
using System.Threading.Tasks;
using API.Models;
using System.Collections.Generic;

namespace API.Controllers
{
    /// <summary>
    /// 菜单栏
    /// </summary>
    public class DataController : ApiController
    {

        /// <summary>
        /// 获取菜单
        /// </summary>
        public MessageModel Get(int page = 1,int intPageSize=10,string query="")
        {
            string where = "1=1";
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                //查询条件
                if (!string.IsNullOrEmpty(query))
                {
                    where = where + " " + "and" + " " + "Name like '%" + query + "%'";
                }
                string sltStr = "select * from Data WHERE "+where;//查询的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                MySqlDataReader reader = sqlCmd.ExecuteReader();
                var jsonData = ToJson(reader);
                sqlCon.Close();
                var model = new PageModel<Data>();
                model.dataCount = jsonData.Count;
                model.data = jsonData.Skip(intPageSize * (page - 1)).Take(intPageSize).ToList();
                model.PageSize = intPageSize;
                model.page = page;
                if (model.data.Count == 0)
                {
                    model.pageCount = 0;
                }
                else
                {
                    model.pageCount = (int)Math.Ceiling(Convert.ToDouble(model.dataCount / model.data.Count));
                }
                return new MessageModel()
                {
                    msg = "获取成功",
                    success = true,
                    response = model
                };
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "获取失败",
                    success = false,
                };
            }

        }

        /// <summary>
        /// DataReader转换为数组数据
        /// </summary>
        public List<Data> ToJson(MySqlDataReader dataReader)
        {
            List<Data> list = new List<Data>();
            while (dataReader.Read())
            {
                var model = new Data();
                double?NULL = null;
                model.Id = Convert.ToInt32(dataReader[0].ToString());
                model.Name = dataReader[1].ToString() != "" ?dataReader[1].ToString():null;
                model.X =dataReader[2].ToString()!=""? Convert.ToDouble(dataReader[2].ToString()):NULL;
                model.Y = dataReader[3].ToString() != "" ? Convert.ToDouble(dataReader[3].ToString()) : NULL;
                model.Num = dataReader[4].ToString() != "" ? Convert.ToDouble(dataReader[4].ToString()) : NULL;
                model.Note = dataReader[5].ToString() != "" ? dataReader[5].ToString() : null;
                list.Add(model);
            }
            dataReader.Close();
            return list;
        }


        /// <summary>
        /// Post请求
        /// </summary>
        public void Post([FromBody] string value)
        {
        }

        /// <summary>
        /// Put请求
        /// </summary>
        public void Put(int id, [FromBody] string value)
        {
        }

        /// <summary>
        /// Delete请求
        /// </summary>
        public void Delete(int id)
        {
        }
    }
}

把后台运行起来(报错就安装对应包),下面我们来修改vue前端

二,vue前台修改

1,创建接口

第一步要在api.js里创建接口了。在vue前端的api文件夹里的api.js文件里添加

//获取数据管理数据
export const getData = params => {
  return axios.get(base+`/api/Data/Get`);
};

2,引用接口和写查询方法

将DataManagement.vue修改为:

<template>
  <section style="overflow: hidden; height: 100%;">
    <!--查询与新增-->
    <el-form :inline="true"
             style="float: left; height: 10%;margin-top: 1%;"
             size="mini"
             class="serchFrom">
      <el-form-item>
        <label>名称:</label>
        <el-input v-model="query"
                  style="width: 150px;"
                  placeholder="请输入名称"></el-input>
      </el-form-item>
      <el-form-item>
        <el-button @click="handleSearch"
                   type="primary">查询</el-button>
      </el-form-item>
      <el-form-item>
        <el-button type="primary"
                   icon="el-icon-circle-plus">新增</el-button>
      </el-form-item>
    </el-form>
    <!--表格-->
    <el-table :data="tableData"
              stripe
              border
              highlight-current-row
              :header-cell-style="{'text-align': 'center'}"
              :cell-style="{'text-align': 'center'}"
              size="mini"
              height="80%"
              style="width: 100%">
      <el-table-column prop="Name"
                       label="名称"
                       width="150">
      </el-table-column>
      <el-table-column prop="Num"
                       label="数量">
      </el-table-column>
      <el-table-column prop="X"
                       label="X坐标">
      </el-table-column>
      <el-table-column prop="Y"
                       label="Y坐标">
      </el-table-column>
      <el-table-column prop="Note"
                       label="备注">
      </el-table-column>
      <el-table-column label="操作"
                       align="center"
                       width="280">
        <!-- width="200" -->
        <template slot-scope="scope">
          <el-button type="primary"
                     icon="el-icon-edit"
                     size="mini">编辑</el-button>
          <el-button type="success"
                     icon="el-icon-more-outline"
                     size="mini">详情</el-button>
          <el-button type="danger"
                     icon="el-icon-delete"
                     size="mini">删除</el-button>
        </template>
      </el-table-column>
    </el-table>
    <!--分页-->
    <el-col :span="24">
      <el-pagination background
                     small
                     layout="total, slot,prev, pager, next,jumper"
                     @current-change="handleCurrentChange"
                     :total="total"
                     :page-size="pageSize"
                     style="float: right; height: 10%;">
        <span style="margin-right: 10px; font-weight: 400; color: #606266;">(每页10条)</span>
      </el-pagination>
    </el-col>
  </section>
</template>

<script>
import { getData } from '../api/api'
export default {
  data () {
    return {
      total: 0,
      page: 1,
      pageSize: 10,
      query: '',
      activeIndex: 1,
      menuList: [],
      tableData: [],    //表格数据  
    }
  },
  methods: {
    //页数改变
    handleCurrentChange (val) {
      this.page = val
      this.getList()
    },
    //获取数据
    getList () {
      let para = {
        query: this.query,
        page: this.page,
        intPageSize: this.pageSize,
      }
      getData(para).then((res) => {
        let data = res.data.response
        this.tableData = data.data
        this.total = data.dataCount
        this.pageSize = data.PageSize
      })
    },
    //查询
    handleSearch(){
      this.getList()
    }
  },
  mounted () {
    this.getList()
  }
}
</script>

那现在名称查询和分页都做好了,我们先完善一下页面,然后来做增删改和详情。

3,查询时的输入做一下验证

加个页面加载效果v-loading,对查询时的输入做一下验证,防止一些select *from之类的SQL注入。

在src文件夹的main.js文件里加上这么一句

//禁止特殊字符输入(防SQL注入)
Vue.prototype.validSe_SQL = function (value, number = 255) {
  value = value.replace(/from|select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute/i, '');
  if (value.length >= number) {
    this.$message({
      type: "warning",
      message: `输入内容不能超过${number}个字符`
    });
  }
  console.log(value)
  return value;
};

变为:

// The Vue build version to load with the `import` command
// (runtime-only or standalone) has been set in webpack.base.conf with an alias.
import Vue from 'vue'
import App from './App'
import router from './router'
import ElementUI from 'element-ui'
import enLocale from 'element-ui/lib/locale/lang/en' //英文
import zhLocale from 'element-ui/lib/locale/lang/zh-CN' //中文
import 'element-ui/lib/theme-chalk/index.css'
import layer from '../src/components/layer/layer.js'
import './style/blue.css'
import esriLoader from 'esri-loader'
import MAP from '../config/config'

Vue.config.productionTip = false
Vue.use(
  ElementUI,
  { zhLocale },
)
Vue.use(esriLoader)
Vue.prototype.$layer=layer(Vue)
//禁止特殊字符输入(防SQL注入)
Vue.prototype.validSe_SQL = function (value, number = 255) {
  value = value.replace(/from|select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute/i, '');
  if (value.length >= number) {
    this.$message({
      type: "warning",
      message: `输入内容不能超过${number}个字符`
    });
  }
  console.log(value)
  return value;
};
/* eslint-disable no-new */
new Vue({
  el: '#app',
  router,
  components: { App },
  template: '<App/>'
})

4,新增

在vue前端的views文件夹里新建HandleDataManagement文件夹,在HandleDataManagement文件夹里新建add.vue文件,代码如下

<template>
  <section style="overflow: hidden; height: 100%;">
    <el-form label-width="80px"
             ref="addForm"
             :model="addForm"
             class="form-box"
             size="mini">
      <el-form-item label="名称"
                    prop="Name">
        <el-input v-model="addForm.Name"
                  auto-complete="off"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="X坐标"
                    prop="X">
        <el-input v-model="addForm.X"
                  auto-complete="off"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="Y坐标"
                    prop="Y">
        <el-input v-model="addForm.Y"
                  auto-complete="off"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="数值"
                    prop="Num">
        <el-input v-model="addForm.Num"
                  auto-complete="off"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="备注"
                    prop="Note">
        <el-input v-model="addForm.Note"
                  auto-complete="off"
                  size="mini"></el-input>
      </el-form-item>
    </el-form>
    <el-col :span="24"
            style="text-align:right">
      <el-button size="mini"
                 @click.native="handleCancelForm">取消</el-button>
      <el-button size="mini"
                 type="primary"
                 @click.native="addSubmit"
                 :loading="submitloading">提交</el-button>
    </el-col>
  </section>
</template>

<script>
import helper from '../../components/layer/helper/helper'
import { addData } from '../../api/api'
export default {
  data () {
    return {
      layerid_p: null,  //父弹窗id
      layerid_c: this.layerid,  //弹窗id
      submitloading: false,  //提交状态
      addForm: {
        Name: '',
        X: '',
        Y: '',
        Num: '',
        Note: '',
      }
    }
  },
  props: {
    info: {
      type: Object,
      default: () => {
        return {}
      },
    },
    layerid: {
      type: String,
      default: '',
    },
    lyoption: {
      type: Object,
      default: () => {
        return {}
      },
    },
  },
  methods: {
    //新增取消
    handleCancelForm () {
      this.$layer.close(this.layerid_c);
    },
    //新增提交
    addSubmit () {
      this.submitloading = true
      let form = Object.assign({}, this.addForm)
      //提交的数据至少有一个名称值
      if (form.Name == null) {
        this.$message({
          message: "名称不能为空",
          type: 'error',
        })
        this.submitloading = false
        return
      }
      addData(form).then((res) => {
        if (res.data.success) {
          this.$message({
            message: res.data.msg,
            type: 'success',
          })
          //刷新表格
          helper.btnyes(null, this.lyoption)
          //关闭弹窗
          this.$layer.close(this.layerid_c)
        }
        else {
          this.$message({
            message: res.data.msg,
            type: 'error',
          })
        }
      }).catch((e) => {
        this.$message({
          message: e.message,
          type: 'error',
        })
      }).finally(() => {
        this.submitloading = false
      })
    }
  },
  mounted () {
    this.layerid_p = this.info.layerid_p
  }
}
</script>

<style scoped>
.form-box {
  margin-top: 10%;
}
</style>

将DataManagement.vue修改为:

<template>
  <section style="overflow: hidden; height: 100%;">
    <!--查询与新增-->
    <el-form :inline="true"
             style="float: left; height: 10%;margin-top: 1%;"
             size="mini"
             class="serchFrom">
      <el-form-item>
        <label>名称:</label>
        <el-input v-model="query"
                  style="width: 150px;"
                  placeholder="请输入名称"></el-input>
      </el-form-item>
      <el-form-item>
        <el-button @click="handleSearch"
                   type="primary">查询</el-button>
      </el-form-item>
      <el-form-item>
        <el-button type="primary"
                   @click="handleAdd"
                   icon="el-icon-circle-plus">新增</el-button>
      </el-form-item>
    </el-form>
    <!--表格-->
    <el-table :data="tableData"
              stripe
              border
              highlight-current-row
              v-loading="listLoading"
              :header-cell-style="{'text-align': 'center'}"
              :cell-style="{'text-align': 'center'}"
              size="mini"
              height="80%"
              style="width: 100%">
      <el-table-column prop="Name"
                       label="名称"
                       width="150">
      </el-table-column>
      <el-table-column prop="Num"
                       label="数量">
      </el-table-column>
      <el-table-column prop="X"
                       label="X坐标">
      </el-table-column>
      <el-table-column prop="Y"
                       label="Y坐标">
      </el-table-column>
      <el-table-column prop="Note"
                       label="备注">
      </el-table-column>
      <el-table-column label="操作"
                       align="center"
                       width="280">
        <!-- width="200" -->
        <template slot-scope="scope">
          <el-button type="primary"
                     icon="el-icon-edit"
                     size="mini">编辑</el-button>
          <el-button type="success"
                     icon="el-icon-more-outline"
                     size="mini">详情</el-button>
          <el-button type="danger"
                     icon="el-icon-delete"
                     size="mini">删除</el-button>
        </template>
      </el-table-column>
    </el-table>
    <!--分页-->
    <el-col :span="24">
      <el-pagination background
                     small
                     layout="total, slot,prev, pager, next,jumper"
                     @current-change="handleCurrentChange"
                     :total="total"
                     :page-size="pageSize"
                     style="float: right; height: 10%;">
        <span style="margin-right: 10px; font-weight: 400; color: #606266;">(每页10条)</span>
      </el-pagination>
    </el-col>
  </section>
</template>

<script>
import _ from 'lodash'
import { getData } from '../api/api'
import Add from './HandleDataManagement/Add.vue'
export default {
  data () {
    return {
      total: 0,
      page: 1,
      pageSize: 10,
      query: '',
      listLoading:false,
      activeIndex: 1,
      menuList: [],
      tableData: [],    //表格数据  
    }
  },
  watch: {
    query: {
      handler (val, oldval) {
        this.query = this.validSe_SQL(val)
        let old = this.validSe_SQL(oldval)
        if (this.query !== old) {
          this.handleQuery(this.query)
        }
      },
    },
  },
  methods: {
    //页数改变
    handleCurrentChange (val) {
      this.page = val
      this.getList()
    },
    //获取数据
    getList () {
      this.listLoading=true
      let para = {
        query: this.query,
        page: this.page,
        intPageSize: this.pageSize,
      }
      getData(para).then((res) => {
        let data = res.data.response
        this.tableData = data.data
        this.total = data.dataCount
        this.pageSize = data.PageSize
        this.listLoading=false
      })
    },
    //查询数据
    handleSearch(){
      this.getList()
    },
    //查询数据
     handleQuery: _.debounce(function (value) {
      this.handleSearch(value)
    }, 500),
    //显示新增页面
    handleAdd () {
      var that = this
      this.$layer.iframe({
        content: {
          content: Add,
          parent: this,
          data: {
            //传递的参数
            info: {
              layerid_p: this.layerid,
            },
          },
        },
        area: ['300px', '350px'],
        title: '新增',
        maxmin: true,
        shade: false,
        shadeClose: false,
        resize: true,
        yes: () => {
          //刷新表格数据
          that.getList()
        },
      })
    },
  },
  mounted () {
    this.getList()
  }
}
</script>

数据库的Data表里新增一个DateTime类型的CreateTime字段,然后在模型Data.cs里加上这个字段:

/// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

在DataController.cs里编写新增数据的方法:

[HttpPost]
        /// <summary>
        /// 新增数据
        /// </summary>
        public MessageModel Post([FromBody] Data request)
        {
           ;
            var data = new MessageModel();
            request.Id = Guid.NewGuid().ToString();
            request.CreateTime= DateTime.Now;
            var X="null";
            if (request.X != null) {
                X = request.X.ToString();
            }
            var Y = "null";
            if (request.Y != null)
            {
                Y = request.Y.ToString();
            }
            var Num = "null";
            if (request.Num != null)
            {
                Num = request.Num.ToString();
            }
            var Note = "null";
            if (request.Note != null)
            {
                Note ="'"+ request.Note+"'";
            }
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                var N = request.Note;
                string sltStr = "insert into Data values( '" + request.Id + "','" + request.Name + "'," + X + "," +Y + "," + Num + "," + Note+ ",'" +request.CreateTime + "')";//新增的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                int i = sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                if (i > 0)
                {
                    data.success = true;
                    data.msg = "新增成功";
                    data.response = request.Id;
                }
                return data;
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "新增失败",
                    success = false,
                };
            }
        }

现在DataController.cs文件就变成了这样:

using Newtonsoft.Json;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Text;
using System.Threading.Tasks;
using API.Models;
using System.Collections.Generic;
using System.Web;

namespace API.Controllers
{
    /// <summary>
    /// 菜单栏
    /// </summary>
    public class DataController : ApiController
    {

        /// <summary>
        /// 获取数据
        /// </summary>
        public MessageModel Get(int page = 1,int intPageSize=10,string query="")
        {
            string where = "1=1";
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                //查询条件
                if (!string.IsNullOrEmpty(query))
                {
                    where = where + " " + "and" + " " + "Name like '%" + query + "%'";
                }
                string sltStr = "select * from Data WHERE "+where+" order by CreateTime desc";//查询的sql语句,按创建时间排序(时间晚的在前面)
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                MySqlDataReader reader = sqlCmd.ExecuteReader();
                var jsonData = ToJson(reader);
                sqlCon.Close();
                var model = new PageModel<Data>();
                model.dataCount = jsonData.Count;
                model.data = jsonData.Skip(intPageSize * (page - 1)).Take(intPageSize).ToList();
                model.PageSize = intPageSize;
                model.page = page;
                if (model.data.Count == 0)
                {
                    model.pageCount = 0;
                }
                else
                {
                    model.pageCount = (int)Math.Ceiling(Convert.ToDouble(model.dataCount / model.data.Count));
                }
                return new MessageModel()
                {
                    msg = "获取成功",
                    success = true,
                    response = model
                };
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "获取失败",
                    success = false,
                };
            }

        }

        /// <summary>
        /// DataReader转换为数组数据
        /// </summary>
        public List<Data> ToJson(MySqlDataReader dataReader)
        {
            List<Data> list = new List<Data>();
            while (dataReader.Read())
            {
                var model = new Data();
                double?NULL = null;
                model.Id = dataReader[0].ToString();
                model.Name = dataReader[1].ToString() != "" ?dataReader[1].ToString():null;
                model.X =dataReader[2].ToString()!=""? Convert.ToDouble(dataReader[2].ToString()):NULL;
                model.Y = dataReader[3].ToString() != "" ? Convert.ToDouble(dataReader[3].ToString()) : NULL;
                model.Num = dataReader[4].ToString() != "" ? Convert.ToDouble(dataReader[4].ToString()) : NULL;
                model.Note = dataReader[5].ToString() != "" ? dataReader[5].ToString() : null;
                list.Add(model);
            }
            dataReader.Close();
            return list;
        }

        [HttpPost]
        /// <summary>
        /// 新增数据
        /// </summary>
        public MessageModel Post([FromBody] Data request)
        {
           ;
            var data = new MessageModel();
            request.Id = Guid.NewGuid().ToString();
            request.CreateTime= DateTime.Now;
            var X="null";
            if (request.X != null) {
                X = request.X.ToString();
            }
            var Y = "null";
            if (request.Y != null)
            {
                Y = request.Y.ToString();
            }
            var Num = "null";
            if (request.Num != null)
            {
                Num = request.Num.ToString();
            }
            var Note = "null";
            if (request.Note != null)
            {
               Note ="'"+ request.Note+"'"; 
            }
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                var N = request.Note;
                string sltStr = "insert into Data values( '" + request.Id + "','" + request.Name + "'," + X + "," +Y + "," + Num + "," + Note+ ",'" +request.CreateTime + "')";//新增的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                int i = sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                if (i > 0)
                {
                    data.success = true;
                    data.msg = "新增成功";
                    data.response = request.Id;
                }
                return data;
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "新增失败",
                    success = false,
                };
            }
        }

        /// <summary>
        /// Put请求
        /// </summary>
        public void Put(int id, [FromBody] string value)
        {
        }

        /// <summary>
        /// Delete请求
        /// </summary>
        public void Delete(int id)
        {
        }
    }
}

在api.js文件里加上这个新增数据的方法

//新增数据管理数据
//qs.stringify()将对象 序列化成URL的形式,以&进行拼接,来传递参数
export const addData = params => {
  return axios.post(`${base}/api/Data/post`, qs.stringify(params),{headers:{"Content-Type": "application/x-www-form-urlencoded;charset=utf-8"}} ) // 这里是跨域的写法
};

现在这个api.js文件就变成了:

import axios from 'axios'
import qs from 'Qs'
// 跨域相关
// url基础前缀
axios.defaults.baseURL="/api"
// post请求类型
//axios.defaults.headers.post['Content-Type']='application/json';

let base='https://localhost:44328'


//获取菜单栏数据
export const getMenu = params => {
  return axios.get(`${base}/api/Menu/Get`);
};

//获取Token
export const getToken = params => {
  return axios.get(`${base}/api/Userinfo/Get`,{ params: params });
};

//获取数据管理数据
export const getData = params => {
  return axios.get(`${base}/api/Data/Get`,{ params: params });
};
//新增数据管理数据
//qs.stringify()将对象 序列化成URL的形式,以&进行拼接,来传递参数
export const addData = params => {
  return axios.post(`${base}/api/Data/post`, qs.stringify(params),{headers:{"Content-Type": "application/x-www-form-urlencoded;charset=utf-8"}} ) // 这里是跨域的写法
};

5,详情

现在让我们来做详情页,在HandleDataManagement文件夹里新建Detail.vue文件,代码如下:

<template>
  <section class="container">
     <el-form :model="detailForm"
               label-width="80px"
               ref="detailForm"
               size="mini"
               class="form-box">
        <el-form-item label="名称"
                      class="form-item"
                      prop="Name">
          <el-tag  class="form-item">{{ detailForm.Name }}</el-tag>
        </el-form-item>
          <el-form-item label="X坐标"
                      class="form-item"
                      prop="X">
          <el-tag  class="form-item">{{ detailForm.X }}</el-tag>
        </el-form-item>
          <el-form-item label="Y坐标"
                      class="form-item"
                      prop="Y">
          <el-tag  class="form-item">{{ detailForm.Y }}</el-tag>
        </el-form-item>
         <el-form-item label="数值"
                      class="form-item"
                      prop="Num">
          <el-tag  class="form-item">{{ detailForm.Num }}</el-tag>
        </el-form-item>
         <el-form-item label="备注"
                      class="form-item"
                      prop="Note">
          <el-tag  class="form-item">{{ detailForm.Note }}</el-tag>
        </el-form-item>
      </el-form>  
  </section>
</template>

<script>
import {GetDataById} from '../../api/api'
export default {
  data () {
    return {
      layerid_p: null,  //父弹窗id
      layerid_c: this.layerid,  //弹窗id
      detailForm: {
        Name: '',
        X: '',
        Y: '',
        Num: '',
        Note: '',
      }
    }
  },
   props: {
    info: {
      type: Object,
      default: () => {
        return {}
      },
    },
    layerid: {
      type: String,
      default: '',
    },
    lyoption: {
      type: Object,
      default: () => {
        return {}
      },
    },
  },
  mounted(){
    // let Id=this.info.Id
    // //根据数据Id获取数据
    // GetDataById(Id).then((res)=>{
    //   this.detailForm=res.data.response.data[0]
    // })
    //直接使用传递来的数据
    this.detailForm=this.info.model
  }
}
</script>

<style lang="less" scoped>
.container{
  width: 100%;
}
.form-box {
  box-sizing: border-box;
  padding: 5px 15px;
  width: 100%;
  display: flex;
  justify-content: flex-start;
  align-content: center;
  flex-wrap: wrap;
}
.form-item {
  min-width: 150px;
  flex: 1;
  flex-basis: 150px;
}
</style>

然后在DataManagement.vue文件中,详情按钮修改为:

<el-button type="success"
                     icon="el-icon-more-outline"
                     @click="handleDetail(scope.$index, scope.row)"
                     size="mini">详情</el-button>

handleDetail方法如下:

//显示详情页面
    handleDetail(index,row){
      var that=this
      this.$layer.iframe({
        content: {
          content: Detail,
          parent: this,
          data: {
            //传递的参数
            info: {
              //Id:row.Id,
              model:row,
              layerid_p: this.layerid,
            },
          },
        },
        area: ['300px', '350px'],
        title: '详情',
        maxmin: true,
        shade: false,
        shadeClose: false,
        resize: true,
        yes: () => {
          //刷新表格数据
          that.getList()
        },
      })
    }

效果是这样的

在这里插入图片描述

6,编辑

下面我们来做编辑,在HandleDataManagement文件夹里新建edit.vue文件,代码如下:

<template>
  <section class="container">
    <el-form :model="editForm"
             label-width="80px"
             ref="detailForm"
             size="mini"
             class="form-box">
      <el-form-item label="名称"
                    class="form-item"
                    prop="Name">
        <el-input v-model="editForm.Name"
                  auto-complete="off"
                  class="form-item"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="X坐标"
                    class="form-item"
                    prop="X">
        <el-input v-model="editForm.X"
                  auto-complete="off"
                  class="form-item"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="Y坐标"
                    class="form-item"
                    prop="Y">
        <el-input v-model="editForm.Y"
                  auto-complete="off"
                  class="form-item"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="数值"
                    class="form-item"
                    prop="Num">
        <el-input v-model="editForm.Num"
                  auto-complete="off"
                  class="form-item"
                  size="mini"></el-input>
      </el-form-item>
      <el-form-item label="备注"
                    class="form-item"
                    prop="Note">
        <el-input v-model="editForm.Note"
                  auto-complete="off"
                  class="form-item"
                  size="mini"></el-input>
      </el-form-item>
    </el-form>
    <el-col :span="24"
            style="text-align:right">
      <el-button size="mini"
                 @click.native="handleCancelForm">取消</el-button>
      <el-button size="mini"
                 type="primary"
                 @click.native="editSubmit"
                 :loading="submitloading">提交</el-button>
    </el-col>
  </section>
</template>

<script>
import helper from '../../components/layer/helper/helper'
import { GetDataById,editData } from '../../api/api'
export default {
  data () {
    return {
      layerid_p: null,  //父弹窗id
      layerid_c: this.layerid,  //弹窗id
      submitloading:false,
      editForm: {
        Name: '',
        X: '',
        Y: '',
        Num: '',
        Note: '',
      }
    }
  },
  props: {
    info: {
      type: Object,
      default: () => {
        return {}
      },
    },
    layerid: {
      type: String,
      default: '',
    },
    lyoption: {
      type: Object,
      default: () => {
        return {}
      },
    },
  },
  methods: {
    //编辑取消
    handleCancelForm () {
      this.$layer.close(this.layerid_c);
    },
    //编辑提交
    editSubmit () {
      this.submitloading = true
      let form = Object.assign({}, this.editForm)
      //提交的数据至少有一个名称值
      if (form.Name == null) {
        this.$message({
          message: "名称不能为空",
          type: 'error',
        })
        this.submitloading = false
        return
      }
      editData(form).then((res) => {
        if (res.data.success) {
          this.$message({
            message: res.data.msg,
            type: 'success',
          })
          //刷新表格
          helper.btnyes(null, this.lyoption)
          //关闭弹窗
          this.$layer.close(this.layerid_c)
        }
        else {
          this.$message({
            message: res.data.msg,
            type: 'error',
          })
        }
      }).catch((e) => {
        this.$message({
          message: e.message,
          type: 'error',
        })
      }).finally(() => {
        this.submitloading = false
      })
    }
  },
  mounted () {
    // let Id=this.info.Id
    // //根据数据Id获取数据
    // GetDataById(Id).then((res)=>{
    //   this.detailForm=res.data.response.data[0]
    // })
    //直接使用传递来的数据
    this.editForm = this.info.model
  }
}
</script>

<style lang="less" scoped>
.container {
  width: 100%;
}
.form-box {
  box-sizing: border-box;
  padding: 5px 15px;
  width: 100%;
  display: flex;
  justify-content: flex-start;
  align-content: center;
  flex-wrap: wrap;
}
.form-item {
  min-width: 150px;
  flex: 1;
  flex-basis: 150px;
}
</style>

同样的,在DataManagement文件里,编辑按钮改为:

 <el-button type="primary"
                     icon="el-icon-edit"
                     @click="handleEdit(scope.$index, scope.row)"
                     size="mini">编辑</el-button>

引入这个编辑组件:

import Edit from './HandleDataManagement/Edit.vue'

handleEdit方法:

//显示编辑页面
    handleEdit(index,row){
      var that=this
      this.$layer.iframe({
        content: {
          content: Edit,
          parent: this,
          data: {
            //传递的参数
            info: {
              //Id:row.Id,
              model:row,
              layerid_p: this.layerid,
            },
          },
        },
        area: ['300px', '350px'],
        title: '编辑',
        maxmin: true,
        shade: false,
        shadeClose: false,
        resize: true,
        yes: () => {
          //刷新表格数据
          that.getList()
        },
      })
    },

api.js里新增代码:

//编辑数据管理数据
export const editData = params => {
  return axios.post(`${base}/api/Data/put`, qs.stringify(params),{headers:{"Content-Type": "application/x-www-form-urlencoded;charset=utf-8"}} ) // 这里是跨域的写法
};

后台的DataController.cs里文件里新增编辑方法:

/// <summary>
        /// 编辑数据
        /// </summary>
        [HttpPost]
        public MessageModel Put([FromBody] Data request)
        {
            ;
            var data = new MessageModel();
            request.UpdateTime = DateTime.Now;
            var X = "null";
            if (request.X != null)
            {
                X = request.X.ToString();
            }
            var Y = "null";
            if (request.Y != null)
            {
                Y = request.Y.ToString();
            }
            var Num = "null";
            if (request.Num != null)
            {
                Num = request.Num.ToString();
            }
            var Note = "null";
            if (request.Note != null)
            {
                Note ="'"+ request.Note+"'";
            }
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //建立DataAdapter对象  
                var N = request.Note;
                string sltStr = "update data set Name='"+request.Name+"',X="+X+",Y="+Y+",Num="+Num+",Note="+Note+ ",UpdateTime='" + request.UpdateTime + "' where Id='"+request.Id+"'";//编辑的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                int i = sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                if (i > 0)
                {
                    data.success = true;
                    data.msg = "更新成功";
                    data.response = request.Id;
                }
                return data;
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "更新失败",
                    success = false,
                };
            }
        }

数据库data表里新增DateTime类型字段UpdateTime,相应的,模型Data.cs里加上

/// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? UpdateTime { get; set; }

现在编辑一个看看,

在这里插入图片描述

提交,显示更新成功,

在这里插入图片描述

7,删除

最后只剩一个删除了,同样的,修改删除按钮:

<el-button type="danger"
                     icon="el-icon-delete"
                     @click="handleDelete(scope.$index,scope.row)"
                     size="mini">删除</el-button>

添加handleDelete方法:

//删除数据
    handleDelete (index, row) {
      let Id = row.Id
      this.$confirm('是否删除数据?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'waring'
      }).then(() => {
        DeleteDataById(Id).then((res) => {
          if (res.data.success) {
            this.$message({
              message: res.data.msg,
              type: 'success',
            })
            //刷新表格
            this.getList()
          }
          else {
            this.$message({
              message: res.data.msg,
              type: 'error',
            })
          }
        }).catch((e) => {
          this.$message({
            message: e.message,
            type: 'error',
          })
        }).finally(() => {
          this.submitloading = false
        })
      }).catch(() => {
        this.$message({
          typeA: 'info',
          message: '已取消操作'
        })
      })
    }

根据Id删除所需数据,后台的DataController.cs里添加:

/// <summary>
        /// 根据Id删除数据
        /// </summary>
        [HttpGet]
        public MessageModel DeleteById(string Id="")
        {
            var data = new MessageModel();
            try
            {
                //数据库连接信息
                string sqlCconnStr = "server=localhost;port=3306;user=xxx;password=xxx; database=xxx;charset=utf8";
                //连接数据库 
                MySqlConnection sqlCon = new MySqlConnection(sqlCconnStr);
                //建立DataSet对象(相当于建立前台的虚拟数据库)
                DataSet ds = new DataSet();
                //打开连接
                sqlCon.Open();
                //删除多条数据:"delete from tablename where Id in(1,2,3,5)"
                string sltStr = "delete from data where Id='" + Id + "'";//删除一条数据的sql语句
                MySqlCommand sqlCmd = new MySqlCommand(sltStr, sqlCon);
                int i = sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
                if (i > 0)
                {
                    data.success = true;
                    data.msg = "删除成功";
                    data.response = Id;
                }
                return data;
            }
            catch (Exception ex)
            {
                return new MessageModel()
                {
                    msg = "删除失败",
                    success = false,
                };
            }
        }

在api.js里添加:

//删除数据管理数据
export const DeleteDataById = id => {
  return axios.get(`${base}/api/Data/DeleteById/${id}`);
}

最后在DataManagement.vue里引用:

import { getData, DeleteDataById } from '../api/api'

现在,增删改查就做完了,我们这一篇到这里也要结束了。想到我们第一篇中创建了菜单栏和基本结构,相当于有了骨架;在第二篇加上这一篇中,数据可以增删改查了,相当于有了血肉;数据库就是我们的心脏;后台接口就是我们的大脑;后面我们要要完成未完成的菜单栏功能,相当于在骨架里添加器官;然后完善系统让它正规化。让我们一步步来,一点点进步,下篇再见。

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐