从零开始的VUE项目-03(数据的增删改查)
这一篇中,我们要实现的基本就是这么个东西这个页面就是我们创建的DataManagement.vue页面,我们的主要代码将写在这里面,这个表格我们将用Element-UI的table组件来实现组件地址:https://element.eleme.cn/#/zh-CN/component/table我们先用这个基础表格看看效果,点击表格下面的显示代码,我们把这些代码复制粘贴到我们的DataManage
代码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'
现在,增删改查就做完了,我们这一篇到这里也要结束了。想到我们第一篇中创建了菜单栏和基本结构,相当于有了骨架;在第二篇加上这一篇中,数据可以增删改查了,相当于有了血肉;数据库就是我们的心脏;后台接口就是我们的大脑;后面我们要要完成未完成的菜单栏功能,相当于在骨架里添加器官;然后完善系统让它正规化。让我们一步步来,一点点进步,下篇再见。
更多推荐
所有评论(0)