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

• Download source files - 92.25 KB

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:

   C#

   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]

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

dpdg.exe -s < enter >

Class Diagram

API

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.

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

• 31^st 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.