- Home /
Problem with SQLite unity Database
Hey there :) Am a newbie developing unity 3d app for android platform and even for windows, am using unity v4.5 pro and I have chosen SQLite with SQLite browser to create my "local" database in unity 3D (in Csharp), just because SQLite is serverless (most important point),free,and does'nt require any configuration (just a matter of DLLs integration). Well, a part of my educational project is to find a way how to modify in my database and adding new values for example after building it ( when it is runnig maybe :confused: )..I still don't know how to do that but am keeping learning for the while and i 'll get my point if somebody helps me guys . I have two questions
1- Is there any other suggestions for choosing other way to create my local database which must run later on android (serverless,free,matches with android platform)!? Or maybe any other addition that may works like SQLiteHelper For SQLite but i can iintegrate it with unity ofcourse..? I dont know ..any help please
2- I found the example bellow of code project and I tried to build it on both windows and android..Well it works fine on unity editor but for build connexion is lost :(..i Tried but i coudn't resolve that problem Please any advices or help would be appreciated.(Here is the code) (5 classes)
"Class CustomerEntity"
using UnityEngine;
using System.Collections;
using System;
/// <summary>
/// Entity class of Customer
/// Mapped manually (!!!) from SampleDB
/// </summary>
public class CustomerEntity
{
#region Fields
private int iD;
private string userName;
#endregion
#region Properties
/// <summary>
/// Gets or sets the identifier.
/// </summary>
/// <value>
/// The identifier.
/// </value>
public int ID
{
get { return iD; }
set { iD = value; }
}
/// <summary>
/// Gets or sets the name of the user.
/// </summary>
/// <value>
/// The name of the user.
/// </value>
public string UserName
{
get { return userName; }
set { userName = value; }
}
/// <summary>
/// Prevents a default instance of the <see cref="CustomerEntity"/> class from being created.
/// </summary>
private CustomerEntity()
{
}
/// <summary>
/// Initializes a new instance of the <see cref="CustomerEntity"/> class.
/// </summary>
/// <param name="iD">The identifier</param>
/// <param name="userName">Name of the user.</param>
public CustomerEntity(int iD, string userName)
{
if (string.IsNullOrEmpty(userName))
throw new ArgumentNullException("userName cannot be null or empty !");
this.iD = iD;
this.userName = userName;
}
#endregion
}
"Class CustomerEntityHelper"
using UnityEngine;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using Mono.Data.Sqlite;
using System;
/// <summary>
/// CustomerEntityHelper Help you to retrieve the named Entity
/// </summary>
public class CustomerEntityHelper : DBHelper
{
public List<CustomerEntity> GetAllCustomers()
{
List<CustomerEntity> result = new List<CustomerEntity>();
try
{
using (IDbConnection dbcon = new SqliteConnection(base.ConnString) as IDbConnection)
{
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT ID, UserName FROM Customer"; ;
using (IDataReader reader = dbcmd.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string userName = reader.GetString(1);
CustomerEntity customer = new CustomerEntity(id, userName);
result.Add(customer);
}
reader.Close();
}
dbcon.Close();
}
}
catch (Exception ex)
{
Debug.LogError("GetAllCustomers thrown an error : " + ex.ToString());
}
return result;
}
}
"Class ConnectionResult "
using UnityEngine;
using System.Collections;
/// <summary>
/// ConnectionResult
/// </summary>
public class ConnectionResult
{
/// <summary>
/// ConnectionStatus
/// </summary>
public enum ConnectionStatus
{
Connected,
NotConnected
}
private string connectionStringUsed;
private string errorMessage;
private ConnectionStatus status;
public ConnectionStatus Status
{
get { return status; }
set { status = value; }
}
public string ConnectionStringUsed
{
get { return connectionStringUsed; }
set { connectionStringUsed = value; }
}
public string ErrorMessage
{
get { return errorMessage; }
set { errorMessage = value; }
}
}
"Class DBHelper"
using UnityEngine;
using System.Collections;
using System;
using System.Data;
using Mono.Data.Sqlite;
using System.IO;
/// <summary>
/// DBHelper
/// </summary>
public class DBHelper
{
#region Fields
private string connString;
private string databaseCompletePath;//the path in your Unity Project and the DB Name
#endregion
#region Properties
/// <summary>
/// Gets the connection string.
/// </summary>
/// <value>
/// The connection string.
/// </value>
public string ConnString
{
get { return connString; }
}
#endregion
#region Constructors
/// <summary>
/// Initializes a new instance of the <see cref="DBHelper"/> class.
/// </summary>s
public DBHelper()
{
this.databaseCompletePath = "/SampleDB";
this.connString = string.Format("URI=file:{0}{1}", Application.dataPath,this. databaseCompletePath);
}
/// <summary>
/// Initializes a new instance of the <see cref="DBHelper"/> class.
/// </summary>
/// <param name="specificConnectionString">The specific connection string.</param>
/// <example>connection string could be: YourFloder1/Yourfolder2/YourDBName</example>
public DBHelper(string specificConnectionString)
{
this.databaseCompletePath = specificConnectionString;
this.connString = string.Format("URI=file:{0}/{1}",Application.dataPath,specificConnectionString);
}
#endregion
#region Methods
/// <summary>
/// Checks the connection.
/// </summary>
/// <returns></returns>
public ConnectionResult CheckConnection()
{
ConnectionResult result = new ConnectionResult();
result.ConnectionStringUsed = this.connString;
try
{
using (IDbConnection dbcon = new SqliteConnection(this.connString) as IDbConnection)
{
//check if db file exist (with a bad name, sqlite try to create the DB and we don't want this behavior!)
if (File.Exists(string.Format("{0}{1}", Application.dataPath,this.databaseCompletePath)))
{
dbcon.Open(); //Open connection to the database.
result.Status = ConnectionResult.ConnectionStatus.Connected;
dbcon.Close();
}
else
throw new Exception("Database was not Found !");
}
}
catch (Exception ex)
{
result.ErrorMessage = ex.ToString();
result.Status = ConnectionResult.ConnectionStatus.NotConnected;
}
return result;
}
#endregion
}
"Class SampleDB "
using UnityEngine;
using System.Collections;
using System.Text;
using System;
public class SampleDB : MonoBehaviour
{
private DBHelper dbHelper;
private bool isDBConnected;
private string checkResultMessage;
private string customerMessage;
/// <summary>
/// Starts this instance.
/// </summary>
void Start()
{
this.checkResultMessage = string.Empty;
this.dbHelper = new DBHelper();//use the embedded DBHelper connection string.
this.isDBConnected = false; // before check, we consider the DB status as not connected.
}
void OnGUI()
{
int heightLocation = 100; //used for GUI
StringBuilder sbCustomer = new StringBuilder();// used to add string
if (GUI.Button(new Rect(10, 10, 200, 30), "Check Connection"))
{
checkResultMessage = string.Empty;
ConnectionResult result = this.dbHelper.CheckConnection();
this.isDBConnected = (result.Status == ConnectionResult.ConnectionStatus.Connected);
checkResultMessage = (result.Status == ConnectionResult.ConnectionStatus.Connected) ?
string.Format("Connection OK with connection string {0}", result.ConnectionStringUsed) : string.Format("Connection ERROR: {0}",result.ErrorMessage) ;
}
GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage);
if (this.isDBConnected)
{
if (GUI.Button(new Rect(220, 10, 200, 30), "Get customers"))
{
CustomerEntityHelper customerHelper = new CustomerEntityHelper();
foreach (CustomerEntity customer in customerHelper.GetAllCustomers())
{
sbCustomer.Append(string.Format("{0} - ID: {1}{2}", customer.UserName, customer.ID,Environment.NewLine));
heightLocation += 40;
}
this.customerMessage = sbCustomer.ToString();
}
}
GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage);
GUI.Label(new Rect(10, 10 + heightLocation, 300, 600), this.customerMessage);
}
}
BTW: I am new in the Forum and That's my first post,sorry for my bad english:p .Thank you even for reading this whole stuff :p
Your answer
Follow this Question
Related Questions
Sqlite3.dll not integrated into Android Build 1 Answer
Distribute terrain in zones 3 Answers
Can't Connect to SQLite after Build..... 3 Answers
SQLite Except: The database file is locked 1 Answer
Database (SQLite) Setup for Unity 0 Answers