Volleria 个人博客
0%

基于Unity和MySQL的数据库课设

发表于 更新于 分类于

(前排提示:为了凑字数导致内容较水)

摘要

本实验采用的数据库为MySQL数据库，其具有体积小、速度快等优点。本实验的GUI部分采用Unity3D引擎制作，通过编写C#脚本挂载到Unity引擎中，实现了Unity与MySQL之间的连接。在完成基础实验一至五的前提上，依照要求实现了一个小型书籍信息管理系统 ，具有增删改查等基本功能，同时还可以根据一定要求进行排序，并且创建了两个图表对数据信息进行汇总，便于后续分析。

关键词

MySQL、Unity、、C#、书籍信息管理

一、实验要求

  • 理解 SQL 定义功能
  • 熟练掌握 SQL 操纵功能
  • 了解 SQL 数据控制功能。
  • 熟练掌握 Oracle、SQL Server、MySQL、DB2、Sybase 或 PostgreSQL 等对数据库的管理和操作，可以采用华为 OpenGauss 数据库。熟练掌握 Visual C++(MFC)、C#、 Qt、Java、PHP 或 Python 等访问数据库的方法，编写学生通讯录或学生选课（不建议）或其他类似的一个小型管理信息系统。

【关于小型管理信息系统】

熟练掌握 Visual C++、C#、Qt、Java、PHP 或 Python 等访问数据库的方法， 设计和实现学生通讯录或学生选课或类似的一个小型管理信息系统。要求具有数 据的增加、删除、修改和查询的基本功能，并尽可能提供较多的查询功能，集成 一半以上实验一～实验五的功能，用户界面要友好。可选内容（加分项）：数据库中存放 100 万条记录，测试访问时间；如效率较低，提供优化方案。

二、实验依赖

1、MySQL

Ⅰ、MySQL 特点

  • 功能强大
  • 支持跨平台
  • 运行速度快
  • 支持面向对象
  • 安全性高
  • 成本低
  • 支持各种开发语言
  • 数据库存储容量大
  • 支持强大的内置函数

Ⅱ、MySQL版本

本实验采用 MySQL 8.0版本

安装地址：https://dev.mysql.com/downloads/installer/

安装包：

image-20220628001737326

2、Unity

Unity 是实时3D互动内容创作和运营平台 。包括游戏开发、美术、建筑、汽车设计、影视在内的所有创作者，借助Unity将创意变成现实。Unity平台提供一整套完善的软件解决方案，可用于创作、运营和变现任何实时互动的2D和3D内容，支持平台包括手机、平板电脑、PC、游戏主机、增强现实和虚拟现实设备。

本实验采用Unity制作GUI界面 ，全部代码均在Unity中通过C#编写，

所用Unity编辑器的版本为 2021.2.7f1c1，渲染管线为 Built-in 渲染管线，新建项目类型为普通3D项目，所以代码编辑器为 Visual Studio 2019。

3、相关参考资料

MySQL的安装：https://blog.csdn.net/qq_59636442/article/details/123058454

PHP与MySQL的连接：http://c.biancheng.net/view/7668.html

Unity连接MySQL：

https://blog.csdn.net/Caijingxiang/article/details/107325645

https://zhuanlan.zhihu.com/p/508391104

三、数据部分

1、数据来源

本实验完成了一个书籍信息管理系统，其中书籍和作者信息为在豆瓣读书网页中爬取所得

【方法】

用python编写网络爬虫

（1）设置url
1
url = "https://book.douban.com/subject/35635639"

后面的数字 35635639 为豆瓣数据库中对每个书籍的编号

通过改变数字来获取书籍信息

（2）建立请求，获取源代码
1
r = requests.get(url, headers={'user-agent': 'Mozilla/5.0'})

注意：必须在 url 后面加上 headers ，否则请求会被拒绝。

（3）根据网页源代码，构建查匹配对应数据所需的正则表达式
1
2
3
pattern1 = re.compile('<meta name="keywords" content="(.*?)">') # 基本信息
pattern2 = re.compile('<strong class="ll rating_num " property="v:average"> (.*?) </strong>') # 豆瓣评分
pettern3 = re.compile('<a class="" href="/search/%E9%87%91%E5%86%89">(.*?)</a>') # 译者，有的书此处为空
（4）进行匹配查询
1
2
3
data = re.findall(pattern1, r.text)
star = re.findall(pattern2, r.text)
translate = re.findall(pettern3, r.text)
（5）字符串连接
1
2
str = 'insert into books values ("' + s[0] + '","' + s[1] + '","' + s[2] + '","' + s[3] + '",' + ' "无" ,"' + star[
    0] + '" , "未借出");'

将上述爬取到的信息连接起来，形成一条具有插入功能的MySQL语句，方便后续运行。

【输出案例】

1
insert into books values ("法治的细节","罗翔","云南人民出版社","2021-11", "无" ,"8.4" , "未借出");

【一些问题】

豆瓣对书籍及作者信息有时收集的并不完全，导致爬取到的数据很少有能用的。

下图是一个完整的书籍信息，书籍编号为 35635639

image-20220628003009895

下图书籍缺少豆瓣评分信息，书籍编号为 35635619

image-20220628003116521

【解决方法】

在大批量爬取数据后，手动删除无用信息，留下具有完整信息的书籍，将其写入到数据库中。

2、详细数据

（1）书籍信息

书籍信息表 books，具有以下7个属性

  • 书名 （主属性）
  • 作者
  • 出版社
  • 出版时间
  • 译者 （可无）
  • 豆瓣评分
  • 借阅状态

使用如下sql语句创建

1
2
3
4
5
6
7
8
9
10
11
create table books
(
    bookname CHAR(40) Primary key,
    author CHAR(40) ,
    producer CHAR(40),
    producetime CHAR(40),
    translater CHAR(40) ,
    star CHAR(10),
    borrowstate CHAR(20),
    foreign key(auther) references authors(authorname)
);

在MySQL中存储如下 （仅部分数据）

image-20220628003930333

（2）作者信息

作者信息表 authors ，具有如下 5个属性

  • 作者姓名
  • 作者性别
  • 出生日期
  • 出生地
  • 国籍

使用如下 sql 语句创建

1
2
3
4
5
6
7
8
create table authers
(
    auther CHAR(40) Primary key,
    sex CHAR(40) ,
    birthtime CHAR(40),
    birtharea CHAR(40),
    country CHAR(10) ,
);

在MySQL中存储如下 （仅部分数据）

image-20220628004555717

（3）学生信息

学生信息表 students ，具有如下 5个属性

  • 学生姓名
  • 学号
  • 性别
  • 系别
  • 借阅书籍

使用如下 sql 语句创建

1
2
3
4
5
6
7
8
9
create table students
(
    name CHAR(20),
    Sno CHAR(10),
    Sex CHAR(10),
    Sdept CHAR(10),
    book CHAR(20),
    FOREIGN KEY (book) REFERENCES books(bookname),
);

在MySQL中存储如下 （仅部分数据）

image-20220628004942659

3、数据 E-R 图

image-20220628010920001

四、具体实验流程

1、建立 Unity 与 MySQL 之间的连接

创建 C# 脚本 SqlAccess.cs ，完整代码如下，具体结束见注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
using System;
using System.Data;
using MySql.Data.MySqlClient;
using UnityEngine;
using System.Text;


public class SqlAccess
{
    public static MySqlConnection dbConnection;
    //如果只是在本地的话，写localhost就可以。
    // static string host = "localhost";  
    //如果是局域网，那么写上本机的局域网IP
    static string host = "localhost";
    static string port = "3306";
    static string username = "root";
    static string pwd = "HUNXIGUILAI2019";
    static string database = "ekkodb";
    

    public SqlAccess()
    {
        OpenSql();
    }

	/// <summary>
	/// 连接数据库
	/// </summary>
	public static void OpenSql()
	{
	    try
	    {
	        string connectionString = string.Format("server = {0};port={1};database = {2};user = {3};password = {4};charset=utf8", host, port, database, username, pwd);
	        Debug.Log("connectionString:"+ connectionString);
	        dbConnection = new MySqlConnection(connectionString);
	        dbConnection.Open();
	        Debug.Log("建立连接");
	    }
	    catch (Exception e)
	    {
	        throw new Exception("服务器连接失败,请重新检查是否打开MySql服务。" + e.Message.ToString());
	    }
	}
	
	/// <summary>
	/// 关闭数据库连接
	/// </summary>

	public void Close()
	{
	    if (dbConnection != null)
	    {
	        dbConnection.Close();
	        dbConnection.Dispose();
	        dbConnection = null;
	    }
	}
	
	// 增加操作
	public DataSet Additem(string tablename,string[] datavalues)
	{
		StringBuilder query = new StringBuilder();
		query.Append("INSERT into ");
		query.Append(tablename);
		query.Append(" VALUES (");
		query.Append("'");
		query.Append(datavalues[0]);
		query.Append("'");
		for(int i = 1;i <datavalues.Length;i++)
		{
			// 判断类型
			query.Append(",'");
			query.Append(datavalues[i]);
			query.Append("'");
		}
		query.Append(");");
		Debug.Log("添加数据:" + query.ToString());
	    return ExecuteQuery(query.ToString());

	}

	// 查询操作
	public DataSet SelectWhere(string tableName, string[] items, string bookname)
	{
	    StringBuilder query = new StringBuilder();
	    query.Append("SELECT ");
	    query.Append(items[0]);
	
	    for (int i = 1; i < items.Length; ++i)
	    {
	        query.Append(", ");
	        query.Append(items[i]);
	    }
	
	    query.Append(" FROM ");
	    query.Append(tableName);

		if( bookname != null)
        {
			query.Append(" WHERE ");
			query.Append("bookname = ");
			query.Append("'");
			query.Append(bookname);
			query.Append("'");
			query.Append(";");
		}
	    Debug.Log("查询数据:" + query.ToString());
	    return ExecuteQuery(query.ToString());
	}
	
	// 删除操作
	public DataSet Delete(string tablename,string bookname)
    {
		// delete from books where bookname = "浮木";
		StringBuilder query = new StringBuilder();
		query.Append("DELETE FROM ");
		query.Append(tablename);
		query.Append(" WHERE bookname = ");
		query.Append("'");
		query.Append(bookname);
		query.Append("'");
		query.Append(";");
		Debug.Log("删除数据:" + query.ToString());
		return ExecuteQuery(query.ToString());

	}

	// 执行 Sql 语句
	public static DataSet ExecuteQuery(string sqlString)
	{
	    if (dbConnection.State == ConnectionState.Open)
	    {
	        DataSet ds = new DataSet();
	        try
	        {
	            MySqlDataAdapter da = new MySqlDataAdapter(sqlString, dbConnection);
	            da.Fill(ds);
	        }
	        catch (Exception ee)
	        {
	            throw new Exception("SQL:" + sqlString + "/n" + ee.Message.ToString());
	        }
	        finally
	        {
	        }
	        return ds;
	    }
	    return null;
	}
}

【踩坑记录】

1、连接问题

image-20220606171815558

1
Exception: 服务器连接失败，请重新检查是否打开MySql服务。The given key 'utf8mb4' was not present in the dictionary.

解决办法：

1
2
3
// 在string末尾添加 “charset=utf8”

string connectionString = string.Format("server = {0};port={1};database = {2};user = {3};password = {4};charset=utf8", host, port, database, username, pwd);
2、查询问题
问题1
1
Exception: SQL:select cno, cname, cpno, ccredit from c/nThe given key 'utf8mb4' was not present in the dictionary.

image-20220606171952042

主要原因：

MySql.Data 的版本过低（此版本为5.2.7）

问题2
1
Exception: 服务器连接失败，请重新检查是否打开MySql服务。Authentication failed, see inner exception.

image-20220606172208966

主要原因：

MySql.Data 的版本过高（此版本为6.9.9）

解决方法：

以上问题都是由于 MySql.Data.dll 的版本问题导致的

我所用的Unity版本为 2021.2.7f1c1

不同的 MySql.Data.dll 的下载地址： https://downloads.mysql.com/archives/c-net/

经过多次实验

image-20220606172506541

找到的合适的 MySql.Data.dll 的版本为 6.1.6

成功建立连接并实现查询

1
SELECT Cno, Cname, Cpno, Ccredit FROM C

image-20220606172751991

image-20220606172726928

2、登陆界面

Ⅰ、界面绘制及功能介绍

image-20220628011720929

用户名 与 密码 为两个 Input Field 组件，可以获取用户输入

用户输入 正确的用户名和密码后可登入数据库，若用户名或密码错误，会出现警告

image-20220628011958371

Ⅱ、功能实现

