This article describes how to use ADO and ADOX to create and open a database in the framework of the MFC Document view. Read MFC technical Article TN025: Document, View, andFrameCreation Microsoft Knowledge Base Article Q183606ActiveXDataObjects (ADO) FrequentlyAskedQuestionsQ169496INFO: UsingActi
This article describes how to use ADO and ADOX to create and open a database in the MFC documentation/View/framework architecture. Read MFC technical Article TN025: Document, View, and Frame Creation Microsoft Knowledge Base Article Q183606 ActiveX Data Objects (ADO) Frequently Asked Questions Q169496 INFO: Using Acti
This article describes how to use ADO and ADOX to create and open a database in the MFC documentation/View/framework architecture.
Preparation
MFC technical article
- TN025: Document, View, and Frame Creation
Microsoft Knowledge Base Article
- Q183606 ActiveX Data Objects (ADO) Frequently Asked Questions
- Q169496 INFO: Using ActiveX Data Objects (ADO) via # import in VC ++
- Q317881 how to: Create an Access Database Using ADOX and Visual C #. NET
- Q252908 HOWTO: Create a Table with Primary Key Through ADOX
- Q201826 PRB: Error 3265 When You Access Properties Collection
Office VBA reference
- Creating and Modifying Access Tables
Procedure
- Install MDAC2.5 or above on your computer
- Open VC. First, we use the MFC Application Wizard to create a standard MDI program. Here I name this project Passport, and then import ADOX in stdafx. h.
# Include
# Import "c:/Program Files/Common Files/system/ado/Msado15.dll" rename ("EOF", "adoEOF") rename ("DataTypeEnum", "adoDataTypeEnum ")
# Import "c:/Program Files/Common Files/System/ADO/Msadox. dll" rename ("EOF", "adoXEOF") rename ("DataTypeEnum", "adoXDataTypeEnum ")
# Import "c:/program files/common files/System/ado/MSJRO. DLL"
Depending on the installation path of ADO on your computer, the path here may be different.
- Declare the database connection ADODB: _ ConnectionPtr m_pConn; and the record set ADODB: _ RecordsetPtr m_pSet; in the document class, And reload the DeleteContents () and OnNewDocument () of the document class () the OnOpenDocument () function is used to disconnect a database, create a database and a table, and open an existing database.
(The author complained that the CSDN Article center should be changed, so code layout is so troublesome)
Void CPassportDoc: DeleteContents ()
{
Try
{
If (m_pSet ){
ESRecordsetClose (m_pSet );
}
If (m_pConn)
If (m_pConn-> State & ADODB: adStateOpen)
M_pConn-> Close ();
M_pConn = NULL;
}
Catch (_ com_error & e ){
ESErrPrintProviderError (m_pConn );
ESErrPrintComError (e );
}
CDocument: DeleteContents ();
} BOOL CPassportDoc: OnNewDocument ()
{
If (! CDocument: OnNewDocument ())
Return FALSE;
CFileDialog dlgFile (FALSE, _ T (". mdb "), NULL, OFN_HIDEREADONLY | OFN_PATHMUSTEXIST, _ T (" Access Database (*. mdb) | *. mdb | all files (*. *) | *. * | "));
If (dlgFile. DoModal ()! = IDOK)
Return FALSE;
CString strDBPath = dlgFile. GetPathName ();
If (! CreateDB (strDBPath) return FALSE;
// Create
CString strConnect;
StrConnect. Format (_ T ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = % s"), strDBPath );
COleVariant Connect (strConnect );
// TODO: add reinitialization code here
// (SDI documents will reuse this document)
Try {
M_pConn.CreateInstance (_ T ("ADODB. Connection "));
M_pSet.CreateInstance (_ T ("ADODB. Recordset "));
M_pConn-> PutCommandTimeout (30 );
M_pConn-> PutConnectionTimeout (30 );
M_pConn-> put_CursorLocation (ADODB: adUseClient );
M_pConn-> Open (_ bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB: adConnectUnspecified );
: ESRecordsetOpen (_ T ("Passport"), m_pConn, m_pSet );
SetPathName (strDBPath );
Return TRUE;
}
Catch (_ com_error & e ){
ESErrPrintProviderError (m_pConn );
ESErrPrintComError (e );
}
Catch (...){
}
M_pConn = NULL;
Return FALSE;
}
BOOL CPassportDoc: OnOpenDocument (LPCTSTR lpszPathName)
{
If (! CDocument: OnOpenDocument (lpszPathName ))
Return FALSE;
ADODB: _ ConnectionPtr tempConnn;
CString strConnect;
CString strDBPath = lpszPathName;
StrConnect. Format (_ T ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = % s"), strDBPath );
COleVariant Connect (strConnect );
// TODO: add reinitialization code here
// (SDI documents will reuse this document)
Try {
TempConnn. CreateInstance (_ T ("ADODB. Connection "));
TempConnn-> PutCommandTimeout (30 );
TempConnn-> PutConnectionTimeout (30 );
TempConnn-> put_CursorLocation (ADODB: adUseClient );
TempConnn-> Open (_ bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB: adConnectUnspecified );
SetPathName (strDBPath );
M_pConn = tempConnn;
M_pSet = NULL;
M_pSet.CreateInstance (_ T ("ADODB. Recordset "));
: ESRecordsetOpen (_ T ("Passport"), m_pConn, m_pSet );
UpdateAllViews (NULL, UpdateHintRefresh );
Return TRUE;
}
Catch (_ com_error & e ){
ESErrPrintProviderError (tempConnn );
ESErrPrintComError (e );
}
Catch (...){
}
Return FALSE;
}
- Compile an auxiliary function for creating databases, tables, and indexes.
BOOL CPassportDoc: CreateDB (LPCTSTR lpszFile)
{
If (: PathFileExists (lpszFile )){
CString strTemp;
StrTemp. Format (IDS_TARGET_EXISTS, lpszFile );
AfxMessageBox (lpszFile );
Return FALSE;
}
ADODB: _ ConnectionPtr tempConnn;
ADOX: _ CatalogPtr pCatalog = NULL;
ADOX: _ TablePtr pTable = NULL;
ADOX: _ IndexPtr pIndexNew = NULL;
ADOX: _ IndexPtr pIndex = NULL;
CString strConnect;
CString strDBPath = lpszFile;
StrConnect. Format (_ T ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source = % s"), strDBPath );
COleVariant Connect (strConnect );
Try {
PCatalog. CreateInstance (_ T ("ADOX. Catalog "));
PCatalog-> Create (LPCTSTR) strConnect); // Create a database
TempConnn. CreateInstance (_ T ("ADODB. Connection "));
TempConnn-> PutCommandTimeout (30 );
TempConnn-> PutConnectionTimeout (30 );
TempConnn-> put_CursorLocation (ADODB: adUseClient );
TempConnn-> Open (_ bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB: adConnectUnspecified );
PCatalog-> PutActiveConnection (_ variant_t (IDispatch *) tempConnn ));
PTable. CreateInstance (_ T ("ADOX. Table "));
PTable-> ParentCatalog = pCatalog;
PTable-> Name = "Passport ";
ADOX: ColumnsPtr pCols = pTable-> Columns;
PCols-> Append (_ T ("RecordID"), ADOX: adInteger, 0); // automatically numbered field
PCols-> Append (_ T ("Name"), ADOX: adWChar, 255); // text field
PCols-> Append (_ T ("DateOfBirth"), ADOX: adDate, 0); // Date Field
PCols-> Append (_ T ("OtherInfo"), ADOX: adLongVarWChar, 0); // remarks field
PCatalog-> Tables-> Refresh ();
Long lCount = pCols-> Count;
For (long I = 0; I PCols-> GetItem (I)-> ParentCatalog = pCatalog; // important! Set Catalog, see Q201826 PRB: Error 3265 When You Access Properties Collection
ADOX: PropertiesPtr pProperties = pCols-> GetItem (I)-> Properties;
If (pProperties) {// here is the property display code for debugging
Long lp = pProperties-> Count;
TRACE ("Properties for Col % s/r/n", (LPCTSTR) pCols-> GetItem (I)-> Name );
For (long j = 0; j TRACE ("/rProperty % s: % s/r/n", g_GetValueString (pProperties-> GetItem (j)-> Name)
, G_GetValueString (pProperties-> GetItem (j)-> Value ));
}
}
}
PCols-> GetItem (_ T ("RecordID")-> Properties-> GetItem (_ T ("Description ")) -> Value = _ T ("Record Number"); // comment
PCols-> GetItem (_ T ("RecordID")-> Properties-> GetItem (_ T ("AutoIncrement")-> Value = true; // automatic ID
PCols-> GetItem (_ T ("Name")-> Properties-> GetItem (_ T ("Jet OLEDB: Compressed UniCode Strings")-> Value = true;
PCols-> GetItem (_ T ("Name")-> Properties-> GetItem (_ T ("Description")-> Value = _ T ("Name ");
PCols-> GetItem (_ T ("DateOfBirth")-> Properties-> GetItem (_ T ("Description ")) -> Value = _ T ("Date of Birth ");
PCols-> GetItem (_ T ("OtherInfo")-> Properties-> GetItem (_ T ("Jet OLEDB: Compressed UniCode Strings")-> Value = true;
PCols-> GetItem (_ T ("OtherInfo")-> Properties-> GetItem (_ T ("Description ")) -> Value = _ T ("other information ");
PCatalog-> Tables-> Append (_ variant_t (IDispatch *) pTable); // Add a table
PCatalog-> Tables-> Refresh (); // Refresh
PIndexNew. CreateInstance (_ T ("ADOX. Index "));
PIndexNew-> Name = "RecordID"; // index Name
PIndexNew-> Columns-> Append ("RecordID", ADOX: adInteger, 0); // index Field
PIndexNew-> PutPrimaryKey (-1); // Primary Index
PIndexNew-> PutUnique (-1); // unique index
PTable-> Indexes-> Append (_ variant_t (IDispatch *) pIndexNew); // create an index
PIndexNew = NULL;
PCatalog-> Tables-> Refresh (); // Refresh
Return TRUE;
}
Catch (_ com_error & e ){
ESErrPrintProviderError (tempConnn );
ESErrPrintComError (e );
Return FALSE;
}
Catch (...){
}
Return FALSE;
}
- Auxiliary database functions. Because these functions were previously written for a project by Jiangsheng. So the name is a bit strange. Some codes of the MFC class CDaoRecordset are used for reference.
# Define _ countof (array) (sizeof (array)/sizeof (array [0])
BOOL ESRecordsetOpen (
LPCTSTR lpszSQL
, ADODB: _ ConnectionPtr pConnection
, ADODB: _ RecordsetPtr & rst
, ADODB: CursorTypeEnum CursorType // = adOpenDynamic
, ADODB: LockTypeEnum LockType // = ado20: adLockOptimistic
, Long lOptions // = adCmdUnspecified
)
{
_ Bstr_t bstrQuery;
Const TCHAR _ afxParameters2 [] = _ T ("PARAMETERS ");
Const TCHAR _ afxSelect2 [] = _ T ("SELECT ");
Const TCHAR _ afxTransform2 [] = _ T ("TRANSFORM ");
Const TCHAR _ afxTable2 [] = _ T ("TABLE ");
// Construct the default query string
If (_ tcsnicmp (lpszSQL, _ afxSelect2, _ countof (_ afxSelect2)-1 )! = 0 )&&
(_ Tcsnicmp (lpszSQL, _ afxParameters2, _ countof (_ afxParameters2)-1 )! = 0 )&&
(_ Tcsnicmp (lpszSQL, _ afxTransform2, _ countof (_ afxTransform2)-1 )! = 0 )&&
(_ Tcsnicmp (lpszSQL, _ afxTable2, _ countof (_ afxTable2)-1 )! = 0 )){
CString strTemp;
StrTemp. Format ("SELECT * FROM (% s)", lpszSQL );
BstrQuery = (LPCTSTR) strTemp;
}
Else
BstrQuery = lpszSQL;
If (rst! = NULL ){
Rst-> CursorLocation = ADODB: adUseClient;
Rst-> Open (bstrQuery, _ variant_t (pConnection. GetInterfacePtr (), true), CursorType, LockType, lOptions );
}
TRACE ("Open Recordset: % s/n", lpszSQL );
Return ESRecordsetIsOpen (rst );
}
BOOL ESRecordsetIsOpen (const ADODB: _ RecordsetPtr & rst)
{
If (rst! = NULL ){
Return rst-> State & ADODB: adStateOpen;
}
Return FALSE;
}
Void ESRecordsetClose (ADODB: _ RecordsetPtr & rst)
{
If (rst! = NULL ){
If (rst-> State & ADODB: adStateOpen)
Rst-> Close ();
}
}
CString g_GetValueString (const _ variant_t & val)
{
CString strVal;
_ Variant_t varDest (val );
If (! G_varIsValid (val )){
Return strVal;
}
If (val. vt = VT_BOOL ){
If (val. boolVal = VARIANT_FALSE ){
Return _ T ("no ");
}
Else
Return _ T ("yes ");
}
Else {
}
If (varDest. vt! = VT_BSTR ){
HRESULT hr =: VariantChangeType (& varDest, & varDest, VARIANT_NOUSEROVERRIDE | VARIANT_LOCALBOOL, VT_BSTR );
If (FAILED (hr )){
Return strVal;
}
}
StrVal = (LPCTSTR) _ bstr_t (varDest );
Return strVal;
}
- Error Handling Code
Void ESErrPrintComError (_ com_error & e)
{
_ Bstr_t bstrSource (e. Source ());
_ Bstr_t bstrDescription (e. Description ());
CString strTemp;
StrTemp. format (_ T ("'error/n/t error code: % 08lx/n/t meaning: % s/n/t from: % s/n/t description: % s/n "),
E. Error (), e. ErrorMessage (), (LPCSTR) bstrSource, (LPCSTR) bstrDescription );
// Print COM errors.
: AfxMessageBox (strTemp );
# Ifdef _ DEBUG
AfxDebugBreak ();
# Endif
}
Void ESErrPrintProviderError (ADODB: _ ConnectionPtr pConnection)
{
If (pConnection = NULL) return;
Try {
// Print Provider Errors from Connection object.
// PErr is a record object in the Connection's Error collection.
ADODB: ErrorPtr pErr = NULL;
ADODB: ErrorsPtr pErrors = pConnection-> Errors;
If (pErrors ){
If (pErrors-> Count)> 0 ){
Long nCount = pErrors-> Count;
// Collection ranges from 0 to nCount-1.
For (long I = 0; I <nCount; I ++ ){
PErr = pErrors-> GetItem (I );
CString strTemp;
StrTemp. Format (_ T ("/t error code: % x/t % s"), pErr-> Number, pErr-> Description );
}
}
}
}
Catch (_ com_error & e ){
ESErrPrintComError (e );
}
}
Summary
Integration of database access in the document, view, and framework architecture is not difficult in general. Microsoft provides many sample codes. Most of the work is to rewrite the sample code from other languages to VC. The main task is to understand the documentation, views, and framework architecture of MFC and call the code as appropriate.
Although I opened a record set while opening the database, I did not provide the code to display the record set content, which is beyond the scope of this article. The prompt I can give is to Use the ready-made data list Control for display. Microsoft Knowledge Base Article Q229029 SAMPLE: AdoDataGrid.exe Demonstrates How to Use ADO DataGrid Control Using Visual C ++ can be used as a reference.
