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