（1）引入所需命名空间
1
2
3
4
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
（2）声明所需的各个变量
1
2
3
4
5
6
7
8
9
public GameObject cuowu;
public InputField zhanghao;
public InputField mima;
public Button queding_btn;

public GameObject maincanvas;

string zhanghaostr ;
string mimastr ;
（3）在启动时进行绑定与初始化设置
1
2
3
4
5
6
7
void Start()
{
    cuowu.SetActive(false);
    maincanvas.SetActive(false);
    queding_btn.onClick.AddListener(log);
    mima.contentType = InputField.ContentType.Password;
}
（4）按钮触发的登陆操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
void log()
{
    string myzh = zhanghao.text;
    string mymima = mima.text;

    if( string.Compare(zhanghaostr,myzh)== 0 && string.Compare(mimastr,mymima) == 0 )
    {
        transform.gameObject.SetActive(false);
        maincanvas.gameObject.SetActive(true);
    }
    else
    {
        cuowu.SetActive(true);
    }
}

3、主界面

Ⅰ、界面绘制及功能介绍

image-20220628012514967

主界面一共有四个按钮事件，通过点击各个按钮显示对应的界面，左上角 × 为关闭整个应用程序，在程序打包后生效。

Ⅱ、功能实现

（1）引入命名空间
1
2
3
4
5
6
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using XCharts.Runtime;
using System.Data;
（2）初始化所需的各个变量及组件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public Button sbtn;
public Button abtn;
public Button alterbtn;
public Button showbtn;
public Button quitbnt;

public Canvas selcanva;
public Canvas addcanva;
public Canvas altercanva;
public Canvas showchartcanva;

public PieChart chart;
public BarChart barchart;
private Serie serie;
ArrayList stars = new ArrayList();

public GameObject tablecanvas;
public GameObject table_prefab;
private GameObject mytable;
（3）对按钮、Canvas等组件进行初始化及绑定操作
1
2
3
4
5
6
7
8
9
10
11
12
13
void Start()
{
    selcanva.gameObject.SetActive(false);
    addcanva.gameObject.SetActive(false);
    altercanva.gameObject.SetActive(false);
    showchartcanva.gameObject.SetActive(false);

    sbtn.onClick.AddListener(showtable );
    abtn.onClick.AddListener(delegate { showandclose(addcanva); });
    alterbtn.onClick.AddListener(delegate { showandclose(altercanva); });
    showbtn.onClick.AddListener(xiugai);
    quitbnt.onClick.AddListener(tuchu);
}
（4）关闭/显示 功能
1
2
3
4
5
6
7
8
9
void showandclose(Canvas thiscanva)
{
    selcanva.gameObject.SetActive(false);
    addcanva.gameObject.SetActive(false);
    altercanva.gameObject.SetActive(false);
    showchartcanva.gameObject.SetActive(false);

    thiscanva.gameObject.SetActive(true); 
}
（5）退出应用程序
1
2
3
4
void tuchu()
{
    Application.Quit();
}
（6）对第四部分的chart图表进行更新修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
void xiugai()
{
    int a = 0;
    int b = 0;
    int c = 0;

    selcanva.gameObject.SetActive(false);
    addcanva.gameObject.SetActive(false);
    altercanva.gameObject.SetActive(false);
    showchartcanva.gameObject.SetActive(true);

    SqlAccess sql = new SqlAccess();
    string[] items = { "star" };
    string tablename = "books";
    string bookname = null;
    DataSet ds = sql.SelectWhere(tablename, items, bookname);

    if (ds != null)
    {
        DataTable table = ds.Tables[0];
        foreach (DataRow row in table.Rows)
        {
            string str = "";
            foreach (DataColumn column in table.Columns)
            {
                str += row[column];
                float num = float.Parse(str);
                if (num >= 9.0f)
                    a++;
                else if (num <= 8.0f)
                    c++;
                else
                    b++;
                //stars.Add(float.Parse(str));
            }
        }
    }
    chart.UpdateData(0, 0, a);
    chart.UpdateData(0, 1, b);
    chart.UpdateData(0, 2, c);

    var yAxis = barchart.GetChartComponent<YAxis>();
    yAxis.minMaxType = Axis.AxisMinMaxType.Default;

    barchart.RemoveData();
    serie = barchart.AddSerie<Bar>("Star");

    barchart.AddXAxisData("小于8.0");
    barchart.AddData(0, c);
    barchart.AddXAxisData("8.0-9.0");
    barchart.AddData(0, b);
    barchart.AddXAxisData("大于9.0" );
    barchart.AddData(0, a);

}
（7）通过预制体的不断删除实例化来更新

通过调用此函数解决了更新脚本只执行一次的bug

1
2
3
4
5
6
7
8
9
10
11
void showtable()
{
    Destroy(tablecanvas.transform.GetChild(0).gameObject);
    mytable = Instantiate(table_prefab);
    mytable.transform.SetParent(tablecanvas.transform, false);

    selcanva.gameObject.SetActive(true);
    addcanva.gameObject.SetActive(false);
    altercanva.gameObject.SetActive(false);
    showchartcanva.gameObject.SetActive(false);
}

4、选择查看界面

Ⅰ、界面绘制及功能介绍

image-20220628013418735

此处程序尚未执行，表中全部为空

【运行程序】

点击 “书籍信息表”

image-20220628013524407

点击 “作者信息表”

image-20220628013558654

点击 “学生信息表”

image-20220628013619545

此外，表中增加了排序功能

点击 “豆瓣评分” 表头，可以根据豆瓣评分高低进行升序排序

image-20220628013745939

点击 “豆瓣评分” 表头，可以根据豆瓣评分高低进行升序排序

image-20220628013823061

Ⅱ、功能实现

创建 Select.cs 脚本
（1）引入命名空间
1
2
3
4
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
（2）初始化变量及组件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public Button shubtn;
public Button autherbtn;
public Button studentbtn;

public GameObject shutable;
public GameObject authertable;
public GameObject studenttable;

public GameObject tablecanvas;
public GameObject tablecanvas1;
public GameObject table_prefab;
public GameObject table_prefab1;

private GameObject mytable;
private GameObject mytable1;

public GameObject tishi;
（3）变量初始化及按钮绑定
1
2
3
4
5
6
void Start()
{
    shubtn.onClick.AddListener(showshu);
    autherbtn.onClick.AddListener(showa);
    studentbtn.onClick.AddListener(shows);      
}
（4）为各个按钮分配功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
void showshu()
{
    shutable.SetActive(true);
    authertable.SetActive(false);
    studenttable.SetActive(false);
    tishi.SetActive(true);
}

