C# SQLiteCommand.ExecuteNonQuery方法代码示例

        private static void InitConnection()
        {
            Console.WriteLine(Application.UserAppDataPath);
            string dataPath = Application.UserAppDataPath + "\\" + "data";

            if (Directory.Exists(dataPath) && File.Exists(dataPath + "\\database.db"))
            {
                sqlConnection =
                    new SQLiteConnection(
                        "Data Source=" + dataPath + "\\database.db" +
                        ";Version=3;New=False;Compress=True;UTF8Encoding=True;");
                sqlConnection.Open();

                sqlCommand = sqlConnection.CreateCommand();
            }
            else
            {
                if (!Directory.Exists(dataPath))
                {
                    Directory.CreateDirectory(dataPath);

                    ClearFileUnderPath(dataPath);
                }
                sqlConnection =
                    new SQLiteConnection(
                        "Data Source=" + dataPath + "\\database.db" +
                        ";Version=3;New=True;Compress=True;UTF8Encoding=True;");
                sqlConnection.Open();

                ClearFileUnderPath(dataPath);

                sqlCommand = sqlConnection.CreateCommand();

                sqlCommand.CommandText = "create table movie(fileLocation nvarchar primary key)";
                sqlCommand.ExecuteNonQuery();
                sqlCommand.CommandText = "create table music(fileLocation nvarchar primary key)";
                sqlCommand.ExecuteNonQuery();
                sqlCommand.CommandText = "create table file(fileLocation nvarchar primary key)";
                sqlCommand.ExecuteNonQuery();
            }

            //string sql = "select count(*) as c from sqlite_master where type ='table' and name ='movie'";

            //sqlCommand.CommandText =
            //    "create table movie(fileLocation nvarchar primary key)";
            //sqlCommand.ExecuteNonQuery();
            //sqlCommand.CommandText = "create table music(fileLocation nvarchar primary key)";
            //sqlCommand.ExecuteNonQuery();
            //sqlCommand.CommandText = "create table file(fileLocation nvarchar primary key)";
            //sqlCommand.ExecuteNonQuery();
        }

            // 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;
            }
        }

        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 void ExecuteQuery(string txtQuery)
 {
     SetConnection();
     sql_con.Open();
     sql_cmd = sql_con.CreateCommand();
     sql_cmd.CommandText = txtQuery;
     sql_cmd.ExecuteNonQuery();
     sql_con.Close();
 }

        static private void ExecuteQuery(string txtQuery)
        {
            lock(sql_con)
            {
                SetConnection();
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = txtQuery;

                sql_cmd.ExecuteNonQuery();
                sql_con.Close();

            }
            
        }

        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();
 }

        /** private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
         * delete reminder
         */
		private void ButtonYes_Click(object sender, System.Windows.RoutedEventArgs e)
		{
            SQLiteConnection  new_con = new  SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
            new_con.Open();
            if  (delete_status == 1)
            {  
                SQLiteCommand  get = new  SQLiteCommand("DELETE FROM reminder WHERE rid ='"  + Convert.ToInt32(id) + "'", new_con);
                get.ExecuteNonQuery();
                MainWindowStart  mainWindow = MainWindowStart.Instance;
                mainWindow.mainWindowUpdate();
            }
            else
            {
                SQLiteCommand  get = new  SQLiteCommand("DELETE FROM contact_detail WHERE cid ='"  + Convert.ToInt32(id) + "'", new_con);
                 get.ExecuteNonQuery();
                MainWindowStart  mainWindow = MainWindowStart.Instance;
                mainWindow.mainWindowUpdateContact();
            }
            new_con.Close();
             this.Close();
		}

        //ajouter moto
        public bool ajouterMoto(Moto v)
        {

            try
            {
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                string req = "Insert Into Automobile(Annee, Immatriculation, Cylindre , VitesseMax, AutoMoto) Values (" + v.Annee + ", '" + v.Immatriculation + "', " + v.Cylindre + ", " + v.VitesseMax + ", 'False');";
          
               // Lets insert something into our new table:
                sqlite_cmd.CommandText = req;
                sqlite_cmd.ExecuteNonQuery();
                
                sqlite_conn.Close();
                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }

 /** private void ButtonOk_Click(object sender, RoutedEventArgs e)
  *  update reminder
  */
 private void ButtonOk_Click(object sender, RoutedEventArgs e)
 {
     string date = ReplaceApostrophe(DatePicker1.SelectedDate.Value.ToShortDateString());
     string name = ReplaceApostrophe(ReminderName.Text);
     string note = ReplaceApostrophe(Note.Text);
     string snooze = ReplaceApostrophe(NumericUpDown1.Value.ToString());
     string time = ReplaceApostrophe(TimePicker.SelectedTime.Value.ToString());
     SQLiteConnection new_up = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
     new_up.Open();
     SQLiteCommand get = new SQLiteCommand("Update reminder set date='" + date + "',name='" + name + "', note='" + note + "',snooze='" + snooze + "',time='" + time + "' where rid='" + Convert.ToInt32(cnt) + "'", new_up);
     get.ExecuteNonQuery();
     new_up.Close();
     this.Close();
     MainWindowStart mainWindow = MainWindowStart.Instance;
     mainWindow.mainWindowUpdate();
 }

        /// <summary>
        /// Execute a SQLiteCommand (that returns no resultset) against the specified SQLiteConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(conn, CommandType.Text, "Update TableTransaction set OrderAmount = 500 where ProdId=?", new SQLiteParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid SQLiteConnection</param>
        /// <param name="commandType">The CommandType (TableDirect, Text)</param>
        /// <param name="commandText">The T-SQL command</param>
        /// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param>
        /// <returns>An int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
        {
            if( connection == null ) throw new ArgumentNullException( "connection" );

            // Create a command and prepare it for execution
            SQLiteCommand cmd = new SQLiteCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the SQLiteParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            if( mustCloseConnection )
                connection.Close();
            return retval;
        }

        private void ButtonOk_Click(object sender, RoutedEventArgs e)
        {
            int index = 0, n = 0;
            int[] array = new int[10000];
            string firstname = ReplaceApostrophe(TextBoxFirstName.Text);
            string lastname = ReplaceApostrophe(TextBoxLastName.Text);
            string mail = ReplaceApostrophe(TextBoxMail.Text);
            string address = ReplaceApostrophe(TextBoxAddress.Text);
            try
            {
                 int number= Convert.ToInt32(TextBoxPhone.Text);
                 SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
                 new_con.Open();
                 SQLiteCommand new_cnt = new SQLiteCommand("SELECT cid FROM  contact_detail", new_con);
                 SQLiteDataReader reader;
                 reader = new_cnt.ExecuteReader();
                 while (reader.Read())
                 {
                     array[index] = Convert.ToInt32(reader[0].ToString());
                     index++;
                 }

                 if (!(index == 0))
                 {
                     n = index;
                     insertionSort(array, n);
                     index = 0;
                     while (index < n)
                     {
                         if (!(array[index] == index))
                             break;
                         index++;
                     }
                 }
                 SQLiteCommand get = new SQLiteCommand("INSERT INTO contact_detail VALUES ('" + index + "', '" + firstname + "', '" + lastname + "', '" + Convert.ToInt32(number) + "', '" + address + "', '" + mail + "','','')", new_con);
                 get.ExecuteNonQuery();
                 new_con.Close();
                 this.Close();
                 MainWindowStart mainWindow = MainWindowStart.Instance;
                 mainWindow.mainWindowUpdateContact();
            }
            catch
            {                
                MessageBox.Show("Phone number is invalid!");
            }
                

            
        }

        /// <summary>
        /// update user 
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <param name="owner"></param>
        /// <param name="oldnick"></param>
        internal void UpdateUserIn(string user, string password, string owner ,string oldnick)
        {
            string file = AppDomain.CurrentDomain.BaseDirectory +
                      "Database\\Shamia.db";
            if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            Connection = new SQLiteConnection("Data Source=" + file + ";Version=3" +
                                              ";New=False;Compress=True");

            try
            {
                Connection.Open();
                C = Connection.CreateCommand();
                C.CommandText = "UPDATE myusers SET user ='" + user + "',"+
                    "password='" + password + "'," +
                    "owner='" + owner + "'" +
                    " WHERE user='" + oldnick + "'";
                C.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MyDelegates.OnDebugMessageCallBack(ex.StackTrace);
            }
            finally
            {
                if (Connection.State != ConnectionState.Closed) { Connection.Close(); }
            }
        }

        /** private void ButtonOk_Click(object sender, RoutedEventArgs e)
         * add new reminder
         */
        private void ButtonOk_Click(object sender, RoutedEventArgs e)
        {
            int index=0,n=0;
            int[] array = new int[10000];
            string date = ReplaceApostrophe(DatePicker1.SelectedDate.Value.ToShortDateString());
            string name = ReplaceApostrophe(ReminderName.Text);
            string note = ReplaceApostrophe(Note.Text);
            string snooze = ReplaceApostrophe(NumericUpDown1.Value.ToString());
            string time = ReplaceApostrophe(TimePicker.SelectedTime.Value.ToString());
            SQLiteConnection new_con = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());            
            new_con.Open();
            SQLiteCommand new_cnt = new SQLiteCommand("SELECT rid FROM  reminder", new_con);
            SQLiteDataReader reader;
            reader = new_cnt.ExecuteReader();
            while (reader.Read())
            {
                array[index] = Convert.ToInt32(reader[0].ToString());
                index++;
            }

            if (!(index == 0))
            {
                n=index;
                insertionSort(array, n);
                index = 0;
                while (index<n)
                {
                    if (!(array[index] == index))
                        break;
                    index++;
                }
            }
            SQLiteCommand get=new SQLiteCommand("INSERT INTO reminder VALUES ('"+ index +"', '" + date + "', '" + name + "', '" + note + "', '" + snooze + "', '" + time + "')", new_con);
            get.ExecuteNonQuery();
            this.Close();

            MainWindowStart mainWindow = MainWindowStart.Instance;
            mainWindow.mainWindowUpdate();

        }

 private void ButtonOk_Click(object sender, RoutedEventArgs e)
 {
     string firstname = ReplaceApostrophe(TextBoxFirstName.Text);
     string lastname = ReplaceApostrophe(TextBoxLastName.Text);
     string mail = ReplaceApostrophe(TextBoxMail.Text);
     string address = ReplaceApostrophe(TextBoxAddress.Text);
     try
     {
         int number = Convert.ToInt32(TextBoxPhone.Text);
         SQLiteConnection new_up = new SQLiteConnection(customwindow.Properties.Settings.Default.ConnectionString.ToString());
         new_up.Open();
         SQLiteCommand up1 = new SQLiteCommand("Update contact_detail set firstname='" + firstname + "',lastname='" + lastname + "', number='" + Convert.ToInt32(number) + "',address='" + address + "',email='" + mail + "' where cid='" + cid + "'", new_up);
         up1.ExecuteNonQuery();
         new_up.Close();
         this.Close();
         MainWindowStart mainWindow = MainWindowStart.Instance;
         mainWindow.mainWindowUpdateContact();
     }
     catch
     {
         MessageBox.Show("Phone number is invalid!");
     }
 }