Database operations

Damsy ERP is database-centric: business rules, validations, and most reads/writes execute through SQL Server stored procedures. The Git repository contains application code and operational scripts — not a full schema dump.

Introduction

Every module — Payroll, Bills, Stock, Registration, ESS, and Administration — ultimately calls procedures such as GetLoginUser, InsertUpdateEmployee, or module-specific Get* / InsertUpdate* routines via the DAL BaseHelper class. Tables hold master and transactional data scoped by CompID (company) and often BranchCode.

Purpose

Give operators and developers a clear model of where data lives, how the app connects, and what must be maintained outside source control when provisioning new environments.

When to use

• Planning a new SQL Server instance or VPS database
• Understanding why Git clone alone is insufficient to run the ERP
• Deciding between restoring a backup vs blank database setup
• Troubleshooting SP or permission errors after deploy

How it works

Connection string

Application code resolves MyConnectionString from deployed Web.config (DAL/BaseHelper.cs). Other connection names exist for legacy Crystal scenarios; routine DAL work uses MyConnectionString only.

Schema ownership

Location	Contents
SQL Server	Full schema: tables, views, procedures, functions
Git repo	Seed scripts, blank-DB tooling, backup notes — not full CREATE scripts
Working local DB	Typical export source (e.g. DB_GLOBALSECURITY) for schema-only scripts

Approximate scale (verify on your instance with 02-verify-blank-database.sql):

Object type	Approximate count
User tables	~185 (seed docs reference ~368 after full export — counts vary by export)
Stored procedures	~887
Views	Varies

info

Treat counts as order-of-magnitude. Always run verification queries on the target database after schema apply.

Operational overview

Environment types

Environment	Typical approach
Developer laptop	Attach or restore full dev DB; or blank DB + schema export
Staging VPS	Schema export + minimal seed; optional menu copy from dev
Production	Backup/restore or controlled migration; never seed-only

Backup strategy

1. Full backups on a schedule (nightly minimum for production).
2. Before schema changes — manual full backup + test restore.
3. Store backups under a path such as C:\DBBackup\ on the VPS (match your org policy).

Security model on IIS

When using Integrated Security, SQL sees the app as:

IIS APPPOOL\<ApplicationPoolName>

Grant db_datareader, db_datawriter, and EXECUTE on procedures (often db_owner on small single-app VPS installs). See Deploy to IIS.

Key tables (conceptual)

Area	Examples
Company / auth	CompanyMaster, UserAccounts, CompanyUser, UserRole, BranchMaster
Permissions	MenuMaster, UserPermissions
Payroll	Employee, attendance, salary process tables
Billing	Invoice, IRN, GST-related tables
Stock	Item master, requisition, PO, issue

Exact column lists live in SSMS — not duplicated here.

Step-by-step — New database (high level)

1. Create empty database DB_DAMSYERP — Blank database setup.
2. Export schema only from a working database; clean and apply.
3. Run 02-verify-blank-database.sql — table and procedure counts > 0.
4. Run 04-seed-minimal.sql — Seed scripts.
5. Update server Web.config → MyConnectionString.
6. Recycle IIS app pool; test login.

Examples

Example — Verify object counts

USE [DB_DAMSYERP];
SELECT
  (SELECT COUNT(*) FROM sys.tables WHERE is_ms_shipped = 0) AS UserTableCount,
  (SELECT COUNT(*) FROM sys.procedures WHERE is_ms_shipped = 0) AS UserProcedureCount;

Example — Test procedure reachability

EXEC dbo.GetCompany;

Should return company rows after seed (at least CompID 1).

Example — Find procedures by prefix

SELECT name FROM sys.procedures
WHERE name LIKE 'Get%' OR name LIKE 'InsertUpdate%'
ORDER BY name;

Related pages

• Blank database setup
• Seed scripts
• Stored procedures
• Connection strings
• Deploy to IIS

Important notes

• Schema not in Git is intentional for repo size and drift management — maintain a controlled export process from a known-good database.
• Minimal seed does not populate all reference data — menus may need MenuMaster / UserPermissions copied from dev.
• Multi-company data is partitioned logically by CompID; procedures enforce company context using session-fed parameters from the UI.
• Version tables (SoftwareVersions, SoftwareVersion) affect login page version checks — included in minimal seed.

Common mistakes

Mistake	Result
Running seed before schema	INSERT failures — tables missing
Assuming EF migrations exist	There are none — manual SQL ops
SQL auth user without EXECUTE on SPs	Random module failures
Pointing app at empty DB_DAMSYERP	Login SP errors immediately
Restoring laptop backup paths on VPS	Orphan .mdf paths — use cleaned schema scripts

Next steps

1. Follow Blank database setup for a fresh DB_DAMSYERP.
2. Apply Seed scripts and test admin login.
3. Copy optional menu/permission data from a dev database if navigation errors appear.
4. Review Troubleshooting for SQL-related symptoms.