void showa()
{
    Destroy(tablecanvas.transform.GetChild(0).gameObject);
    mytable = Instantiate(table_prefab);
    mytable.transform.SetParent(tablecanvas.transform, false);

    shutable.SetActive(false);
    studenttable.SetActive(false);
    authertable.SetActive(true);
    tishi.SetActive(false);
}
void shows()
{
    Destroy(tablecanvas1.transform.GetChild(0).gameObject);
    mytable1 = Instantiate(table_prefab1);
    mytable1.transform.SetParent(tablecanvas1.transform, false);
    mytable1.gameObject.SetActive(true);

    shutable.SetActive(false);
    authertable.SetActive(false);
    studenttable.SetActive(true);
}
为书籍信息表创建 example.cs 脚本
（1）引入命名空间
1
2
3
4
5
6
7
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using WDT;
using System.Data;
using System;
（2）初始化变量及组件
1
2
3
4
5
6
public WDataTable dataTable;
private List<IList<object>> m_datas = null;
private List<WColumnDef> m_columnDefs = null;
private int m_tempSelectIndex = -1;
ArrayList stars = new ArrayList();
int row_n;
（3）对表格进行赋值初始化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
void Start()
    {
        SqlAccess sql = new SqlAccess();
        string[] items = { "*" };
        string tablename = "books";
        string bookname = null;
        DataSet ds = sql.SelectWhere(tablename, items, bookname);

        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)
            {
                // string str = "";
                foreach (DataColumn column in table.Columns)
                {
                    string str = "";
                    str += row[column];
                    stars.Add(str);
                }
                // Debug.Log(str + "ee");
            }
        }
        Debug.Log("changdu:" + stars.Count);
        row_n = stars.Count / 7;


        //table.onClick.AddListener(gengxin);

        m_datas = new List<IList<object>>();
        // name is necessary in columnDefs
        m_columnDefs = new List<WColumnDef>
        {
            new WColumnDef() {name = "书名",width = "15%"},
            new WColumnDef() {name = "作者名",width = "20%"},
            new WColumnDef() {name = "出版社",width = "19%"},
            new WColumnDef() {name = "出版时间",width = "12%"},
            new WColumnDef() {name = "译者",width = "11%"},
            new WColumnDef() {name = "豆瓣评分",},
            new WColumnDef() {name = "借阅状态",},
            //new WColumnDef() {name = "译者", width = "50%", disableSort = true}

        };
        for (int i = 0; i < row_n; i++)
        {
            m_datas.Add(GetRandomData(i));
        }
        dataTable.msgHandle += HandleTableEvent;
        dataTable.InitDataTable(m_datas, m_columnDefs);

    }
（4）实现表头排序功能的引入、实现表头界面颜色的更改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public void HandleTableEvent(WEventType messageType, params object[] args)
{
    if (messageType == WEventType.INIT_ELEMENT)
    {
        int rowIndex = (int) args[0];
        int columnIndex = (int) args[1];
        WElement element = args[2] as WElement;
        if (element == null)
            return;
        Text tText = element.GetComponent<Text>();
        if (tText == null)
            return;
        tText.color = Color.black;
    }
    else if (messageType == WEventType.SELECT_ROW)
    {
        int rowIndex = (int) args[0];
        m_tempSelectIndex = rowIndex;
    }
}
（5）为表项内容赋值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
private List<object> GetRandomData(int i )
{
    return new List<object>
    {
       // "浮木",
       // "杨本芬",
       //"四川人民出版社",
       // "2018-12",
       // "无",
       // 8.0 + i,
       // "未借出"
       stars[i * 7].ToString(),
       stars[i * 7 + 1].ToString(),
       stars[i * 7 + 2].ToString(),
       stars[i * 7 + 3].ToString(),
       stars[i * 7 + 4].ToString(),
       stars[i * 7 + 5].ToString(),
       stars[i * 7 + 6].ToString()
    };
}

同理，创建 Aexample.cs 为 “作者信息表” 进行创建及更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using WDT;
using System.Data;
using System;

public class Aexample : MonoBehaviour
{
    public WDataTable dataTable;
    private List<IList<object>> m_datas = null;
    private List<WColumnDef> m_columnDefs = null;
    private int m_tempSelectIndex = -1;
    ArrayList stars = new ArrayList();
    int row_n;

    void Start()
    {
        SqlAccess sql = new SqlAccess();
        string[] items = { "*" };
        string tablename = "auther";
        string bookname = null;
        DataSet ds = sql.SelectWhere(tablename, items, bookname);

        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)
            {
                // string str = "";
                foreach (DataColumn column in table.Columns)
                {
                    string str = "";
                    str += row[column];
                    stars.Add(str);
                }
                // Debug.Log(str + "ee");
            }
        }
        Debug.Log("changdu:" + stars.Count);
        row_n = stars.Count / 5;


        //table.onClick.AddListener(gengxin);

        m_datas = new List<IList<object>>();
        // name is necessary in columnDefs
        m_columnDefs = new List<WColumnDef>
        {
            new WColumnDef() {name = "作者名"},
            new WColumnDef() {name = "性别",width = "100"},
            new WColumnDef() {name = "出生时间"},
            new WColumnDef() {name = "出生地"},
            new WColumnDef() {name = "国家",width = "200"},
            //new WColumnDef() {name = "译者", width = "50%", disableSort = true}

        };
        for (int i = 0; i < row_n; i++)
        {
            m_datas.Add(GetRandomData(i));
        }
        dataTable.msgHandle += HandleTableEvent;
        dataTable.InitDataTable(m_datas, m_columnDefs);

    }


    public void HandleTableEvent(WEventType messageType, params object[] args)
    {
        if (messageType == WEventType.INIT_ELEMENT)
        {
            int rowIndex = (int)args[0];
            int columnIndex = (int)args[1];
            WElement element = args[2] as WElement;
            if (element == null)
                return;
            Text tText = element.GetComponent<Text>();
            if (tText == null)
                return;
            tText.color = Color.black;
        }
        else if (messageType == WEventType.SELECT_ROW)
        {
            int rowIndex = (int)args[0];
            m_tempSelectIndex = rowIndex;
        }
    }
    private List<object> GetRandomData(int i)
    {
        return new List<object>
        {
           stars[i * 5].ToString(),
           stars[i * 5 + 1].ToString(),
           stars[i * 5 + 2].ToString(),
           stars[i * 5 + 3].ToString(),
           stars[i * 5 + 4].ToString(),
        };
    }
}

