Beispiel #1
0
    void TestDb()
    {
        string dbPath = Application.dataPath + "/../Save/SaveData.db";
        //SqlDbConnect sqlDbConnect = new SqlDbConnect(dbPath);
        SqlDbCommand sql = new SqlDbCommand(dbPath);

        sql.CreateTable();
    }
        public Author FindOrCreateAuthor(AuthorInfoNode info, IDbTransaction transaction)
        {
            string commandText;
            FbCommand command;
            Author author;

            if (info == null)
            {
                throw new ArgumentNullException("info");
            }

            this.manager.BeginConnect();

            try
            {
                commandText =
                        @"SELECT autor.* FROM autor
                          INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid
                          WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND
                                UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND
                                UPPERCASE(autor_synonims.midname) = UPPERCASE(@midname)";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
                command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName;
                command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    author = dbCommand.ExecuteObject<Author>();
                }

                if (author != null)
                {
                    return author;
                }

                commandText =
                    @"SELECT autor.* FROM autor
                      INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid
                      WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND
                            UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND
                            ((autor_synonims.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor_synonims.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
                command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty;
                command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    author = dbCommand.ExecuteObject<Author>();
                }

                if (author != null)
                {
                    return author;
                }

                commandText =
                    @"SELECT * FROM autor
                      WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
                            UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
                            UPPERCASE(autor.midname) = UPPERCASE(@midname) AND
                            UPPERCASE(autor.nickname) = UPPERCASE(@nickname)";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName;
                command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName;
                command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName;
                command.Parameters.Add("@nickname", FbDbType.VarChar).Value = info.NickName;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    author = dbCommand.ExecuteObject<Author>();
                }

                if (author != null)
                {
                    return author;
                }

                commandText =
                    @"SELECT * FROM autor
                      WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
                            UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
                            UPPERCASE(autor.midname) = UPPERCASE(@midname)";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName;
                command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName;
                command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    author = dbCommand.ExecuteObject<Author>();
                }

                if (author != null)
                {
                    return author;
                }

                commandText =
                    @"SELECT autor.* FROM autor
                      WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND
                            UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND
                            ((autor.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName;
                command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty;
                command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    author = dbCommand.ExecuteObject<Author>();
                }

                if (author != null)
                {
                    return author;
                }

                if (String.IsNullOrEmpty(info.FirstName) && String.IsNullOrEmpty(info.LastName) && !String.IsNullOrEmpty(info.NickName))
                {
                    commandText =
                        @"SELECT autor.* FROM autor WHERE UPPERCASE(autor.nickname) = UPPERCASE(@nickname)";

                    command = this.connection.CreateCommand();
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;
                    command.Transaction = transaction as FbTransaction;

                    command.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = info.NickName;

                    using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                    {
                        author = dbCommand.ExecuteObject<Author>();
                    }

                    if (author != null)
                    {
                        return author;
                    }
                }

                author = new Author();

                author.FirstName = info.FirstName;
                author.MiddleName = info.MiddleName;
                author.LastName = info.LastName;
                author.NickName = info.NickName;
                author.LibraryId = info.Id;
                author.EMail = StringUtils.Truncate(StringUtils.Join(", ", info.Email), 125);
                author.Homepage = StringUtils.Truncate(StringUtils.Join(", ", info.Homepage), 125);

                commandText =
                    "INSERT INTO AUTOR (AUTORID, FIRSTNAME, MIDNAME, LASTNAME, NICKNAME, EMAIL, HOMEPAGE) " +
                    "VALUES (GEN_ID(GEN_AUTOR_ID, 1), @firstname, @midname, @lastname, @nickname, @email, @homepage) " +
                    "RETURNING AUTORID";

                using (FbCommand cmd = this.connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = commandText;
                    cmd.Transaction = transaction as FbTransaction;

                    cmd.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = author.FirstName ?? String.Empty;
                    cmd.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = author.MiddleName ?? String.Empty;
                    cmd.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = author.LastName ?? String.Empty;
                    cmd.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = author.NickName ?? String.Empty;
                    cmd.Parameters.Add("@email", FbDbType.VarChar, 125).Value = author.EMail;
                    cmd.Parameters.Add("@homepage", FbDbType.VarChar, 125).Value = author.Homepage;

                    cmd.Parameters.Add("@authorid", FbDbType.Integer).Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();

                    author.Id = Convert.ToInt32(cmd.Parameters["@authorid"].Value);
                }

                if (!String.IsNullOrEmpty(author.LibraryId))
                {
                    commandText =
                        "INSERT INTO AUTHOR_INFO (AUTORID, ID) VALUES (@autorid, @id)";

                    using (FbCommand cmd = this.connection.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = commandText;
                        cmd.Transaction = transaction as FbTransaction;

                        cmd.Parameters.Add("@autorid", FbDbType.Integer).Value = author.Id;
                        cmd.Parameters.Add("@id", FbDbType.VarChar, 254).Value = author.LibraryId;

                        cmd.ExecuteNonQuery();
                    }
                }

                List<Author> authors = AuthorList;
                authors.Add(author);
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }

            return author;
        }
        private List<AuthorSynonym> LoadAuthorSynonyms()
        {
            this.manager.BeginConnect();

            try
            {
                string commandText = @"SELECT autor_synonims.* FROM autor_synonims";

                using (SqlDbCommand command = new SqlDbCommand(connection.CreateCommand()))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    return command.ExecuteList<AuthorSynonym>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        private DatabaseInfo LoadDatabaseInfo()
        {
            this.manager.BeginConnect();

            try
            {
                string commandText = "SELECT * FROM VERINFO";

                using (SqlDbCommand command = new SqlDbCommand(connection.CreateCommand()))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    return command.ExecuteObject<DatabaseInfo>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        public List<GenreInfo> LoadGenreList()
        {
            this.manager.BeginConnect();

            try
            {
                string commandText = "SELECT * FROM GENRE";

                Dictionary<string, GenreInfo> genreTable;
                List<GenreInfo> result;

                using (SqlDbCommand command = new SqlDbCommand(connection.CreateCommand()))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    result = command.ExecuteList<GenreInfo>();

                    foreach (GenreInfo genreInfo in result)
                    {
                        genreInfo.Name = (genreInfo.Name != null) ? genreInfo.Name.Trim() : null;
                    }

                    genreTable = new Dictionary<string, GenreInfo>(result.Count);
                    foreach (GenreInfo genreInfo in result)
                    {
                        try
                        {
                            genreTable.Add(genreInfo.Name, genreInfo);
                        }
                        catch (ArgumentException)
                        {
                        }
                    }
                }

                commandText = "SELECT * FROM GENREALT";

                using (SqlDbCommand command = new SqlDbCommand(connection.CreateCommand()))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    List<GenreMapInfo> genreMap = command.ExecuteList<GenreMapInfo>();
                    foreach (GenreMapInfo genreMapInfo in genreMap)
                    {
                        genreMapInfo.GenreName = (genreMapInfo.GenreName != null) ? genreMapInfo.GenreName.Trim() : null;
                        genreMapInfo.GenreAltName = (genreMapInfo.GenreAltName != null) ? genreMapInfo.GenreAltName.Trim() : null;
                    }

                    foreach (GenreMapInfo genreMapInfo in genreMap)
                    {
                        if (genreTable.ContainsKey(genreMapInfo.GenreName))
                        {
                            genreTable[genreMapInfo.GenreName].GenreMap.Add(genreMapInfo);
                        }
                    }

                    return result;
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        private List<Author> LoadAuthorList()
        {
            this.manager.BeginConnect();

            try
            {
                string commandText =
                    @"SELECT autor.autorid, autor.lastname, autor.firstname, autor.midname, autor.nickname, author_info.id as library_id
                      FROM autor
                      LEFT JOIN author_info ON autor.autorid = author_info.autorid
                      UNION ALL
                      SELECT autor_synonims.autorid, autor_synonims.lastname, autor_synonims.firstname, autor_synonims.midname, autor_synonims.nickname, author_info.id as library_id
                      FROM autor_synonims
                      LEFT JOIN author_info ON autor_synonims.autorid = author_info.autorid";

                using (SqlDbCommand command = new SqlDbCommand(connection.CreateCommand()))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = commandText;

                    return command.ExecuteList<Author>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        public List<BookInfo> LoadBookInfoByDocumentId(string documentId)
        {
            string commandText;
            FbCommand command;

            if (String.IsNullOrEmpty(documentId))
            {
                throw new ArgumentException("documentId");
            }

            this.manager.BeginConnect();

            try
            {
                commandText =
                    @"SELECT BOOKID, BOOKNAME, ""SEQUENCE"", SEQNUMBER, OLDID, DI_VERSION, FILENAME, FILEDATE, DATEIN
                      FROM BOOK
                      WHERE OLDID = @documentid";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;

                command.Parameters.Add("@documentid", FbDbType.VarChar, 254).Value = documentId;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    return dbCommand.ExecuteList<BookInfo>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        public BookInfo LoadBookInfoByBookId(int bookId)
        {
            string commandText;
            FbCommand command;

            this.manager.BeginConnect();

            try
            {
                commandText =
                    @"SELECT BOOKID, AUTORLIST, GENRELIST, BOOKNAME, ""SEQUENCE"", SEQNUMBER, OLDID, DI_VERSION, FILENAME, FILEDATE, FILESIZE, DATEIN
                      FROM BOOK
                      WHERE BOOKID = @bookid";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;

                command.Parameters.Add("@bookid", FbDbType.Integer).Value = bookId;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    return dbCommand.ExecuteObject<BookInfo>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        public List<BookInfo> LoadBookInfoByAuthorIdList(IEnumerable<int> list)
        {
            string commandText;
            FbCommand command;

            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            this.manager.BeginConnect();

            try
            {
                string idsList = StringUtils.Join(", ", list);
                if (String.IsNullOrEmpty(idsList))
                {
                    return new List<BookInfo>();
                }

                commandText =
                    @"SELECT BOOK.BOOKID, BOOKNAME, ""SEQUENCE"", SEQNUMBER, OLDID, DI_VERSION, FILENAME, FILEDATE, DATEIN
                      FROM BOOK
                      INNER JOIN BOOK_AUTOR ON BOOK_AUTOR.BOOKID = BOOK.BOOKID
                      WHERE BOOK_AUTOR.AUTORID IN (" + idsList + ")";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    return dbCommand.ExecuteList<BookInfo>();
                }
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }
        }
        public SequenceInfo FindOrCreateBookSequence(SequenceInfoNode info, IDbTransaction transaction)
        {
            string commandText;
            FbCommand command;
            SequenceInfo sequence;

            if (info == null)
            {
                throw new ArgumentNullException("info");
            }

            this.manager.BeginConnect();

            try
            {
                commandText =
                    @"SELECT sequences.* FROM sequences
                      WHERE UPPERCASE(sequences.""SEQUENCE"") = UPPERCASE(@sequencename)";

                command = this.connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.Transaction = transaction as FbTransaction;

                command.Parameters.Add("@sequencename", FbDbType.VarChar).Value = info.Name;

                using (SqlDbCommand dbCommand = new SqlDbCommand(command))
                {
                    sequence = dbCommand.ExecuteObject<SequenceInfo>();
                }

                if (sequence != null)
                {
                    sequence.SequenceNumber = info.Number;
                    return sequence;
                }

                sequence = new SequenceInfo();
                sequence.SequenceName = StringUtils.Truncate(info.Name, 125);

                commandText =
                    "INSERT INTO SEQUENCES (SEQUENCEID, \"SEQUENCE\") " +
                    "VALUES (GEN_ID(GEN_SEQUENCES_ID, 1), @sequencename) " +
                    "RETURNING SEQUENCEID";

                using (FbCommand cmd = this.connection.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = commandText;
                    cmd.Transaction = transaction as FbTransaction;

                    cmd.Parameters.Add("@sequencename", FbDbType.VarChar, 125).Value = sequence.SequenceName ?? String.Empty;

                    cmd.Parameters.Add("@sequenceid", FbDbType.Integer).Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();

                    sequence.SequenceId = Convert.ToInt32(cmd.Parameters["@sequenceid"].Value);
                }

                sequence.SequenceNumber = info.Number;
            }
            catch (FbException exp)
            {
                throw new DatabaseException(exp.Message, exp);
            }
            finally
            {
                this.manager.EndConnect();
            }

            return sequence;
        }
        protected virtual void OnFirstActivated()
        {
            try
            {
                Cursor = Cursors.WaitCursor;
                Application.DoEvents();

                database.BeginConnect();

                try
                {
                    string commandText = "SELECT * FROM GENRE";

                    using (SqlDbCommand command = new SqlDbCommand(database.Connection.CreateCommand()))
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = commandText;

                        List<GenreInfo> result = command.ExecuteList<GenreInfo>();

                        foreach (GenreInfo genreInfo in result)
                        {
                            genreInfo.Name = (genreInfo.Name != null) ? genreInfo.Name.Trim() : null;
                        }

                        genreEditor.DataSource = result;
                    }
                }
                finally
                {
                    database.EndConnect();
                }
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }