SQilte/DataSet

最新推荐文章于 2021-02-05 17:02:31 发布

SHY不小心

最新推荐文章于 2021-02-05 17:02:31 发布

阅读量278

点赞数

本文链接：https://blog.csdn.net/douniwan08/article/details/77883903

版权

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using Mono.Data.Sqlite;
using System.Data;
using UnityEngine;
using UnityEngine.Networking;

public class DBAccess : MonoBehaviour {
/// <summary>
/// 配置文件DB的路径
/// </summary>
public string configDBPath;
/// <summary>
/// 配置文件拷贝DB的路径
/// </summary>
public string configCopyDBPath;

public DBHelper ConfigDB {
get {
return configDB;
}
}
public DataSet ConfigDataSet {
get {
return configDataSet;
}
}

public static DBAccess Instance
{
get
{
return NetworkManager.singleton.gameObject.GetComponent<DBAccess>();
}
}

public DataRow FindRow(string tableName,int id)
{
var table = configDataSet.Tables[tableName];
var q = (from t in table.AsEnumerable()
where t.Field<int>("id") == id
select t);
foreach (var item in q)
{
return item;
}
return null;
}

public T FindScalar<T>(string tableName,int id,string columnName)
{
var row = FindRow(tableName, id);
if(row != null)
{
return row.Field<T>(columnName);
}
return default(T);
}

public bool IsDBDownload {
get {
return isDBDownload;
}
}
private DBHelper configDB;
private DataSet configDataSet = new DataSet();
private bool isDBDownload;

void Awake(){
try {
StartCoroutine(DownloadDB());
}
catch (Exception e) {
Debug.LogError("error:" + e.Message + e.StackTrace);
isDBDownload = true;
}
}

IEnumerator DownloadDB() {
yield return ReadAppIniFile();
if (File.Exists(configDBPath)) {
File.Delete(configDBPath);
}
WWW www = new WWW(configCopyDBPath);
yield return www;
FileStream fileStream = File.Create(configDBPath);
fileStream.Write(www.bytes, 0, www.bytes.Length);
fileStream.Dispose();
www.Dispose();
//File.WriteAllBytes(configDBPath, System.IO.File.ReadAllBytes(configCopyDBPath));
isDBDownload = true;
configDB = new DBHelper(configDBPath);
configDB.OpenConnection();
InitDataSet();
yield return 0;
}

private void OnDestroy() {
if (configDB != null) {
configDB.CloseConnection();
}
}

private void OnApplicationQuit() {
if (configDB != null) {
configDB.CloseConnection();
}
}

private void InitDataSet() {
SqliteDataAdapter adapter = new SqliteDataAdapter();
string sql = "select name from sqlite_master";
SqliteCommand cmd = new SqliteCommand(sql, configDB.conn);
adapter.SelectCommand = cmd;
adapter.Fill(configDataSet, "sqlite_master");
for (int i = 0; i < configDataSet.Tables["sqlite_master"].Rows.Count; i++) {
string tableName = configDataSet.Tables["sqlite_master"].Rows[i][0] as string;
string sql2 = "select * from "+ tableName;
cmd.CommandText = sql2;
adapter.SelectCommand = cmd;
adapter.Fill(configDataSet, tableName);
}
cmd.Dispose();
adapter.Dispose();
DBAccess.Instance.ConfigDB.CloseConnection();
}

private IEnumerator ReadAppIniFile() {
WWW www = new WWW(Application.streamingAssetsPath+"/app.ini");
yield return www;
string strs = www.text.Replace("\r\n", ",");
www.Dispose();
string[] subs = strs.Split(',');
string dbName = "";
string dbPostfix = "";
foreach (string s in subs) {
string[] ss = s.Split('=');
if (ss.Length == 2) {
if (ss[0] == "language") {
dbPostfix = ss[1];
}
else if (ss[0] == "dbName") {
dbName = ss[1];
}
}
}
string configDBName = dbName + "_" + dbPostfix + ".db";
configDBPath = Application.persistentDataPath + "/" + configDBName;
configCopyDBPath = Application.streamingAssetsPath + "/" + configDBName;
}

}

