在前面的几篇文章中,分别对ado操作接口,单条的记录结构,variant数据转换为c数据类型等做了说明,这些都是为最终实现一个方便的数据库操作接口作准备,在这一篇里,将创建一个模板数据库记录集操作类,它需要实现的功能有, select, update, 和delete操作,当然,除了select sql是将在程序中给出外,其它的象update,delete等都应该是这个模板记录集根据字段类型和名称自动生成的,而不必再人工生成这些sql语句. 用过微软的.net的dataset应该有类似的体验,在vs2003中,通过wizzard生成和数据库相关的dataset记录集和datarow等,用户只要在dataset中添加和删除,最后update一下,dataset自动帮你生成sql并执行.这样就方便了很多.同时,记录集的某条记录的成员都是强数据类型的,而不是可变的variant数据类型. 对于经常使用的跨表的数据查询等,也可以使用,无需先定义数据结构的麻烦.
template
<
typename
_tlist
>
class data_op_recordset
{
public
:
typedef data_op_record_row
<
_tlist
>
_DataRow;
typedef std::list
<
_DataRow
*
>
_DataSet;
typedef std::vector
<
__tagFieldInfo
>
_FieldNames;
enum {
en_field_count
=
_DataRow::en_member_count,
};
public
:
virtual BOOL Load(
const
TCHAR
*
query, CCom_Connection
&
conn) {
CCom_Recordset lreset(__uuidof(Recordset));
_bstr_t sql
=
query;
lreset
->
Open(sql, (IDispatch
*
)conn, ADODB_L::adOpenStatic, ADODB_L::adLockReadOnly, ADODB_L::adCmdText);
if
(m_DataSet.size()) {
Clear();
}
GetFieldName(lreset);
if
(!lreset
->
ADO_EOF
&&
!lreset
->
BOF) {
lreset
->
MoveFirst();
int
i;
for
(i
=
0
; i
<
lreset
->
GetRecordCount(); i
++
) {
_DataRow
*
prow
=
new
_DataRow;
_data_get
<
en_field_count
>
::DoDataChange(prow, m_FieldNames, lreset);
m_DataSet.push_back(prow);
lreset
->
MoveNext();
}
}
return
0
;
}
virtual BOOL Update(CCom_Connection
&
conn) {
BeforeUpdate();
BOOL ret
=
FALSE
;
std::list
<
CString
>
sql_list;
for
(_DataSet::iterator it
=
m_DataSet.begin(); it !
=
m_DataSet.end(); it
++
) {
CString sql;
if
((
*
it)
->
m_en_State
==
_DataRow::_en_Row_Add) {
sql
=
MakeInsertSql(
*
(
*
it));
}
if
((
*
it)
->
m_en_State
==
_DataRow::_en_Row_Update) {
sql
=
MakeUpdateSql(
*
(
*
it));
}
if
((
*
it)
->
m_en_State
==
_DataRow::_en_Row_Del) {
sql
=
MakeDeleteSql(
*
(
*
it));
}
if
(sql.GetLength()) {
sql_list.push_back(sql);
TRACE(
"
%s\n
"
, sql);
}
}
if
(sql_list.size()) {
conn
->
BeginTrans();
try {
std::list
<
CString
>
::iterator it;
for
(it
=
sql_list.begin(); it !
=
sql_list.end(); it
++
) {
CString cc;
_bstr_t temp;
_variant_t RecordsAffected;
cc
=
*
it;
cc.Replace(
'
\'','\"');
TRACE(
"
%s\n
"
, cc);
temp
=
cc;
conn
->
Execute
(temp,
&
RecordsAffected, ADODB_L::adCmdText);
}
}catch (_com_error
&
e) {
CString info;
_bstr_t es, ds;
es
=
e.ErrorMessage();
ds
=
e.Description();
CString c1, c2;
c1
=
es.operator char
*
();
c2
=
ds.operator char
*
();
info.Format(
"
错误: %s %s
"
, c1, c2);
AfxMessageBox(info);
conn
->
RollbackTrans();
return
FALSE
;
}
conn
->
CommitTrans();
}
AfterUpdate();
return
TRUE
;
}
int
GetRecordCount() {
return m_DataSet.size();
}
size_t AddRow(_DataRow
*
pnew) {
m_DataSet.push_back(pnew);
return m_DataSet.size();
}
void DelLastRow(size_t index) {
assert(m_DataSet.size()
==
index);
if
(m_DataSet.size()) {
_DataSet::iterator it
=
m_DataSet.end();
m_DataSet.erase(
--
it);
}
}
_DataRow
&
Row(
int
index) {
int
i;
_DataSet::iterator it
=
m_DataSet.begin();
for
(i
=
0
; i
<
index; i
++
) {
it
++
;
}
return
*
(
*
it);
}
private
:
void BeforeUpdate() {
}
void AfterUpdate() {
if
(m_DataSet.size()) {
_DataSet::iterator it;
it
=
m_DataSet.begin();
while
(it !
=
m_DataSet.end()) {
if
((
*
it)
->
m_en_State
==
_DataRow::_en_Row_Add || (
*
it)
->
m_en_State
==
_DataRow::_en_Row_Update) {
(
*
it)
->
m_en_State
=
_DataRow::_en_Row_NoChange;
it
++
;
continue;
}
//
更新成功,删除条目
if
((
*
it)
->
m_en_State
==
_DataRow::_en_Row_Del) {
_DataSet::iterator tt;
tt
=
it
++
;
m_DataSet.erase(tt);
continue;
}
it
++
;
}
}
}
CString MakeInsertSql(_DataRow
&
row) {
CString ret
=
CString();
int
i;
ret
=
"
INSERT INTO
"
;
ret
+=
m_TableName.c_str();
ret
+=
"
(
"
;
for
(i
=
0
; i
<
en_field_count; i
++
) {
if
(i
>
0
) {
ret
+=
"
,
"
;
}
ret
+=
m_FieldNames.at(i).m_FieldName.c_str();
}
ret
+=
"
) values (
"
;
ret
+=
_data_get
<
en_field_count
>
::MakeFieldValue(row);
ret
+=
"
);
"
;
return ret;
}
BOOL IsKeyField(
int
) {
return
FALSE
;
}
CString MakeUpdateSql(_DataRow
&
row) {
CString ret
=
CString();
ret
=
"
UPDATE
"
;
ret
+=
m_TableName.c_str();
ret
+=
"
SET
"
;
ret
+=
_data_get
<
en_field_count
>
::MakeUpdateValue(row, m_FieldNames, m_TableName.c_str());
ret
+=
"
;
"
;
return ret;
}
CString MakeDeleteSql(_DataRow
&
row) {
CString ret
=
CString();
ret
=
"
Delete * from
"
;
ret
+=
m_TableName.c_str();
ret
+=
"
"
;
ret
+=
_data_get
<
en_field_count
>
::MakeDeleteValue(row, m_FieldNames);
ret
+=
"
;
"
;
return ret;
}
public
:
void Test() {
_DataSet::iterator it
=
m_DataSet.begin();
MakeInsertSql(
*
(
*
it));
MakeUpdateSql(
*
(
*
it));
MakeDeleteSql(
*
(
*
it));
}
private
:
BOOL GetFieldName(CCom_Recordset
&
reset) {
long
fieldcount;
long
i;
CComFields lfields;
lfields
=
reset
->
GetFields();
fieldcount
=
lfields
->
GetCount();
m_FieldNames.clear();
for
(i
=
0
; i
<
fieldcount; i
++
) {
CComField lf;
lf
=
lfields
->
GetItem(_variant_t(i));
_bstr_t fname
=
lf
->
Name;
CString fieldname
=
fname.operator char
*
();
m_FieldNames.push_back(__tagFieldInfo(std::
string
(LPCTSTR(fieldname)), lf
->
Type));
TRACE(
"
fieldname %s\n
"
, fieldname);
}
return
TRUE
;
}
virtual BOOL Save() {
BOOL ret
=
FALSE
;
for
(_DataSet::iterator it
=
m_DataSet.begin(); it !
=
m_DataSet.end(); it
++
) {
}
return ret;
}
BOOL InsertRow() {
return
0
;
}
BOOL DeleteRow() {
return
0
;
}
BOOL UpdateRow() {
return
0
;
}
public
:
void SetTableName(LPCTSTR table_name) {
m_TableName
=
table_name;
}
void SetKey(
int
index) {
m_FieldNames.at(index).m_KeyField
=
TRUE
;
}
void SetKey(
int
id1,
int
id2) {
m_FieldNames.at(id1).m_KeyField
=
TRUE
;
m_FieldNames.at(id2).m_KeyField
=
TRUE
;
}
void Clear() {
if
(m_DataSet.size()) {
_DataSet::iterator it;
for
(it
=
m_DataSet.begin(); it !
=
m_DataSet.end(); it
++
) {
delete
*
it;
}
m_DataSet.clear();
}
}
void SetModified() {
m_Modified
=
true
;
}
bool GetModified() {
return m_Modified;
}
public
:
data_op_recordset() : m_Modified(
false
) {}
~data_op_recordset() {
Clear();
}
protected:
private
:
_DataSet m_DataSet;
_FieldNames m_FieldNames;
std::
string
m_TableName;
bool m_Modified;
};
例子代码在:
http://www.cppblog.com/Files/hdqqq/ado_template.rar
在其中实现了表格数据显示,添加记录和一个跨表数据查询结果的显示.
vc 6 工程, 编译需要loki 和boost 库支持.
过完春节以后,很多事情,一直没有时间写东西,这次把基于模板的数据操作类写完,总算是对自己有个交待.