Skip to main content

Connection strings

Damsy ERP reads database connectivity from Web.config connection strings. All DAL operations flow through MyConnectionString unless code explicitly overrides the static BaseHelper.ConnectionString property.

Purpose​

Configure the correct SQL Server catalog before first run, understand legacy connection names used by Crystal Reports, and know how the DAL resolves connections at runtime.

When to update connection strings​

  • Pointing local development at your SQL Server instance
  • Creating a new blank database (DB_DAMSYERP or custom name)
  • Publishing to IIS with a production catalog
  • Aligning Crystal report subreports that reference legacy connection names

Connection string names​

NameUsed byRole
MyConnectionStringDAL / BaseHelperPrimary — all stored procedure calls
ZDBERP_ZPILOTConnectionStringCrystal Reports / legacyLegacy alias — often same DB as primary
ZDBERP_PANZERConnectionStringCrystal Reports / legacyLegacy alias — often same DB as primary
info

Application code paths use MyConnectionString exclusively via BaseHelper.ResolveConnectionString(). The ZDBERP_* entries exist for Crystal Reports and historical tooling — keep them synchronized with the primary database to avoid report runtime connection failures.

Primary connection — MyConnectionString​

Example structure (update server and catalog for your environment):

<connectionStrings>
  <add name="MyConnectionString"
       connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=DB_DAMSYERP;Integrated Security=True;Encrypt=False;TrustServerCertificate=True;Connection Timeout=30;"
       providerName="System.Data.SqlClient"/>
</connectionStrings>
ParameterGuidance
Data SourceYour instance — e.g., localhost\SQLEXPRESS, (localdb)\MSSQLLocalDB, or SERVERNAME
Initial CatalogERP database name with full schema applied
Integrated Security=TrueWindows authentication (typical for dev)
Encrypt=FalseCommon for local Express instances; use True with proper certs in production
Connection Timeout=30Seconds before connection attempt fails

For SQL authentication, replace integrated security with User ID and Password — store production credentials on the server, not in Git.

How BaseHelper resolves connections​

Implementation in DAL/BaseHelper.cs:

public static string ResolveConnectionString()
{
    if (!string.IsNullOrWhiteSpace(ConnectionString))
        return ConnectionString;

    var settings = ConfigurationManager.ConnectionStrings["MyConnectionString"];
    if (settings == null || string.IsNullOrWhiteSpace(settings.ConnectionString))
        throw new ConfigurationErrorsException(
            "Connection string 'MyConnectionString' is missing or empty in Web.config.");

    ConnectionString = settings.ConnectionString;
    return ConnectionString;
}

The static cache means the first resolved string is reused for the app domain lifetime. Changing Web.config triggers an app restart under IIS/IIS Express, which refreshes the cache.

Legacy Crystal connection strings​

Reports created against older database aliases may embed ZDBERP_ZPILOTConnectionString or ZDBERP_PANZERConnectionString. Define all three names in Web.config pointing at the same catalog unless you genuinely use separate databases:

<add name="ZDBERP_ZPILOTConnectionString" connectionString="..." providerName="System.Data.SqlClient"/>
<add name="ZDBERP_PANZERConnectionString" connectionString="..." providerName="System.Data.SqlClient"/>

Script database-backup/scripts-blank-db/05-update-connection-string-NOTES.sql reminds you to update all three names together when migrating to a new database.

Blank database workflow​

Creating a usable ERP database is a three-phase process:

PhaseActionOutput
1. Create catalogRun 01-create-blank-database.sqlEmpty DB_DAMSYERP
2. Apply schemaExport from working DB (~185 tables, ~887 SPs)Full object model
3. Seed dataRun 04-seed-minimal.sqlCompany, admin user, module flags

Then update MyConnectionString (and legacy names) to Initial Catalog=DB_DAMSYERP.

warning

Schema is not fully versioned in Git. You must export it from a working local database using the provided PowerShell tooling (Export-SchemaOnly-FromLocal.ps1, Clean-SchemaScript.ps1) documented in database-backup/NEW-BLANK-DATABASE-SETUP.md.

Verify connectivity​

After updating Web.config:

  1. Start the app (Running locally)
  2. Open /Login/Index — company dropdown calls GetCompany
  3. If dropdown populates, connectivity and core SPs are present
  4. Sign in with admin / Admin@123 / Damsy Technologies

Optional SQL check:

SELECT DB_NAME() AS CurrentDatabase;
SELECT COUNT(*) AS TableCount FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

Expect on the order of ~185 tables after schema apply.

Developer notes​

Multiple environments​

Maintain separate catalogs (e.g., DB_DAMSYERP_DEV, DB_DAMSYERP_STAGING) and swap only connection strings — never mix seed/production data in one catalog during testing.

Connection pooling​

ADO.NET pools connections automatically. Long-running report requests use extended command timeout (120 seconds default in BaseHelper).

Troubleshooting​

ErrorLikely cause
ConfigurationErrorsException for MyConnectionStringKey missing or empty in Web.config
Login timeoutSQL Server stopped, wrong instance name, firewall
Login succeeds, reports failZDBERP_* strings point at wrong catalog
Invalid object nameSchema not applied to target database
danger

Never commit production SQL credentials to source control. Use IIS environment-specific configuration or secret stores on the server.

Common mistakes​

MistakeResult
Updating only MyConnectionStringDAL works; Crystal reports fail
Pointing at empty catalogMissing SP exceptions on first page load
Wrong instance name after SQL reinstallConnection timeout
Forgetting to restart app after Web.config editStale cached connection string (rare under IIS Express)

Next steps​

With connection strings configured and schema applied, run the app and complete your first login using Running locally. Explore modules in Features.