using UnityEngine;
using System;
using System.Collections.Generic;
using System.IO;
using Mono.Data.Sqlite;
using System.Text;

public class DBHelper {

public bool isConnection;
public SqliteConnection conn;
private SqliteCommand dbCommand;
private string dbPath;

private DBHelper(){}

public DBHelper(string dbPath) {
this.dbPath = dbPath;
}

//打开数据库
public void OpenConnection() {
string connStr = "Data Source=" + this.dbPath;
conn = new SqliteConnection(connStr);
conn.Open();
isConnection = true;
}

public void AttachDb(string path, string dbName = "localDb") {
StringBuilder sb = new StringBuilder();
sb.AppendFormat("attach database '{0}' as {1}", path, dbName);
Execute(sb.ToString());
}

//关闭连接
public void CloseConnection() {
if (dbCommand != null) {
dbCommand.Cancel();
}
dbCommand = null;
if (conn != null) {
conn.Close();
}
conn = null;
}

public object QuerySingle(string sqlQuery, params SqliteParameter[] parameter) {
CheckConn();
dbCommand = conn.CreateCommand();
dbCommand.CommandText = sqlQuery;
if (parameter != null)
dbCommand.Parameters.AddRange(parameter);
object obj = dbCommand.ExecuteScalar();
dbCommand.Cancel();
return obj;
}

public int Execute(string sqlQuery,SqliteParameter[] parameter = null) {
//Debug.Log("sqlQuery:" + sqlQuery);
CheckConn();
dbCommand = conn.CreateCommand();
dbCommand.CommandText = sqlQuery;
if (parameter != null)
{
dbCommand.Parameters.AddRange(parameter);
}
int ret = dbCommand.ExecuteNonQuery();
dbCommand.Cancel();
return ret;
}

public void ExecuteTransaction(Action succeedAction, Action failedAction = null) {
CheckConn();
SqliteTransaction trans = conn.BeginTransaction();
try {
succeedAction();
trans.Commit();
}
catch (Exception e) {
Debug.LogError(e.Message);
trans.Rollback();
if (failedAction != null) {
failedAction();
}
}
}

public void Query(string sql,Action<SqliteDataReader> call ,params SqliteParameter[] parameter)
{
if (!string.IsNullOrEmpty(sql))
{
if (call != null)
{
CheckConn();
dbCommand = conn.CreateCommand();
dbCommand.CommandText = sql;
if (parameter != null)
{
dbCommand.Parameters.AddRange(parameter);
}
var reader = dbCommand.ExecuteReader();

call(reader);

if (reader != null && !reader.IsClosed)
reader.Close();
}
}
}

public void Query(StringBuilder sql, Action<SqliteDataReader> call, params SqliteParameter[] parameter) {
Query(sql.ToString(), call, parameter);
}

public List<Dictionary<string, object>> QueryTable(string tableName, params string[] colNames)
{
if (colNames.Length > 0)
{
var sb = new StringBuilder();
sb.Append("select ");
foreach (var colName in colNames)
{
sb.AppendFormat("{0},", colName);
}
sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" from {0}", tableName);

var list = new List<Dictionary<string, object>>();
Query(sb.ToString(), (reader) => {
while (reader.Read())
{
var dict = new Dictionary<string, object>();
list.Add(dict);
for (int i = 0; i < reader.FieldCount; i++)
{
dict[colNames[i]] = reader[i];
}
}
}, null);
return list;
}
return null;
}

private void CheckConn() {
if (conn == null) {
OpenConnection();
}
}

public byte[] GetBytes(SqliteDataReader reader, int col)
{
const int SIZE = 128;
byte[] buffer = new byte[SIZE];
long bytesRead;
long fieldOffset = 0;
using (MemoryStream ms = new MemoryStream())
{
while ((bytesRead = reader.GetBytes(col, fieldOffset, buffer, 0, buffer.Length)) > 0)
{
ms.Write(buffer, 0, (int)bytesRead);
fieldOffset += bytesRead;
}
return ms.ToArray();
}
}
}