public CardRefundPayService()
 {
     string localDriver = "C";
     if (Directory.Exists("D:\\"))
     {
         localDriver = "D";
     }
     string dbPath = localDriver + ":\\DataBase\\top4pos.db3";
     if (!File.Exists(dbPath))
     {
         //创建数据库
         SQLiteConnection.CreateFile(dbPath);
     }
     SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder
                                                         {
                                                             DataSource = dbPath,
                                                             Password = "******",
                                                             Pooling = true,
                                                             FailIfMissing = false
                                                         };
     m_SQLiteHelper = new SQLiteHelper(connectionString.ToString());
     //判断表是否存在
     string strSql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='CardRefundPay'";
     if (!m_SQLiteHelper.Exists(strSql))
     {
         string createTableSql = @"create table CardRefundPay(StoreValueID INTEGER primary key autoincrement, 
                                                         CardNo varchar(50), ShopID varchar(36), 
                                                         TradePayNo varchar(30), 
                                                         PayAmount decimal(12, 4), EmployeeNo varchar(20), 
                                                         DeviceNo varchar(16), IsFixed Integer,
                                                         CreateTime datetime, LastTime datetime);";
         m_SQLiteHelper.ExecuteSql(createTableSql);
     }
 }
 protected SqlHelper(String tableName)
 {
     Directory.CreateDirectory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SolderHelper"));
     String databaseName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SolderHelper", "SolderHelper.db");
     try
     {
         if (!File.Exists(databaseName))
         {
             if (IsUnix())
             {
                 SqliteConnection.CreateFile(databaseName);
             }
             else
             {
                 SQLiteConnection.CreateFile(databaseName);
             }
         }
     }
     catch (Exception)
     {
         // ignored
     }
     if (IsUnix())
     {
         SqliteConnectionStringBuilder c = new SqliteConnectionStringBuilder { DataSource = databaseName };
         ConnectionString = c.ConnectionString;
     }
     else
     {
         SQLiteConnectionStringBuilder c = new SQLiteConnectionStringBuilder { DataSource = databaseName };
         ConnectionString = c.ConnectionString;
     }
     TableName = tableName;
 }
Exemple #3
0
        public static List <T> GetAllObject <T>() where T : BaseObject
        {
            List <T> list      = new List <T>();
            string   tableName = typeof(T).Name;//.Substring(typeof(T).Name.LastIndexOf('.'));

            if (commonConnection == null)
            {
                commonConnection = new SQLiteConnection();
                SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = DbFileName;
                commonConnection.ConnectionString = connstr.ToString();
            }
            try
            {
                commonConnection.Open();
                SQLiteCommand cmd = new SQLiteCommand(commonConnection);
                cmd.CommandText = "Select * from " + tableName + "";
                SQLiteDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    T obj = Activator.CreateInstance <T>();
                    obj.SetProperties(sdr);
                    list.Add(obj);
                }
                commonConnection.Close();
            }
            catch (Exception e)
            {
                commonConnection.Close();
                throw e;
            }
            return(list);
        }
        /// <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>
        /// 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(
$@"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);
        }
Exemple #6
0
		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 static SQLiteBlockchainStorage Open(string storageLocation)
        {
            if (!Directory.Exists(storageLocation))
            {
                Directory.CreateDirectory(storageLocation);
            }

            SQLiteConnectionStringBuilder connStrBuilder = new SQLiteConnectionStringBuilder();
            connStrBuilder.DataSource = Path.Combine(storageLocation, BlockchainFilename);
            connStrBuilder.JournalMode = SQLiteJournalModeEnum.Wal;

            string connStr = connStrBuilder.ConnectionString;

            SQLiteConnection conn = new SQLiteConnection(connStr);
            conn.Open();

            ApplySchemaSettings(conn, "main");

            //todo: SQLite in WAL mode does not guarantee atomic commits across all attached databases
            AttachDatabase(conn, "blocks", Path.Combine(storageLocation, BlocksFilename));
            AttachDatabase(conn, "utxo", Path.Combine(storageLocation, UtxoFilename));

            ApplySchemaSettings(conn, "blocks");
            ApplySchemaSettings(conn, "utxo");

            //todo: handle exceptions, describe ant test them
            CheckSchema(conn);

            return new SQLiteBlockchainStorage(storageLocation, conn);
        }
        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;
        }
 private static void SetPathToParentDirectoryOfDatabaseFile()
 {
     var builder =
         new SQLiteConnectionStringBuilder(WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString);
     _pathToDatabaseFile = Consts.Consts.GetPath(builder.DataSource);
     _pathToDatabase = Directory.GetParent(_pathToDatabaseFile).FullName + @"\";
 }
        public void TestInitialise()
        {
            _OriginalClassFactory = Factory.TakeSnapshot();
            _RuntimeEnvironment = TestUtilities.CreateMockSingleton<IRuntimeEnvironment>();

            _ConfigurationStorage = TestUtilities.CreateMockSingleton<IConfigurationStorage>();
            _Configuration = new Configuration();
            _ConfigurationStorage.Setup(s => s.Load()).Returns(_Configuration);

            _CreateDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "CreatedDatabase.sqb");
            if(File.Exists(_CreateDatabaseFileName)) File.Delete(_CreateDatabaseFileName);

            _EmptyDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "TestCopyBaseStation.sqb");
            File.Copy(Path.Combine(TestContext.TestDeploymentDir, "BaseStation.sqb"), _EmptyDatabaseFileName, true);

            _Database = Factory.Singleton.Resolve<IBaseStationDatabase>();
            _Database.FileName = _EmptyDatabaseFileName;

            _Provider = new Mock<IBaseStationDatabaseProvider>() { DefaultValue = DefaultValue.Mock }.SetupAllProperties();
            _Database.Provider = _Provider.Object;
            _Provider.Setup(p => p.UtcNow).Returns(DateTime.UtcNow);

            _ConnectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = _EmptyDatabaseFileName };

            _Criteria = new SearchBaseStationCriteria() {
                FromDate = DateTime.MinValue,
                ToDate = DateTime.MaxValue,
            };

            _FileNameChangingEvent = new EventRecorder<EventArgs>();
            _FileNameChangedEvent = new EventRecorder<EventArgs>();
        }
		/// <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!");
		}
