/// <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'");
}