同理，创建 Bexample.cs 为 “学生信息表” 进行创建及更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using WDT;
using System.Data;
using System;

public class Bexample : MonoBehaviour
{
    public WDataTable dataTable;
    private List<IList<object>> m_datas = null;
    private List<WColumnDef> m_columnDefs = null;
    private int m_tempSelectIndex = -1;
    ArrayList stars = new ArrayList();
    int row_n;

    void Start()
    {
        SqlAccess sql = new SqlAccess();
        string[] items = { "*" };
        string tablename = "student";
        string bookname = null;
        DataSet ds = sql.SelectWhere(tablename, items, bookname);

        if (ds != null)
        {
            DataTable table = ds.Tables[0];
            foreach (DataRow row in table.Rows)
            {
                // string str = "";
                foreach (DataColumn column in table.Columns)
                {
                    string str = "";
                    str += row[column];
                    stars.Add(str);
                }
                // Debug.Log(str + "ee");
            }
        }
        Debug.Log("changdu:" + stars.Count);
        row_n = stars.Count / 5;


        //table.onClick.AddListener(gengxin);

        m_datas = new List<IList<object>>();
        // name is necessary in columnDefs
        m_columnDefs = new List<WColumnDef>
        {
            new WColumnDef() {name = "姓名"},
            new WColumnDef() {name = "学号"},
            new WColumnDef() {name = "性别"},
            new WColumnDef() {name = "院系"},
            new WColumnDef() {name = "借阅书籍"},
            //new WColumnDef() {name = "译者", width = "50%", disableSort = true}

        };
        for (int i = 0; i < row_n; i++)
        {
            m_datas.Add(GetRandomData(i));
        }
        dataTable.msgHandle += HandleTableEvent;
        dataTable.InitDataTable(m_datas, m_columnDefs);

    }


    public void HandleTableEvent(WEventType messageType, params object[] args)
    {
        if (messageType == WEventType.INIT_ELEMENT)
        {
            int rowIndex = (int)args[0];
            int columnIndex = (int)args[1];
            WElement element = args[2] as WElement;
            if (element == null)
                return;
            Text tText = element.GetComponent<Text>();
            if (tText == null)
                return;
            tText.color = Color.black;
        }
        else if (messageType == WEventType.SELECT_ROW)
        {
            int rowIndex = (int)args[0];
            m_tempSelectIndex = rowIndex;
        }
    }
    private List<object> GetRandomData(int i)
    {
        return new List<object>
        {

           stars[i * 5].ToString(),
           stars[i * 5 + 1].ToString(),
           stars[i * 5 + 2].ToString(),
           stars[i * 5 + 3].ToString(),
           stars[i * 5 + 4].ToString(),
        };
    }
}

DataTable.cs 脚本实现对表格内容进行动态更新和排序功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
using System;
using System.Collections.Generic;
using System.Linq;
using UnityEngine;
using UnityEngine.Assertions;
using UnityEngine.UI;

namespace WDT
{
    public enum WEventType
    {
        SELECT_ROW,
        SELECT_COLUMN,
        SORT_BY_COLUMN,
        INIT_ELEMENT,
        EVENT_COUNT,
    }

    public class WColumnDef
    {
        public string name;
        public string headPrefabName;
        public string elementPrefabName;
        public string width;
        public bool disableSort;
    }

    public delegate void WMsgHandle(WEventType messageType, params object[] args);

    public class WDataTable : MonoBehaviour
    {
        public class RowElementInfo
        {
            public int rowIndex;
            public WDataTable bindDataTable;
            public IList<WColumnDef> columnsDefs;
        }

        private class SortItem
        {
            public SortItem(int indexIn, object itemIn)
            {
                rowIndex = indexIn;
                item = itemIn;
            }

            public readonly int rowIndex;
            public readonly object item;
        }

        [HideInInspector] public WMsgHandle msgHandle;

        public string rowPrefab;
        public string defaultHeadPrefabName;
        public string defaultElementPrefabName;
        public int itemHeight;
        public int tableWidth;
        public int tableHeight;
        public bool isUseSort = true;
        public bool isUseSelect = true;
        private bool m_init;
        private WHead m_head;
        private LoopVerticalScrollRect m_scrollRect;
        private RectTransform m_scrollRectTransform;
        private RectTransform m_scrollbarRectTransform;
        private IList<IList<object>> m_datas = new List<IList<object>>();
        private IList<WColumnDef> m_columnDefs = new List<WColumnDef>();
        private readonly IList<RowElementInfo> m_rowInfos = new List<RowElementInfo>();
        private readonly IList<int> m_columnWidths = new List<int>();
        private readonly List<SortItem> m_sortItems = new List<SortItem>();

        #region public

        public LoopVerticalScrollRect GetLoopScrollRect()
        {
            return m_scrollRect;
        }

        public float GetPositionByNewSize(float oldPosition, int oldCount, int newCount)
        {
            float offset = oldPosition * (itemHeight * oldCount - tableHeight);
            float newPosition = offset / (itemHeight * newCount - tableHeight);
            return newPosition;
        }

        public float GetPosition()
        {
            if (m_scrollRect == null)
                return 0;

            return m_scrollRect.verticalNormalizedPosition;
        }

        public void SetPosition(float position)
        {
            if (m_scrollRect == null)
                return;

            m_scrollRect.verticalNormalizedPosition = position;
        }

        /// <summary>
        /// Sorts the index of the by.
        /// </summary>
        /// <param name="columnIndex">The index.</param>
        public void SortByIndex(int columnIndex)
        {
            if (columnIndex < 0 || columnIndex >= m_columnDefs.Count)
                return;

            if (m_rowInfos.Count == 0 || !(m_datas[0][columnIndex] is IComparable))
                return;

            m_sortItems.Clear();
            for (int i = 0; i < m_datas.Count; i++)
                m_sortItems.Add(new SortItem(i, m_datas[i][columnIndex]));

            m_sortItems.Sort((x, y) =>
            {
                var cpX = x.item as IComparable;
                var cpY = y.item as IComparable;
                if (cpX == null || cpY == null)
                    return 0;
                return cpX.CompareTo(cpY);
            });

            for (int i = 0; i < m_sortItems.Count; i++)
                m_rowInfos[i].rowIndex = m_sortItems[i].rowIndex;

            UpdateByRowInfo();

            if (msgHandle != null)
                msgHandle(WEventType.SORT_BY_COLUMN, columnIndex);
        }

