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
Download the dpdg.exe
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.
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())
{
DataTable employeeTable = dp.SelectEmployees(null, null);
foreach(DataRow row in employeeTable.Rows)
{
int employeeId = (int)row["EmployeeId"];
DataRow employeeRow = dp.FetchEmployees(employeeId);
}
DP.EmployeesEntity[] employees = dp.SelectEmployeesEntities(null, null);
foreach(DP.EmployeesEntity record in employees)
{
dp.FetchEmployeesEntity(record.EmployeeID);
}
int regionId = 9;
dp.InsertRegion(regionId, "china");
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();
dp.ReferFetchRegionByTerritoriesWithRegionID(regionId, null);
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
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:
- 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]
. - 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
Q: What is dp/dg?
A: dp/dg = Database process utility based on dp/dg generator.
Q: Where is the source code?
A: You can extract source code from dpdg.exe with command dpdg.exe /s.
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.