工程數據庫編程

/*

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \

no_namespace rename("EOF", "EndOfFile")

*/

CoInitialize(NULL);

_bstr_t varSource="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*.mdb";

//_bstr_t varSource="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

_ConnectionPtr m_pConnection(_uuidof(Connection));

m_pConnection->Open(varSource,"","",adModeUnknow);

_RecordsetPtr m_pSet(_uuid(Recordset));

try {

m_pSet->Open(%%1,m_pConnection.GetInterfacePtr()

adOpenDynamic,adLockPessimistic,adCmdText);

}

catch(_com_error *e){

{

AfxMessageBox(e->ErrorMessage());

return;

}

_variant_t var;

CString %%2="";

long fldc=m_pSet->GetFields()->GetCount();

long i=0;

try {

m_pSet->MoveFirst();

if(!m_pSet->adoEOF)

{

for(i=0;i<fldc;i++)

{

var=m_pSet->GetCollect((long)i);

var.ChangeType(VT_BSTR);

%%2+=var.bstrVal;

%%2+=" ";

}

//m_pSet->MoveNext();

}

}

catch(_com_error *e){

AfxMessageBox(e->ErrorMessage());

delete e;

}

//m_pSet->MoveFirst();

CoUninitialize(NULL);

2.單值比較

/*

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \

no_namespace rename("EOF", "EndOfFile")

*/

CoInitialize(NULL);

_bstr_t varSource="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*.mdb";

//_bstr_t varSource="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

_ConnectionPtr m_pConnection(_uuidof(Connection));

m_pConnection->Open(varSource,"","",adModeUnknow);

_RecordsetPtr m_pSet(_uuid(Recordset));

try {

m_pSet->Open(%%1,m_pConnection.GetInterfacePtr(),

adOpenDynamic,adLockPessimistic,adCmdText);

}

catch(_com_error *e){

{

AfxMessageBox(e->ErrorMessage());

return;

}

_variant_t var;

try {

m_pSet->MoveFirst();

if(!m_pSet->adoEOF)

{

var=m_pSet->GetCollect((long)0);

var.ChangeType(VT_I2);

int %%3=var.intVal;

if(%%3==%%4)

{

%%5

}

//m_pSet->MoveNext();

}

catch(_com_error *e){

AfxMessageBox(e->ErrorMessage());

delete e;

}

//m_pSet->MoveFirst();

CoUninitialize(NULL);

3.顯示表格

/*

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \

no_namespace rename("EOF", "EndOfFile")

*/

CoInitialize(NULL);

_bstr_t varSource="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=*.mdb";

//_bstr_t varSource="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

_ConnectionPtr m_pConnection(_uuidof(Connection));

m_pConnection->Open(varSource,"","",adModeUnknow);

//打開屬性為默認（adModeRead(只讀)，adModeWrite(可寫)，adModeReadWrite(可讀寫)等）

_RecordsetPtr m_pSet(_uuid(Recordset));

try {

HRESULT hr=m_pSet->Open(%%1,m_pConnection.GetInterfacePtr(),

adOpenDynamic,adLockPessimistic,adCmdText);

}

catch(_com_error *e){

AfxMessageBox(e->ErrorMessage());

}

if(SUCCESSED(hr))

{

//表打開成功

}

FieldsPtr p_fields=m_pSet->Fields;

FieldPtr p_field;

_variant_t var_index;

LPCSTR field_name;

int index=0;

_bstr_t bstr_field_name;

int countfields=p_fields->GetCount();

CString *Column=new CString[countfields];

CListCtrl *pList=(CListCtrl*)GetDlgItem(%%1);//IDC_LIST_TABLEDATA

VERIFY(pList);

pList->DeleteAllItems();

for(index=0;index<countfields;index++)

{

var_index.vt=VT_I4;

var_index.IVal=index;

p_field=p_fields->Item[var_index];

bstr_field_name=p_field->GetName();

field_name=(LPCSTR)bstr_field_name;

Column[index]=field_name;

int ColumnWidth=Column[index].GetLength()*15;

pList->InsertColumn(index,field_name,LVCFMT_CENTER,ColumnWidth);

}

int i=0;

_bstr_t vCol;

//pList->SetTextBkColor(RGB(122,200,122));

//pList->SetTextColor(RGB(0,0,200));

while(!m_pSet->adoEOF)

{

pList->Insert(i,atoi(i));

for(int j=0;j<countfields;j++)

{

vCol=m_pSet->GetCollect((long)j);

pList->SetItemText(i,j,vCol);

}

m_pSet->MoveNext();

i++;

}

CoUninitialize(NULL);

22.批量執行SQL和存儲過程

22.1、如果用ODBC訪問數據庫的話，可參考下列代碼：

CDatabase * pDatabase = new CDatabase;

TRY

{

pDatabase->OpenEx( _T("DSN=ODBCName;UID=%%1;PWD=%%2"), CDatabase::noOdbcDialog);

}

CATCH (CDBException, e)

{

delete pDatabase;

return;

}END_CATCH

SQL.Format("exec sp_Name");//有參數的話直接寫再後面

pDatabase->ExecuteSQL(SQL);

pDatabase->Close();

delete pDatabase;

22.2、用ADO調用存儲過程

_ConnectionPtr Conn = NULL;

_RecordsetPtr Rs = NULL;

_CommandPtr Cmd = NULL;

CoInitialize(NULL);

Conn.CreateInstance ( __uuidof(Connection));

Rs.CreateInstance (__uuidof(Recordset));

Cmd.CreateInstance (__uuidof(Command));

Conn->Open(L"db", L"sa", L"", adOpenUnspecified); //打開正常

//如下為_CommandPtr對象參數的賦值和調用

Cmd->ActiveConnection = Conn;

Cmd->CommandText = "SP_TEST";

//數據庫中實際存在這個測試存儲過程，select * from atable,不傳遞參數

Cmd->CommandType = adCmdStoredProc;

Cmd->Parameters->Refresh();

Rs = Cmd->Execute( NULL,NULL, adCmdUnknown ); //COM出錯。

//註釋掉的代碼為直接的SQL語句提交，運行正確。

//Cmd->ActiveConnection=Conn;

//Cmd->CommandText = "select * from atable";

//Cmd->CommandType = adCmdText;

//Cmd->Parameters->Refresh();

//Rs = Cmd->Execute(NULL,NULL,adCmdUnknown);

Rs->Close();

Conn->Close();

CoUninitialize();