jeudi 18 juin 2015

SQLite Database lock Mutli Query

Hello i have abit of a problem with my C# script basically it is supposed to run through a list array then update values on a sqlite database but it seems to lock up when trying to update the database this is my code

using UnityEngine;
using System.Collections;
using Parse;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using Mono.Data.Sqlite; 
using System.Data; 
using System.Threading.Tasks;

public class MyTestArray : MonoBehaviour {

    public int MyInt;
    // Use this for initialization
    void Start () {
        int[] pets = { 1, 2, 3, 4, 5 };

        // ... Loop with the foreach keyword.
        foreach (int value in pets) {
            Debug.Log (value);

            MyInt = value;

            string sqlQuery;
            string conn = "";
            #if UNITY_EDITOR
            conn = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
            #elif UNITY_IPHONE

            conn = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
            #elif UNITY_STANDALONE_WIN
            conn = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
            #elif UNITY_ANDROID
            conn = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
            #endif
            IDbConnection dbconn;
            dbconn = (IDbConnection)new SqliteConnection (conn);
            dbconn.Open (); //Open connection to the database.
            IDbCommand dbcmd = dbconn.CreateCommand ();
            PlayerPrefs.SetString ("Question", "1");

            sqlQuery = "select  SUM(qo.Score) from [Answer] as a inner join Questionas qo on a.Question= qo.QuestionI inner join Question as q on qo.QuestionId = q.QuestionId where q.QID=" + value;
            dbcmd.CommandText = sqlQuery;
            IDataReader reader = dbcmd.ExecuteReader ();
            while (reader.Read()) {
                int RiskNumber = reader.GetInt32 (0);
                Debug.Log (RiskNumber);
                string sqlQuery2 = "";
                string conn2 = "";
                #if UNITY_EDITOR
                conn2 = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
                #elif UNITY_IPHONE

                conn2 = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
                #elif UNITY_STANDALONE_WIN
                conn2 = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
                #elif UNITY_ANDROID
                conn2 = "URI=file:" + Application.persistentDataPath + "/" + "DB.db";
                #endif
                IDbConnection dbconn2;
                dbconn2 = (IDbConnection)new SqliteConnection (conn2);
                dbconn2.Open (); //Open connection to the database.
                IDbCommand dbcmd2 = dbconn2.CreateCommand ();
                if (MyInt == 1){
                    sqlQuery2 = "UPDATE UserScore SET MyScore1="+RiskNumber;
                } else if (MyInt == 2){
                    sqlQuery2 = "UPDATE UserScore SET MyScore2="+RiskNumber;
                } else if (MyInt == 3){
                    sqlQuery2 = "UPDATE UserScore SET MyScore3="+RiskNumber;
                } else if (MyInt == 4){
                    sqlQuery2 = "UPDATE UserScore SET MyScore4="+RiskNumber;
                } else if (MyInt == 5){
                    sqlQuery2 = "UPDATE UserScore SET MyScore5="+RiskNumber;
                }

                Debug.Log (sqlQuery2);
                dbcmd2.CommandText = sqlQuery2;
                IDataReader reader2 = dbcmd2.ExecuteReader ();


                reader2.Close ();
                reader2 = null;
                dbcmd2.Dispose ();
                dbcmd2 = null;
                dbconn2.Close ();
                dbconn2 = null; 
            }




            reader.Close ();
            reader = null;
            dbcmd.Dispose ();
            dbcmd = null;
            dbconn.Close ();
            dbconn = null;  
        }   
    }

    }

Is it the way i am creating the query or have i missed something any help would be fantastic cheers

Aucun commentaire:

Enregistrer un commentaire