C# SQLite example for Unity 3.3

Hi everyone,

I needed to get an SQLite database to run on Ipad/Iphone/Mac standalone and Windows standalone.
Searching in the Forum I only found some kind of old statements which did not run at all.
Most of the Example work with System.Data.dll which Unity cannot compile at all (maybe I was only an Idiot, you never know). I was always getting an ArgumentException.

I will now post a solution which will work with the variants mentioned above. The only thing which is not so nice, is that you have to copy the Database to the Application.datapath by hand. If somebody has a better solution for that he is free to post so.

You only need two dlls. You have to copy both to the Plugins-Folder in Unity:
sqlite3.dll
Mono.Data.Sqlite.dll

Here is the code for the Connection:

------------------------------------------------------

using UnityEngine;
using System;
using Mono.Data.Sqlite;

public class dbController : MonoBehaviour {

protected SqliteConnection dbconn;

void Awake () {
if (Application.platform == RuntimePlatform.IPhonePlayer) {
dbconn = new SqliteConnection("URI=file:" +Application.dataPath + "/../Data.db");
} else {
dbconn = new SqliteConnection("URI=file:" +Application.dataPath + "/Data.db");
}

dbconn.Open();

try {
SqliteCommand cmd = new SqliteCommand("SELECT firstname, lastname FROM adressbook", dbconn);
SqliteDataReader reader = cmd.ExecuteReader();

if (reader.HasRows) {
while(reader.Read()) {
string FirstName = reader.GetString (0);
string LastName = reader.GetString (1);

Debug.Log ( FirstName + LastName );
}
}

reader.Close();
reader = null;
} catch {
Debug.Log("Error reading DB");
}

cmd.Dispose();
cmd = null;
dbconn.Close();
dbconn = null;


}

}

------------------------------------------------

So now I tell you where to put the Data.db file so it works for serveral cases:

1. Unity Editor: Place de db in the Project/Assets Folder (this stays you don't have to change any more)
2. Unity Windows: After every Build you have to place the db in the {Name}_Data Folder
3. Ipad/Iphone: After the Build you have to add the fille to XCode:
- XCode "File" -> Add Files to "...." -> Select db
Then the db-File must be located in the first layer
4. Mac Standalone: Create app. Open Finder. Rightclick on app and select "Show package content" and add the db in the "Contents"-Folder

So I hope you don't need search the whole WWW any more.

 

Hi! First of all, I wanted to thank you so much for sharing this code here on the forum! Second, I was wondering if you might be able to spend a bit of time and post your code (and maybe an example script that uses it)? I know it's a lot to ask - I put together a JavaScript tutorial on how to get SQLite working there, so I understand if you don't want to commit to the time required. But let me know if you'd be willing to do it!

http://www.unifycommunity.com/wiki/index.php?title=SQLite

 

if you want a strong solution to store data locally take a look here: http://siaqodb.com/?p=482 (full disclosure- I'm involved in project), but we really see Unity3D developers need a solution that just works. Put dll in your scripts folder and use it, that's all!

 

Hi,

I did excatly as you posted but I am getting the following error when I make a windows build (the game runs fine in the editor):

Is it possible that I am getting this error because I am using Unity Basic and not Unity Pro? I have looked at tons of other topcs on the forums, but this is the most recent. I have tried most of the other fixes that I have seen but nothing seems to work. Did you run into this problem at all when you were getting this solution to work?

Thanks

 

I got the exact same problem.. any news?

 

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 )..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"

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3. using System;
4.  
5. /// <summary>
6. /// Entity class of Customer
7. /// Mapped manually (!!!) from SampleDB
8. /// </summary>
9. public class CustomerEntity
10. {
11.     #region Fields
12.    
13.     private int iD;
14.     private string userName;
15.    
16.     #endregion
17.    
18.     #region Properties
19.     /// <summary>
20.     /// Gets or sets the identifier.
21.     /// </summary>
22.     /// <value>
23.     /// The identifier.
24.     /// </value>
25.     public int ID
26.     {
27.         get { return iD; }
28.         set { iD = value; }
29.     }
30.     /// <summary>
31.     /// Gets or sets the name of the user.
32.     /// </summary>
33.     /// <value>
34.     /// The name of the user.
35.     /// </value>
36.     public string UserName
37.     {
38.         get { return userName; }
39.         set { userName = value; }
40.     }
41.    
42.     /// <summary>
43.     /// Prevents a default instance of the <see cref="CustomerEntity"/> class from being created.
44.     /// </summary>
45.     private CustomerEntity()
46.     {
47.     }
48.    
49.     /// <summary>
50.     /// Initializes a new instance of the <see cref="CustomerEntity"/> class.
51.     /// </summary>
52.     /// <param name="iD">The identifier</param>
53.     /// <param name="userName">Name of the user.</param>
54.     public CustomerEntity(int iD, string userName)
55.     {
56.         if (string.IsNullOrEmpty(userName))
57.             throw new ArgumentNullException("userName cannot be null or empty !");
58.        
59.         this.iD = iD;
60.         this.userName = userName;
61.     }
62.     #endregion
63. }

