/// <summary> /// Stores the specified model. /// </summary> /// <param name="model">The model.</param> public void Store(Services.ViewModels.ConnectionStringPmo model) { var parms = new { id = model.Id, packageId = model.PackageId, name = model.Name, connectionString = model.ConnectionString, providerName = model.ProviderName, createdOn = DateTime.Now, updatedOn = DateTime.Now }; if (model.Id == 0) { Execute(cn => { if (Get(model.PackageId, model.Name) != null) { throw new UniqueIndexException("Violation of unique index."); } cn.Execute(@"INSERT INTO ConnectionStrings (PackageId, Name, ConnectionString, ProviderName, CreatedOn, UpdatedOn) VALUES (@packageId, @name, @connectionString, @providerName, @createdOn, @updatedOn);", parms); }); return; } Execute(cn => cn.Execute(@"UPDATE ConnectionStrings SET PackageId = @packageId, Name = @name, ConnectionString = @connectionString, ProviderName = @providerName, UpdatedOn = @updatedOn WHERE Id = @id", parms)); }
public void AddParameters(IDbCommand command, DapperDotNet.SqlMapper.Identity identity) { foreach (var item in this.Dictionary) { var p = command.CreateParameter(); p.ParameterName = item.Key; p.Value = item.Value ?? System.DBNull.Value; command.Parameters.Add(p); } }
/// <summary> /// Stores the specified release. /// </summary> /// <param name="release">The release.</param> /// <returns></returns> public DataModel.Release Store(DataModel.Release release) { return Execute(cn => { var sqlParams = new { id = release.Id, version = release.Version, description = release.Description, iconUrl = release.IconUrl, listed = release.Listed, published = release.Published, releaseNotes = release.ReleaseNotes, summary = release.Summary, tags = release.Tags, title = release.Title, sha1 = release.SHA1, createdOn = DateTime.Now, updatedOn = DateTime.Now }; if (0 == cn.ExecuteScalar<int>("SELECT count(*) from Releases WHERE Id COLLATE nocase = @id AND Version COLLATE nocase = @version", sqlParams)) { cn.Execute(@"INSERT INTO Releases (Id, Version, Description, IconUrl, Listed, Published, ReleaseNotes, Summary, Tags, Title, SHA1, CreatedOn, UpdatedOn) VALUES (@id, @version, @description, @iconUrl, @listed, @published, @releaseNotes, @summary, @tags, @title, @sha1, @createdOn, @updatedOn);", sqlParams); } else { cn.Execute( @"UPDATE Releases SET Description = @description, IconUrl = @iconUrl, Listed = @listed, Published = @published, ReleaseNotes = @releaseNotes, Summary = @summary, Tags = @tags, Title = @title, SHA1 = @sha1, UpdatedOn = @updatedOn WHERE Id COLLATE nocase = @id AND Version = @version; ", sqlParams); } return release; }); }
/// <summary> /// Stores the specified model. /// </summary> /// <param name="model">The model.</param> public void Store(Services.ViewModels.AppSettingPmo model) { Execute(cn => { var parms = new { id = model.Id, packageId = model.PackageId, key = model.Key, Value = model.Value, createdOn = DateTime.Now, updatedOn = DateTime.Now }; if (model.Id == 0) // assume insert { if (Get(model.PackageId, model.Key) != null) { throw new UniqueIndexException("Violation of unique index."); } cn.Execute(@"INSERT INTO PackageSettings (PackageId, Key, Value, CreatedOn, UpdatedOn) VALUES (@packageId, @key, @value, @createdOn, @updatedOn);", parms); return; } cn.Execute(@"UPDATE PackageSettings SET PackageId = @packageId, Key = @key, Value = @value, UpdatedOn = @updatedOn WHERE ID = @id;", parms); }); }
public new void AddParameters(IDbCommand command, Dapper.SqlMapper.Identity identity) { base.AddParameters(command, identity); var sqlCommand = (SqlCommand)command; sqlCommand.CommandType = CommandType.StoredProcedure; List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>(); // Create an SqlMetaData object that describes our table type. Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) }; foreach (int n in numbers) { // Create a new record, using the metadata array above. Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition); rec.SetInt32(0, n); // Set the value. number_list.Add(rec); // Add it to the list. } // Add the table parameter. var p = sqlCommand.Parameters.Add("ints", SqlDbType.Structured); p.Direction = ParameterDirection.Input; p.TypeName = "int_list_type"; p.Value = number_list; }
/// <summary> /// Set custom mapping for type deserializers. /// </summary> /// <param name="type">the <see cref="System.Type"/> to map</param> /// <param name="map">the mapping rules impementation, or null to remove custom map</param> public void SetTypeMap(Type type, Dapper.SqlMapper.ITypeMap map) { _mapping.SetTable(type, map as Mapping.Table); }
public void TestMultiMapArbitraryMaps() { // please excuse the trite example, but it is easier to follow than a more real-world one var createSql = @" create table #ReviewBoards (Id int, Name varchar(20), User1Id int, User2Id int, User3Id int, User4Id int, User5Id int, User6Id int, User7Id int, User8Id int, User9Id int) create table #Users (Id int, Name varchar(20)) insert #Users values(1, 'User 1') insert #Users values(2, 'User 2') insert #Users values(3, 'User 3') insert #Users values(4, 'User 4') insert #Users values(5, 'User 5') insert #Users values(6, 'User 6') insert #Users values(7, 'User 7') insert #Users values(8, 'User 8') insert #Users values(9, 'User 9') insert #ReviewBoards values(1, 'Review Board 1', 1, 2, 3, 4, 5, 6, 7, 8, 9) "; connection.Execute(createSql); try { var sql = @" select rb.Id, rb.Name, u1.*, u2.*, u3.*, u4.*, u5.*, u6.*, u7.*, u8.*, u9.* from #ReviewBoards rb inner join #Users u1 on u1.Id = rb.User1Id inner join #Users u2 on u2.Id = rb.User2Id inner join #Users u3 on u3.Id = rb.User3Id inner join #Users u4 on u4.Id = rb.User4Id inner join #Users u5 on u5.Id = rb.User5Id inner join #Users u6 on u6.Id = rb.User6Id inner join #Users u7 on u7.Id = rb.User7Id inner join #Users u8 on u8.Id = rb.User8Id inner join #Users u9 on u9.Id = rb.User9Id "; var types = new[] { typeof(ReviewBoard), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User), typeof(User) }; Func<object[], ReviewBoard> mapper = (objects) => { var board = (ReviewBoard)objects[0]; board.User1 = (User)objects[1]; board.User2 = (User)objects[2]; board.User3 = (User)objects[3]; board.User4 = (User)objects[4]; board.User5 = (User)objects[5]; board.User6 = (User)objects[6]; board.User7 = (User)objects[7]; board.User8 = (User)objects[8]; board.User9 = (User)objects[9]; return board; }; var data = connection.Query<ReviewBoard>(sql, types, mapper).ToList(); var p = data.First(); p.Id.IsEqualTo(1); p.Name.IsEqualTo("Review Board 1"); p.User1.Id.IsEqualTo(1); p.User2.Id.IsEqualTo(2); p.User3.Id.IsEqualTo(3); p.User4.Id.IsEqualTo(4); p.User5.Id.IsEqualTo(5); p.User6.Id.IsEqualTo(6); p.User7.Id.IsEqualTo(7); p.User8.Id.IsEqualTo(8); p.User9.Id.IsEqualTo(9); p.User1.Name.IsEqualTo("User 1"); p.User2.Name.IsEqualTo("User 2"); p.User3.Name.IsEqualTo("User 3"); p.User4.Name.IsEqualTo("User 4"); p.User5.Name.IsEqualTo("User 5"); p.User6.Name.IsEqualTo("User 6"); p.User7.Name.IsEqualTo("User 7"); p.User8.Name.IsEqualTo("User 8"); p.User9.Name.IsEqualTo("User 9"); } finally { connection.Execute("drop table #Users drop table #ReviewBoards"); } }
public IActionResult Get() { var r = Dapper.Query("select * from COMPANY where id=1 LIMIT 1 OFFSET 0;"); return(Ok(new{ r, DI.Value })); }
internal override void RemoveIndex(string tableName, string indexName) { var escapedIndexName = EscapeIdentifier(indexName); Dapper.Execute($"DROP INDEX {escapedIndexName}"); }
/// <summary> /// submmit order /// </summary> /// <param name="order">order info</param> /// <returns></returns> public async Task <ResponseResult <NewOrderResult> > Submmit(NewOrderAdd order) { var orderCode = Guid.NewGuid().ToString("N"); var dateNow = DateTime.Now; var strDateNow = dateNow.ToString("yyyy-MM-dd HH:mm:ss"); var lstGoodsDetail = order.GoodsInfos.Select(i => $"insert into `OrderDetail` (OrderCode,GoodsId,Count,Price,CreatedOn) values('{orderCode}',{i.GoodsId},{i.Count},{i.Price},'{strDateNow}')"); Dapper.BeginTransaction(); try { await Dapper.ExecuteAsync( "insert into `Order` (OrderCode,UserId,PayCode,Amount,PayStatus,OrderStatus,CreatedOn,CompletedTime) values(@OrderCode,@UserId,@PayCode,@Amount,@PayStatus,@OrderStatus,@CreatedOn,@CompletedTime);" + string.Join(";", lstGoodsDetail), new { OrderCode = orderCode, order.UserId, PayCode = string.Empty, Amount = order.GoodsInfos.Sum(i => i.Price *i.Count), PayStatus = (int)PayStatus.UnComplete, OrderStatus = (int)OrderStatus.Submmit, CreatedOn = strDateNow, CompletedTime = new DateTime(1999, 1, 1, 0, 0, 0) }); //publish message to goods service. await CapBus.PublishAsync("route.order.submmit", new OrderPublish { UserId = order.UserId, OrderCode = orderCode, GoodsInfos = order.GoodsInfos.Select(i => new GoodsInfoBase { Count = i.Count, GoodsId = i.GoodsId }) .ToList() }, "callback-stock-update"); //publish message to send email. await CapBus.PublishAsync("route.order.email", new OrderUser { OrderCode = orderCode, UserId = order.UserId, OrderStatus = OrderStatus.Submmit }); Dapper.CommitTransaction(); return(new ResponseResult <NewOrderResult> { Success = true, Result = new NewOrderResult { CreatedOn = dateNow, OrderCode = orderCode }, Error = "" }); } catch (Exception e) { //log e.message Logger.LogError(e, "submmit order has error"); Dapper.RollbackTransaction(); return(new ResponseResult <NewOrderResult> { Success = false, Result = null, Error = "submmit order has error" }); } }
/// <summary> /// update order status /// </summary> /// <param name="orderCode">order uid</param> /// <param name="status">order status</param> /// <returns></returns> public async Task <ResponseResult <bool> > UpdateOrderStatus(string orderCode, OrderStatus status, string orderResult = "") { try { var order = await Dapper.QueryFirstOrDefaultAsync <NewOrderBase>( "select OrderCode,OrderStatus,PayStatus from `Order` where OrderCode=@orderCode", new { orderCode }); if (order.OrderStatus == status) { //log Logger.LogError($"order code is :{orderCode},updated status is the same to the old status."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } if (order.OrderStatus == OrderStatus.Delete) //deleted order cann't be handle { //log Logger.LogError($"order code is :{orderCode},deleted order cann't be handled."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } if (order.OrderStatus == OrderStatus.Failed) //failed order can only be delete { if (status != OrderStatus.Delete) { //log Logger.LogError($"order code is :{orderCode},failed order can only be deleted."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } } if (order.OrderStatus == OrderStatus.Cancel) //cancelled order can only be deleted { if (status != OrderStatus.Delete) { //log Logger.LogError($"order code is :{orderCode},cancelled order can only be deleted."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } } if (order.OrderStatus == OrderStatus.Submmit) //submmitted order can be cancelled or failed. { if (status != OrderStatus.Cancel && status != OrderStatus.Failed) { //log Logger.LogError($"order code is :{orderCode},submmitted order can only be cancelled or failed."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } } if (order.OrderStatus == OrderStatus.Complete) //completed order can only be deleted { if (status != OrderStatus.Delete) { //log Logger.LogError($"order code is :{orderCode},completed order can only be deleted."); return(new ResponseResult <bool> { Result = false, Error = $"operation not permitted.", Success = false }); } } var result = await Dapper.ExecuteAsync( "update `Order` set OrderStatus=@status,Result=@orderResult where OrderCode=@orderCode", new { status, orderResult, orderCode }); if (result == 1) { return(new ResponseResult <bool> { Result = true, Error = "", Success = true }); } else { //log Logger.LogError($"order code is :{orderCode},order status was not changed."); return(new ResponseResult <bool> { Result = false, Error = $"operation failed.", Success = false }); } } catch (Exception e) { Logger.LogError(e, $"order code is :{orderCode},order status changed has error."); return(new ResponseResult <bool> { Result = false, Error = $"operation has error.", Success = false }); } }
internal override void RenameTable(string tableName, string newTableName) { Dapper.Execute($"sp_RENAME '{tableName}', '{newTableName}'"); }
internal override void RenameColumn(string tableName, string columnName, string newColumnName) { Dapper.Execute($"sp_RENAME '{tableName}.{columnName}', '{newColumnName}', 'COLUMN'"); }