C# SQLiteConnection.Open方法代码示例

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

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

        //public static int Insert(string dbFile, string sql,)
        public static ObservableCollection<Jewelry> GetAll()
        {
            ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();

            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
            //using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
            {
                conn.Open();

                string sql = string.Format("select * from detail");

                SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                using (SQLiteDataReader dr1 = cmd.ExecuteReader())
                {
                    while (dr1.Read())
                    {
                        var d = dr1;
                        var dd = dr1.GetValue(0);
                        var data = dr1.GetValue(1);
                        var insert = dr1.GetValue(2);
                        var update = dr1.GetValue(3);
                    }
                }

                conn.Close();
            }

            ob.Add(new Jewelry());

            return ob;
        }

        public static DataTable SearchMusicDatabase(
            FileInfo theDatabaseFileInfo,
            string theCriteria)
        {
            using (DataSet theDataSet = new DataSet())
            {
                using (SQLiteConnection theConnection = new SQLiteConnection(
                    DatabaseLayer.ToConnectionString(theDatabaseFileInfo)))
                {
                    try
                    {
                        theConnection.Open();

                        using (SQLiteCommand theCommand = theConnection.CreateCommand())
                        {
                            StringBuilder theCommandBuilder = new StringBuilder(
                                "SELECT [ID], [Path], [Details], [Extension], 0, " +
                                    "[Path] || '\\' || [Details] || [Extension] AS [FullPath] ");
                            theCommandBuilder.Append("FROM [Tracks] ");
                            theCommandBuilder.Append("WHERE ");

                            bool IsFirstParameter = true;
                            foreach (string thisCriteria in theCriteria.Split(' '))
                            {
                                if (!IsFirstParameter)
                                {
                                    theCommandBuilder.Append(" AND ");
                                }
                                theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)");

                                SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter();
                                theCommand.Parameters.Add(thisCriteriaParameter);
                                theCommand.Parameters.Add(thisCriteriaParameter);

                                thisCriteriaParameter.Value = String.Format(
                                    CultureInfo.CurrentCulture,
                                    "%{0}%",
                                    thisCriteria.ToString());

                                IsFirstParameter = false;
                            }

                            theCommandBuilder.Append(" ORDER BY [Path], [Details]");

                            theCommand.CommandText = theCommandBuilder.ToString();

                            using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand))
                            {
                                theAdapter.Fill(theDataSet);
                            }
                        }
                    }
                    finally
                    {
                        theConnection.Close();
                    }
                }
                return theDataSet.Tables[0];
            }
        }

        public void Clear(string name)
        {
            int procCount = Process.GetProcessesByName("firefox").Length;
            if (procCount > 0)
                throw new ApplicationException(string.Format("There are {0} instances of Firefox still running",
                                                             procCount));

            try
            {
                using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName()))
                {
                    conn.Open();
                    SQLiteCommand command = conn.CreateCommand();
                    command.CommandText = "delete from moz_cookies where name='" + name + "'";
                    int count = command.ExecuteNonQuery();
                }
            }
            catch (SQLiteException ex)
            {
                if (
                    !(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) ||
                      ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked)))
                    throw new ApplicationException("The Firefox cookies.sqlite file is locked");
            }
        }

        public void create(string file)
        {
            db_file = file;
            conn = new SQLiteConnection("Data Source=" + db_file);
            if (!File.Exists(db_file))
            {
                conn.Open();
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.CommandText = @"CREATE TABLE `erogamescape` (
                                        `id`	INTEGER NOT NULL,
                                        `title`	TEXT,
                                        `saleday`	TEXT,
                                        `brand`	TEXT,
                                        PRIMARY KEY(id));
                                        CREATE TABLE `tableinfo` (
                                        `tablename`	TEXT NOT NULL,
                                        `version`	INTEGER,
                                        PRIMARY KEY(tablename))";
                    command.ExecuteNonQuery();
                    command.CommandText = @"INSERT INTO  tableinfo VALUES('erogamescape',0)";
                    command.ExecuteNonQuery();

                }
                conn.Close();
            }
        }

        public List<AttachmentDetail> getWitAttachments()
        {
            List<AttachmentDetail> attachments;
            try
            {
                sql_con = new SQLiteConnection(Common.localDatabasePath, true);
                sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);

                sql_con.Open();
                SQLiteDataReader reader = sql_cmd.ExecuteReader();

                attachments = new List<AttachmentDetail>();
                while (reader.Read())
                {
                    AttachmentDetail attachment = new AttachmentDetail();
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
                    attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
                    attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
                    attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
                   
                    attachments.Add(attachment);
                }

            }
            catch (SQLiteException e) { throw e; }
            finally { sql_con.Close(); }

            return attachments;

        }

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

        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 loadHeroDiary()
        {
            // Load the latest Hero Diary entries
            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;"))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where [email protected] order by Diary_ID desc limit 1000";
                    cmd.CommandText = commandText;
                    cmd.Parameters.AddWithValue("@HeroName", this.HeroName);

                    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da = new SQLiteDataAdapter(cmd);
                    ds = new DataSet();
                    da.Fill(ds);

                    BindingSource bindingSource = new BindingSource();
                    bindingSource.DataSource = ds.Tables[0];
                    grdDiary.DataSource = bindingSource;
                    grdDiary.AutoGenerateColumns = true;
                    grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders;
                    grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                }
            }
        }

 private DoorInfo Retrieve(string DoorID)
 {
     var ret = new DoorInfo();
     var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
     var conn = new SQLiteConnection(cs);
     conn.Open();
     var cmd = new SQLiteCommand(conn);
     cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
     cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
     SQLiteDataReader res = null;
     try
     {
         res = cmd.ExecuteReader();
         if (res.HasRows && res.Read())
         {
             ret.DoorID = DoorID;
             ret.Location = res.GetString(1);
             ret.Description = res.GetString(2);
             ret.EventID = res.GetInt64(3);
         }
         return ret;
     }
     catch(Exception ex)
     {
         throw;
     }
     finally
     {
         if (null != res && !res.IsClosed)
             res.Close();
     }
 }

        public static void addAccount(string name, string passwd, string url, string provider, string blogname)
        {
            string conn_str = "Data Source=" + path + pwd_str;

            SQLiteConnection conn = new SQLiteConnection(conn_str);

            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();

            String sql = "Insert INTO BlogAccount(BlogUrl,BlogName,Provider,AccountName,Password) Values(@url,@blogname,@provider,@name,@password)";

            cmd.CommandText = sql;
            cmd.Connection = conn;

            cmd.Parameters.Add(new SQLiteParameter("url", url));
            cmd.Parameters.Add(new SQLiteParameter("blogname", blogname));
            cmd.Parameters.Add(new SQLiteParameter("provider", provider));
            cmd.Parameters.Add(new SQLiteParameter("name", name));
            cmd.Parameters.Add(new SQLiteParameter("password", passwd));

            cmd.ExecuteNonQuery();

            conn.Close();
        }

        /// <summary>
        /// Constructor creates a database if it doesn't exist and creates the database's tables
        /// </summary>
        public DataBaseRepository()
        {
            // "using " keywords ensures the object is properly disposed.
            using (var conn = new SQLiteConnection(Connectionstring))
            {
                try
                {
                    if (!File.Exists(Startup.dbSource))
                    {
                        SQLiteConnection.CreateFile(Startup.dbSource);
                    }

                    conn.Open();
                    //Create a Table
                    string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
                    SQLiteCommand command = new SQLiteCommand(query, conn);
                    command.ExecuteNonQuery();
                }
                //TODO: Handle try catch better cath a more specific error type. 
                catch (SQLiteException ex )
                {
                    Console.WriteLine(ex.ToString());
                }
                conn.Close();
            }
        }

		public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None)
		{
			_databaseFullPath = databaseFullPath;
			_destructorType = destructorType;
			_connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath);
			
			if (!File.Exists(_databaseFullPath))
			{
				SQLiteConnection.CreateFile(_databaseFullPath);
				Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath);
			}
			else
			{
				Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath);
			}

			using (var connection = new SQLiteConnection(_connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
					Log.Verbose("About to create or check for the Entities table in the EntityStore database.");
					cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))";
					cmd.CommandType = CommandType.Text;
					cmd.ExecuteNonQuery();
					Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database.");
				}
			}
		}

        public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection)
        {
            DataSet dataSet = new DataSet();
            dataSet.Reset();

            SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection);
            try
            {
                Connection.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
                dataAdapter.Fill(dataSet);
            }
            catch (SQLiteException ex)
            {
                Log.Write(ex);
                //Debug.WriteLine(ex.Message);
                throw; // пересылаем исключение на более высокий уровень
            }
            finally
            {
                Connection.Dispose();
            }

            return dataSet;
        }