C# SQLite.SQLiteConnectionStringBuilder类代码示例

        /// <summary>
        /// Initializes a new instance of the SQLiteRepository class.
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public SQLiteRepository(string connectionString)
        {
            connectionString = (connectionString ?? string.Empty).Trim();

            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = "data source=|DataDirectory|BlueCollar.sqlite;synchronous=Off;journal mode=Off;version=3";
            }

            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(connectionString);
            builder.DataSource = BlueCollarSection.Section.ResolvePath(builder.DataSource);
            builder.DateTimeKind = DateTimeKind.Utc;

            EnsureDatabase(builder.DataSource);
            this.ConnectionString = builder.ConnectionString;

            try
            {
                this.defaultIsolationLevel = builder.DefaultIsolationLevel;
            }
            catch (NullReferenceException)
            {
                this.defaultIsolationLevel = IsolationLevel.Serializable;
            }

            this.connection = new SQLiteConnection(this.ConnectionString);
            this.connection.Open();
        }

        /// <summary>
        /// Connects to db.
        /// </summary>
        public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger)
        {
            if (string.IsNullOrEmpty(dbPath))
            {
                throw new ArgumentNullException("dbPath");
            }

            SQLiteConnection.SetMemoryStatus(false);

            var connectionstr = new SQLiteConnectionStringBuilder
            {
                PageSize = 4096,
                CacheSize = cacheSize ?? 2000,
                SyncMode = SynchronizationModes.Normal,
                DataSource = dbPath,
                JournalMode = SQLiteJournalModeEnum.Wal,

                // This is causing crashing under linux
                Pooling = enablePooling && Environment.OSVersion.Platform == PlatformID.Win32NT,
                ReadOnly = isReadOnly
            };

            var connectionString = connectionstr.ConnectionString;

            if (!enablePooling)
            {
                logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString);
            }

            var connection = new SQLiteConnection(connectionString);

            await connection.OpenAsync().ConfigureAwait(false);

            return connection;
        }

		public override void CreateDatabase(string constr)
		{
			// SQLite молча пересоздаст файл если такой уже есть.
			//
			var csb = new SQLiteConnectionStringBuilder(constr);
			if (!File.Exists(csb.DataSource)
				|| MessageBox.Show(
					string.Format(Resources.FileExistedMessage, Path.GetFileName(csb.DataSource)),
					@"SQLite",
					MessageBoxButtons.YesNo,
					MessageBoxIcon.Question,
					MessageBoxDefaultButton.Button2) == DialogResult.Yes)
			{
				// Create file
				SQLiteConnection.CreateFile(csb.DataSource);

				// Change default page size
				using (var con = new SQLiteConnection(constr))
				using (var cmd = con.CreateCommand())
				{
					con.Open();
					cmd.CommandText = @"pragma page_size=" + PageSize + @"; VACUUM;";
					cmd.ExecuteNonQuery();
				}
			}
		}

        public DatabaseDriver()
        {
            this.DatabaseEngine = Config.GetDatabaseEngine();

            DbConnectionStringBuilder Builder;

            if (this.DatabaseEngine == DatabaseEngine.Sqlite)
            {
                Builder = new SQLiteConnectionStringBuilder();
                string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType<string>("Database", "Database") + ".sqlite3");
                IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;

                Builder.Add("Data Source", FullPath);

                Connection = new SQLiteConnection(Builder.ConnectionString);
            }
            else if (this.DatabaseEngine == DatabaseEngine.Mysql)
            {
                Builder = new MySqlConnectionStringBuilder();

                Builder.Add("Server", Config.GetType<string>("Database", "Hostname"));
                Builder.Add("Port", Config.GetType<int>("Database", "Port"));
                Builder.Add("User ID", Config.GetType<string>("Database", "Username"));
                Builder.Add("Password", Config.GetType<string>("Database", "Password"));
                Builder.Add("Database", Config.GetType<string>("Database", "Database"));

                Connection = new MySqlConnection(Builder.ConnectionString);
            }
            else
            {
                throw new Exception("Invalid Database type.");
            }
        }

        public DatabaseReader(string databaseFilename)
        {
            FileAttributes attr = File.GetAttributes(databaseFilename);
            if (attr.ToString() == "")
            {
                _databaseConnection = null;
            }

            SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            builder.DataSource = databaseFilename;
            builder.ReadOnly = true;

            _databaseConnection = new SQLiteConnection(builder.ConnectionString);

            try
            {
                _databaseConnection.Open();
            }
            catch (SQLiteException ex)
            {
                if (ex.ErrorCode == SQLiteErrorCode.NotADatabase)
                {
                    throw new UnreadableDatabaseFileException(databaseFilename, ex);
                }
                else
                {
                    throw;
                }
            }
        }

        protected virtual bool ConnectToDB(string dbPath)
        {
            SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
            connectionstr.PageSize = 4096;
            connectionstr.CacheSize = 4096;
            connectionstr.SyncMode = SynchronizationModes.Normal;
            connectionstr.DataSource = dbPath;
            connectionstr.JournalMode = SQLiteJournalModeEnum.Delete;
            connection = new SQLiteConnection(connectionstr.ConnectionString);
            int retries = 0;
            bool connected = false;
            while (!connected && retries < MAX_RETRIES)
            {
                try
                {
                    connection.Open();
                    connected = true;
                }
                catch (Exception e)
                {
                    Logger.ReportException("Error connecting to database "+dbPath+"! Will retry " + MAX_RETRIES + " times.", e);
                    retries++;
                    Thread.Sleep(250);
                }
            }

            return connected;
        }

		/// <summary>
		/// Creates a new SQLite profiling data provider and opens or creates a new database stored in a file.
		/// </summary>
		public ProfilingDataSQLiteWriter(string fileName, bool profileUnitTests, string[] unitTestNames)
		{
			if (File.Exists(fileName))
				throw new IOException("File already exists!");
			
			SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder();
			conn.Add("Data Source", fileName);
			conn.Add("New", true);
			// Disable protecting the database on crashes - it's a new database,
			// it may go corrupt if we crash during DB creation. Disabling journalling
			// makes Inserts faster.
			conn.Add("Journal Mode", "OFF");
			conn.Add("Synchronous", "OFF");
			this.connection = new SQLiteConnection(conn.ConnectionString);
			
			this.connection.Open();
			
			InitializeTables();
			
			File.SetAttributes(fileName, FileAttributes.Compressed);
			
			this.profileUnitTests = profileUnitTests;
			this.unitTestNames = unitTestNames;
			
			if (profileUnitTests && unitTestNames == null)
				throw new InvalidOperationException("Please add unit tests to filter!");
		}

 private static void SetPathToParentDirectoryOfDatabaseFile()
 {
     var builder =
         new SQLiteConnectionStringBuilder(WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString);
     _pathToDatabaseFile = Consts.Consts.GetPath(builder.DataSource);
     _pathToDatabase = Directory.GetParent(_pathToDatabaseFile).FullName + @"\";
 }

 /// <summary>
 /// コンストラクタ
 /// </summary>
 /// <param name="dbname">接続するDB名</param>
 public SQLiteDB(String dbname)
 {
     DBName = dbname;
     SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
     builder.DataSource = DBName;
     Conn = new SQLiteConnection(builder.ConnectionString);
 }

        static partial void ApplyRestrictedCredentials(SQLiteConnectionStringBuilder b, bool admin = false)
        {
            //b.Add("InternalHost", "invalidhost");

            b.Add("InternalUser", "user3");
            b.Password = "mypass";
        }

        /// <summary>
        /// New Sqlite.
        /// </summary>
        public Database()
        {
            try
            {
                if (!File.Exists($"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db"))
                    SQLiteConnection.CreateFile($"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db");
            }
            catch (Exception ex) { ex.CatchError(); }

            SQLiteConnectionStringBuilder Builder = new SQLiteConnectionStringBuilder();
            Builder.DataSource = $"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db";
            Builder.Version = 3;
            Builder.Pooling = true;
            Builder.SyncMode = SynchronizationModes.Full;

            ConnectionString = Builder.ConnectionString;

            using (SQLiteDataReader Result = EnqueueTransaction(
[email protected]"create table if not exists Player_Info (ID integer primary key, Name text, Gamejolt_ID integer default -1, IP_Address text, Last_Activity integer);
create table if not exists Channel_Info (ID integer primary key, Name text, Motd text, Max_Limit integer default -1);
create table if not exists Global_BlackList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_IPBlackList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_MuteList (ID integer primary key, Player_ID integer default -1, Channel_ID integer default -1, Mute_ID integer, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_OperatorList (ID integer primary key, Player_ID integer, Channel_ID default -1, Reason text default 'No Reason.', Permission integer default {(int)Player.OperatorTypes.Player});
create table if not exists Global_WhiteList (ID integer primary key, Player_ID integer, Channel_ID default -1, Reason text default 'No Reason.');
create table if not exists Global_ChatHistory (ID integer primary key, Player_ID integer, Channel_ID default -1, Message text, TimeStamp integer);
create table if not exists Global_TradeHistory (ID integer primary key, Channel_ID integer default -1, Host_ID integer, Host_Pokemon text, Client_ID integer, Client_Pokemon text, TimeStamp integer);
create table if not exists Global_WorldList (ID integer primary key, Channel_ID integer, Season integer default {(int)World.SeasonType.DefaultSeason}, Weather integer default {(int)World.WeatherType.DefaultWeather}, Time_Offset integer default 0, DoDayCycle integer default 1);
create table if not exists Player_WorldList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Season integer default {(int)World.SeasonType.DefaultSeason}, Weather integer default {(int)World.WeatherType.DefaultWeather}, Time_Offset integer default 0, DoDayCycle integer Default 1);"))
                Core.Logger.Log("Database initialized.", Logger.LogTypes.Info);
        }

 static SQLiteConnection Connection(string filePath)
 {
     if (filePath.IsNullOrBlank()) throw new ArgumentNullException("filePath");
     SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder() { DataSource = filePath };
     SQLiteConnection connection = new SQLiteConnection(builder.ConnectionString, true);
     return connection;
 }

 public void TestQuery()
 {
     var builder = new SQLiteConnectionStringBuilder();
       builder.DataSource = "test.db";
       using (DbConnection connection = new SQLiteConnection(builder.ToString()))
       {
     connection.Open();
     using (var cmd1 = connection.CreateCommand())
     {
       cmd1.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='table_test';";
       var reader = cmd1.ExecuteReader();
       if (reader.Read())
       {
     var tableName = reader.GetString(0);
     System.Diagnostics.Trace.WriteLine(String.Format("table name={0}", tableName));
       }
       else
       {
     using (var cmd2 = connection.CreateCommand())
     {
       cmd2.CommandText = @"Create Table 'table_test' (num Integer, str)";
       cmd2.ExecuteNonQuery();
     }
       }
     }
       }
 }

        //Set up the connection with the password
        public static SQLiteConnection Connection(string dbname, string pwd)
        {
            string datasource = dbname;
            SQLiteConnection.CreateFile(datasource);
            //连接数据库
            SQLiteConnection connection = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            connstr.Password = pwd;//设置密码，SQLite ADO.NET实现了数据库密码保护

            //将连接的信息传递给connection
            connection.ConnectionString = connstr.ToString();

            if (connection == null)
            {
                connection = new SQLiteConnection();
                connection.ConnectionString = connstr.ToString();
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }
            else if (connection.State == System.Data.ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }

            return connection;
        }

        /// <summary>
        /// Initializes a new instance of the MainViewModel class.
        /// </summary>
        public DBViewModel(string dbName, string dbPath)
        {
            this.dbName = dbName;
            this.dbPath = dbPath;

            try
            {
                SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
                builder.DataSource = DBPath;
                conn = new SQLiteConnection(builder.ConnectionString);
                conn.Open();
                var schema = conn.GetSchema("Tables");
                foreach (System.Data.DataRow row in schema.Rows)
                {
                    try
                    {
                        TableViewModel table = new TableViewModel(row.ItemArray[2].ToString(), row.ItemArray[6].ToString(), conn);
                        Tables.Add(table);
                    }
                    catch(Exception)
                    {
                        continue;
                    }

                }

                IsValid = true;
            }
            catch (Exception ex)
            {
                IsValid = false;
                System.Windows.MessageBox.Show(ex.Message);
            }
        }