Mono.Data.Sqlite
一、常用属性
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对象文章来源：https://www.toymoban.com/news/detail-437215.html
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 :文章来源地址https://www.toymoban.com/news/detail-437215.html
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();
}
}
到了这里，关于Unity—Mono.Data.Sqlite的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
