本文依据CSDN另一位网友关于三层构架的简单搭建，基于他的源码进行修改。实现了三层构架合理结构，以及从数据库中传递数值在echarts显示的实验目的。
废话不多说，show me codes：
具体构建方式：点击打开链接
1.MODEL层代码：
依据数据库中定义的表格结构，构造实体类中的各个属性值。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MODEL
{
public class Users
{
private string Sname;//字段类型和字段名要与数据库对应
public string Sname1//封装后的字段
{
get { return Sname; }
set { Sname = value; }
}
private string Ssex;
public string Ssex1
{
get { return Ssex; }
set { Ssex = value; }
}
private string Snumber;
public string Snumber1
{
get { return Snumber; }
set { Snumber = value; }
}
private int Sgrade;
public int Sgrade1
{
get { return Sgrade; }
set { Sgrade = value; }
}
private string Steacher;
public string Steacher1
{
get { return Steacher; }
set { Steacher = value; }
}
private int Sid;
public int Sid1
{
get { return Sid; }
set { Sid = value; }
}
}
}
2.DAL层：
主要对数据库数据进行处理。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MODEL;
using System.Reflection;
namespace DAL
{
public class UserService
{
//连接数据库
public static SqlConnection connection;
public static SqlConnection Connection
{
get
{
if (connection == null)
{
//远程连接数据库命令（前提远程数据库服务器已经配置好允许远程连接）
//string strConn = @"Data Source=172.18.72.158;Initial Catalog=WebKuangjia;User ID=sa;Password=LIwei123;Persist Security Info=True";
//连接本地数据库命令
string strConn = @"Data Source=.;Initial Catalog=SanCengDemo;Integrated Security=True";
connection = new SqlConnection(strConn);
connection.Open();
}
else if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
//执行sql语句,返回被修改行数
public static int ExecuteCommand(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
connection.Close();
cmd.Dispose();
}
}
//执行sql语句,返回数据库表
public static DataTable GetDataTable(string commandText, CommandType commandType, SqlParameter[] para)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = commandText;
cmd.CommandType = commandType;
try
{
if (para != null)
{
cmd.Parameters.AddRange(para);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable temp = new DataTable();
da.Fill(temp);
return temp;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
connection.Close();
cmd.Dispose();
}
}
//增加用户
public static bool AddStudent(Users user)
{
string sql = "insert into Student(Sname,Ssex,Snumber,Sgrade,Steacher)" + "values(@name,@sex,@number,@grade,@teacher)";//sql语句字符串
if (user.Sname1 == null)
{
user.Sname1 = "";
}
if (user.Ssex1 == null)
{
user.Ssex1 = "";
}
if (user.Snumber1 == null)
{
user.Snumber1 = "";
}
if (user.Steacher1 == null)
{
user.Steacher1 = "";
}
SqlParameter[] para = new SqlParameter[]//存储相应参数的容器
{
new SqlParameter("@name",user.Sname1),
new SqlParameter("@sex",user.Ssex1),
new SqlParameter("@number",user.Snumber1),
new SqlParameter("@grade",user.Sgrade1),
new SqlParameter("@teacher",user.Steacher1),
};
int count = ExecuteCommand(sql, CommandType.Text, para);//调用执行sql语句函数
if (count > 0)
{
return true;
}
else
{
return false;
}
}
//查询数据库表
public static DataTable Selecttable()
{
string sql = "select * from Student";
return GetDataTable(sql, CommandType.Text, null);
}
//test for echarts
public static DataTable returntable()
{
string sql = "select Sgrade,Sname from Student";
return GetDataTable(sql, CommandType.Text, null);
}
//删除用户
/****************删除用户返回影响行数*****************/
public static bool DeleteStudentBySnumber(string number)
{
string sql = "delete from Student where Snumber=@number";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@number",number),
};
int count = ExecuteCommand(sql, CommandType.Text, para);
if (count > 0)
{
return true;
}
else
{
return false;
}
}
/****************删除用户返回表*****************/
public static DataTable DeleteStudentBySid(int id)
{
string sql = "delete from Student where Sid=@id";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@id",id),
};
return GetDataTable(sql, CommandType.Text, para);
}
//修改用户
public static bool ModifyStudent(Users user)
{
string sql = "update Student set Sname=@name,Ssex=@sex,Snumber=@number,Sgrade=@grade,Steacher=@teacher where Sid=@id";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@name",user.Sname1),
new SqlParameter("@sex",user.Ssex1),
new SqlParameter("@number",user.Snumber1),
new SqlParameter("@grade",user.Sgrade1),
new SqlParameter("@teacher",user.Steacher1),
new SqlParameter("@id",user.Sid1),
};
int count = ExecuteCommand(sql, CommandType.Text, para);
if (count > 0)
{
return true;
}
else
{
return false;
}
}
//查询用户
public static bool QueryStudent(string number)
{
string sql = "select * from Student where Snumber=@number";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@number",number),
};
int count = ExecuteCommand(sql, CommandType.Text, para);
if (count > 0)
{
return true;
}
else
{
return false;
}
}
}
}
3.BLL层代码：
主要用于参数传递和DAL中的函数调用。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
using System.Data.SqlClient;
using MODEL;
namespace BLL
{
public class UserManage
{
public static bool add(Users user)
{
return UserService.AddStudent(user);
}
public static bool delete(string number)
{
return UserService.DeleteStudentBySnumber(number);
}
public static bool xiugai(string number)
{
return UserService.QueryStudent(number);
}
public static bool modify(Users user)
{
return UserService.ModifyStudent(user);
}
public static bool select(string number)
{
return UserService.QueryStudent(number);
}
public static DataTable table()
{
return UserService.Selecttable();
}
public static DataTable deletebyid(int id)
{
return UserService.DeleteStudentBySid(id);
}
public static DataTable returntable()
{
return UserService.returntable();
}
}
}
echarts前台显示部分：
实现对后台数据的异步读取和实时刷新（根据setIntervar()方法中的秒数，来实现实时性)
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<script src="Scripts/jquery-3.3.1.min.js"></script>
<script src="Scripts/echarts.js"></script>
<script src="Scripts/macarons.js"></script>
<title></title>
</head>
<body>
<div id="main" style=" 600px;height:400px;"></div>
<script type="text/javascript">
var mychart = echarts.init(document.getElementById('main'), 'macarons');
mychart.setOption({
title: {
text: '异步加载数据示例'
},
tooltip: {},
legend: {
data: ['班级']
},
xAxis: {
data: []
},
yAxis: {},
series: [{
name: 'Sname',
type: 'bar',
data: []
}]
});
mychart.showLoading();
var sname =[]; //sname数组（实际用来盛放X轴坐标值）
var sgrade = []; //sgrade数组（实际用来盛放Y坐标值）
getdata();
function getdata() {
var sname = []; //类别数组（实际用来盛放X轴坐标值）
var sgrade = []; //销量数组（实际用来盛放Y坐标值）</span>
$.ajax({
type: "post",
async: true, //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）
url: "Default.aspx?method=getdata",
data: {},
dataType: "json", //返回数据形式为json
success: function (result) {
//请求成功时执行该函数内容，result即为服务器返回的json对象
if (result) {
for (var i = 0; i < result.length; i++) {
sname.push(result[i].Sname); //挨个取出类别并填入类别数组
}
for (var i = 0; i < result.length; i++) {
sgrade.push(result[i].Sgrade); //挨个取出销量并填入销量数组
}
mychart.hideLoading(); //隐藏加载动画
mychart.setOption({ //加载数据图表
xAxis: {
data: sname
},
series: [{
// 根据名字对应到相应的系列
name: '班级',
data: sgrade
}]
});
}
},
error: function (errorMsg) {
//请求失败时执行该函数
alert("图表请求数据失败!");
myChart.hideLoading();
}
})
}
setInterval(getdata, 20000);
</script>
</body>
</html>
显示结果：
注：原博客用的gridview来实现对数据的显示，增删改操作，本人进行适当该写，增加了分页功能，以及增加按钮。
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register assembly="DevExpress.Xpo.v14.1.Web, Version=14.1.4.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Xpo" tagprefix="dx" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1" GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnSelectedIndexChanging="GridView1_SelectedIndexChanging" AutoGenerateColumns="False" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="5">
<Columns>
<asp:BoundField DataField="Sid" HeaderText="Sid" />
<asp:BoundField DataField="Sname" HeaderText="Sname" />
<asp:BoundField DataField="Ssex" HeaderText="Ssex" />
<asp:BoundField DataField="Sgrade" HeaderText="Sgrade" />
<asp:BoundField DataField="Snumber" HeaderText="Snumber" />
<asp:BoundField DataField="Steacher" HeaderText="Steacher" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowSelectButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#594B9C" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#33276A" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="新增" />
</div>
</form>
</body>
</html>Default.aspx后台代码：using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using DAL;
using MODEL;
using BLL;
using Newtonsoft.Json;
public partial class _Default : System.Web.UI.Page
{
List<object> lists = new List<object>();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)//初次加载该页
{
gvbind();
}
string method = Request.QueryString["method"];
if (!string.IsNullOrEmpty(method))
{
if (method == "getdata")
{
data();
}
}
}
private void data()
{
DataTable dt = new DataTable();
dt = UserManage. returntable();
// lists = new List<object>();
// lists = UserManage.returnlist();
object JSONObj = (Object)JsonConvert.SerializeObject(dt);
Response.Write(JSONObj);
// 一定要加，不然前端接收失败
Response.End();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
// string number = GridView1.Rows[e.RowIndex].Cells[3].Text;
// UserManage.delete(number);
//// GridView1.DataBind();
int id = int.Parse(GridView1.Rows[e.RowIndex].Cells[0].Text);
UserManage.deletebyid(id);
gvbind();
}
protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
GridView1.SelectedIndex = e.NewSelectedIndex;
//GridView1.DataBind();
gvbind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
// GridView1.DataBind();
gvbind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Users us = new Users();
us.Sid1 = int.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());//获取编辑行的数据主键值//((TextBox)GridView1.Rows[e.RowIndex].Cells[0].Controls[0]).Text;//[0].Controls[0]).Text;
us.Sname1 = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text.ToString().Trim();//获取编辑行的第1列的textbox控件中的内容赋值给Users对象us的Sname1封装字段
us.Ssex1 = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text.ToString().Trim();
us.Snumber1 = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text.ToString().Trim();
us.Sgrade1 = int.Parse(((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text.ToString().Trim());
us.Steacher1 = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text.ToString().Trim();
if (UserManage.modify(us))
{
GridView1.EditIndex = -1;
//GridView1.DataBind();
gvbind();
}
else
{
Response.Write("<script>alert('修改失败!')</script>");
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
// GridView1.DataBind();
gvbind();
}
private void gvbind()
{
DataTable list;//声明表变量
list = UserManage.table();
GridView1.DataSource = list;
GridView1.DataKeyNames = new string[] { "Sid" };//主键
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
Users user1 = new Users();
UserManage.add(user1);
gvbind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
gvbind();
}
}
实现效果：
