public static int InsertNoTrans(this IEnumerable <TableBase2> rows, string tableName) { if (rows == null) { return(-1); } var arrRows = rows.ToArray(); if (arrRows.Length == 0) { return(0); } var dbType = arrRows.First().DbType; using (var conn = DbBase2.GetSqlConnection(dbType)) { var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn); var sda = Sql2.CreateDbDataAdapter(cmd); var dtSource = new DataTable(); sda.Fill(dtSource); var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource); using (var bulkCopy = new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName = tableName; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); return(arrRows.Length); } } }
public UserProfile GetProfile(int?userId) { using (var db = new DbTorronto()) { return(db.User .Where(u => u.ID == userId) .Select(u => new UserProfile { ID = u.ID, DisplayName = u.DisplayName, Email = u.Email, FilterAudio = u.FilterAudio, FilterTraslation = u.FilterTraslation, FilterVideo = u.FilterVideo, FilterSizes = u.FilterSizes, Identities = db.UserIdentity .Where(i => i.UserID == u.ID) .Select(i => new UserProfile.Identity { Email = i.Email, AuthProviderName = i.AuthProviderName, AuthProviderID = i.AuthProviderID, DisplayName = i.DisplayName }), RssHash = Sql2.MD5(Sql.ConvertTo <string> .From(u.Identifier)) }) .FirstOrDefault()); } }
/// <summary> /// 批量更新记录(无事务) /// </summary> public static int UpdateNoTrans <T, TK>(this IEnumerable <Table2 <T, TK> > tables, Func <T, string> condition) { if (tables == null) { return(-1); } var updateTabs = tables.Where(a => a.__EditColumns.Count > 0).ToArray(); if (updateTabs.Length == 0) { return(0); } var sqls = new List <KeyValuePair <string, List <DbParameter> > >(); var sb = new StringBuilder(); var paraIndex = 0; var parameters = new List <DbParameter>(); foreach (var table in updateTabs) { if (table.__EditColumns.Count > 0) { sb.Append("\nupdate ").Append(table.TableName).Append(" set "); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("]=@p").Append(parameters.Count.ToString()).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + parameters.Count, column.Value)); paraIndex += 2; } sb.Remove(sb.Length - 1, 1); sb.Append(" where ").Append(condition((T)(object)table)); if (paraIndex > 1500) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); sb = new StringBuilder(); paraIndex = 0; parameters = new List <DbParameter>(); } } } if (sb.Length > 0) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); } using (var conn = DbBase2 <TK> .GetConnection(false)) { foreach (var sql in sqls) { var cmd = Sql2.CreateDbCommand(sql.Key, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sql.Value.ToArray()); cmd.ExecuteNonQuery(); } } return(updateTabs.Length); }
public TorrentItem GetSingleTorrent(int torrentID, int?userId) { using (var db = new DbTorronto()) { var item = db.Torrent .LoadWith(t => t.Movie) .FirstOrDefault(torrent => torrent.ID == torrentID && Sql2.IsNullOrFalse(torrent.Movie.IsCopyrighted)); var subscription = db.TorrentUser .FirstOrDefault(tu => tu.TorrentID == torrentID && tu.UserID == userId); return(new TorrentItem { Self = item, IsSubscribed = subscription?.IsSubscribed == true, IsRss = subscription?.AddedRss != null }); } }
/// <summary> /// 删除后重新添加 /// </summary> public static int DeleteInsertNoTrans <T, TK>(this IEnumerable <Table2 <T, TK> > rows, string deleteCondition) { if (rows == null) { return(-1); } var arrRows = rows.ToArray(); if (arrRows.Length == 0) { return(0); } var tableName = arrRows[0].TableName; using (var conn = DbBase2 <TK> .GetSqlConnection(false)) { var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn); var sda = Sql2.CreateDbDataAdapter(cmd); var dtSource = new DataTable(); sda.Fill(dtSource); var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource); if (deleteCondition.HasValue()) { var sql = "DELETE FROM {0} WHERE {1}".Formats(tableName, deleteCondition); cmd = Sql2.CreateDbCommand(sql, conn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } using (var bulkCopy = new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName = tableName; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); return(arrRows.Length); } } }
/// <summary> /// 更新记录(注意条件,否则批量更新导致无法挽回的错误) /// </summary> public static int UpdateSetNoTrans <T, TK>(this Table2 <T, TK> table, string condition, SqlConnection conn, string set = "") { if (table == null) { return(-1); } if (table.__EditColumns.Count == 0) { throw new Exception("更新字段数至少1个以上"); } var sql = new StringBuilder(); var parameters = new List <DbParameter>(); sql.Append("update ").Append(table.TableName).Append(" set "); foreach (var column in table.__EditColumns) { sql.Append("[").Append(column.Key).Append("]=@p").Append(parameters.Count.ToString()).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + parameters.Count, column.Value)); } if (set.HasValue()) { sql.Append(set); } else { sql.Remove(sql.Length - 1, 1); } sql.Append(" where ").Append(condition); var cmd = Sql2.CreateDbCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(parameters.ToArray()); var result = cmd.ExecuteNonQuery(); return(result); }
public MoviePagination GetMovies(int?userId, MovieSearchParams search, PaginationParams pageParams) { using (var db = new DbTorronto()) { var filter = db.Movie .NoCopyrighted() .SelectMany(m => db.MovieUser.Where(x => x.MovieID == m.ID && x.UserID == userId).DefaultIfEmpty(), (movie, movieUser) => new { M = movie, MuWaitList = movieUser.IsWaitlist, MuUserWatched = movieUser.IsWatched, MuDontWant = movieUser.IsDontWant, WlDate = movieUser.Created, MuMark = movieUser.Mark, }); if (!string.IsNullOrEmpty(search.Search)) { if (_isSphinxEnabled) { var movieIds = _searchService.SearchMovieIds(search); filter = filter.Where(x => movieIds.Contains(x.M.ID)); } else { filter = filter.Where(x => x.M.Title.Contains(search.Search) || x.M.OriginalTitle.Contains(search.Search)); } } if (search.WaitList) { filter = filter.Where(x => x.MuWaitList); } if (search.KinopoiskID > 0) { filter = filter.Where(x => x.M.KinopoiskID == search.KinopoiskID); } if (search.SystemList) { var candidates = from mu in db.MovieUser join mr in db.MovieRecommendation on mu.MovieID equals mr.MovieID from omu in db.MovieUser.Where(mm => mm.MovieID == mr.OtherMovieID && mm.UserID == userId).DefaultIfEmpty() where mu.UserID == userId && mu.Mark == 10 && Sql2.IsNullOrFalse(omu.IsDontWant) && Sql2.IsNullOrFalse(omu.IsWaitlist) && Sql2.IsNullOrFalse(omu.IsWatched) orderby mr.Position select(int?) mr.OtherMovieID; var rmovies = candidates .Take(50) .ToArray(); if (rmovies.Length > 0) { filter = filter.Where(x => rmovies.Contains(x.M.ID)); } else { filter = filter.Where(x => (x.M.Status == MovieStatus.ComingSoon || x.M.Status == MovieStatus.RecentPremiere) && Sql2.IsNullOrFalse(x.MuUserWatched) && Sql2.IsNullOrFalse(x.MuWaitList) && Sql2.IsNullOrFalse(x.MuDontWant) ); } } if (search.MovieStatus != MovieStatus.Unknown) { filter = filter.Where(x => x.M.Status == search.MovieStatus); } List <Person> actors = null; if (!string.IsNullOrEmpty(search.Actors)) { var actorIds = search.Actors .Split(',') .Select(x => Convert.ToInt32(x)) .Cast <int?>() .ToArray(); actors = db.Person .Where(p => actorIds.Contains(p.ID)) .ToList(); var movieIds = db.MoviePerson .Where(mp => actorIds.Contains(mp.PersonID)) .Select(mp => mp.MovieID) .Cast <int?>() .ToArray(); filter = filter.Where(x => movieIds.Contains(x.M.ID)); } switch (pageParams.Order) { case "wldate": filter = filter.OrderByDescending(x => x.WlDate); break; case "rkp": filter = filter.OrderByDescending(f => f.M.RatingKinopoisk); break; case "rimdb": filter = filter.OrderByDescending(f => f.M.RatingImdb); break; case "ruser": filter = filter .Where(x => x.MuMark != null) .OrderByDescending(f => f.MuMark) .ThenByDescending(f => f.M.RatingKinopoisk); break; case "quality": filter = filter .OrderByDescending(f => f.M.BestVideoQuality) .ThenByDescending(f => f.M.RatingKinopoisk); break; case "added": filter = filter .OrderByDescending(f => f.M.Created) .ThenByDescending(f => f.M.ID); break; case "topweek": // ReSharper disable ConditionIsAlwaysTrueOrFalse filter = filter .Where(x => x.M.MovieTopWeek.TorrentCount != null) .Where(x => Sql2.IsNullOrFalse(x.MuUserWatched) && Sql2.IsNullOrFalse(x.MuWaitList) && Sql2.IsNullOrFalse(x.MuDontWant)) .OrderByDescending(f => f.M.MovieTopWeek.TorrentCount); // ReSharper restore ConditionIsAlwaysTrueOrFalse break; default: filter = filter.OrderBy(t => t.M.Title); break; } var count = pageParams.NoCount ? 0 : filter.Count(); var movies = filter .Skip(pageParams.SkipCount) .Take(pageParams.PageSize) .Select(t => new MovieItem { Self = new Movie { ID = t.M.ID, Title = t.M.Title, OriginalTitle = t.M.OriginalTitle, ImdbID = t.M.ImdbID, KinopoiskID = t.M.KinopoiskID, RatingImdb = t.M.RatingImdb, RatingKinopoisk = t.M.RatingKinopoisk, ReleaseDate = t.M.ReleaseDate, Status = t.M.Status, BestVideoQuality = t.M.BestVideoQuality }, InWaitList = t.MuWaitList, Mark = t.MuMark, IsWatched = t.MuUserWatched, IsDontWant = t.MuDontWant }) .ToList(); return(new MoviePagination(movies) { PageSize = pageParams.PageSize, TotalItems = count, Actors = actors }); } }
public static int InsertOrUpdate <T, TK>(this IEnumerable <Table2 <T, TK> > tables, Func <T, string> condition) { if (tables == null) { return(-1); } var updateTabs = tables.Where(a => a.__EditColumns.Count > 0).ToArray(); if (updateTabs.Length == 0) { return(0); } var sw = Stopwatch.StartNew(); var tableName = updateTabs[0].TableName; var sqls = new List <KeyValuePair <string, List <DbParameter> > >(); try { var paraIndex = 0; var sb = new StringBuilder(); var parameters = new List <DbParameter>(); foreach (var table in updateTabs) { var where = condition((T)(object)table); sb.Append("if not exists (select 1 from ").Append(tableName).Append(" where ").Append(where).Append(")\n"); sb.Append("insert into ").Append(tableName).Append(" ("); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("],"); } sb.Remove(sb.Length - 1, 1); sb.Append(")values("); foreach (var column in table.__EditColumns) { sb.Append("@p").Append(paraIndex).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + paraIndex, column.Value)); paraIndex++; } sb.Remove(sb.Length - 1, 1); sb.Append(")\n"); sb.Append("else update ").Append(tableName).Append(" set "); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("]=@p").Append(paraIndex).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + paraIndex, column.Value)); paraIndex++; } sb.Remove(sb.Length - 1, 1); sb.Append(" where ").Append(where).Append("\n"); if (paraIndex > 1500) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); sb = new StringBuilder(); paraIndex = 0; parameters = new List <DbParameter>(); } } if (sb.Length > 0) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); } using (var trans = new TransactionScope(TransactionScopeOption.Required, DbBase2.TransactionOption)) { using (var conn = DbBase2 <TK> .GetConnection(false)) { foreach (var sql in sqls) { var cmd = Sql2.CreateDbCommand(sql.Key, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sql.Value.ToArray()); cmd.ExecuteNonQuery(); } trans.Complete(); } } } catch (Exception e) { Log.Error(e.Message + e.StackTrace + sqls.Join("\n")); return(-1); } finally { var useTime = sw.ElapsedMilliseconds; var logStr = "Table.InsertOrUpdate({0}[{1}]) 执行时间 {2} 毫秒".Formats(tableName, updateTabs.Length, useTime.ToString("F0")); if (useTime / 1000 > 5) { Log.Warn(logStr); } Log.Debug(logStr); } return(updateTabs.Length); }
/// <summary> /// 删除后重新添加 /// </summary> public static int DeleteInsert <T, TK>(this IEnumerable <Table2 <T, TK> > rows, string deleteCondition) { try { if (rows == null) { return(-1); } var arrRows = rows.ToArray(); if (arrRows.Length == 0) { return(0); } var sw = Stopwatch.StartNew(); var option = new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = new TimeSpan(0, 2, 0) }; using (var trans = new TransactionScope(TransactionScopeOption.Required, option)) { using (var conn = DbBase2 <TK> .GetSqlConnection(false)) { using (var bulkCopy = new SqlBulkCopy(conn)) { var tableName = arrRows[0].TableName; var logStr = new StringBuilder(); try { var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn); var sda = Sql2.CreateDbDataAdapter(cmd); var dtSource = new DataTable(); sda.Fill(dtSource); var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource); if (deleteCondition.HasValue()) { var sql = "DELETE FROM {0} WHERE {1}".Formats(tableName, deleteCondition); cmd = Sql2.CreateDbCommand(sql, conn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); logStr.Append(sql).Append(";"); } bulkCopy.DestinationTableName = tableName; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); trans.Complete(); return(arrRows.Length); } catch (Exception e) { Log.Error(e.Message); return(-1); } finally { DbBase2.Close(conn); bulkCopy.Close(); sw.Stop(); var useTime = sw.ElapsedMilliseconds; logStr.AppendFormat("{0}.Insert({1}) 执行时间 {2} 毫秒", tableName, arrRows.Length, useTime.ToString("F0")); if (useTime > 5000) { Log.Warn(logStr.ToString()); } Log.Debug(logStr.ToString()); } } } } } catch (Exception e) { Log.Error(e.Message + e.StackTrace); return(-1); } }