C# SQLite.SQLiteCommand类代码示例

 /** public void createUc()  - create user controls (display reminders)         * 
  */
 public void createUc() 
 {
     int i = 0;
     UserControl1[] uc = new UserControl1[200];
     SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
     new_con.Open();
     SQLiteCommand get = new SQLiteCommand("SELECT * FROM  reminder LIMIT 0 , 30", new_con);
     String header;
     SQLiteDataReader reader;
     reader = get.ExecuteReader();
     while (reader.Read())
     {
         //data[i,0] = new ArrayList();
         string[] str = new string[3];
         str[0]=reader[0].ToString();
         str[1]=DateTime.Parse(reader[1].ToString()).ToShortDateString();
         str[2]=DateTime.Parse(reader[5].ToString()).ToShortTimeString();
         reminderList.Add(str);
         header = String.Format("{1,-20}   {0,5}", reader[2].ToString(), DateTime.Parse(reader[1].ToString()).ToShortDateString());
         uc[i] = new UserControl1();
         uc[i].setContent(Convert.ToInt16(reader[0].ToString()), reader[3].ToString(), reader[4].ToString(), DateTime.Parse(reader[5].ToString()).ToShortTimeString() , header);
         WrapPanel1.Children.Add(uc[i]);
         i++;   
     }
     reminderlistarray = reminderList.ToArray();
 }

        private void btnSubmit_Click(object sender, EventArgs e)
        {
            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_conn = new SQLiteConnection("Data Source=database.db;Version=3;Compress=True;");
            sqlite_conn.Open();
            //UPDATE People set [email protected],stxtLname = @stxtLName,
            SQLiteCommand sqlite_cmd = new SQLiteCommand(@"Update ProgramConfig Set sPassword = @password, sEmail = @email, sSchoolName = @name,
                            sAddress = @address, sCity = @city, sState = @state, sZip = @zip, sLatitude = @latitude,
                            sLongitude = @longitude, sImageFile = @ImageFile Where id = 1", sqlite_conn);

            sqlite_cmd.Parameters.Add("@password", SqlDbType.Text).Value = parentForm.school.password;
            sqlite_cmd.Parameters.Add("@email", SqlDbType.Text).Value = parentForm.school.email;
            sqlite_cmd.Parameters.Add("@name", SqlDbType.Text).Value = parentForm.school.name;
            sqlite_cmd.Parameters.Add("@address", SqlDbType.Text).Value = parentForm.school.address;
            sqlite_cmd.Parameters.Add("@city", SqlDbType.Text).Value = parentForm.school.city;
            sqlite_cmd.Parameters.Add("@state", SqlDbType.Text).Value = parentForm.school.state;
            sqlite_cmd.Parameters.Add("@zip", SqlDbType.Text).Value = parentForm.school.zip;
            sqlite_cmd.Parameters.Add("@latitude", SqlDbType.Text).Value = parentForm.school.latitude;
            sqlite_cmd.Parameters.Add("@longitude", SqlDbType.Text).Value = parentForm.school.longitude;
            sqlite_cmd.Parameters.Add("@ImageFile", SqlDbType.Text).Value = parentForm.school.ImageFile;
            sqlite_cmd.CommandType = CommandType.Text;

            try
            {
                int i = sqlite_cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            parentForm.RefreshLogo(parentForm.school.ImageFile);
            this.Close();
        }

        public List<Drop> GetDrops(int mobId)
        {
            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

            // But how do we read something out of our table ?
            // First lets build a SQL-Query again:
            sqlite_cmd.CommandText = "SELECT * FROM droplist where mobId = " + mobId;

            // Now the SQLiteCommand object can give us a DataReader-Object:
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            List<Drop> droplist = new List<Drop>(10);

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                int itemId = Convert.ToInt32(sqlite_datareader["itemId"]);
                int min = Convert.ToInt32(sqlite_datareader["min"]);
                int max = Convert.ToInt32(sqlite_datareader["max"]);
                int category = Convert.ToInt32(sqlite_datareader["category"]);
                int chance = Convert.ToInt32(sqlite_datareader["chance"]);

                droplist.Add(new Drop(mobId, itemId, min, max, category, chance));
            }
            return droplist;
        }

 public bool GetSchool()
 {
     bool bReturn = false;
     sqlite_cmd = new SQLiteCommand("Select * from ProgramConfig where id = 1", sqlite_conn);
     // Now the SQLiteCommand object can give us a DataReader-Object:
     sqlite_datareader = sqlite_cmd.ExecuteReader();
     // The SQLiteDataReader allows us to run through the result lines:
     while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
     {
         Console.WriteLine(sqlite_datareader["sLatitude"].ToString());
         school = new School(sqlite_datareader["sPassword"].ToString(), sqlite_datareader["sEmail"].ToString(), sqlite_datareader["sSchoolName"].ToString(),
             sqlite_datareader["sAddress"].ToString(), sqlite_datareader["sCity"].ToString(), sqlite_datareader["sState"].ToString(), sqlite_datareader["sZip"].ToString(),
             sqlite_datareader["sLatitude"].ToString(), sqlite_datareader["sLongitude"].ToString(), sqlite_datareader["sImageFile"].ToString());
         try
         {
             string sExistingDatabaseVersion = sqlite_datareader["sDatabaseVersion"].ToString();
             if(OldDBVersion(sExistingDatabaseVersion,DatabaseVersion))
             {
                 UpgradeDB(sExistingDatabaseVersion, DatabaseVersion);
             }
         }
         catch (Exception e)
         {
             Console.WriteLine(e.ToString());
             Console.WriteLine("No database version available.  Need to upgrade to DB Version 1.0 after backing things up.");
         }
         bReturn = true;
     }
     return bReturn;
 }

 internal SQLiteDataReader(SQLiteCommand pCmd, CommandBehavior cmdBehavior)
 {
     if (pCmd == null)
         throw new ArgumentNullException();
     if( pCmd.GetSQLStatementCount() <= 0 )
         throw new ArgumentException("CommandText doesn't contain any SQL statements in SQLiteCommand");
     mpCmd = pCmd;
     mCmdBehavior = cmdBehavior;
 }

        public OneSQLStatement( SQLiteCommand pCmd, String cmdText, ArrayList paramNames )
        {
            mCmd = pCmd;
            mCmdText = cmdText;
            mpParamNames = paramNames;
            if (mCmd == null)
                throw new ArgumentNullException("pCmd");

            if (mCmdText == null)
                throw new ArgumentNullException("cmdText");

            if (mCmdText.Length == 0)
                throw new ArgumentException("The command text must be non-empty");
        }

        void LoadUserSelect()
        {
            comboUser.Items.Clear();
            SQLiteCommand sqlite_cmd = new SQLiteCommand("SELECT * FROM People", sqlite_conn);

            // Now the SQLiteCommand object can give us a DataReader-Object:
            sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                //Console.WriteLine(String.Format("{0}, {1}", sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
                comboUser.Items.Add(String.Format("{0}: {1}, {2}", sqlite_datareader["id"], sqlite_datareader["stxtLname"], sqlite_datareader["stxtFname"]));
            }
        }

        /// <summary>
        /// Simplify the creation of a SQLite command object by allowing
        /// a CommandType and Command Text to be provided
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SQLiteCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers");
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection object</param>
        /// <param name="commandType">CommandType (TableDirect, Text)</param>
        /// <param name="commandText">CommandText</param>
        /// <returns>A valid SQLiteCommand object</returns>
        public static SQLiteCommand CreateCommand(SQLiteConnection connection, CommandType commandType, string commandText )
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported.");

            // If we receive parameter values, we need to figure out where they go
            if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" );

            // Create a SQLiteCommand
            SQLiteCommand cmd = new SQLiteCommand(commandText, connection );
            cmd.CommandType = CommandType.Text ;

            return cmd;
        }

        public DataTable Insert(string queryString)
        {
            try
            {
                lock(DataBase._conn)
                {
                SQLiteCommand Cmd = new SQLiteCommand();
                Cmd = DataBase._conn.CreateCommand();
                Cmd.CommandText = queryString;
                Cmd.CommandType=CommandType.Text ;
                Cmd.ExecuteNonQuery();

                }
                return null;
            }catch(Exception e){
                Debug.WriteLine("DataBase Insert Problem: "+ e.Message);
                return null;
            }
        }

 private void btnAddGuardian_Click(object sender, EventArgs e)
 {
     SearchForUser GetContact = new SearchForUser(GUARDIAN_ASSOCIATION_REQUEST, this, sqlite_conn);
     GetContact.ShowDialog();
     Console.Write("{0}{1}", iUser, sRelationship);
     SQLiteCommand sqlite_cmd = new SQLiteCommand("INSERT INTO Guardians (id,iStudent,iPerson,sRelationship) VALUES (@id,@iStudent,@iPerson,@sRelationship)", sqlite_conn);
     sqlite_cmd.Parameters.Add("@id", SqlDbType.Int).Value = null;
     sqlite_cmd.Parameters.Add("@iStudent", SqlDbType.Int).Value = Int32.Parse(txtID.Text);
     sqlite_cmd.Parameters.Add("@iPerson", SqlDbType.Int).Value = iUser;
     sqlite_cmd.Parameters.Add("@sRelationship", SqlDbType.Text).Value = sRelationship;
     try
     {
         sqlite_cmd.ExecuteNonQuery();
     }
     catch (Exception ex)
     {
         throw new Exception(ex.Message);
     }
     LoadGuardians();
 }

        public MainWindow()
        {
            InitializeComponent();
            AddDinValues();
            AddPCBValues();
            AddRKValues();

            // create a new database connection:
            sqlite_conn = new SQLiteConnection("Data Source=ICTSupportInventory.db;Version=3;New=False;Compress=True;");

            // open the table for connection
            sqlite_conn.Open();

            // create a new SQL command:
            sqlite_cmd = sqlite_conn.CreateCommand();

            // create a new table to work with
            sqlite_cmd.CommandText = "CREATE TABLE Items (ProductType varchar(100), SerialNumber varchar(100), Location varchar(100), Availability varchar(50), Owner varchar(100), ETR varchar(100), Comments varchar(100));";

            // close the connection
            sqlite_conn.Close();

            // get values to populate all items 
            PopulateAllLists();
        }

            // create a new database connection:

        //ajouter voiture
      
      public bool ajouterVoiture(Voiture v)
        {

            try
            {
               
                sqlite_conn.Open();

       
                sqlite_cmd = sqlite_conn.CreateCommand();

        
                
                string req = "Insert Into Automobile(Annee, Immatriculation, Coulour, Marque, TypeV, AutoMoto) Values (" + v.Annee + ", '" + v.Immatriculation + "', '" + v.Coulour + "', '" + v.Marque + "', '" + v.TypeV + "', 'True');";

                 

                // Lets insert something into our new table:
                sqlite_cmd.CommandText = req;

                sqlite_cmd.ExecuteNonQuery();
   
                sqlite_conn.Close();
                return true;
            }
            catch (Exception)
            {
    
                return false;
            }
        }

 public void ExecuteQuery(string txtQuery)
 {
     SetConnection();
     sql_con.Open();
     sql_cmd = sql_con.CreateCommand();
     sql_cmd.CommandText = txtQuery;
     sql_cmd.ExecuteNonQuery();
     sql_con.Close();
 }

 public String getSecteur(int id)
 {
     sql_con.Open();
     sql_cmd = sql_con.CreateCommand();
     string CommandText = "SELECT nom FROM secteur WHERE id = " + id;
     DB = new SQLiteDataAdapter(CommandText, sql_con);
     DS.Reset();
     DB.Fill(DS);
     DT = DS.Tables[0];
     sql_con.Close();
     return (String)DT.Rows[0][0];
 }

 public DataTable getCarTable()
 {
     sql_con.Open();
     sql_cmd = sql_con.CreateCommand();
     string CommandText = "SELECT * FROM voiture";
     DB = new SQLiteDataAdapter(CommandText, sql_con);
     DS.Reset();
     DB.Fill(DS);
     DT = DS.Tables[0];
     sql_con.Close();
     return DT;
 }