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