C# SQLiteConnection.Close方法代码示例

        public int iExecuteNonQuery(string FileData, string sSql, int where)
        {
            int n = 0;
            //try
            //{
                using (SQLiteConnection con = new SQLiteConnection())
                {
                    if (where == 0)
                    {
                        con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=True;";
                    }
                    else
                    {
                        con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=False;";
                    }
                    con.Open();
                    using (SQLiteCommand sqlCommand = con.CreateCommand())
                    {
                        sqlCommand.CommandText = sSql;
                        n = sqlCommand.ExecuteNonQuery();
                    }
                    con.Close();
                }
            //}
            //catch
            //{
            //    n = 0;
            //}
            return n;

        }

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

 // Functie pentru a sterge un capitol din baza de date
 public static void delete_capitole(string decizie, string title)
 {
     SQLiteConnection sqlite_conn;
     SQLiteCommand sqlite_cmd;
     string database_title = get_databaseName_tableName.get_databaseName(decizie, "capitole");
     string table_title = get_databaseName_tableName.get_tableName(decizie, "capitole");
        /* if (decizie == "info") { database_title = "Capitole.db"; table_title = "capitole";}
     if (decizie == "mate") { database_title = "Capitole_mate.db"; table_title = "capitole_mate"; }
     if (decizie == "bio") { database_title = "Capitole_bio.db"; table_title = "capitole_bio"; }*/
     try
     {
         sqlite_conn = new SQLiteConnection("Data Source=" + database_title + ";Version=3;New=False;Compress=True;");
         sqlite_conn.Open();
         sqlite_cmd = sqlite_conn.CreateCommand();
         sqlite_cmd.CommandText = "delete from " + table_title + " where titlu = @titlu_capitol";
         SQLiteParameter parameter = new SQLiteParameter("@titlu_capitol", DbType.String);
         parameter.Value = title;
         sqlite_cmd.Parameters.Add(parameter);
         sqlite_cmd.ExecuteNonQuery();
         MessageBox.Show("Lectia a fost stearsa cu succes!");
         sqlite_conn.Close();
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
 }

        public DataRow[] drExecute(string FileData, string sSql)
        {
            DataRow[] datarows = null;
            SQLiteDataAdapter dataadapter = null;
            DataSet dataset = new DataSet();
            DataTable datatable = new DataTable();
            try
            {
                using (SQLiteConnection con = new SQLiteConnection())
                {
                    con.ConnectionString = @"Data Source=" + FileData + "; Version=3; New=False;";
                    con.Open();
                    using (SQLiteCommand sqlCommand = con.CreateCommand())
                    {
                        dataadapter = new SQLiteDataAdapter(sSql, con);
                        dataset.Reset();
                        dataadapter.Fill(dataset);
                        datatable = dataset.Tables[0];
                        datarows = datatable.Select();
                        k = datarows.Count();
                    }
                    con.Close();
                }
            }
            catch(Exception ex)
            {
            //    throw ex;
                datarows = null;
            }
            return datarows;

        }

        /// <summary>
        /// OnDelDir delete dir from DB if its there
        /// </summary>
        /// <param name="args">input arguments</param>
        /// <returns><c>true</c> if dir was deleted</returns>
        public static bool CheckDupeDelDir(string[] args)
        {
            Log.Info("DelDir.CheckDupeDelDir");

            if (!SkipDupe.CheckSkipDupe(args))
            {
                Log.Info("DelDir.CheckDupeDelDir");
                return true;
            }

            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;

            string dbName = ConfigReader.GetConfig("dbDupeDir");
            string db = String.Format(CultureInfo.InvariantCulture,
                @"Data Source={0};Version=3;New=False;Compress=True;", dbName);
            sqlite_conn = new SQLiteConnection(db);
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();

            string dbCommand = String.Format(CultureInfo.InvariantCulture,
                @"DELETE FROM dirDupe WHERE ReleaseName = '{0}'", Global.dupe_name);

            Log.InfoFormat("{0}", dbCommand);

            sqlite_cmd.CommandText = dbCommand;
            sqlite_cmd.ExecuteNonQuery();

            sqlite_conn.Close();

            Log.Info("DelDir.CheckDupeDelDir");

            return true;
        }

        public static void UnDupeDir(string[] args)
        {
            Log.Info("UnDupe.UnDupeDir");

            Global.undupe_dir = args[1];
            Log.InfoFormat("Un Dupe String = '{0}'", Global.undupe_dir);

            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;
            SQLiteDataReader sqlite_datareader;

            string dbName = ConfigReader.GetConfig("dbDupeDir");
            string db = String.Format(CultureInfo.InvariantCulture,
                @"Data Source={0};Version=3;New=False;Compress=True;", dbName);

            sqlite_conn = new SQLiteConnection(db);
            sqlite_conn.Open();
            sqlite_cmd = sqlite_conn.CreateCommand();

            string cmdText =
                String.Format(CultureInfo.InvariantCulture,
                "SELECT COUNT(*) FROM dirDupe WHERE ReleaseName = '{0}'", Global.undupe_dir);
            sqlite_cmd.CommandText = cmdText;
            sqlite_datareader=sqlite_cmd.ExecuteReader();

            int count = 0;

            while (sqlite_datareader.Read())
            {
                count = Int32.Parse(sqlite_datareader.GetValue(0).ToString());
            }

            sqlite_datareader.Close();

            if ( count > 0 )
            {
                string deleteCommand =
                    String.Format(CultureInfo.InvariantCulture,
                    @"DELETE FROM dirDupe WHERE ReleaseName = '{0}'", Global.undupe_dir);

                Log.InfoFormat("{0}", deleteCommand);

                sqlite_cmd.CommandText = deleteCommand;
                sqlite_cmd.ExecuteNonQuery();

                Console.WriteLine(
                    Format.FormatStr1ng(ConfigReader.GetConfig("msgUnDupes_ok"), 0, null));

            }
            else
            {
                Console.WriteLine(
                    Format.FormatStr1ng(ConfigReader.GetConfig("msgUnDupes_fail"), 0, null));
            }

            sqlite_conn.Close();

            Log.Info("UnDupe.UnDupeDir");
        }

        private void Adaugare_Intrebare_Load(object sender, EventArgs e)
        {
            btn_home = home.Size;
            btn_adauga_intrebarea = adauga_intrebarea.Size;
            if (decizie == "info")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(0);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();
            }
            if (decizie == "mate")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole_mate.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole_mate";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(1);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();
            }
            if (decizie == "bio")
            {
                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;
                sqlite_conn = new SQLiteConnection("Data Source=Capitole_bio.db;Version=3;New=False;Compress=True;");
                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();
                sqlite_cmd.CommandText = "SELECT * FROM capitole_bio";
                sqlite_datareader = sqlite_cmd.ExecuteReader();
                while (sqlite_datareader.Read())
                {
                    String myr = sqlite_datareader.GetString(1);
                    checkedListBox1.Items.Add(myr);
                }
                sqlite_conn.Close();

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // [snip] - As C# is purely object-oriented the following lines must be put into a class:

            // We use these three SQLite objects:
            SQLiteConnection sqlite_conn;
            SQLiteCommand sqlite_cmd;
            SQLiteDataReader sqlite_datareader;

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

            // open the connection:
            sqlite_conn.Open();

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

            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

            // Now lets execute the SQL ;D
            sqlite_cmd.ExecuteNonQuery();

            // Lets insert something into our new table:
            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Test Text 1');";

            // And execute this again ;D
            sqlite_cmd.ExecuteNonQuery();

            // ...and inserting another line:
            sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (2, 'Test Text 2');";

            // And execute this again ;D
            sqlite_cmd.ExecuteNonQuery();

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

            // 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
            {
                // Print out the content of the text field:
                // System.Console.WriteLine(sqlite_datareader["text"]);
                string a = sqlite_datareader.GetString(0);
                MessageBox.Show(a);
            }

            // We are ready, now lets cleanup and close our connection:
            sqlite_conn.Close();
        }

 private void pictureBox2_Click(object sender, EventArgs e)
 {
     if (nume.Text == "" || prenume.Text == "" || repass.Text == "" || password.Text == "" || username.Text == "")
         MessageBox.Show("Trebuie sa completati toate campurile!");
     else
         if (cross.Visible == true)
             MessageBox.Show("Parolele nu corespund!");
         else
         {
             string parola_criptata;
             parola_criptata = RC4Class.RC4_Class.RC4(password.Text, "38577af7-379f-421d-ad29-cd1994521704");
             SQLiteConnection sqlite_conn;
             SQLiteCommand sqlite_cmd;
             FileStream fstream;
             sqlite_conn = new SQLiteConnection("Data Source=LOGIN.db;Version=3;New=False;Compress=True;");
             try
             {
                 sqlite_conn.Open();
                 byte[] imageBt = null;
                 fstream = new FileStream(this.image_path.Text, FileMode.Open, FileAccess.Read);
                 BinaryReader br = new BinaryReader(fstream);
                 imageBt = br.ReadBytes((int)fstream.Length);
                 sqlite_cmd = sqlite_conn.CreateCommand();
                 sqlite_cmd.CommandText = "INSERT INTO utilizatori(nume, prenume, username, password,avatar) " +
                                                             "Values('" + nume.Text + "', '" + prenume.Text + "', '" + username.Text + "', '" + parola_criptata + "', @IMG);";
                 SQLiteParameter parameter = new SQLiteParameter("@IMG", System.Data.DbType.Binary);
                 SQLiteParameter parameter1 = new SQLiteParameter("@nume", System.Data.DbType.String);
                 SQLiteParameter parameter2 = new SQLiteParameter("@prenume", System.Data.DbType.String);
                 SQLiteParameter parameter3 = new SQLiteParameter("@username", System.Data.DbType.String);
                 SQLiteParameter parameter4 = new SQLiteParameter("@parola", System.Data.DbType.String);
                 parameter.Value = imageBt;
                 parameter1.Value = nume.Text;
                 parameter2.Value = prenume.Text;
                 parameter3.Value = username.Text;
                 parameter4.Value = parola_criptata;
                 sqlite_cmd.Parameters.Add(parameter);
                 sqlite_cmd.Parameters.Add(parameter1);
                 sqlite_cmd.Parameters.Add(parameter2);
                 sqlite_cmd.Parameters.Add(parameter3);
                 sqlite_cmd.Parameters.Add(parameter4);
                 sqlite_cmd.ExecuteNonQuery();
                 sqlite_conn.Close();
                 MessageBox.Show("Tocmai te-ai inscris in baza noastra de date!");
                 Login lg = new Login();
                 this.Hide();
                 lg.Show();
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }
         }
 }

        private void button1_Click(object sender, EventArgs e)
        {
            //clean history
            SQLiteConnection sql_con;
            SQLiteCommand sql_cmd;
            
            {
                sql_con = new SQLiteConnection("Data Source=History.db;Version=3;New=False;Compress=True;");
                sql_con.Open();

                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "delete from mains";

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

                MessageBox.Show("操作成功！");
            }
        }

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

        internal void DelMnuUserItem(string user)
        {
            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 = "DELETE from myusers WHERE user='" + user + "'";
                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)
        {
            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!");
            }
                

            
        }

        public static void tbox_to_default(SQLiteConnection sql_con)
        {
            sql_con.Open();
            SQLiteCommand sql_cmd = sql_con.CreateCommand();
            string CommandText = "SELECT * FROM settings";
            SQLiteDataAdapter DB = new SQLiteDataAdapter(CommandText, sql_con);
            GlobalVar.DS.Reset();
            DB.Fill(DS);
            DataTable DT = DS.Tables[0];

            for (int i = 0; i < 16; i++)
            {
                GlobalVar.defCars[i] = Convert.ToInt32(DT.Rows[i][0]);
                GlobalVar.defCoeff[i] = Convert.ToInt32(DT.Rows[i][1]);
                GlobalVar.defRebate[i] = Convert.ToInt32(DT.Rows[i][2]);
            }
            sql_con.Close();
        }

        public static bool CheckDupeDB(string dbName)
        {
            Log.Info("SQLiteCheck.CheckDupeDB");

            string fileName = ConfigReader.GetConfig(dbName);

            if ( File.Exists(fileName) )
            {
                Log.DebugFormat("DirDupe DB File '{0}/{1}' Is There.", dbName, fileName);
            }
            else
            {
                Log.DebugFormat("DirDupe DB File '{0}/{1}' Is Not There. Creating It...", dbName, fileName);

                SQLiteConnection sqlite_conn;
                SQLiteCommand sqlite_cmd;
                SQLiteDataReader sqlite_datareader;

                string db = String.Format(CultureInfo.InvariantCulture,
                    @"Data Source={0};Version=3;New=True;Compress=True;", fileName);
                sqlite_conn = new SQLiteConnection(db);

                sqlite_conn.Open();
                sqlite_cmd = sqlite_conn.CreateCommand();

                sqlite_cmd.CommandText = @"CREATE TABLE dirDupe (
                    Id integer primary key,
                    ReleaseTime int(10),
                    ReleaseDateTime varchar(19),
                    ReleaseName varchar(256),
                    Pwd varchar(256),
                    Section varchar(32),
                    UserName varchar(64),
                    GroupName varchar(64),
                    Wiped integer(1),
                    Nuked integer(1),
                    NukeReason varchar(64)
                    );";
                sqlite_cmd.ExecuteNonQuery();

                sqlite_cmd.CommandText = @"CREATE INDEX DirDupeIndex ON dirDupe (ReleaseName);";
                sqlite_cmd.ExecuteNonQuery();

                Log.InfoFormat("Created Table dirDupe '{0}/{1}'", dbName, fileName);

                sqlite_cmd.CommandText = @"INSERT INTO dirDupe
                    (ReleaseTime, ReleaseName, Pwd, Section, UserName, GroupName)
                    VALUES (0, 'ReleaseName', '/test/ReleaseName', 'test', 'jcScripts', 'NoGroup');";
                sqlite_cmd.ExecuteNonQuery();

                Log.DebugFormat("SELECT * FROM dirDupe =");

                sqlite_cmd.CommandText = "SELECT * FROM dirDupe";
                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
                {
                    Log.DebugFormat("{1} {0}", sqlite_datareader["ReleaseName"], sqlite_datareader["Id"] );
                }

                sqlite_conn.Close();
            }

            Log.Info("SQLiteCheck.CheckDupeDB");

            return true;
        }