C# SqliteConnection.CreateCommand方法代码示例

    public Store(string fname, string cachedir)
    {
        updates = new ArrayList ();
        newchans = new ArrayList ();
        connection = new SqliteConnection ("URI=file:" + fname);
        connection.Open ();

        IDbCommand dbcmd = connection.CreateCommand ();
        // setting encoding only works on sqlite3
        //dbcmd.CommandText = "PRAGMA encoding = \"UTF-8\";";
        //if (dbcmd.ExecuteNonQuery () != 0) {
        //	throw new Exception ("Couldn't set encoding");
        //}
        string sql = "PRAGMA table_info(channel)";
        dbcmd = connection.CreateCommand ();
        dbcmd.CommandText = sql;
        try {
            IDataReader result = dbcmd.ExecuteReader ();
         			if (!result.Read ()) {
         				// schema doesn't exist.
         				System.Console.WriteLine ("Creating new database schema");
         				CreateSchema ();
            }
            result.Close ();
        } catch (Exception e) {
            // TODO: catch further exceptions here.
            System.Console.WriteLine ("Error executing reader: {0}", e.ToString ());
        }
        _cachedir = cachedir;
        InitializeCache ();
        // TODO: catch problems making the cache dir
    }

    // Use this for initialization
    void Start()
    {

        //E:\Etude\Projet\Projet_4e_semestre\GITHUB\New_Unity_Project\Assets


        const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
        IDbConnection dbcon = new SqliteConnection(connectionString);
        IDbCommand dbcmd = dbcon.CreateCommand();
        try
        {
            dbcon.Open();

            const string sql = "select nomcarte from carte";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string NomCarte = reader.GetString(0);
                Debug.Log("Carte: " + NomCarte + "\n");
            }
            reader.Dispose();

        }
        catch (Exception ex)
        {
            Debug.Log(ex.Message);
        }
        dbcmd.Dispose();
        dbcon.Close();

    }

    // Use this for initialization
    void Start () {
        const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
        IDbConnection dbcon = new SqliteConnection(connectionString);
        IDbCommand dbcmd = dbcon.CreateCommand();
        try
        {
            dbcon.Open();

            const string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.IDCARTE = '1' AND atk.IDATK='1'";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                IDCARTE = reader.GetInt32(0);
            }
            reader.Dispose();

        }
        catch (Exception ex)
        {
            Debug.Log(ex.Message);
        }
        dbcmd.Dispose();
        dbcon.Close();
    }

    public void init(int idcarte, string numserie, int idatk, int idatk2)
    {
        this.idcarte = idcarte;
        this.numserie = numserie;

        xdeck = this.transform.position.x;
        ydeck = this.transform.position.y;
        zdeck = this.transform.position.z;
        ObservJ1 = false;
        ObservJ2 = false;

        mettredsdeck = false;

        const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";

        IDbConnection dbcon = new SqliteConnection(connectionString);
        IDbCommand dbcmd = dbcon.CreateCommand();
        try
        {
            dbcon.Open();

            string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk + "";

            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                NomCarte = reader.GetString(0);
                puisscrea = reader.GetInt32(1);
                vitcrea = reader.GetInt32(2);
                typecrea = reader.GetString(3);
                pvcrea = reader.GetInt32(4);
                nomatk = reader.GetString(5);
                dommatk = reader.GetInt32(6);
                vitatk = reader.GetInt32(7);
            }
            reader.Dispose();

            string sql2 = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk2 + "";
            dbcmd.CommandText = sql2;

            IDataReader reader2 = dbcmd.ExecuteReader();
            while (reader2.Read())
            {
                nomatk2 = reader2.GetString(5);
                dommatk2 = reader2.GetInt32(6);
                vitatk2 = reader2.GetInt32(7);
            }
            reader2.Dispose();
        }
        catch (Exception ex)
        {
            Debug.Log(ex.Message);
        }
        dbcmd.Dispose();
        dbcon.Close();

    }

 public static IDataReader ExecuteSQL(string db, string sql)
 {
     var connection = new SqliteConnection(db);
     connection.Open();
     var command = connection.CreateCommand();
     command.CommandText = sql;
     var result = command.ExecuteReader();
     connection.Close();
     return result;
 }

    public Database(string file)
    {
        bool create = false;
        if (!File.Exists (file)) {
            create = true;
        }

        try {
            StreamReader srVersion = new StreamReader (file+".version");
            dbVersion = srVersion.ReadToEnd ();
            if (dbVersion != null) {
                dbVersion = dbVersion.Trim();
            }
            srVersion.Close();
        }
        catch {
            dbVersion = null;
        }

        string connectionString = "URI=file:"+file;
        connection = new SqliteConnection(connectionString);
        connection.Open();
        command = connection.CreateCommand();

        if (create) {
            Conf.EmptyCache();
            Assembly thisAssembly = Assembly.GetEntryAssembly ();
            Stream stream = thisAssembly.GetManifestResourceStream("Database.sql");
            if (stream != null) {
                StreamReader sr = new StreamReader (stream);
                string sql = sr.ReadToEnd();
                command.CommandText = sql;
                command.ExecuteNonQuery();

                StreamWriter swVersion = new StreamWriter (file+".version", false);
                swVersion.Write (Defines.VERSION);
                swVersion.Close();

                dbVersion = Defines.VERSION;
            }
            else {
                System.Console.WriteLine("Error creating the database");
            }
        }

        if (dbVersion == null || !dbVersion.Equals (Defines.VERSION)) {
            UpdateDatabase(file, dbVersion);
        }
    }

    // Hier wird eine gegebene Drone in die Tabelle DRONEN eingefüllt
    public void addDrone(Drone drone)
    {
        IDbConnection _connection = new SqliteConnection(_strDBName);
        IDbCommand _command = _connection .CreateCommand();
        string sql;

        _connection .Open();

        sql = "INSERT INTO DRONEN (DRONENNAME,AKTUELLERKNOTEN,HOMEPUNKTID, STATUS,CARTOSHOW) Values ('"+ drone.getName()+"','2','2','0','ef')";
        _command.CommandText = sql;
        _command.ExecuteNonQuery();

        _command.Dispose();
        _command = null;
        _connection .Close();
        _connection.Dispose ();
        _connection = null;
        sql = null;
    }

    //Diese Methode Schreibt ein Gegebenes Auto in die Datenbank.
    // Das Auto wird als Parameter gegeben
    public void addauto(Autos autodaten)
    {
        IDbConnection _connection = new SqliteConnection(_strDBName);
        IDbCommand _command = _connection .CreateCommand();
        string sql;

        _connection .Open();

        sql = "INSERT INTO AUTOS (KENNZEICHEN, STATUS) Values ('"+ autodaten.getKennzeichen ()+"','"+autodaten.getStatus ()+"')";
        _command.CommandText = sql;
        _command.ExecuteNonQuery();

        _command.Dispose();
        _command = null;
        _connection .Close();
        _connection.Dispose ();
        _connection = null;
        //Debug.Log (autodaten.getKennzeichen ());
    }

    public ArrayList Select(string quer)
    {
        Resultd = new ArrayList();
        string connectionString = "URI=file:" + Application.dataPath + "/Qjournaldb.s3db";
        IDbConnection dbcon = new SqliteConnection(connectionString);
        dbcon.Open();
        IDbCommand dbcmd = dbcon.CreateCommand();
        dbcmd.CommandText = quer;
        IDataReader reader = dbcmd.ExecuteReader();
        while (reader.Read())
        {
            Resultd.Add(reader.GetString(2));
            //Debug.Log(reader.GetString(2));
        }

        // clean up
        reader.Dispose();
        dbcmd.Dispose();
        dbcon.Close();
        return Resultd;
    }

 // Hier wird überprüft ob eine bestimmte Tabelle in der Datenbank existieren. Die Anzahl der Funde wird Zurückgegeben. 1=Existiert; 0=Gibt es nicht
 public int abfrageexisttabelle(string Tabellenname)
 {
     IDbConnection _connection = new SqliteConnection(_strDBName);
     IDbCommand _command = _connection .CreateCommand();
     string sql;
     IDataReader _reader;
     _connection .Open();
     sql = "SELECT count(name) as Count FROM sqlite_master WHERE type='table' AND name='"+Tabellenname+"'";
     _command.CommandText = sql;
     _reader = _command.ExecuteReader();
     _reader.Read ();
     _connection .Close();
     _connection.Dispose ();
     _connection = null;
     _command.Dispose ();
     _command = null;
     int anzahl = System.Convert.ToInt32 (_reader ["Count"]);
     _reader.Close();
     _reader.Dispose ();
     _reader = null;
     return anzahl;
 }

    // Hier wird ein zufälliges PArkendes Auto zurückgesendet
    public Autos getrandomparkingcar()
    {
        IDbConnection _connection = new SqliteConnection(_strDBName);
        IDbCommand _command = _connection .CreateCommand();
        string sql;
        IDataReader _reader;

        _connection .Open();
        sql = "SELECT Count(KENNZEICHEN) as COUNT FROM AUTOS WHERE STATUS='2' ";
        _command.CommandText = sql;
        _reader = _command.ExecuteReader();
        _reader.Read ();
        int zufall = UnityEngine.Random.Range (1, System.Convert.ToInt32(_reader ["COUNT"]));
        _reader.Close ();
        sql = "SELECT * FROM AUTOS WHERE STATUS = '2' LIMIT "+ zufall;
        _command.CommandText = sql;
        _reader = _command.ExecuteReader();

        // Hier wird ein zufälliges Auto gesucht
        for (int i=0; i<zufall; i++) {
            _reader.Read();
                }
        Autos car = new Autos ();
        //Debug.Log (_reader ["KENNZEICHEN"]);
        car.setKennzeichen (System.Convert.ToString (_reader ["KENNZEICHEN"]));
        car.setStatus("2");
        _command.Dispose();
        _command = null;
        _connection .Close();
        _connection.Dispose ();
        _connection = null;
        _reader.Close ();
        _reader.Dispose ();
        _reader = null;
        return car;
    }

    //Hier wird die Datenbank wieder auf Anfang gesetzt. Alle Änderungen beim Letzten Ausführen des Programms werden gelöst
    public void allesaufanfang()
    {
        Debug.Log ("delete Car");
        IDbConnection _connection = new SqliteConnection(_strDBName);
        IDbCommand _command = _connection .CreateCommand();
        string sql;
        _connection.Open();
        sql = " Delete From AUTOS ";
        _command.CommandText = sql;
        _command.ExecuteNonQuery ();

        sql=" UPDATE PARKPLATZ SET KENNZEICHENFAHRZEUG='', FREI='1' WHERE FREI='0'";
        _command.CommandText = sql;
        _command.ExecuteNonQuery ();

        sql=" UPDATE DRONEN SET STATUS='0' WHERE STATUS > 0 ";
        _command.CommandText = sql;
        _command.ExecuteNonQuery ();

        _command.Dispose();
        _command = null;
        _connection .Close();
        _connection.Dispose ();
        _connection = null;
    }

    // Hier wird die Tabelle Drone gefüllt
    // Zurzeit ist es nur eine Drone
    public void fillTableDronen()
    {
        Debug.Log ("Dronen");
        IDbConnection _connection = new SqliteConnection(_strDBName);
        IDbCommand _command = _connection .CreateCommand();
        string sql;
        _connection.Open();
        sql = " Delete From DRONEN";
        _command.CommandText = sql;
        _command.ExecuteNonQuery ();

        _connection.Close ();
        _connection.Dispose ();
        _connection = null;
        _command.Dispose ();
        _command = null;

        Drone drone = new Drone ();
        drone.setName ("QuadCopter");drone.setStatus ("0");this.addDrone (drone);
        drone = null;
    }

    /// <summary>
    /// ////////////////////////////////////////Les fonctions liées aux gui
    /// </summary>

    void OnGUI()
    {
        //Debug.Log(idcarte);
        if (ObservJ1 == true)
        {
            if (GUI.Button(new Rect(710, 50, 200, 100), "Mettre dans le Deck"))
            {
                const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
                IDbConnection dbcon = new SqliteConnection(connectionString);
                IDbCommand dbcmd = dbcon.CreateCommand();
                try
                {
                    dbcon.Open();
                    string sql5 = "INSERT INTO j_deck_carte (IDDECK,IDCARTE) VALUES ('1'," + this.idcarte + ")";
                    dbcmd.CommandText = sql5;
                    IDataReader reader5 = dbcmd.ExecuteReader();
                    while (reader5.Read())
                    {
                    }
                    reader5.Dispose();
                }
                catch (Exception ex)
                {
                    Debug.Log(ex.Message);
                }
                dbcmd.Dispose();
                dbcon.Close();
            }
            if (GUI.Button(new Rect(710, 270, 200, 100), "Enlever du Deck"))
            {
                idcartebddJ1 = 0;
                const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
                IDbConnection dbcon = new SqliteConnection(connectionString);
                IDbCommand dbcmd = dbcon.CreateCommand();
                try
                {
                    dbcon.Open();
                    string sql6 = "DELETE FROM j_deck_carte WHERE IDDECK='1' AND IDCARTE='" + this.idcarte + "'";
                    dbcmd.CommandText = sql6;
                    IDataReader reader6 = dbcmd.ExecuteReader();
                    while (reader6.Read())
                    {
                    }
                    reader6.Dispose();
                }
                catch (Exception ex)
                {
                    Debug.Log(ex.Message);
                }
                dbcmd.Dispose();
                dbcon.Close();
            }
            if (GUI.Button(new Rect(710, 490, 200, 100), "Reposer"))
            {
                this.gameObject.transform.position = new Vector3(xdeck, ydeck, zdeck);
                ObservJ1 = false;
            }

        }


        if (ObservJ2 == true)
        {
            if (GUI.Button(new Rect(710, 50, 200, 100), "Mettre dans le Deck"))
            {
                const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
                IDbConnection dbcon = new SqliteConnection(connectionString);
                IDbCommand dbcmd = dbcon.CreateCommand();
                try
                {
                    dbcon.Open();
                    string sql7 = "INSERT INTO j_deck_carte (IDDECK,IDCARTE) VALUES ('2','" + this.idcarte + "')";
                    dbcmd.CommandText = sql7;
                    IDataReader reader7 = dbcmd.ExecuteReader();
                    while (reader7.Read())
                    {
                    }
                    reader7.Dispose();
                }
                catch (Exception ex)
                {
                    Debug.Log(ex.Message);
                }
                dbcmd.Dispose();
                dbcon.Close();
            }
            if (GUI.Button(new Rect(710, 270, 200, 100), "Enlever du Deck"))
            {
                idcartebddJ2 = 0;
                const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
                IDbConnection dbcon = new SqliteConnection(connectionString);
                IDbCommand dbcmd = dbcon.CreateCommand();
                try
                {
                    dbcon.Open();
                    string sql8 = "DELETE FROM j_deck_carte WHERE IDDECK='2' AND IDCARTE='" + this.idcarte + "'";
//.........这里部分代码省略.........

    /// <summary>Creates the database if necessary and returns the current
    /// iteration.</summary>
    protected int InitializeDatabase()
    {
      IDbConnection dbcon = new SqliteConnection(_db_con);
      dbcon.Open();
      IDbCommand dbcmd = dbcon.CreateCommand();
      string sql = "CREATE TABLE crawl (iter INTEGER, Address TEXT, Hits INTEGER, " +
        "Rtt REAL, Hops REAL)";
      dbcmd.CommandText = sql;
      try {
        dbcmd.ExecuteNonQuery();
      } catch {
      }

      sql = "CREATE TABLE iterations (iter INTEGER, time INTEGER)";
      dbcmd.CommandText = sql;
      try {
        dbcmd.ExecuteNonQuery();
      } catch {
      }

      // Set _current_iter to how many iterations there exist already
      sql = "SELECT iter FROM iterations";
      dbcmd.CommandText = sql;
      int current_iter = dbcmd.ExecuteNonQuery();
      dbcmd.Dispose();

      dbcon.Close();
      return current_iter;
    }