C# DynamicParameters.AddDynamicParams方法代码示例

        /// <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 [email protected]");
            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 [email protected]");
            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 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 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 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 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 [email protected] 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();
            }
        }