C# SQLiteConnection.Close方法代码示例

        /// <summary>
        ///   Removes the old entries (entries that have been in the database more than the specified time) from the database.
        /// </summary>
        /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
        public static void CleanNewsOlderThan(int olderThan)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    DateTime olderDate = DateTime.Now;
                    TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
                    olderDate = olderDate.Subtract(daySpan);

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "DELETE " +
                                                                        "FROM NEWS_STORAGE " +
                                                                        "WHERE NEWSITEM_AQUISITION_DATE <= ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, olderDate);
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        private bool CreateDatabase()
        {
            SQLiteConnection tempConnection = null;
            try
            {
                SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
                tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
                tempConnection.Open();

                SQLiteCommand command = tempConnection.CreateCommand();
                command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
                command.ExecuteNonQuery();

                tempConnection.Close();
            }
            catch(Exception e)
            {
                errors.Add("Tietokannan luominen epäonnistui");
                return false;
            }
            finally
            {
                if(tempConnection != null) tempConnection.Close();
            }

            return true;
        }

        /// <summary>
        /// Creates an example table containing 'DVD' records.
        /// </summary>
        /// <param name="conn">A SQLiteConnection object.</param>
        public void CreateDVDTable(SQLiteConnection conn)
        {
            //This sql query creates a dvds table with an auto incrementing id field as the primary key and some other columns.
            string sql = "CREATE TABLE dvds (id INTEGER PRIMARY KEY ASC, title TEXT, genre TEXT, date_added DATETIME)";

            //SQLiteCommand objects are where we exectute sql statements.
            SQLiteCommand cmd = new SQLiteCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SQLiteException ex)
            {
                //if anything is wrong with the sql statement or the database,
                //a SQLiteException will show information about it.
                Debug.Print(ex.Message);

                //always make sure the database connectio is closed.
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public int ExecuteSql(string SQLString)
 {
     using (SQLiteConnection connection = new SQLiteConnection(connectionString))
     {
         using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
         {
             try
             {
                 connection.Open();
                 int rows = cmd.ExecuteNonQuery();
                 return rows;
             }
             catch (MySql.Data.MySqlClient.MySqlException e)
             {
                 connection.Close();
                 throw new Exception(e.Message);
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
 }

        public static bool Login(int accountId, string password)
        {
            using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = "SELECT COUNT(accountId) FROM vAccountPassword WHERE accountId = @account AND password = @password";
                    SQLiteParameter pAccount = new SQLiteParameter("account",accountId);
                    pAccount.Direction = ParameterDirection.Input;

                    SQLiteParameter pPassword = new SQLiteParameter("password", password);
                    pPassword.Direction = ParameterDirection.Input;

                    cmd.Parameters.AddRange(new SQLiteParameter[] { pAccount, pPassword });
                    con.Open();
                    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
                    {
                        con.Close();
                        return true;
                    }
                    con.Close();
                    return false;
                }
            }
        }

        public bool AddQuote()
        {
            var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
            string parentDirectory = myDirectory.Parent.FullName;
            using (
                var cn =
                    new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory,
                                                       ConfigurationManager.ConnectionStrings["connectionstring"]
                                                           .ConnectionString)))
            {
                try
                {
                    string _sql = string.Format("Insert into quotes values('{0}','{1}','{2}',{3}','');",
                                                ProjectName, QuoteBy, Quotestring, User);

                        SQLiteCommand myCommand = cn.CreateCommand();
                        myCommand.CommandText = _sql;
                        cn.Open();
                        myCommand.ExecuteNonQuery();
                        cn.Close();

                    return true;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    cn.Close();
                    return false;
                }

            }
        }

        /// <summary>
        /// Authenticate User with giver login data. Returns null if user does not exist
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static UserDatabaseError Authenticate(string username, string password, out User user)
        {
            user = null;
            SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
            SQLiteCommand command = new SQLiteCommand("SELECT salt FROM users WHERE [email protected] and active = 1", connection);
            //SQLiteCommand command = new SQLiteCommand("SELECT count(*) FROM users", connection);
            command.Parameters.AddWithValue("@username", username);
            try
            {
                connection.Open();
            }
            catch
            {
                return UserDatabaseError.ERR_DATABASE_CONNECTION;
            }
            String salt = (string)command.ExecuteScalar();

            if (salt == null || salt == string.Empty)
            {
                connection.Close();
                return UserDatabaseError.ERR_USER_DOES_NOT_EXIST;
            }
            string hash = DatabaseManager.GetSha256(password + salt);
            command.CommandText = "SELECT name, privilege FROM users WHERE [email protected] AND sha256p [email protected]";
            command.Parameters.AddWithValue("@password", hash);

            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                user = new User(username, (string)reader["name"], (User.UserPrivilege)(Int64)reader["privilege"]);
            if (user == null)
                return UserDatabaseError.ERR_AUTH;

            connection.Close();
            return UserDatabaseError.ERR_SUCCESS;
        }

 /// <summary>
 /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
 /// </summary>
 /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
 /// <param name="paramList">object[] array of parameter values</param>
 /// <returns></returns>
 public static DataSet ExecuteDataSet(string commandText, params object[] paramList)
 {
     using (SQLiteConnection conn = new SQLiteConnection(connStr))
     {
         using (SQLiteCommand cmd = new SQLiteCommand(commandText, conn))
         {
             try
             {
                 conn.Open();
                 if (paramList != null)
                 {
                     AttachParameters(cmd, commandText, paramList);
                 }
                 DataSet ds = new DataSet();
                 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                 da.Fill(ds);
                 da.Dispose();
                 cmd.Dispose();
                 conn.Close();
                 return ds;
             }
             catch (Exception ex)
             {
                 cmd.Dispose();
                 conn.Close();
                 throw new Exception(ex.Message);
             }
         }
     }
 }

        public static void Try(string[] parameters)
        {
            Player.PlayerStruct p = Player.GetPlayer(parameters[1]);
            if (!string.IsNullOrEmpty(p.Squad) && !string.IsNullOrEmpty(parameters[3]) && p.name.ToLower() != parameters[3].ToLower())
            {
                /* Begin Database Connection */
                DataTable dt = new DataTable();

                SQLiteConnection SConnection = new SQLiteConnection();
                SConnection.ConnectionString = SQLite.ConnectionString;
                SConnection.Open();

                SQLiteCommand cmd = new SQLiteCommand(SConnection);

                cmd.CommandText = @"SELECT owner FROM squads WHERE name = @nsquad";
                SQLiteParameter nsquad = new SQLiteParameter("@nsquad");
                SQLiteParameter pname = new SQLiteParameter("@pname");
                cmd.Parameters.Add(nsquad);
                cmd.Parameters.Add(pname);
                nsquad.Value = p.Squad;
                pname.Value = p.name;

                SQLiteDataReader Reader = cmd.ExecuteReader();
                dt.Load(Reader);
                Reader.Close();

                SConnection.Close();
                /* End Database Connection */

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0][0].ToString().ToLower() == p.name.ToLower())
                    {
                        /* Begin Database Connection */
                        SConnection.Open();
                        cmd.CommandText = @"UPDATE players SET squad = '' WHERE name = @kname AND squad = @nsquad";
                        SQLiteParameter kname = new SQLiteParameter("@kname");
                        cmd.Parameters.Add(kname);
                        kname.Value = parameters[3];
                        cmd.ExecuteNonQuery();
                        SConnection.Close();
                        /* End Database Connection */

                        //TODO: do we send the kicked player a message?
                        Message.Send = "MSG:" + parameters[1] + ":0:If said player was a member of your squad, he or she has been kicked.";
                        SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " kicked " + parameters[3] + " from squad <" + p.Squad + ">.");
                    }
                    else
                    {
                        Message.Send = "MSG:" + parameters[1] + ":0:You do not own this squad.";
                        SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " attempted to kick " + parameters[3] + " from squad <"
                                    + p.Squad + ">, but is not owner.");
                        return;
                    }
                }
            }
        }

        /// <summary>
        /// 检查用户密码问题
        /// </summary>
        /// <param name="uname"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static string CheckLogin(string UUser, string PPwd)
        {
            string Querystring = "select username from online_user_tab where username='" + UUser.Trim() + "' and password='" + PPwd.Trim() + "'";
            SQLiteConnection conn = new SQLiteConnection();
            string connectstr = DataAccess.PDAConnStr;
            conn.ConnectionString = connectstr;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(Querystring, conn);
            SQLiteDataReader Datareader = cmd.ExecuteReader();
            Datareader.Read();
            //Method 1
            //OracleNumber oraclenumber = reader.GetOracleNumber(0);
            //Response.Write("OracleNum " + oraclenumber.ToString());
            //string aa = oraclenumber.ToString();
            //if (aa=="1")
            //{
            //    Response.Redirect("mainform.aspx");
            //    reader.Close();
            //}
            //else
            //{
            //    Response.Write("Username and password do not match!Please try it again!");
            //    reader.Close();
            //    TextBox1.Text = "";
            //    TextBox2.Text = "";
            //    TextBox1.Focus();

            //}
            //Method 2;
            if (Datareader.HasRows)
            {
                //Response.Write(cmd.ExecuteScalar().ToString());
                //reader.Close();
                //conn.Close();
                //Response.Redirect("mainform.aspx");
                Datareader.Close();
                conn.Close();
                return "1";

            }
            else
            {
                //Response.Write("Invalid Password or Username,Please Check!!");
                //reader.Close();
                //conn.Close();
                Datareader.Close();
                conn.Close();
                return "2";

            }
        }

 public static DataTable buscaDatosSing4()
 {
     DataTable tabla = new dataCredito.datosSing4DataTable();
     using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
     {
         using (SQLiteCommand comando = new SQLiteCommand())
         {
             comando.CommandText = "select * from datosSing4";
             comando.Connection = con;
             con.Open();
             SQLiteDataReader lector = comando.ExecuteReader();
             while (lector.Read())
             {
                 DataRow fila = tabla.NewRow();
                 fila["nombre"] = lector.GetString(0);
                 fila["direccion"] = lector.GetString(1);
                 fila["direccion2"] = lector.GetString(2);
                 fila["ciudad"] = lector.GetString(3);
                 fila["id"] = lector.GetString(4);
                 fila["idCliente"] = lector.GetInt32(5);
                 fila["asesor"] = lector.GetString(6);
                 fila["encabezado"] = lector.GetString(7);
                 fila["fecha"] = lector.GetDateTime(8);
                 fila["pie"] = lector.GetString(9);
                 fila["saludo"] = lector.GetString(10);
                 tabla.Rows.Add(fila);
             }
         }
         con.Close();
     }
     return (tabla);
 }

 private void DeleteCardBase()
 {
     if (dataGridView1.SelectedRows.Count > 0)
     {
         id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
         if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
         DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
         if (result == DialogResult.Yes)
             using (SQLiteConnection conn = new SQLiteConnection(connString))
             {
                 conn.Open();
                 SQLiteCommand command = new SQLiteCommand(conn);
                 command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
                 command.Parameters.Add(new SQLiteParameter("@id", id));
                 command.ExecuteNonQuery();
                 conn.Close();
                 Odswierz();
                 if (dataGridView1.RowCount != 0)
                 {
                     if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
                     else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
                 }
             }
     }
 }

        public static void ChapterFinished(string mangaTitle)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "UPDATE READING_LIST " +
                                                                        "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
                                                                        "WHERE MANGA_ID = ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
                    sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        private void Consulta_Load(object sender, EventArgs e)
        {
            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;";

            DataSet DS = new DataSet();
            SQLiteConnection con = new SQLiteConnection(connString);
            con.Open();
            SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con);
            DA.Fill(DS, "Expediente");
            dataGridView1.DataSource = DS.Tables["Expediente"];
            con.Close();

            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

            int i = 0;
            foreach (DataGridViewColumn c in dataGridView1.Columns)
            {
                i += c.Width;

            }
            if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
            {
                dataGridView1.Width = 616;
            }
            else
            {
                dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
            }
        }

        /// <summary>
        /// Takes a GIS model and a file and writes the model to that file.
        /// </summary>
        /// <param name="model">
        /// The GisModel which is to be persisted.
        /// </param>
        /// <param name="fileName">
        /// The name of the file in which the model is to be persisted.
        /// </param>
        public void Persist(GisModel model, string fileName)
        {
            Initialize(model);
            PatternedPredicate[] predicates = GetPredicates();

            if (	File.Exists(fileName))
            {
                File.Delete(fileName);
            }

            using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
            {
                mDataConnection.Open();
                mDataCommand = mDataConnection.CreateCommand();
                CreateDataStructures();

                using (mDataTransaction = mDataConnection.BeginTransaction())
                {
                    mDataCommand.Transaction = mDataTransaction;

                    CreateModel(model.CorrectionConstant, model.CorrectionParameter);
                    InsertOutcomes(model.GetOutcomeNames());
                    InsertPredicates(predicates);
                    InsertPredicateParameters(model.GetOutcomePatterns(), predicates);

                    mDataTransaction.Commit();
                }
                mDataConnection.Close();
            }
        }