public async Task <bool> AddEmployeeWithDetails(Guid BusinessID, EmployeeModel employeeModel) { var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID); var empName = new Npgsql.NpgsqlParameter("@theempfullname", employeeModel.EmployeeName); var empGender = new Npgsql.NpgsqlParameter("@theempgender", employeeModel.EmployeeGender); var empSSN = new Npgsql.NpgsqlParameter("@theempssn", employeeModel.EmployeeSSN); var empDepartmentID = new Npgsql.NpgsqlParameter("@thedeptid", employeeModel.DepartmentID); var empDetails = new Npgsql.NpgsqlParameter("@theempdetails", NpgsqlDbType.Json) { Direction = ParameterDirection.InputOutput, Value = employeeModel.EmployeeDetails != null ? employeeModel.EmployeeDetails : (object)DBNull.Value }; EmployeeModel employeeModel1 = await Task.Run(() => _projectContextDB.EmployeeModel.FromSqlRaw("select * from addemployee(@thebusinessid,@theempfullname," + "@theempgender,@theempssn,@thedeptid,@theempdetails) as emp_id", businessID, empName, empGender, empSSN, empDepartmentID, empDetails) .Select(e => new EmployeeModel() { EmployeeID = e.EmployeeID }).FirstOrDefault()); if (employeeModel1.EmployeeID != new Guid()) { return(true); } return(false); }
public async Task <object> AddNewBusiness(BusinessModel businessModel) { try { var banme = new Npgsql.NpgsqlParameter("@thebusinessname", businessModel.BusinessName); var blogo = new Npgsql.NpgsqlParameter("@thelogo", businessModel.BusinessLogo); var bcurrency = new Npgsql.NpgsqlParameter("@thedefaultcurrency", businessModel.BusinessDefaultCurrency); var bAddressDetails = new Npgsql.NpgsqlParameter("@theaddressdetails", NpgsqlDbType.Json) { Direction = ParameterDirection.InputOutput, Value = businessModel.BusinessAddressDetails != null ? businessModel.BusinessAddressDetails : (object)DBNull.Value }; var bContactDetails = new Npgsql.NpgsqlParameter("@thecontactdetails", NpgsqlDbType.Json) { Direction = ParameterDirection.InputOutput, Value = businessModel.BusinessContactDetails != null ? businessModel.BusinessContactDetails : (Object)DBNull.Value }; var bOwner = new Npgsql.NpgsqlParameter("@thebusinessowner", businessModel.BusinessOwner); await Task.Run(() => _projectContextDB.Database.ExecuteSqlRaw("call addbusinesswithdetailsandcreateschema(@thebusinessname,@thelogo,@thedefaultcurrency,@theaddressdetails,@thecontactdetails,@thebusinessowner)", banme, blogo, bcurrency, bAddressDetails, bContactDetails, bOwner)); return(true); } catch (Exception ex) { return(null); } }
protected override ValidationResult IsValid(object value, ValidationContext validationContext) { var db = new FinancesContext(); Object obj = validationContext.ObjectType; System.Diagnostics.Debug.WriteLine(obj.ToString()); var className = obj.ToString().Split('.').Last(); var propertyName = validationContext.MemberName; var parameterName = string.Format("@{0}", propertyName); var sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}={2}", className.ToLower() + "s", propertyName, parameterName); var parameters = new Npgsql.NpgsqlParameter(parameterName, value); var result = db.Database.SqlQuery <Int64>(sql, parameters).Single(); if (result > 0) { return(new ValidationResult(String.Format(ErrorMessage, value), new List <string>() { propertyName })); } return(null); }
public void AddParameter(string name, object value, System.Data.DbType paramtype) { Npgsql.NpgsqlParameter param = new Npgsql.NpgsqlParameter(name, paramtype); param.Value = value; comm.Parameters.Add(param); }
/// <summary> /// converte o parametro para o tipo esperado /// </summary> /// <param name="copy">Se true, copia os valores deste objeto</param> /// <returns></returns> internal DbParameter ToParameterType(bool copy = true) { DbParameter result = null; #region Detecta o tipo de parametro switch (Configuration.DataGenericSettings.Settings.DatabaseType) { case DatabaseType.PostgreSQL: result = new Npgsql.NpgsqlParameter(); break; case DatabaseType.SQLite: result = new System.Data.SQLite.SQLiteParameter(); break; default: throw new Exceptions.DatabaseTypeNotDefined(); } #endregion #region Copia o objeto if (copy) { result.ParameterName = ParameterName; result.DbType = DbType; result.SourceColumn = SourceColumn; result.Value = Value; } #endregion return(result); }
private void SetQueueOptions(QueueConsumerConfiguration obj, int orderId) { var SqlParam = new Npgsql.NpgsqlParameter("@OrderID", orderId); obj.AddUserParameter(SqlParam); obj.SetUserWhereClause("(q.OrderID = @OrderID)"); }
public async Task <object> ShowDeductionsByEmployeeId(Guid businessID, Guid employeeID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var empoyeeId = new Npgsql.NpgsqlParameter("@theemployeeid", employeeID); List <DeductionResponseInJson> deductionResponseInJsons = await Task.Run(() => _projectContextDb.DeductionResponseInJson. FromSqlRaw("select * from showdeductionsbyemployeeid(@thebusinessid,@theemployeeid)", businessId, empoyeeId). Select(e => new DeductionResponseInJson() { DeductionsDetails = e.DeductionsDetails }).ToList()); foreach (DeductionResponseInJson dd in deductionResponseInJsons) { if (dd.DeductionsDetails != null) { List <DeductionResponse> deductionResponse = JsonConvert.DeserializeObject <List <DeductionResponse> >(deductionResponseInJsons.FirstOrDefault().DeductionsDetails); var response = new { Success = "OK", deductionResponse }; return(response); } } return(null); }
public void AddParameter(string name, object value, System.Data.DbType paramtype) { Npgsql.NpgsqlParameter param = new Npgsql.NpgsqlParameter(name, paramtype); param.Value = value; comm.Parameters.Add(param); }
public async Task <object> ShowAttendanceByDate(Guid businessID, string theFromDate, string theToDate) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var fromDate = new Npgsql.NpgsqlParameter("@fromdate", NpgsqlDbType.Date) { Direction = ParameterDirection.InputOutput, Value = !string.IsNullOrEmpty(theFromDate) ? Convert.ToDateTime(theFromDate) : (object)DBNull.Value }; var toDate = new Npgsql.NpgsqlParameter("@todate", NpgsqlDbType.Date) { Direction = ParameterDirection.InputOutput, Value = !string.IsNullOrEmpty(theToDate) ? Convert.ToDateTime(theToDate) : (object)DBNull.Value }; List <AttendanceResponse> attendanceResponse = await Task.Run(() => projectContextDB.AttendanceResponse .FromSqlRaw("select * from showattendancebydate(@thebusinessid,@fromdate,@todate)", businessId, fromDate, toDate) .Select(e => new AttendanceResponse() { AttendanceID = e.AttendanceID, EmployeeID = e.EmployeeID, Attendance = e.Attendance, AttendanceDate = Convert.ToString(e.AttendanceDate) }).ToList()); if (attendanceResponse.Count > 0) { var Response = new { Success = "OK", attendanceResponse = attendanceResponse }; return(Response); } return(null); }
public async Task <Guid[]> AddDeduction(Guid businessID, DeductionModel deductionModel) { try { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var deductionDetails = new Npgsql.NpgsqlParameter("@thedeductiondetails", NpgsqlDbType.Json) { Direction = ParameterDirection.InputOutput, Value = deductionModel.DeductionDetails != null ? deductionModel.DeductionDetails : (object)DBNull.Value }; DeductionAddingButEmployeeIDsNotPresent deductionAddingButEmployeeIDsNotPresent = await Task.Run(() => _projectContextDb.DeductionAddingButEmployeeIDsNotPresent.FromSqlRaw("select * from adddeduction(@thebusinessid,@thedeductiondetails)", businessId, deductionDetails) .Select(e => new DeductionAddingButEmployeeIDsNotPresent() { NotFoundEmployeesIds = e.NotFoundEmployeesIds }).FirstOrDefault()); if (deductionAddingButEmployeeIDsNotPresent.NotFoundEmployeesIds.Length == 0) { return(null); } return(deductionAddingButEmployeeIDsNotPresent.NotFoundEmployeesIds); } catch (Exception ex) { return(null); } }
public async Task <object> ShowDeductionById(Guid businessID, Guid deductionID) { try { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var deductionId = new Npgsql.NpgsqlParameter("@thedeductionid", deductionID); DeductionResponseInJson deductionResponseInJson = await Task.Run(() => _projectContextDb.DeductionResponseInJson.FromSqlRaw("select * from showdeductionbyid(@thebusinessid,@thedeductionid)", businessId, deductionId) .Select(e => new DeductionResponseInJson() { DeductionsDetails = e.DeductionsDetails }).FirstOrDefault()); if (deductionResponseInJson.DeductionsDetails != null) { DeductionResponse deductionResponse = JsonConvert.DeserializeObject <DeductionResponse>(deductionResponseInJson.DeductionsDetails); var response = new { Success = "OK", deductionResponse }; return(response); } return(null); } catch (Exception) { return(null); } }
public async Task <object> ShowIncrementsByemployeeId(Guid businessID, Guid employeeID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var employeeId = new Npgsql.NpgsqlParameter("@theemployeeid", employeeID); List <IncrementResponseInJson> incrementResponseInJson = await Task.Run(() => _projectContextDB.IncrementResponseInJson. FromSqlRaw("select * from showincrementsbyemployeeid(@thebusinessid,@theemployeeid)", businessId, employeeId). Select(e => new IncrementResponseInJson() { IncrementDetails = e.IncrementDetails }).ToList()); foreach (IncrementResponseInJson ir in incrementResponseInJson) { if (ir.IncrementDetails != null) { List <IncrementsResponse> incrementsResponse = JsonConvert.DeserializeObject <List <IncrementsResponse> >(incrementResponseInJson.FirstOrDefault().IncrementDetails); var response = new { Success = "OK", incrementsResponse }; return(response); } } return(null); }
public async Task <object> ShowIncrementById(Guid businessID, Guid incrementID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID); IncrementResponseInJson incrementResponseInJson = await Task.Run(() => _projectContextDB.IncrementResponseInJson. FromSqlRaw("select * from showincrementbyid(@thebusinessid,@theincrementid)", businessId, incrementId). Select(e => new IncrementResponseInJson() { IncrementDetails = e.IncrementDetails }).FirstOrDefault()); if (incrementResponseInJson.IncrementDetails != null) { IncrementsResponse incrementsResponse = JsonConvert.DeserializeObject <IncrementsResponse>(incrementResponseInJson.IncrementDetails); var response = new { Success = "OK", incrementsResponse }; return(response); } return(null); }
public async Task <bool> UpdateEmployeeWithDetailsById(Guid BusinessID, Guid employeeId, UpdateEmployeeModel updateEmployeeModel) { var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID); var employeeID = new Npgsql.NpgsqlParameter("@theempid", employeeId); var empName = new Npgsql.NpgsqlParameter("@theempfullname", updateEmployeeModel.EmployeeName); var empGender = new Npgsql.NpgsqlParameter("@theempgender", updateEmployeeModel.EmployeeGender); var empSSN = new Npgsql.NpgsqlParameter("@theempssn", updateEmployeeModel.EmployeeSSN); var empDepartmentID = new Npgsql.NpgsqlParameter("@thedeptid", updateEmployeeModel.DepartmentID); var empBps = new Npgsql.NpgsqlParameter("@bps", updateEmployeeModel.BPS); var empDesignation = new Npgsql.NpgsqlParameter("@theempdesignation", updateEmployeeModel.EmpDesignation); var empPaCountry = new Npgsql.NpgsqlParameter("@theemppacountry", updateEmployeeModel.PACountry); var empPaCity = new Npgsql.NpgsqlParameter("@theemppacity", updateEmployeeModel.PACity); var empPaProvince = new Npgsql.NpgsqlParameter("@theemppaprovince", updateEmployeeModel.PAProvince); var empPaZip = new Npgsql.NpgsqlParameter("@theemppazip", updateEmployeeModel.PAZip); var empMaCountry = new Npgsql.NpgsqlParameter("@theempmacountry", updateEmployeeModel.MACountry); var empMaCity = new Npgsql.NpgsqlParameter("@theempmacity", updateEmployeeModel.MACity); var empMaProvince = new Npgsql.NpgsqlParameter("@theempmaprovince", updateEmployeeModel.MAProvince); var empMaZip = new Npgsql.NpgsqlParameter("@theempmazip", updateEmployeeModel.MAZip); var empEmail = new Npgsql.NpgsqlParameter("@theempemail", updateEmployeeModel.EmpEmail); var empJoiningDate = new Npgsql.NpgsqlParameter("@theempjoiningdate", NpgsqlDbType.Date) { Direction = ParameterDirection.InputOutput, Value = !string.IsNullOrEmpty(updateEmployeeModel.EmpJoiningDate) ? Convert.ToDateTime(updateEmployeeModel.EmpJoiningDate) : (object)DBNull.Value }; var empAppDate = new Npgsql.NpgsqlParameter("@theempappointmentdate", NpgsqlDbType.Date) { Direction = ParameterDirection.InputOutput, Value = !string.IsNullOrEmpty(updateEmployeeModel.EmpAppointmentDate) ? Convert.ToDateTime(updateEmployeeModel.EmpAppointmentDate) : (object)DBNull.Value }; var empDesc = new Npgsql.NpgsqlParameter("@thedescription", updateEmployeeModel.Description); var empPhoto = new Npgsql.NpgsqlParameter("@thephoto", updateEmployeeModel.Photo); var empSkype = new Npgsql.NpgsqlParameter("@theskypeusername", updateEmployeeModel.SkypeUserName); var empOfficePhoneNo = new Npgsql.NpgsqlParameter("@theempofficephoneno", updateEmployeeModel.EmpOfficeNo); var empCellNo = new Npgsql.NpgsqlParameter("@theempcellno", updateEmployeeModel.EmpCellNo); UpdateEmployeeModel updateEmployeeModel1 = await Task.Run(() => _projectContextDB.UpdateEmployeeModel.FromSqlRaw("select * from updateemployee(@thebusinessid,@theempid,@theempfullname," + "@theempgender,@theempssn,@thedeptid,@bps,@theempdesignation,@theemppacountry,@theemppacity,@theemppaprovince,@theemppazip," + "@theempmacountry,@theempmacity,@theempmaprovince,@theempmazip,@theempemail,@theempjoiningdate,@theempappointmentdate, " + "@thedescription,@thePhoto,@theskypeusername,@theempofficephoneno,@theempcellno) as emp_id ", businessID, employeeID, empName, empGender, empSSN, empDepartmentID, empBps, empDesignation, empPaCountry, empPaCity, empPaProvince, empPaZip, empMaCountry, empMaCity, empMaProvince, empMaZip, empEmail, empJoiningDate, empAppDate, empDesc, empPhoto, empSkype, empOfficePhoneNo, empCellNo) .Select(e => new UpdateEmployeeModel() { EmployeeID = e.EmployeeID }).FirstOrDefault()); if (updateEmployeeModel1.EmployeeID != new Guid()) { return(true); } return(false); }
public async Task UpdateDeductionByID(Guid businessID, Guid deductionID, UpdateDeductionModel updateDeductionModel) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var deductionId = new Npgsql.NpgsqlParameter("@thedeductid", deductionID); var amount = new Npgsql.NpgsqlParameter("@theamount", updateDeductionModel.Amount); var description = new Npgsql.NpgsqlParameter("@thedescription", updateDeductionModel.Description); await Task.Run(() => _projectContextDb.Database.ExecuteSqlRaw("call updatededuction(@thebusinessid,@thedeductid,@theamount,@thedescription)", businessId, deductionId, amount, description)); }
public async Task UpdateIncrementByID(Guid businessID, Guid incrementID, IncrementsUpdateModel incrementsUpdateModel) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID); var amount = new Npgsql.NpgsqlParameter("@theamount", incrementsUpdateModel.IncrementAmount); var description = new Npgsql.NpgsqlParameter("@thedescription", incrementsUpdateModel.Description); await Task.Run(() => _projectContextDB.Database.ExecuteSqlRaw("call updateincrementbyid(@thebusinessid,@theincrementid,@theamount,@thedescription)", businessId, incrementId, amount, description)); }
public async Task <object> ShowAllDepartments(Guid BusinessID) { var theBusinessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID); return(await Task.Run(() => _projectContextDB.departmentResponse.FromSqlRaw("select * from showalldepartments(@thebusinessid)", theBusinessID) .Select(e => new DepartmentResponse() { DepartmentID = e.DepartmentID, DepartmentName = e.DepartmentName }).ToList())); }
public async Task <bool> CheckBusinessID(Guid businessID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkbusinessid(@thebusinessid) as checkbusinessid", businessId) .Select(e => new CheckStatusModel() { BusinessIDStatus = e.BusinessIDStatus }).FirstOrDefault()); return(checkStatusModel.BusinessIDStatus); }
public async Task <bool> ActivateUserById(Guid userID) { var userId = new Npgsql.NpgsqlParameter("@theuserid", userID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activateuser(@theuserid)", userId) .Select(e => new CheckStatusModel() { Status = e.Status }).FirstOrDefault()); return(checkStatusModel.Status); }
public async void IntegerAsync(int check) { var param = new Npgsql.NpgsqlParameter[] { new Npgsql.NpgsqlParameter("p_integer", NpgsqlTypes.NpgsqlDbType.Integer) { Value = check }, }; var query = await Function.IntegerAsync("public.return_number", param, this.Common.ConnectionString, this.Cancellation.Token); Assert.Equal(check + 1, query); }
public async void BooleanAsync(bool check) { var param = new Npgsql.NpgsqlParameter[] { new Npgsql.NpgsqlParameter("p_boolean", NpgsqlTypes.NpgsqlDbType.Boolean) { Value = check }, }; var query = await Function.BooleanAsync("public.return_boolean", param, this.Common.ConnectionString, this.Cancellation.Token); Assert.Equal(!check, query); }
public void Boolean(bool check) { var param = new Npgsql.NpgsqlParameter[] { new Npgsql.NpgsqlParameter("p_boolean", NpgsqlTypes.NpgsqlDbType.Boolean) { Value = check }, }; var query = Function.Boolean("public.return_boolean", param, this.Common.ConnectionString); Assert.Equal(!check, query); }
public async Task <bool> ActivateEmployeeById(Guid BusinessID, Guid employeeId) { var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID); var employeeID = new Npgsql.NpgsqlParameter("@theempid", employeeId); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activateemployeebyid(@thebusinessid,@theempid)", businessID, employeeID) .Select(e => new CheckStatusModel() { Status = e.Status }).FirstOrDefault()); return(checkStatusModel.Status); }
public async Task <bool> CheckDeductionID(Guid businessID, Guid deductionID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var deductionId = new Npgsql.NpgsqlParameter("@thedeductionid", deductionID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkdeductionid(@thebusinessid,@thedeductionid)", businessId, deductionId) .Select(e => new CheckStatusModel() { DeductionIDStatus = e.DeductionIDStatus }).FirstOrDefault()); return(checkStatusModel.DeductionIDStatus); }
public async Task <bool> ActivateBusinessUserById(Guid businessID, Guid businesUserID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var businesUserId = new Npgsql.NpgsqlParameter("@thebuid", businesUserID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activatebusinessuser(@thebusinessid,@thebuid) as status", businessId, businesUserId) .Select(e => new CheckStatusModel() { Status = e.Status }).FirstOrDefault()); return(checkStatusModel.Status); }
public async Task <bool> CheckIfEmployeeHasAlreaySaleryAssigned(Guid businessID, Guid employeeID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var employeeId = new Npgsql.NpgsqlParameter("@theempid", employeeID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkemployeeidhavingsaleryassigned(@thebusinessid,@theempid) as checkemployeeid", businessId, employeeId) .Select(e => new CheckStatusModel() { EmployeeIDStatus = e.EmployeeIDStatus }).FirstOrDefault()); return(checkStatusModel.EmployeeIDStatus); }
public async Task <bool> CheckIncrementID(Guid businessID, Guid incrementID) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkincrementid(@thebusinessid,@theincrementid) ", businessId, incrementId) .Select(e => new CheckStatusModel() { IncrementIDStatus = e.IncrementIDStatus }).FirstOrDefault()); return(checkStatusModel.IncrementIDStatus); }
/// <summary> /// get Update Sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="expression"></param> /// <returns></returns> public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class { (string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true); var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression); CreateUpdateBody(createUpdateBodyData, expression.Body); var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters var sqlColumns = (createUpdateBodyData.DatabaseType == DbServer.SQLServer) ? createUpdateBodyData.UpdateColumnsSql : createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", ""); var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}"; if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP")) { string tableAliasPrefix = "[" + tableAlias + "]."; resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}"; } if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql { resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0]; } var databaseType = SqlAdaptersMapping.GetDatabaseType(context); if (databaseType == DbServer.PostgreSQL) { resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery); var npgsqlParameters = new List <object>(); foreach (var param in sqlParameters) { var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value); string paramName = npgsqlParam.ParameterName.Replace("@", ""); var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType; if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) { npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb; } npgsqlParameters.Add(npgsqlParam); } sqlParameters = npgsqlParameters; } return(resultQuery, sqlParameters); }
public async Task <bool> AddEmployeeSalary(Guid businessID, SalaryModel salaryModel) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var employeeId = new Npgsql.NpgsqlParameter("@theempid", salaryModel.EmployeeID); var salary = new Npgsql.NpgsqlParameter("@thesalary", salaryModel.Salary); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from addsalary(@thebusinessid,@theempid," + "@thesalary)", businessId, employeeId, salary) .Select(e => new CheckStatusModel() { Status = e.Status }).FirstOrDefault()); return(checkStatusModel.Status); }
// FUTURE: Do not depend on Npgsql. Too npgsql specific. public static DbParameter AddJsonParameterWithValue(this DbCommand command, string parameterName, object value) { if (command == null) { throw new ArgumentNullException(nameof(command)); } var parameter = new Npgsql.NpgsqlParameter(parameterName, NpgsqlTypes.NpgsqlDbType.Jsonb) { Value = value ?? DBNull.Value }; command.Parameters.Add(parameter); return(parameter); }
public async Task <bool> UpdateEmployeeSalaryBySalaryID(Guid businessID, Guid SalaryID, UpdateSalaryModel updateSalaryModel) { var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID); var salaryId = new Npgsql.NpgsqlParameter("@thesalaryid", SalaryID); var salary = new Npgsql.NpgsqlParameter("@thesalary", updateSalaryModel.Salary); CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from updatesalarybyid(@thebusinessid,@thesalaryid," + "@thesalary) as salaryupdatastatus", businessId, salaryId, salary) .Select(e => new CheckStatusModel() { salaryUpdateStatus = e.salaryUpdateStatus }).FirstOrDefault()); return(checkStatusModel.salaryUpdateStatus); }
public IDbDataParameter CreateParameter() { var name = this.Name; switch (this.DbType.DatabaseServer) { case DatabaseServer.SqlServer: { var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.SqlServerDbType.Value); return p; } case DatabaseServer.Oracle: { var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.OracleServerDbType.Value); return p; } case DatabaseServer.MySql: { var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value); p.Direction = ParameterDirection.Input; p.Value = GetParameterValue(this.DbType.MySqlServerDbType.Value); return p; } case DatabaseServer.PostgreSql: { var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value); p.Direction = ParameterDirection.Input; //p.Value = GetParameterValue(this.MySqlDbType.Value); return p; } default: throw new InvalidOperationException(); } }