Unity3D direct connection to Sqlite Database

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. 
   
6. Class dbAccess {
   
7. // Variables for basic query access
   
8. Private var connection: String; // database connection String, used to establish a connection with a specific data source
   
9. Private var dbcon: IDbConnection; // connection object of IDbConnection, which is actually a Class Object
   
10. 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:
    
11. // Create an IDbConnection connection object, assign a database command to a string, and use this string and the connection object
    
12. // You can create (new) An IDbCommand object, and then run this command using the provided method.
    
13. Private var reader: IDataReader; // reader is used to read one or more result streams.
    
14. 
    
15. Function OpenDB (p: String ){
    
16. Connection = "URI = file:" + p; // we set the connection to our database
    
17. Dbcon = new SqliteConnection (connection );
    
18. Dbcon. Open (); // Open the database connection operation
    
19. }
    
20. 
    
21. Function BasicQuery (q: String, r: boolean) {// run a baic Sqlite query
    
22. Dbcmd = dbcon. CreateCommand (); // create empty command
    
23. Dbcmd. CommandText = q; // fill the command
    
24. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader returns the IDataReader object and creates the IDataReader object
    
25. If (r) {// if we want to return the reader
    
26. Return reader; // return the reader returns the read object, that is, what is read
    
27. }
    
28. }
    
29. 
    
30. // This returns a 2 dimen1_arraylist with all
    
31. // Data from the table requested
    
32. Function ReadFullTable (tableName: String ){
    
33. Var query: String;
    
34. Query = "SELECT * FROM" + tableName;
    
35. Dbcmd = dbcon. CreateCommand ();
    
36. Dbcmd. CommandText = query;
    
37. Reader = dbcmd. ExecuteReader ();
    
38. Var readArray = new ArrayList ();
    
39. While (reader. Read ()){
    
40. Var lineArray = new ArrayList ();
    
41. For (var I = 0; I <reader. FieldCount; I ++)
    
42. LineArray. Add (reader. GetValue (I); // This reads the entries in a row
    
43. ReadArray. Add (lineArray); // This makes an array of all the rows
    
44. }
    
45. Return readArray; // return matches
    
46. }
    
47. 
    
48. // This function deletes all the data in the given table. Forever. watch out! Use sparingly, if at all
    
49. Function DeleteTableContents (tableName: String ){
    
50. Var query: String;
    
51. Query = "delete from" + tableName;
    
52. Dbcmd = dbcon. CreateCommand ();
    
53. Dbcmd. CommandText = query;
    
54. Reader = dbcmd. ExecuteReader ();
    
55. }
    
56. 
    
57. Function CreateTable (name: String, col: Array, colType: Array) {// Create a table, name, column array, column type array
    
58. Var query: String;
    
59. Query = "create table" + name + "(" + col [0] + "" + colType [0];
    
60. For (var I = 1; I
61. Query + = "," + col + "" + colType;
    
62. }
    
63. Query + = ")";
    
64. Dbcmd = dbcon. CreateCommand (); // create empty command
    
65. Dbcmd. CommandText = query; // fill the command
    
66. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader
    
67. }
    
68. Function InsertIntoSingle (tableName: String, colName: String, value: String) {// single insert
    
69. Var query: String;
    
70. Query = "insert into" + tableName + "(" + colName + ")" + "VALUES (" + value + ")";
    
71. Dbcmd = dbcon. CreateCommand (); // create empty command
    
72. Dbcmd. CommandText = query; // fill the command
    
73. Reader = dbcmd. ExecuteReader (); // execute command which returns a reader
    
74. }
    
75. Function insertintospecpacific (tableName: String, col: Array, values: Array) {// Specific insert with col and values
    
76. Var query: String;
    
77. Query = "insert into" + tableName + "(" + col [0];
    
78. For (var I = 1; I
79. Query + = "," + col;
    
80. }
    
81. Query + = ") VALUES (" + values [0];
    
82. For (I = 1; I
83. Query + = "," + values;
    
84. }
    
85. Query + = ")";
    
86. Dbcmd = dbcon. CreateCommand ();
    
87. Dbcmd. CommandText = query;
    
88. Reader = dbcmd. ExecuteReader ();
    
89. }
    
90. 
    
91. Function InsertInto (tableName: String, values: Array) {// basic Insert with just values
    
92. Var query: String;
    
93. Query = "insert into" + tableName + "VALUES (" + values [0];
    
94. For (var I = 1; I
95. Query + = "," + values;
    
96. }
    
97. Query + = ")";
    
98. Dbcmd = dbcon. CreateCommand ();
    
99. Dbcmd. CommandText = query;
    
100. Reader = dbcmd. ExecuteReader ();
     
101. }
     
102. 
     
103. // This function reads a single column
     
104. // WCol is the WHERE column, wPar is the operator you want to use to compare,
     
105. // And wValue is the value you want to compare against.
     
106. // Ex.-SingleSelectWhere ("puppies", "breed", "earType", "=", "floppy ")
     
107. // Returns an array of matches from the command: SELECT breed FROM puppies WHERE earType = floppy;
     
108. Function SingleSelectWhere (tableName: String, itemToSelect: String, wCol: String, wPar: String, wValue: String) {// Selects a single Item
     
109. Var query: String;
     
110. Query = "SELECT" + itemToSelect + "FROM" + tableName + "WHERE" + wCol + wPar + wValue;
     
111. Dbcmd = dbcon. CreateCommand ();
     
112. Dbcmd. CommandText = query;
     
113. Reader = dbcmd. ExecuteReader ();
     
114. Var readArray = new Array ();
     
115. While (reader. Read ()){
     
116. ReadArray. Push (reader. GetString (0); // Fill array with all matches
     
117. }
     
118. Return readArray; // return matches
     
119. }
     
120. 
     
121. 
     
122. Function CloseDB (){
     
123. Reader. Close (); // clean everything up
     
124. Reader = null;
     
125. Dbcmd. Dispose ();
     
126. Dbcmd = null;
     
127. Dbcon. Close ();
     
128. Dbcon = null;
     
129. }
     
130. 
     
131. }

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. 
   
6. This script is a GUI script-attach it to your main camera.
   
7. It creates/opens a SQLite database, and with the GUI you can read and write to it.
   
8. */
   
9. 
   
10. // This is the file path of the database file we want to use
    
11. // Right now, it'll load TestDB. sqdb in the project's root folder.
    
12. // If one doesn't exist, it will be automatically created.
    
13. Public var DatabaseName: String = "TestDB. sqdb ";
    
14. 
    
15. // This is the name of the table we want to use
    
16. Public var TableName: String = "TestTable ";
    
17. Var db: dbAccess;
    
18. 
    
19. Function Start (){
    
20. // Give ourselves a dbAccess object to work with, and open it
    
21. 
    
22. 
    
23. 
    
24. Db = new dbAccess ();
    
25. Db. OpenDB (DatabaseName );
    
26. // Let's make sure we 've ve got a table to work with as well!
    
27. Var tableName = TableName;
    
28. Var columnNames = new Array ("firstName", "lastName ");
    
29. Var columnValues = new Array ("text", "text ");
    
30. Try {db. CreateTable (tableName, columnNames, columnValues );
    
31. }
    
32. Catch (e) {// Do nothing-our table was already created
    
33. //-We don't care about the error, we just don't want to see it
    
34. }
    
35. }
    
36. 
    
37. // These variables just hold info to display in our GUI
    
38. Var firstName: String = "First Name ";
    
39. Var lastName: String = "Last Name ";
    
40. Var DatabaseEntryStringWidth = 100;
    
41. Var scrollPosition: Vector2;
    
42. Var databaseData: ArrayList = new ArrayList ();
    
43. 
    
44. // This GUI provides us with a way to enter data into our database
    
45. // As well as a way to view it
    
46. Function OnGUI (){
    
47. GUI. Box (Rect (25, 25, Screen. width-50, Screen. height-50), "Data ");
    
48. GUILayout. BeginArea (Rect (50, 50, Screen. width-100, Screen. height-100 ));
    
49. // This first block allows us to enter new entries into our table
    
50. GUILayout. BeginHorizontal ();
    
51. FirstName = GUILayout. TextField (firstName, GUILayout. Width (DatabaseEntryStringWidth ));
    
52. LastName = GUILayout. TextField (lastName, GUILayout. Width (DatabaseEntryStringWidth ));
    
53. 
    
54. // LastName = GUILayout. TextField ();
    
55. GUILayout. EndHorizontal ();
    
56. 
    
57. If (GUILayout. Button ("Add to database ")){
    
58. // Insert the data
    
59. InsertRow (firstName, lastName );
    
60. // And update the readout of the database
    
61. DatabaseData = ReadFullTable ();
    
62. }
    
63. // This second block gives us a button that will display/refresh the contents of our database
    
64. GUILayout. BeginHorizontal ();
    
65. If (GUILayout. Button ("Read Database "))
    
66. DatabaseData = ReadFullTable ();
    
67. If (GUILayout. Button ("Clear "))
    
68. DatabaseData. Clear ();
    
69. GUILayout. EndHorizontal ();
    
70. 
    
71. GUILayout. Label ("Database Contents ");
    
72. ScrollPosition = GUILayout. BeginScrollView (scrollPosition, GUILayout. Height (100 ));
    
73. For (var line: ArrayList in databaseData ){
    
74. GUILayout. BeginHorizontal ();
    
75. For (var s in line ){
    
76. GUILayout. Label (s. ToString (), GUILayout. Width (DatabaseEntryStringWidth ));
    
77. }
    
78. GUILayout. EndHorizontal ();
    
79. }
    
80. 
    
81. GUILayout. EndScrollView ();
    
82. If (GUILayout. Button ("Delete All Data") {unity3dand sqlitedata warehouse are directly connected to .rar (168.27 KB, download times: 239)
    
83. 
    
84. 
    
85. Unity3dand sqlitedata warehouse direct connection .rar (446.06 KB, downloads: 584)
    
86. 
    
87. 
    
88. 
    
89. 
    
90. 
    
91. DeleteTableContents ();
    
92. DatabaseData = ReadFullTable ();
    
93. }
    
94. GUILayout. EndArea ();
    
95. }
    
96. 
    
97. // Wrapper function for inserting our specific entries into our specific database and table for this file
    
98. Function InsertRow (firstName, lastName ){
    
99. Var values = new Array ("'" + firstName + "'"), ("'" + lastName + "'"));
    
100. Db. InsertInto (TableName, values );
     
101. }
     
102. 
     
103. // Wrapper function, so we only mess with our table.
     
104. Function ReadFullTable (){
     
105. Return db. ReadFullTable (TableName );
     
106. }
     
107. 
     
108. // Another wrapper function...
     
109. Function DeleteTableContents (){
     
110. Db. DeleteTableContents (TableName );
     
111. }

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