Unity—Mono.Data.Sqlite

查看：411 |回复：1 | *2022-8-29 13:46:53* 519580 · 2022-8-29 13:46:53

您需要登录才可以下载或查看，没有账号？注册

x

本帖最后由 MrTang1988 于 2022-8-29 19:13 编辑

一、常用属性
Depth:获取一个值，用于指示当前行的嵌套深度
FieldCount：获取当前行中的列数
HasRows：获取一个值，该值指示SQLDataReader是否有行
IsClosed:指定的SQLDataReader实例是否已关闭
Item[Int32]：获取指定列（数字索引），通常在While.Read()中使用
Item[String]：获取指定列（字符串索引），通常在While.Read()中使用
RecordsAffected：获取执行SqlDataReader中未隐藏的字段数目

class Program
{
static void Main(string[] args)
{
string str = "server=.;database=JunTest;uid=sa;pwd=123;Asynchronous Processing=true";
SqlConnection conn = new SqlConnection(str); //创建连接
SqlCommand cmd = conn.CreateCommand(); //创建SqlCommand对象
cmd.CommandText = "SELECT * FROM Person";
conn.Open(); //打开连接
using (SqlDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine(reader.FieldCount); //2 获取列数
Console.WriteLine(reader.Depth); //0 嵌套深度
Console.WriteLine(reader.HasRows); //true 是否包含行
Console.WriteLine(reader.IsClosed); //false SqlDataReader是否关闭
Console.WriteLine(reader.RecordsAffected); //-1 执行T-SQL语句所插入、修改、删除的行数
Console.WriteLine(reader.VisibleFieldCount); //2 未隐藏的字段数目(一共就两列)
while (reader.Read())
{
Console.WriteLine(reader["PersonName"]);
//Console.WriteLine(reader[1]); 通过数字索引或字符串索引访问
}
}
conn.Close(); //关闭连接
Console.ReadKey();
}
}

点击此处复制文本

二、常用方法
Read：前进到下一记录，异步版本ReadAsync
GetString：返回指定类型的值，其他的类型
NextResult：当处理批批处理T-SQL语句时，跳到下一结果异步版本NextResultAsync
GetValue：获取该列的值，返回object类型
GetValues：使用当前列，来填充参数中的对象数组
Close：关闭SqlDataReader对象

class Program
{
static void Main(string[] args)
{
string str = "server=.;database=JunTest;uid=sa;pwd=123;Asynchronous Processing=true";
SqlConnection conn = new SqlConnection(str); //创建连接
SqlCommand cmd = conn.CreateCommand(); //创建SqlCommand对象
cmd.CommandText = "SELECT * FROM Person";
conn.Open(); //打开连接
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader.IsDBNull(1)); //是否是null值
Console.WriteLine(reader.GetString(1)); //Get什么类型就返回什么类型，这没啥好说的。
}
Console.WriteLine(reader.GetName(1)); //PersonName 由数字获得列名
Console.WriteLine(reader.GetOrdinal("PersonName")); //1 由列名获取其在reader中的数字索引
if (reader.NextResult())
{
Console.WriteLine(reader.GetString(1));
}
}
conn.Close(); //关闭连接
Console.ReadKey();
}
}

点击此处复制文本

Demo:

using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;
/// <summary>
/// SQLite数据库操作类
/// </summary>
public class SqliteAccess
{
private SqliteConnection conn; // SQLite连接
private SqliteCommand cmd; // SQLite命令
private SqliteDataReader reader;
public SqliteAccess(string connectionString)
{
OpenDB(connectionString);
}
public SqliteAccess() { }
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="connectionString"></param>
public void OpenDB(string connectionString)
{
try
{
conn = new SqliteConnection(connectionString);
conn.Open();
Debug.Log("Connected to db,连接数据库成功！");
}
catch (Exception e)
{
string temp1 = e.ToString();
Debug.Log(temp1);
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseSqlConnection()
{
if (cmd != null) { cmd.Dispose(); cmd = null; }
if (reader != null) { reader.Dispose(); reader = null; }
if (conn != null) { conn.Close(); conn = null; }
Debug.Log("Disconnected from db.关闭数据库！");
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sqlQuery"></param>
/// <returns></returns>
public SqliteDataReader ExecuteQuery(string sqlQuery)
{
cmd = conn.CreateCommand();
cmd.CommandText = sqlQuery;
reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 查询表中全部数据 param tableName=表名
/// </summary>
public SqliteDataReader ReadFullTable(string tableName)
{
string query = "SELECT * FROM " + tableName;
return ExecuteQuery(query);
}
/// <summary>
/// 插入数据 param tableName=表名 values=插入数据内容
/// </summary>
public SqliteDataReader InsertInto(string tableName, string[] values)
{
string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
for (int i = 1; i < values.Length; ++i)
{
query += ", " + values[i];
}
query += ")";
return ExecuteQuery(query);
}
/// <summary>
/// 更新数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容
/// </summary>
public SqliteDataReader UpdateInto(string tableName, string[] cols, string[] colsvalues, string selectkey, string selectvalue)
{
string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsvalues[0];
for (int i = 1; i < colsvalues.Length; ++i)
{
query += ", " + cols[i] + " =" + colsvalues[i];
}
query += " WHERE " + selectkey + " = " + selectvalue + " ";
return ExecuteQuery(query);
}
/// <summary>
/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
/// </summary>
public SqliteDataReader Delete(string tableName, string[] cols, string[] colsvalues)
{
string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsvalues[0];
for (int i = 1; i < colsvalues.Length; ++i)
{
query += " or " + cols[i] + " = " + colsvalues[i];
}
return ExecuteQuery(query);
}
/// <summary>
/// 插入数据 param tableName=表名 cols=插入字段 value=插入内容
/// </summary>
public SqliteDataReader InsertIntoSpecific(string tableName, string[] cols, string[] values)
{
if (cols.Length != values.Length)
{
throw new SqliteException("columns.Length != values.Length");
}
string query = "INSERT INTO " + tableName + "(" + cols[0];
for (int i = 1; i < cols.Length; ++i)
{
query += ", " + cols[i];
}
query += ") VALUES (" + values[0];
for (int i = 1; i < values.Length; ++i)
{
query += ", " + values[i];
}
query += ")";
return ExecuteQuery(query);
}
/// <summary>
/// 删除表中全部数据
/// </summary>
public SqliteDataReader DeleteContents(string tableName)
{
string query = "DELETE FROM " + tableName;
return ExecuteQuery(query);
}
/// <summary>
/// 创建表 param name=表名 col=字段名 colType=字段类型
/// </summary>
public SqliteDataReader CreateTable(string name, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new SqliteException("columns.Length != colType.Length");
}
string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
for (int i = 1; i < col.Length; ++i)
{
query += ", " + col[i] + " " + colType[i];
}
query += ")";
return ExecuteQuery(query);
}
/// <summary>
/// 按条件查询数据 param tableName=表名 items=查询字段 col=查找字段 operation=运算符 values=内容
/// </summary>
public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values)
{
if (col.Length != operation.Length || operation.Length != values.Length)
{
throw new SqliteException("col.Length != operation.Length != values.Length");
}
string query = "SELECT " + items[0];
for (int i = 1; i < items.Length; ++i)
{
query += ", " + items[i];
}
query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
for (int i = 1; i < col.Length; ++i)
{
query += " AND " + col[i] + operation[i] + "'" + values[i] + "' ";
}
return ExecuteQuery(query);
}
/// <summary>
/// 查询表
/// </summary>
public SqliteDataReader Select(string tableName, string col, string values)
{
string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + values;
return ExecuteQuery(query);
}
public SqliteDataReader Select(string tableName, string col, string operation, string values)
{
string query = "SELECT * FROM " + tableName + " WHERE " + col + operation + values;
return ExecuteQuery(query);
}
/// <summary>
/// 升序查询
/// </summary>
public SqliteDataReader SelectOrderASC(string tableName, string col)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " ASC";
return ExecuteQuery(query);
}
/// <summary>
/// 降序查询
/// </summary>
public SqliteDataReader SelectOrderDESC(string tableName, string col)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " DESC";
return ExecuteQuery(query);
}
/// <summary>
/// 查询表行数
/// </summary>
public SqliteDataReader SelectCount(string tableName)
{
string query = "SELECT COUNT(*) FROM " + tableName;
return ExecuteQuery(query);
}
}

点击此处复制文本

using Mono.Data.Sqlite;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
public class User : MonoBehaviour
{
private SqliteAccess db;
protected SqliteDataReader reader;
private string dbName = "sqlite.db";
private void Start()
{
OpenDB();
reader = db.ReadFullTable("User");
for(int i = 0; i < reader.VisibleFieldCount; i++)
{
Debug.Log(reader.GetName(i)+"--"+reader.GetValue(i));
}
while (reader.Read())
{
Debug.Log(reader["username"]);
//Console.WriteLine(reader[1]); 通过数字索引或字符串索引访问
}
reader.Close();
}
}

点击此处复制文本

liubing129 · 2023-3-1 12:07:12

[计算机] Unity—Mono.Data.Sqlite

显示全部标签

最新问题

最热回答

浏览过的版块

短杖

学徒法袍

无色原始矿

火元素

绿母翠晶矿

彗星钛晶矿

铁剑

钢剑

水晶剑

翡翠剑

赤铁剑

元素铜币

元素银币

双杀

巨浪

龙枪

吃鸡头盔

吉普车

吃鸡甲

青戒之钻

莆田鞋

守望者【EX】

希望人没事

烈火长枪

粒粒皆辛苦

裁决

波塞冬之叉

魔法帽

魔法扫帚

天机·长青扇

天机·卧龙扇

长枪

长剑

短剑

元素秘籍