Saturday 31 October 2015

SQL Server Stored Procedure Helper

Few years back, I had a project with very tight timeline and very clear and load objective of performance. We wanted to have optimal performance at database level which were not possible to achieve with any ORM like Entity Framework but due to time constraints, it was also difficult to complete project in time by writing thousands on stored procedure manually.

To standardize and speed up stored procedure writing, I developed a simple application SQL Server Stored Procedure Helper to create basic code for following CRUD operations in no time:
  • Create as Add
  • Read as Get
  • Update
  • Delete
  • and ReadAll as GetAll(additional)

User just have to perform following actions:
  • Run Application
  • Provide Connection String
  • Provide Author Name for Comment
  • Provide prefix for stored procedure name if required
  • Load Database
  • Generate Script
  • Execute Scripts on Database
We have following assumptions:
  • Every table must have Identity Column and that will be Primary key too.
  • For Add and Update operations may work without any change for full object.
  • For Delete operation user may have to handle calls to delete child data as per requirement.
  • For Get operation, user may get standard object for given identity/primary key.
  • For GetAll operation user may have to code joins as per requirements.
You can download full code from following CodePlex Repository: https://ijzsqlserverstoredprocedurehelper.codeplex.com