Click here to Skip to main content
Click here to Skip to main content

dp/dg -- A Lazy Man's Database Process Utility

, 31 Jul 2006
Rate this:
Please Sign up or sign in to vote.
A tool that generates database process utility code

Life is too short to write 'select ... from'

---- lugi

Introduction

I don't remember how many times I have seen programmers write 'select field1, field2, field3... from ...', and it really annoyed me that not only does the programmer write the messy code, but also sometimes she or he does not write it right. Moreover, when the database design was changed, you cannot know whether the existing code is right or not, so many bugs were buried under the deep SQL string that the software was hardly bug free. I know that there exists NHibernate, and even LINQ, BLINQ, but all these are not quite convenient, and LINQ is still in the beta version, so I wrote this tool to make things simple.

This tool runs only under .NET 2.0.

Example

  1. Download the dpdg.exe

  2. Generate a dp.dll by invoking dpdg.exe in the command line:

    dpdg.exe -c "Data Source=localhost;Initial Catalog=Northwind;
        Integrated Security=True"

    A dp.dll will be generated under the current directory.

  3. Reference the dp.dll in your project, and use the DP class to process the data (Northwind).
    By default the DP will use the connection string specified in the app.config file. Here is a code example:

    using(DP dp = new DP())
    {
        // select all records from Employees table
        DataTable employeeTable = dp.SelectEmployees(null, null);
        foreach(DataRow row in employeeTable.Rows)
        {
            int employeeId = (int)row["EmployeeId"];
            DataRow employeeRow = dp.FetchEmployees(employeeId);
        }
    
        // select Employees records into EmployeesEntity array
        DP.EmployeesEntity[] employees = dp.SelectEmployeesEntities(null, null);
        foreach(DP.EmployeesEntity record in employees)
        {
            dp.FetchEmployeesEntity(record.EmployeeID);
        }
    
        // add a region
        int regionId = 9;
        dp.InsertRegion(regionId, "china");
    
        // add territories
        int territoryId = regionId * 100000;
        dp.InsertTerritories((territoryId + 1).ToString(), "shanghai", regionId);
        dp.InsertTerritories((territoryId + 2).ToString(), "beijing", regionId);
        dp.InsertTerritories((territoryId + 3).ToString(), "guangzhou", regionId);
        dp.Commit();
    
        // fetch region and territories by regionId
        dp.ReferFetchRegionByTerritoriesWithRegionID(regionId, null);
    
        // delete the inserted region and territories
        dp.DeleteTerritories((territoryId + 1).ToString());
        dp.DeleteTerritories((territoryId + 2).ToString());
        dp.DeleteTerritories((territoryId + 3).ToString());
        dp.DeleteRegion(regionId);
        dp.Commit();
    }

Usage

dpdg.exe [/c connection] [/n namespace] [/h] [/s]
Argument Description
/c -c --connection The connection string - currently, only SqlClient's connection string and OleDb connection string are supported.
In the future we will support Oracle connection string.
For example:

dpdg.exe -c "Data Source=localhost;Initial Catalog=Northwind;
    Integrated Security=True" < enter >

The above will generate a DP.dll, with a DP class full of generated functions to process the Northwind database.

/n -n --namespace Specifies the generated DP class's namespace, if not specified, a default namespace Leaf will be used.
/h -h --help Shows the help information.
/s -s --source Extracts the source code into the current directory.
For example:

dpdg.exe -s < enter >

Class Diagram

Sample screenshot

API

