sql数据库处理

77 阅读 0 评论 51 点赞

我是靠谱客的博主外向外套，最近开发中收集的这篇文章主要介绍sql数据库处理，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

using System;-------------数据库工具类--------------
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
//如何在unity中使用数据库
//封装一个工具类 负责创建 增删改查等操作
public class SqlLiteDataHelper : MonoBehaviour
{
//数据库连接定义
private SqliteConnection dbConnection;
//SQL命令定义
private SqliteCommand dbCommand;
//查询记录数据读取器
private SqliteDataReader dataReader;
//构造函数，负责创建数据库的连接
public SqlLiteDataHelper(string dbName)
{
try
{
//拼接数据库连接字符串
string connectionStr = "data source=" + Application.dataPath + "/" + dbName + ".db";
//构造数据库的连接对象
dbConnection=new SqliteConnection(connectionStr);
//打开这个数据库
dbConnection.Open();
Debug.Log("数据库打开成功");
}
catch (Exception e)
{
Debug.LogError("数据库打开失败"+e.Message);
throw;
}
}
//执行sql语句，使用sql语句字符串
public SqliteDataReader ExecuteQuery(string querying)
{
//创建命令
dbCommand= dbConnection.CreateCommand();
//设置执行sql语句
dbCommand.CommandText = querying;
//执行命令并返回结果
dataReader= dbCommand.ExecuteReader();
return dataReader;
}
//关闭数据库连接，释放系统资源
public void CloseConnection()
{
//销毁command
if (dbCommand!=null)
{
dbCommand.Cancel();
dbCommand = null;
}
//销毁reader
if (dataReader!=null)
{
dataReader.Close();
dataReader = null;
}
//销毁connection
if (dbConnection!=null)
{
dbConnection.Close();
dbConnection = null;
}
Debug.Log("数据库已关闭");
}
//创建数据库的表
public SqliteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
{
//判断字段个数跟类型个数是否一致
if (colNames.Length!=colTypes.Length)
{
Debug.LogError("创表失败，字段个数与类型个数不匹配！");
return null;
}
string queryString = "CREATE TABLE " + tableName + "(" + colNames[0] + " " + colTypes[0];
for (int i = 1; i < colNames.Length; i++)
{
queryString += "," + colNames[i] + " " + colNames[i];
}
queryString += ");";
Debug.Log("创建表" + tableName + "成功");
return ExecuteQuery(queryString);
}
//读取整张表的所有数据
public SqliteDataReader ReadFullTable(string tableName)
{
string queryString = "SELECT*FROM " + tableName;
return ExecuteQuery(queryString);
}
//向指定表格中插入数据
public SqliteDataReader InsertValues(string tableName, params string[] values)
{
//获取表格数据中的所有字段个数
int fieldCount=ReadFullTable(tableName).FieldCount;
//如果插入数据的个数与表格中字段个数不一致
if (values.Length!=fieldCount)
{
Debug.LogError("向表"+tableName+"中插入字段失败，值与字段不相符");
return null;
}
//拼接sql语句字符串
string queryString = "INSERT INTO " + tableName + " VALUES(" + values[0];
for (int i = 1; i < values.Length; i++)
{
queryString += "," + values[i];
}
queryString += ")";
Debug.Log("向表" + tableName + "中插入数据成功");
//执行命令并返回
return ExecuteQuery(queryString);
}
//更新指定表格中的记录
/// <summary>
/// UpdataValues
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="colNames">字段</param>
/// <param name="colValues">字段值</param>
/// <param name="key">条件key</param>
/// <param name="operation">条件</param>
/// <param name="value">条件的值</param>
/// <returns></returns>
public SqliteDataReader UpdataValues(string tableName,string[] colNames,
string[]colValues,string operation)
{
if (colNames.Length!=colValues.Length)
{
Debug.LogError("更新表"+tableName+"失败，字段个数与值的个数不匹配！");
return null;
}
//拼接sql语句字符串
string queryString = "UPDATE " + tableName + " SET " + colNames[0] + " = " + colValues[0];
for (int i = 1; i < colNames.Length; i++)
{
queryString += "," + colNames[i] + " = " + colValues[i];
}
//拼接条件语句
queryString += " WHERE " +
operation ;
Debug.Log("更新表格"+tableName+"，更新成功！");
return ExecuteQuery(queryString);
}
//删除指定的记录
/// <summary>
/// DeleteValues
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="colNames">字段</param>
/// <param name="operations">字段条件</param>
/// <param name="colValues">字段值</param>
/// <param name="delmode">and or</param>
/// <returns></returns>
private SqliteDataReader DeleteValues(string tableName,string[] colNames,
string[] operations,string[] colValues,string delmode)
{
if (colNames.Length!=operations.Length||colValues.Length!=operations.Length)
{
Debug.LogError("删除数据表"+tableName+"中的记录失败：字段，运算符，值的个数不一样");
return null;
}
//拼接字符串
string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] +
operations[0] + colValues[0];
for (int i = 1; i < colNames.Length; i++)
{
queryString += " " + delmode + " " + colNames[i] + operations[i] + colValues[i];
}
Debug.Log("删除数据表"+tableName+"中的记录成功！");
return ExecuteQuery(queryString);
}
//删除指定表格中的记录 条件之间用and连接
public SqliteDataReader DeleteValuesAnd(string tableName, string[] colNames,
string[] operations, string[] colValues)
{
return DeleteValues(tableName, colNames, operations, colValues, " AND ");
}
//删除指定表格中的记录 条件之间用or连接
public SqliteDataReader DeleteValuesOr(string tableName, string[] colNames,
string[] operations, string[] colValues)
{
return DeleteValues(tableName, colNames, operations, colValues, " OR ");
}
//查询数据
public SqliteDataReader ReadTableDatas(string tableName,string[] items,
string[] colNames,string[]operations,string[]colValues){
//判断字段个数和运算符个数和字段值个数是否相同
if (colNames.Length != operations.Length || operations.Length != colValues.Length)
{
Debug.LogError("查询数据表" + tableName + "失败:字段个数,运算符,字段值个数不匹配!");
return null;
}
//拼接SQL字符串语句
string queryString = "SELECT " + items[0];
for (int i = 1; i < items.Length; i++)
{
queryString += ", " + items[i];
}
//指定查询的字段已拼接完毕
queryString += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
for (int i = 1; i < colNames.Length; i++)
{
queryString += " AND " + colNames[i] + operations[i] + colValues[i];
}
return ExecuteQuery(queryString);
}
}

using System;------------数据库manage------------
using System.Collections;
using System.Collections.Generic;
using Mono.Data.Sqlite;
using UnityEngine;
public class SqllitTest : MonoBehaviour
{
private SqlLiteDataHelper dataHelper;
private void Start()
{
dataHelper=new SqlLiteDataHelper("HeroDataTestDBB");
}
//创建数据
public void CreateTable()
{
dataHelper.CreateTable("Heroes", new String[] {"ID", "Name", "Age", "Des"},
new String[] {"INTERGER", "TEXT", "INTEGER", "TEXT"});
}
//删除数据
public void RemoveAllData()
{
dataHelper.ExecuteQuery(@"DROP TABLE Heroes;");
}
//删除指定数据
public void RemoveData()
{
dataHelper.DeleteValuesAnd("Heroes",
new String[] {"Name", "Age"},
new String[] {"=", "="},
new String[] {"'欧洲人'", "28"});
}
//增加数据
public void AddData()
{
dataHelper.InsertValues("Heroes", "1001", "'土豪'", "23", "'我是人民币'");
dataHelper.InsertValues("Heroes", "1002", "'屌丝'", "22", "'我是肝帝'");
dataHelper.InsertValues("Heroes", "1003", "'欧洲人'", "28", "'我是欧洲人'");
}
//更改数据
public void ChangeData()
{
// dataHelper.ExecuteQuery(@"UPDATE HERO SET ATTACK=10000 WHERE ID==4;");
dataHelper.UpdataValues("Heroes", new String[] {"Name", "Age"},
new String[] {"'张大'", "25"}, "Name='张大哥'");
}
//查询数据
public void FindData()
{
//读取整张表的记录
SqliteDataReader reader= dataHelper.ReadTableDatas("Heroes",
new string[] {"ID ", "Name "},
new String[] {"Age"},
new String[] {">"},
new String[] {"20"});
//记录的计数器
int count = 0;
while (reader.Read())
{
count++;
int ID = reader.GetInt32(reader.GetOrdinal("ID"));
string Name = reader.GetString(reader.GetOrdinal("Name"));
Debug.Log(
string.Format("第{0}记录为：ID{1},Name{2})",count,ID,Name
));
}
//读取数据库中age>20的玩家信息
Debug.Log("表中共有"+count+"条记录");
}
}