ADO学习（七）ADOX相关知识
一、ADOX?概述?
Microsoft??ActiveX??Data?Objects?Extensions?for?Data?Definition?Language?and?Security?(ADOX)?是對(duì)?ADO?對(duì)象和編程模型的擴(kuò)展。ADOX?包括用于模式創(chuàng)建和修改的對(duì)象，以及安全性。由于它是基于對(duì)象實(shí)現(xiàn)模式操作，所以用戶可以編寫(xiě)對(duì)各種數(shù)據(jù)源都能有效運(yùn)行的代碼，而與它們?cè)颊Z(yǔ)法中的差異無(wú)關(guān)。?ADOX?是核心?ADO?對(duì)象的擴(kuò)展庫(kù)。它顯露的其他對(duì)象可用于創(chuàng)建、修改和刪除模式對(duì)象，如表格和過(guò)程。它還包括安全對(duì)象，可用于維護(hù)用戶和組，以及授予和撤消對(duì)象的權(quán)限。?
要通過(guò)開(kāi)發(fā)工具使用?ADOX，需要建立對(duì)?ADOX?類型庫(kù)的引用。對(duì)?ADOX?庫(kù)的說(shuō)明為“Microsoft?ADO?Ext.?for?DDL?and?Security.”。ADOX?庫(kù)文件名為“Msadox.dll”，程序?ID?(ProgID)?為“ADOX”。有關(guān)建立庫(kù)引用的詳細(xì)信息，請(qǐng)參閱開(kāi)發(fā)工具的文檔。?
二、ADOX?對(duì)象?
Catalog?包含描述數(shù)據(jù)源模式目錄的集合。?
Column?表示表、索引或關(guān)鍵字的列。?
Group?表示在安全數(shù)據(jù)庫(kù)內(nèi)有訪問(wèn)權(quán)限的組帳號(hào)。?
Index?表示數(shù)據(jù)庫(kù)表中的索引。?
Key?表示數(shù)據(jù)庫(kù)表中的主關(guān)鍵字、外部關(guān)鍵字或唯一關(guān)鍵字。?
Procedure?表示存儲(chǔ)的過(guò)程。?
Table?表示數(shù)據(jù)庫(kù)表，包括列、索引和關(guān)鍵字。?
User?表示在安全數(shù)據(jù)庫(kù)內(nèi)具有訪問(wèn)權(quán)限的用戶帳號(hào)。?
View?表示記錄或虛擬表的過(guò)濾集。?
三、ADOX?方法?
Append（Columns）?將新的?Column?對(duì)象添加到?Columns?集合。??
Append（Groups）?將新的?Group?對(duì)象添加到?Groups?集合。??
Append（Indexes）?將新的?Index?對(duì)象添加到?Indexes?集合。??
Append（Keys）?將新的?Key?對(duì)象添加到?Keys?集合。??
Append（Procedures）?將新的?Procedure?對(duì)象添加到?Procedures?集合。??
Append（Tables）?將新的?Table?對(duì)象添加到?Tables?集合。??
Append（Users）?將新的?User?對(duì)象添加到?Users?集合。??
Append（Views）?將新的?View?對(duì)象添加到?Views?集合。??
ChangePassword?更改用戶帳號(hào)的密碼。??
Create?創(chuàng)建新的目錄。??
Delete?刪除集合中的對(duì)象。??
GetObjectOwner?返回目錄中對(duì)象的擁有者。??
GetPermissions?獲得對(duì)象上組或用戶的權(quán)限。??
Item?按名稱或序號(hào)返回集合的指定成員。??
Refresh?更新集合中的對(duì)象，以反映針對(duì)提供者可用的和指定的對(duì)象。??
SetObjectOwner?指定目錄中對(duì)象的擁有者。??
SetPermissions?設(shè)置對(duì)象上組或用戶的權(quán)限。??
四、ADOX?屬性?
ActiveConnection?指示目錄所屬的?ADO?Connection?對(duì)象。??
Attributes?描述列特性。??
Clustered?指示索引是否被分簇。??
Command?指定可用于創(chuàng)建或執(zhí)行過(guò)程的?ADO?Command?對(duì)象。??
Count?指示集合中的對(duì)象數(shù)量。??
DateCreated?指示創(chuàng)建對(duì)象的日期。??
DateModified?指示上一次更改對(duì)象的日期。??
DefinedSize?指示列的規(guī)定最大大小。??
DeleteRule?指示主關(guān)鍵字被刪除時(shí)將執(zhí)行的操作。??
IndexNulls?指示在索引字段中有?Null?值的記錄是否有索引項(xiàng)。??
Name?指示對(duì)象的名稱。??
NumericScale?指示列中數(shù)值的范圍。??
ParentCatalog?指定表或列的父目錄以便訪問(wèn)特定提供者的屬性。??
Precision?指示列中數(shù)據(jù)值的最高精度。??
PrimaryKey?指示索引是否代表表的主關(guān)鍵字。??
RelatedColumn?指示相關(guān)表中相關(guān)列的名稱（僅關(guān)鍵字列）。??
RelatedTable?指示相關(guān)表的名稱。??
SortOrder?指示列的排序順序（僅索引列）。??
Type(列)?指示列的數(shù)據(jù)類型。??
Type（關(guān)鍵字）?指示關(guān)鍵字的數(shù)據(jù)類型。??
Type（表）?指示表的類型。??
Unique?指示索引關(guān)鍵字是否必須是唯一的。??
UpdateRule?指示主關(guān)鍵字被更新時(shí)會(huì)執(zhí)行的操作。??
五、范例?
5.1?創(chuàng)建數(shù)據(jù)庫(kù)范例?
如下代碼顯示如何通過(guò)?Create?方法創(chuàng)建新的?Jet?數(shù)據(jù)庫(kù)。?
Sub?CreateDatabase()?
?Dim?cat?As?New?ADOX.Catalog?
?cat.Create?"Provider=Microsoft.Jet.OLEDB.4.0;Data?Source=c:\new.mdb"?
End?Sub?
5.2?創(chuàng)建表范例?
Sub?CreateTable()?
?Dim?tbl?As?New?Table?
?Dim?cat?As?New?ADOX.Catalog?
'打開(kāi)目錄。?
?'?打開(kāi)目錄。?
?cat.ActiveConnection?=?_??
?"Provider=Microsoft.Jet.OLEDB.4.0;"?&?_?
?"Data?Source=c:\Program?Files\Microsoft?Office\"?&?_?
?"Office\Samples\Northwind.mdb;"?
?tbl.Name?=?"MyTable"?
?tbl.Columns.Append?"Column1",?adInteger?
?tbl.Columns.Append?"Column2",?adInteger?
?tbl.Columns.Append?"Column3",?adVarWChar,?50?
?cat.Tables.Append?tbl?
End?Sub?
5.3?創(chuàng)建索引范例?
如下代碼演示如何創(chuàng)建新的索引。索引針對(duì)表的兩個(gè)列建立。?
Sub?CreateIndex()?
?Dim?tbl?As?New?Table?
?Dim?idx?As?New?ADOX.Index?
?Dim?cat?As?New?ADOX.Catalog?
'?打開(kāi)目錄。?
?'?打開(kāi)目錄。?
?cat.ActiveConnection?=?_??
?"Provider=Microsoft.Jet.OLEDB.4.0;"?&?_?
?"Data?Source=c:\Program?Files\Microsoft?Office\"?&?_?
?"Office\Samples\Northwind.mdb;"?
?'?定義表并將其追加到目錄?
?tbl.Name?=?"MyTable"?
?tbl.Columns.Append?"Column1",?adInteger?
?tbl.Columns.Append?"Column2",?adInteger?
?tbl.Columns.Append?"Column3",?adVarWChar,?50?
?cat.Tables.Append?tbl?
?'?定義多列索引?
?idx.Name?=?"multicolidx"?
?idx.Columns.Append?"Column1"?
?idx.Columns.Append?"Column2"?
?'?將索引追加到表上?
?tbl.Indexes.Append?idx?
End?Sub?
5.4?創(chuàng)建關(guān)鍵字范例?
如下代碼演示如何創(chuàng)建新的外部關(guān)鍵字。假定已存在兩個(gè)表（Customers?和?orders）。?
Sub?CreateKey()?
?Dim?kyForeign?As?New?ADOX.Key?
?Dim?cat?As?New?ADOX.Catalog?
?cat.ActiveConnection?=?"Provider=Microsoft.Jet.OLEDB.4.0;"?&?_?
?"Data?Source=c:\Program?Files\Microsoft?Office\"?&?_?
?"Office\Samples\Northwind.mdb;"?
?kyForeign.Name?=?"CustOrder"?
?kyForeign.Type?=?adKeyForeign?
?kyForeign.RelatedTable?=?"Customers"?
?kyForeign.Columns.Append?"CustomerId"?
?kyForeign.Columns("CustomerId").RelatedColumn?=?"CustomerId"?
?kyForeign.UpdateRule?=?adRICascade?
?cat.Tables("Orders").Keys.Append?kyForeign?
End?Sub? ==========================================
最近偶然要做一個(gè)小型的數(shù)據(jù)庫(kù)管理系統(tǒng)，為了方便用戶無(wú)需自己用access創(chuàng)建數(shù)據(jù)庫(kù)，所以，我必須設(shè)法讓程序能夠創(chuàng)建數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)表以及判斷數(shù)據(jù)庫(kù)表的存在。下面的程序代碼希望能夠?yàn)橛龅竭@方面困難的朋友提供幫助：(2008/11/5重新編輯)
//1.引入ado庫(kù)?
#import "c:/Program Files/Common Files/System/ADO/Msadox.dll" ?rename_namespace("ADOCG") rename("EOF", "adoXEOF") rename("DataTypeEnum","adoXDataTypeEnum") ?
#import "C:/Program Files/Common Files/System/ADO/msado15.dll" named_guids rename("EOF","adoEOF"), rename("BOF","adoBOF")?
using namespace ADODB; ?
using namespace ADOCG;
#define TESTHR(x) if FAILED(x) _com_issue_error(x);?
//2.創(chuàng)建ACCESS數(shù)據(jù)庫(kù) ?
BOOL CDeRenDlg::CreateMdb(CString strDBName)
{?
CString strMdbConn = "Provider='Microsoft.JET.OLEDB.4.0';Data source = " + strDBName;
try
{
HRESULT hr = S_OK;?
_CatalogPtr pCatalog = NULL;
_bstr_t cnnstring(strMdbConn);
TESTHR(hr = pCatalog.CreateInstance(__uuidof (Catalog)));
pCatalog->Create(cnnstring);
}
catch(_com_error e)
{
_bstr_t bstrDescription(e.Description());
CString strErro=CString(_T("創(chuàng)建ACCEESS數(shù)據(jù)庫(kù)出錯(cuò): "))?
+ (LPCSTR)e.Description()
+ CString(_T("Create ACCESS DB error: "))
+ (LPCSTR)e.Description();
AfxMessageBox(strErro);
return FALSE;
}
return TRUE;
}
//3.創(chuàng)建數(shù)據(jù)庫(kù)表-材料表?
BOOL CDeRenDlg::CreateTable( CString strTabName)
{
HRESULT hr = S_OK;
_CatalogPtr pCatalog = NULL;
_TablePtr pTableNew = NULL;
_IndexPtr pIndexNew = NULL;
_IndexPtr pIndex ?= NULL;
_ColumnPtr pColumn ?= NULL;
CString strConn,strDBName;
strDBName=this->GetDBName(m_nDBYear);
CFileFind ff;
if(!ff.FindFile(strDBName))
{
AfxMessageBox("數(shù)據(jù)庫(kù)不存在，請(qǐng)先在高級(jí)設(shè)置對(duì)話框中創(chuàng)建數(shù)據(jù)庫(kù)！");
return FALSE;
}
strConn="Provider='Microsoft.JET.OLEDB.4.0';Data source = " + strDBName;
_bstr_t strcnn(strConn);
try
{
TESTHR(hr = pCatalog.CreateInstance (__uuidof(Catalog)));
TESTHR(hr = pTableNew.CreateInstance(__uuidof(Table)));
TESTHR(hr = pIndexNew.CreateInstance(__uuidof(Index)));
TESTHR(hr = pIndex.CreateInstance ? (__uuidof(Index)));
TESTHR(hr = pColumn.CreateInstance ?(__uuidof(Column)));
// 連接?
pCatalog->PutActiveConnection(strcnn);
// 表名?
pTableNew->Name = _bstr_t(strTabName);
pTableNew->ParentCatalog = pCatalog;
//?
//m_pTableNew->Columns->Append("ContactId", ::adInteger,0); ? ? ? ? ??
//m_pTableNew->Columns->GetItem("ContactId")->Properties->GetItem("AutoIncrement")->Value = true;?
// 加入字段?
CString str;
str = _T("公司名稱");//"公司名稱";?
pTableNew->Columns->Append(_variant_t(str), ADOCG::adVarWChar, 50);
pTableNew->Columns->GetItem(_variant_t(str))->Properties->GetItem("Jet OLEDB:Allow Zero Length")->Value =false; //將必填字段設(shè)置為否?
str = _T("產(chǎn)品名稱");//"產(chǎn)品名稱";?
pTableNew->Columns->Append(_variant_t(str), ADOCG::adVarWChar, 50);
pTableNew->Columns->GetItem(_variant_t(str))->Properties->GetItem("Jet OLEDB:Allow Zero Length")->Value =false;
str = _T("產(chǎn)品規(guī)格");//"產(chǎn)品名稱";?
pTableNew->Columns->Append(_variant_t(str), ADOCG::adVarWChar, 50);
pTableNew->Columns->GetItem(_variant_t(str))->Properties->GetItem("Jet OLEDB:Allow Zero Length")->Value =false;
//* ? ? ??
// 加入主鍵?
pIndexNew->Name = "pryIndex";//"日期";?
pIndexNew->Columns->Append(_variant_t(str), ADOCG::adVarWChar, 50); ? ? ? ??
pIndexNew->PutPrimaryKey(-1);
pIndexNew->PutUnique(-1);
pTableNew->Indexes->Append(_variant_t ((IDispatch*)pIndexNew));
//*/?
// 加入表中?
pCatalog->Tables->Append(_variant_t ((IDispatch*)pTableNew)); ? ? ? ?
pCatalog->Tables->Refresh();
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
AfxMessageBox(e.Description());
return FALSE;
}
return TRUE;
}
//4.判斷一個(gè)表在數(shù)據(jù)庫(kù)中是否存在?
BOOL CDeRenDlg::IsTalbeExit(CString strTable)
{
try?
{?
if(!m_adoConnection.IsOpen()) return FALSE;
_RecordsetPtr ? pRstSchema ? = ? NULL;
pRstSchema=m_adoConnection.OpenSchema(adSchemaTables);
while(!pRstSchema->adoEOF)?
{?
_bstr_t table_name = pRstSchema->Fields->GetItem("TABLE_NAME")->Value;
_bstr_t table_type = pRstSchema->Fields->GetItem("TABLE_TYPE")->Value;
if ( strcmp(((LPCSTR)table_type),"TABLE")==0)
{
if(strTable.Compare((LPCSTR)table_name)==0)return ?true;
}?
pRstSchema->MoveNext();?
}
if(pRstSchema) pRstSchema->Close(); /*&&(pRstSchema->State==ADODB::adStateOpen)*/
}
catch(_com_error e)// ?
{?
::MessageBox(NULL,e.Description(),"Error",MB_OK);
return FALSE;
}
return false;
}
總結(jié)
以上是生活随笔為你收集整理的ADO学习（七）ADOX相关知识的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： TCP安全问题浅析
- 下一篇： win32中如何使用CString