Exemple #12
0
        public void Migrate()
        {
            hash = SHA256.Create();

            System.Data.SQLite.SQLiteConnectionStringBuilder cnstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            cnstr.DataSource = "Transfercontext.sdb";
            conn             = new System.Data.SQLite.SQLiteConnection(cnstr.ConnectionString);

            transferContext = new TransferContext(conn, true);

            sourceClient = new ImapClient();



            destinationClient = new ImapClient();


            sourceClient.Connect(SourceServer.Server, SourceServer.Port, SourceServer.SSL);
            Console.WriteLine("Source server connected");

            destinationClient.Connect(DestinationServer.Server, DestinationServer.Port, DestinationServer.SSL);
            Console.WriteLine("Destination server connected");


            sourceClient.Authenticate(SourceServer.Username, SourceServer.Password);
            Console.WriteLine("Source server authenticated");

            destinationClient.Authenticate(DestinationServer.Username, DestinationServer.Password);
            Console.WriteLine("Destination server authenticated");

            foreach (var ns in sourceClient.PersonalNamespaces)
            {
                CopyNamespace(ns);
            }
        }
Exemple #13
0
        private static PhpResource Open(string filename, int mode, PhpReference error, bool persistent)
        {
            if (persistent) PhpException.FunctionNotSupported(PhpError.Notice);

            SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder();
            csb.DataSource = filename;
            csb.Version = 3;

            try
            {
                PhpSQLiteDbConnection connection = new PhpSQLiteDbConnection(csb.ConnectionString);
                if (error != null)
                {
                    error.Value = null;
                }
                return connection;
            }
            catch (Exception ex)
            {
                if (error != null)
                {
                    error.Value = ex.Message;
                }
                return null;
            }
        }
		public SqLiteDatabaseProvider(SQLiteConnectionStringBuilder connectionStringBuilder)
			: base(connectionStringBuilder)
		{
			Enforce.Argument(() => connectionStringBuilder);

			this.connectionStringBuilder = connectionStringBuilder;
		}
		public SqLiteDatabaseProvider(string connectionString)
			: base(connectionString)
		{
			Enforce.Argument(() => connectionString);

			connectionStringBuilder = new SQLiteConnectionStringBuilder(connectionString);
		}
 public DatabaseCreator(string filePath)
 {
     SQLiteConnection.CreateFile(filePath);
     var csb = new SQLiteConnectionStringBuilder();
     csb.DataSource = filePath;
     _connectionString = csb.ConnectionString;
 }
Exemple #17
0
    public bool Connect()
    {
        Debug.Log("DataBase connect: " + Predefine.DatabaseName);

        //Debug.Log("sqlite3 version: " + SQLite3.LibVersionNumber());
        if (_connection != null)
        {
            Debug.Log("dataBase has connected, needn't connect again");
            return(true);
        }
#if !RUNINSERVER
        var dbPath = "";
        Debug.Log("DataBase file path: " + dbPath);
        _connection = new SQLiteConnection(Key, dbPath, SQLiteOpenFlags.ReadOnly);
#else
#if DEBUG
        var dbPath = "D:/Work/Unity/Project/Client/p25/Assets/StreamingAssets/cfg";
#else
        var dbPath = "./cfg";
#endif
        Debug.Log("DataBase file path: " + dbPath);
        _connection = new System.Data.SQLite.SQLiteConnection();
        System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
        connstr.DataSource = dbPath;
        //connstr.
        _connection.ConnectionString = connstr.ToString();
        _connection.Open();
#endif
        return(true);
    }
Exemple #18
0
 /// <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);
 }
Exemple #19
0
        public static T GetObject <T>(string key, object value) where T : BaseObject
        {
            T      obj       = Activator.CreateInstance <T>();
            string tableName = obj.GetType().Name;//.Substring(obj.GetType().Name.LastIndexOf('.'));

            if (commonConnection == null)
            {
                commonConnection = new SQLiteConnection();
                SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = DbFileName;
                commonConnection.ConnectionString = connstr.ToString();
            }
            try
            {
                commonConnection.Open();
                SQLiteCommand cmd = new SQLiteCommand(commonConnection);
                cmd.CommandText = "Select * from " + tableName + " where " + key + "='" + value.ToString() + "'";
                SQLiteDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    obj.SetProperties(sdr);
                }
                commonConnection.Close();
            }
            catch//(Exception ex)
            {
                commonConnection.Close();
                return(null);
            }
            return(obj);
        }
        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;
                }
            }
        }
        static partial void ApplyRestrictedCredentials(SQLiteConnectionStringBuilder b, bool admin = false)
        {
            //b.Add("InternalHost", "invalidhost");

            b.Add("InternalUser", "user3");
            b.Password = "******";
        }
Exemple #22
0
        public static void UpdataObjectById <T>(T newInstance) where T : BaseObject
        {
            SQLiteTransaction transaction = null;
            string            tableName   = typeof(T).Name;

            try
            {
                if (commonConnection == null)
                {
                    commonConnection = new SQLiteConnection();
                    SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                    connstr.DataSource = DbFileName;
                    commonConnection.ConnectionString = connstr.ToString();
                }
                commonConnection.Open();
                transaction = commonConnection.BeginTransaction();
                SQLiteCommand cmd = new SQLiteCommand(commonConnection);
                cmd.Transaction = transaction;
                cmd.CommandText = "Delete  from " + tableName + " where id='" + newInstance.Id.ToString() + "'";;
                cmd.ExecuteNonQuery();
                cmd.CommandText = BuildInsertCommand(tableName, newInstance);;
                cmd.ExecuteNonQuery();
                transaction.Commit();
                commonConnection.Close();
            }
            catch (Exception e)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                commonConnection.Close();
                throw e;
            }
        }