        /// <summary>
        /// update data of the data table. need ensure right data
        /// </summary>
        /// <param name="datas">The datas.</param>
        public void UpdateData(IList<IList<object>> datas)
        {
            if (datas == null)
                return;

            if (!m_init)
            {
                Debug.LogError("not init data table");
                return;
            }

            IList<IList<object>> tDatas = datas;
            if (!CheckInputData(tDatas, m_columnDefs))
                return;

            if (!CheckConfig())
                return;

            m_datas = datas;
            m_rowInfos.Clear();
            for (int i = 0; i < m_datas.Count; i++)
                m_rowInfos.Add(new RowElementInfo {rowIndex = i, bindDataTable = this, columnsDefs = m_columnDefs});

            UpdateByRowInfo();
        }

        /// <summary>
        /// Initializes the data table. need ensure right data
        /// </summary>
        /// <param name="datas">The datas.</param>
        /// <param name="columnDefs"></param>
        public void InitDataTable(IList<IList<object>> datas, IList<WColumnDef> columnDefs)
        {
            if (!CheckInputData(datas, columnDefs))
                return;

            if (!CheckConfig())
                return;

            if (!m_init)
                Init();

            // copy
            m_datas = datas;
            m_columnDefs = columnDefs;
            m_rowInfos.Clear();
            for (int i = 0; i < m_datas.Count; i++)
                m_rowInfos.Add(new RowElementInfo {rowIndex = i, bindDataTable = this, columnsDefs = m_columnDefs});

            UpdateColumnWidths();
            m_head.SetColumnInfo(m_columnDefs, this);
            m_scrollRect.prefabSource.prefabName = rowPrefab;
            UpdateScrollRectSize();
            UpdateByRowInfo();
        }

        public IList<object> GetInfosByRowIndex(int rowIndex)
        {
            if (rowIndex < 0 || rowIndex >= m_datas.Count)
                return null;

            return m_datas[rowIndex];
        }

        public void OnClickRow(int rowIndex)
        {
            Debug.Log("clicked rowIndex" + rowIndex);
            if (msgHandle != null)
                msgHandle(WEventType.SELECT_ROW, rowIndex);
        }

        public void OnClickColumn(int columnIndex)
        {
            if (isUseSort)
                SortByIndex(columnIndex);

            Debug.Log("clicked columnIndex " + columnIndex);
            if (msgHandle != null)
                msgHandle(WEventType.SELECT_COLUMN, columnIndex);
        }

        public void OnClickButton(int rowIndex, int columnIndex)
        {
            Debug.Log("clicked button row " + rowIndex + " columnIndex " + columnIndex);
            if (rowIndex == -1)
                OnClickColumn(columnIndex);
        }

        public void OnInitElement(int rowIndex, int columnIndex, WElement element)
        {
            if (msgHandle != null)
                msgHandle(WEventType.INIT_ELEMENT, rowIndex, columnIndex, element);
        }

        [ContextMenu("UpdateSize")]
        public void UpdateSize()
        {
            if (!m_init)
                return;

            UpdateColumnWidths();
            m_head.UpdateHeadSize();
            UpdateScrollRectSize();
            m_scrollRect.RefillCells();
        }

        public int GetWidthByColumnIndex(int columnIndex)
        {
            if (columnIndex < 0 || columnIndex >= m_columnWidths.Count)
                return 0;
            return m_columnWidths[columnIndex];
        }

        public bool CanSortByColumnIndex(int columnIndex)
        {
            if (m_columnDefs == null)
                return true;

            if (columnIndex < 0 || columnIndex >= m_columnDefs.Count)
                return false;

            if (m_columnDefs[columnIndex] == null)
                return true;

            return !m_columnDefs[columnIndex].disableSort;
        }

        #endregion

        private void Init()
        {
            m_scrollRect = GetComponentInChildren<LoopVerticalScrollRect>();
            m_head = GetComponentInChildren<WHead>();
            Assert.IsNotNull(m_scrollRect);
            Assert.IsNotNull(m_head);
            m_scrollRectTransform = m_scrollRect.GetComponent<RectTransform>();
            m_scrollbarRectTransform = m_scrollRect.verticalScrollbar.GetComponent<RectTransform>();
            Assert.IsNotNull(m_scrollRectTransform);
            Assert.IsNotNull(m_scrollbarRectTransform);
            m_init = true;
        }

        private void UpdateColumnWidths()
        {
            if (m_columnDefs == null)
                return;

            m_columnWidths.Clear();
            if (m_columnDefs == null || m_columnDefs.Count == 0)
            {
                for (int i = 0; i < m_columnDefs.Count; i++)
                {
                    m_columnWidths.Add(tableWidth / m_columnDefs.Count);
                }
            }
            else
            {
                int totalWidth = 0;
                int totalCount = 0;
                for (int i = 0; i < m_columnDefs.Count; i++)
                {
                    int width = 0;
                    if (m_columnDefs[i] != null && !string.IsNullOrEmpty(m_columnDefs[i].width))
                    {
                        if (m_columnDefs[i].width.Contains('%'))
                        {
                            string percentString = m_columnDefs[i].width.Replace("%", "");
                            int percent;
                            int.TryParse(percentString, out percent);
                            width = (int) (tableWidth * (percent / 100.0f));
                        }
                        else
                        {
                            int.TryParse(m_columnDefs[i].width, out width);
                        }
                    }

                    m_columnWidths.Add(width);
                    if (width > 0)
                    {
                        totalCount += 1;
                        totalWidth += width;
                    }
                }

                if (totalCount < m_columnDefs.Count)
                {
                    int otherWidth = (tableWidth - totalWidth) / (m_columnDefs.Count - totalCount);
                    if (otherWidth <= 0)
                        Debug.LogError("Error columnDef for calculate column width");

                    for (int i = 0; i < m_columnWidths.Count; i++)
                    {
                        if (m_columnWidths[i] == 0)
                            m_columnWidths[i] = otherWidth;
                    }
                }
            }
        }

        private void UpdateScrollRectSize()
        {
            m_scrollRectTransform.sizeDelta = new Vector2(tableWidth, tableHeight);
            m_scrollbarRectTransform.anchoredPosition = new Vector2(tableWidth, 0);
            m_scrollbarRectTransform.sizeDelta = new Vector2(20, tableHeight);
        }

