n1gr3bVc

1using UnityEngine;
2using System.Data;
3using Mono.Data.Sqlite;
4using System.IO;
5using System.Collections.Generic;
6using TMPro;
7using UnityEngine.Networking;
8using System.Collections;
9
10
11public class SQLCrud : MonoBehaviour
12{
13    [SerializeField]
14    public List<QAClass> QuestionAnswers = new List<QAClass>();
15
16    public GameObject MCQHandler;
17    public TextMeshProUGUI detext;
18
19    private string connection;
20    string connectionString;
21    // Start is called before the first frame update
22    void Start()
23    {
24        ConnectionDB();
25    }
26
27    public void OldConnectionMethod()
28    {
29        QuestionAnswers = new List<QAClass>();
30
31        // connection = Path.Combine(Application.streamingAssetsPath, "/MCQDB_H.db");
32        /*        var loadDb = new UnityWebRequest("jar:file://" + Application.dataPath + "!/assets/" + "MCQBIO.db");
33                var filepath = string.Format("{0}/{1}", Application.persistentDataPath, "MCQBIO.db");
34                var dbPath = string.Format(@"Assets/StreamingAssets/{0}", "MCQBIO.db");*/
35        //while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
36        // then save to Application.persistentDataPath
37        //File.WriteAllBytes(filepath, loadDb.bytes);
38        //  connection = "URI=file:" + Application.streamingAssetsPath + "/MCQBIO.db";
39        //  connection = "jar:file://"  +  Application.dataPath + "/StreamingAssets/MCQBIO.db";
40
41
42
43
44        IDbConnection dbcon = new SqliteConnection(connection);
45        dbcon.Open();
46        IDataReader reader;
47
48        //dbcmd = dbcon.CreateCommand();
49        //string q_createTable =
50        // "CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, val INTEGER )";
51
52        //dbcmd.CommandText = q_createTable;
53        //reader = dbcmd.ExecuteReader();
54
55        //IDbCommand cmnd = dbcon.CreateCommand();
56        //cmnd.CommandText = "INSERT INTO my_table (id, val) VALUES (0, 5)";
57        //cmnd.ExecuteNonQuery();
58
59        IDbCommand cmnd_read = dbcon.CreateCommand();
60
61        string query = "SELECT * FROM QuestionAnswers";
62        cmnd_read.CommandText = query;
63        reader = cmnd_read.ExecuteReader();
64        while (reader.Read())
65        {
66            QAClass qa = new QAClass();
67            qa.QNo = int.Parse(reader[0].ToString());
68            qa.Question = reader[1].ToString();
69            qa.OptionA = reader[2].ToString();
70            qa.OptionB = reader[3].ToString();
71            qa.OptionC = reader[4].ToString();
72            qa.OptionD = reader[5].ToString();
73            qa.AnsKey = reader[6].ToString();
74            QuestionAnswers.Add(qa);
75            Debug.Log(qa.ToString());
76
77        }
78        if (QuestionAnswers != null && QuestionAnswers.Count > 0)
79        {
80            MCQHandler.SendMessage("SetUpQAList", QuestionAnswers, SendMessageOptions.DontRequireReceiver);
81            /*detext.text = "qa "+QuestionAnswers.Count;*/
82        }
83        dbcon.Close();
84    }
85    public void ConnectionDB()
86    {
87
88        if (Application.platform != RuntimePlatform.Android)
89        {
90            connectionString = "URI=file:" + Application.streamingAssetsPath + "/mcqbio.db";
91        }
92        else
93        {
94            connectionString = Application.persistentDataPath + "/mcqbio.db";
95            if (!File.Exists(connectionString))
96            {
97                                UnityWebRequest load = new UnityWebRequest("jar:file://" + Application.dataPath + "!/assets/" + "mcqbio.db");
98
99                                while (!load.isDone) { }
100
101                                //File.WriteAllBytes(connectionString, load.downloadedBytes);
102                StartCoroutine(GetText());
103            }
104        }
105
106        IDbConnection dbcon = new SqliteConnection(connectionString);
107        dbcon.Open();
108        IDataReader reader;
109
110        IDbCommand cmnd_read = dbcon.CreateCommand();
111
112        string query = "SELECT * FROM QuestionAnswers";
113        cmnd_read.CommandText = query;
114        reader = cmnd_read.ExecuteReader();
115        Debug.Log(" File count "+reader.FieldCount);
116        while (reader.Read())
117        {
118            QAClass qa = new QAClass();
119            qa.QNo = int.Parse(reader[0].ToString());
120            qa.Question = reader[1].ToString();
121            qa.OptionA = reader[2].ToString();
122            qa.OptionB = reader[3].ToString();
123            qa.OptionC = reader[4].ToString();
124            qa.OptionD = reader[5].ToString();
125            qa.AnsKey = reader[6].ToString();
126            Debug.Log("B4 adding to QA");
127            QuestionAnswers.Add(qa);
128            Debug.Log(qa.ToString());
129
130        }
131        if (QuestionAnswers != null && QuestionAnswers.Count > 0)
132        {
133            MCQHandler.SendMessage("SetUpQAList", QuestionAnswers, SendMessageOptions.DontRequireReceiver);
134            detext.text = "qa " + QuestionAnswers.Count;
135        }
136        dbcon.Close();
137    }
138    IEnumerator GetText()
139    {
140        UnityWebRequest www = UnityWebRequest.Get("jar:file://" + Application.dataPath + "!/assets/" + "mcqbio.db");
141        yield return www.Send();
142
143        if (www.isHttpError)
144        {
145            Debug.Log(www.error);
146        }
147        else
148        {
149            // Show results as text
150            Debug.Log(www.downloadHandler.text);
151
152            // Or retrieve results as binary data
153            byte[] results = www.downloadHandler.data;
154            File.WriteAllBytes(connectionString, results);
155        }
156
157    }
158}