"Class CustomerEntityHelper "

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3. using System.Collections.Generic;
4. using System.Data;
5. using Mono.Data.Sqlite;
6. using System;
7.  
8. /// <summary>
9. /// CustomerEntityHelper Help you to retrieve the named Entity
10. /// </summary>
11. public class CustomerEntityHelper : DBHelper
12. {
13.     public List<CustomerEntity> GetAllCustomers()
14.     {
15.         List<CustomerEntity> result = new List<CustomerEntity>();
16.         try
17.         {
18.             using (IDbConnection dbcon = new SqliteConnection(base.ConnString) as IDbConnection)
19.             {
20.                 dbcon.Open();
21.                
22.                 IDbCommand dbcmd = dbcon.CreateCommand();
23.                 dbcmd.CommandText = "SELECT ID, UserName FROM Customer"; ;
24.                
25.                 using (IDataReader reader = dbcmd.ExecuteReader())
26.                 {
27.                     while (reader.Read())
28.                     {
29.                         int id = reader.GetInt32(0);
30.                         string userName = reader.GetString(1);
31.                        
32.                         CustomerEntity customer = new CustomerEntity(id, userName);
33.                         result.Add(customer);
34.                     }
35.                     reader.Close();
36.                 }
37.                 dbcon.Close();
38.             }
39.         }
40.         catch (Exception ex)
41.         {
42.             Debug.LogError("GetAllCustomers thrown an error : " + ex.ToString());
43.         }
44.        
45.         return result;
46.     }
47. }
48.  

"Class ConnectionResult"

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3.  
4. /// <summary>
5. /// ConnectionResult
6. /// </summary>
7. public class ConnectionResult
8. {
9.    
10.     /// <summary>
11.     /// ConnectionStatus
12.     /// </summary>
13.     public enum ConnectionStatus
14.     {
15.         Connected,
16.         NotConnected
17.     }
18.    
19.     private string connectionStringUsed;
20.     private string errorMessage;
21.     private ConnectionStatus status;
22.    
23.     public ConnectionStatus Status
24.     {
25.         get { return status; }
26.         set { status = value; }
27.     }
28.     public string ConnectionStringUsed
29.     {
30.         get { return connectionStringUsed; }
31.         set { connectionStringUsed = value; }
32.     }
33.     public string ErrorMessage
34.     {
35.         get { return errorMessage; }
36.         set { errorMessage = value; }
37.     }
38. }
39.  

