public async Task InsertQueueItemsAsync(List <QueueDTO> queueItems) { var dataTable = new System.Data.DataTable(); dataTable.Columns.Add(new DataColumn("Payload", typeof(string))); queueItems.ForEach(e => dataTable.Rows.Add(e.Payload)); await _sqlService .ExecuteAsync ( _connectionString, INSERT_QUEUE_ITEMS, new { @QueueItems = dataTable.AsTableValuedParameter() }, commandType : CommandType.StoredProcedure ).ConfigureAwait(false); }
public async Task AppendStream(string streamId, int expectedVersion, IEnumerable<StreamEvent> streamEvents) { Guard.NullOrWhiteSpace(() => streamId); Guard.NullOrDefault(() => streamEvents); await _log.Debug("Appending stream {@streamId} with {@events}", streamId, streamEvents.ToArray()); // create DataTable to send as a TVP var newStreamEventsTable = new DataTable(); newStreamEventsTable.Columns.Add("Id", typeof(Guid)); newStreamEventsTable.Columns.Add("Type", typeof(string)); newStreamEventsTable.Columns.Add("Data", typeof(string)); newStreamEventsTable.Columns.Add("Metadata", typeof(string)); newStreamEventsTable.Columns.Add("StreamVersion", typeof(int)); var eventVersion = expectedVersion; var fallbackCommitId = Guid.NewGuid(); newStreamEventsTable.BeginLoadData(); foreach(var se in streamEvents) { se.EnrichMetadata(streamId, eventVersion++, fallbackCommitId); newStreamEventsTable.Rows.Add( se.Metadata[EventMetadataKeys.Id], se.Event.GetType().Name, _serializer.Serialize(se.Event), _serializer.Serialize(se.Metadata), eventVersion); } newStreamEventsTable.EndLoadData(); // create parameters var parameters = new DynamicParameters(); parameters.AddDynamicParams(new { StreamId = streamId, ExpectedStreamVersion = expectedVersion, StreamEvents = newStreamEventsTable.AsTableValuedParameter("StreamEvents") }); int actualVersion; // execute operation using(var connection = new SqlConnection(_settings.ConnectionString)) { actualVersion = await connection .ExecuteScalarAsync<int>( sql : "AppendStream", param : parameters, commandType: CommandType.StoredProcedure) .ConfigureAwait(false); } // if the actual version is different from the expected version if(actualVersion != eventVersion) { throw new StreamConcurrencyException(streamId, expectedVersion, actualVersion); } await _log.Information("Events appended to stream {@streamId}", streamId); // dispatch events await _dispatcher.DispatchStreamEvents(streamEvents); }
public bool UpdatePost(ContentTypeUpdateGet ContentType) { string usp = "usp_ContentTypeUpdatePost @ContentTypeId, @CodePrefix , @CodeSuffix , @CodeTypeId , @ContentTypeGroupId , @ProcessTypeId ,@SecurityLevelId, @Name, @Description, @MenuName, @MouseOver, @UserId, @IsRelationBasedOwnership " + " , @IsProjectBasedOwnership " + " , @IsOrganizationBasedOwnership " + " , @IsFreeOwnership " + " , @IsRelationBasedEdit " + " , @IsRelationBasedEditSelectable " + " , @IsOrganizationBasedEdit " + " , @IsOrganizationBasedEditSelectable " + " , @IsOrganizationBasedEditSub " + " , @IsOrganizationBasedEditParent " + " , @IsProjectBasedEdit " + " , @IsProjectBasedEditSelectable " + " , @IsProjectBasedEditSub " + " , @IsProjectBasedEditParent " + " , @IsFreeEdit " + " , @IsFreeEditSelectable " + " , @IsRelationBasedRead " + " , @IsRelationBasedReadSelectable " + " , @IsOrganizationBasedRead " + " , @IsOrganizationBasedReadSelectable " + " , @IsOrganizationBasedReadSub " + " , @IsOrganizationBasedReadParent " + " , @IsProjectBasedRead " + " , @IsProjectBasedReadSelectable " + " , @IsProjectBasedReadSub " + " , @IsProjectBasedReadParent " + " , @IsFreeRead " + " , @IsFreeReadSelectable, @Color, @IconId, @ContentTypeClassificationTable"; //_sqlDataAccess.SaveData<ContentTypeUpdateGet>(usp, ContentType); //return true; System.Data.DataTable ClassificationTable = ContentTypeClassificationValueDataTable.CreateTable(); var xy = new List <ContentTypeClassification>(); foreach (var x in ContentType.ContentTypeClassifications) { //if (x. != null && x.ClassificationValueId != 0) //{ ClassificationTable.Rows.Add( x.ClassificationId , x.ObjectTypeClassificationStatusId); //} } // string usp = "usp_ContentTypeCreatePost @ContentTypeGroupId, @ProcessTypeId , @SecurityLevelId, @Name , @Description, @MenuName , @MouseOver, @CreatorID, @MouseOver, @IconID, @ContentTypeClassificationTable "; _sqlDataAccess.SaveData <dynamic>(usp, new { ContentTypeId = ContentType.ContentTypeId , ContentTypeGroupId = ContentType.ContentTypeGroupId , ProcessTypeId = ContentType.@ProcessTypeId , SecurityLevelId = ContentType.SecurityLevelId , Name = ContentType.Name , Description = ContentType.Description , MenuName = ContentType.MenuName , MouseOver = ContentType.MouseOver , UserId = ContentType.UserId , IsRelationBasedOwnership = ContentType.IsRelationBasedOwnership , IsProjectBasedOwnership = ContentType.IsProjectBasedOwnership , IsOrganizationBasedOwnership = ContentType.IsOrganizationBasedOwnership , IsFreeOwnership = ContentType.IsFreeOwnership , IsRelationBasedEdit = ContentType.IsRelationBasedEdit , IsRelationBasedEditSelectable = ContentType.IsRelationBasedEditSelectable , IsOrganizationBasedEdit = ContentType.IsOrganizationBasedEdit , IsOrganizationBasedEditSelectable = ContentType.IsOrganizationBasedEditSelectable , IsOrganizationBasedEditSub = ContentType.IsOrganizationBasedEditSub , IsOrganizationBasedEditParent = ContentType.IsOrganizationBasedEditParent , IsProjectBasedEdit = ContentType.IsProjectBasedEdit , IsProjectBasedEditSelectable = ContentType.IsProjectBasedEditSelectable , IsProjectBasedEditSub = ContentType.IsProjectBasedEditSub , IsProjectBasedEditParent = ContentType.IsProjectBasedEditParent , IsFreeEdit = ContentType.IsFreeEdit , IsFreeEditSelectable = ContentType.IsFreeEditSelectable , IsRelationBasedRead = ContentType.IsRelationBasedRead , IsRelationBasedReadSelectable = ContentType.IsRelationBasedReadSelectable , IsOrganizationBasedRead = ContentType.IsOrganizationBasedRead , IsOrganizationBasedReadSelectable = ContentType.IsOrganizationBasedReadSelectable , IsOrganizationBasedReadSub = ContentType.IsOrganizationBasedReadSub , IsOrganizationBasedReadParent = ContentType.IsOrganizationBasedReadParent , IsProjectBasedRead = ContentType.IsProjectBasedRead , IsProjectBasedReadSelectable = ContentType.IsProjectBasedReadSelectable , IsProjectBasedReadSub = ContentType.IsProjectBasedReadSub , IsProjectBasedReadParent = ContentType.IsProjectBasedReadParent , IsFreeRead = ContentType.IsFreeRead , IsFreeReadSelectable = ContentType.IsFreeReadSelectable , Color = ContentType.Color , IconId = ContentType.IconId , ContentTypeClassificationTable = ClassificationTable.AsTableValuedParameter("udt_ContentTypeClassificationNew") }); return(true); }
public static List<ProcessedPost> GetProcessed( List<string> postIDs ) { var tableParam = new DataTable(); tableParam.Columns.Add( "postID", typeof( string ) ); foreach ( string postID in postIDs ) { tableParam.Rows.Add( postID ); } var query = @" select sub.SubName, p.PostID, act.ActionName as ""Action"",p.SeenByModules, p.AnalysisResults from ProcessedPosts p inner join Subreddits sub on sub.ID = p.SubredditID inner join Actions act on act.ID = p.ActionID inner join @postIDs pids on pids.PostID = p.PostID ;"; using ( var conn = DirtBagConnection.GetConn() ) { return conn.Query<ProcessedPost, byte[], ProcessedPost>( query, ( pp, b ) => { pp.AnalysisResults = Helpers.ProcessedPostHelpers.InflateAndDeserializeResults( b ); return pp; }, splitOn: "AnalysisResults", param: new { postIDs = tableParam.AsTableValuedParameter("postIDs") } ).ToList(); } }
/// <summary><see cref="IEnumerable{T}"/>をテーブル型パラメータへ変換 </summary> /// <typeparam name="T"><see cref="int"/>または<see cref="long"/>または<see cref="string"/></typeparam> /// <param name="items"><see cref="IEnumerable{T}"/>の配列<see cref="T[]"/> または、<see cref="List{T}"/>など</param> /// <returns> /// <see cref="Dapper.SqlMapper.ICusomQueryParameter"/>となる /// そのため、匿名クラスでプロパティを定義する必要がある /// </returns> /// <remarks> /// 利用方法 /// Id IN @Param となっていた箇所を修正 /// /// Id IN ( /// SELECT Id /// FROM @TableParam ) /// /// helper.GetItem(query, new { TableParam = array.GetTableParameter() }); /// /// ※ テーブル型として定義した Ids(int), BigIds(long), Codes(string) は、それぞれ /// 項目を Primary Key として登録しているため、問合せ前に重複しないよう GroupBy などで /// ユニークな値の配列を連携するように注意してください。 /// </remarks> public static SqlMapper.ICustomQueryParameter GetTableParameter <T>(this IEnumerable <T> items) { var type = typeof(T); var columnNames = new Dictionary <string, Type>(); var typeName = string.Empty; PropertyInfo[] props = null; Type[] duplicationType = new [] { typeof(Models.BillingImportDuplication), typeof(Models.ReceiptImportDuplication) }; if (type == typeof(int)) { columnNames.Add("Id", type); typeName = "Ids"; } else if (type == typeof(long)) { columnNames.Add("Id", type); typeName = "BigIds"; } else if (type == typeof(string)) { columnNames.Add("Code", type); typeName = "Codes"; } else { var modelType = duplicationType.FirstOrDefault(t => t.IsAssignableFrom(type)); if (modelType != null) { props = modelType.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty); foreach (var prop in props) { Type propType; if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>)) { propType = prop.PropertyType.GetGenericArguments().First(); } else { propType = prop.PropertyType; } columnNames.Add(prop.Name, propType); } typeName = modelType.Name; } else { throw new ArgumentException("parameter allowed int, long, string, XxxxImportDuplication"); } } var table = new System.Data.DataTable(); foreach (var pair in columnNames) { table.Columns.Add(pair.Key, pair.Value); } if (items == null) { return(null); } if (props == null) { foreach (var value in items) { table.Rows.Add(value); } } else { foreach (var value in items) { table.Rows.Add(props.Select(p => p.GetValue(value)).ToArray()); } } return(table.AsTableValuedParameter(typeName)); }