Exemple #23
0
 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;
 }
        /// <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);
            }
        }
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="Engine">The string name, from the GetDatabaseEngine() method</param>
        /// <param name="Host">The Database server IP Address</param>
        /// <param name="Port">The Database server Port Number</param>
        /// <param name="DatabaseName">The name of the database</param>
        /// <param name="User">A username, with database privliages</param>
        /// <param name="Pass">The password to the User</param>
        public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
        {
            // Set class variables, and create a new connection builder
            this.DatabaseEngine = GetDatabaseEngine(Engine);
            DbConnectionStringBuilder Builder;

            if (this.DatabaseEngine == DatabaseEngine.Sqlite)
            {
                string FullPath = Path.Combine(MainForm.Root, DatabaseName + ".sqlite3");
                IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
                Builder = new SQLiteConnectionStringBuilder();
                Builder.Add("Data Source", FullPath);
                Connection = new SQLiteConnection(Builder.ConnectionString);
            }
            else if (this.DatabaseEngine == DatabaseEngine.Mysql)
            {
                IsNewDatabase = false;
                Builder = new MySqlConnectionStringBuilder();
                Builder.Add("Server", Host);
                Builder.Add("Port", Port);
                Builder.Add("User ID", User);
                Builder.Add("Password", Pass);
                Builder.Add("Database", DatabaseName);
                Connection = new MySqlConnection(Builder.ConnectionString);
            }
            else
            {
                throw new Exception("Invalid Database type.");
            }
        }
Exemple #26
0
 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();
     }
       }
     }
       }
 }
Exemple #27
0
        /// <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;
        }
Exemple #28
0
        //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>
        /// Constructor
        /// </summary>
        /// <param name="Engine">The string name, for the GetDatabaseEngine() method</param>
        /// <param name="Host">The Database server IP Address</param>
        /// <param name="Port">The Database server Port Number</param>
        /// <param name="DatabaseName">The name of the database</param>
        /// <param name="User">A username, with database privliages</param>
        /// <param name="Pass">The password to the User</param>
        public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
        {
            // Set class variables, and create a new connection builder
            this.DatabaseEngine = GetDatabaseEngine(Engine);
            DbConnectionStringBuilder Builder;

            // Establish the connection
            if (this.DatabaseEngine == DatabaseEngine.Sqlite)
            {
                // Create the connection
                Builder = new SQLiteConnectionStringBuilder();
                Builder.Add("Data Source", Path.Combine(Program.RootPath, DatabaseName + ".sqlite3"));
                Connection = new SQLiteConnection(Builder.ConnectionString);
            }
            else if (this.DatabaseEngine == DatabaseEngine.Mysql)
            {
                // Create the connection
                Builder = new MySqlConnectionStringBuilder();
                Builder.Add("Server", Host);
                Builder.Add("Port", Port);
                Builder.Add("User ID", User);
                Builder.Add("Password", Pass);
                Builder.Add("Database", DatabaseName);
                Builder.Add("Convert Zero Datetime", "true");
                Connection = new MySqlConnection(Builder.ConnectionString);
            }
            else
            {
                throw new Exception("Invalid Database type.");
            }
        }
Exemple #30
0
 public DBManager(String dataSource)
 {
     // build connection string
     SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
     builder.DataSource = dataSource;
     connectionString = builder.ToString();
 }
        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 SQLiteImageCache(string dbPath)
        {
            if (sqliteAssembly == null)
            {
                sqliteAssembly = System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(ApplicationPaths.AppConfigPath, "system.data.sqlite.dll"));
                AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(SqliteResolver);
            }

            SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
            connectionstr.PageSize = 4096;
            connectionstr.CacheSize = 4096;
            connectionstr.SyncMode = SynchronizationModes.Normal;
            connectionstr.DataSource = dbPath;
            connection = new SQLiteConnection(connectionstr.ConnectionString);
            connection.Open();

            string[] queries = {"create table if not exists images (guid, width, height, updated, stream_size, data blob)",
                                "create unique index if not exists idx_images on images(guid, width)",
                               };

            foreach (var query in queries) {
                try {

                    connection.Exec(query);
                } catch (Exception e) {
                    Logger.ReportInfo(e.ToString());
                }
            }

            alive = true; // tell writer to keep going
            Async.Queue("ImageCache Writer", DelayedWriter);
        }
        private void dataGridView1_DragDrop(object sender, DragEventArgs e)
        {
            var f = e.Data.GetFormats();
            // FileDrop
            var x = f.Select(k => e.Data.GetData(k)).ToArray();

            var FileDrop = (string[])e.Data.GetData(DataFormats.FileDrop);

            FileDrop.WithEach(
                k =>
                {
                    var fi = new FileInfo(k);

                    dataGridView1.Rows.Add(
                        fi.Extension,
                        fi.Name,
                        fi.Length
                    );


                    #region add to db
                    // http://code.activestate.com/recipes/252531-storing-binary-data-in-sqlite/


                    var csb = new SQLiteConnectionStringBuilder
                    {
                        DataSource = DataSource,
                        Version = 3
                    };

                    using (var c = new SQLiteConnection(csb.ConnectionString))
                    {
                        c.Open();

                        {
                            var sql = "create table if not exists Table1 (ContentKey INTEGER PRIMARY KEY AUTOINCREMENT, ContentValue text not null, ContentBytes blob)";
                            using (var reader = new SQLiteCommand(sql, c).ExecuteReader())
                            {
                            }
                        }

                        {
                            var sql = "insert into Table1 (ContentValue, ContentBytes) values (?, ?)";
                            var cmd = new SQLiteCommand(sql, c);
                            cmd.Parameters.AddWithValue("", fi.Name);
                            cmd.Parameters.AddWithValue("", File.ReadAllBytes(fi.FullName));

                            using (var reader = cmd.ExecuteReader())
                            {
                            }
                        }
                    }
                    #endregion
                }
            );

            SystemSounds.Beep.Play();
            //Console.Beep();
        }
 /// <summary>
 /// 获得连接数据库
 /// </summary>
 /// <param name="datasource"></param>
 /// <returns></returns>
 public SQLiteConnection getSqlLiteDB(String datasource)
 {
     SQLiteConnection conn = new SQLiteConnection();
     SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
     connstr.DataSource = "databases/" + datasource + ".db";
     conn.ConnectionString = connstr.ToString();
     return conn;
 }
Exemple #35
0
        private static string GetConnectionString(string path)
        {
            var csb = new SQLiteConnectionStringBuilder();
            csb.DataSource = path;
            csb.Version = 3;

            return csb.ConnectionString;
        }
        /// <inheritDoc />
        protected override string BuildConnectionString(string dsn, string user, string password, PhpArray options)
        {
            var csb = new ConnectionStringBuilder();

            csb.DataSource = dsn;
            csb.Add("Password", password);
            return(csb.ConnectionString);
        }
Exemple #37
0
        private void VeriTabaniniOlustur()
        {
            if (!Directory.Exists(Application.StartupPath + @"\Yazdirma"))
            {
                Directory.CreateDirectory(Application.StartupPath + @"\Yazdirma");
            }

            string DbDosya = Application.StartupPath + @"\Yazdirma\YaziciAyarlari.sqlite";


            System.Data.SQLite.SQLiteConnectionStringBuilder connectStr =
                new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connectStr.DataSource       = DbDosya;
            connectStr.UseUTF16Encoding = true;

            try
            {
                sqlKonneksin =
                    new System.Data.SQLite.SQLiteConnection(connectStr.ConnectionString);
                {
                    // bu çalıştığında Dosya yoksa Oluşturuyor;

                    using (System.Data.SQLite.SQLiteCommand sqlCommand =
                               new System.Data.SQLite.SQLiteCommand(sqlKonneksin))
                    {
                        // Veritabanı dosyasının varlğının kontrol&uuml;
                        if (!File.Exists(DbDosya))
                        {
                            System.Data.SQLite.SQLiteConnection.CreateFile(DbDosya);


                            sqlKonneksin.Open();
                            sqlCommand.CommandText = @"--Table: Logs

--DROP TABLE Logs;

CREATE TABLE YaziciAyarlari (
ID     integer NOT NULL PRIMARY KEY AUTOINCREMENT,
RaporDizaynID INT, 
ModulID integer,
YaziciAdi nvarchar(500),
KagitKaynagi nvarchar(50),
KagitKaynagiIndex integer,
RenkliMi bit,
KagitTipi integer,
CiftTarafliMi integer,
Aciklama nvarchar(500)
)";
                            sqlCommand.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception e)
            {
            }
        }
Exemple #38
0
 public static void SqlServerToSqlLite(string db, string sql)
 {
     //string sql = "select  [gzDay],[gzMonth],[gzYear],DateValue=rtrim([DateValue])+' '+ substring(JieQi,4,5),[weekDay],[constellation],JieQi,[nlMonth],[nlDay]  from [ChineseTenThousandCalendar] where left(ltrim(JieQi),2) in (" + JieQiHelper.GetInJieQis() + ")";
     System.Data.SqlClient.SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection();
     sqlCon.ConnectionString = "server=(local);user id=sa;password=***;initial catalog=HanZiMisc;TimeOut=10000;Packet Size=4096;Pooling=true;Max Pool Size=100;Min Pool Size=1";
     System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(sql);
     sqlCmd.Connection = sqlCon;
     sqlCon.Open();
     System.Data.SqlClient.SqlDataReader sqlReader = sqlCmd.ExecuteReader();
     if (sqlReader != null)
     {
         string datasource = db;// Application.StartupPath + "/JieQi.db";
         System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
         //连接数据库
         System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
         System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
         connstr.DataSource    = datasource;
         connstr.Password      = "******";//可以设密码
         conn.ConnectionString = connstr.ToString();
         conn.Open();
         //conn.ChangePassword("nguchen");//可以改已打开CON的密码
         //创建表
         System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
         string sqlc = "CREATE TABLE ChinaJiQiTable(ID Integer PRIMARY KEY,DayGZ TEXT(4) NULL,MonthGZ TEXT(4) NULL,YearGZ TEXT(4) NULL,	DateValue datetime NULL,Week TEXT(6) NULL,Star TEXT(6) NULL,	JieQi TEXT(30) NULL,	NongLiMonth TEXT(6) NULL,NongLiDay TEXT(4) NULL)";
         cmd.CommandText = sqlc;
         cmd.Connection  = conn;
         cmd.ExecuteNonQuery();
         //插入数据
         SQLiteParameter[] sqlparams = new SQLiteParameter[]
         {
             new SQLiteParameter("@ID", DbType.Int64, 10),
             new SQLiteParameter("@dG", DbType.String, 4),
             new SQLiteParameter("@mG", DbType.String, 4),
             new SQLiteParameter("@yG", DbType.String, 4),
             new SQLiteParameter("@start", DbType.String, 6),
             new SQLiteParameter("@wk", DbType.String, 6),
             new SQLiteParameter("@date", DbType.DateTime),
             new SQLiteParameter("@jieqi", DbType.String, 30),
             new SQLiteParameter("@nM", DbType.String, 6),
             new SQLiteParameter("@nD", DbType.String, 6),
         };
         cmd.Parameters.AddRange(sqlparams);
         while (sqlReader.Read())
         {
             InsertSQLiteGZTable(sqlReader["DateValue"].ToString().Trim(), sqlReader["weekDay"].ToString().Trim(), sqlReader["constellation"].ToString().Trim(),
                                 sqlReader["gzYear"].ToString().Trim(), sqlReader["gzMonth"].ToString().Trim(), sqlReader["gzDay"].ToString().Trim(), sqlReader["JieQi"].ToString().Trim(),
                                 sqlReader["nlMonth"].ToString().Trim(), sqlReader["nlDay"].ToString().Trim(), conn, cmd);
         }
         sqlReader.Close();
         conn.Close();
         cmd.Dispose();
     }
     sqlCon.Close();
     sqlCmd = null;
     sqlCon.Dispose();
 }
Exemple #39
0
        public string GetConnection()
        {
            var d = new System.Data.SQLite.SQLiteConnectionStringBuilder()
            {
                DataSource = DB_NAME
            };

            d.Add("mode", "memory");
            d.Add("cache", "shared");
            return(d.ToString());
        }
Exemple #40
0
        private static Lite.SQLiteConnection GetSqLiteConnection(string exeFolder)
        {
            var connStringBuilder = new Lite.SQLiteConnectionStringBuilder()
            {
                DataSource  = exeFolder + "\\Metadata.db",
                ForeignKeys = true
            };

            return(new Lite.SQLiteConnection()
            {
                ConnectionString = connStringBuilder.ConnectionString
            });
        }
Exemple #41
0
        public static string ConnectionStringExample()
        {
            var b = new System.Data.SQLite.SQLiteConnectionStringBuilder(@"Data Source=e:\\StorageDB\\StorageDB.sqlite");

            b.CacheSize         = 8192;
            b.JournalMode       = System.Data.SQLite.SQLiteJournalModeEnum.Wal;
            b.ForeignKeys       = true;
            b.RecursiveTriggers = true;
            b.Enlist            = true;
            b.SyncMode          = System.Data.SQLite.SynchronizationModes.Normal;
            b.ReadOnly          = true;
            b.Pooling           = true;
            return(b.ToString());
        }
Exemple #42
0
        public static string GetConnectString(string sqliteDataSource)
        {
            var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder {
                DataSource   = sqliteDataSource,
                Version      = 3,
                LegacyFormat = false,
                //PageSize = 8192,
                //CacheSize = 81920,
                SyncMode    = SynchronizationModes.Full, //途中で強制的に電源をOFFにすることも考えられるため。
                JournalMode = SQLiteJournalModeEnum.Default
            };

            return(builder.ToString());
        }
Exemple #43
0
 /// <summary>
 /// 创建数据库
 /// </summary>
 /// <param name="datasource"></param>
 /// <param name="pwd"></param>
 public static void CreateDB(string datasource, string pwd)
 {
     //创建一个数据库文件
     //string datasource = @"F:\Source\Solution_Wanxiang2011\WinAppTestSQLite\bin\Debug\test1234.db";
     System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
     //连接数据库
     System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
     System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
     connstr.DataSource    = datasource;
     connstr.Password      = pwd;    // "adminwx";//设置密码,SQLite ADO.NET实现了数据库密码保护
     conn.ConnectionString = connstr.ToString();
     conn.Open();
     //创建表
 }
        private void buttonConnectOption_Click()
        {
            try
            {
                //-----< オプションを指定して接続 >-----
                var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder
                {
                    DataSource  = CONNECTION_STRING,
                    SyncMode    = SynchronizationModes.Normal,
                    JournalMode = SQLiteJournalModeEnum.Persist,
                };
                string connectionString = builder.ToString();


                List <Artist> artists = new List <Artist>();
                //-----< オプションを指定して接続 >-----
                //using (var con = new SQLiteConnection(connectionString)) //上手く行かない・・・。
                using (var con = new SQLiteConnection(CONNECTION_STRING))
                {
                    con.Open();

                    using (var cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "SELECT * FROM artists";

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Artist _artist = new Artist();
                                _artist.ID   = reader.GetInt16(0);
                                _artist.Name = reader.GetString(1);
                                //_artist.CreatedAt = reader.GetValue(0);
                                //_artist.UpdatedAt = reader.GetValues(0);

                                artists.Add(_artist);
                            }
                        }
                    }
                }

                //-----< オプションを指定して接続 >-----
                myDataGrid01.ItemsSource = artists;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Exemple #45
0
        /// <summary>
        /// 创建
        /// </summary>
        private void connectDatabase()
        {
            //连接
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            //connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            //创建表格
            //System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            //string sql = "CREATE TABLE control_authority(ElementName varchar(32), MinLevel int,ToStr varchar(32))";
            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            //cmd.ExecuteNonQuery();
        }
Exemple #46
0
        public static SQLiteDataReader ExecuteDataReader(string sql, string db)
        {
            string datasource = db;// Application.StartupPath + "/nongli.db";

            //System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
            //连接数据库
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource    = datasource;
            connstr.Password      = "******";
            conn.ConnectionString = connstr.ToString();
            conn.Open();
            //取出数据
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            cmd.Connection  = conn;
            cmd.CommandText = sql;
            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
            return(reader);
        }
Exemple #47
0
        public static void Main(string[] args)
        {
            // create options builder


            /*builder. Mode = SqliteOpenMode.ReadOnly;
             * builder.Cache = SqliteCacheMode.Shared;
             * builder.ForeignKeys = true;
             * builder.RecursiveTriggers = true;*/
            // configure connection string
            //builder.UseSQLite(@"e:\StorageDB\StorageDB.sqlite");
            var b = new System.Data.SQLite.SQLiteConnectionStringBuilder(@"Data Source=e:\\StorageDB\\StorageDB.sqlite");

            b.CacheSize         = 8192;
            b.JournalMode       = System.Data.SQLite.SQLiteJournalModeEnum.Wal;
            b.ForeignKeys       = true;
            b.RecursiveTriggers = true;
            b.Enlist            = true;
            b.PageSize          = 4096;
            b.SyncMode          = System.Data.SQLite.SynchronizationModes.Full;
            b.ReadOnly          = true;
            b.Pooling           = true;


            var o = new LinqToDbConnectionOptionsBuilder();
            var p = o.UseSQLite(b.ToString());

            //System.Data.SQLite.SQLiteFunction
            // pass configured options to data connection constructor
            //List<Item> items = null;

            /*using (var dc = new SQLiteProvider(p.Build()))
             * {
             *
             *      //		items = (from i in dc.Items where i.ItemFileName.StartsWith("P") select i).ToList();
             * }
             *
             * foreach (var i in items)
             *      Console.WriteLine($"{i}");*/
            Console.ReadKey();
        }
Exemple #48
0
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static bool CreatNewDB(string dbName)
        {
            try
            {
                if (dbName == "")
                {
                    dbName = "yanshanshuo.db";
                }
                string json = File.ReadAllText(ConfigurationManager.AppSettings["DataBaseCreate"]);
                List <Model.TableFrame> tables = LitJson.JsonMapper.ToObject <List <Model.TableFrame> >(json);;

                //判断数据文件是否存在,存在的话就删除
                string datasource = GetPath() + "App_Data\\" + dbName;
                System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
                System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                //connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connstr.ToString();
                conn.Open();
                foreach (Model.TableFrame table in tables)
                {
                    string columnString = "";
                    foreach (Model.ColumnFrame c in table.column)
                    {
                        columnString += c.ColumnName + " " + c.DataType + " " + c.Condition + ",";
                    }
                    columnString = columnString.Substring(0, columnString.Length - 1);
                    //创建表
                    System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                    string sql = "CREATE TABLE " + table.TableName + "(" + columnString + ")";
                    cmd.CommandText = sql;
                    cmd.Connection  = conn;
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                conn.Dispose();
                return(true);
            }
            catch (Exception ex) { return(false); }
        }
        public void OpenDb()
        {
            if (_isopen && _conn != null)
            {
                return;
            }
            try
            {
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = this._kqfilepath;
                //connstr.Password = "******";
                //_conn = new SQLiteConnection(string.Format(@"Data Source={0};", _kqfilepath));
                _conn = new SQLiteConnection();
                _conn.ConnectionString = connstr.ToString();

                if (File.Exists(this._kqfilepath))
                {
                    _conn.Open();
                    _isopen = true;
                }
                else
                {
                    _conn.Open();
                    if (CreateTable())
                    {
                        _isopen = true;
                    }
                }
                return;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("打开KQDB失败");
                _isopen = false;
                if (_conn != null)
                {
                    _conn = null;
                }
                return;
            }
        }
Exemple #50
0
        /// <summary>
        /// 查询配置
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static List <ConfigClass> query(string name)
        {
            try
            {
                List <ConfigClass> list = new List <ConfigClass>();
                SQLiteConnection   conn = new SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource    = SqlLiteHelper.getSQLiteConn();
                connstr.Password      = "******";           //设置密码,SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connstr.ToString();
                SQLiteCommand cmd = new SQLiteCommand(); //是不是很熟悉呢?

                DateTime StartComputerTime = DateTime.Now;

                cmd.Connection = conn;

                if (name == null)
                {
                    cmd.CommandText = "select * from t_config order by id asc";
                }
                else
                {
                    cmd.CommandText = "select * from t_config where name='" + name + "' order by id asc";
                }
                conn.Close();
                conn.Open();
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ConfigClass config = new ConfigClass();
                    config.Id          = reader["id"].ToString();
                    config.Name        = reader["name"].ToString();
                    config.FormalValue = reader["formalValue"].ToString();
                    config.TestValue   = reader["testValue"].ToString();
                    config.Remark      = reader["remark"].ToString();
                    list.Add(config);
                }
                conn.Close();
                return(list);
            }catch (Exception ex) { return(new List <ConfigClass>()); }
        }
Exemple #51
0
        static void Main(string[] args)
        {
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connStr.DataSource    = @"F:\John's Dir\Principal Project\更新发布工具\Deployer\beta0.3\srvDB";
            conn.ConnectionString = connStr.ToString();
            conn.Open();
            string str = "select * from Deployer_WeblogicAppInfo_ML";

            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            cmd.CommandText = str;
            cmd.Connection  = conn;
            SQLiteDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            while (reader.Read())
            {
                System.Console.WriteLine(reader.GetValue(1));
            }

            System.Console.Read();
        }
Exemple #52
0
        /// <summary>
        /// 修改配置
        /// </summary>
        /// <param name="config"></param>
        /// <returns></returns>
        public static bool update(ConfigClass config)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection();
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource    = SqlLiteHelper.getSQLiteConn();
                connstr.Password      = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
                conn.ConnectionString = connstr.ToString();
                SQLiteCommand comm = new SQLiteCommand(conn);
                comm.CommandText = "update t_config set testValue='" + config.FormalValue + "',formalValue='" + config.FormalValue + "' where name='" + config.Name + "'";

                conn.Open();
                int result = comm.ExecuteNonQuery();
                if (result > 0)
                {
                    return(true);
                }
                conn.Close();
                return(false);
            }
            catch (Exception ex) { return(false); }
        }
Exemple #53
0
 public static void InsertRangeData(Guid id, List <PublicProtocal.Coordinate> queue)
 {
     if (dataConnection == null)
     {
         dataConnection = new SQLiteConnection();
         SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
         connstr.DataSource = DbFileName;
         dataConnection.ConnectionString = connstr.ToString();
     }
     try
     {
         commonConnection.Open();
         SQLiteCommand cmd = new SQLiteCommand(commonConnection);
         cmd.CommandText = string.Format("Insert into CurveData([X],[Y],[id]) values(@p0,@p1,@p2)");
         cmd.Parameters.Add("@p0", System.Data.DbType.Decimal);
         cmd.Parameters.Add("@p1", System.Data.DbType.Decimal);
         cmd.Parameters.Add("@p2", System.Data.DbType.Decimal);
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.ExecuteNonQuery();
         for (int i = 0; i < queue.Count; i++)
         {
             try
             {
                 cmd.Parameters["@p0"].Value = queue[i].X;
                 cmd.Parameters["@p1"].Value = queue[i].Y;
                 cmd.Parameters["@p2"].Value = id;
                 cmd.ExecuteNonQuery();
             }
             catch { continue; }
         }
     }
     catch { return; }
     finally
     {
         commonConnection.Close();
     }
 }
Exemple #54
0
 public static void InsertObject <T>(T instance) where T : BaseObject
 {
     try
     {
         string tableName = typeof(T).Name;//;.Substring(typeof(T).Name.LastIndexOf('.'));
         if (commonConnection == null)
         {
             commonConnection = new SQLiteConnection();
             SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
             connstr.DataSource = DbFileName;
             commonConnection.ConnectionString = connstr.ToString();
         }
         commonConnection.Open();
         SQLiteCommand cmd = new SQLiteCommand(commonConnection);
         cmd.CommandText = BuildInsertCommand(tableName, instance);
         cmd.ExecuteNonQuery();
         commonConnection.Close();
     }
     catch (Exception e)
     {
         commonConnection.Close();
         throw e;
     }
 }
Exemple #55
0
        private void SqliteTest(object sender, RoutedEventArgs e)
        {
            string datasource = "test.db";

            System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            connstr.CacheSize  = 1024;
            //  connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";

            cmd.CommandText = sql;
            cmd.Connection  = conn;
            cmd.ExecuteNonQuery();

            sql             = "INSERT INTO test VALUES('a','b')";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql             = "SELECT * FROM test";
            cmd.CommandText = sql;
            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
            StringBuilder sb = new StringBuilder();

            while (reader.Read())
            {
                sb.Append("username:"******"\n")
                .Append("password:").Append(reader.GetString(1));
            }
            MessageBox.Show(sb.ToString());
        }
Exemple #56
0
 public static void DeleteObjectById <T>(Guid id)
 {
     try
     {
         string tableName = typeof(T).Name;
         if (commonConnection == null)
         {
             commonConnection = new SQLiteConnection();
             SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
             connstr.DataSource = DbFileName;
             commonConnection.ConnectionString = connstr.ToString();
         }
         commonConnection.Open();
         SQLiteCommand cmd = new SQLiteCommand(commonConnection);
         cmd.CommandText = "Delete  from " + tableName + " where id='" + id.ToString() + "'";
         cmd.ExecuteNonQuery();
         commonConnection.Close();
     }
     catch (Exception e)
     {
         commonConnection.Close();
         throw e;
     }
 }
Exemple #57
0
        public static void CheckDataFile(string datasource)
        {
            //string datasource = Application.StartupPath + "\\data.db";
            System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
            //连接数据库
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            //connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connstr.ToString();
            conn.Open();
            //创建表
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            string sql = "CREATE TABLE billInfo (BillID varchar(50) PRIMARY KEY,SiteName varchar(50),SiteUserName varchar(50),TotalMoney decimal(5,2),YearRete decimal(5,2),Deadline interger ,DeadlineType interger,ReceivedPeriod interger,ReceivablePeriod interger,ReceivablePrincipalAndInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),WayOfRepayment interger,Reward decimal(5,2),BeginDay varchar(20),EndDay varchar(20),YuQiCount interger,Deleted interger,Flag interger,Remark varchar(200),UpdateTime datetime,CreateTime datetime)";

            cmd.CommandText = sql;
            cmd.Connection  = conn;
            cmd.ExecuteNonQuery();

            sql             = "Create Table billdetail(BillDetailID varchar(50) PRIMARY KEY,BillID varchar(50),Periods varchar(50),ReceivableDay varchar(20),ReceivedDay varchar(20),ReceivablePrincipalAndInterest decimal(5,2),ReceivableInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),IsYuQi interger ,Deleted interger,Flag interger,UpdateTime datetime,CreateTime datetime)";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public override void Open()
        {
            VerifyNotDisposed();
            if (State != ConnectionState.Closed)
            {
                throw new InvalidOperationException("Cannot Open when State is {0}.".FormatInvariant(State));
            }

            var connectionStringBuilder = new SQLiteConnectionStringBuilder {
                ConnectionString = ConnectionString
            };

            m_dataSource = connectionStringBuilder.DataSource;
            if (string.IsNullOrEmpty(m_dataSource))
            {
                throw new InvalidOperationException("Connection String Data Source must be set.");
            }

            SQLiteOpenFlags openFlags = connectionStringBuilder.ReadOnly ? SQLiteOpenFlags.ReadOnly : SQLiteOpenFlags.ReadWrite;

            if (!connectionStringBuilder.FailIfMissing && !connectionStringBuilder.ReadOnly)
            {
                openFlags |= SQLiteOpenFlags.Create;
            }

            SetState(ConnectionState.Connecting);

            Match  m         = s_vfsRegex.Match(m_dataSource);
            string fileName  = m.Groups["fileName"].Value;
            string vfsName   = m.Groups["vfsName"].Value;
            var    errorCode = NativeMethods.sqlite3_open_v2(ToNullTerminatedUtf8(fileName), out m_db, openFlags, string.IsNullOrEmpty(vfsName) ? null : ToNullTerminatedUtf8(vfsName));

            bool success = false;

            try
            {
                if (errorCode != SQLiteErrorCode.Ok)
                {
                    SetState(ConnectionState.Broken);
                    errorCode.ThrowOnError();
                }

                if (!string.IsNullOrEmpty(connectionStringBuilder.Password))
                {
                    byte[] passwordBytes = Encoding.UTF8.GetBytes(connectionStringBuilder.Password);
                    NativeMethods.sqlite3_key(m_db, passwordBytes, passwordBytes.Length).ThrowOnError();
                }

                int isReadOnly = NativeMethods.sqlite3_db_readonly(m_db, "main");
                if (isReadOnly == 1 && !connectionStringBuilder.ReadOnly)
                {
                    throw new SQLiteException(SQLiteErrorCode.ReadOnly);
                }

                if (connectionStringBuilder.CacheSize != 0)
                {
                    this.ExecuteNonQuery("pragma cache_size={0}".FormatInvariant(connectionStringBuilder.CacheSize));
                }

                if (connectionStringBuilder.PageSize != 0)
                {
                    this.ExecuteNonQuery("pragma page_size={0}".FormatInvariant(connectionStringBuilder.PageSize));
                }

                if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.MmapSizeKey))
                {
                    this.ExecuteNonQuery("pragma mmap_size={0}".FormatInvariant(connectionStringBuilder.MmapSize));
                }

                if (connectionStringBuilder.ForeignKeys)
                {
                    this.ExecuteNonQuery("pragma foreign_keys = on");
                }

                if (connectionStringBuilder.JournalMode != SQLiteJournalModeEnum.Default)
                {
                    this.ExecuteNonQuery("pragma journal_mode={0}".FormatInvariant(connectionStringBuilder.JournalMode));
                }

                if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.SynchronousKey))
                {
                    this.ExecuteNonQuery("pragma synchronous={0}".FormatInvariant(connectionStringBuilder.SyncMode));
                }

                if (connectionStringBuilder.TempStore != SQLiteTemporaryStore.Default)
                {
                    this.ExecuteNonQuery("pragma temp_store={0}".FormatInvariant(connectionStringBuilder.TempStore));
                }

                if (m_statementCompleted != null)
                {
                    NativeMethods.sqlite3_profile(m_db, m_profileCallback, IntPtr.Zero);
                }

                SetState(ConnectionState.Open);
                success = true;
            }
            finally
            {
                if (!success)
                {
                    Utility.Dispose(ref m_db);
                }
            }
        }
Exemple #59
0
        public override void Open()
        {
            VerifyNotDisposed();
            if (State != ConnectionState.Closed)
            {
                throw new InvalidOperationException("Cannot Open when State is {0}.".FormatInvariant(State));
            }

            var connectionStringBuilder = new SQLiteConnectionStringBuilder {
                ConnectionString = ConnectionString
            };

            m_dataSource = connectionStringBuilder.DataSource;
            if (string.IsNullOrEmpty(m_dataSource))
            {
                throw new InvalidOperationException("Connection String Data Source must be set.");
            }

            SQLiteOpenFlags openFlags = connectionStringBuilder.ReadOnly ? SQLiteOpenFlags.ReadOnly : SQLiteOpenFlags.ReadWrite;

            if (!connectionStringBuilder.FailIfMissing && !connectionStringBuilder.ReadOnly)
            {
                openFlags |= SQLiteOpenFlags.Create;
            }

            SetState(ConnectionState.Connecting);

            Match  m         = s_vfsRegex.Match(m_dataSource);
            string fileName  = m.Groups["fileName"].Value;
            string vfsName   = m.Groups["vfsName"].Value;
            var    errorCode = NativeMethods.sqlite3_open_v2(ToNullTerminatedUtf8(fileName), out m_db, openFlags, string.IsNullOrEmpty(vfsName) ? null : ToNullTerminatedUtf8(vfsName));

            bool success = false;

            try
            {
                if (errorCode != SQLiteErrorCode.Ok)
                {
                    SetState(ConnectionState.Broken);
                    throw new SQLiteException(errorCode, m_db);
                }

                if (!string.IsNullOrEmpty(connectionStringBuilder.Password))
                {
                    byte[] passwordBytes = Encoding.UTF8.GetBytes(connectionStringBuilder.Password);
                    errorCode = NativeMethods.sqlite3_key(m_db, passwordBytes, passwordBytes.Length);
                    if (errorCode != SQLiteErrorCode.Ok)
                    {
                        throw new SQLiteException(errorCode, m_db);
                    }
                }

                bool allowOpenReadOnly = true;
#if MONOANDROID
                // opening read-only throws "EntryPointNotFoundException: sqlite3_db_readonly" on Android API 15 and below (JellyBean is API 16)
                allowOpenReadOnly = Android.OS.Build.VERSION.SdkInt >= Android.OS.BuildVersionCodes.JellyBean;
#endif
                if (allowOpenReadOnly)
                {
                    int isReadOnly = NativeMethods.sqlite3_db_readonly(m_db, "main");
                    if (isReadOnly == 1 && !connectionStringBuilder.ReadOnly)
                    {
                        throw new SQLiteException(SQLiteErrorCode.ReadOnly);
                    }
                }

                // wait up to ten seconds (in native code) when there is DB contention, but still give managed code a
                // chance to respond to cancellation periodically
                NativeMethods.sqlite3_busy_timeout(m_db, 10000);

                if (connectionStringBuilder.CacheSize != 0)
                {
                    this.ExecuteNonQuery("pragma cache_size={0}".FormatInvariant(connectionStringBuilder.CacheSize));
                }

                if (connectionStringBuilder.PageSize != 0)
                {
                    this.ExecuteNonQuery("pragma page_size={0}".FormatInvariant(connectionStringBuilder.PageSize));
                }

                if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.MmapSizeKey))
                {
                    this.ExecuteNonQuery("pragma mmap_size={0}".FormatInvariant(connectionStringBuilder.MmapSize));
                }

                if (connectionStringBuilder.ForeignKeys)
                {
                    this.ExecuteNonQuery("pragma foreign_keys = on");
                }

                if (connectionStringBuilder.JournalMode != SQLiteJournalModeEnum.Default)
                {
                    this.ExecuteNonQuery("pragma journal_mode={0}".FormatInvariant(connectionStringBuilder.JournalMode));
                }

                if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.SynchronousKey))
                {
                    this.ExecuteNonQuery("pragma synchronous={0}".FormatInvariant(connectionStringBuilder.SyncMode));
                }

                if (connectionStringBuilder.TempStore != SQLiteTemporaryStore.Default)
                {
                    this.ExecuteNonQuery("pragma temp_store={0}".FormatInvariant(connectionStringBuilder.TempStore));
                }

                if (m_statementCompleted != null)
                {
                    SetProfileCallback(s_profileCallback);
                }

                SetState(ConnectionState.Open);
                success = true;
            }
            finally
            {
                if (!success)
                {
                    Utility.Dispose(ref m_db);
                }
            }
        }
Exemple #60
0
        private void button1_Click(object sender, EventArgs e)
        {
            //创建一个数据库文件

            string datasource = "test.db";

            System.Data.SQLite.SQLiteConnection.CreateFile(datasource);

            //连接数据库

            System.Data.SQLite.SQLiteConnection conn =

                new System.Data.SQLite.SQLiteConnection();

            System.Data.SQLite.SQLiteConnectionStringBuilder connstr =

                new System.Data.SQLite.SQLiteConnectionStringBuilder();

            connstr.DataSource = datasource;

            connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护

            conn.ConnectionString = connstr.ToString();

            conn.Open();

            //创建表

            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();

            string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";

            cmd.CommandText = sql;

            cmd.Connection = conn;

            cmd.ExecuteNonQuery();

            //插入数据

            sql = "INSERT INTO test VALUES(’dotnetthink’,'mypassword’)";

            cmd.CommandText = sql;

            cmd.ExecuteNonQuery();

            //取出数据

            sql = "SELECT * FROM test";

            cmd.CommandText = sql;

            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();

            StringBuilder sb = new StringBuilder();

            while (reader.Read())
            {
                sb.Append("username:"******"\n")

                .Append("password:").Append(reader.GetString(1));
            }

            MessageBox.Show(sb.ToString());
        }