C# DAL.OpenConnection方法代码示例

        /// <summary>
        /// Delete a person
        /// </summary>
        /// <param name="id"></param>
        public void DeleteMovie(string id)
        {
            using(DAL dal = new DAL(this._cfg)) {
                dal.OpenConnection();

                DbCommand cmd = dal.CreateCommand();

                // delete movie
                cmd.CommandText = "DELETE";
                cmd.CommandText += " FROM tbl_movies";

                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += " WHERE pkid = '" + id + "'";
                }
                else {
                    cmd.CommandText += " WHERE lower(pkid) = lower('{" + id + "}'";
                }

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }

                // delete movie-category-link
                cmd.CommandText = "DELETE ";
                cmd.CommandText += " FROM tbl_movies_to_categories";

                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += " WHERE movie_pkid = '" + id + "'";
                }
                else {
                    cmd.CommandText += " WHERE lower(movie_pkid) = lower('{" + id + "}'";
                }

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }

                // delete movie-genre-link
                cmd.CommandText = "DELETE ";
                cmd.CommandText += " FROM tbl_movies_to_genres";

                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += " WHERE movie_pkid = '" + id + "'";
                }
                else {
                    cmd.CommandText += " WHERE lower(movie_pkid) = lower('{" + id + "}'";
                }

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }

                // delete movie-person-link
                cmd.CommandText = "DELETE ";
                cmd.CommandText += " FROM tbl_movies_to_persons";

                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += " WHERE movie_pkid = '" + id + "'";
                }
                else {
                    cmd.CommandText += " WHERE lower(movie_pkid) = lower('{" + id + "}'";
                }

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// Searches the static item.
        /// </summary>
        /// <param name="tag">The tag.</param>
        /// <param name="content">The content.</param>
        /// <returns></returns>
        public List<Static> SearchStaticItem(string tag, string content)
        {
            List<Static> list = new List<Static>();

            using(DAL dal = new DAL(this._cfg)) {
                dal.OpenConnection();

                DbCommand cmd = dal.CreateCommand();

                cmd.CommandText = "SELECT * ";
                cmd.CommandText += " FROM tbl_static";
                cmd.CommandText += " WHERE tag = @tag";
                cmd.CommandText += " AND content LIKE '%' + @content + '%'";

                cmd.AddParameter("tag", tag);
                cmd.AddParameter("content", content);

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    while(reader.Read()) {
                        list.Add(
                            this._FillStaticFromTableReader(reader)
                        );
                    }
                }
            }

            return list;
        }

        // -------------------------------------------------------
        // PUBLIC MEMBERS
        // -------------------------------------------------------
        /// <summary>
        /// Checks the database version.
        /// </summary>
        public void CheckDatabaseVersion()
        {
            if(this._cfg.ProviderType != ProviderType.SQLite) {
                using(DAL dal = new DAL(this._cfg)) {
                    dal.OpenConnection();

                    /*
                     * for version 1.1.4
                     */
                    bool hasSortValue = false;

                    DbCommand cmd114 = dal.CreateCommand();

                    if(this._cfg.ProviderType == ProviderType.SQLite) {
                        cmd114.CommandText = "SELECT* FROM tbl_movies LIMIT 1";
                    }
                    else {
                        cmd114.CommandText = "SELECT TOP 1 * FROM tbl_movies";
                    }

                    try {
                        using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd114)) {
                            for(int i = 0; i < reader.FieldCount; i++) {
                                if(reader.GetName(i) == "sort_value") {
                                    hasSortValue = true;
                                    break;
                                }
                            }
                        }
                    }
                    catch(Exception ex) {
                        throw ex;
                    }

                    try {
                        if(!hasSortValue) {
                            cmd114.CommandText = "ALTER TABLE tbl_movies ";
                            cmd114.CommandText += "ADD	sort_value varchar(10) NULL;";
                            cmd114.CommandText += "ALTER TABLE tbl_movies ";
                            cmd114.CommandText += "ADD CONSTRAINT DF_tbl_movies_sort_value DEFAULT '' FOR sort_value;";

                            dal.ExecuteNonQuery(cmd114);
                        }
                    }
                    catch(Exception ex) {
                        throw ex;
                    }

                    /*
                     * for version 1.1.7
                     */
                    bool hasRoleName = false;
                    bool hasRoleType = false;

                    DbCommand cmd117 = dal.CreateCommand();
                    cmd117.CommandText = "SELECT TOP 1 * ";
                    cmd117.CommandText += "FROM tbl_movies_to_persons";

                    try {
                        using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd117)) {
                            for(int i = 0; i < reader.FieldCount; i++) {
                                if(reader.GetName(i) == "role_name") {
                                    hasRoleName = true;
                                }

                                if(reader.GetName(i) == "role_type") {
                                    hasRoleType = true;
                                }

                                if(hasRoleName && hasRoleType) {
                                    break;
                                }
                            }
                        }
                    }
                    catch(Exception ex) {
                        throw ex;
                    }

                    try {
                        cmd117.CommandText = "";

                        if(!hasRoleName) {
                            cmd117.CommandText += "ALTER TABLE tbl_movies_to_persons ";
                            cmd117.CommandText += "ADD role_name nvarchar(max) NULL;";
                        }

                        if(!hasRoleType) {
                            cmd117.CommandText += "ALTER TABLE tbl_movies_to_persons ";
                            cmd117.CommandText += "ADD role_type int NULL;";
                        }

                        if(!string.IsNullOrEmpty(cmd117.CommandText)) {
                            dal.ExecuteNonQuery(cmd117);
//.........这里部分代码省略.........

        /// <summary>
        /// Cleans the database.
        /// </summary>
        /// <returns></returns>
        public bool CleanDatabase()
        {
            using(DAL dal = new DAL(this._cfg)) {
                dal.OpenConnection();

                DbCommand cmd = dal.CreateCommand();

                // clean empty genre-link entries
                cmd.CommandText = "DELETE ";
                cmd.CommandText += "FROM tbl_movies_to_genres ";
                cmd.CommandText += "WHERE pkid IN ( ";
                cmd.CommandText += "	SELECT mg.pkid ";
                cmd.CommandText += "	FROM tbl_movies_to_genres AS mg ";
                cmd.CommandText += "	LEFT JOIN tbl_movies AS m ";
                cmd.CommandText += "	ON mg.movie_pkid = m.pkid ";
                cmd.CommandText += "	WHERE m.pkid IS NULL";
                cmd.CommandText += ") ";

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }

                // clean empty category-link entries
                cmd.CommandText = "DELETE ";
                cmd.CommandText += "FROM tbl_movies_to_categories ";
                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += "WHERE (CAST(movie_pkid AS VARCHAR(MAX)) + CAST(category_pkid AS VARCHAR(MAX))) IN ( ";
                    cmd.CommandText += "	SELECT (CAST(mc.movie_pkid AS VARCHAR(MAX)) + CAST(mc.category_pkid AS VARCHAR(MAX))) ";
                }
                else {
                    cmd.CommandText += "WHERE (CAST(movie_pkid AS VARCHAR) + CAST(category_pkid AS VARCHAR)) IN ( ";
                    cmd.CommandText += "	SELECT (CAST(mc.movie_pkid AS VARCHAR) + CAST(mc.category_pkid AS VARCHAR)) ";
                }
                cmd.CommandText += "	FROM tbl_movies_to_categories AS mc ";
                cmd.CommandText += "	LEFT JOIN tbl_movies AS m ";
                cmd.CommandText += "	ON mc.movie_pkid = m.pkid ";
                cmd.CommandText += "	WHERE m.pkid IS NULL ";
                cmd.CommandText += ") ";

                try {
                    dal.ExecuteNonQuery(cmd);
                }
                catch(Exception ex) {
                    throw ex;
                }
            }

            return true;
        }

        /// <summary>
        /// Get a list of movies by a sql query string
        /// </summary>
        /// <param name="query">The Sql query string.</param>
        /// <param name="filter"></param>
        /// <param name="withAdditionals"></param>
        /// <returns></returns>
        private List<Movie> GetMovieListBySqlQuery(string query, FilterType filter, bool withAdditionals)
        {
            List<Movie> list = new List<Movie>();
            StringBuilder str = new StringBuilder();

            // new way
            using(DAL dal = new DAL(this._cfg)) {
                IDbCommand cmd = dal.CreateCommand();
                cmd.CommandText = query;

                dal.OpenConnection();

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    while(reader.Read()) {
                        Movie mov = new Movie();

                        mov.Id = reader.GetSafeValue<Guid>("pkid").ToString();
                        mov.Number = reader.GetSafeValue<int>("number");
                        mov.Name = reader.GetSafeValue<string>("name");
                        mov.Note = reader.GetSafeValue<string>("note");
                        mov.HasCover = reader.GetSafeValue<bool>("has_cover");
                        mov.IsOriginal = reader.GetSafeValue<bool>("is_original");
                        mov.IsConferred = reader.GetSafeValue<bool>("is_conferred");
                        mov.ConferredTo = reader.GetSafeValue<string>("conferred_to");
                        mov.Codec = (Codec)reader.GetSafeValue<int>("codec");
                        mov.Actors = new List<Person>();
                        mov.Directors = new List<Person>();
                        mov.Producers = new List<Person>();
                        mov.Musicians = new List<Person>();
                        mov.Cameramans = new List<Person>();
                        mov.Cutters = new List<Person>();
                        mov.Writers = new List<Person>();
                        mov.Genres = new List<Genre>();
                        mov.DiscAmount = reader.GetSafeValue<int>("disc_amount");
                        mov.Year = reader.GetSafeValue<int>("year");
                        mov.Country = reader.GetSafeValue<string>("country");
                        mov.Quality = (Quality)reader.GetSafeValue<int>("quality");
                        mov.SortValue = ( reader["sort_value"] == DBNull.Value ? "" : reader.GetSafeValue<string>("sort_value") );
                        mov.Language = ( reader["language"] == DBNull.Value ? "" : reader.GetSafeValue<string>("language") );
                        mov.CountryString = reader.GetSafeValue<string>("content");

                        list.Add(mov);
                    }
                }

                foreach(Movie mov in list) {
                    // genre
                    List<Genre> glist = new List<Genre>();

                    str.Remove(0, str.Length);
                    str.Append("SELECT g.*");
                    str.Append(" FROM tbl_genres AS g");
                    str.Append(" INNER JOIN tbl_movies_to_genres AS mg");
                    str.Append(" ON mg.genre_pkid = g.pkid");

                    if(this._cfg.ProviderType != ProviderType.SQLite) {
                        str.Append(" WHERE mg.movie_pkid = '" + mov.Id + "'");
                    }
                    else {
                        str.Append(" WHERE lower(mg.movie_pkid) = lower('{" + mov.Id + "}')");
                    }

                    IDbCommand cmdGenre = dal.CreateCommand();
                    cmdGenre.CommandText = str.ToString();

                    using(IDataReader genreReader = dal.ExecuteQueryForDataReader(cmdGenre)) {
                        while(genreReader.Read()) {
                            glist.Add(
                                new Genre(
                                    genreReader.GetSafeValue<Guid>("pkid").ToString(),
                                    genreReader.GetSafeValue<string>("name")
                                )
                            );
                        }
                    }

                    mov.Genres.AddRange(glist);

                    // categories
                    List<Category> clist = new List<Category>();

                    str.Remove(0, str.Length);
                    str.Append("SELECT c.*");
                    str.Append(" FROM tbl_categories AS c");
                    str.Append(" INNER JOIN tbl_movies_to_categories AS mc");
                    str.Append(" ON mc.category_pkid = c.pkid");

                    if(this._cfg.ProviderType != ProviderType.SQLite) {
                        str.Append(" WHERE mc.movie_pkid = '" + mov.Id + "'");
                    }
                    else {
                        str.Append(" WHERE lower(mc.movie_pkid) = lower('{" + mov.Id + "}')");
                    }
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                        str.AppendFormat(
                            ", p.firstname {0}, p.lastname {0}",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );
                        break;

                    case "MovieQuantityAsCutter":
                        str.Append("\r\n");
                        str.AppendFormat(
                            SqlResources.GetPersonList_SortOrder,
                            "cutterQuantity",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );

                        str.Append("\r\n");
                        str.AppendFormat(
                            ", p.firstname {0}, p.lastname {0}",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );
                        break;

                    case "MovieQuantityAsWriter":
                        str.Append("\r\n");
                        str.AppendFormat(
                            SqlResources.GetPersonList_SortOrder,
                            "writerQuantity",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );

                        str.Append("\r\n");
                        str.AppendFormat(
                            ", p.firstname {0}, p.lastname {0}",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );
                        break;

                    case "Fullname":
                    default:
                        str.Append("\r\n");
                        str.AppendFormat(
                            SqlResources.GetPersonList_SortOrder,
                            "p.firstname",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );

                        str.AppendFormat(
                            ", p.lastname {0}",
                            ( sortDirection == DataSortDirection.Ascending ? "ASC" : "DESC" )
                        );
                        break;
                }
            }
            else {
                str.Append(" ORDER BY p.firstname ASC, p.lastname ASC");
            }

            if(this._cfg.ProviderType == ProviderType.SQLite) {
                str = str.Replace(" = 1", " = 'True'");
            }

            // TODO: Optimize for SQLite

            // new way
            using(DAL dal = new DAL(this._cfg)) {
                IDbCommand cmd = dal.CreateCommand();
                cmd.CommandText = str.ToString();

                dal.OpenConnection();

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    while(reader.Read()) {
                        list.Add(
                            new Person(
                                reader.GetSafeValue<Guid>("pkid").ToString(),
                                reader.GetSafeValue<string>("firstname"),
                                reader.GetSafeValue<string>("lastname"),
                                reader.GetSafeValue<bool>("is_actor"),
                                reader.GetSafeValue<bool>("is_director"),
                                reader.GetSafeValue<bool>("is_producer"),
                                ( reader["is_cameraman"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_cameraman") ),
                                ( reader["is_cutter"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_cutter") ),
                                ( reader["is_musician"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_musician") ),
                                ( reader["is_writer"] == DBNull.Value ? false : reader.GetSafeValue<bool>("is_writer") )
                            ) {
                                MovieQuantityAsActor = reader["actorQuantity"].ToString().ToInt32(),
                                MovieQuantityAsDirector = reader["directorQuantity"].ToString().ToInt32(),
                                MovieQuantityAsProducer = reader["producerQuantity"].ToString().ToInt32(),
                                MovieQuantityAsCameraman = reader["cameramanQuantity"].ToString().ToInt32(),
                                MovieQuantityAsCutter = reader["cutterQuantity"].ToString().ToInt32(),
                                MovieQuantityAsMusician = reader["musicianQuantity"].ToString().ToInt32(),
                                MovieQuantityAsWriter = reader["writerQuantity"].ToString().ToInt32(),
                                MovieQuantity = reader["movieQuantity"].ToString().ToInt32()
                            }
                        );
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// Get a movie
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Movie GetMovie(string id)
        {
            string tmp = "";
            Movie ret = null;
            List<Genre> glist = new List<Genre>();
            List<Person> alist = new List<Person>();
            List<Person> dlist = new List<Person>();
            List<Person> plist = new List<Person>();
            List<Person> mlist = new List<Person>();
            List<Person> calist = new List<Person>();
            List<Person> culist = new List<Person>();
            List<Person> wlist = new List<Person>();
            List<Category> clist = new List<Category>();
            StringBuilder str = new StringBuilder();

            str.Append("SELECT * ");
            str.Append(" FROM tbl_movies");

            if(this._cfg.ProviderType != ProviderType.SQLite) {
                str.Append(" WHERE pkid = '" + id + "'");
            }
            else {
                str.Append(" WHERE lower(pkid) = lower('{" + id + "}')");
            }

            //DataTableReader dtr = null;

            //this._db.OpenConnection();

            //SqlDataReader reader = (SqlDataReader)this._db.ExecuteQueryForDataReader(
            //    str.ToString(),
            //    CommandType.Text
            //);

            using(DAL dal = new DAL(this._cfg)) {
                IDbCommand cmd = dal.CreateCommand();
                cmd.CommandText = str.ToString();

                dal.OpenConnection();

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    if(reader.Read()) {
                        tmp = reader["pkid"].ToString();

                        // movie
                        ret = new Movie();

                        ret.Id = reader.GetSafeValue<Guid>("pkid").ToString();
                        ret.Number = reader.GetSafeValue<int>("number");
                        ret.Name = reader.GetSafeValue<string>("name");
                        ret.Note = reader.GetSafeValue<string>("note");
                        ret.HasCover = reader.GetSafeValue<bool>("has_cover");
                        ret.IsOriginal = reader.GetSafeValue<bool>("is_original");
                        ret.IsConferred = reader.GetSafeValue<bool>("is_conferred");
                        ret.ConferredTo = reader.GetSafeValue<string>("conferred_to");
                        ret.Codec = (Codec)reader.GetSafeValue<int>("codec");
                        ret.Actors = new List<Person>();
                        ret.Directors = new List<Person>();
                        ret.Producers = new List<Person>();
                        ret.Musicians = new List<Person>();
                        ret.Cameramans = new List<Person>();
                        ret.Cutters = new List<Person>();
                        ret.Writers = new List<Person>();
                        ret.Genres = new List<Genre>();
                        ret.DiscAmount = reader.GetSafeValue<int>("disc_amount");
                        ret.Year = reader.GetSafeValue<int>("year");
                        ret.Country = reader.GetSafeValue<string>("country");
                        ret.Quality = (Quality)reader.GetSafeValue<int>("quality");
                        ret.SortValue = ( reader["sort_value"] == DBNull.Value ? "" : reader.GetSafeValue<string>("sort_value") );
                        ret.Language = ( reader["language"] == DBNull.Value ? "" : reader.GetSafeValue<string>("language") );

                        //ret = new Movie() {
                        //    Id = tmp,
                        //    Number = reader["number"),
                        //    Name = reader.GetSafeValue<string>("name"),
                        //    Note = reader.GetSafeValue<string>("note"),
                        //    HasCover = reader.GetSafeValue<bool>("has_cover"),
                        //    IsOriginal = reader.GetSafeValue<bool>("is_original"),
                        //    IsConferred = reader.GetSafeValue<bool>("is_conferred"),
                        //    ConferredTo = reader.GetSafeValue<string>("conferred_to"),
                        //    Codec = (Codec)reader.GetSafeValue<int>("codec"),
                        //    Actors = alist,
                        //    Directors = dlist,
                        //    Producers = plist,
                        //    Musicians = mlist,
                        //    Cameramans = calist,
                        //    Cutters = culist,
                        //    Writers = wlist,
                        //    Genres = glist,
                        //    Categories = clist,
                        //    DiscAmount = reader.GetSafeValue<int>("disc_amount"),
                        //    Year = reader.GetSafeValue<int>("year"),
                        //    Country = reader.GetSafeValue<string>("country"),
                        //    Quality = (Quality)reader.GetSafeValue<int>("quality"),
                        //    SortValue = ( reader["sort_value"] == DBNull.Value ? "" : reader.GetSafeValue<string>("sort_value") ),
//.........这里部分代码省略.........

        /// <summary>
        /// Get a list of genres
        /// </summary>
        /// <returns></returns>
        public List<Genre> GetGenreList()
        {
            List<Genre> list = new List<Genre>();

            using(DAL dal = new DAL(this._cfg)) {
                dal.OpenConnection();

                DbCommand cmd = dal.CreateCommand();

                cmd.CommandText = "SELECT g.*, ";
                cmd.CommandText += " (";
                cmd.CommandText += "	SELECT COUNT(pkid)";
                cmd.CommandText += "	FROM tbl_movies_to_genres";
                cmd.CommandText += "	WHERE genre_pkid = g.pkid";
                cmd.CommandText += " ) AS count";
                cmd.CommandText += " FROM tbl_genres AS g";
                cmd.CommandText += " ORDER BY g.name ASC";

                //cmd.AddParameter("invoiceId", order.Id);

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    while(reader.Read()) {
                        Genre genre = new Genre();

                        genre.ID = reader.GetSafeValue<Guid>("pkid").ToString();
                        genre.Name = reader.GetSafeValue<string>("name");
                        genre.MovieCount = reader["count"].ToString().ToInt32();

                        list.Add(genre);
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// Gets the category list.
        /// </summary>
        /// <returns></returns>
        public List<Category> GetCategoryList()
        {
            List<Category> list = new List<Category>();

            using(DAL dal = new DAL(this._cfg)) {
                dal.OpenConnection();

                DbCommand cmd = dal.CreateCommand();

                cmd.CommandText = "SELECT c.*, ";
                cmd.CommandText += " (";
                if(this._cfg.ProviderType != ProviderType.SQLite) {
                    cmd.CommandText += "	SELECT CAST(COUNT(CAST(movie_pkid AS VARCHAR(MAX)) + CAST(category_pkid AS VARCHAR(MAX))) AS int)";
                }
                else {
                    cmd.CommandText += "	SELECT CAST(COUNT(CAST(movie_pkid AS VARCHAR) + CAST(category_pkid AS VARCHAR)) AS int)";
                }
                cmd.CommandText += "	FROM tbl_movies_to_categories";
                cmd.CommandText += "	WHERE category_pkid = c.pkid";
                cmd.CommandText += " ) AS count";
                cmd.CommandText += " FROM tbl_categories AS c";
                cmd.CommandText += " ORDER BY c.name ASC";

                //cmd.AddParameter("invoiceId", order.Id);

                using(IDataReader reader = dal.ExecuteQueryForDataReader(cmd)) {
                    while(reader.Read()) {
                        list.Add(
                            new Category(
                                reader.GetSafeValue<Guid>("pkid").ToString(),
                                reader.GetSafeValue<string>("name"),
                                reader["count"].ToString().ToInt32()
                            )
                        );
                    }
                }
            }

            return list;
        }