public IEnumerable <AuthorEM> GetAuthors(BaseDataTableFilterEM filter, out int totalFiltered)
{
string spName = "USPGetAuthors";
var order = filter.Order.First();
var column = filter.Columns[order.Column];
DynamicParameters sqlParams = new DynamicParameters();
sqlParams.Add("OrderBy", column.Name);
sqlParams.Add("Direction", order.Dir);
sqlParams.Add("Start", filter.Start);
sqlParams.Add("Length", filter.Length);
sqlParams.Add("TotalFiltered", DbType.Int32, direction: ParameterDirection.Output);
using (IDbConnection db = new SqlConnection(base.CurrentContext.DbConnection))
{
var result = db.Query <AuthorEM>(spName, sqlParams, null, true, null, CommandType.StoredProcedure);
totalFiltered = sqlParams.Get <int?>("@TotalFiltered") ?? 0;
return(result);
}
}
public int Insert(Satista entity)
{
var parameters = new DynamicParameters();
try
{
parameters.Add("@id", entity.id, DbType.Int32);
parameters.Add("@satisFiyati", entity.satisFiyati, dbType: DbType.Decimal);
parameters.Add("@itemId", entity.itemId, dbType: DbType.Int32);
parameters.Add("@kullaniciId", entity.kullaniciId, dbType: DbType.Int32);
Connection.Execute("AddSatista", parameters, commandType: System.Data.CommandType.StoredProcedure, transaction: Transaction);
entity.id = parameters.Get <int>("@id");
}
catch (Exception)
{
entity.id = -1;
}
return(entity.id);
}
public TeamModel CreateTeam(TeamModel model)
{
using (IDbConnection conneciton = new System.Data.SqlClient.SqlConnection(GlobalConfig.GetConnection(db)))
{
// var p = new DynamicParameters
var p = new DynamicParameters();
p.Add("@TeamName", model.TeamName);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
conneciton.Execute("dbo.spTeam_insert", p, commandType: CommandType.StoredProcedure);
model.id = p.Get <int>("@id");
foreach (PersonModel tm in model.TeamMembers)
{
p = new DynamicParameters();
p.Add("@TeamID", model.id);
p.Add("@PersonID", tm.Id);
conneciton.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
}
return(model);
}
}
public async Task <int> CreateUserClaim(UserClaimModel model)
{
try
{
var storedProcedure = "dbo.UserClaim_Create";
DynamicParameters p = new DynamicParameters();
p.Add("Type", model.Type);
p.Add("Value", model.Value);
p.Add("UserAccountId", model.UserAccountId);
p.Add("Id", DbType.Int32, direction: ParameterDirection.Output);
await _dataGateway.Execute(storedProcedure, p, _connectionString.SqlConnectionString);
return(p.Get <int>("Id"));
}
catch (SqlCustomException e)
{
throw new SqlCustomException("UserClaim could not be created.", e.InnerException);
}
}
public List <AccountEntity> ListAllPagingByStatus(int type, string keySearch, int pageIndex, int pageSize, string sortColumn, string sortDesc, ref int totalRow)
{
try
{
var p = new DynamicParameters();
p.Add("@type", type);
p.Add("@KeySearch", keySearch);
p.Add("@pageIndex", pageIndex);
p.Add("@pageSize", pageSize);
p.Add("@sortColumn", sortColumn);
p.Add("@sortDesc", sortDesc);
p.Add("@totalRow", dbType: DbType.Int32, direction: ParameterDirection.Output);
var data = unitOfWork.Procedure <AccountEntity>("Sp_Account_ListAllPagingByStatus", p);
totalRow = p.Get <int>("@totalRow");
return(data.ToList());
}
catch (Exception ex)
{
Logging.PutError(ex.Message, ex);
throw;
}
}
public int AddTestTaker(TestTaker testTaker)
{
string procName = "spTestTakerInsert";
var param = new DynamicParameters();
int TestTakerID = 0;
param.Add("@TestTakerID", testTaker.TestTakerID, null, ParameterDirection.Output);
try
{
SqlMapper.Execute(_connectionFactory.GetConnection,
procName, param, commandType: CommandType.StoredProcedure);
TestTakerID = param.Get <int>("@TestTakerID");
}
finally
{
_connectionFactory.CloseConnection();
}
return(TestTakerID);
}
public int Create(Employee obj)
{
using (SqlConnection con = new SqlConnection(Startup.ConnectionString))
{
try
{
con.Open();
var param = new DynamicParameters();
param.Add("id", null);
param.Add("personId", null);
param.Add("lastName", obj.LastName);
param.Add("firstName", obj.FirstName);
param.Add("birthday", obj.Birthday);
param.Add("phone", obj.Phone);
if (obj.Gender != null && new Regex("[0-9]+").IsMatch(obj.Gender))
{
param.Add("gender", Convert.ToInt32(obj.Gender));
}
param.Add("employeeSince", obj.EmployeeSince);
param.Add("returnValue", null, DbType.Int32, ParameterDirection.ReturnValue);
con.Execute("spInsertOrUpdateEmployee", param, commandType: CommandType.StoredProcedure);
return(param.Get <Int32>("returnValue"));
}
catch (SqlException ex)
{
return(-1);
}
finally
{
try
{
con.Close();
}
catch (SqlException) { }
}
}
}
/// <summary>
/// 获取消费列表
/// </summary>
/// <param name="pageModels"></param>
/// <param name="count"></param>
/// <returns></returns>
public IEnumerable <ItemTableViewModel> GetItemTable(QueryPageModel pageModels, out int count)
{
using (var conn = OpenConnection())
{
var param = new DynamicParameters();
param.Add("@UserID", pageModels.userId);
param.Add("@DateType", pageModels.dateType);
param.Add("@BeginDate", pageModels.startDate);
param.Add("@EndDate", pageModels.endDate);
param.Add("@Keywords", pageModels.keySearch);
param.Add("@Sorts", pageModels.sortString);
param.Add("@PageNumber", pageModels.page);
param.Add("@PagePerNumber", pageModels.pageSize);
param.Add("@HowManyItems", 0, DbType.Int32, ParameterDirection.Output);
var lists = conn.Query <ItemTableViewModel>("GetItemListWithPage_v7", param, null, true, null, CommandType.StoredProcedure);
count = param.Get <int>("@HowManyItems");
return(lists);
}
}
/// <summary>
/// Create a new person entry in the TournamentTracker SQL DataBase
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public PersonModel CreatePerson(PersonModel model)
{
//Stabilish the use of a connection inside the Using statement
//When this block of code is already executed then this connection
//will be automatic destroyed
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
{
var p = new DynamicParameters();
p.Add("@FirstName", model.FirstName);
p.Add("@LastName", model.LastName);
p.Add("@EmailAddress", model.EmailAddress);
p.Add("@CellPhoneNumber", model.CellPhoneNumber);
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spPeople_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@Id");
return(model);
}
}
public void Save(Contact contact)
{
using var txScope = new TransactionScope();
var parameters = new DynamicParameters();
parameters.Add("@Id", value: contact.Id, dbType: DbType.Int32, direction: ParameterDirection.InputOutput);
parameters.Add("@FirstName", contact.FirstName);
parameters.Add("@LastName", contact.LastName);
parameters.Add("@Company", contact.Company);
parameters.Add("@Title", contact.Title);
parameters.Add("@Email", contact.Email);
this.db.Execute("SaveContact", parameters, commandType: CommandType.StoredProcedure);
contact.Id = parameters.Get <int>("@Id");
foreach (var addr in contact.Addresses.Where(a => !a.IsDeleted))
{
addr.ContactId = contact.Id;
var addrParams = new DynamicParameters(new
{
ContactId = addr.ContactId,
AddressType = addr.AddressType,
StreetAddress = addr.StreetAddress,
City = addr.City,
StateId = addr.StateId,
PostalCode = addr.PostalCode
});
addrParams.Add("@Id", addr.Id, DbType.Int32, ParameterDirection.InputOutput);
this.db.Execute("SaveAddress", addrParams, commandType: CommandType.StoredProcedure);
addr.Id = addrParams.Get <int>("@Id");
}
foreach (var addr in contact.Addresses.Where(a => a.IsDeleted))
{
this.db.Execute("DeleteAddress", new { Id = addr.Id }, commandType: CommandType.StoredProcedure);
}
txScope.Complete();
}
public async Task <long> ReverseDocument(
long reversedTransactionDocumentId,
string companyCode,
int yearOfReversalDoc,
int yearNumberOfReversalDoc,
string docRefOfReversal,
DateTime docDateOfReversal)
{
DynamicParameters queryParameters = new DynamicParameters();
queryParameters.Add(DataVersionIdParameter, null);
queryParameters.Add("@ReversedTransactionDocumentId", reversedTransactionDocumentId);
queryParameters.Add("@CompanyCode", companyCode);
queryParameters.Add("@ReversalYear", yearOfReversalDoc);
queryParameters.Add("@ReversalYearNumber", yearNumberOfReversalDoc);
queryParameters.Add("@ReversalDocRef", docRefOfReversal);
queryParameters.Add("@ReversalDocumentDate", docDateOfReversal);
queryParameters.Add("@ReversalTransactionId", null, DbType.Int64, ParameterDirection.Output);
await ExecuteNonQueryAsync(StoredProcedureNames.ReverseDocument, queryParameters, true);
return(queryParameters.Get <long?>("ReversalTransactionId").Value);
}
public void InsertarS(string Accion, string Json)
{
try
{
AbrirConexion("SEG");
DynamicParameters param = new DynamicParameters();
param.Add("@Accion", Accion);
param.Add("@Json", Json);
param.Add("@Id_Sesion", null, DbType.Int64, ParameterDirection.Output);
objSesion.SqlConexion.Execute("SEG.pSesion", param, commandType: CommandType.StoredProcedure);
objSesion.idSesion = param.Get <long>("@Id_Sesion");
}
catch (Exception ex)
{
Mensajes.EscribirLog(Constantes.MensajeError, ex.Message, "CoreSEG.Datos.SesionDatos - Insertar");
throw;
}
finally
{
CerrarConexion();
}
}
public void CreateTeam(TeamModel model)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("Tournaments")))
{
var p = new DynamicParameters();
p.Add("@TeamName", model.TeamName);
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("dbo.spTeams_Insert", p, commandType: CommandType.StoredProcedure);
model.Id = p.Get <int>("@id");
foreach (PersonModel tm in model.TeamMembers)
{
p = new DynamicParameters();
p.Add("@TeamId", model.Id);
p.Add("@PersonId", tm.Id);
connection.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
}
}
}
public async Task <InterviewTemplate> InsertInterviewTemplate(InterviewTemplate interviewTemplate)
{
using (var connection = new SqlConnection(_AppSettings.ConnectionStrings.DefaultConnection))
{
var parameters = new DynamicParameters(
new
{
Name = interviewTemplate.Name,
});
parameters.Add("Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
await connection.ExecuteAsync(
StoredProcedures.InsertInterviewTemplate,
parameters,
commandType : CommandType.StoredProcedure);
interviewTemplate.Id = parameters.Get <int>("Id").ToString();
return(interviewTemplate);
}
}
public static long insert(IDbTransaction dbTransaction = null, params string[] input)
{
string sql = $"insert into users (firstname,lastname,contactFK) values (@firstname,@lastname,@contactFK);" +
$"SELECT @id=SCOPE_IDENTITY();";
var dp = new DynamicParameters();
dp.Add("@id", 0, DbType.Int64, ParameterDirection.Output);
dp.Add("@firstname", input[0]);
dp.Add("@lastname", input[1]);
dp.Add("@contactFK", input[2]);
if (dbTransaction == null)
{
dbConnection.Execute(sql, dp);
}
else
{
dbConnection.Execute(sql, dp, transaction: dbTransaction);
}
return(dp.Get <long>("@id"));
}
public virtual int CadastrarProduto(Produto novoProduto)
{
using (IDbConnection cnn = new SqlConnection(connectionString))
{
var param = new DynamicParameters();
param.Add("@ProdutoDescricao", novoProduto.ProdutoDescricao);
param.Add("@ProdutoValor", novoProduto.ProdutoValor);
param.Add("@ProdutoQuantidadeEstoque", novoProduto.ProdutoQuantidadeEstoque);
param.Add("@ProdutoCodigo", 0, DbType.Int32, ParameterDirection.Output);
string sql = "dbo.up_CadastrarProduto";
var retorno = cnn.Execute(sql, param, commandType: CommandType.StoredProcedure);
if (!retorno.Equals(1))
{
return(0);
}
return(param.Get <int>("@ProdutoCodigo"));
}
}
/// <summary>
/// 保存实体数据（新增、修改）
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public void SaveEntity(string keyValue, Mes_SupplyEntity entity)
{
var db = this.BaseRepository().BeginTrans();
try
{
if (!string.IsNullOrEmpty(keyValue))
{
entity.Modify(keyValue);
this.BaseRepository().Update(entity);
}
else
{
var dp = new DynamicParameters(new { });
dp.Add("@codeType", "供应商编码");
dp.Add("@code", "", DbType.String, ParameterDirection.Output);
dp.Add("@goodsSecNo", "");
dp.Add("@stockType", "");
db.ExecuteByProc("sp_GetCode", dp);
var S_Code = dp.Get <string>("@code"); //存储过程返回编号
entity.S_Code = S_Code;
entity.Create();
db.Insert(entity);
}
db.Commit();
}
catch (Exception ex)
{
if (ex is ExceptionEx)
{
throw;
}
else
{
throw ExceptionEx.ThrowServiceException(ex);
}
}
}
public async Task <CommonResult <MedioSolicitud> > MedioSolicitud_CreaMdf(MedioSolicitud medioSolicitudModel)
{
CommonResult <MedioSolicitud> _commonResult = new CommonResult <MedioSolicitud>();
try
{
using (IDbConnection conexion = new SqlConnection(_Iconexion.GetConexion()))
{
var Parameters = new DynamicParameters();
Parameters.Add("@IdMedSol", medioSolicitudModel.IdMedSol, dbType: DbType.Int32, direction: ParameterDirection.Input);
Parameters.Add("@Descripcion", medioSolicitudModel.Descripcion, dbType: DbType.String, direction: ParameterDirection.Input, size: 2500);
Parameters.Add("@msj", dbType: DbType.String, direction: ParameterDirection.Output, size: 200);
var Result = await conexion.ExecuteScalarAsync("SP_MedioSolicitud_CreaMdf", param : Parameters, commandType : CommandType.StoredProcedure);
string PCmsj = Parameters.Get <string>("@msj");
if (String.IsNullOrEmpty(PCmsj))
{
_commonResult.Exito = true;
return(_commonResult);
}
else
{
_commonResult.Exito = false;
_commonResult.MsjDB = PCmsj;
_commonResult.MsjError = "";
return(_commonResult);
}
}
}
catch (Exception ex)
{
_commonResult.Exito = false;
_commonResult.MsjDB = "";
_commonResult.MsjError = ex.Message;
return(_commonResult);
}
}
public async Task <CommonResult <IncidenciaListModel> > Incidencia_xEstado_Cons(int IdEstIncidencia)
{
CommonResult <IncidenciaListModel> _commonResult = new CommonResult <IncidenciaListModel>();
try
{
using (IDbConnection conexion = new SqlConnection(_Iconexion.GetConexion()))
{
var Parameters = new DynamicParameters();
Parameters.Add("@IdEstIncidencia", IdEstIncidencia, dbType: DbType.Int64, direction: ParameterDirection.Input);
Parameters.Add("@msj", dbType: DbType.String, direction: ParameterDirection.Output, size: 200);
var Result = await conexion.QueryAsync <IncidenciaListModel>("SP_Incidencia_xEstado_Cons", param : Parameters, commandType : CommandType.StoredProcedure);
string PCmsj = Parameters.Get <string>("@msj");
if (String.IsNullOrEmpty(PCmsj))
{
_commonResult.Exito = true;
_commonResult.Lista = Result.AsList();
return(_commonResult);
}
else
{
_commonResult.Exito = false;
_commonResult.MsjDB = PCmsj;
_commonResult.MsjError = "";
return(_commonResult);
}
}
}
catch (Exception ex)
{
_commonResult.Exito = false;
_commonResult.MsjDB = "";
_commonResult.MsjError = ex.Message;
return(_commonResult);
}
}
public async Task <CommonResult <TipDocIdnListModel> > TipoDocIdn_Cons()
{
CommonResult <TipDocIdnListModel> _commonResult = new CommonResult <TipDocIdnListModel>();
try
{
using (IDbConnection conexion = new SqlConnection(_Iconexion.GetConexion()))
{
var Parameters = new DynamicParameters();
Parameters.Add("@msj", dbType: DbType.String, direction: ParameterDirection.Output, size: 200);
IEnumerable <TipDocIdnListModel> Result = await conexion.QueryAsync <TipDocIdnListModel>("SP_TipDocIdnCons", param : Parameters, commandType : CommandType.StoredProcedure);
string PCmsj = Parameters.Get <string>("@msj");
if (String.IsNullOrEmpty(PCmsj))
{
_commonResult.Exito = true;
_commonResult.Lista = Result.AsList();
return(_commonResult);
}
else
{
_commonResult.Exito = false;
_commonResult.MsjDB = PCmsj;
_commonResult.MsjError = "";
return(_commonResult);
}
}
}
catch (Exception ex)
{
_commonResult.Exito = false;
_commonResult.MsjDB = "";
_commonResult.MsjError = ex.Message;
return(_commonResult);
}
}
internal static void Acquire(IDbConnection connection, string resource, TimeSpan timeout)
{
var parameters = new DynamicParameters();
parameters.Add("@Resource", resource);
parameters.Add("@DbPrincipal", "public");
parameters.Add("@LockMode", LockMode);
parameters.Add("@LockOwner", LockOwner);
parameters.Add("@LockTimeout", (int)timeout.TotalMilliseconds);
parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
// Ensuring the timeout for the command is longer than the timeout specified for the stored procedure.
var commandTimeout = (int)(timeout.TotalSeconds + CommandTimeoutAdditionSeconds);
connection.Execute(
@"sp_getapplock",
parameters,
commandTimeout: commandTimeout,
commandType: CommandType.StoredProcedure);
var lockResult = parameters.Get <int>("@Result");
if (lockResult < 0)
{
if (lockResult == -1)
{
throw new DistributedLockTimeoutException(resource);
}
throw new SqlServerDistributedLockException(
String.Format(
"Could not place a lock on the resource '{0}': {1}.",
resource,
LockErrorMessages.ContainsKey(lockResult)
? LockErrorMessages[lockResult]
: String.Format("Server returned the '{0}' error.", lockResult)));
}
}
public static string PageBreakTables(CMSDataContext db, string sql, DynamicParameters p)
{
var cs = Util.ReadOnlyConnectionString(db.Host, db.CurrentRoles().Contains("Finance"));
var cn = new SqlConnection(cs);
cn.Open();
var sb = new StringBuilder();
int pagebreakcol = 0;
var pg = 1;
while (true)
{
var s = sql.Replace("WHERE 1=1", $"WHERE pagebreak={pg}");
var cmd = new SqlCommand(s, cn);
foreach (var parm in p.ParameterNames)
{
var value = p.Get <dynamic>(parm);
cmd.Parameters.AddWithValue(parm, value);
}
cmd.CommandTimeout = 1200;
using (var rd = cmd.ExecuteReader())
{
if (!rd.HasRows)
{
return(sb.ToString());
}
if (pg == 1)
{
var colnames = Enumerable.Range(0, rd.FieldCount).Select(rd.GetName).ToList();
pagebreakcol = colnames.FindIndex(vv => vv == "pagebreak");
}
var t = HtmlTable(rd, $"pagebreak={pagebreakcol}");
t.RenderControl(new HtmlTextWriter(new StringWriter(sb)));
sb.AppendLine("<div class='page-break'></div>");
}
pg++;
}
}
public int Insertar(SocioFinca socioFinca)
{
int result = 0;
var parameters = new DynamicParameters();
parameters.Add("@SocioId", socioFinca.SocioId);
parameters.Add("@ProductorFincaId", socioFinca.ProductorFincaId);
parameters.Add("@ViasAccesoCentroAcopio", socioFinca.ViasAccesoCentroAcopio);
parameters.Add("@DistanciaKilometrosCentroAcopio", socioFinca.DistanciaKilometrosCentroAcopio);
parameters.Add("@TiempoTotalFincaCentroAcopio", socioFinca.TiempoTotalFincaCentroAcopio);
parameters.Add("@MedioTransporte", socioFinca.MedioTransporte);
parameters.Add("@Cultivo", socioFinca.Cultivo);
parameters.Add("@Precipitacion", socioFinca.Precipitacion);
parameters.Add("@CantidadPersonalCosecha", socioFinca.CantidadPersonalCosecha);
parameters.Add("@FechaRegistro", socioFinca.FechaRegistro);
parameters.Add("@UsuarioRegistro", socioFinca.UsuarioRegistro);
parameters.Add("@EstadoId", socioFinca.EstadoId);
parameters.Add("@AreaTotal", socioFinca.AreaTotal);
parameters.Add("@AreaCafeEnProduccion", socioFinca.AreaCafeEnProduccion);
parameters.Add("@Crecimiento", socioFinca.Crecimiento);
parameters.Add("@Bosque", socioFinca.Bosque);
parameters.Add("@Purma", socioFinca.Purma);
parameters.Add("@PanLlevar", socioFinca.PanLlevar);
parameters.Add("@Vivienda", socioFinca.Vivienda);
parameters.Add("@SocioFincaId", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (IDbConnection db = new SqlConnection(_connectionString.Value.CoffeeConnectDB))
{
result = db.Execute("uspSocioFincaInsertar", parameters, commandType: CommandType.StoredProcedure);
}
int id = parameters.Get <int>("SocioFincaId");
return(id);
}
public async Task <SearchResult <ProductSearchViewModel> > ProductSearchByCategory(string tenantId, string languageId, string categorySeoLink, bool?isHot, bool?isHomePage, int page = 1, int pageSize = 20)
{
try
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
if (con.State == ConnectionState.Closed)
{
await con.OpenAsync();
}
DynamicParameters param = new DynamicParameters();
param.Add("@TenantId", tenantId);
param.Add("@LanguageId", languageId);
param.Add("@IsHot", isHot);
param.Add("@IsHomePage", isHomePage);
param.Add("@CategorySeoLink", categorySeoLink);
param.Add("@Page", page);
param.Add("@PageSize", pageSize);
param.Add("@TotalRows", DbType.Int32, direction: ParameterDirection.Output);
var items = await con.QueryAsync <ProductSearchViewModel>("[dbo].[sp_Product_GetByProductCategory]", param, commandType : CommandType.StoredProcedure);
var result = new SearchResult <ProductSearchViewModel>
{
Items = items.ToList(),
TotalRows = param.Get <int>("TotalRows")
};
return(result);
}
}
catch (Exception ex)
{
_logger.LogError(ex, "sp_Product_GetByProductCategory ProductService Error.");
return(null);
}
}
public Tag Update(Tag entity)
{
using (var connection = _dbConnectionFactory.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var parameters = new DynamicParameters();
parameters.Add("@Id", entity.Id, DbType.Int32);
parameters.Add("@Name", entity.Name, DbType.String);
// Configure outputs
parameters.Add("@ErrorMessage", dbType: DbType.String, size: 1000, direction: ParameterDirection.Output);
// Execute query
int result = connection.Execute(
sql: TagUpdate_Proc,
commandType: CommandType.StoredProcedure,
transaction: transaction,
param: parameters
);
// Check for errors
string errorMessage = parameters.Get <string>("@ErrorMessage");
if (!string.IsNullOrEmpty(errorMessage))
{
throw new RepositoryException(errorMessage);
}
transaction.Commit();
return(entity);
}
}
}
public ResultModel Create(apple model)
{
var _conn = new GetConnection(_config);
var result = new ResultModel();
using (var conn = new SqlConnection(new GetConnection(_config).applePenConnection()))
{
//INSERT DATA
var sql = @"INSERT INTO apples(name)
VALUES (@name); ";
var p = new DynamicParameters();
p.Add("@name", model.name, DbType.String, direction: ParameterDirection.Input);
p.Add("@r", DbType.Int32, direction: ParameterDirection.Output);
var sw = new Stopwatch();
sw.Start();
try
{
//conn.Execute(sql, p);
var rr = conn.Execute("sp_create_apple", p, commandType: CommandType.StoredProcedure);
result.d = p.Get <dynamic>("@r");//接收SP回傳值
}
catch (Exception e)
{
result.r = false;
result.d = e;
return(result);
}
//GET latest data
sql = @"SELECT TOP(1) * FROM apples ORDER BY id DESC";
var query = conn.Query(sql).SingleOrDefault();
sw.Stop();
result.r = true;
result.m = "Elapsed time = " + sw.ElapsedMilliseconds / 1000.0 + "s";
//result.d = query;
}
return(result);
}
protected virtual void Dispose(bool disposing)
{
if (_disposedValue)
{
return;
}
if (disposing)
{
// DISPOSE THE UNDERLYING SQLSERVER STUFF
var parameters = new DynamicParameters();
parameters.Add("@Resource", Name);
parameters.Add("@LockOwner", "Session");
parameters.Add("exitCode", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
_dbConnection.Execute("sp_releaseapplock",
parameters,
commandType: CommandType.StoredProcedure);
var exitCode = parameters.Get <int>("exitCode");
if (exitCode == (int)SpGetAppLockReturnCode.CallOrParameterError)
{
// TODO what to do if we are passing invalid data to SQL server?
}
if (_config.DisposeDbConnection)
{
_dbConnection?.Close();
_dbConnection?.Dispose();
_dbConnection = null;
}
IsActive = false;
Name = null;
}
_disposedValue = true;
}
/// <summary>
/// 分页查询 通过 存储过程 PROC_SPLITPAGE
/// </summary>
/// <param name="SqlStr"></param>
/// <param name="Page"></param>
/// <param name="Rows"></param>
/// <param name="Param"></param>
/// <param name="ArryEntity"></param>
/// <returns></returns>
private PagingEntity GetPagingEntity(string SqlStr, int Page, int Rows, DbParameter[] Param, params BaseClass[] ArryEntity)
{
var _PagingEntity = new PagingEntity();
//解析参数
foreach (var item in Param)
{
SqlStr = SqlStr.Replace("@" + item.ParameterName, item.Value == null ? null : "'" + item.Value + "' ");
}
var _DynamicParameters = new DynamicParameters();
_DynamicParameters.Add("@SQL", SqlStr, DbType.String, ParameterDirection.Input);
_DynamicParameters.Add("@PAGE", Page, DbType.Int32, ParameterDirection.Input);
_DynamicParameters.Add("@PAGESIZE", Rows, DbType.Int32, ParameterDirection.Input);
_DynamicParameters.Add("@PAGECOUNT", 0, DbType.Int32, ParameterDirection.Output);
_DynamicParameters.Add("@RECORDCOUNT", 0, DbType.Int32, ParameterDirection.Output);
var _IDataReader = db.ExecuteReader("PROC_SPLITPAGE", _DynamicParameters, null, 30, CommandType.StoredProcedure);
//将 IDataReader 对象转换为 DataSet
DataSet _DataSet = new AdoExtend.WYDataSet();
_DataSet.Load(_IDataReader, LoadOption.OverwriteChanges, null, new DataTable[] { });
if (_DataSet.Tables.Count == 2)
{
var _Table = _DataSet.Tables[1];
var _Total = _DynamicParameters.Get <int>("@RECORDCOUNT");
_PagingEntity.Table = _Table;
_PagingEntity.Counts = _Total;
_PagingEntity.PageCount = (_Total / Rows);
_PagingEntity.List = _Table.ToList();
this.SetHeaderJson(_PagingEntity, ArryEntity);
}
return(_PagingEntity);
}
public async Task <CheckLists> AddCheckList(CheckLists list)
{
using (IDbConnection conn = Connection){
CheckLists _list = new CheckLists {
CHECKLISTID = -1,
TITLE = list.TITLE,
USERID = list.USERID,
CARDID = list.CARDID,
ITEMS = new List <CheckListItems>()
};
var _params = new DynamicParameters();
_params.Add("@title", _list.TITLE);
_params.Add("@userid", _list.USERID);
_params.Add("@cardid", _list.CARDID);
_params.Add(name: "@v_checklistid", value: _list.CHECKLISTID, dbType: DbType.Int64,
direction: ParameterDirection.InputOutput);
string sQuery = @" begin ";
sQuery += " insert into CHECKLISTS(TITLE,USERID,CARDID) values (@title,@userid,@cardid) ";
sQuery += " set @v_checklistid=SCOPE_IDENTITY()";
sQuery += " end ";
//try{
var affectedRows = await conn.ExecuteAsync(sQuery, _params);
if (affectedRows > 0)
{
Int64 newListId = _params.Get <Int64>("@v_checklistid");
_list.CHECKLISTID = newListId;
}
// }catch(Exception ex){
// return null;
// }
return(_list);
}
}
public SiteTestAnswer GetGuessStep(List <VMSiteTestStepGuess> steps, out int subjectsCount)
{
var table = new DataTable();
table.Columns.Add(new DataColumn {
ColumnName = "QuestionId"
});
table.Columns.Add(new DataColumn {
ColumnName = "IsCorrect"
});
table.Columns.Add(new DataColumn {
ColumnName = "Order"
});
steps.ForEach(x =>
{
table.Rows.Add(x.QuestionId, x.IsCorrect, x.Order);
});
var p = new DynamicParameters();
p.Add("oldSteps", table.AsTableValuedParameter("dbo.OldSiteTestStepGuess"));
p.Add("subjectsCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
using (var conn = new SqlConnection(ConnectionString))
{
var data = conn.Query <SiteTestAnswer, SiteTestQuestion, SiteTestSubject, SiteTest, SiteTestAnswer>("dbo.get_site_test_next_guess_step", (a, q, s, t) =>
{
a.Question = q;
q.Test = t;
a.Subject = s;
return(a);
}, p, commandType: CommandType.StoredProcedure).SingleOrDefault();
subjectsCount = p.Get <int>("subjectsCount");
return(data);
}
}
/*
/// <summary>
/// Adds a buddy request. (Should be approved later by "ToUserID")
/// </summary>
/// <param name="FromUserID">The from user id.</param>
/// <param name="ToUserID">The to user id.</param>
/// <param name="useDisplayName">Display name of the use.</param>
/// <returns>
/// The name of the second user + Whether this request is approved or not.
/// </returns>
public static string[] AddRequest(
this IRepository<Buddy> repository,
int fromUserID,
int toUserID,
bool useDisplayName)
{
CodeContracts.VerifyNotNull(repository, "repository");
bool approved = false;
string paramOutput = null;
repository.DbFunction.Query.buddy_addrequest(
FromUserID: fromUserID,
ToUserID: toUserID,
UTCTIMESTAMP: DateTime.UtcNow,
approved: out approved,
UseDisplayName: useDisplayName,
paramOutput: out paramOutput);
//return new[] { paramOutput, approved.ToString() };
}*/
/// <summary>
/// Approves a buddy request.
/// </summary>
/// <param name="FromUserID">
/// The from user id.
/// </param>
/// <param name="ToUserID">
/// The to user id.
/// </param>
/// <param name="Mutual">
/// Should the requesting user (ToUserID) be added to FromUserID's buddy list too?
/// </param>
/// <returns>
/// the name of the second user.
/// </returns>
public static string ApproveRequest(this IRepository<Buddy> repository, int fromUserID, int toUserID, bool mutual, bool useDisplayName)
{
CodeContracts.VerifyNotNull(repository, "repository");
var parameters = new DynamicParameters();
parameters.Add("@FromUserID", fromUserID);
parameters.Add("@ToUserID", toUserID);
parameters.Add("@mutual", mutual);
parameters.Add("@UTCTIMESTAMP", DateTime.UtcNow);
parameters.Add("@UseDisplayName", useDisplayName);
parameters.Add("@paramOutput", dbType: DbType.String, direction: ParameterDirection.Output);
repository.DbAccess.GetCommand().Execute("buddy_addrequest", parameters, CommandType.StoredProcedure);
return parameters.Get<string>("@paramOutput");
}
public bool ForecastVehicleSave(ForecastVehicle obj)
{
bool retVal = true;
string procName = (obj.Id == 0 ? "dbo.ForecastVehicle_New" : "dbo.ForecastVehicle_Edit");
using (IDbConnection conn = DbHelper.GetDBConnection())
{
try
{
var para = new DynamicParameters();
para.Add("@p_ForecastVehicleId", obj.ForecastVehicleId, dbType: DbType.Int32);
para.Add("@p_ProgrammeId", obj.ProgrammeId, dbType: DbType.Int32);
para.Add("@p_VehicleId", obj.VehicleId, dbType: DbType.Int32);
para.Add("@p_GatewayId", obj.GatewayId, dbType: DbType.Int32);
if (obj.Id == 0)
{
para.Add("@p_Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
}
else
{
para.Add("@p_Id", obj.Id, dbType: DbType.Int32);
}
conn.Execute(procName, para, commandType: CommandType.StoredProcedure);
if (obj.Id == 0)
{
obj.Id = para.Get<int>("@p_Id");
}
}
catch (Exception ex)
{
AppHelper.LogError("ForecastVehicleDataStore.ForecastVehicleSave", ex.Message, CurrentCDSID);
retVal = false;
}
}
return retVal;
}
/// <summary>
/// Log the info message to SQL
/// </summary>
protected override void LogInfo(Info info)
{
using (var c = GetConnection())
{
if (RollupThreshold.HasValue && info.ItemHash.HasValue)
{
var queryParams = new DynamicParameters(new
{
info.DuplicateCount,
info.ItemHash,
ApplicationName = info.ApplicationName.Truncate(50),
minDate = DateTime.UtcNow.Add(RollupThreshold.Value.Negate())
});
queryParams.Add("@newGUID", dbType: DbType.Guid, direction: ParameterDirection.Output);
var count = c.Execute(@"
Update Info
Set DuplicateCount = DuplicateCount + @DuplicateCount,
@newGUID = GUID
Where Id In (Select Top 1 Id
From Info
Where ItemHash = @ItemHash
And ApplicationName = @ApplicationName
And DeletionDate Is Null
And CreationDate >= @minDate)"
, queryParams);
// if we found an error that's a duplicate, jump out
if (count > 0)
{
info.GUID = queryParams.Get<Guid>("@newGUID");
return;
}
}
c.Execute(@"insert into Info(ApplicationName, MachineName, CreationDate, Message, IsProtected, ItemHash, DuplicateCount)
values (@ApplicationName, @MachineName, @CreationDate, @Message, @IsProtected, @ItemHash, @DuplicateCount)",
new
{
ApplicationName = info.ApplicationName.Truncate(50),
MachineName = info.MachineName.Truncate(50),
info.CreationDate,
info.Message,
info.IsProtected,
info.ItemHash,
info.DuplicateCount
});
}
}
/// <summary>
/// Logs the error to SQL
/// If the rollup conditions are met, then the matching error will have a DuplicateCount += @DuplicateCount (usually 1, unless in retry) rather than a distinct new row for the error
/// </summary>
/// <param name="error">The error to log</param>
protected override void LogError(Error error)
{
using (var c = GetConnection())
{
if (RollupThreshold.HasValue && error.ErrorHash.HasValue)
{
var queryParams = new DynamicParameters(new
{
error.DuplicateCount,
error.ErrorHash,
ApplicationName,
minDate = DateTime.UtcNow.Add(RollupThreshold.Value.Negate())
});
queryParams.Add("@newGUID", dbType: DbType.Guid, direction: ParameterDirection.Output);
var count = c.Execute(@"
Update Exceptions
Set DuplicateCount = DuplicateCount + @DuplicateCount,
@newGUID = GUID
Where Id In (Select Top 1 Id
From Exceptions
Where ErrorHash = @ErrorHash
And ApplicationName = @ApplicationName
And DeletionDate Is Null
And CreationDate >= @minDate)", queryParams);
// if we found an error that's a duplicate, jump out
if (count > 0)
{
error.GUID = queryParams.Get<Guid>("@newGUID");
return;
}
}
error.FullJson = error.ToJson();
c.Execute(@"
Insert Into Exceptions (GUID, ApplicationName, MachineName, CreationDate, Type, IsProtected, Host, Url, HTTPMethod, IPAddress, Source, Message, Detail, StatusCode, SQL, FullJson, ErrorHash, DuplicateCount)
Values (@GUID, @ApplicationName, @MachineName, @CreationDate, @Type, @IsProtected, @Host, @Url, @HTTPMethod, @IPAddress, @Source, @Message, @Detail, @StatusCode, @SQL, @FullJson, @ErrorHash, @DuplicateCount)",
new {
error.GUID,
ApplicationName = error.ApplicationName.Truncate(50),
MachineName = error.MachineName.Truncate(50),
error.CreationDate,
Type = error.Type.Truncate(100),
error.IsProtected,
Host = error.Host.Truncate(100),
Url = error.Url.Truncate(500),
HTTPMethod = error.HTTPMethod.Truncate(10), // this feels silly, but you never know when someone will up and go crazy with HTTP 1.2!
error.IPAddress,
Source = error.Source.Truncate(100),
Message = error.Message.Truncate(1000),
error.Detail,
error.StatusCode,
error.SQL,
error.FullJson,
error.ErrorHash,
error.DuplicateCount
});
}
}
public void QueryTyped_WithAnonymousParamsAndOutParam_Executes()
{
using (var c = _dataAndSchema.GetConnection())
{
c.Open();
var args = new DynamicParameters();
args.AddDynamicParams(new { Val = _dataAndSchema.Value.ToString() });
args.Add("@affected", null, DbType.Int32, ParameterDirection.InputOutput);
var result = c.Query<EntityBar>(
SqlDataAndSchema.SprocNameDapperSingleInSingleOutParam,
args,
commandType: CommandType.StoredProcedure).FirstOrDefault();
var outparam = args.Get<int>("@affected");
Assert.IsNotNull(result);
Assert.IsTrue(outparam == 1);
}
}
private static void TableValuedParms()
{
//HERE DATATABLE NAME SHOULD BE THE TABLEVALUE TYPE NAME OTHERWISE WILL GET AN ERROR TYPENAME IS FOUND
var dt = new DataTable { TableName = "DepartmentType" };
dt.Columns.Add("DepartmentId", typeof(int));
dt.Columns.Add("Name", typeof(string));
var arrayName = new[] { "HR", "Dev", "DevOps", "Admin" };
for (var i = 0; i <= 3; i++)
{
var row = dt.NewRow();
row["DepartmentId"] = i + 2;
row["Name"] = arrayName[i];
dt.Rows.Add(row);
}
using (var con = new SqlConnection(ConString))
{
var parameters = new DynamicParameters();
parameters.Add("@DepartmentDetails", dt, DbType.Structured, ParameterDirection.Input);
parameters.Add("@ReturnValue", 0, direction: ParameterDirection.ReturnValue);
con.Execute("spBulkInsert", parameters, CommandType.StoredProcedure);
Console.WriteLine("\nExpected - {0}, Got - {1}", 4, parameters.Get<int>("@ReturnValue"));
}
}
