今天我儿子和我过生日，共享快乐。顺便贡献多年写的delphi数据库封装原代码。 ( 积分: 8 )

好东西，谢谢楼主，祝楼主全家幸福

这是查询所有数据的方法
procedure TForm1.cmdQueryAllClick(Sender: TObject);
var
etyRule : TEtyRule;
listRule : TEtyList;
begin
//set rule
etyRule := TEtyRule.Create;
etyRule.SetAttributeValue(FD_OPERATION, RULE_ORDERBY);
//这里FD_USER_NAME的值是EtyUser.xml文件的<EntityFieldName>
etyRule.SetAttributeValue(FD_ENTITY_FIELD_NAME, FD_USER_NAME);

listRule := TEtyList.Create(TEtyRule.Create);
listRule.AddEntity(etyRule);

//load all users from database
m_CtrSample.LoadEntityList(m_listUser, nil, listRule);

RefreshGrid;
end;

这是删除记录的方法，没有设置条件
procedure TForm1.cmdDelete1Click(Sender: TObject);
var
etyUser : IEntity;
begin
if txtUserName.Text = '' then
begin
Application.MessageBox('Please select one row', 'Error', MB_OK);
Exit;
end;

// delete record
etyUser := TEtyUser.Create;

//这里没有条件参数, 所以必须给etyUser的PK赋值
etyUser.SetAttributeValue(FD_USER_NAME, txtUserName.Text);
if not m_CtrSample.DeleteEntity(@etyUser) then
begin
Application.MessageBox('TDBGW.DeleteEntity', 'Error', MB_OK);
Exit;
end;

//refresh
cmdQueryAllClick(Sender);
end;

这是删除记录的方法，设置了条件
procedure TForm1.cmdDelete2Click(Sender: TObject);
var
etyCondition : TEtyCondition;
listCondition : TEtyList;
etyUser : IEntity;
begin
//get user name condition
if txtUserName.Text = '' then
begin
Application.MessageBox('please select one row', 'Error');
exit;
end;

etyCondition := TEtyCondition.Create;
//这里FD_USER_NAME的值是EtyUser.xml文件的<EntityFieldName>
etyCondition.SetAttributeValue(FD_ENTITY_FIELD_NAME, FD_USER_NAME);
etyCondition.SetAttributeValue(FD_OPERATION, '=');
etyCondition.SetAttributeValue(FD_VALUE, txtUserName.Text);

listCondition := TEtyList.Create(TEtyCondition.Create);
listCondition.AddEntity(etyCondition);

//delete
etyUser := TEtyUser.Create;
m_CtrSample.DeleteEntity(@etyUser, listCondition);

//refresh
cmdQueryAllClick(Sender);
end;

这是修改记录的方法，没有设置条件
procedure TForm1.cmdUpdateAddress1Click(Sender: TObject);
var
etyUser : IEntity;
begin
//get user name condition
if txtUserName.Text = '' then
begin
Application.MessageBox('please select one row', 'Error');
exit;
end;

etyUser := TEtyUser.Create;

//这里没有条件参数, 所以必须给etyUser的PK赋值
etyUser.SetAttributeValue(FD_USER_NAME, txtUserName.Text);

//update address
etyUser.SetAttributeValue(FD_ADDRESS, txtAddress.Text);
m_CtrSample.UpdateEntity(@etyUser);

//refresh
cmdQueryAllClick(Sender);
end;

这是修改记录的方法，设置了条件
procedure TForm1.cmdUpdateAddress2Click(Sender: TObject);
var
etyCondition : TEtyCondition;
listCondition : TEtyList;
etyUser : IEntity;
begin
//get user name condition
if txtUserName.Text = '' then
begin
Application.MessageBox('please select one row', 'Error');
exit;
end;

etyCondition := TEtyCondition.Create;
//这里FD_USER_NAME的值是EtyUser.xml文件的<EntityFieldName>
etyCondition.SetAttributeValue(FD_ENTITY_FIELD_NAME, FD_USER_NAME);
etyCondition.SetAttributeValue(FD_OPERATION, '=');
etyCondition.SetAttributeValue(FD_VALUE, txtUserName.Text);

listCondition := TEtyList.Create(TEtyCondition.Create);
listCondition.AddEntity(etyCondition);

//update address
etyUser := TEtyUser.Create;
etyUser.SetAttributeValue(FD_ADDRESS, txtAddress.Text);
m_CtrSample.UpdateEntity(@etyUser, listCondition);

//refresh
cmdQueryAllClick(Sender);
end;

这是存储过程的使用方法
//-----------------------------------------------------------------------------
// Store Procedure
//-----------------------------------------------------------------------------
procedure TForm1.cmdStoreProcedureClick(Sender: TObject);
var
etySumAge : IEntity;
etyCondition : TEtyCondition;
listCondition : TEtyList;
nFromAge, nToAge : integer;
begin
etySumAge := TEtySumAge.Create;

//get from age and to age condition
listCondition := TEtyList.Create(TEtyCondition.Create);

//set from condition
etyCondition := TEtyCondition.Create;
nFromAge := 0;
if txtFromAge.Text <> '' then
nFromAge := StrToInt(txtFromAge.Text);
etyCondition.SetAttributeValue(FD_ENTITY_FIELD_NAME, FD_SUM_AGE);
etyCondition.SetAttributeValue(FD_OPERATION, '>=');
etyCondition.SetAttributeValue(FD_VALUE, nFromAge);

listCondition.AddEntity(etyCondition);

//set to condition
etyCondition := TEtyCondition.Create;
nToAge := 0;
if txtToAge.Text <> '' then
nToAge := StrToInt(txtToAge.Text);
etyCondition.SetAttributeValue(FD_ENTITY_FIELD_NAME, FD_SUM_AGE);
etyCondition.SetAttributeValue(FD_OPERATION, '<=');
etyCondition.SetAttributeValue(FD_VALUE, nToAge);

listCondition.AddEntity(etyCondition);

//run stored procedure to get sum age
m_CtrSample.LoadEntity(@etySumAge, listCondition);

//display sum age
txtSumAge.Text := IntToStr(etySumAge.GetAttributeValue(FD_SUM_AGE));
end;

生日快乐

---------- 如何使用Sample代码 ----------
1. 数据库是SQL Server 2005, 在“sample/Database”里有已经生成的数据库，还有数据库脚本
2. 创建完数据库后需要创建数据库的ODBC, TdmSampleDBGW的AleasName必须是ODBC的名字, 参考TdmSampleDBGW.DataModuleCreate
//init m_db
m_db.AliasName := 'Sample_SQLServer';
3. 数据库的用户名和密码需要设置在
procedure TdmSampleDBGW.m_dbBeforeConnect(Sender: TObject);
begin
inherited;

if m_db.LoginPrompt = false then
begin
m_db.Params.Values['USER NAME'] := 'sa';
m_db.Params.Values['PASSWORD'] := '';
end;
end;
4. 每一个自定义的Entity类都必须要有一个对应的xml文件， xml文件必须放在“Sample/EntityMap/”路径下
5. 在Factory类里要创建每一个自定义的Entity类, 参考以下代码
//-----------------------------------------------------------------------------
// GetEntityName
//-----------------------------------------------------------------------------
function TFtySample.CreateEntity(strEntityName : String) : IEntity;
begin
strEntityName := LowerCase(strEntityName);

if (strEntityName = LowerCase(TEtyUser.Create.GetEntityName)) then
Result := TEtyUser.Create
else if (strEntityName = LowerCase(TEtySumAge.Create.GetEntityName)) then
Result := TEtySumAge.Create
else
Result := nil;
end;


---------- 如何写xml文件 ----------
<EntityMap>
<!--etyUser是TEtyUser.GetEntityName()的值-->
<EntityName>etyUser</EntityName>

<!--Table表示该etyUser对应的是数据库的表-->
<LoadType>Table</LoadType>

<!--tabUser 是对应的是数据库的表的名字-->
<TableName>tabUser</TableName>
<Fields>
<Field>
<!--UserName 是TEtyUser类的一个常量字符串, 是FD_USER_NAME = 'UserName'-->
<EntityFieldName>UserName</EntityFieldName>

<!--UserName是Entity的UserName对应在表tabUser里的字段名字-->
<TableFieldName>UserName</TableFieldName>

<!--string是字段的类型-->
<TableFieldType>string</TableFieldType>

<!--字段是否是表的主键(Primary Key)-->
<IsPK>true</IsPK>
</Field>

<Field>
<EntityFieldName>Address</EntityFieldName>
<TableFieldName>Address</TableFieldName>
<TableFieldType>string</TableFieldType>
<IsPK>false</IsPK>
</Field>

<Field>
<EntityFieldName>Age</EntityFieldName>
<TableFieldName>Age</TableFieldName>
<TableFieldType>int</TableFieldType>
<IsPK>false</IsPK>
</Field>
</Fields>
</EntityMap>

说明:
1. "EntityMap"文件夹的路径必须和EXE路径一样
2. <LoadType>目前可以是Table, StoredProc
3. <TableFieldType>目前可以是string, int等


---------- 注意事项 ----------
1. 数据库的字段类型必须是delphi能识别的, 参考 http://www.delphibbs.com/delphibbs/dispq.asp?lid=3693204


---------- 需要改进的地方 ----------
1. 目前只测试过SQL Server数据库, 没有测试过其他数据库
2. <TableFieldType>目前可以是string, int等, 但需要改进
3. <LoadType>目前可以是Table, StoredProc, 但需要改进
4. 不支持主从表直接操作
5. 需要改进FormatValueAppToDB
6. 需要改进FormatValueDBToApp
7. 需要改进ConnectionPool
8. 数据库暂时是用ODBC, 可以改成其他方式


---------- 已知bug ----------
1. TEtyList.Clear并没有真正释放内存
2. Entity = nil并没有真正释放内存, 参考 http://www.delphibbs.com/delphibbs/dispq.asp?lid=3674946

这是控制类，一般商务逻辑可以写在这里，也可以不要这个类，直接调用DBGW

unit CtrSample;

interface

uses
Forms, Entity, EtyList, SampleDBGW, FtySample;

type
TCtrSample = class
private
protected
m_dbgw : TdmSampleDBGW;
public
constructor Create;

function LoadEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
function LoadEntityList(list : TEtyList; const listCondition : TEtyList = nil; const listRule : TEtyList = nil) : Boolean;
function InsertEntity(pety : PIEntity) : Boolean;
function UpdateEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
function DeleteEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
end;

implementation
uses DB;

//-----------------------------------------------------------------------------
// Create
//-----------------------------------------------------------------------------
constructor TCtrSample.Create;
var
factory : TFtySample;
begin
m_dbgw := dmSampleDBGW;
factory := TFtySample.Create;

//注意，这里一定要设置factory, 在DGBW的LoadEntityList里需要用
m_dbgw.SetEntityFactory(factory);
end;

//-----------------------------------------------------------------------------
// LoadEntity
//-----------------------------------------------------------------------------
function TCtrSample.LoadEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
begin
//这里还根据商务逻辑增加写日志，或事务处理，权限控制

Result := m_dbgw.LoadEntity(pety, listCondition);
end;

//-----------------------------------------------------------------------------
// LoadEntityList
//-----------------------------------------------------------------------------
function TCtrSample.LoadEntityList(list : TEtyList; const listCondition : TEtyList = nil; const listRule : TEtyList = nil) : Boolean;
begin
Result := m_dbgw.LoadEntityList(list, listCondition, listRule);
end;

//-----------------------------------------------------------------------------
// InsertEntity
//-----------------------------------------------------------------------------
function TCtrSample.InsertEntity(pety : PIEntity) : Boolean;
begin
Result := m_dbgw.InsertEntity(pety);
end;

//-----------------------------------------------------------------------------
// UpdateEntity
//-----------------------------------------------------------------------------
function TCtrSample.UpdateEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
begin
Result := m_dbgw.UpdateEntity(pety, listCondition);
end;

//-----------------------------------------------------------------------------
// DeleteEntity
//-----------------------------------------------------------------------------
function TCtrSample.DeleteEntity(pety : PIEntity; const listCondition : TEtyList = nil) : Boolean;
begin
Result := m_dbgw.DeleteEntity(pety, listCondition);
end;

end.

最简单的o/r map的实现
我想知道为什么叫“哲别”，难道你儿子叫哲别？

我儿子的名字有个字是“哲”

谢谢大家祝我们生日快了
如果你没有做过，那这些代码可以用来学习
诚然，这并不是最好的代码，而且中国高人如云
学海无涯

是个好人，祝你们爷俩生日快乐。

MARK ~
HAPPY BIRTHDAY TO YOUR SON!

生日快乐!!

生日快乐！

生日快乐, 我比你大一天,呵呵.

有个性。程序员的特别礼物。哈

happy birthday！