Database Design Guidelines

tables.png
table design

For DAOGenerator to work properly, a table should design as standalone table and no relationship should be enforced. Relationship only being enforced in the application. Please follow the following design guidelines:
  1. Should only have one primary key
  2. Table name should be in plural (i.e. Companies, Products, Peoples, Users and etc.)
  3. Multiple words column such as Price Per Unit should comprise "_" in between words (i.e. PRICE_PER_UNIT and etc.)

Download the following files and run the script in mySQL.

PAPASHOP_COMPANIES.sql
PAPASHOP_PRODUCTS.sql

Using DAOGenerator.exe

Before executing the generator, application configuration must be configured.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <appSettings>
    <!-- Choose CSharp or VisualBasic -->
    <add key="CodeToGenerate" value="CSharp"/>
    <add key="OutputPath" value="D:\Temp\POCO"/>
    <add key="Namespace" value="FooBar"/>
    <!-- Choose mysql or mssql -->
    <add key="DbProvider" value="mysql"/>
  </appSettings>
</configuration>


Information such as OutputPath and Namespace are very crucial. Once all this configured, execute DAOGenerator with the following parameters supplied.

C:\>DAOGenerator.exe 192.168.74.128 root P@ssw0rd PAPASHOP

DAOGenerator.exe will requires four parameters which are as following:
  1. IP Address of the server
  2. Username
  3. Password
  4. Database Name

Data Access Layer Generator 1.0.0.0
MySQL Edition
Connecting to 192.168.74.128
Information Schemas acquired. Generating source code...
Done.


DAOGenerator.exe will generate 3 types of classes which are:
  1. Definition class (to provide developer with column names of each tables)
  2. Ordinal class (to provide developer with ordinal column reference)
  3. POCO Object (i.e. Product.cs, Company.cs and etc)
  4. Provider class (to provide developer with CRUD functionality of each table)

Once generator generated the generated files, please copy the files to your solution. For example please refer to the following solution structures:

PapaShopProviderExampleSolution.png

from the screenshot, generated files, I have organized it under Database folder, and I have spawned another file Extension.cs. Since that generated POCO classes are partial class, therefore I can enforce "relationship" for each entities here.

namespace FooBar.PAPASHOP.Model
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;

    /// <summary>
    /// This is an extension class. Code Generator does not generate this.
    /// </summary>

    public partial class Company
    {
        public List<Product> Products { get; set; }
    }
}


The following are example on how to use the provider.

//initiating the provider with Connection String.
string connectionString = "server=192.168.74.128;user=root;password=P@ssw0rd;port=3306;database=PAPASHOP";
var objCompany = new CompanyProvider(connectionString);

//insert new record using the provider
var companyA = new Company();
companyA.Id = Guid.NewGuid().ToString();
companyA.Name = "Apple Inc";
companyA.Address = "Infinite Loop Street";
companyA.Phone = "123";
companyA.Email = "hello@apple.com";
companyA.InsertedAt = DateTime.Now;
companyA.InsertedBy = "faruq";
companyA.UpdatedAt = DateTime.Now;
companyA.UpdatedBy = "faruq";
objCompany.Insert(companyA);

//To get all companies
var allCompanies = objCompany.SelectAll();

//to get all companies with custom query
var selectCompanyA = objCompany.SelectAll("SELECT * FROM COMPANIES WHERE NAME LIKE @NAME", 
          new List<Parameter>() { new Parameter { Name = "@NAME", Value = "%Apple%" } });

//if you know the ID, i.e 2de581bd-053d-4ac1-8f82-f6d88e23cea5 for Microsoft
var companyM = objCompany.Select("2de581bd-053d-4ac1-8f82-f6d88e23cea5");

//to update
companyM.Address = "Seatle";
companyM.UpdatedAt = DateTime.Now;
companyM.UpdatedBy = "faruq";
objCompany.Update(companyM);

//to delete i.e Oracle cd09fe62-8964-410e-8e43-08fd88465e56
objCompany.Delete("cd09fe62-8964-410e-8e43-08fd88465e56");



Download the following example project for references:

PapaShopProviderExample.zip

Check the Manager folder for example of Manager class.

Last edited Sep 30, 2014 at 4:42 PM by alfaruq, version 5