Unity3D direct connection to Sqlite Database

Source: Internet
Author: User
Tags sqlite query
URL: bbs.9ria. comforum. php? Modviewthreadtid168629fromuid308561 over the past few days, I also studied how Unity3D can be directly connected to the Sqlite database. Don't talk nonsense. Let's talk about it directly. The original Article is from: wiki. unity3d. comindex. phpSqlite. You can learn it. 1,

URL: http://bbs.9ria.com/forum.php? Mod = viewthreadtid = 168629 fromuid = 308561 over the past few days, I also studied how Unity3D can be directly connected to the Sqlite database. Don't talk nonsense. Let's talk about it directly. The original Article from: http://wiki.unity3d.com/index.php/Sqlite, you can learn. 1,

URL: http://bbs.9ria.com/forum.php? Mod = viewthread & tid = 168629 & fromuid = 308561

In the past few days, I have also studied how Unity3D can be directly connected to the Sqlite database. Don't talk nonsense. Let's talk about it directly. The original Article is from: http://wiki.unity3d.com/index.php/sqlite. you can learn it now.

1. Environment Introduction:

Windows 7, Unity3D, SQLite Expert Personal 3

2. Development language:

JavaScript

3. required dll files:

Mono. Data. Sqlite. dll and sqlite3.dll. I will package all the files for discussion later. Let's take a look at where these dll files should be put and see the following:

, Must be in this directory, please be consistent with me.

4. If you need to release the compiled program successfully, you need to modify the code. For details, see the following:

I have marked the change in red. Note that this should be changed to. NET2.0 for release. The default value is not. NET2.0. Please pay attention to this !!!

5. Let's take a look at the code below. Let's first look at how to create the database code. This Code does not need to be mounted to any object. You just need to use it as a tool. As follows:

/* Javascript class for accessing SQLite objects.

To use it, you need to make sure you COPY Mono. Data. SQLiteClient. dll from wherever it lives in your Unity directory

To your project's Assets folder

Originally created by dklompmaker in 2009

Http://forum.unity3d.com/threads... sier-Database-Stuff

Modified 2011 by Alan Chatham */

// # Pragma strict

/* Code Description

* This code is written to run the unity3d and Sqlite databases in a Windows environment. The basic function is that unity3d can communicate with databases. For example

After the data in the database is changed, the data in unity3d will be refreshed and changed. This is just a basic core technology, in order to be applied to large-scale unity3d

In a project, you can store the properties of a project in a scenario. It can be easily used when you need to change the attributes of an object, such as adding or decreasing objects.

To implement this code. First, you need some dll files, Mono. Data. SQLiteClient. dll and sqlite3.dll, which can be found in the unity3d installation directory.

In addition, you need to put these two files under the path of your project: \ Assets \ Plugins \. This folder must be created without the Plugins folder, then write the two dll files in the folder.

Of course, if you want to publish an executable file on a PC, you still need to modify it. In Play Setting-> Other Setting in unity3d, change the level of Api Compatibility

. NET 2.0; after these operations are completed, if your code is well written, you can succeed.

Now let's explain the code in detail.

  1. *
  2. */
  3. Import System. Data; // we import our data class. we first import our Dataset
  4. Import Mono. Data. Sqlite; // we import sqlite. we import the sqlite dataset, that is, the dll file in the Plugins folder.

  5. Class dbAccess {
  6. // Variables for basic query access
  7. Private var connection: String; // database connection String, used to establish a connection with a specific data source
  8. Private var dbcon: IDbConnection; // connection object of IDbConnection, which is actually a Class Object
  9. Private var dbcmd: IDbCommand; // The IDbCommand class object, which is used to perform database operation commands: Note: How can I execute commands on the database on the Internet:
  10. // Create an IDbConnection connection object, assign a database command to a string, and use this string and the connection object
  11. // You can create (new) An IDbCommand object, and then run this command using the provided method.
  12. Private var reader: IDataReader; // reader is used to read one or more result streams.

  13. Function OpenDB (p: String ){
  14. Connection = "URI = file:" + p; // we set the connection to our database
  15. Dbcon = new SqliteConnection (connection );
  16. Dbcon. Open (); // Open the database connection operation
  17. }

  18. Function BasicQuery (q: String, r: boolean) {// run a baic Sqlite query
  19. Dbcmd = dbcon. CreateCommand (); // create empty command
  20. Dbcmd. CommandText = q; // fill the command
  21. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader returns the IDataReader object and creates the IDataReader object
  22. If (r) {// if we want to return the reader
  23. Return reader; // return the reader returns the read object, that is, what is read
  24. }
  25. }

  26. // This returns a 2 dimen1_arraylist with all
  27. // Data from the table requested
  28. Function ReadFullTable (tableName: String ){
  29. Var query: String;
  30. Query = "SELECT * FROM" + tableName;
  31. Dbcmd = dbcon. CreateCommand ();
  32. Dbcmd. CommandText = query;
  33. Reader = dbcmd. ExecuteReader ();
  34. Var readArray = new ArrayList ();
  35. While (reader. Read ()){
  36. Var lineArray = new ArrayList ();
  37. For (var I = 0; I <reader. FieldCount; I ++)
  38. LineArray. Add (reader. GetValue (I); // This reads the entries in a row
  39. ReadArray. Add (lineArray); // This makes an array of all the rows
  40. }
  41. Return readArray; // return matches
  42. }

  43. // This function deletes all the data in the given table. Forever. watch out! Use sparingly, if at all
  44. Function DeleteTableContents (tableName: String ){
  45. Var query: String;
  46. Query = "delete from" + tableName;
  47. Dbcmd = dbcon. CreateCommand ();
  48. Dbcmd. CommandText = query;
  49. Reader = dbcmd. ExecuteReader ();
  50. }

  51. Function CreateTable (name: String, col: Array, colType: Array) {// Create a table, name, column array, column type array
  52. Var query: String;
  53. Query = "create table" + name + "(" + col [0] + "" + colType [0];
  54. For (var I = 1; I
  55. Query + = "," + col + "" + colType;
  56. }
  57. Query + = ")";
  58. Dbcmd = dbcon. CreateCommand (); // create empty command
  59. Dbcmd. CommandText = query; // fill the command
  60. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader
  61. }
  62. Function InsertIntoSingle (tableName: String, colName: String, value: String) {// single insert
  63. Var query: String;
  64. Query = "insert into" + tableName + "(" + colName + ")" + "VALUES (" + value + ")";
  65. Dbcmd = dbcon. CreateCommand (); // create empty command
  66. Dbcmd. CommandText = query; // fill the command
  67. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader
  68. }
  69. Function insertintospecpacific (tableName: String, col: Array, values: Array) {// Specific insert with col and values
  70. Var query: String;
  71. Query = "insert into" + tableName + "(" + col [0];
  72. For (var I = 1; I
  73. Query + = "," + col;
  74. }
  75. Query + = ") VALUES (" + values [0];
  76. For (I = 1; I
  77. Query + = "," + values;
  78. }
  79. Query + = ")";
  80. Dbcmd = dbcon. CreateCommand ();
  81. Dbcmd. CommandText = query;
  82. Reader = dbcmd. ExecuteReader ();
  83. }

  84. Function InsertInto (tableName: String, values: Array) {// basic Insert with just values
  85. Var query: String;
  86. Query = "insert into" + tableName + "VALUES (" + values [0];
  87. For (var I = 1; I
  88. Query + = "," + values;
  89. }
  90. Query + = ")";
  91. Dbcmd = dbcon. CreateCommand ();
  92. Dbcmd. CommandText = query;
  93. Reader = dbcmd. ExecuteReader ();
  94. }

  95. // This function reads a single column
  96. // WCol is the WHERE column, wPar is the operator you want to use to compare,
  97. // And wValue is the value you want to compare against.
  98. // Ex.-SingleSelectWhere ("puppies", "breed", "earType", "=", "floppy ")
  99. // Returns an array of matches from the command: SELECT breed FROM puppies WHERE earType = floppy;
  100. Function SingleSelectWhere (tableName: String, itemToSelect: String, wCol: String, wPar: String, wValue: String) {// Selects a single Item
  101. Var query: String;
  102. Query = "SELECT" + itemToSelect + "FROM" + tableName + "WHERE" + wCol + wPar + wValue;
  103. Dbcmd = dbcon. CreateCommand ();
  104. Dbcmd. CommandText = query;
  105. Reader = dbcmd. ExecuteReader ();
  106. Var readArray = new Array ();
  107. While (reader. Read ()){
  108. ReadArray. Push (reader. GetString (0); // Fill array with all matches
  109. }
  110. Return readArray; // return matches
  111. }


  112. Function CloseDB (){
  113. Reader. Close (); // clean everything up
  114. Reader = null;
  115. Dbcmd. Dispose ();
  116. Dbcmd = null;
  117. Dbcon. Close ();
  118. Dbcon = null;
  119. }

  120. }


The basic addition, deletion, modification, and query of the above Code are all available. If you take a closer look and add my comments, this code is not difficult. It may be a bit unacceptable for beginners. To be honest, I am also a beginner, if there is no original text, I absolutely cannot write this...

7. Now let's take a look at how to use the database code in Unity3D:

  1. // # Pragma strict
  2. /* Script for testing out SQLite in Javascript
  3. 2011-Alan Chatham
  4. Released into the public domain

  5. This script is a GUI script-attach it to your main camera.
  6. It creates/opens a SQLite database, and with the GUI you can read and write to it.
  7. */

  8. // This is the file path of the database file we want to use
  9. // Right now, it'll load TestDB. sqdb in the project's root folder.
  10. // If one doesn't exist, it will be automatically created.
  11. Public var DatabaseName: String = "TestDB. sqdb ";

  12. // This is the name of the table we want to use
  13. Public var TableName: String = "TestTable ";
  14. Var db: dbAccess;

  15. Function Start (){
  16. // Give ourselves a dbAccess object to work with, and open it



  17. Db = new dbAccess ();
  18. Db. OpenDB (DatabaseName );
  19. // Let's make sure we 've ve got a table to work with as well!
  20. Var tableName = TableName;
  21. Var columnNames = new Array ("firstName", "lastName ");
  22. Var columnValues = new Array ("text", "text ");
  23. Try {db. CreateTable (tableName, columnNames, columnValues );
  24. }
  25. Catch (e) {// Do nothing-our table was already created
  26. //-We don't care about the error, we just don't want to see it
  27. }
  28. }

  29. // These variables just hold info to display in our GUI
  30. Var firstName: String = "First Name ";
  31. Var lastName: String = "Last Name ";
  32. Var DatabaseEntryStringWidth = 100;
  33. Var scrollPosition: Vector2;
  34. Var databaseData: ArrayList = new ArrayList ();

  35. // This GUI provides us with a way to enter data into our database
  36. // As well as a way to view it
  37. Function OnGUI (){
  38. GUI. Box (Rect (25, 25, Screen. width-50, Screen. height-50), "Data ");
  39. GUILayout. BeginArea (Rect (50, 50, Screen. width-100, Screen. height-100 ));
  40. // This first block allows us to enter new entries into our table
  41. GUILayout. BeginHorizontal ();
  42. FirstName = GUILayout. TextField (firstName, GUILayout. Width (DatabaseEntryStringWidth ));
  43. LastName = GUILayout. TextField (lastName, GUILayout. Width (DatabaseEntryStringWidth ));

  44. // LastName = GUILayout. TextField ();
  45. GUILayout. EndHorizontal ();

  46. If (GUILayout. Button ("Add to database ")){
  47. // Insert the data
  48. InsertRow (firstName, lastName );
  49. // And update the readout of the database
  50. DatabaseData = ReadFullTable ();
  51. }
  52. // This second block gives us a button that will display/refresh the contents of our database
  53. GUILayout. BeginHorizontal ();
  54. If (GUILayout. Button ("Read Database "))
  55. DatabaseData = ReadFullTable ();
  56. If (GUILayout. Button ("Clear "))
  57. DatabaseData. Clear ();
  58. GUILayout. EndHorizontal ();

  59. GUILayout. Label ("Database Contents ");
  60. ScrollPosition = GUILayout. BeginScrollView (scrollPosition, GUILayout. Height (100 ));
  61. For (var line: ArrayList in databaseData ){
  62. GUILayout. BeginHorizontal ();
  63. For (var s in line ){
  64. GUILayout. Label (s. ToString (), GUILayout. Width (DatabaseEntryStringWidth ));
  65. }
  66. GUILayout. EndHorizontal ();
  67. }

  68. GUILayout. EndScrollView ();
  69. If (GUILayout. Button ("Delete All Data") {unity3dand sqlitedata warehouse are directly connected to .rar (168.27 KB, download times: 239)


  70. Unity3dand sqlitedata warehouse direct connection .rar (446.06 KB, downloads: 584)





  71. DeleteTableContents ();
  72. DatabaseData = ReadFullTable ();
  73. }
  74. GUILayout. EndArea ();
  75. }

  76. // Wrapper function for inserting our specific entries into our specific database and table for this file
  77. Function InsertRow (firstName, lastName ){
  78. Var values = new Array ("'" + firstName + "'"), ("'" + lastName + "'"));
  79. Db. InsertInto (TableName, values );
  80. }

  81. // Wrapper function, so we only mess with our table.
  82. Function ReadFullTable (){
  83. Return db. ReadFullTable (TableName );
  84. }

  85. // Another wrapper function...
  86. Function DeleteTableContents (){
  87. Db. DeleteTableContents (TableName );
  88. }


This piece of code is intended for you to stick on your main camera. In fact, I am not very familiar with the database, and I do not understand it in many places, I hope that some of you can annotate this code in detail so that we can fully absorb this cainiao-level code...

9. Let's take a look at our running results below:

This is the result of running in Unity3D. What will happen to the data operation?

We can see that our data operations can be successful. After testing, other buttons can also have the corresponding effect. Then we can see whether the desired database file is generated:

Let's take a look:

No, we generated the database file we wanted. Let's take a look at the data in this file:

  1. We have succeeded. After testing, when we operate on the data in the database, the data in our Unity3D will change accordingly. Therefore, this time we have succeeded. Here, we would like to thank the original article for its support. Hope that the passing experts will not spray it...

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.