DG Class Name Description
P:ConnectionString The connection string the DG will use to connect to the database. If not specified, the DG will find the default connection string from app.config file.
P:Connection Current database connection, the connection will be automatically opened when a database process method is called, and will be closed when DG.Commit method or DG.Rollback method is called.
P:Transaction Current database transaction - the transaction will be automatically opened and BeginTransaction when a process method is called, and will be committed and closed when DG.Commit or DG.Rollback method was called.
M:Commit Commits the current database transaction
M:Rollback Rolls back the current transaction from a pending state
M:Execute Executes an SQL statement against a connection object
M:Query Executes an SQL and returns the result data set
M:QueryTable Executes an SQL/command and returns the result table
M:QueryRow Executes an SQL/command and returns the first row of result table
M:QueryValue Executes an SQL/command and returns the first column of first row's value
M:SchemaTables Queries all tables in current database
M:SchemaColumns Queries all columns in specified table
M:SchemaProcedures Queries all the store procedures in current database
M:TableCreateFrame Calls CreateFrame[Table]() method in DP class with reflection.
M:TableFetch(Command) Calls Fetch[Table](command) method in DP class with reflection.
M:TableSelect(Command) Calls Select[Table](command) method in DP class with reflection.
M:TableInsert(Command) Calls Insert[Table](command) method in DP class with reflection.
M:TableDelete(Command) Calls Delete[Table](command) method in DP class with reflection.
M:TableDeleteAny(Command) Calls Delete[Table]Any(command) method in DP class with reflection.
M:TableUpdate(Command) Calls Update[Table](command) method in DP class with reflection.

The DP class inherits from DG in the method series.

Patterns you should know are as follows:

  1. For any method that actually performs the action, there exists a method that returns a command to perform the action. So if you see a method M:Insert[xxxx], there must exist a method M:Insert[xxxx]Command, that returns a command to Insert[xxxx].
  2. For any method that can apply to one Table/Index/ForeignKey(FK), it can apply to any Table/Index/ForeignKey. So if you see a method M:Delete[Table]By[Index], there must exist methods to Delete Any Table By Any Index of that Table.
Name Description
M:CreateFrame[Table] Create a new DataTable frame without any record.
M:Delete[Table] Perform deletions at the specified Table by primary key
M:Delete[Table]Any Perform deletions at the specified Table by provided condition.
M:Fetch[Table] Fetch a row at specified Table with primary key.
M:Fetch[Table]Entity Fetch a [Table]Entity object at specified Table with primary key.
M:Select[Table] Query at specified Table with provided condition and sort order.
M:Select[Table]Entity Query all [Table]Entity objects at specified Table with provided condition and sort order.
M:Insert[Table] Perform insertions at specified Table.
M:Insert[Table]Entity Perform insertions at specified Table.
M:Update[Table] Perform updates at specified Table.
M:Update[Table]Entity Perform updates at specified Table.
M:IndexDelete[Table]By[Index] Perform deletions at the specified Table by fields that are defined in specified Index.
M:IndexSelect[Table]By[Index] Query at specified Table by fields that are defined in specified Index.
M:ReferDelete[MasterTable]By[DetailTable]With[ForeignKey] Delete the record in the MasterTable by primary key value, and delete any records in the DetailTable by ForeignKey value.
M:ReferFetch[MasterTable]By[DetailTable]With[ForeignKey] Fetch the record in the MasterTable by primary key value, and fetch any records in the DetailTable by ForeignKey value.
M:[StoreProcedure] Execute the StoreProcedure.
C:[Table]Entity A simple entity class that's corresponding to the Table.

FAQ

  1. Q: What is dp/dg?
    A: dp/dg = Database process utility based on dp/dg generator.

  2. Q: Where is the source code?
    A: You can extract source code from dpdg.exe with command dpdg.exe /s.

  3. Q: What database does dp/dg support?
    A: Currently it supports Microsoft SQL Server(Version > 7.0) database with SqlClient and Access database with OleDb. In the future, Oracle database will be considered.

History

  • 31st July, 2006: Initial post

About lugi

lugi is a software developer working in China. He has over 8 years of experience and his interests include .NET, VB, C#, MIS, ERP. You can visit his blog.

License

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

About the Author

l_lugi
Web Developer
China China
No Biography provided

Comments and Discussions

 
GeneralHello Pinmembergeesy2021-Jun-07 4:09 
GeneralHello Pinmembergeesy2021-Jun-07 4:05 
GeneralHello Pinmembergeesy2021-Jun-07 4:02 
GeneralVery Good PinmemberJames Yang1-Aug-06 18:40 
AnswerRe: Very Good Pinmemberl_lugi1-Aug-06 21:59 
GeneralRe: Very Good PinmemberJames Yang1-Aug-06 23:53 
GeneralRe: Very Good Pinmemberl_lugi2-Aug-06 21:50 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 31 Jul 2006
Article Copyright 2006 by l_lugi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid