C# System.Data.SQLite.SQLiteConnection.Close方法代码示例

        public static void FindOpcodes(string query)
        {
            var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderByDescending(t => t);

            foreach (var file in files)
            {
                using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
                {
                    con.Open();
                    using (var sqlcommand = con.CreateCommand())
                    {
                        sqlcommand.CommandText = "select count(*) from packets where opcode in (" + query + ")";
                        var reader = sqlcommand.ExecuteReader();

                        while (reader.Read())
                        {
                            var found = reader.GetInt32(0);
                            if (found > 0)
                            {
                                System.Diagnostics.Debug.WriteLine(file + "\t" + found);
                            }
                            break;
                        }
                    }

                    con.Close();
                }
            }
        }

        public void TestCaseSensitiveKeyColumn()
        {
            var path = Path.GetTempFileName();
            try
            {
                var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path);
                sqlite.Open();
                var cmd = sqlite.CreateCommand();
                cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)";
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                sqlite.Close();
                sqlite.Dispose();
                using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID"))
                {
                    var ext = new Envelope();
                    var ds = new SharpMap.Data.FeatureDataSet();
                    sq.ExecuteIntersectionQuery(ext, ds);
                    NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count);
                }

            }
            catch (Exception ex)
            {
                Assert.Fail("Got exception, should not happen");

            }
            finally
            {
                File.Delete(path);
            }
        }

        public void UpdateDatabaseWithArteProps( string ConnectionString )
        {
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
            conn.Open();
            System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
            System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
            command.Transaction = transaction;
            foreach ( var a in ArteList ) {
                string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
                Console.WriteLine( UpdateNames );
                command.CommandText = UpdateNames;
                command.ExecuteNonQuery();
                string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
                Console.WriteLine( UpdateDescs );
                command.CommandText = UpdateDescs;
                command.ExecuteNonQuery();

                if ( a.Type == Arte.ArteType.Generic ) {
                    string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
                    Console.WriteLine( UpdateStatus );
                    command.CommandText = UpdateStatus;
                    command.ExecuteNonQuery();
                }
            }
            command.Dispose();
            transaction.Commit();
            conn.Close();
            conn.Dispose();
        }

        public List<String> checkProjects()
        {
            List<String> list = new List<String>();
            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();

                    com.CommandText = "Select * FROM LASTPROJECTS";

                    using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["address"]);

                            if (!File.Exists(reader["address"].ToString()))
                                list.Add(reader["address"].ToString());
                        }
                    }
                    con.Close();
                }
            }
            if(list.Count > 0)
                removeProject(list);

            return getLastProjects();
        }

        public static int Execute( List<string> args )
        {
            // 0xCB20

            if ( args.Count != 2 ) {
                Console.WriteLine( "Generates a scenario db for use in Tales.Vesperia.Website from a MAPLIST.DAT." );
                Console.WriteLine( "Usage: maplist.dat scenario.db" );
                return -1;
            }

            String maplistFilename = args[0];
            string connStr = "Data Source=" + args[1];

            using ( var conn = new System.Data.SQLite.SQLiteConnection( connStr ) ) {
                conn.Open();
                using ( var ta = conn.BeginTransaction() ) {
                    SqliteUtil.Update( ta, "CREATE TABLE descriptions( filename TEXT PRIMARY KEY, shortdesc TEXT, desc TEXT )" );
                    int i = 0;
                    foreach ( MapName m in new MapList( System.IO.File.ReadAllBytes( maplistFilename ) ).MapNames ) {
                        Console.WriteLine( i + " = " + m.ToString() );
                        List<string> p = new List<string>();
                        p.Add( "VScenario" + i );
                        p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
                        p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
                        SqliteUtil.Update( ta, "INSERT INTO descriptions ( filename, shortdesc, desc ) VALUES ( ?, ?, ? )", p );
                        ++i;
                    }
                    ta.Commit();
                }
                conn.Close();
            }

            return 0;
        }

        public DatabaseManager()
        {
            string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
                          [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                          [address] VARCHAR(2048)  NULL
                          )";
            string path = Path.
                GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
            path = path + @"\bin\Debug\databaseFile.db3";

            if(!File.Exists (path))
                System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();
                    com.CommandText = createTableQuery;
                    com.ExecuteNonQuery();
                    con.Close();
                }
            }
            Console.WriteLine("ALLO");
        }

        public static void DumpOpcodes()
        {
            var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderBy(t => t);

            var versionOpcodeList = new System.Collections.Generic.SortedList<uint, ClientBuildCache>();

            foreach (var file in files)
            {
                uint clientBuild = 0;

                using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
                {
                    con.Open();
                    using (var sqlcommand = con.CreateCommand())
                    {
                        sqlcommand.CommandText = "select key, value from header where key = 'clientBuild'";
                        var reader = sqlcommand.ExecuteReader();

                        while (reader.Read())
                        {
                            clientBuild = (uint)reader.GetInt32(1);
                            break;
                        }
                    }

                    if (!versionOpcodeList.ContainsKey(clientBuild))
                    {
                        versionOpcodeList.Add(clientBuild, new ClientBuildCache() { ClientBuild = clientBuild, OpcodeList = new List<OpcodeCache>() });
                    }

                    var clientBuildOpcodes = versionOpcodeList[clientBuild];

                    using (var sqlcommand = con.CreateCommand())
                    {
                        sqlcommand.CommandText = "select distinct opcode, direction from packets order by opcode , direction";
                        var reader = sqlcommand.ExecuteReader();

                        while (reader.Read())
                        {
                            var opcode = (uint)reader.GetInt32(0);
                            var direction = (byte)reader.GetInt32(1);

                            if (!clientBuildOpcodes.OpcodeList.Exists(t => t.Opcode == opcode && t.Direction == direction))
                                clientBuildOpcodes.OpcodeList.Add(new OpcodeCache() { Direction = direction, Opcode = opcode });
                        }
                    }

                    con.Close();
                }
            }

            var clientBuildOpcodeList = versionOpcodeList.Select(t => t.Value).ToList();

            clientBuildOpcodeList.SaveObject("clientBuildOpcodeList.xml");
        }

        static void ExecuteDDL()
        {
            var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
            System.Data.SQLite.SQLiteConnection.CreateFile(path);

            var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };

            using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
            {
                cn.Open();

                //  テーブル名は複数形で指定する(Memberではなく、Members)
                var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
                sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";

                var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
                cmd.ExecuteNonQuery();

                cn.Close();
            }
        }

        /*
        static void UpdateMySql(string connstr, string inputfile, string outputfile)
        {
            Console.WriteLine("Updating mysql database.");
            using(MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connstr))
            {
                conn.Open();

                string cmdtext = "update Recording set RecordingFileName = @NewFilename where RecordingFileName = @OldFilename;";
                using(MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(cmdtext, conn))
                {
                    cmd.Parameters.AddWithValue("@NewFilename", outputfile);
                    cmd.Parameters.AddWithValue("@OldFilename", inputfile);
                    int rows = cmd.ExecuteNonQuery();
                    if(rows > 0)
                        Console.WriteLine("Successfully updated database.");
                    else
                        Console.WriteLine("Failed to update database.");
                }

                conn.Close();
            }
        }

        static void UpdateSqlServer(string connstr, string inputfile, string outputfile)
        {
            Console.WriteLine("Updating SQL Server database.");
            using(SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                string cmdtext = "update Recording set RecordingFileName = @NewFilename where [email protected]";
                using(SqlCommand cmd = new SqlCommand(cmdtext, conn))
                {
                    cmd.Parameters.AddWithValue("@NewFilename", outputfile);
                    cmd.Parameters.AddWithValue("@OldFilename", inputfile);
                    int rows = cmd.ExecuteNonQuery();
                    if(rows > 0)
                        Console.WriteLine("Successfully updated database.");
                    else
                        Console.WriteLine("Failed to update database.");
                }
                conn.Close();
            }

        }
        */
        static void UpdateSqlLite(string connstr, string inputfile, string outputfile)
        {
            Console.WriteLine("Update Sql Lite database.");
            using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connstr))
            {
                conn.Open();
                string cmdtext = "update scheduled_recording set filename = @NewFilename where lower(filename) = @OldFilename";
                using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(cmdtext, conn))
                {
                    cmd.Parameters.AddWithValue("@NewFilename", outputfile);
                    cmd.Parameters.AddWithValue("@OldFilename", inputfile.ToLower());
                    int rows = cmd.ExecuteNonQuery();
                    if (rows > 0)
                        Console.WriteLine("Sucessfully updated database.");
                    else
                        Console.WriteLine("Failed to update database.");
                }
                conn.Close();
            }
        }

        static void ExecuteDDL()
        {
            var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter.db");

            if (File.Exists(path))
            {
                return;
            }

            System.Data.SQLite.SQLiteConnection.CreateFile(path);
            var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };

            CommonFunction common = new CommonFunction();
            common.setDbPath(path);

            using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
            {
                cn.Open();

                //  テーブル名は複数形で指定する(Wordではなく、Words)
                var sql = "CREATE TABLE WORD_DIC( ";
                sql += "  WORD_ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sql += "  , RONRI_NAME1 TEXT";
                sql += "  , RONRI_NAME2 TEXT";
                sql += "  , BUTSURI_NAME TEXT";
                sql += "  , USER_ID INTEGER";
                sql += "  , VERSION INTEGER";
                sql += "  , CRE_DATE TEXT";
                sql += "  , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
                sql += "); ";
                sql += "CREATE TABLE WORD_SHINSEI( ";
                sql += "  SHINSEI_ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sql += "  , RONRI_NAME1 TEXT";
                sql += "  , RONRI_NAME2 TEXT";
                sql += "  , BUTSURI_NAME TEXT";
                sql += "  , WORD_ID INTEGER";
                sql += "  , STATUS INTEGER";
                sql += "  , USER_ID INTEGER";
                sql += "  , VERSION INTEGER";
                sql += "  , CRE_DATE TEXT";
                sql += "  , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
                sql += "); ";
                sql += "CREATE TABLE USER_MST( ";
                sql += "  USER_ID INTEGER PRIMARY KEY AUTOINCREMENT";
                sql += "  , EMP_ID INTEGER UNIQUE ";
                sql += "  , USER_NAME TEXT";
                sql += "  , KENGEN INTEGER";
                sql += "  , MAIL_ID TEXT";
                sql += "  , PASSWORD TEXT";
                sql += "  , MAIL_ADDRESS TEXT";
                sql += "  , SANKA_KAHI INTEGER";
                sql += "  , DELETE_FLG INTEGER";
                sql += "  , VERSION INTEGER";
                sql += "  , CRE_DATE TEXT";
                sql += "); ";
                sql += "insert into USER_MST(USER_ID,EMP_ID,USER_NAME,KENGEN,MAIL_ID,PASSWORD,MAIL_ADDRESS,SANKA_KAHI,DELETE_FLG,VERSION) values (1,999, 'Admin',0,'999','[email protected]','[email protected]',0,0,0);";

                var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
                cmd.ExecuteNonQuery();

                cn.Close();
            }
        }

        public void CreateUserDatabase()
        {
            // This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
            var createTableQueries = new[] {@"CREATE TABLE IF NOT EXISTS [AspNetRoles] (
            [Id]   NVARCHAR (128) NOT NULL PRIMARY KEY,
            [Name] NVARCHAR (256) NOT NULL
            );",
            @"CREATE TABLE IF NOT EXISTS [AspNetUsers] (
            [Id]                   NVARCHAR (128) NOT NULL PRIMARY KEY,
            [Email]                NVARCHAR (256) NULL,
            [EmailConfirmed]       BIT            NOT NULL,
            [PasswordHash]         NVARCHAR (4000) NULL,
            [SecurityStamp]        NVARCHAR (4000) NULL,
            [PhoneNumber]          NVARCHAR (4000) NULL,
            [PhoneNumberConfirmed] BIT            NOT NULL,
            [TwoFactorEnabled]     BIT            NOT NULL,
            [LockoutEndDateUtc]    DATETIME       NULL,
            [LockoutEnabled]       BIT            NOT NULL,
            [AccessFailedCount]    INT            NOT NULL,
            [UserName]             NVARCHAR (256) NOT NULL
            );",
            @"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
            [UserId] NVARCHAR (128) NOT NULL,
            [RoleId] NVARCHAR (128) NOT NULL,
            PRIMARY KEY ([UserId], [RoleId]),
            FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
            FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
            );",
            @"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
            [LoginProvider] NVARCHAR (128) NOT NULL,
            [ProviderKey]   NVARCHAR (128) NOT NULL,
            [UserId]        NVARCHAR (128) NOT NULL,
            PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
            FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
            );",
            @"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
            [Id]    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            [UserId]     NVARCHAR (128) NOT NULL,
            [ClaimType]  NVARCHAR (4000) NULL,
            [ClaimValue] NVARCHAR (4000) NULL,
            FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
            );",
            @"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
            [MigrationId]    NVARCHAR (150)  NOT NULL,
            [ContextKey]     NVARCHAR (300)  NOT NULL,
            [Model]          VARBINARY (4000) NOT NULL,
            [ProductVersion] NVARCHAR (32)   NOT NULL,
            PRIMARY KEY ([MigrationId], [ContextKey])
            );"};

            System.Data.SQLite.SQLiteConnection.CreateFile(@"d:\work\Ricettario\Ricettario\App_Data ricettario.db3");        // Create the file which will be hosting our database
            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=d:\work\Ricettario\Ricettario\App_Data\ricettario.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();                             // Open the connection to the database

                    foreach (var createTableQuery in createTableQueries)
                    {
                        com.CommandText = createTableQuery;     // Set CommandText to our query that will create the table
                        com.ExecuteNonQuery();                  // Execute the query
                    }

                    //com.CommandText = "Select * FROM MyTable";      // Select all rows from our database table

                    //using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                    //{
                    //    while (reader.Read())
                    //    {
                    //        Console.WriteLine(reader["Key"] + " : " + reader["Value"]);     // Display the value of the key and value column for every row
                    //    }
                    //}
                    con.Close();        // Close the connection to the database
                }
            }
        }

 public void removeProject(List<String> paths)
 {
     using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
     {
         using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
         {
             con.Open();
             for (int i = 0; i < paths.Count; i++)
             {
                 com.CommandText = "DELETE FROM LASTPROJECTS WHERE address = '" + paths[i] + "'";
                 com.ExecuteNonQuery();
             }
             con.Close();
         }
     }
 }

        public string RicettarioDb()
        {
            var sb = new StringBuilder();
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RicettarioConnection"].ConnectionString;
            using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
            using (var com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open();
                com.CommandText = "Select * FROM WeekSchedule";

                using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
                    }
                }
                con.Close(); // Close the connection to the database
            }
            return sb.ToString().Substring(0, 300);
        }

        public string TestDb()
        {
            var sb = new StringBuilder();
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AccountConnection"].ConnectionString;
            using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
            using (var com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open();
                com.CommandText = "Select * FROM AspNetRoles";

                using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
                    }
                }
                con.Close(); // Close the connection to the database
            }

            return sb.ToString();
        }

    private void SaveBadgeForItem(String itemPath, String badgePath, Boolean isClear = false) {
      var m_dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source=" + this._DBPath + ";Version=3;");
      m_dbConnection.Open();
      if (isClear) {
        var command3 = new System.Data.SQLite.SQLiteCommand("DELETE FROM badges WHERE [email protected]", m_dbConnection);
        command3.Parameters.AddWithValue("Path", itemPath);
        command3.ExecuteNonQuery();
      } else {
        var command1 = new System.Data.SQLite.SQLiteCommand("SELECT * FROM badges WHERE [email protected]", m_dbConnection);
        command1.Parameters.AddWithValue("Path", itemPath);
        var Reader = command1.ExecuteReader();
        var sql = Reader.Read()
        ? @"UPDATE badges  SET Collection = @Collection, Badge = @Badge	 WHERE Path = @Path"
        : @"INSERT INTO badges (Path, Collection, Badge) VALUES (@Path, @Collection, @Badge)";

        var command2 = new System.Data.SQLite.SQLiteCommand(sql, m_dbConnection);
        command2.Parameters.AddWithValue("Path", itemPath);
        command2.Parameters.AddWithValue("Collection", Path.GetFileName(Path.GetDirectoryName(badgePath)));
        command2.Parameters.AddWithValue("Badge", Path.GetFileName(badgePath));
        command2.ExecuteNonQuery();
        Reader.Close();
      }

      m_dbConnection.Close();
    }