Click here to Skip to main content
13,004,397 members (80,647 online)
Click here to Skip to main content
Add your own
alternative version

Stats

36.9K views
112 downloads
12 bookmarked
Posted 31 Jul 2006

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]
ArgumentDescription
/c -c --connectionThe 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 --namespaceSpecifies 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 --sourceExtracts the source code into the current directory.
For example:

dpdg.exe -s < enter >

Class Diagram

Sample screenshot

API

DG Class NameDescription
P:ConnectionStringThe 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:ConnectionCurrent 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:TransactionCurrent 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:CommitCommits the current database transaction
M:RollbackRolls back the current transaction from a pending state
M:ExecuteExecutes an SQL statement against a connection object
M:QueryExecutes an SQL and returns the result data set
M:QueryTableExecutes an SQL/command and returns the result table
M:QueryRowExecutes an SQL/command and returns the first row of result table
M:QueryValueExecutes an SQL/command and returns the first column of first row's value
M:SchemaTablesQueries all tables in current database
M:SchemaColumnsQueries all columns in specified table
M:SchemaProceduresQueries all the store procedures in current database
M:TableCreateFrameCalls 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.
NameDescription
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]AnyPerform deletions at the specified Table by provided condition.
M:Fetch[Table]Fetch a row at specified Table with primary key.
M:Fetch[Table]EntityFetch 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]EntityQuery all [Table]Entity objects at specified Table with provided condition and sort order.
M:Insert[Table]Perform insertions at specified Table.
M:Insert[Table]EntityPerform insertions at specified Table.
M:Update[Table]Perform updates at specified Table.
M:Update[Table]EntityPerform 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]EntityA 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)

Share

About the Author

l_lugi
Web Developer
China China
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralHello Pin
geesy2021-Jun-07 4:09
membergeesy2021-Jun-07 4:09 
GeneralHello Pin
geesy2021-Jun-07 4:05
membergeesy2021-Jun-07 4:05 
GeneralHello Pin
geesy2021-Jun-07 4:02
membergeesy2021-Jun-07 4:02 
GeneralVery Good Pin
James Yang1-Aug-06 18:40
memberJames Yang1-Aug-06 18:40 
AnswerRe: Very Good Pin
l_lugi1-Aug-06 21:59
memberl_lugi1-Aug-06 21:59 
GeneralRe: Very Good Pin
James Yang1-Aug-06 23:53
memberJames Yang1-Aug-06 23:53 
GeneralRe: Very Good Pin
l_lugi2-Aug-06 21:50
memberl_lugi2-Aug-06 21:50 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170627.1 | Last Updated 31 Jul 2006
Article Copyright 2006 by l_lugi
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid