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); }