Search Unity

  1. Unity 2019.2 is now released.
    Dismiss Notice

[Tutorial] How to integrate SQLite in C#

Discussion in 'Community Learning & Teaching' started by Sildaekar, Jul 24, 2013.

  1. Sildaekar

    Sildaekar

    Joined:
    Jul 8, 2013
    Posts:
    95
    I have been banging my head to find a decent description of how to integrate SQLite in my project using C# and regrettably could not find any kind of tutorials, or walkthroughs on how to do it. Thankfully after a few hours I finally managed to figure it out and thought I would share the process with the rest of the community.

    Please keep in mind that I am horrible when it comes to writing tutorials so this is more of a "do this" style document.

    Step 1: Download the file attatched to this post and place the files in your Assets/Plugins folder.

    Step 2: Place the following "using" statements at the top of your code.
    Code (csharp):
    1.  
    2. using System.Data;
    3. using Mono.Data.SqliteClient
    Step 3: Identify these variables somewhere in your code where the function will be able to access them. They don't have to be private or even have the same names but be sure to keep the types.
    Code (csharp):
    1.    
    2. private string _constr="URI=file:NPCMaster.db";
    3. private IDbConnection _dbc;
    4. private IDbCommand _dbcm;
    5. private IDataReader _dbr;
    Step 4: use the following code to connect to a database and pull the info you need from it.
    Code (csharp):
    1.            
    2. _dbc=new SqliteConnection(_constr);
    3. _dbc.Open();
    4. _dbcm=_dbc.CreateCommand();
    5. _dbcm.CommandText="SELECT `id` FROM `npc` WHERE `name`='"+NPCname+"'";
    6. _dbr=_dbcm.ExecuteReader();
    7.            
    8. while( _dbr.Read()){
    9.     _NPCid=_dbr.GetInt16(0);
    10. }
    Please keep in mind this is some code from a project I am working on so you will have to change it to suite your needs, this is just a basic "how to" to get it up and working.

    EDIT

    It's been a WHILE since I created this post. I would be hesitant to use this now. Look into SQLite PCL for your needs. If I remember I will write a more well thought out tutorial in the near future.
     

    Attached Files:

    Last edited: Sep 12, 2018
    Teomani, turosteel, AscendDev and 6 others like this.
  2. princeWIGUAN

    princeWIGUAN

    Joined:
    Aug 14, 2013
    Posts:
    337
    After adding the NPCname and _NPCid variable so it won't show error,

    I got these error.

    Code (csharp):
    1. qliteSyntaxException: no such table: npc
    2. Mono.Data.SqliteClient.SqliteCommand.GetNextStatement (IntPtr pzStart, System.IntPtr pzTail, System.IntPtr pStmt)
    3. Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior, Boolean want_results, System.Int32 rows_affected)
    4. Mono.Data.SqliteClient.SqliteCommand.ExecuteReader (CommandBehavior behavior)
    5. Mono.Data.SqliteClient.SqliteCommand.ExecuteDbDataReader (CommandBehavior behavior)
    6. System.Data.Common.DbCommand.ExecuteReader ()
    7. System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader ()
    8. connectDB.Update () (at Assets/connectDB.cs:36)
    How do we create and browse the database for the first time, without using Unity?
     
  3. Yukichu

    Yukichu

    Joined:
    Apr 2, 2013
    Posts:
    417
    FANTASTIC!! If there was some kind of upvote love here, I'd be all over it.

    I spent the better part of a day trying to get this working using a ton of other various, stupid methods, mostly directly from the SQLite website, downloading .Net packages and bundles and importing and failing and this and that.

    Fantastic. Thank you. Tons of thank yous in fact.
     
  4. Yukichu

    Yukichu

    Joined:
    Apr 2, 2013
    Posts:
    417
    You need to create the database first. Here is a starting example I used for testing. Mind you, either create the database somewhere else and do it only once, or delete it every test because you can only create it once.

    Code (csharp):
    1.  
    2.         string _strDBName = "URI=file:MasterSQLite.db";
    3.         IDbConnection _connection = new SqliteConnection(_strDBName);
    4.         IDbCommand _command = _connection .CreateCommand();
    5.         string sql;
    6.  
    7.         _connection .Open();
    8.  
    9.         sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";
    10.         _command.CommandText = sql;
    11.         _command.ExecuteNonQuery();
    12.  
    13.         sql = "INSERT INTO highscores (name, score) VALUES ('Me', 3000)";
    14.         _command.CommandText = sql;
    15.         _command.ExecuteNonQuery();
    16.  
    17.         sql = "insert into highscores (name, score) values ('Myself', 6000)";
    18.         _command.CommandText = sql;
    19.         _command.ExecuteNonQuery();
    20.  
    21.         sql = "insert into highscores (name, score) values ('And I', 9001)";
    22.         _command.CommandText = sql;
    23.         _command.ExecuteNonQuery();
    24.  
    25.         sql = "select * from highscores order by score desc";
    26.         _command.CommandText = sql;
    27.         IDataReader _reader = _command.ExecuteReader();
    28.         while (_reader.Read())
    29.             Debug.Log("****** Name: " + _reader["name"] + "\tScore: " + _reader["score"]);
    30.  
    31.         _reader.Close();
    32.         _reader = null;
    33.         _command.Dispose();
    34.         _command = null;
    35.         _connection .Close();
    36.         _connection = null;
    37.  
     
    pKallv likes this.
  5. princeWIGUAN

    princeWIGUAN

    Joined:
    Aug 14, 2013
    Posts:
    337
  6. Sildaekar

    Sildaekar

    Joined:
    Jul 8, 2013
    Posts:
    95
    Glad you guys enjoyed it and found it useful! :D Sorry it took so long to reply, apparently I'm not getting emails when people respond to my posts :confused:
     
  7. Oksana-Iashchuk

    Oksana-Iashchuk

    Joined:
    Sep 10, 2012
    Posts:
    111
    You could try SQLiteKit and get something like this:

    SQLiteQuery qr;
    SQLiteDB db = new SQLiteDB("..../MasterSQLite.db");

    qr = new SQLiteQuery(db,"CREATE TABLE highscores (name VARCHAR(20), score INT)");
    qr.Step();
    qr.Release();

    qr = new SQLiteQuery(db,"INSERT INTO highscores (name, score) VALUES (?, ?)");
    qr.Bind("Me");
    qr.Bind(3000);
    qr.Step();
    qr.Release();

    qr = new SQLiteQuery(db,"INSERT INTO highscores (name, score) VALUES (?, ?)");
    qr.Bind("Myself");
    qr.Bind(6000);
    qr.Step();
    qr.Release();

    qr = new SQLiteQuery(db,"select * from highscores order by score desc");
    while(qr.Step())
    {
    ... = qr.GetString("name");
    ... = qr.GetInt("score");
    }
    qr.Release();

    Best regards
     
    doanlinhit24 likes this.
  8. Ethan

    Ethan

    Joined:
    Jan 2, 2008
    Posts:
    497
    Thanks Sildaekar!
    I owe you one beer. ;)
     
  9. dustxx

    dustxx

    Joined:
    Jan 27, 2014
    Posts:
    1
    Thanks so much for this!
     
  10. Ajes

    Ajes

    Joined:
    Sep 13, 2013
    Posts:
    26
    Sadly I am getting this error, but only when I try to build. it runs fine in the editor:


    ArgumentException: The Assembly System.Configuration is referenced by System.Data. But the dll is not allowed to be included or could not be found.
    UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:113)
    UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:116)
    UnityEditor.AssemblyHelper.AddReferencedAssembliesRecurse (System.String assemblyPath, System.Collections.Generic.List`1 alreadyFoundAssemblies, System.String[] allAssemblyPaths, System.String[] foldersToSearch, System.Collections.Generic.Dictionary`2 cache, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:116)
    UnityEditor.AssemblyHelper.FindAssembliesReferencedBy (System.String[] paths, System.String[] foldersToSearch, BuildTarget target) (at C:/BuildAgent/work/d3d49558e4d408f4/Editor/Mono/AssemblyHelper.cs:148)
    UnityEditor.BuildPlayerWindow:BuildPlayerAndRun()


    I have tried to add the missing dll's like "System.Configuration.dll" but that just breaks my game..

    anyone know anything about this problem?
     
    Last edited: Oct 7, 2014
  11. Nekonomicon

    Nekonomicon

    Joined:
    Aug 8, 2015
    Posts:
    1
    I have the same problem
    Me too, is there anyone who knows why this happens?
     
  12. pKallv

    pKallv

    Joined:
    Mar 2, 2014
    Posts:
    781
    Did you solve this?
     
  13. rizwandeveloper

    rizwandeveloper

    Joined:
    Jul 15, 2016
    Posts:
    8
    I need some help. :)
     
  14. mathieu93

    mathieu93

    Joined:
    Jul 25, 2016
    Posts:
    8
    Have you already found an answer to this problem? I think it's because you're trying to build for another platform than the general Windows platform. See the following post, found on Stackoverflow

    I'm currently trying to add a SQLite PCL in my project. Whenever I find out how to do this properly (for UWP, iOS, Android), I will post my findings here!
     
  15. mathieu93

    mathieu93

    Joined:
    Jul 25, 2016
    Posts:
    8
  16. savlon

    savlon

    Joined:
    Jan 4, 2013
    Posts:
    7
    Have a look at this course of mine. It covers fully saving and loading game data with PlayerPrefs, Text files, Binary serialization, Xml serialization and SQLite. I go through with you step by step explaining each method in detail. Usually this course is $30 but use this coupon code to get it for only $10. Also, if you aren't happy with it or learned absolutely nothing, rest assured that you can hit the refund button and get 100% of your money back. You have 30 days to do that and it is completely risk free!

    Good luck

    https://www.udemy.com/saving-and-loading-game-data-in-unity3d/?couponCode=OCCUNITY
     
    pKallv likes this.
  17. walidabazo

    walidabazo

    Joined:
    Jul 8, 2017
    Posts:
    16
    You can show this videos to connect unity 3d and sqlite and solved all dll error compile for different windows 32bit and 64bit