public bool SaveEmployeeRole(EmployeeRole modelRole, string connectionString)
{
int savedCount = 0;
try
{
if (!string.IsNullOrEmpty(modelRole.pEmployeeRoleName))
{
savedCount = NPGSqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, "insert into tblmstemployeerole(rolename,statusid,createdby,createddate) values('" + ManageQuote(modelRole.pEmployeeRoleName) + "'," + Convert.ToInt32(Status.Active) + "," + modelRole.pCreatedby + ",current_timestamp);");
}
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
}
}
return(savedCount > 0 ? true : false);
}
public void PersistentAcrossConnections()
{
var connSettings = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(PersistentAcrossConnections)
};
using var conn = OpenConnectionAndUnprepare(connSettings);
var processId = conn.ProcessID;
AssertNumPreparedStatements(conn, 0);
using (var cmd = new NpgsqlCommand("SELECT 1", conn))
cmd.Prepare();
var stmtName = GetPreparedStatements(conn).Single();
conn.Close();
conn.Open();
Assert.That(conn.ProcessID, Is.EqualTo(processId), "Unexpected connection received from the pool");
AssertNumPreparedStatements(conn, 1, "Prepared statement deallocated");
Assert.That(GetPreparedStatements(conn).Single(), Is.EqualTo(stmtName), "Prepared statement name changed unexpectedly");
// Rerun the test using the persistent prepared statement
using (var cmd = new NpgsqlCommand("SELECT 1", conn))
{
cmd.Prepare();
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1));
}
AssertNumPreparedStatements(conn, 1, "Prepared statement deallocated");
Assert.That(GetPreparedStatements(conn).Single(), Is.EqualTo(stmtName), "Prepared statement name changed unexpectedly");
NpgsqlConnection.ClearPool(conn);
}
public bool SaveChallanaEntry(ChallanaEntryDTO _ChallanaEntryDTO, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string query = "";
long ChallanaId = 0;
if (string.IsNullOrEmpty(Convert.ToString(_ChallanaEntryDTO.pChallanaNo)))
{
_ChallanaEntryDTO.pChallanaNo = NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY', 'CHALLANA', current_date)").ToString();
//_ChallanaEntryDTO.pChallanaNo =Convert.ToString(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "SELECT FN_GENERATENEXTID('CHALLANA ENTRY','CHALLANA',current_date);"));
}
if (string.IsNullOrEmpty(_ChallanaEntryDTO.ptypeofoperation.ToString()))
{
_ChallanaEntryDTO.ptypeofoperation = "CREATE";
}
if (_ChallanaEntryDTO.ptypeofoperation.ToString().ToUpper() == "CREATE")
{
query = "insert into challana_entry( challana_no, from_date, to_date, company_type, tds_percent,calc_tds,actual_tds,paid_Amount,companyid)values('" + _ChallanaEntryDTO.pChallanaNo + "', '" + FormatDate(_ChallanaEntryDTO.pFromDate.ToString()) + "', '" + FormatDate(_ChallanaEntryDTO.pToDate.ToString()) + "', '" + _ChallanaEntryDTO.pCompanyType + "','" + _ChallanaEntryDTO.pTdsType + "'," + _ChallanaEntryDTO.pTotalTdsAmount + "," + _ChallanaEntryDTO.pActualTotalTdsAmount + "," + _ChallanaEntryDTO.pTotalPaidAmount + "," + _ChallanaEntryDTO.pCompanyId + ") returning challana_id";
ChallanaId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, query));
}
if (_ChallanaEntryDTO._ChallanaEntryDetails != null)
{
for (int i = 0; i < _ChallanaEntryDTO._ChallanaEntryDetails.Count; i++)
{
sbinsert.Append("insert into challana_entry_details( challana_id, tds_voucher_id,parent_id,account_id,panno,amount,calc_tds,actual_tds,balance,paidamt,status)values (" + ChallanaId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsVoucherId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pParentId + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAccountId + ",'" + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPanNo + "'," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pActualTdsAmount + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pBalance + "," + _ChallanaEntryDTO._ChallanaEntryDetails[i].pPaidAmount + ",'true');");
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public void GlobalReset()
{
var myFactory = MapMyIntGlobally();
using var _ = CreateTempPool(ConnectionString, out var connectionString);
using (OpenConnection(connectionString)) {}
// We now have a connector in the pool with our custom mapping
NpgsqlConnection.GlobalTypeMapper.Reset();
using (var conn = OpenConnection(connectionString))
{
// Should be the pooled connector from before, but it should have picked up the reset
conn.ExecuteScalar("SELECT 1");
Assert.That(myFactory.Reads, Is.Zero);
// Now create a second *physical* connection to make sure it picks up the new mapping as well
using (var conn2 = OpenConnection(connectionString))
{
conn2.ExecuteScalar("SELECT 1");
Assert.That(myFactory.Reads, Is.Zero);
}
NpgsqlConnection.ClearPool(conn);
}
}
public override void Dispose()
{
switch (Enum.Parse <MixEnums.DatabaseProvider>(MixService.GetConfig <string>("DatabaseProvider")))
{
case 0:
{
SqlConnection.ClearPool((SqlConnection)RelationalDatabaseFacadeExtensions.GetDbConnection(this.get_Database()));
break;
}
case 1:
{
MySqlConnection.ClearPool((MySqlConnection)RelationalDatabaseFacadeExtensions.GetDbConnection(this.get_Database()));
break;
}
case 2:
{
NpgsqlConnection.ClearPool((NpgsqlConnection)RelationalDatabaseFacadeExtensions.GetDbConnection(this.get_Database()));
break;
}
}
this.Dispose();
return;
}
public void Across_close_open_different_connector()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(AutoPrepareTests) + '.' + nameof(Across_close_open_different_connector),
MaxAutoPrepare = 10,
AutoPrepareMinUsages = 2
}.ToString();
using var conn1 = new NpgsqlConnection(connString);
using var conn2 = new NpgsqlConnection(connString);
using var cmd = new NpgsqlCommand("SELECT 1", conn1);
conn1.Open();
cmd.ExecuteNonQuery(); cmd.ExecuteNonQuery();
Assert.That(cmd.IsPrepared, Is.True);
var processId = conn1.ProcessID;
conn1.Close();
conn2.Open();
conn1.Open();
Assert.That(conn1.ProcessID, Is.Not.EqualTo(processId));
Assert.That(cmd.IsPrepared, Is.False);
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1)); // Execute unprepared
cmd.Prepare();
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1));
NpgsqlConnection.ClearPool(conn1);
}
public void ReusePreparedStatement()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(PrepareTests) + '.' + nameof(ReusePreparedStatement)
}.ToString();
using (var conn1 = OpenConnection(connString))
{
var preparedStatement = "";
using (var cmd1 = new NpgsqlCommand("SELECT @p", conn1))
{
cmd1.Parameters.AddWithValue("p", 8);
cmd1.Prepare();
Assert.That(cmd1.IsPrepared, Is.True);
Assert.That(cmd1.ExecuteScalar(), Is.EqualTo(8));
preparedStatement = cmd1.Statements[0].PreparedStatement !.Name !;
}
using (var cmd2 = new NpgsqlCommand("SELECT @p", conn1))
{
cmd2.Parameters.AddWithValue("p", 8);
cmd2.Prepare();
Assert.That(cmd2.IsPrepared, Is.True);
Assert.That(cmd2.Statements[0].PreparedStatement !.Name, Is.EqualTo(preparedStatement));
Assert.That(cmd2.ExecuteScalar(), Is.EqualTo(8));
}
NpgsqlConnection.ClearPool(conn1);
}
}
public void RollbackOnClose()
{
// Npgsql 3.0.0 to 3.0.4 prepended a rollback for the next time the connector is used, as an optimization.
// This caused some issues (#927) and was removed.
// Clear connections in pool as we're going to need to reopen the same connection
var dummyConn = new NpgsqlConnection(ConnectionString);
NpgsqlConnection.ClearPool(dummyConn);
int processId;
using (var conn = OpenConnection())
{
processId = conn.Connector.BackendProcessId;
conn.BeginTransaction();
conn.ExecuteNonQuery("SELECT 1");
Assert.That(conn.Connector.TransactionStatus, Is.EqualTo(TransactionStatus.InTransactionBlock));
}
using (var conn = OpenConnection())
{
Assert.That(conn.Connector.BackendProcessId, Is.EqualTo(processId));
Assert.That(conn.Connector.TransactionStatus, Is.EqualTo(TransactionStatus.Idle));
}
}
public bool SaveRoleSubModule(RolesubmodulesDTO _RolesubmodulesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
_ = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, cmdText: "INSERT INTO tblmstmodules (modulename,moduledescription,parentmoduleid,parentmodulename,statusid,createdby,createddate) VALUES ('" + ManageQuote(_RolesubmodulesDTO.pSubmodulename).Trim() + "', '', " + _RolesubmodulesDTO.pModuleId + ", '" + ManageQuote(_RolesubmodulesDTO.pModulename).Trim() + "'," + Convert.ToInt32(Status.Active) + ", " + _RolesubmodulesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public void AcrossCloseOpenDifferentConnector()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(PrepareTests) + '.' + nameof(AcrossCloseOpenDifferentConnector)
}.ToString();
using (var conn1 = new NpgsqlConnection(connString))
using (var conn2 = new NpgsqlConnection(connString))
using (var cmd = new NpgsqlCommand("SELECT 1", conn1))
{
conn1.Open();
cmd.Prepare();
Assert.That(cmd.IsPrepared, Is.True);
var processId = conn1.ProcessID;
conn1.Close();
conn2.Open();
conn1.Open();
Assert.That(conn1.ProcessID, Is.Not.EqualTo(processId));
Assert.That(cmd.IsPrepared, Is.False);
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1)); // Execute unprepared
cmd.Prepare();
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(1));
NpgsqlConnection.ClearPool(conn1);
}
}
public bool SaveWorkGroupinRole(WorkGroupinrolesDTO _WorkGroupinrolesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstgroup (groupname,groupdescription,issystemgroup,statusid,createdby,createddate) VALUES ('" + ManageQuote(_WorkGroupinrolesDTO.pGroupname).Trim().ToUpper() + "', '', 'N', " + Convert.ToInt32(Status.Active) + ", " + _WorkGroupinrolesDTO.pCreatedby + ", current_timestamp); ");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public void LocalMappingDontLeak()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Pooling = false,
ApplicationName = nameof(LocalMappingDontLeak)
};
NpgsqlConnection.GlobalTypeMapper.MapComposite <Composite2>("composite");
try
{
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TYPE composite AS (bar int)");
conn.ReloadTypes();
Assert.That(conn.ExecuteScalar("SELECT '(8)'::composite"), Is.TypeOf <Composite2>());
conn.TypeMapper.MapComposite <Composite3>("composite");
Assert.That(conn.ExecuteScalar("SELECT '(8)'::composite"), Is.TypeOf <Composite3>());
}
using (var conn = OpenConnection(csb))
Assert.That(conn.ExecuteScalar("SELECT '(8)'::composite"), Is.TypeOf <Composite2>());
}
finally
{
NpgsqlConnection.GlobalTypeMapper.UnmapComposite <Composite2>("composite");
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("DROP TYPE IF EXISTS composite");
NpgsqlConnection.ClearPool(conn);
}
}
}
private static void WaitForExistsImplementation(NpgsqlConnection connection)
{
var retryCount = 0;
while (true)
{
try
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
NpgsqlConnection.ClearPool(connection);
connection.Open();
connection.Close();
return;
}
catch (PostgresException e)
{
if (++retryCount >= 30 ||
e.SqlState != "08001" && e.SqlState != "08000" && e.SqlState != "08006")
{
throw;
}
Thread.Sleep(100);
}
}
}
public List <LoanchargetypesConfigDTO> GetLoanWiseLoanPayin(string ConnectionString, long loanid, string applicanttype)
{
lstLoanchargesConfig = new List <LoanchargetypesConfigDTO>();
try
{
using (NpgsqlDataReader dr = NPGSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, "select distinct loanpayin from tblmstloanconfiguration where Loanid=" + loanid + " and applicanttype='" + applicanttype + "'"))
{
while (dr.Read())
{
LoanchargetypesConfigDTO objloanchargesConfig = new LoanchargetypesConfigDTO();
objloanchargesConfig.pLoanpayin = dr["loanpayin"].ToString();
lstLoanchargesConfig.Add(objloanchargesConfig);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(lstLoanchargesConfig);
}
public void TimeoutFromConnectionString()
{
Assert.That(NpgsqlConnector.MinimumInternalCommandTimeout, Is.Not.EqualTo(NpgsqlCommand.DefaultTimeout));
var timeout = NpgsqlConnector.MinimumInternalCommandTimeout;
int connId;
using (var conn = new NpgsqlConnection(ConnectionString + ";CommandTimeout=" + timeout))
{
var command = new NpgsqlCommand("SELECT 1", conn);
conn.Open();
Assert.That(command.CommandTimeout, Is.EqualTo(timeout));
command.CommandTimeout = 10;
command.ExecuteScalar();
Assert.That(command.CommandTimeout, Is.EqualTo(10));
connId = conn.ProcessID;
}
using (var conn = new NpgsqlConnection(ConnectionString + ";CommandTimeout=" + timeout))
{
conn.Open();
var command = CreateSleepCommand(conn, timeout + 2);
Assert.That(conn.ProcessID, Is.EqualTo(connId), "Got a different connection...");
Assert.That(command.CommandTimeout, Is.EqualTo(timeout));
Assert.That(() => command.ExecuteNonQuery(),
Throws.TypeOf <NpgsqlException>()
.With.Property("Code").EqualTo("57014")
);
Assert.That(ExecuteScalar("SHOW statement_timeout", conn), Is.EqualTo(timeout + "s"));
conn.Close();
NpgsqlConnection.ClearPool(conn);
}
}
public bool SavedisbursalLetter(DisbursmentLetterDTO _DisbursmentLetterDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
string StrsaveDisbursalletter = string.Empty;
StrsaveDisbursalletter = "INSERT INTO tabapplicationdisbursementletter(applicationid, vchapplicationid, transdate, disbursementlettersent,sentthrough, applicantname, loanname, loanamount, interesttype,rateofinterest, tenureofloan, tenuretype, downpayment, termsandconditions,statusid, createdby, createddate,dibursementamount,modeofpayment,installmentamount,voucherno) VALUES(" + _DisbursmentLetterDTO.pApplicationId + ", '" + ManageQuote(_DisbursmentLetterDTO.pVchapplicationID) + "', current_date, 'Y','','" + ManageQuote(_DisbursmentLetterDTO.pApplicantname) + "', '" + ManageQuote(_DisbursmentLetterDTO.pLoanname) + "', " + _DisbursmentLetterDTO.pApprovedloanamount + ", '" + ManageQuote(_DisbursmentLetterDTO.pInteresttype) + "', " + _DisbursmentLetterDTO.pInterestRate + ", " + _DisbursmentLetterDTO.pTenureofloan + ", '" + _DisbursmentLetterDTO.pLoanpayin + "', " + _DisbursmentLetterDTO.pDownpayment + ", '', " + Convert.ToInt32(Status.Active) + ", " + _DisbursmentLetterDTO.pCreatedby + ", current_timestamp," + _DisbursmentLetterDTO.pDisbursalamount + ",'" + ManageQuote(_DisbursmentLetterDTO.pModeofpayment) + "'," + _DisbursmentLetterDTO.pInstallmentamount + ",'" + ManageQuote(_DisbursmentLetterDTO.pVoucherno) + "'); ";
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, StrsaveDisbursalletter);
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public void ReleaseWaiterOnConnectionFailure()
{
var connectionString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
Port = 9999,
MaxPoolSize = 1
}.ToString();
try
{
var tasks = Enumerable.Range(0, 2).Select(i => Task.Run(async() =>
{
using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
})).ToArray();
try
{
Task.WaitAll(tasks);
}
catch (AggregateException e)
{
foreach (var inner in e.InnerExceptions)
{
Assert.That(inner, Is.TypeOf <NpgsqlException>());
}
return;
}
Assert.Fail();
}
finally
{
NpgsqlConnection.ClearPool(new NpgsqlConnection(connectionString));
}
}
public bool DeleteMemberType(int MemberID, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "update tblmstmembertypes set statusid= (select case when statusid=" + Convert.ToInt32(Status.Active) + " then " + Convert.ToInt32(Status.Inactive) + " when statusid=" + Convert.ToInt32(Status.Inactive) + " then " + Convert.ToInt32(Status.Active) + " end from tblmstmembertypes where membertypeid=" + MemberID + ") where membertypeid=" + MemberID + ";");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public void Cleanup()
{
foreach (var c in _cleanup)
{
NpgsqlConnection.ClearPool(c);
}
_cleanup.Clear();
}
public bool SaveRole(RolesDTO _RolesDTO, string Connectionstring)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(Connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (!string.IsNullOrEmpty(_RolesDTO.ptypeofoperation))
{
int RoleStatus = 2;
if (Convert.ToBoolean(_RolesDTO.pRoleactivestatus) == true)
{
RoleStatus = 1;
}
else
{
RoleStatus = 2;
}
if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "CREATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstgrouproles (roleid,groupid,groupname,rolename,roledescription,issystemrole,statusid,createdby,createddate) VALUES (" + _RolesDTO.pRoleID + "," + _RolesDTO.pGroupid + ", '" + ManageQuote(_RolesDTO.pGroupname) + "','" + ManageQuote(_RolesDTO.pRoledesignation).Trim().ToUpper() + "','', 'N', " + RoleStatus + ", " + _RolesDTO.pCreatedby + ", current_timestamp); ");
}
else if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "UPDATE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstgrouproles set roleid=" + _RolesDTO.pRoleID + ",groupid=" + _RolesDTO.pGroupid + "," +
"groupname= '" + ManageQuote(_RolesDTO.pGroupname) + "', rolename= '" + ManageQuote(_RolesDTO.pRoledesignation).Trim().ToUpper() + "',statusid=" + RoleStatus + ",modifiedby=" + _RolesDTO.pCreatedby + ",modifieddate=current_timestamp where recordid=" + _RolesDTO.pRecordId + ";");
}
else if (_RolesDTO.ptypeofoperation.Trim().ToUpper() == "DELETE")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "Update tblmstgrouproles set statusid=" + Convert.ToInt32(Status.Inactive) + ",modifiedby=" + _RolesDTO.pCreatedby + ",modifieddate=current_timestamp where recordid=" + _RolesDTO.pRecordId + ";");
}
trans.Commit();
Issaved = true;
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveLienreleaseentry(LienreleaseSaveDTO _LienreleasesaveDTO, string connectionstring)
{
bool IsSaved = false;
StringBuilder sbinsert = new StringBuilder();
string qry = string.Empty;
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (_LienreleasesaveDTO.ListLienreleaseDTO != null)
{
for (int i = 0; i < _LienreleasesaveDTO.ListLienreleaseDTO.Count; i++)
{
if (Convert.ToString(_LienreleasesaveDTO.ListLienreleaseDTO[i].ptypeofoperation) == "CREATE")
{
sbinsert.Append("insert into tbltranslienrealse(lienid,lienrealsedate,statusid,createdby,createddate)values(" + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ",'" + FormatDate(_LienreleasesaveDTO.ListLienreleaseDTO[i].pLienrealsedate.ToString()) + "'," + Convert.ToInt32(Status.Active) + ", " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pCreatedby + ", current_timestamp);");
sbinsert.Append("update tbltranslienentry set lienstatus='Y' where lienid= " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ";");
}
else
{
sbinsert.Append("Update tbltranslienrealse set lienid = " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ",lienrealsedate = '" + FormatDate(_LienreleasesaveDTO.ListLienreleaseDTO[i].pLienrealsedate.ToString()) + "',modifiedby = '" + _LienreleasesaveDTO.ListLienreleaseDTO[i].pCreatedby + "',modifieddate= current_timestamp where lienid = " + _LienreleasesaveDTO.ListLienreleaseDTO[i].pLienid + ";");
}
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
IsSaved = true;
}
catch (Exception Ex)
{
trans.Rollback();
throw Ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(IsSaved);
}
public bool UpdateMemberType(MemberTypeDTO MemberTypeDTO, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertype))
{
MemberTypeDTO.pmembertype = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertypecode))
{
MemberTypeDTO.pmembertypecode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pcompanycode))
{
MemberTypeDTO.pcompanycode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pbranchcode))
{
MemberTypeDTO.pbranchcode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pseries))
{
MemberTypeDTO.pseries = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembercode))
{
MemberTypeDTO.pmembercode = "";
}
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "update tblmstmembertypes set membertype='" + ManageQuote(MemberTypeDTO.pmembertype) + "',membertypecode='" + ManageQuote(MemberTypeDTO.pmembertypecode) + "',companycode='" + ManageQuote(MemberTypeDTO.pcompanycode) + "',branchcode='" + ManageQuote(MemberTypeDTO.pbranchcode) + "',series='" + ManageQuote(MemberTypeDTO.pseries) + "',serieslength=" + MemberTypeDTO.pserieslength + ",membercode='" + ManageQuote(MemberTypeDTO.pmembercode) + "',issharesissueapplicable=" + MemberTypeDTO.pissharesissueapplicable + ",isaccounttypecreationapplicable=" + MemberTypeDTO.pisaccounttypecreationapplicable + ",ismembershipfeeapplicable=" + MemberTypeDTO.pismembershipfeeapplicable + " where membertypeid=" + MemberTypeDTO.pmembertypeid + " and statusid=" + Convert.ToInt32(Status.Active) + ";");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public bool SaveMemberType(MemberTypeDTO MemberTypeDTO, string ConnectionString)
{
bool Issaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertype))
{
MemberTypeDTO.pmembertype = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembertypecode))
{
MemberTypeDTO.pmembertypecode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pcompanycode))
{
MemberTypeDTO.pcompanycode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pbranchcode))
{
MemberTypeDTO.pbranchcode = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pseries))
{
MemberTypeDTO.pseries = "";
}
if (string.IsNullOrEmpty(MemberTypeDTO.pmembercode))
{
MemberTypeDTO.pmembercode = "";
}
trans = con.BeginTransaction();
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "INSERT INTO tblmstmembertypes(membertype,membertypecode,companycode,branchcode,series,serieslength,membercode,issharesissueapplicable,statusid,createdby,createddate,isaccounttypecreationapplicable,ismembershipfeeapplicable) VALUES('" + ManageQuote(MemberTypeDTO.pmembertype) + "','" + ManageQuote(MemberTypeDTO.pmembertypecode) + "','" + ManageQuote(MemberTypeDTO.pcompanycode) + "','" + ManageQuote(MemberTypeDTO.pbranchcode) + "','" + ManageQuote(MemberTypeDTO.pseries) + "'," + MemberTypeDTO.pserieslength + ",'" + ManageQuote(MemberTypeDTO.pmembercode) + "'," + MemberTypeDTO.pissharesissueapplicable + "," + Convert.ToInt32(Status.Active) + "," + MemberTypeDTO.pCreatedby + ",CURRENT_TIMESTAMP," + MemberTypeDTO.pisaccounttypecreationapplicable + "," + MemberTypeDTO.pismembershipfeeapplicable + ");");
trans.Commit();
Issaved = true;
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public void ClearWithNoPool()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(ClearWithNoPool)
}.ToString();
using (var conn = CreateConnection(connString))
NpgsqlConnection.ClearPool(conn);
}
public bool SaveFdReceipt(FDReceiptDTO ObjFdReceiptDTO, string ConnectionString, out string OUTReceiptid)
{
bool Issaved = false;
StringBuilder sbInsert = new StringBuilder();
bool IsAccountSaved = false;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (Savegenaralreceipt(ObjFdReceiptDTO, trans, out OUTReceiptid))
{
IsAccountSaved = true;
}
else
{
trans.Rollback();
return(IsAccountSaved);
}
string Receiptno = OUTReceiptid;
if (string.IsNullOrEmpty(ObjFdReceiptDTO.pRecordid.ToString()) || ObjFdReceiptDTO.pRecordid == 0)
{
sbInsert.Append("INSERT INTO public.fd_receipt(fd_receiptt_date,member_id, fd_account_id,deposit_type, instalment_amount, received_amount, mode_of_receipt,receipt_no,narration,status)VALUES('" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "'," + ObjFdReceiptDTO.pMemberid + "," + ObjFdReceiptDTO.pFdaccountid + ",'" + ObjFdReceiptDTO.pDeposittype + "'," + ObjFdReceiptDTO.pInstalmentamount + "," + ObjFdReceiptDTO.pReceivedamount + ",'" + ObjFdReceiptDTO.pModeofreceipt + "','" + Receiptno + "','" + ObjFdReceiptDTO.pNarration + "'," + ObjFdReceiptDTO.pStatus + ");");
}
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
if (ObjFdReceiptDTO.pModeofreceipt == "CASH")
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, "SELECT FN_PROMOTOR_SALARY_JV(" + ObjFdReceiptDTO.pFdaccountid + ",'" + FormatDate(ObjFdReceiptDTO.pReceiptdate) + "')");
}
trans.Commit();
Issaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Issaved);
}
public string SaveshareApplication(ShareApplicationDTO ShareApplicationDTO, string ConnectionString, out long pShareaccountid)
{
bool IsSaved = false;
StringBuilder sbInsert = new StringBuilder();
Int64 Maxsharetovalue = 0;
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(ShareApplicationDTO.pshareapplicationid.ToString()) || ShareApplicationDTO.pshareapplicationid == 0)
{
Maxsharetovalue = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "select coalesce(max(distinctive_to),0) from Share_Account_creation;"));
ShareApplicationDTO.pdistinctivefrom = Maxsharetovalue + 1;
// ShareApplicationDTO.pdistinctiveto = (Maxsharetovalue + ShareApplicationDTO.pnoofsharesissued) - 1;
ShareApplicationDTO.pdistinctiveto = (ShareApplicationDTO.pdistinctivefrom + ShareApplicationDTO.pnoofsharesissued);
ShareApplicationDTO.pShareAccountNo = NPGSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "SELECT FN_GENERATENEXTID('SHARE CAPITAL','" + ShareApplicationDTO.psharename + "','" + ShareApplicationDTO.pTransdate + "')").ToString();
//ShareApplicationDTO.pShareAccountNo = "sd0001";
ShareApplicationDTO.pshareapplicationid = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into Share_Account_creation(share_Account_number,share_config_id,share_name,member_type_id,member_id,member_name,applicant_type,Trans_date,reference_no,face_value,no_of_shares_issued,distinctive_from,distinctive_to,total_amount,shares_issue_date,is_member_fee_applicable,Bond_print_status,statusid,createdby,createddate)values('" + ShareApplicationDTO.pShareAccountNo + "'," + ShareApplicationDTO.pshareconfigid + ",'" + ManageQuote(ShareApplicationDTO.psharename) + "'," + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + "," + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", '" + ManageQuote(ShareApplicationDTO.pmembername) + "','" + ManageQuote(ShareApplicationDTO.pApplicanttype) + "','" + FormatDate(ShareApplicationDTO.pTransdate) + "','" + ManageQuote(ShareApplicationDTO.preferenceno) + "'," + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + "," + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + "," + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + "," + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",'" + FormatDate(ShareApplicationDTO.pshareissuedate) + "','" + ShareApplicationDTO.pismemberfeeapplicable + "','N'," + Convert.ToInt32(Status.Active) + "," + ShareApplicationDTO.pCreatedby + ",current_timestamp) returning share_account_id;"));
}
else
{
sbInsert.Append("update Share_Account_creation set is_member_fee_applicable='" + ShareApplicationDTO.pismemberfeeapplicable + "', member_type_id=" + Convert.ToInt64(ShareApplicationDTO.pmembertypeid) + ", member_id=" + Convert.ToInt64(ShareApplicationDTO.pmemberid) + ", share_config_id=" + Convert.ToInt64(ShareApplicationDTO.pshareconfigid) + ",reference_no='" + ManageQuote(ShareApplicationDTO.preferenceno) + "', face_value=" + Convert.ToDecimal(ShareApplicationDTO.pfacevalue) + ",no_of_shares_issued=" + Convert.ToInt64(ShareApplicationDTO.pnoofsharesissued) + ", distinctive_from=" + Convert.ToInt64(ShareApplicationDTO.pdistinctivefrom) + ", distinctive_to=" + Convert.ToInt64(ShareApplicationDTO.pdistinctiveto) + ", total_amount=" + Convert.ToDecimal(ShareApplicationDTO.ptotalamount) + ",Share_Joining_date='" + FormatDate(ShareApplicationDTO.pTransdate) + "', shares_issue_date='" + FormatDate(ShareApplicationDTO.pshareissuedate) + "', Bond_print_status='N',modifiedby=" + ShareApplicationDTO.pCreatedby + ",modifieddate=current_timestamp where share_account_id=" + ShareApplicationDTO.pShareaccountid + " and share_account_number=" + ShareApplicationDTO.pShareAccountNo + ";");
if (!string.IsNullOrEmpty(sbInsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbInsert.ToString());
}
}
pShareaccountid = ShareApplicationDTO.pshareapplicationid;
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(Convert.ToString(ShareApplicationDTO.pShareAccountNo));
}
public bool SaveIdentificationdocuments(IdentificationDocumentsDTO obIdentificationDocuments, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (obIdentificationDocuments.identificationdocumentsList != null)
{
//if (obIdentificationDocuments.ptypeofoperation == "UPDATE")
//{
//if (obIdentificationDocuments.identificationdocumentsList.Count > 0)
//{
sbinsert.Append("delete from tblmstloanwisedocumentproofs where loanid=" + obIdentificationDocuments.pSavingConfigid + ";");
//}
//}
for (int i = 0; i < obIdentificationDocuments.identificationdocumentsList.Count; i++)
{
if (obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired == true || obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory == true)
{
sbinsert.Append("insert into tblmstloanwisedocumentproofs(loantypeid,loanid,contacttype,documentid,documentgroupid,isdocumentrequired,isdocumentmandatory,statusid,createdby,createddate) values(" + obIdentificationDocuments.pSavingConfigid + "," + obIdentificationDocuments.pSavingConfigid + ",'" + ManageQuote(obIdentificationDocuments.identificationdocumentsList[i].pContactType) + "'," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentId + "," + obIdentificationDocuments.identificationdocumentsList[i].pDocumentgroupId + ",'" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentRequired + "','" + obIdentificationDocuments.identificationdocumentsList[i].pDocumentMandatory + "'," + Convert.ToInt32(Status.Active) + "," + obIdentificationDocuments.identificationdocumentsList[i].pCreatedby + ",current_timestamp);");
}
}
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool SaveLoanFacility(LoanFacilityDTO objLoanFacility, string connectionstring)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionstring);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objLoanFacility.pEligiblepercentage.ToString()))
{
objLoanFacility.pEligiblepercentage = 0;
}
if (string.IsNullOrEmpty(objLoanFacility.pAgeperiod.ToString()))
{
objLoanFacility.pAgeperiod = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountLoansConfig where savingconfigid=" + objLoanFacility.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountLoansConfig(savingconfigid, savingaccname, isloanfacilityapplicable, eligiblepercentage, ageperiod, ageperiodtype, statusid, createdby, createddate)values(" + objLoanFacility.pSavingConfigid + ",'" + objLoanFacility.pSavingAccname + "'," + objLoanFacility.pIsloanfacilityapplicable + "," + objLoanFacility.pEligiblepercentage + "," + objLoanFacility.pAgeperiod + ",'" + objLoanFacility.pAgeperiodtype + "'," + Convert.ToInt32(Status.Active) + "," + objLoanFacility.pCreatedby + ",current_timestamp);");
}
if (count != 0)
{
sbinsert.Append("update tblmstSavingAccountLoansConfig set isloanfacilityapplicable=" + objLoanFacility.pIsloanfacilityapplicable + ", eligiblepercentage=" + objLoanFacility.pEligiblepercentage + ", ageperiod=" + objLoanFacility.pAgeperiod + ", ageperiodtype='" + objLoanFacility.pAgeperiodtype + "',modifiedby =" + objLoanFacility.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objLoanFacility.pSavingConfigid + ";");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
public bool saveGroupConfiguration(GroupCreationDTO groupDetails, string connectionString)
{
int groupSavedCount = 0;
long groupId;
StringBuilder saveGroupConfiguration = new StringBuilder();
try
{
con = new NpgsqlConnection(connectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
groupDetails.pMembersCount = Convert.ToString(groupDetails.pMembersCount) == string.Empty ? 0 : groupDetails.pMembersCount < 0 ? 0 : groupDetails.pMembersCount;
// Master Data
groupId = Convert.ToInt64(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "insert into tblmstgroupconfig(grouptype,groupname,groupcode,membercount,statusid,createdby,createddate) values('" + ManageQuote(groupDetails.pGroupType).Trim() + "','" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupCode).Trim() + "'," + groupDetails.pMembersCount + "," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp) returning groupid;"));
// groupId = 23;
// Child Details
if (groupDetails.pListGroupDetails != null && groupDetails.pListGroupDetails.Count > 0)
{
foreach (GroupCreation childDetails in groupDetails.pListGroupDetails)
{
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblgroupnamewisemembers where contactid=" + childDetails.pContactID + " and groupname='" + ManageQuote(groupDetails.pGroupName).Trim() + "';"));
if (count == 0)
{
saveGroupConfiguration.Append("insert into tblgroupnamewisemembers(groupid,groupname,groupno,contactid,contactreferenceid,contactname,contactnumber,grouproleid,roleingroup,statusid,createdby,createddate) values(" + groupId + ",'" + ManageQuote(groupDetails.pGroupName).Trim() + "','" + ManageQuote(groupDetails.pGroupNo).Trim() + "'," + childDetails.pContactID + ",'" + ManageQuote(childDetails.pContactRefId) + "','" + ManageQuote(childDetails.pContactName).Trim() + "','" + ManageQuote(childDetails.pContactNo).Trim() + "'," + (childDetails.pGrouproleID) + ",'" + ManageQuote(childDetails.pRoleInGroup).Trim() + "'," + Convert.ToInt32(Status.Active) + "," + groupDetails.pCreatedby + ",current_timestamp);");
}
}
}
if (Convert.ToString(saveGroupConfiguration) != string.Empty)
{
groupSavedCount = NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, saveGroupConfiguration.ToString());
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(groupSavedCount > 0 ? true : false);
}
public bool SaveReferralCommission(ReferralCommissionDTO objReferralCommission, string ConnectionString)
{
bool isSaved = false;
StringBuilder sbinsert = new StringBuilder();
try
{
con = new NpgsqlConnection(ConnectionString);
if (con.State != ConnectionState.Open)
{
con.Open();
}
trans = con.BeginTransaction();
if (string.IsNullOrEmpty(objReferralCommission.pCommissionValue.ToString()))
{
objReferralCommission.pCommissionValue = 0;
}
if (string.IsNullOrEmpty(objReferralCommission.pTdspercentage.ToString()))
{
objReferralCommission.pTdspercentage = 0;
}
int count = Convert.ToInt32(NPGSqlHelper.ExecuteScalar(trans, CommandType.Text, "select count(*) from tblmstSavingAccountConfigreferraldetails where savingconfigid=" + objReferralCommission.pSavingConfigid + " and statusid=" + Convert.ToInt32(Status.Active) + ""));
if (count != 0)
{
sbinsert.Append("Update tblmstSavingAccountConfigreferraldetails set isreferralcommissionapplicable=" + objReferralCommission.pIsreferralcommissionapplicable + ", referralcommissioncalfield='" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "',referralcommissiontype='" + objReferralCommission.pReferralcommissiontype + "' ,commissionValue=" + objReferralCommission.pCommissionValue + ",istdsapplicable=" + objReferralCommission.pIstdsapplicable + ",tdsaccountid='" + ManageQuote(objReferralCommission.pTdsaccountid) + "',tdssection='" + ManageQuote(objReferralCommission.ptdssection) + "',tdspercentage=" + objReferralCommission.pTdspercentage + ",modifiedby =" + objReferralCommission.pCreatedby + ",modifieddate=current_timestamp where savingconfigid =" + objReferralCommission.pSavingConfigid + ";");
}
if (count == 0)
{
sbinsert.Append("insert into tblmstSavingAccountConfigreferraldetails(savingconfigid ,savingaccname,isreferralcommissionapplicable,referralcommissioncalfield,referralcommissiontype ,commissionValue,istdsapplicable,tdsaccountid,tdssection,tdspercentage,statusid,createdby,createddate) values(" + objReferralCommission.pSavingConfigid + ",'" + objReferralCommission.pSavingAccname + "'," + objReferralCommission.pIsreferralcommissionapplicable + ",'" + ManageQuote(objReferralCommission.pReferralcommissioncalfield) + "','" + objReferralCommission.pReferralcommissiontype + "'," + objReferralCommission.pCommissionValue + "," + objReferralCommission.pIstdsapplicable + ",'" + ManageQuote(objReferralCommission.pTdsaccountid) + "','" + ManageQuote(objReferralCommission.ptdssection) + "'," + objReferralCommission.pTdspercentage + "," + Convert.ToInt32(Status.Active) + "," + objReferralCommission.pCreatedby + ",current_timestamp);");
}
if (!string.IsNullOrEmpty(sbinsert.ToString()))
{
NPGSqlHelper.ExecuteNonQuery(trans, CommandType.Text, sbinsert.ToString());
}
trans.Commit();
isSaved = true;
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Dispose();
con.Close();
con.ClearPool();
trans.Dispose();
}
}
return(isSaved);
}
