1780 lines
38 KiB
C++
1780 lines
38 KiB
C++
#include "stdafx.h"
|
|
#include "UIIImpADORXServer.h"
|
|
#include "UIIVariantUtility.h"
|
|
|
|
#ifdef _DEBUG
|
|
#undef THIS_FILE
|
|
static char THIS_FILE[]=__FILE__;
|
|
#define new DEBUG_NEW
|
|
#endif
|
|
|
|
CUIIImpADORXServer::CUIIImpADORXServer()
|
|
{
|
|
//初始化COM调用
|
|
if(FAILED(::CoInitialize(NULL)))
|
|
{
|
|
AfxMessageBox(_T("初始化Com失败!"));
|
|
}
|
|
m_bSuccessState = FALSE;
|
|
m_ConPtr = NULL;
|
|
m_sConnection = _T("");
|
|
m_sDatabase = _T("");
|
|
}
|
|
|
|
CUIIImpADORXServer::~CUIIImpADORXServer()
|
|
{
|
|
if(m_ConPtr != NULL && m_bSuccessState)
|
|
{
|
|
m_ConPtr->Close();
|
|
}
|
|
m_bSuccessState = FALSE;
|
|
}
|
|
|
|
//建立连接、连接数据库，打开数据库[access本地]
|
|
BOOL CUIIImpADORXServer::InitADOServer(LPCTSTR pszDbFileName)
|
|
{
|
|
if (pszDbFileName == NULL) return FALSE;
|
|
|
|
//如果打开则初始化
|
|
if(m_bSuccessState)
|
|
{
|
|
CloseAll();
|
|
}
|
|
|
|
//初始化数据库连接
|
|
if(FAILED(m_ConPtr.CreateInstance(__uuidof(Connection))))
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
HRESULT hr = 0;
|
|
try
|
|
{
|
|
//示例
|
|
//mdb : sDbFileName="DRIVER={Microsoft Access Driver (*.mdb)};"
|
|
//"DBQ=c:\\mydata.mdb;UID=sa;PWD=";
|
|
|
|
//xls : Driver={Microsoft Excel Driver (*.xls)};"
|
|
//"DBQ=c:\\mydata.xls;ReadOnly=false;"
|
|
|
|
/*
|
|
//https://www.microsoft.com/zh-cn/download/details.aspx?id=54920
|
|
* 使用 ODBC 连接到 Microsoft Office Access 数据的应用程序开发人员，请将连接字符串设置为
|
|
“Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
|
|
使用 ODBC 连接到 Microsoft Office Excel 数据的应用程序开发人员，请将连接字符串设置为
|
|
“Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”
|
|
*/
|
|
|
|
CString sExt,sConnect = pszDbFileName;
|
|
CString sDbFileName = pszDbFileName;
|
|
sDbFileName.MakeLower();
|
|
if (sDbFileName.GetLength() > 4)
|
|
{
|
|
int nPos = sDbFileName.ReverseFind(_T('.'));
|
|
if (nPos != -1)
|
|
{
|
|
sExt = sDbFileName.Mid(nPos);
|
|
}
|
|
|
|
if (sExt.CompareNoCase( _T(".mdb")) == 0 ||
|
|
sExt.CompareNoCase( _T("*.accdb")) == 0 )
|
|
{
|
|
#ifdef _WIN64
|
|
sConnect.Format(_T("DRIVER={Microsoft Access Driver (*.mdb,*.accdb)};")
|
|
_T("DBQ=%s;UID='';PWD=''"), sDbFileName);
|
|
#else
|
|
sConnect.Format(_T("DRIVER={Microsoft Access Driver (*.mdb)};")
|
|
_T("DBQ=%s;UID=;PWD="), sDbFileName);
|
|
#endif
|
|
}
|
|
else if (sExt.Find(_T(".xls")) != -1)//xls xlsx xlsm xlsb
|
|
{
|
|
#ifdef _WIN64
|
|
//sConnect.Format(_T("DRIVER={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};")
|
|
// _T("DBQ=%s;UID='';PWD=''"), sDbFileName);
|
|
|
|
sConnect.Format(_T("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%s;Extended Properties='Excel 12.0; HDR=YES; IMEX=3'"), sDbFileName);
|
|
#else
|
|
sConnect.Format(_T("DRIVER={Microsoft Excel Driver (*.xls)};")
|
|
_T("DBQ=%s;UID=;PWD="), sDbFileName);
|
|
#endif
|
|
}
|
|
else
|
|
{
|
|
m_ConPtr.Release();
|
|
m_ConPtr = NULL;
|
|
return FALSE;
|
|
}
|
|
}
|
|
m_ConPtr->ConnectionTimeout = 60;
|
|
hr = m_ConPtr->Open(_bstr_t(sConnect),_T(""),_T(""),adModeUnknown);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
//进行异常捕捉
|
|
DisplayADOException(er);
|
|
AfxMessageBox(er.ErrorMessage());
|
|
m_bSuccessState = FALSE;
|
|
m_ConPtr.Release();
|
|
m_ConPtr = NULL;
|
|
return FALSE;
|
|
}
|
|
m_sConnection = pszDbFileName;
|
|
m_bSuccessState = TRUE;
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::InitADOServer(LPCTSTR pszConnection,bool bDisplayException,ConnectOptionEnum OptionEnum)
|
|
{
|
|
if (pszConnection == NULL) return FALSE;
|
|
|
|
//如果打开则初始化
|
|
if(m_bSuccessState)
|
|
{
|
|
CloseAll();
|
|
}
|
|
|
|
//初始化数据库连接
|
|
if(FAILED(m_ConPtr.CreateInstance(__uuidof(Connection))))
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
//示例:
|
|
//_bstr_t strCnn("Provider=sqloledb;Data Source=MyServer;"
|
|
//"Initial Catalog=pubs;User Id=sa;Password=;");
|
|
try
|
|
{
|
|
//以数据源方式连接数据库
|
|
m_ConPtr->Open(_bstr_t(pszConnection),_T(""),_T(""),OptionEnum);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
//进行异常捕捉
|
|
if( bDisplayException )
|
|
{
|
|
DisplayADOException(er,true);
|
|
}
|
|
m_bSuccessState = FALSE;
|
|
m_ConPtr.Release();
|
|
m_ConPtr = NULL;
|
|
return FALSE;
|
|
}
|
|
m_sConnection = pszConnection;
|
|
m_bSuccessState = TRUE;
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::InitADOServer(LPCTSTR pszDSN,LPCTSTR pszUserID,LPCTSTR pszPassword,ConnectModeEnum eMode,CursorTypeEnum eCursor)
|
|
{
|
|
if (pszDSN == NULL || pszUserID == NULL)
|
|
return FALSE;
|
|
|
|
if (pszPassword == NULL) pszPassword = _T("");
|
|
|
|
//如果打开则初始化
|
|
if(m_bSuccessState)
|
|
{
|
|
CloseAll();
|
|
}
|
|
|
|
//初始化数据库连接
|
|
if(FAILED(m_ConPtr.CreateInstance(__uuidof(Connection))))
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
//示例:
|
|
//_bstr_t strCnn("DSN=CAPP;UID=sa;PWD="
|
|
|
|
//定义连接描述
|
|
CString sXTCAPPDescr;
|
|
sXTCAPPDescr.Format(_T("DSN=%s; UID=%s; PWD=%s"),pszDSN,pszUserID,pszPassword);
|
|
|
|
m_ConPtr->Mode = eMode;
|
|
|
|
try
|
|
{
|
|
//以数据源方式连接数据库
|
|
m_ConPtr->Open(_bstr_t(sXTCAPPDescr),_T(""),_T(""),eCursor);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
//进行异常捕捉
|
|
DisplayADOException(er);
|
|
AfxMessageBox(er.ErrorMessage());
|
|
m_bSuccessState = FALSE;
|
|
return FALSE;
|
|
m_ConPtr.Release();
|
|
m_ConPtr = NULL;
|
|
}
|
|
|
|
m_sConnection = sXTCAPPDescr;
|
|
m_bSuccessState = TRUE;
|
|
return TRUE;
|
|
}
|
|
|
|
void CUIIImpADORXServer::AppendAssocDatabase(LPCTSTR pszKey,LPCTSTR pszAssocDb)
|
|
{
|
|
m_mapAssocDb.SetAt(pszKey,pszAssocDb);
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::AssocDatabase(LPCTSTR pszKey,CString& sAssocDb)
|
|
{
|
|
return m_mapAssocDb.Lookup(pszKey,sAssocDb);
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::IsSQLSrvTableExist(LPCTSTR pszTableName,LPCTSTR pszDbName)
|
|
{
|
|
CString sSQL;
|
|
sSQL.Format(_T("SELECT name FROM %s.dbo.sysobjects WHERE name = '%s'"),pszDbName,pszTableName);
|
|
|
|
CStringArray arValue;
|
|
this->GetSelData(sSQL,arValue);
|
|
|
|
if(arValue.GetSize() == 1)
|
|
{
|
|
return TRUE;
|
|
}
|
|
return FALSE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::CreateOLEDBTable(LPCTSTR pszTableName,Ttable& arFields,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
//1.先判断要创建的表是否存在[外部判断]
|
|
CString strSQL,sTemp;
|
|
int i;
|
|
int nSize = (int)arFields.GetSize();
|
|
CUIITableField fieldInfo;
|
|
CString keyStr = _T(""),sFormat;
|
|
|
|
//创建标题明细数据库
|
|
if (pszDbName == NULL)
|
|
{
|
|
strSQL.Format(_T("CREATE TABLE %s ("),pszTableName);
|
|
}
|
|
else
|
|
{
|
|
strSQL.Format(_T("CREATE TABLE [%s].[dbo].[%s] ("),pszDbName,pszTableName);
|
|
}
|
|
|
|
for( i=0; i<nSize; i++ )
|
|
{
|
|
fieldInfo = arFields.GetAt(i);
|
|
|
|
if( fieldInfo.m_sFieldType == _T("nvarchar") ||
|
|
fieldInfo.m_sFieldType == _T("varchar"))
|
|
{
|
|
sFormat = _T("[%s] %s (%d) ");
|
|
if(!fieldInfo.m_bPrimaryKey)
|
|
{
|
|
if( fieldInfo.m_bIsAllowNull)
|
|
{
|
|
sFormat += _T("NULL,");
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("NOT NULL,");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("PRIMARY KEY,");
|
|
}
|
|
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName,fieldInfo.m_sFieldType, fieldInfo.m_nSize);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("char") )
|
|
{
|
|
sFormat = _T("[%s] %s (%d) ");
|
|
if(!fieldInfo.m_bPrimaryKey)
|
|
{
|
|
if( fieldInfo.m_bIsAllowNull)
|
|
{
|
|
sFormat += _T("NULL,");
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("NOT NULL,");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("PRIMARY KEY,");
|
|
}
|
|
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName,fieldInfo.m_sFieldType, fieldInfo.m_nSize);
|
|
}
|
|
else if(fieldInfo.m_sFieldType.CompareNoCase(_T("int")) == 0 ||
|
|
fieldInfo.m_sFieldType.CompareNoCase(_T("smallint")) == 0)
|
|
{
|
|
sFormat = _T("[%s] int ");
|
|
if(!fieldInfo.m_bPrimaryKey)
|
|
{
|
|
if( fieldInfo.m_bIsAllowNull)
|
|
{
|
|
sFormat += _T("NULL,");
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("NOT NULL,");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
//sFormat += _T("NOT NULL,");
|
|
sFormat += _T("IDENTITY (1,1) PRIMARY KEY NOT NULL,");//标识递增1
|
|
//sFormat += _T("IDENTITY (1,1) NOT NULL,");//标识递增1
|
|
//sFormat += _T("PRIMARY KEY NOT NULL,");
|
|
}
|
|
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName);
|
|
}
|
|
else if( fieldInfo.m_sFieldType == _T("float") )
|
|
{
|
|
sFormat = _T("[%s] float ");
|
|
if(!fieldInfo.m_bPrimaryKey)
|
|
{
|
|
if( fieldInfo.m_bIsAllowNull)
|
|
{
|
|
sFormat += _T("NULL,");
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("NOT NULL,");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("PRIMARY KEY,");
|
|
}
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("image"))
|
|
{
|
|
sFormat = _T("[%s] image NULL,");
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("binary"))
|
|
{
|
|
sFormat = _T("[%s] binary (%d) NULL,");
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName,fieldInfo.m_nSize);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("datetime"))
|
|
{
|
|
sFormat = _T("[%s] datetime DEFAULT GETDATE(),");
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("text") ||
|
|
fieldInfo.m_sFieldType == _T("ntext"))
|
|
{
|
|
sFormat = _T("[%s] %s NULL,");
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName,fieldInfo.m_sFieldType);
|
|
}
|
|
else if(fieldInfo.m_sFieldType == _T("real") ||
|
|
fieldInfo.m_sFieldType == _T("double") )
|
|
{
|
|
sFormat = _T("[%s] real ");
|
|
if(!fieldInfo.m_bPrimaryKey)
|
|
{
|
|
if( fieldInfo.m_bIsAllowNull)
|
|
{
|
|
sFormat += _T("NULL,");
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("NOT NULL,");
|
|
}
|
|
}
|
|
else
|
|
{
|
|
sFormat += _T("PRIMARY KEY,");
|
|
}
|
|
sTemp.Format(sFormat, fieldInfo.m_sFieldName);
|
|
}
|
|
|
|
if(sTemp.GetLength() > 0)
|
|
{
|
|
strSQL += sTemp;
|
|
}
|
|
sTemp = _T("");
|
|
}
|
|
|
|
int nlen = strSQL.GetLength();
|
|
if(nlen > 0)
|
|
{
|
|
strSQL.SetAt(nlen - 1, _T(')'));
|
|
}
|
|
else
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
BOOL bSuccess = FALSE;
|
|
try
|
|
{
|
|
bSuccess = ExecuteSQL(strSQL);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return bSuccess;
|
|
}
|
|
return bSuccess;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::CreateSQLSrvTable(LPCTSTR pszTableName,Ttable &arFields,LPCTSTR pszDbName)
|
|
{
|
|
//1.先判断要创建的表是否存在
|
|
BOOL bIsExist = this->IsSQLSrvTableExist(pszTableName,pszDbName);
|
|
if(bIsExist)
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
return CreateOLEDBTable(pszTableName,arFields,pszDbName);
|
|
}
|
|
|
|
|
|
//获取表的结构信息
|
|
BOOL CUIIImpADORXServer::GetSQLSrvTableStru(LPCTSTR pszTableName,Ttable &arFields,LPCTSTR pszDbName)
|
|
{
|
|
CString sSQL,sName;
|
|
|
|
if(pszDbName == NULL)//当前数据库
|
|
{
|
|
sSQL.Format(_T("SELECT name, xtype, length FROM syscolumns WHERE id = ")
|
|
_T("(SELECT id FROM sysobjects WHERE name = '%s') ORDER BY colid"),pszTableName);
|
|
|
|
}
|
|
else
|
|
{
|
|
sSQL.Format(_T("SELECT name, xtype, length FROM %s.dbo.syscolumns WHERE id = ")
|
|
_T("(SELECT id FROM %s.dbo.sysobjects WHERE name = '%s') ORDER BY colid"),pszDbName,pszDbName,pszTableName);
|
|
}
|
|
|
|
CUIIValueTable table;
|
|
this->GetSelData(sSQL,table);
|
|
|
|
if(pszDbName == NULL)
|
|
{
|
|
sSQL = _T("SELECT xtype,name from systypes");
|
|
}
|
|
else
|
|
{
|
|
sSQL.Format(_T("SELECT xtype,name from %s.dbo.systypes"),pszDbName);
|
|
}
|
|
|
|
CMapStringToString mapNametype;
|
|
this->GetSelData(sSQL,mapNametype);
|
|
|
|
int i = 0;
|
|
CUIITableField field;
|
|
CStringArray arValue;
|
|
int nSize = table.GetTableLogicSize();
|
|
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
arValue.RemoveAll();
|
|
table.GetValueRecord(i,arValue);
|
|
|
|
if(arValue.GetSize() == 3)
|
|
{
|
|
field.m_sFieldName = arValue.GetAt(0);
|
|
|
|
field.m_sFieldName.TrimLeft(_T('['));
|
|
field.m_sFieldName.TrimRight(_T(']'));
|
|
|
|
mapNametype.Lookup(arValue.GetAt(1),sName);
|
|
field.m_sFieldType = sName;
|
|
field.m_nSize = _ttoi(arValue.GetAt(2));
|
|
arFields.Add(field);
|
|
}
|
|
}
|
|
return arFields.GetSize() > 0;
|
|
}
|
|
|
|
//获取表的结构信息
|
|
BOOL CUIIImpADORXServer::GetSQLSrvTableStru(LPCTSTR pszTableName,CStringList &listField,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
CString sSQL;
|
|
if(pszDbName = NULL)
|
|
{
|
|
sSQL.Format(_T("SELECT name FROM syscolumns WHERE id = ")
|
|
_T("(SELECT id FROM sysobjects WHERE name = '%s') ORDER BY colid"),pszTableName);
|
|
}
|
|
else
|
|
{
|
|
sSQL.Format(_T("SELECT name FROM %s.dbo.syscolumns WHERE id = ")
|
|
_T("(SELECT id FROM %s.dbo.sysobjects WHERE name = '%s') ORDER BY colid"),pszDbName,pszDbName,pszTableName);
|
|
}
|
|
this->GetSelData(sSQL,listField);
|
|
return TRUE;
|
|
}
|
|
|
|
//获取表的结构信息
|
|
BOOL CUIIImpADORXServer::GetTableStructInfoEx(LPCTSTR pszTableName,Ttable& arFields)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
_bstr_t _bstr_Cnn = m_ConPtr->GetConnectionString();
|
|
_RecordsetPtr pRecord = NULL;
|
|
HRESULT hr = pRecord.CreateInstance(__uuidof(Recordset));
|
|
if(FAILED(hr))
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
hr = pRecord->Open( _bstr_t(pszTableName), _bstr_Cnn, adOpenForwardOnly,
|
|
adLockReadOnly, adCmdTable);
|
|
if(FAILED(hr))
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
int nSize = pRecord->Fields->Count;
|
|
|
|
CUIITableField strufield;
|
|
FieldPtr pFld = NULL;
|
|
|
|
int nLen = 0;
|
|
CString sSQLType;
|
|
for (short iIndex = 0; iIndex < nSize;iIndex++)
|
|
{
|
|
pFld = pRecord->Fields->GetItem(iIndex);
|
|
strufield.m_sFieldName.Format(_T("%s"),(LPCTSTR)pFld->GetName());
|
|
strufield.m_sFieldType = GetFieldType(pFld->GetType());
|
|
strufield.m_nSize = (long)pFld->GetDefinedSize();
|
|
|
|
nLen = ConverToSQLType(strufield.m_sFieldType,strufield.m_nSize,sSQLType);
|
|
|
|
strufield.m_sFieldType = sSQLType;
|
|
strufield.m_nSize = nLen;
|
|
|
|
arFields.Add(strufield);
|
|
}
|
|
|
|
return TRUE;
|
|
}
|
|
|
|
//类型: 字符型 : varchar 文本 : text 字节 : varbinary
|
|
// ole型 : image 长整型: int 实数型: float
|
|
// double型: real 短整型: smallint
|
|
// 时间 : datetime
|
|
|
|
int CUIIImpADORXServer::ConverToSQLType(LPCTSTR pszType,int nDefSize,CString& sSQLType)
|
|
{
|
|
if (pszType == NULL) return nDefSize;
|
|
|
|
int nLen = 50;
|
|
sSQLType = _T("varchar");
|
|
CString sType = pszType;
|
|
|
|
if(sType.CompareNoCase(_T("adVarChar")) == 0)
|
|
{
|
|
sSQLType = _T("varchar");
|
|
return nDefSize;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adLongVarChar")) == 0)
|
|
{
|
|
sSQLType = _T("text");
|
|
return 16;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adVarbinary")) == 0)
|
|
{
|
|
sSQLType = _T("varbinary");
|
|
return 50;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adLongVarBinary")) == 0)
|
|
{
|
|
sSQLType = _T("image");
|
|
return 16;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adInteger")) == 0)
|
|
{
|
|
sSQLType = _T("int");
|
|
return 4;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adDouble")) == 0)
|
|
{
|
|
sSQLType = _T("float");
|
|
return 8;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adSingle")) == 0)
|
|
{
|
|
sSQLType = _T("real");
|
|
return 4;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adSmallInt")) == 0)
|
|
{
|
|
sSQLType = _T("smallint");
|
|
return 2;
|
|
}
|
|
else if(sType.CompareNoCase(_T("adDBTimeStamp")) == 0)
|
|
{
|
|
sSQLType = _T("datetime");
|
|
return 8;
|
|
}
|
|
return nLen;
|
|
}
|
|
|
|
CString CUIIImpADORXServer::GetFieldType(int propType)
|
|
{
|
|
CString strMsg;
|
|
switch(propType)
|
|
{
|
|
case adBigInt:
|
|
strMsg = _T("adBigInt");
|
|
break;
|
|
case adBinary:
|
|
strMsg = _T("adBinary");
|
|
break;
|
|
case adBoolean:
|
|
strMsg = _T("adBoolean");
|
|
break;
|
|
case adBSTR:
|
|
strMsg = _T("adBSTR");
|
|
break;
|
|
case adChapter:
|
|
strMsg = _T("adChapter");
|
|
break;
|
|
case adChar:
|
|
strMsg = _T("adChar");
|
|
break;
|
|
case adCurrency:
|
|
strMsg = _T("adCurrency");
|
|
break;
|
|
case adDate:
|
|
strMsg = _T("adDate");
|
|
break;
|
|
case adDBDate:
|
|
strMsg = _T("adDBDate");
|
|
break;
|
|
case adDBTime:
|
|
strMsg = _T("adDBTime");
|
|
break;
|
|
case adDBTimeStamp:
|
|
strMsg = _T("adDBTimeStamp");
|
|
break;
|
|
case adDecimal:
|
|
strMsg = _T("adDecimal");
|
|
break;
|
|
case adDouble:
|
|
strMsg = _T("adDouble");
|
|
break;
|
|
case adEmpty:
|
|
strMsg = _T("adEmpty");
|
|
break;
|
|
case adError:
|
|
strMsg = _T("adError");
|
|
break;
|
|
case adFileTime:
|
|
strMsg = _T("adFileTime");
|
|
break;
|
|
case adGUID:
|
|
strMsg = _T("adGUID");
|
|
break;
|
|
case adIDispatch:
|
|
strMsg = _T("adIDispatch");
|
|
break;
|
|
case adInteger:
|
|
strMsg = _T("adInteger");
|
|
break;
|
|
case adIUnknown:
|
|
strMsg = _T("adIUnknown");
|
|
break;
|
|
case adLongVarBinary:
|
|
strMsg = _T("adLongVarBinary");
|
|
break;
|
|
case adLongVarChar:
|
|
strMsg = _T("adLongVarChar");
|
|
break;
|
|
case adLongVarWChar:
|
|
strMsg = _T("adLongVarWChar");
|
|
break;
|
|
case adNumeric:
|
|
strMsg = _T("adNumeric");
|
|
break;
|
|
case adPropVariant:
|
|
strMsg = _T("adPropVariant");
|
|
break;
|
|
case adSingle:
|
|
strMsg = _T("adSingle");
|
|
break;
|
|
case adSmallInt:
|
|
strMsg = _T("adSmallInt");
|
|
break;
|
|
case adTinyInt:
|
|
strMsg = _T("adTinyInt");
|
|
break;
|
|
case adUnsignedBigInt:
|
|
strMsg = _T("adUnsignedBigInt");
|
|
break;
|
|
case adUnsignedInt:
|
|
strMsg = _T("adUnsignedInt");
|
|
break;
|
|
case adUnsignedSmallInt:
|
|
strMsg = _T("adUnsignedSmallInt");
|
|
break;
|
|
case adUnsignedTinyInt:
|
|
strMsg = _T("adUnsignedTinyInt");
|
|
break;
|
|
case adUserDefined:
|
|
strMsg = _T("adUserDefined");
|
|
break;
|
|
case adVarBinary:
|
|
strMsg = _T("adVarBinary");
|
|
break;
|
|
case adVarChar:
|
|
strMsg = _T("adVarChar");
|
|
break;
|
|
case adVariant:
|
|
strMsg = _T("adVariant");
|
|
break;
|
|
case adVarNumeric:
|
|
strMsg = _T("adVarNumeric");
|
|
break;
|
|
case adVarWChar:
|
|
strMsg = _T("adVarWChar");
|
|
break;
|
|
case adWChar:
|
|
// Unicode: Leaving as char type
|
|
strMsg = _T("adWChar");
|
|
break;
|
|
default:
|
|
strMsg = _T("*UNKNOWN*");
|
|
break;
|
|
}
|
|
return strMsg;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::DeleteTable(LPCTSTR pszTableName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
CString strSQL;
|
|
strSQL.Format(_T("DROP TABLE [%s]"),pszTableName);
|
|
ExecuteSQL(strSQL);
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::DeleteTable(CStringArray &arTableNames)
|
|
{
|
|
int nSize = (int)arTableNames.GetSize();
|
|
int i;
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
DeleteTable(arTableNames.GetAt(i));
|
|
}
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::DeleteTableFields(LPCTSTR pszTableName,CStringArray &arFields,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
int nSize = (int)arFields.GetSize();
|
|
int i;
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
DeleteTableFields(pszTableName,arFields.GetAt(i),pszDbName);
|
|
}
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::DeleteTableFields(LPCTSTR pszTableName,LPCTSTR pszFields,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL ||
|
|
pszFields == NULL ||
|
|
pszDbName == NULL)
|
|
return FALSE;
|
|
|
|
CString strSQL;
|
|
strSQL.Format(_T("ALTER TABLE [%s.dbo.%s] DROP COLUMN [%s]"),pszDbName,pszTableName,pszFields);
|
|
ExecuteSQL(strSQL);
|
|
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::AppendTableFields(LPCTSTR pszTableName,Ttable &arFields,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
int nSize = (int)arFields.GetSize();
|
|
int i;
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
AppendTableFields(pszTableName,arFields.GetAt(i),pszDbName);
|
|
}
|
|
return TRUE;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::AppendTableFields(LPCTSTR pszTableName, CUIITableField& arFields,LPCTSTR pszDbName)
|
|
{
|
|
if (pszTableName == NULL) return FALSE;
|
|
|
|
CString strSQL,sFieldType;
|
|
sFieldType = arFields.m_sFieldType;
|
|
|
|
if(sFieldType.CompareNoCase(_T("varchar")) == 0 ||
|
|
sFieldType.CompareNoCase(_T("nvarchar")) == 0)
|
|
{
|
|
strSQL.Format(_T("ALTER TABLE [%s.dbo.%s] ADD [%s] %s(%d)"),pszDbName,pszTableName,arFields.m_sFieldName,
|
|
arFields.m_sFieldType,arFields.m_nSize);
|
|
}
|
|
else
|
|
{
|
|
strSQL.Format(_T("ALTER TABLE [%s.dbo.%s] ADD [%s] %s"),pszDbName,pszTableName,arFields.m_sFieldName,
|
|
arFields.m_sFieldType);
|
|
}
|
|
ExecuteSQL(strSQL);
|
|
return TRUE;
|
|
}
|
|
|
|
//获得数据库中所有的表名
|
|
void CUIIImpADORXServer::GetSQLSrvTableNames(CStringArray &arTables,LPCTSTR pszDbName)
|
|
{
|
|
if (pszDbName == NULL) return;
|
|
|
|
CString sSQL;
|
|
sSQL.Format(_T("SELECT name FROM %s.dbo.sysobjects where xtype='U'"),pszDbName);
|
|
|
|
this->GetSelData(sSQL,arTables);
|
|
}
|
|
|
|
//获得数据库中所有的表名
|
|
void CUIIImpADORXServer::GetSQLSrvTableNames(CStringList &listTables,LPCTSTR pszDbName)
|
|
{
|
|
if (pszDbName == NULL) return;
|
|
CString sSQL;
|
|
sSQL.Format(_T("SELECT name FROM %s.dbo.sysobjects where xtype='U'"),pszDbName,pszDbName);
|
|
|
|
this->GetSelData(sSQL,listTables);
|
|
}
|
|
|
|
//获得数据库中所有的表名
|
|
void CUIIImpADORXServer::GetOLDBTableNameList(CStringList &listTables,LPCTSTR pszTableType /*= _T("TABLE")*/)
|
|
{
|
|
CString sTableText,sTabType,sTableType;
|
|
_RecordsetPtr pRstSchema = NULL;
|
|
|
|
if (pszTableType == NULL) pszTableType = _T("TABLE");
|
|
|
|
//指定连接的数据库
|
|
if(m_ConPtr == NULL)
|
|
{
|
|
AfxMessageBox(_T("数据库未正常连接..."));
|
|
return;
|
|
}
|
|
try
|
|
{
|
|
pRstSchema = m_ConPtr->OpenSchema(adSchemaTables);
|
|
while(!(pRstSchema->EndOfFile))
|
|
{
|
|
_bstr_t table_name = pRstSchema->Fields->GetItem(_T("TABLE_NAME"))->Value.bstrVal;
|
|
_bstr_t table_type = pRstSchema->Fields->GetItem(_T("TABLE_TYPE"))->Value.bstrVal;
|
|
|
|
sTableText.Format(_T("%s"),(LPCTSTR) table_name);
|
|
sTabType.Format(_T("%s"),(LPCTSTR) table_type);
|
|
if(sTabType.CompareNoCase(pszTableType) == 0)
|
|
{
|
|
sTableText.TrimRight();
|
|
listTables.AddTail(sTableText);
|
|
}
|
|
pRstSchema->MoveNext();
|
|
}
|
|
pRstSchema->Close();
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
}
|
|
}
|
|
|
|
//获得数据库中所有的表名
|
|
void CUIIImpADORXServer::GetOLDBTableNameArray(CStringArray &arTables,LPCTSTR pszTableType /*= _T("TABLE")*/)
|
|
{
|
|
CString sTableText,sTabType;
|
|
_RecordsetPtr pRstSchema = NULL;
|
|
if (pszTableType == NULL) pszTableType = _T("TABLE");
|
|
|
|
//指定连接的数据库
|
|
if(m_ConPtr == NULL)
|
|
{
|
|
AfxMessageBox(_T("数据库未正常连接..."));
|
|
return;
|
|
}
|
|
try
|
|
{
|
|
pRstSchema = m_ConPtr->OpenSchema(adSchemaTables);
|
|
while(!(pRstSchema->EndOfFile))
|
|
{
|
|
_bstr_t table_name = pRstSchema->Fields->GetItem(_T("TABLE_NAME"))->Value.bstrVal;
|
|
_bstr_t table_type = pRstSchema->Fields->GetItem(_T("TABLE_TYPE"))->Value.bstrVal;
|
|
|
|
sTableText.Format(_T("%s"),(LPCTSTR) table_name);
|
|
sTabType.Format(_T("%s"),(LPCTSTR) table_type);
|
|
if(sTabType.CompareNoCase(pszTableType) == 0)
|
|
{
|
|
sTableText.TrimRight();
|
|
arTables.Add(sTableText);
|
|
}
|
|
pRstSchema->MoveNext();
|
|
}
|
|
pRstSchema->Close();
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
}
|
|
}
|
|
|
|
//获得数据库中所有的表名
|
|
void CUIIImpADORXServer::GetTableNameArray(CStringArray &arTables,LPCTSTR pszTableType)
|
|
{
|
|
CString sTableText,sTabType;
|
|
_RecordsetPtr pRstSchema = NULL;
|
|
if (pszTableType == NULL) pszTableType = _T("TABLE");
|
|
|
|
//指定连接的数据库
|
|
if(m_ConPtr == NULL)
|
|
{
|
|
AfxMessageBox(_T("数据库未正常连接..."));
|
|
return;
|
|
}
|
|
try
|
|
{
|
|
pRstSchema = m_ConPtr->OpenSchema(adSchemaTables);
|
|
while(!(pRstSchema->EndOfFile))
|
|
{
|
|
_bstr_t table_name = pRstSchema->Fields->GetItem(_T("TABLE_NAME"))->Value.bstrVal;
|
|
_bstr_t table_type = pRstSchema->Fields->GetItem(_T("TABLE_TYPE"))->Value.bstrVal;
|
|
|
|
sTableText.Format(_T("%s"),(LPCTSTR) table_name);
|
|
sTabType.Format(_T("%s"),(LPCTSTR) table_type);
|
|
if(sTabType.CompareNoCase(pszTableType) == 0)
|
|
{
|
|
sTableText.TrimRight();
|
|
arTables.Add(sTableText);
|
|
}
|
|
pRstSchema->MoveNext();
|
|
}
|
|
pRstSchema->Close();
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
}
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::ExecuteSQL(LPCTSTR pstrSQL)
|
|
{
|
|
//命令智能接口指针
|
|
_CommandPtr pCommand(_T("ADODB.Command"));
|
|
|
|
//选择集智能接口指针
|
|
_RecordsetPtr rs;
|
|
|
|
//指定连接的数据库
|
|
if(m_ConPtr == NULL)
|
|
{
|
|
AfxMessageBox(_T("数据库未正常连接..."));
|
|
return FALSE;
|
|
}
|
|
pCommand->ActiveConnection = m_ConPtr;
|
|
pCommand->CommandText = _bstr_t(pstrSQL);
|
|
pCommand->CommandType = adCmdText;
|
|
|
|
CString sEr;
|
|
//执行并返回选择集
|
|
try
|
|
{
|
|
//执行SQL语句
|
|
rs = pCommand->Execute(NULL, NULL, adCmdUnknown);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
#ifdef _DEBUG
|
|
sEr.Format(_T("执行出错的SQL语句: %s\n") ,pstrSQL);
|
|
AfxMessageBox(sEr);
|
|
#endif
|
|
return FALSE;
|
|
}
|
|
return TRUE;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CString& sValue)
|
|
{
|
|
if (psztrSQL == NULL) return -1;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
long lNumFields = 0l,lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
if (pRec->EndOfFile != -1)
|
|
{
|
|
if(lNumFields > 0)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sValue);
|
|
return 1;
|
|
}
|
|
}
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::IsSelEmpty(LPCTSTR pszStrSQL)
|
|
{
|
|
if (pszStrSQL == NULL ) return FALSE;
|
|
|
|
_bstr_t strSQLAdo(pszStrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo,NULL, adCmdText);
|
|
if (pRec == NULL || pRec->EndOfFile == -1)
|
|
{
|
|
return TRUE;
|
|
}
|
|
return FALSE;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CStringArray &arStrValue)
|
|
{
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo,NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l,lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
for(lIndex = 0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
arStrValue.Add(sText);
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CStringArray &arStrValue,int nFr,int nTo)
|
|
{
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo,NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l,lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
pRec->Move(nFr);
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
for(lIndex=0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
arStrValue.Add(sText);
|
|
}
|
|
if (nRows >= (nTo - nFr)) break;
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL, std::vector<CString>& arStrValue)
|
|
{
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l, lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
for (lIndex = 0; lIndex < lNumFields; lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(), sText);
|
|
arStrValue.push_back(sText);
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch (_com_error& er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
//把获得相应的信息填到链表中
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CStringList &listStrValue,BOOL bMakeLower)
|
|
{
|
|
if (psztrSQL == NULL ) return -1;
|
|
try
|
|
{
|
|
CStringArray arStrValue;
|
|
int nResult = GetSelData(psztrSQL,arStrValue);
|
|
if(nResult > -1)
|
|
{
|
|
CString sText;
|
|
int nSize = (int)arStrValue.GetSize();
|
|
int i;
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
sText = arStrValue.GetAt(i);
|
|
//小写
|
|
if(bMakeLower)
|
|
{
|
|
sText.MakeLower();
|
|
}
|
|
listStrValue.AddTail(sText);
|
|
}
|
|
return nSize;
|
|
}
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CStringList &listStrValue,int nFr,int nTo,BOOL bMakeLower)
|
|
{
|
|
if (psztrSQL == NULL ) return -1;
|
|
try
|
|
{
|
|
CStringArray arStrValue;
|
|
int nResult = GetSelData(psztrSQL,arStrValue,nFr,nTo);
|
|
if(nResult > -1)
|
|
{
|
|
CString sText;
|
|
int nSize = (int)arStrValue.GetSize();
|
|
int i;
|
|
for(i = 0;i<nSize;i++)
|
|
{
|
|
sText = arStrValue.GetAt(i);
|
|
//小写
|
|
if(bMakeLower)
|
|
{
|
|
sText.MakeLower();
|
|
}
|
|
listStrValue.AddTail(sText);
|
|
}
|
|
return nSize;
|
|
}
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CUIIValueTable &valueTable,BOOL bUpdateLable)
|
|
{
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL )
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l,lIndex = 0l,i = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
CStringArray arStrValue,arLable;
|
|
|
|
for( i=0; i<lNumFields; i++ )
|
|
{
|
|
CUIIVariantUtility::bstr_t2String(pRec->GetFields()->GetItem(i)->GetName(),sText);
|
|
arLable.Add(sText);
|
|
}
|
|
|
|
//设置标题记录
|
|
if(bUpdateLable)
|
|
{
|
|
valueTable.SetLableRecord(arLable);
|
|
}
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
arStrValue.RemoveAll();
|
|
for(lIndex = 0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
arStrValue.Add(sText);
|
|
}
|
|
valueTable.AddValueRecord(arStrValue);
|
|
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,CUIIValueTable &valueTable,int nFr,int nTo,BOOL bUpdateLable)
|
|
{
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL )
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l,lIndex = 0l,i = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
CStringArray arStrValue,arLable;
|
|
|
|
for( i=0; i<lNumFields; i++ )
|
|
{
|
|
CUIIVariantUtility::bstr_t2String(pRec->GetFields()->GetItem(i)->GetName(),sText);
|
|
arLable.Add(sText);
|
|
}
|
|
|
|
//设置标题记录
|
|
if(bUpdateLable)
|
|
{
|
|
valueTable.SetLableRecord(arLable);
|
|
}
|
|
|
|
int nRows = 0;
|
|
pRec->Move(nFr);
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
arStrValue.RemoveAll();
|
|
for(lIndex=0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
arStrValue.Add(sText);
|
|
}
|
|
valueTable.AddValueRecord(arStrValue);
|
|
if (nRows >= (nTo - nFr)) break;
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
//选取两列,一个为Key,一个为Value建map
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR pszKey_ValueSQL,CMapStringToString &mapKey_Value)
|
|
{
|
|
if (pszKey_ValueSQL == NULL) return -1;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(pszKey_ValueSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sKey,sText,sValue;
|
|
long lNumFields = 0l,lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
if(lNumFields < 2) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
for(lIndex = 0;lIndex<2;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
if(lIndex == 0)
|
|
{
|
|
sKey = sText;
|
|
}
|
|
else
|
|
{
|
|
sValue = sText;
|
|
}
|
|
mapKey_Value.SetAt(sKey,sValue);
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR pszKey_ValueSQL, std::map<CString, CString>& mapKey_Value)
|
|
{
|
|
if (pszKey_ValueSQL == NULL) return -1;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(pszKey_ValueSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sKey, sText, sValue;
|
|
long lNumFields = 0l, lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
if (lNumFields < 2) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
for (lIndex = 0; lIndex < 2; lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(), sText);
|
|
if (lIndex == 0)
|
|
{
|
|
sKey = sText;
|
|
}
|
|
else
|
|
{
|
|
sValue = sText;
|
|
}
|
|
mapKey_Value.insert(std::make_pair(sKey, sValue));
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
//选取一行,获得Field到对应数据的映射
|
|
int CUIIImpADORXServer::GetSelDataEx(LPCTSTR pszSelectSQL,CMapStringToString &mapFieldToValue)
|
|
{
|
|
if (pszSelectSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(pszSelectSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sKey,sText,sValue;
|
|
long lNumFields = 0l,lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
if(lNumFields < 2) return -1;
|
|
|
|
int nRows = 0;
|
|
if(!pRec->EndOfFile)
|
|
{
|
|
for(lIndex = 0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
CUIIVariantUtility::bstr_t2String(pRec->GetFields()->GetItem(lIndex)->GetName(),sKey);
|
|
|
|
mapFieldToValue.SetAt(sKey,sText);
|
|
}
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelDataEx(LPCTSTR psztrSQL, std::vector<std::vector<CString>>& datas)
|
|
{
|
|
datas.clear();
|
|
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL )
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText;
|
|
long lNumFields = 0l,lIndex = 0l,i = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
//列
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if(FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
datas.push_back(std::vector<CString>());
|
|
for(lIndex = 0;lIndex<lNumFields;lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(),sText);
|
|
datas.rbegin()->push_back( sText );
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows ++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
|
|
return (int)datas.size();
|
|
}
|
|
int CUIIImpADORXServer::GetSelDataEx(LPCTSTR psztrSQL, std::vector<std::map<CString, CString>> &datas)
|
|
{
|
|
datas.clear();
|
|
|
|
if (psztrSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sText, sKey;
|
|
long lNumFields = 0l, lIndex = 0l, i = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
//列
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
int nRows = 0;
|
|
while (!pRec->EndOfFile)
|
|
{
|
|
datas.push_back(std::map<CString, CString>());
|
|
for (lIndex = 0; lIndex<lNumFields; lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(), sText);
|
|
CUIIVariantUtility::bstr_t2String(pRec->GetFields()->GetItem(lIndex)->GetName(), sKey);
|
|
datas.rbegin()->insert(std::make_pair(sKey, sText));
|
|
}
|
|
|
|
pRec->MoveNext();
|
|
nRows++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
|
|
return (int)datas.size();
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelDataEx(LPCTSTR pszSelectSQL, std::map<CString, CString>& mapFieldToValue)
|
|
{
|
|
if (pszSelectSQL == NULL) return FALSE;
|
|
try
|
|
{
|
|
_bstr_t strSQLAdo(pszSelectSQL);
|
|
_RecordsetPtr pRec = m_ConPtr->Execute(strSQLAdo, NULL, adCmdText);
|
|
if (pRec == NULL)
|
|
{
|
|
return -1;
|
|
}
|
|
|
|
FieldsPtr pFields;
|
|
CString sKey, sText, sValue;
|
|
long lNumFields = 0l, lIndex = 0l;
|
|
|
|
HRESULT hr = pRec->get_Fields(&pFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
hr = pFields->get_Count(&lNumFields);
|
|
if (FAILED(hr)) return -1;
|
|
|
|
if (lNumFields < 2) return -1;
|
|
|
|
int nRows = 0;
|
|
if (!pRec->EndOfFile)
|
|
{
|
|
for (lIndex = 0; lIndex < lNumFields; lIndex++)
|
|
{
|
|
CUIIVariantUtility::Variant2String(pRec->GetFields()->GetItem(lIndex)->GetValue(), sText);
|
|
CUIIVariantUtility::bstr_t2String(pRec->GetFields()->GetItem(lIndex)->GetName(), sKey);
|
|
|
|
mapFieldToValue.insert(std::make_pair(sKey, sText));
|
|
}
|
|
nRows++;
|
|
}
|
|
return nRows;
|
|
}
|
|
catch (_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
void CUIIImpADORXServer::CloseAll()
|
|
{
|
|
if(m_ConPtr != NULL)
|
|
{
|
|
try
|
|
{
|
|
m_ConPtr->Close();
|
|
::CoUninitialize();
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
}
|
|
m_bSuccessState = FALSE;
|
|
m_ConPtr = NULL;
|
|
}
|
|
}
|
|
|
|
BOOL CUIIImpADORXServer::IsDatabaseOpen()
|
|
{
|
|
return m_bSuccessState;
|
|
}
|
|
|
|
_ConnectionPtr CUIIImpADORXServer::GetConnectionPtr()
|
|
{
|
|
return m_ConPtr;
|
|
}
|
|
|
|
void CUIIImpADORXServer::FromConnectionPtr(_ConnectionPtr pConPtr)
|
|
{
|
|
if(m_ConPtr != NULL && m_bSuccessState)
|
|
{
|
|
m_ConPtr->Close();
|
|
m_ConPtr = NULL;
|
|
}
|
|
m_ConPtr = pConPtr;
|
|
m_bSuccessState = TRUE;
|
|
}
|
|
|
|
void CUIIImpADORXServer::DisplayADOException(_com_error &e,bool bDisplay)
|
|
{
|
|
_bstr_t bstrSource(e.Source());
|
|
_bstr_t bstrDescription(e.Description());
|
|
CString sError;
|
|
|
|
sError.Format(_T("Error:\n")
|
|
_T("\tCode = %08lx\n")
|
|
_T("\tCode meaning = %s\n")
|
|
_T("\tSource = %s\n")
|
|
_T("\tDescription = %s\n"),
|
|
e.Error(),
|
|
e.ErrorMessage(),
|
|
(LPCTSTR) bstrSource,
|
|
(LPCTSTR) bstrDescription);
|
|
if (bDisplay)
|
|
{
|
|
AfxMessageBox(sError);
|
|
}
|
|
}
|
|
|
|
int CUIIImpADORXServer::GetSelData(LPCTSTR psztrSQL,_variant_t &vResults,long &lAdoRow,long &lAdoCol)
|
|
{
|
|
if (psztrSQL == NULL) return -1;
|
|
|
|
try
|
|
{
|
|
_RecordsetPtr rs(_T("ADODB.Recordset"));
|
|
//rs.CreateInstance(__uuidof(Recordset));
|
|
_bstr_t strSQLAdo(psztrSQL);
|
|
try
|
|
{
|
|
rs->Open(strSQLAdo, m_ConPtr.GetInterfacePtr(), adOpenForwardOnly,adLockReadOnly, adCmdText);
|
|
if(rs->EndOfFile == -1 || rs->BOF)
|
|
{
|
|
return 0;
|
|
}
|
|
vResults=rs->GetRows(adGetRowsRest);
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
|
|
SafeArrayGetUBound(vResults.parray,2,&lAdoRow);
|
|
SafeArrayGetUBound(vResults.parray,1,&lAdoCol);
|
|
lAdoRow++;
|
|
lAdoCol++;
|
|
rs->Close();
|
|
}
|
|
catch(_com_error &er)
|
|
{
|
|
DisplayADOException(er);
|
|
return -1;
|
|
}
|
|
return 1;
|
|
}
|