Unity 4.3.3f (Free) With SQLite 3 For Standalone

Okay, I plan to make an open world RPG game where I will use the database to store inventory, quest, and everything.

I manage to make the basic code working.

It can connect and read the desired value.

Here are the code;

Code (csharp):

1.  
2. using UnityEngine;
3. using System.Collections;
4. using Mono.Data.Sqlite;
5. using System.Data;
6. using System;
7.  
8. public class DBAccess : MonoBehaviour {
9.  
10.     // Use this for initialization
11.     void Start () {
12.  
13.         string connectionString = "URI=file:" +Application.dataPath + "/GameMaster"; //Path to database.
14.         IDbConnection dbcon;
15.         dbcon = (IDbConnection) new SqliteConnection(connectionString);
16.  
17.         dbcon.Open(); //Open connection to the database.
18.  
19.         IDbCommand dbcmd = dbcon.CreateCommand();
20.  
21.         string sql = "SELECT firstname, lastname " + "FROM addressbook";
22.  
23.         dbcmd.CommandText = sql;
24.  
25.         IDataReader reader = dbcmd.ExecuteReader();
26.  
27.         while(reader.Read()) {
28.             string FirstName = reader.GetString (0);
29.             string LastName = reader.GetString (1);
30.             Console.WriteLine("Name: " +
31.                               FirstName + " " + LastName);
32.             Debug.Log (FirstName + LastName);
33.         }
34.  
35.         // clean up
36.         reader.Close();
37.         reader = null;
38.         dbcmd.Dispose();
39.         dbcmd = null;
40.         dbcon.Close();
41.         dbcon = null;
42.    
43.     }
44.    
45.     // Update is called once per frame
46.     void Update () {
47.  
48.    
49.     }
50.  
51.     void OnGUI () {
52.  
53.  
54.  
55.         string connectionString = "URI=file:" +Application.dataPath + "/GameMaster"; //Path to database.
56.         IDbConnection dbcon;
57.         dbcon = (IDbConnection) new SqliteConnection(connectionString);
58.        
59.         dbcon.Open(); //Open connection to the database.
60.        
61.         IDbCommand dbcmd = dbcon.CreateCommand();
62.        
63.         string sql = "SELECT firstname FROM addressbook WHERE rowid=1";
64.        
65.         dbcmd.CommandText = sql;
66.        
67.         IDataReader reader = dbcmd.ExecuteReader();
68.        
69.         while(reader.Read()) {
70.             string FirstName = reader.GetString (0);
71.             //string LastName = reader.GetString (1);
72.             //Console.WriteLine("Name: " +
73.                               //FirstName + " " + LastName);
74.             //Debug.Log (FirstName + LastName);
75.  
76.             GUI.Box (new Rect (Screen.width - 270, Screen.height - 55, 260, 30), "Copyright "+FirstName+" 2014");
77.         }
78.        
79.         // clean up
80.         reader.Close();
81.         reader = null;
82.         dbcmd.Dispose();
83.         dbcmd = null;
84.         dbcon.Close();
85.         dbcon = null;
86.  
87.     }
88. }
89.  
90.  

Here are the screenshots of what the code suppose to do.







I read that the database connection must be close as soon as possible, so do I need to repeat the whole code of opening, and reading the value whenever I need them?

Can someone show me how to just run an sql queries on the spot whenever I need the value just like when we use a variable?

 

Think about what values from your database will not change during the course of the execution. Those values, get them once at the start, store them in variables.

You can't open a connection to the database in OnGui, the universe will explode.

Only open it when you need to, to make updates.

 

Why did you say the universe will explode?

So when I need to make an updates, so I need to write the whole open, run sql queries, close db all over again every time I update a value to database?

Or store in memory variable and only update when the player "save" or exit the application?

 

He says it will explode because OnGUI is called once every frame.... meaning you would open a connection to the DB every second basically, and that would be insane for obvious reasons.

You could do as you said and just update the sql tables whenever the player saves or quits.

 

Right, onGui executes many times per second. You don't want to even talk to the database that often let alone establish and close connections.

Store the data locally, manipulate it there, and only update the database on save, exit, and perhaps periodically - auto save on important events, or every certain number of minutes.

 

Alright guys.

Thank you for the enlightenment.
I think I should change my program structure again.