Click here to Skip to main content
15,867,869 members
Articles / Web Development / ASP.NET
Tip/Trick

Auto Generate Code (CRUD) for 3 Layered architecture

Rate me:
Please Sign up or sign in to vote.
4.54/5 (17 votes)
12 Dec 2016CPOL4 min read 49.1K   3.2K   49   16
Auto Generate Code (CRUD) for 3 Layered architecture (Entity, Data Access & Business Layer) with Stored Procedures based on table design.

Introduction

In this tip, we will try to understand how to generate code for tiered architecture automatically and generate stored procedures for CRUD operations automatically based on table schema. Through this tool, we will try to understand what are the different classes that are generated and what is the use of those classes and we will try to see how this tool helps us to save time at the time of coding. We will try to understand what kind of logic should go inside this tool.

This is a small tool for all my .NET friends to generate code automatically which saves time for coding, avoids redundant fields, follows similar names and data types, creates stored procedures for CRUD operations with similar fields which is similar to database table columns.

We can create project architecture easily within less time period and we can maintain all projects with unique structure.

Background

I found some issues or problems while creating projects, designing systems, creating stored projects.

  • Dissimilarity with field names and datatypes in Tables Columns, Procedures, Data Layers, Presentation Layers, etc.
  • No Uniform structure for project architecture, different developers follow different structures for coding within the same project.
  • Redundant fields, methods, functions and stored procedures to perform operation because of dissimilar naming conventions.
  • Consuming lot of time for coding to perform the same job like creating similar kind of logic and operations.

To solve the above problems, I have created this simple tool for code generation which is helpful and useful to programmers especially.

Using the Code

1. DB Mapping

This is DB Mapping window to select your SQL Servers from local machine or network, select database from that selected server, select tables from the selected database, select various conditions to generate the code based on user choice.

This is DB Mapping page to select your sql servers from local machine or from network, select database, select tables, select various operation for code operations

Step 1: The user can enter the server name or load server names from local machine or from network by clicking button S in server details section. Depending on server selection, enter the corresponding user id and password and click button load databases to load all the databases in that server.

Image 2

Step 2: Select database from database details section, click on button Load Table to load tables.

Image 3

Step 3: Select required tables for code generation from select table sections click on button Load Selected.

Image 4

Step 4: Select choose option either Auto or Manually, Other Class either Same or Common, Property Class either Same or Common.

Image 5

Step 5: Enter Project name without any spaces and click on button Add Class Names to Add/Edit Class information in grid.

Image 6

Step 6: Based on the user preference, the Grid is loaded, i.e.:

Image 7

Note

Choose option section: If Auto is selected, all check boxes are selected in Data Grid. If Manually is selected, all check boxes are unselected in Data Grid, then user can select required options from Data Grid.

Other Class section: If Same is selected, the OClassName will be same as TableName, i.e., all classes in business layer and data access layer will be generated separately. If Common is selected, all classes in business layer and data access layer will be generated in a single class.

Property Class section: If Same is selected, the Class Name will be same as TableName, i.e., all property classes in data layer will be generated separately. If Common is selected, all property classes in data layer will be generated in single class.

2. DB Query

This DB Query window will generate stored procedures based on the options selected by user. You can execute these queries from here to generate stored procedure in your selected database.

This based generates stored procedures based on the selected options, you can execute these queries from here to stored procedure in your selected database.

3. DB Base Classes

This DB Base Classes window will generate base classes for the project with predefined structure. You can add your own logic inside these base classes.

Image 9

4. DB Classes

This DB Base Classes window will generate base classes for the project with predefined structure. You can add your own logic inside these base classes.

Image 10

Points of Interest

While creating this tool, I learnt these interesting things:

  • How to find database servers from network
  • How to handle data grid with controls
  • How to create and use user defined components
  • How to handle different panels
  • How to dock controls in the window

History

  • 10th June, 2014: Initial version

I'll keep updates on this tool for any changes or improvements made.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Technical Lead with 13+ years of experience in requirement, analysis, design, development and execution of software applications using Microsoft Technologies (C#.NET, ASP.NET, SQL Server, VB.NET, ADO.NET, WCF, LINQ, MVC, WEB API, Windows Services).

Comments and Discussions

 
QuestionSource Code Please Pin
Ragnarök Tarn11-Apr-21 7:18
Ragnarök Tarn11-Apr-21 7:18 
QuestionExcellent post, but Lacking source code. Pin
DumpsterJuice15-Dec-16 7:02
DumpsterJuice15-Dec-16 7:02 
QuestionGood one Suresh ! Pin
Praneet Nadkar14-Dec-16 1:36
Praneet Nadkar14-Dec-16 1:36 
QuestionWhere is the code? Pin
DumpsterJuice13-Dec-16 9:04
DumpsterJuice13-Dec-16 9:04 
QuestionAwesome dude Pin
Member 1281096812-Dec-16 15:41
Member 1281096812-Dec-16 15:41 
GeneralGreate Pin
Aladár Horváth14-Aug-15 0:42
professionalAladár Horváth14-Aug-15 0:42 
GeneralSimilar type of application i found at below link with more options Pin
Member 301549210-Jul-14 1:05
Member 301549210-Jul-14 1:05 
QuestionMy Vote is 5 Pin
Ram Nunna13-Jun-14 19:04
professionalRam Nunna13-Jun-14 19:04 
GeneralNice one !!! Pin
PrashanthJagannath12-Jun-14 19:27
PrashanthJagannath12-Jun-14 19:27 
GeneralRe: Nice one !!! Pin
Suresh Dasari's13-Jun-14 8:50
Suresh Dasari's13-Jun-14 8:50 
Questionwhere is the source? Pin
Thava Rajan11-Jun-14 3:13
professionalThava Rajan11-Jun-14 3:13 
AnswerRe: where is the source? Pin
Suresh Dasari's13-Jun-14 8:49
Suresh Dasari's13-Jun-14 8:49 
GeneralMy vote of 2 Pin
Kornfeld Eliyahu Peter11-Jun-14 2:57
professionalKornfeld Eliyahu Peter11-Jun-14 2:57 
GeneralRe: My vote of 2 Pin
Suresh Dasari's13-Jun-14 8:45
Suresh Dasari's13-Jun-14 8:45 
GeneralMy vote of 3 Pin
Klaus Luedenscheidt10-Jun-14 19:18
Klaus Luedenscheidt10-Jun-14 19:18 
GeneralRe: My vote of 3 Pin
Suresh Dasari's13-Jun-14 8:54
Suresh Dasari's13-Jun-14 8:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.