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);
}
}