        private void UpdateByRowInfo()
        {
            m_scrollRect.objectsToFill = m_rowInfos.ToArray();
            m_scrollRect.totalCount = m_rowInfos.Count;
            m_scrollRect.RefillCells();
        }

        private bool CheckConfig()
        {
            if (string.IsNullOrEmpty(rowPrefab))
            {
                Debug.LogError("need set rowPrefab name");
                return false;
            }

            if (itemHeight <= 0 || tableWidth <= 0 || tableHeight <= 0)
            {
                Debug.LogError("size number greater than zero");
                return false;
            }

            if (itemHeight > tableHeight)
            {
                Debug.LogError("need itemHeight < tableHeight");
                return false;
            }

            return true;
        }

        private bool CheckInputData(IList<IList<object>> datas, ICollection<WColumnDef> columnDefs)
        {
            if (datas == null || columnDefs == null)
            {
                Debug.LogError("datas columnDefs not be null");
                return false;
            }

            if (datas.Count == 0)
            {
                Debug.LogError("empty data");
                return false;
            }

            for (int i = 0; i < datas.Count; i++)
            {
                if (datas[i].Count != columnDefs.Count)
                {
                    Debug.LogError("row data length not equal columns length:" + i);
                    return false;
                }
            }

            foreach (var column in columnDefs)
            {
                if (string.IsNullOrEmpty(column.name))
                {
                    Debug.LogError("columnDefs need contain column name");
                    return false;
                }

                if (string.IsNullOrEmpty(defaultHeadPrefabName) && string.IsNullOrEmpty(column.headPrefabName))
                {
                    Debug.LogError("if defaultHeadPrefabName is empty, columnDefs need contain headPrefabName");
                    return false;
                }

                if (string.IsNullOrEmpty(defaultElementPrefabName) && string.IsNullOrEmpty(column.elementPrefabName))
                {
                    Debug.LogError("if defaultElementPrefabName is empty, columnDefs need contain elementPrefabName");
                    return false;
                }
            }

            for (int i = 0; i < columnDefs.Count; i++)
            {
                for (int j = 0; j < datas.Count - 1; j++)
                {
                    if ((datas[j][i] == null) || (datas[j + 1][i] == null))
                    {
                        if ((datas[j][i] == null) && (datas[j + 1][i] == null))
                            continue;

                        Debug.LogError("data type not same:[" + j + "," + i + "], [" + (j + 1) + "," + i + "]");
                        return false;
                    }

                    if (datas[j][i].GetType() == datas[j + 1][i].GetType())
                        continue;

                    Debug.LogError("data type not same:[" + j + "," + i + "], [" + (j + 1) + "," + i + "]");
                    return false;
                }
            }

            return true;
        }
    }
}

5、添加界面

Ⅰ、界面绘制及功能介绍

image-20220628015334405

该界面由 11个输入框组件 和 2个按钮组件 构成

用户通过在本界面输入相应的书籍或者作者信息，可以实现在数据库中添加记录的功能

【例】

image-20220628015724530

注：书籍信息为个人编纂，仅测试用

若输入已有书籍，则会产生报错

image-20220628015833643

Ⅱ、功能实现

（1）引入命名空间
1
2
3
4
5
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using System.Data;
（2）对变量及组件进行初始化赋值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public InputField nameinput;
public InputField autherinput;
public InputField producerinput;
public InputField ageinput;
public InputField tanslaterinput;
public InputField starinput;

public InputField authernameinput;
public InputField authersexinput;
public InputField birthinput;
public InputField areainput;
public InputField countryinput;

public Button addbtn;
public Button addbtn1;
public GameObject shibai;
public GameObject chenggong;
DataSet ds;
（3）对按钮事件进行绑定
private void Start()
{
    shibai.SetActive(false);
    chenggong.SetActive(false);
    addbtn.onClick.AddListener(Add);
    addbtn1.onClick.AddListener(Add1);
}
（4）实现添加书籍信息的功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
void Add()
{
    SqlAccess sql = new SqlAccess();
    string tablename = "books";
    string bookname = nameinput.text;
    string auther = autherinput.text;
    string producer = producerinput.text;
    string age = ageinput.text;
    string translate = tanslaterinput.text;
    string star = starinput.text;


    string[] datavalues = new string[] {bookname,auther,producer,age,translate,star,"未借出"};
    if( bookname != "")
    {
        ds = sql.Additem(tablename, datavalues);
        chenggong.SetActive(true);
    }
         
    if (ds == null)
    {
        shibai.SetActive(true);
    }        
}
（5）实现添加作者信息的功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
void Add1()
{
    SqlAccess sql = new SqlAccess();
    string tablename = "auther";
    string authername = authernameinput.text;
    string authersex = authersexinput.text;
    string birth = birthinput.text;
    string area = areainput.text;
    string country = countryinput.text;


    string[] datavalues = new string[] { authername, authersex, birth, area, country };
    if (authername != "")
    {
        ds = sql.Additem(tablename, datavalues);
        chenggong.SetActive(true);
    }

    if (ds == null)
    {
        shibai.SetActive(true);
    }
}

6、修改界面

Ⅰ、界面绘制及功能介绍

image-20220628020246461

用户通过输入书籍名称对要修改的书籍进行查找

image-20220628020338245

image-20220628020355296

查找完成后可以对书籍信息进行修改，修改完成后点击 修改 按钮

image-20220628020450178

再点击确认，即可修改成功

image-20220628020540363

Ⅱ、功能实现

创建 AlterCanvas.cs 脚本

（1）引入命名空间
1
2
3
4
5
6
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using System.Data;
using System.Timers;
（2）变量及组件的初始化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public GameObject confcanvas;
public GameObject confcanvas1;
public InputField input_bookname;
public Text showtext;

public InputField nameinput;
public InputField autherinput;
public InputField producerinput;
public InputField ageinput;
public InputField tanslaterinput;
public InputField starinput;
public InputField borrowinput;

public Button querybtn;
public Button alterbtn;
public Button shanchubtn;

public Button quxiao_btn;
public Button queren_btn;

public Button quxiao_btn1;
public Button queren_btn1;

