/// <summary> /// 保存图书标签信息 /// </summary> /// <param name="bookTopical"></param> /// <returns></returns> public static bool InsertBookTopical(BookTopical bookTopical) { MySqlParameter[] param = new MySqlParameter[] { new MySqlParameter("@isbn", bookTopical.isbn), new MySqlParameter("@topical_code", bookTopical.topical_code), new MySqlParameter("@create_time", bookTopical.create_time) }; string sql = "insert into book_topical(isbn,topical_code,create_time)values(@isbn,@topical_code,@create_time)"; int res = MySqlInstance.ExecuteNonQuery(CommandType.Text, sql, param); return(res > 0); }
/// <summary> /// 批量保存图书信息 /// </summary> /// <param name="bookInfos"></param> /// <param name="enableUpdate"></param> public static void BatchInsertBookInfo(List <BookInfoExt> bookInfos, out List <BookInfoExt> failBookList, out List <BookInfoExt> existsBookList, out List <BookInfoExt> successBookListbool, bool enableUpdate = false) { failBookList = new List <BookInfoExt>(); existsBookList = new List <BookInfoExt>(); successBookListbool = new List <BookInfoExt>(); using (MySqlConnection con = new MySqlConnection(Utility.MysqlDES3DecryptConnctionString)) { con.Open(); using (MySqlTransaction trans = con.BeginTransaction()) { List <BooktopicalMappings> listBooktopicalMappings = GetBookTopicalMappingList(); Func <BookInfoExt, bool> funcInsertBookTopic = bookinfo => { //保存图书主题标签信息 if (!string.IsNullOrEmpty(bookinfo.topical_name)) { string[] topicalNames = bookinfo.topical_name.Replace(",", ",").Replace(" ", "").Trim().Split(','); if (topicalNames.Count() > 0) { topicalNames.ToList().ForEach(topicalName => { var bookTopical = GetBookTopical(bookinfo.isbn_no, topicalName); if (bookTopical == null) { var topical_code = listBooktopicalMappings.Where(item => item.topical_name == topicalName.Trim()).Select(item => item.topical_code).DefaultIfEmpty(null); if (topical_code != null) { var toppic = new BookTopical { create_time = DateTime.Now, isbn = bookinfo.isbn_no, topical_code = topical_code.FirstOrDefault() }; InsertBookTopical(toppic); } } }); } } return(false); }; for (int i = 0; i < bookInfos.Count; i++) { var bookinfo = bookInfos[i]; var res = MySqlInstance.ExecuteList <BookInfo>(CommandType.Text, $"select * from book_info where isbn_no='{bookinfo.isbn_no}'", null); if (res != null && res.Count == 1) { //是否更新图书信息 if (enableUpdate) { try { string sql = $"update book_info set book_name='{bookinfo.book_name}',brief='{bookinfo.brief}',`describe`='{bookinfo.describe}' where isbn_no='{bookinfo.isbn_no.Trim()}';"; sql += $"update Book_readable set min_age='{bookinfo.min_age}',max_age='{bookinfo.max_age}',create_time='{DateTime.Now}' where isbn='{bookinfo.isbn_no}'"; int updateResult = MySqlInstance.ExecuteNonQuery(CommandType.Text, sql, null); if (updateResult > 0) { //更新图书主题时,先删除再插入记录 sql = $" delete from book_topical where isbn='{bookinfo.isbn_no}'"; if (MySqlInstance.ExecuteNonQuery(CommandType.Text, sql, null) > 0) { funcInsertBookTopic(bookinfo); } } existsBookList.Add(bookinfo); } catch (Exception ee) { failBookList.Add(bookinfo); } } } else { try { string sql = "insert into book_info(isbn_no, book_name, author, press, publication_date, category, price, readable, imgurl, brief, `describe`, create_time, modify_time)values(@isbn_no, @book_name, @author, @press, @publication_date, @category, @price, @readable, @imgurl, @brief, @describe, @create_time, @modify_time)"; MySqlParameter[] param = new MySqlParameter[] { new MySqlParameter("@isbn_no", bookinfo.isbn_no), new MySqlParameter("@book_name", bookinfo.book_name), new MySqlParameter("@author", bookinfo.author), new MySqlParameter("@press", bookinfo.press), new MySqlParameter("@publication_date", bookinfo.publication_date), new MySqlParameter("@category", bookinfo.category), new MySqlParameter("@price", bookinfo.price), new MySqlParameter("@readable", bookinfo.readable), new MySqlParameter("@imgurl", bookinfo.imgurl), new MySqlParameter("@brief", bookinfo.brief), new MySqlParameter("@describe", bookinfo.describe), new MySqlParameter("@create_time", bookinfo.create_time), new MySqlParameter("@modify_time", bookinfo.modify_time) }; int result = MySqlInstance.ExecuteNonQuery(trans, CommandType.Text, sql, param); if (result > 0) { //保存适读年龄 if (GetBookReadable(bookinfo.isbn_no) == null) { var bookReadable = new BookReadable() { create_time = DateTime.Now, isbn = bookinfo.isbn_no, min_age = bookinfo.min_age != null ? bookinfo.min_age : null, max_age = bookinfo.max_age != null ? bookinfo.max_age : null, }; InsertBookReadable(bookReadable); } //写入图书主题标签信息 funcInsertBookTopic(bookinfo); } successBookListbool.Add(bookinfo); } catch { failBookList.Add(bookinfo); trans.Rollback(); } } } trans.Commit(); } } }