C# SQLiteCommand.Prepare方法代码示例

        public bool deleteDepartmentById(int id)
        {
            SQLiteConnection connection = DbConnection;

            try
            {
                connection.Open();
                SQLiteCommand command = new SQLiteCommand(connection);

                command.CommandText = "DELETE FROM `departments` WHERE id = @id";
                command.Prepare();

                command.Parameters.AddWithValue("@id", id);

                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        public static bool AddCalenderItem(CalenderItemDto dto, SQLiteConnection conn = null)
        {
            bool closeCon = false;
            if (conn == null)
            {
                conn = DatabaseHelper.GetDatabaseConnection();
                closeCon = true;
            }

            using (SQLiteCommand command = new SQLiteCommand(conn))
            {
                command.CommandText = ADD_ITEM_SQL;
                command.Prepare();
                command.Parameters.AddWithValue("@Name", dto.Name);
                command.Parameters.AddWithValue("@Type", dto.Type.ToString());
                command.Parameters.AddWithValue("@Date", dto.ItemDate.Date.ToString());
                command.Parameters.AddWithValue("@Time", dto.ItemDate.TimeOfDay.ToString());
                command.Parameters.AddWithValue("@Complete", dto.done);
                command.Parameters.AddWithValue("@Details", dto.Details);
                command.Parameters.AddWithValue("@CategoryId", dto.categoryId);
                command.Parameters.AddWithValue("@FilePaths", dto.AddFilesToArchive());

                dto.id = command.ExecuteNonQuery();
            }

            if (closeCon) conn.Close();
            return false;
        }

        public IEnumerable<string> searchByGenre(string genre)
        {
            SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
            if (genre == null)
            {
                return getList(searchCom);
            }
            searchCom.CommandText =
                @"SELECT Title FROM Music WHERE Genre LIKE @searchGenre Union ALL SELECT Title FROM Video WHERE Genre LIKE @searchGenre;";
            searchCom.Parameters.AddWithValue("@searchGenre", $"%{genre}%");
            searchCom.Prepare();

            return getList(searchCom);
        }

        public IEnumerable<string> searchByArtist(string artist)
        {
            SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
            if (artist == null)
            {
                return getList(searchCom);
            }
            searchCom.CommandText =
                @"SELECT Title FROM Music WHERE Artist LIKE @searchArtist UNION ALL SELECT Title FROM Video WHERE Publisher LIKE @searchArtist";
            searchCom.CommandType = CommandType.Text;
            searchCom.Parameters.AddWithValue("@searchArtist", $"%{artist}%");
            searchCom.Prepare();

            return getList(searchCom);
        }

        /// <summary>
        /// Insert a podcast into the database
        /// </summary>
        /// <param name="pi">Podcast information to add</param>
        /// <param name="strFileName">File name of the podcast</param>
        public void InsertPodcast(PodcastInfo pi, string strFileName)
        {
            Connect();

            SQLiteCommand sql = new SQLiteCommand("INSERT INTO Episodes (ShowID, Title, Description, FileName, Release) VALUES (@ShowID, @Title, @Description, @FileName, @Release)", conn);
            sql.Prepare();
            sql.Parameters.AddWithValue("@ShowID", pi.kShow.nID);
            sql.Parameters.AddWithValue("@Title", pi.strTitle);
            sql.Parameters.AddWithValue("@Description", pi.strDescription);
            sql.Parameters.AddWithValue("@FileName", strFileName);
            sql.Parameters.AddWithValue("@Release", pi.dEnded);

            sql.ExecuteNonQuery();

            conn.Close();
        }

        static void Main(string[] args)
        {
            // Get current directory info
            DirectoryInfo di = new DirectoryInfo(".");

            // Get file info for files in current directory and subfolders
            FileInfo[] fi = di.GetFiles("*.sqlite", SearchOption.AllDirectories);

            // Output small info
            Console.WriteLine("Found {0} files to parse", fi.Length);

            // Create dump file for each database
            foreach (FileInfo f in fi)
            {
                Console.WriteLine("Parsing {0}...", f.Name);

                string ofn = f.FullName + ".dump";

                BinaryWriter bw = new BinaryWriter(new FileStream(ofn, FileMode.Create));

                SQLiteConnection connection = new SQLiteConnection("Data Source=" + f.Name);
                SQLiteCommand command = new SQLiteCommand(connection);
                connection.Open();
                command.CommandText = "SELECT opcode, data FROM packets;";
                command.Prepare();
                SQLiteDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    ushort opcode = (ushort)reader.GetInt16(0);
                    byte[] data = (byte[])reader.GetValue(1);

                    uint size = sizeof(ushort) + (uint)data.Length;

                    bw.Write(size);
                    bw.Write(opcode);
                    bw.Write(data);
                }

                reader.Close();
                connection.Close();

                bw.Flush();
                bw.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="profileName"></param>
        /// <returns></returns>
        public static DatabaseMessage DeleteIfExist_NetworkPage(string profileName)
        {
            string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            SQLiteConnection conn = null;

            try
            {
                conn = new SQLiteConnection(ConString);
                conn.Open();

                SQLiteCommand delCmd = new SQLiteCommand();
                delCmd.Connection = conn;
                delCmd.CommandText = "DELETE FROM network WHERE profileName = @profileName";
                delCmd.Prepare();
                delCmd.Parameters.AddWithValue("@profilename", profileName);
                int affectedRows = delCmd.ExecuteNonQuery();

                if (conn != null)
                {
                    conn.Close();
                }

                if (affectedRows > 0) return DatabaseMessage.entryExists;
                return DatabaseMessage.entryDoesNotExists;
            }
            catch (SQLiteException ex)
            {

                if (conn != null)
                {
                    conn.Close();
                }

                Console.WriteLine("Error: {0}", ex.ToString());
                return DatabaseMessage.databaseError;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        private string execScalar(string sql) {
            SQLiteConnection conn = new SQLiteConnection(this.dataSource);
            SQLiteCommand cmd = new SQLiteCommand(conn);
            object result = null;
            try {
                cmd.CommandText = sql;
                cmd.Prepare();
                conn.Open();
                result = cmd.ExecuteScalar();
            }
            catch (SQLiteException e) {
                Console.WriteLine("SQLite exception: {0}", e);
            }
            finally {
                conn.Close();
            }

            return result != null ? result.ToString() : "";
        }

        private int execNonQuery(string sql) {
            SQLiteConnection conn = new SQLiteConnection(this.dataSource);
            SQLiteCommand cmd = new SQLiteCommand(conn);
            int rowsAffected = 0;
            try {
                cmd.CommandText = sql;
                cmd.Prepare();
                conn.Open();
                rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (SQLiteException e) {
                Console.WriteLine("SQLite exception: {0}", e);
            }
            finally {
                conn.Close();
            }

            return rowsAffected;
        }

        public static bool AddItem(CatagoryDto dto, SQLiteConnection conn = null)
        {
            bool closeCon = false;
            if (conn == null)
            {
                conn = DatabaseHelper.GetDatabaseConnection();
                closeCon = true;
            }

            using (SQLiteCommand command = new SQLiteCommand(conn))
            {
                command.CommandText = ADD_ITEM_SQL;
                command.Prepare();
                command.Parameters.AddWithValue("@Name", dto.Name);
                command.Parameters.AddWithValue("@Color", dto.Color.ToString());
                dto.id = command.ExecuteNonQuery();
            }

            if (closeCon) conn.Close();
            return false;
        }

        public EDDIStarSystem GetEDDIStarSystem(string name)
        {
            if (!File.Exists(DbFile)) return null;

            EDDIStarSystem result = null;
            try
            {
                using (var con = SimpleDbConnection())
                {
                    con.Open();
                    using (var cmd = new SQLiteCommand(con))
                    {
                        cmd.CommandText = SELECT_BY_NAME_SQL;
                        cmd.Prepare();
                        cmd.Parameters.AddWithValue("@name", name);
                        using (SQLiteDataReader rdr = cmd.ExecuteReader())
                        {
                            if (rdr.Read())
                            {
                                result = new EDDIStarSystem();
                                if (!rdr.IsDBNull(0)) result.EliteID = rdr.GetInt32(0);
                                if (!rdr.IsDBNull(1)) result.EDDBID = rdr.GetInt32(1);
                                result.Name = rdr.GetString(2);
                                result.TotalVisits = rdr.GetInt32(3);
                                result.LastVisit = rdr.GetDateTime(4);
                                if (!rdr.IsDBNull(5)) result.PreviousVisit = rdr.GetDateTime(5);
                                result.StarSystem = JsonConvert.DeserializeObject<StarSystem>(rdr.GetString(6));
                                result.StarSystemLastUpdated = rdr.GetDateTime(7);
                            }
                        }
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return result;
        }

        public IEnumerable<string> searchByArtistAndGenre(string searchArtist, string searchGenre)
        {
            SQLiteCommand searchCom = new SQLiteCommand(_dbConnection);
            if (!string.IsNullOrEmpty(searchArtist)&& string.IsNullOrEmpty(searchGenre))//search Artist
            {
                return searchByArtist(searchArtist);
            }
            else if (string.IsNullOrEmpty(searchArtist) && !string.IsNullOrEmpty(searchGenre))//search Genre
            {
                return searchByGenre(searchGenre);
            }
            else if (!string.IsNullOrEmpty(searchArtist))//search Both
            {
                searchCom.CommandText =
                    @"SELECT Title FROM Music WHERE Artist LIKE @searchArtist AND Genre Like @searchGenre Union all SELECT Title FROM Video WHERE Publisher LIKE @searchArtist AND Genre Like @searchGenre";
                searchCom.CommandType = CommandType.Text;

                searchCom.Parameters.AddWithValue("@searchArtist", $"%{searchArtist}%");
                searchCom.Parameters.AddWithValue("@searchGenre", $"%{searchGenre}%");
                searchCom.Prepare();

            }
            return getList(searchCom);
        }

        public bool Save(string path)
        {
            var conInfo = "URI=file:" + path;

            const string createGroupsTable = "CREATE TABLE IF NOT EXISTS `groups` (" +
                                             "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                                             "`name` VARCHAR(20)," +
                                             "`default` BOOLEAN," +
                                             "`meta` TEXT," +
                                             "`permissions` TEXT" +
                                             ")";

            const string createPlayersTable = "CREATE TABLE IF NOT EXISTS `players` (" +
                                              "`id` INTEGER PRIMARY KEY AUTOINCREMENT," +
                                              "`name` VARCHAR(20)," +
                                              "`group` INTEGER," +
                                              "`meta` TEXT," +
                                              "`permissions` TEXT" +
                                              ")";

            const string addGroup = "INSERT INTO groups(name, default, meta, permissions) VALUES(@Name, @Default, @Meta, @Permissions)";
            const string addPlayer =
                "INSERT INTO players(name, group, meta, permissions) VALUES(@Name, @Group, @Meta, @Permissions)";

            const string getGroupName = "SELECT * FROM groups WHERE [email protected]";

            try
            {
                _con = new SQLiteConnection(conInfo);
                _con.Open();

                Console.WriteLine(Resources.PermissionSet_Create_Group_Table);
                _cmd = new SQLiteCommand(createGroupsTable, _con);
                _cmd.ExecuteNonQuery();
                Console.WriteLine(Resources.PermissionSet_Created_Group_Table);

                if (_cmd != null)
                {
                    _cmd.Dispose();
                }

                Console.WriteLine(Resources.PermissionSet_Create_Players_Table);
                _cmd = new SQLiteCommand(createPlayersTable, _con);
                _cmd.ExecuteNonQuery();
                Console.WriteLine(Resources.PermissionSet_Created_Players_Table);

                if (_cmd != null)
                {
                    _cmd.Dispose();
                }

                Console.WriteLine();

                foreach (var group in _groups)
                {
                    var groupName = group.Name();
                    var groupDefault = group.Default();
                    var groupMeta = group.Meta();
                    var groupPermissions = group.Permissions();

                    _cmd.CommandText = addGroup;
                    _cmd.Prepare();

                    _cmd.Parameters.AddWithValue("@Name", groupName);
                    _cmd.Parameters.AddWithValue("@Default", groupDefault);
                    _cmd.Parameters.AddWithValue("@Meta", Serialization.SerializeMeta(groupMeta));
                    _cmd.Parameters.AddWithValue("@Permissions", Serialization.SerializePermissionsList(groupPermissions));

                    _cmd.ExecuteNonQuery();

                    if (_cmd != null)
                    {
                        _cmd.Dispose();
                    }

                    Console.WriteLine(Resources.PermissionSet_Saved_Group, groupName);
                }

                foreach (var player in _players)
                {
                    var playerName = player.Name();
                    var playerGroup = 0;
                    var playerMeta = Serialization.SerializeMeta(player.Meta());
                    var playerPermissions = Serialization.SerializePermissionsList(player.Permissions());

                    var playerGroupName = player.Group().Name();

                    _cmd.CommandText = getGroupName;
                    _cmd.Prepare();

                    _cmd.Parameters.AddWithValue("@Name", playerGroupName);
                    using (var reader = _cmd.ExecuteReader())
                    {
                        while(reader.Read())
                        {
                            playerGroup = reader.GetInt32(0);
                        }
                    }

                    if (_cmd != null)
//.........这里部分代码省略.........

        private static bool ParseFile(FileInfo f)
        {
            DateTime filestarttime = DateTime.Now;

            Console.Write("Parsing {0}...", f.Name);

            SQLiteConnection connection = new SQLiteConnection("Data Source=" + f.FullName);
            SQLiteCommand command = new SQLiteCommand(connection);
            connection.Open();
            command.CommandText = "SELECT id, sess_id, timestamp, direction, opcode, data FROM packets ORDER BY id;";
            command.Prepare();
            SQLiteDataReader reader = command.ExecuteReader();

            MemoryStream ms = new MemoryStream();

            while (reader.Read())
            {
                uint id = (uint)reader.GetInt32(0);
                uint sess_id = (uint)reader.GetInt32(1);
                string timestamp = reader.GetString(2);
                byte direction = reader.GetByte(3);
                ushort opcode = (ushort)reader.GetInt32(4);
                if (opcode > 1054)
                {
                    Console.WriteLine(opcode);
                    throw new Exception("Test");
                }
                byte[] data_ = (byte[])reader.GetValue(5);

                uint size = sizeof(uint) + sizeof(uint) + (uint)timestamp.Length + 1 + sizeof(byte) + sizeof(ushort) + (uint)data_.Length;

                byte[] id_arr = BitConverter.GetBytes(id);
                byte[] sessid_arr = BitConverter.GetBytes(sess_id);
                byte[] time_arr = Encoding.ASCII.GetBytes(timestamp);
                byte[] op = BitConverter.GetBytes(opcode);
                byte[] sz = BitConverter.GetBytes(size);

                ms.Write(sz, 0, sz.Length);
                ms.Write(id_arr, 0, id_arr.Length);
                ms.Write(sessid_arr, 0, sessid_arr.Length);
                ms.Write(time_arr, 0, time_arr.Length);
                ms.WriteByte(0);
                ms.WriteByte(direction);
                ms.Write(op, 0, op.Length);
                ms.Write(data_, 0, data_.Length);
            }

            reader.Close();
            connection.Close();

            GenericReader gr = new GenericReader(ms, Encoding.ASCII);
            gr.BaseStream.Position = 0;

            string error_log = f.FullName + ".errors.txt";
            StreamWriter swe = new StreamWriter(error_log);

            string database_log = f.FullName + ".data.txt";
            StreamWriter data = new StreamWriter(database_log);

            string hex_log = f.FullName + ".hex.log";
            StreamWriter hex = new StreamWriter(hex_log);

            string ofn = f.FullName + ".data_out.txt";
            StreamWriter sw = new StreamWriter(ofn);

            sw.AutoFlush = true;
            swe.AutoFlush = true;
            data.AutoFlush = true;
            hex.AutoFlush = true;

            while (gr.PeekChar() >= 0)
            {
                //try
                //{
                if (ParseHeader(gr, sw, swe, data, hex))
                    packet++;
                //}
                //catch (Exception exc)
                //{
                //    MessageBox.Show(exc.ToString());
                //    swe.WriteLine("error in pos " + gr.BaseStream.Position.ToString("X16"));
                //}
            }

            // clear objects list...
            m_objects.Clear();

            sw.Close();
            swe.Close();
            data.Close();
            hex.Close();
            gr.Close();

            TimeSpan fileworktime = DateTime.Now - filestarttime;

            Console.WriteLine(" Parsed in {0}", fileworktime);

            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="profileName"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public static DatabaseMessage saveRemarks(string profileName, string remark)
        {
            string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            SQLiteConnection conn = null;

            try
            {
                conn = new SQLiteConnection(ConString);
                conn.Open();

                SQLiteCommand cmdPrf = new SQLiteCommand();
                cmdPrf.Connection = conn;
                cmdPrf.CommandText = "INSERT INTO remarks (profileName, remark)  VALUES (@profileName, @remark)";
                cmdPrf.Prepare();

                cmdPrf.Parameters.AddWithValue("@profileName", profileName);
                cmdPrf.Parameters.AddWithValue("@remark", remark);

                cmdPrf.ExecuteNonQuery();
                if (conn != null)
                {
                    conn.Close();
                }
                return DatabaseMessage.successfulInsertion;
            }
            catch (SQLiteException)
            {
                if (conn != null)
                {
                    conn.Close();
                }
                return DatabaseMessage.databaseError;
            }
            finally {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }