public static DataTable SelectUserIdsByUnionID(IEnumerable <string> unionids) { if (unionids != null && unionids.Any()) { string sql = @"SELECT u.UserId ,u.UnionId COLLATE Chinese_PRC_CI_AS FROM Tuhu_profiles..UserAuth AS u WITH ( NOLOCK ) JOIN @TVP AS t ON u.UnionId COLLATE Chinese_PRC_CI_AS = t.TargetID WHERE u.AuthSource = N'Weixin' AND u.BindingStatus = N'Bound' and u.UserId is not null and u.UnionId is not null ;"; var records = new List <SqlDataRecord>(unionids.Count()); foreach (var target in unionids) { var record = new SqlDataRecord(new SqlMetaData("TargetID", SqlDbType.Char, 100)); var chars = new SqlChars(target); record.SetSqlChars(0, chars); records.Add(record); } using (var helper = DbHelper.CreateDbHelper(true)) { using (var cmd = new SqlCommand(sql)) { cmd.CommandType = CommandType.Text; SqlParameter p = new SqlParameter("@TVP", SqlDbType.Structured); p.TypeName = "dbo.Targets"; p.Value = records; cmd.Parameters.Add(p); DataTable result = helper.ExecuteQuery(cmd, dt => dt); return(result); } } } return(null); }
private void WriteSyncLogs(IEnumerable <UserMessageBoxInfoRelation> logs) { // type=1的时候id是Tuhu_notification..MessageBoxMessageInfo的pkid type=2时id是Tuhu_notification..UserMessageBoxInfoRelation的pkid if (logs != null && logs.Any()) { Func <IEnumerable <int>, int, int> WriteLogs = (ids, type) => { if (ids != null && ids.Any()) { string sql = $@"INSERT INTO Tuhu_notification..ExpiredMessageBoxInfo ( ID , Type , CreateDateTime , LastUpdateDateTime ) SELECT ss.TargetID , {type} , GETDATE() , GETDATE() FROM @TVP AS ss;"; using (var helper = DbHelper.CreateLogDbHelper()) { using (var cmd = new SqlCommand(sql)) { cmd.CommandType = CommandType.Text; var records = new List <SqlDataRecord>(ids.Count()); foreach (var target in ids) { var record = new SqlDataRecord(new SqlMetaData("TargetID", SqlDbType.Char, 40)); var chars = new SqlChars(target.ToString()); record.SetSqlChars(0, chars); records.Add(record); } SqlParameter p = new SqlParameter("@TVP", SqlDbType.Structured); p.TypeName = "dbo.Target"; p.Value = records; cmd.Parameters.Add(p); return(helper.ExecuteNonQuery(cmd)); } } } return(0); }; var messageids = logs.Select(x => x.MessageID).Distinct(); var relationids = logs.Select(x => x.PKID); var messageresult = WriteLogs(messageids, 1); Logger.Info($"写过期消息{messageresult}个"); var relationresult = WriteLogs(relationids, 2); Logger.Info($"写过期消息关系{relationresult}个"); } }