"Class DBHelper"

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3. using System;
4. using System.Data;
5. using Mono.Data.Sqlite;
6. using System.IO;
7.  
8. /// <summary>
9. /// DBHelper
10. /// </summary>
11. public class DBHelper
12. {
13.     #region Fields
14.    
15.     private string connString;
16.     private string databaseCompletePath;//the path in your Unity Project and the DB Name
17.    
18.     #endregion
19.    
20.     #region Properties
21.    
22.     /// <summary>
23.     /// Gets the connection string.
24.     /// </summary>
25.     /// <value>
26.     /// The connection string.
27.     /// </value>
28.     public string ConnString
29.     {
30.         get { return connString; }
31.     }
32.    
33.     #endregion
34.    
35.     #region Constructors
36.    
37.     /// <summary>
38.     /// Initializes a new instance of the <see cref="DBHelper"/> class.
39.     /// </summary>s
40.     public DBHelper()
41.     {
42.         this.databaseCompletePath = "/SampleDB";
43.         this.connString = string.Format("URI=file:{0}{1}", Application.dataPath,this. databaseCompletePath);
44.        
45.     }
46.    
47.     /// <summary>
48.     /// Initializes a new instance of the <see cref="DBHelper"/> class.
49.     /// </summary>
50.     /// <param name="specificConnectionString">The specific connection string.</param>
51.     /// <example>connection string could be: YourFloder1/Yourfolder2/YourDBName</example>
52.     public DBHelper(string specificConnectionString)
53.     {
54.         this.databaseCompletePath = specificConnectionString;
55.         this.connString = string.Format("URI=file:{0}/{1}",Application.dataPath,specificConnectionString);
56.     }
57.    
58.     #endregion
59.    
60.     #region Methods
61.    
62.     /// <summary>
63.     /// Checks the connection.
64.     /// </summary>
65.     /// <returns></returns>
66.     public ConnectionResult CheckConnection()
67.     {
68.         ConnectionResult result = new ConnectionResult();
69.         result.ConnectionStringUsed = this.connString;
70.         try
71.         {
72.            
73.             using (IDbConnection dbcon = new SqliteConnection(this.connString) as IDbConnection)
74.             {
75.                 //check if db file exist (with a bad name, sqlite try to create the DB and we don't want this behavior!)
76.                 if (File.Exists(string.Format("{0}{1}", Application.dataPath,this.databaseCompletePath)))
77.                 {
78.                     dbcon.Open(); //Open connection to the database.
79.                     result.Status = ConnectionResult.ConnectionStatus.Connected;
80.                     dbcon.Close();
81.                 }
82.                 else
83.                     throw new Exception("Database was not Found !");
84.             }
85.         }
86.         catch (Exception ex)
87.         {
88.             result.ErrorMessage = ex.ToString();
89.             result.Status = ConnectionResult.ConnectionStatus.NotConnected;
90.         }
91.        
92.         return result;
93.     }
94.    
95.     #endregion
96.    
97. }

"Class SampleDB"

Code (CSharp):

1. using UnityEngine;
2. using System.Collections;
3. using System.Text;
4. using System;
5.  
6. public class SampleDB : MonoBehaviour
7. {
8.    
9.     private DBHelper dbHelper;
10.     private bool isDBConnected;
11.     private string checkResultMessage;
12.     private string customerMessage;
13.     /// <summary>
14.     /// Starts this instance.
15.     /// </summary>
16.     void Start()
17.     {
18.         this.checkResultMessage = string.Empty;
19.        
20.         this.dbHelper = new DBHelper();//use the embedded DBHelper connection string.
21.         this.isDBConnected = false; // before check, we consider the DB status as not connected.
22.     }
23.     void OnGUI()
24.     {
25.         int heightLocation = 100; //used for GUI
26.         StringBuilder sbCustomer = new StringBuilder();// used to add string
27.        
28.         if (GUI.Button(new Rect(10, 10, 200, 30), "Check Connection"))
29.         {
30.             checkResultMessage = string.Empty;
31.             ConnectionResult result = this.dbHelper.CheckConnection();
32.             this.isDBConnected = (result.Status == ConnectionResult.ConnectionStatus.Connected);
33.             checkResultMessage = (result.Status == ConnectionResult.ConnectionStatus.Connected) ?
34.                 string.Format("Connection OK with connection string {0}", result.ConnectionStringUsed) : string.Format("Connection ERROR: {0}",result.ErrorMessage) ;
35.         }
36.         GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage);
37.        
38.         if (this.isDBConnected)
39.         {
40.             if (GUI.Button(new Rect(220, 10, 200, 30), "Get customers"))
41.             {
42.                 CustomerEntityHelper customerHelper = new CustomerEntityHelper();
43.                 foreach (CustomerEntity customer in customerHelper.GetAllCustomers())
44.                 {
45.                     sbCustomer.Append(string.Format("{0} - ID: {1}{2}", customer.UserName, customer.ID,Environment.NewLine));
46.                     heightLocation += 40;
47.                 }
48.                 this.customerMessage = sbCustomer.ToString();
49.             }
50.         }
51.        
52.         GUI.Label(new Rect(10, 50, 600, 60), checkResultMessage);
53.         GUI.Label(new Rect(10, 10 + heightLocation, 300, 600), this.customerMessage);
54.        
55.     }
56. }

BTW: I am new in the Forum and That's my first post,sorry for my bad english .Thank you even for reading this whole stuff