public List <dynamic> Get(long content_id, string[] fields, long page, long size, out long totals, string query, dynamic where)
{
List <dynamic> items; totals = 0;
var parameters = new Dapper.DynamicParameters();
parameters.AddDynamicParams(where);
parameters.AddDynamicParams(new { fields = (fields == null || fields.Length == 0 ? new[] { string.Empty } : fields), guid = content_id, content_id = content_id, limit = size, offset = GetOffset(page, size) });
using (_db_connection)
{
_db_connection.Open();
using (var result = SqlMapper.QueryMultiple(_db_connection,
string.Format(@"select SQL_CALC_FOUND_ROWS files.*
from files
where
if(@guid = -1, 1 = 1, file_group = @guid)
and file_field in @fields
and {0} order by file_order limit @limit offset @offset; select FOUND_ROWS();",
query == null ? " 1 = 1 " : query
),
parameters
))
{
items = result.Read <dynamic>().ToList();
totals = GetTotalPages(result.Read <Int64>().Single(), size);
}
_db_connection.Close();
}
return(items);
}
/// <summary>
/// sql查询 分页集合对象
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public virtual async Task <PagedResult <TEntity> > QueryPageAsync(PagedRequest request, object param = null, int?commandTimeout = null, CommandType commandType = CommandType.StoredProcedure)
{
var type = typeof(TEntity);
var para = new Dapper.DynamicParameters();
para.Add("TableName", Reflector.GetTableName(type), DbType.String, ParameterDirection.Input);
para.Add("FieldsStr", "*", DbType.String, ParameterDirection.Input);
para.Add("OrderString", request.Order, DbType.String, ParameterDirection.Input);
para.Add("PageIndex", request.PageIndex, DbType.Int16, ParameterDirection.Input);
para.Add("PageSize", request.PageSize, DbType.Int16, ParameterDirection.Input);
para.Add("TotalRecord", request.PageSize, DbType.Int64, ParameterDirection.Output);
para.AddDynamicParams(param);
var DataList = await _context._connection.QueryAsync <TEntity>("proc_PageList", para, _context._transaction, commandTimeout, CommandType.StoredProcedure);
var TotalRecord = para.Get <long>("TotalRecord");
PagedResult <TEntity> entities = new PagedResult <TEntity>
{
Data = DataList.ToList(),
TotalRecords = TotalRecord,
PageIndex = request.PageIndex,
PageSize = request.PageSize,
TotalPages = ((int)TotalRecord + request.PageSize - 1) / request.PageSize
};
return(entities);
}
public List <dynamic> GetActive(string[] block_name, long[] content_root, string[] template_name, string order_fields, long page, long size, out long totals, string query, dynamic where, object domain)
{
List <dynamic> items; totals = 0;
var parameters = new Dapper.DynamicParameters();
parameters.AddDynamicParams(where);
parameters.AddDynamicParams(new { block_name = block_name, content_root = content_root, template_name = template_name, limit = size, offset = GetOffset(page, size), domain_name = (domain is string?domain: null), domain_id = (domain is string?null: domain) });
using (_db_connection)
{
_db_connection.Open();
using (var result = SqlMapper.QueryMultiple(_db_connection,
string.Format(@"select SQL_CALC_FOUND_ROWS contents.*, if(content_main = true, '/', content_url) as `content_url`, views.*, blocks.*, templates.*, domains.*
from contents left join views on (content_view = view_id) join blocks on (block_id = content_block) join templates on (template_id = content_template)
join domains on (domain_id = content_domain)
where if(@domain_name is null, 1 = 1, find_in_set(@domain_name, domain_name) > 0) and if(@domain_id is null, 1 = 1, @domain_id = domain_id) and content_active = true and (content_publish is null or content_publish <= now())
and {0} and {1} and {2} and {4} order by {3} limit @limit offset @offset; select FOUND_ROWS();",
query == null ? " 1 = 1 " : query,
block_name == null || block_name.Length == 0 ? " 1 = 1 " : "block_name in @block_name",
template_name == null || template_name.Length == 0 ? " 1 = 1 " : "template_name in @template_name",
order_fields ?? "content_order",
content_root == null || content_root.Length == 0 ? " 1 = 1 " : "content_root in @content_root"
),
parameters
))
{
items = result.Read <dynamic>().ToList();
totals = GetTotalPages(result.Read <Int64>().Single(), size);
}
_db_connection.Close();
}
return(items);
}
public List <dynamic> Search(string terms, string[] block_name, string field, long?content_id, long page, long size, out long totals, string query, dynamic where)
{
List <dynamic> items; totals = 0;
var parameters = new Dapper.DynamicParameters();
parameters.AddDynamicParams(where);
parameters.AddDynamicParams(new { terms = terms, block_name = block_name, field = field, icbcode_guid = content_id, content_id = content_id, limit = size, offset = GetOffset(page, size) });
using (_db_connection)
{
_db_connection.Open();
using (var result = SqlMapper.QueryMultiple(_db_connection,
string.Format(@"select SQL_CALC_FOUND_ROWS files.*, (MATCH (file_desc) AGAINST (@terms)) as score
from files join contents on (file_group = content_id) join blocks on (block_id = content_block)
where (MATCH (file_desc) AGAINST (@terms)) and
if(@icbcode_guid = -1, 1 = 1, file_group = @icbcode_guid) and
{1}
and file_field = @field
and {0} order by score desc limit @limit offset @offset; select FOUND_ROWS();",
query == null ? " 1 = 1 " : query,
block_name == null || block_name.Length == 0 ? " 1 = 1 " : "block_name in @block_name"
),
parameters
))
{
items = result.Read <dynamic>().ToList();
totals = GetTotalPages(result.Read <Int64>().Single(), size);
}
_db_connection.Close();
}
return(items);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(HotelInfo model)
{
StringBuilder query = new StringBuilder();
query.Append("Insert Into[dbo].[wx_hotels_info]");
query.Append(" ([wid],[hotelName],[hotelAddress],[hotelPhone],[mobilPhone],[noticeEmail],[emailPws],[smtp],[coverPic],[topPic],[orderLimit],[listMode],[messageNotice],[pwd],[hotelIntroduct],[orderRemark],[createDate],[sortid],[xplace],[yplace],[HotelCode],[Operator],[HotelLevel],[Recommend])");
query.Append(" Values");
query.Append(" (@wid, @hotelName, @hotelAddress, @hotelPhone, @mobilPhone, @noticeEmail, @emailPws, @smtp, @coverPic, @topPic, @orderLimit, @listMode, @messageNotice, @pwd, @hotelIntroduct, @orderRemark, @createDate, @sortid, @xplace, @yplace, @HotelCode, @Operator, @HotelLevel,@Recommend);");
query.Append("Select @Id = Scope_Identity();");
query.Append("Update dbo.wx_hotels_info Set HotelCode=Right('0000'+Cast(@Id As Varchar(10)),4) Where Id=@Id");
using (IDbConnection db = DbFactory.GetOpenedConnection())
{
DynamicParameters dynamicParameters = new DynamicParameters();
dynamicParameters.AddDynamicParams(model);
dynamicParameters.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
db.Execute(query.ToString(), dynamicParameters);
return dynamicParameters.Get<int>("@Id");
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(RoomInfo model)
{
StringBuilder query = new StringBuilder();
query.Append("Insert Into[dbo].[wx_hotel_room]");
query.Append("([hotelid],[roomType],[indroduce],[roomPrice],[salePrice],[facilities],[createDate],[sortid],[RoomCode],[UseInstruction],[RefundRule],[Status],[ExpiryDate_Begin],[ExpiryDate_End])");
query.Append("Values");
query.Append("(@hotelid, @roomType, @indroduce, @roomPrice, @salePrice, @facilities, @createDate, @sortid, @RoomCode, @UseInstruction, @RefundRule, @Status,@ExpiryDate_Begin,@ExpiryDate_End);");
query.Append("Select @Id = Scope_Identity()");
//query.Append("Update dbo.wx_hotel_room Set RoomCode=Right('0000'+Cast(@Id As Varchar(10)),4) Where Id=@Id");
using (IDbConnection db = DbFactory.GetOpenedConnection())
{
DynamicParameters dynamicParameters = new DynamicParameters();
dynamicParameters.AddDynamicParams(model);
dynamicParameters.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
db.Execute(query.ToString(), dynamicParameters);
return dynamicParameters.Get<int>("@Id");
}
}
public void TestAppendingAList()
{
DynamicParameters p = new DynamicParameters();
var list = new int[] { 1, 2, 3 };
p.AddDynamicParams(new { list });
var result = connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @list", p).ToList();
result[0].IsEqualTo(1);
result[1].IsEqualTo(2);
result[2].IsEqualTo(3);
}
public void TestAppendingAnExpandoObject()
{
dynamic expando = new System.Dynamic.ExpandoObject();
expando.A = 1;
expando.B = "two";
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(expando);
var result = connection.Query("select @A a, @B b", p).Single();
((int)result.a).IsEqualTo(1);
((string)result.b).IsEqualTo("two");
}
public void TestAppendingADictionary()
{
var dictionary = new Dictionary<string, object>();
dictionary.Add("A", 1);
dictionary.Add("B", "two");
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
var result = connection.Query("select @A a, @B b", p).Single();
((int)result.a).IsEqualTo(1);
((string)result.b).IsEqualTo("two");
}
public void TestAppendingAnonClasses()
{
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(new { A = 1, B = 2 });
p.AddDynamicParams(new { C = 3, D = 4 });
var result = connection.Query("select @A a,@B b,@C c,@D d", p).Single();
((int)result.a).IsEqualTo(1);
((int)result.b).IsEqualTo(2);
((int)result.c).IsEqualTo(3);
((int)result.d).IsEqualTo(4);
}
public void TestAppendingAList()
{
DynamicParameters p = new DynamicParameters();
var list = new int[] { 1, 2, 3 };
p.AddDynamicParams(new { list });
var result = Enumerable.ToList(connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @list", p));
Assert.IsEqualTo(result[0], 1);
Assert.IsEqualTo(result[1], 2);
Assert.IsEqualTo(result[2], 3);
}
public void TestAppendingADictionary()
{
var dictionary = new Dictionary<string, object>();
dictionary.Add("A", 1);
dictionary.Add("B", "two");
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
var result = Enumerable.Single(connection.QueryDirect("select @A a, @B b", p));
Assert.IsEqualTo(Convert.ToInt32(result["a"]), 1);
Assert.IsEqualTo(result["b"], "two");
}
public void TestAppendingAnonClasses()
{
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(new { A = 1, B = 2 });
p.AddDynamicParams(new { C = 3, D = 4 });
var result = Enumerable.Single(connection.QueryDirect("select @A a,@B b,@C c,@D d", p));
Assert.IsEqualTo(Convert.ToInt32(result["a"]), 1);
Assert.IsEqualTo(Convert.ToInt32(result["b"]), 2);
Assert.IsEqualTo(Convert.ToInt32(result["c"]), 3);
Assert.IsEqualTo(Convert.ToInt32(result["d"]), 4);
}
public void Test_AddDynamicParametersRepeatedShouldWork()
{
var args = new DynamicParameters();
args.AddDynamicParams(new { Foo = 123 });
args.AddDynamicParams(new { Foo = 123 });
int i = Enumerable.Single(connection.Query<int>("select @Foo", args));
Assert.IsEqualTo(i, 123);
}
public CommandDefinition ToCommand(int page, int size)
{
if (page < 1)
{
page = 1;
}
if (size < 1)
{
size = 20;
}
var sb = new StringBuilder();
var param = new DynamicParameters();
foreach (var p in data.SelectMany(o => o.Value.Where(p => p != null)))
{
param.AddDynamicParams(p.Parameters);
}
if (data.ContainsKey("having") && !data.ContainsKey("groupby"))
{
throw new ArgumentException("group by 参数未提供");
}
var offset = (page - 1) * size;
sb.AppendFormat("SELECT COUNT(0) as Count FROM {0}", data["from"]);
if (data.ContainsKey("where"))
{
sb.Append(data["where"]);
}
if (data.ContainsKey("groupby"))
{
sb.Append(data["groupby"]);
}
if (data.ContainsKey("having"))
{
sb.Append(data["having"]);
}
sb.Append(";\n");
sb.Append("SELECT ");
sb.Append(data["select"]);
sb.AppendFormat("FROM {0}", data["from"]);
if (data.ContainsKey("where"))
{
sb.Append(data["where"]);
}
if (data.ContainsKey("groupby"))
{
sb.Append(data["groupby"]);
}
if (data.ContainsKey("having"))
{
sb.Append(data["having"]);
}
if (data.ContainsKey("orderby"))
{
sb.Append(data["orderby"]);
}
sb.Append(" limit " + size + " offset " + offset);
sb.Append(";\n");
return(new CommandDefinition(sb.ToString(), param));
}
public void TestAppendingAListAsDictionary()
{
DynamicParameters p = new DynamicParameters();
var list = new int[] { 1, 2, 3 };
var args = new Dictionary<string, object>();
args.Add("ids", list);
p.AddDynamicParams(args);
var result = connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @ids", p).ToList();
result[0].IsEqualTo(1);
result[1].IsEqualTo(2);
result[2].IsEqualTo(3);
}
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 async Task<StreamEventsPage> ReadStream(string streamId, int fromVersion, int count, StreamReadDirection direction = StreamReadDirection.Forward)
{
Guard.NullOrWhiteSpace(() => streamId);
await _log.Debug("Reading stream {@streamId} from version {fromVersion} to version {count}", streamId, fromVersion, count);
// create parameters
var parameters = new DynamicParameters();
parameters.AddDynamicParams(new {
StreamId = streamId,
FromVersion = fromVersion,
Count = count,
ReadForward = direction == StreamReadDirection.Forward ? 1 : 0
});
parameters.AddOutput("Error");
parameters.AddReturnValue();
IEnumerable<StreamEventData> result;
// execute operation
using(var connection = new SqlConnection(_settings.ConnectionString)) {
// found a hardcore bug with Dapper!
// if we exit a query without executing a select, the .QueryAsync<T> fails because it
// tries to read the resultsetschema and fails.
// therefore we do not have access to return or output values.
result = await connection
.QueryAsync<StreamEventData>(
sql : "ReadStream",
param : parameters,
commandType: CommandType.StoredProcedure)
.ConfigureAwait(false);
}
// check for errors
switch(parameters.GetOutput<int>("Error")) {
case -100:
throw new StreamNotFoundException(streamId);
case -200:
throw new StreamDeletedException(streamId, fromVersion);
}
await _log.Information("Stream {@streamId} read from version {fromVersion} to version {count}", streamId, fromVersion, count);
// return stream page
var streamVersion = parameters.GetReturnValue();
var streamEvents = result.Select(
streamEventData => {
var metadata = _serializer.Deserialize<IDictionary<string, string>>(streamEventData.Metadata);
var domainEventType = Type.GetType(metadata[EventMetadataKeys.ClrType].ToString());
var streamEvent = _serializer.DeserializeAs<object>(streamEventData.Data, domainEventType);
return new StreamEvent(streamEvent, metadata);
}).ToList();
var lastReadEventVersion = streamEvents.Any()
? int.Parse(streamEvents.Last().Metadata[EventMetadataKeys.Version])
: -1;
await _log.Debug("Stream {@streamId} event serialization finished", streamId);
return new StreamEventsPage(
streamId : streamId,
fromVersion: fromVersion,
toVersion : lastReadEventVersion,
lastVersion: streamVersion,
events : streamEvents,
direction : direction);
}
public static List<DevFieldInfo> SearchFields(DevFieldInfo model, int excludedCategoryId = 0, bool like = true)
{
StringBuilder sb = new StringBuilder(@"SELECT top 200 * FROM DevFieldInfo WHERE Deleted<>1 ");
List<string> filters = new List<string>();
if (!string.IsNullOrEmpty(model.TableName))
{
filters.Add("TableName like @TableName");
if (like)
model.TableName = "%" + model.TableName + "%";
}
if (!string.IsNullOrEmpty(model.FieldName))
{
filters.Add("FieldName like @FieldName");
if (like)
model.FieldName = "%" + model.FieldName + "%";
}
if (!string.IsNullOrEmpty(model.FieldLabel))
{
filters.Add("FieldLabel like @FieldLabel");
if (like)
model.FieldLabel = "%" + model.FieldLabel + "%";
}
DynamicParameters dps = new DynamicParameters();
dps.AddDynamicParams(model);
if (excludedCategoryId > 0)
{
filters.Add("FieldId NOT IN (SELECT FieldId FROM DevFieldCategory WHERE CategoryId=@ExCategoryId AND Deleted<>1) ");
dps.Add("ExCategoryId", excludedCategoryId);
}
if (filters.Count > 0)
{
sb.Append(" AND ");
sb.Append(string.Join(" AND ", filters));
}
using (var db = DbHelper.Create())
{
return db.Query<DevFieldInfo>(sb.ToString(), dps).ToList();
}
}
public void Test_AddDynamicParametersRepeatedShouldWork()
{
var args = new DynamicParameters();
args.AddDynamicParams(new { Foo = 123 });
args.AddDynamicParams(new { Foo = 123 });
int i = connection.Query<int>("select @Foo", args).Single();
i.IsEqualTo(123);
}
public static void SetValues(ICollection<string> criteria, DynamicParameters dp, Result like)
{
criteria.Add(like.Query);
dp.AddDynamicParams(like.Parameters);
}