private Color colorOrgion = new Color(0, 0, 0, 1);//默认为黑色
private float Alpha = 1.0f;
private bool isbeclikck = false;
Timer timer = new Timer(2000);//延迟2秒
（3）组件绑定及变量初始化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
void Start()
{    
    querybtn.onClick.AddListener(chaxun);

    alterbtn.onClick.AddListener(xianshi);
    quxiao_btn.onClick.AddListener(quxiao);
    queren_btn.onClick.AddListener(queren);

    shanchubtn.onClick.AddListener(xianshi1);
    quxiao_btn1.onClick.AddListener(quxiao1);
    queren_btn1.onClick.AddListener(queren1);

    showtext.color = new Color(0, 0, 0, 0);//默认不显示

    timer.Elapsed += (object sender, ElapsedEventArgs e) =>
    {
        isbeclikck = true;
    };

    timer.AutoReset = false; //如果 System.Timers.Timer 应在每次间隔结束时引发 System.Timers.Timer.Elapsed 事件，则为 true；如果它仅在间隔第一次结束后引发一次，可以新建一颗控制台应用，然后F12跟进去查看详情，这是我复制粘贴的，
    timer.Enabled = true;// 如果 System.Timers.Timer 应引发 System.Timers.Timer.Elapsed 事件，则为 true；

}
（4）依次实现各个按钮的功能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
private void Update()
{
    if (isbeclikck)
    {
        Alpha = Alpha - (Time.deltaTime);//通过修改阿尔法值来设置显示或隐藏
        colorOrgion.a = Alpha;
        showtext.color = colorOrgion;
    }
    if (colorOrgion.a <= 0)//如果阿尔法小于0就代表文字完全消失了
    {
        isbeclikck = false;
    }

}
void chaxun()
{
    SqlAccess sql = new SqlAccess();
    string[] inputitem = new string[7];
    string[] items = { "*" };
    string tablename = "books";
    string bookname = input_bookname.text;
    DataSet ds = sql.SelectWhere(tablename, items, bookname);

    if (ds != null)
    {
        DataTable table = ds.Tables[0];
        foreach (DataRow row in table.Rows)
        {
            string str = "";
            foreach (DataColumn column in table.Columns)
            {
                Debug.Log(row[column] + "aa");
                str += row[column] + " ";
            }
            
            Debug.Log(str);
            string[] strSplit = str.Split();

            foreach (var item in strSplit)
            {
                Debug.Log(item + " ");
            }
            nameinput.text = strSplit[0];
            autherinput.text = strSplit[1];
            producerinput.text = strSplit[2];
            ageinput.text = strSplit[3];
            tanslaterinput.text = strSplit[4];
            starinput.text = strSplit[5];
            borrowinput.text = strSplit[6];
        }
    }
   

}
void queren()
{
    if (input_bookname.text != "")
    {
        SqlAccess sql = new SqlAccess();

        string tablename = "books";

        string deletename = input_bookname.text;
        DataSet ds1 = sql.Delete(tablename, deletename);



        string bookname = nameinput.text;
        string auther = autherinput.text;
        string producer = producerinput.text;
        string age = ageinput.text;
        string translate = tanslaterinput.text;
        string star = starinput.text;
        string borrowed = borrowinput.text;
        string[] datavalues = new string[] { bookname, auther, producer, age, translate, star, borrowed };
        DataSet ds2 = sql.Additem(tablename, datavalues);

        confcanvas.SetActive(false);
        showtext.color = new Color(0, 0, 1, 1);//只要点击按钮就会把文字的阿尔法值设置为1
        Alpha = 1;
        colorOrgion.a = 1.0f;
        timer.Start(); //开始计时，2秒之后执行Timer_Elapsed方法；
    }


}
void queren1()
{
    if (input_bookname.text != "")
    {
        SqlAccess sql = new SqlAccess();

        string tablename = "books";

        string deletename = input_bookname.text;
        DataSet ds1 = sql.Delete(tablename, deletename);

        confcanvas1.SetActive(false);
        Alpha = 1;
        colorOrgion.a = 1.0f;
        timer.Start(); //开始计时，2秒之后执行Timer_Elapsed方法；
    }


}
void xianshi()
{
    confcanvas.SetActive(true);
}
void quxiao()
{
    confcanvas.SetActive(false);
}
void xianshi1()
{
    confcanvas1.SetActive(true);
}
void quxiao1()
{
    confcanvas1.SetActive(false);
}
private void Timer_Elapsed(object sender, ElapsedEventArgs e)
{
    isbeclikck = true;
}

7、图表界面

Ⅰ、界面绘制及功能介绍

image-20220628021037129

该界面有一个饼状图和一个柱状图

上图为初始化状态

程序运行后

image-20220628021210340

这两个图表会显示全部书籍的豆瓣评分分布状态

将鼠标移到饼状图的各个分布上，还会实现突出显示功能

点击饼状图的副标题，可选择是否隐藏该分部

image-20220628021413705

【注】底部柱状图x轴的乱码属于编译编码问题，在程序打包后即可解决

Ⅱ、功能实现

图表绘制调用了Unity内置的动画状态机实现

图表数据的更新赋值调用如下函数实现，该函数在 MainCanva.cs 中被调用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
void xiugai()
{
    int a = 0;
    int b = 0;
    int c = 0;

    selcanva.gameObject.SetActive(false);
    addcanva.gameObject.SetActive(false);
    altercanva.gameObject.SetActive(false);
    showchartcanva.gameObject.SetActive(true);

    SqlAccess sql = new SqlAccess();
    string[] items = { "star" };
    string tablename = "books";
    string bookname = null;
    DataSet ds = sql.SelectWhere(tablename, items, bookname);

    if (ds != null)
    {
        DataTable table = ds.Tables[0];
        foreach (DataRow row in table.Rows)
        {
            string str = "";
            foreach (DataColumn column in table.Columns)
            {
                str += row[column];
                float num = float.Parse(str);
                if (num >= 9.0f)
                    a++;
                else if (num <= 8.0f)
                    c++;
                else
                    b++;
                //stars.Add(float.Parse(str));
            }
        }
    }
    chart.UpdateData(0, 0, a);
    chart.UpdateData(0, 1, b);
    chart.UpdateData(0, 2, c);

    var yAxis = barchart.GetChartComponent<YAxis>();
    yAxis.minMaxType = Axis.AxisMinMaxType.Default;

    barchart.RemoveData();
    serie = barchart.AddSerie<Bar>("Star");

    barchart.AddXAxisData("小于8.0");
    barchart.AddData(0, c);
    barchart.AddXAxisData("8.0-9.0");
    barchart.AddData(0, b);
    barchart.AddXAxisData("大于9.0" );
    barchart.AddData(0, a);

}

五、实验总结及心得

（略）