oledbtestdlg.cpp

这是一个基于COM的数据库访问技术

// OleDbTestDlg.cpp : implementation file
//

#include "stdafx.h"
#include "OleDbTest.h"
#include "OleDbTestDlg.h"
#include "MyTableStruct.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif


class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL

// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// COleDbTestDlg dialog

COleDbTestDlg::COleDbTestDlg(CWnd* pParent /*=NULL*/)
	: CDialog(COleDbTestDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(COleDbTestDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void COleDbTestDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(COleDbTestDlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(COleDbTestDlg, CDialog)
	//{{AFX_MSG_MAP(COleDbTestDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	ON_BN_CLICKED(IDC_BUTTON3, OnButton3)
	ON_BN_CLICKED(IDC_BUTTON4, OnButton4)
	ON_BN_CLICKED(IDC_BUTTON5, OnButton5)
	ON_BN_CLICKED(IDC_BUTTON6, OnButton6)
	ON_BN_CLICKED(IDC_BUTTON7, OnButton7)
	ON_BN_CLICKED(IDC_BUTTON8, OnButton8)
	ON_BN_CLICKED(IDC_BUTTON9, OnButton9)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// COleDbTestDlg message handlers

BOOL COleDbTestDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	strcpy(m_Provider,"MSDASQL") ;
	//m_Provider = "MSDASQL" SQLOLEDB; //ODBC 的连接提供 86208577

	bOpen = FALSE ;
	//OnButton1()  ;

//  	if(m_Conn.Open("WatchServer","dba","sql"))
//  		AfxMessageBox("SypODLConnection Successed !");

	return TRUE;  // return TRUE  unless you set the focus to a control
}

void COleDbTestDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void COleDbTestDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR COleDbTestDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void COleDbTestDlg::OnButton1() 
{
	if(bOpen)
		return ;
 	if(OpenDb("192.168.0.202","sqip","sa","") )
 //	if(OpenDb("192.168.0.202","HebData","sa","") )
		AfxMessageBox("Connect Successed!") ;
	else
		AfxMessageBox("Connect Failed!") ;
}

BOOL COleDbTestDlg::OpenDb(char *szAddress, char *szDatabase, char *szUid, char *szPwd)
{
	CDBPropSet	dbinit(DBPROPSET_DBINIT);

	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, szAddress);
    dbinit.AddProperty(DBPROP_INIT_CATALOG, szDatabase); 
	dbinit.AddProperty(DBPROP_AUTH_USERID, szUid);
	dbinit.AddProperty(DBPROP_AUTH_PASSWORD, szPwd);
	dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
	dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);

	hr = m_db.OpenWithServiceComponents(m_Provider, &dbinit);
	if (FAILED(hr))
	{
		return false;
	}

	hr = m_session.Open(m_db);
	if (FAILED(hr))
	{
		m_db.Close();
 		return false;
	}

    bOpen = TRUE ;

	return TRUE ;
}

void COleDbTestDlg::OnDestroy() 
{
	CDialog::OnDestroy();
	
    if(bOpen)
	{
		m_session.Close() ;
		m_db.Close() ;
		bOpen=FALSE ;
	}
}

void COleDbTestDlg::OnButton2() 
{
 	CCommand<CNoAccessor,CNoRowset> cmd;
 //	CCommand<CAccessor,CRowset> cmd2;
  	
 	if(!bOpen)
	{
		MessageBox("数据库尚未连接成功！","提示",MB_ICONWARNING|MB_OK) ;
		return ;
	}
	m_session.StartTransaction() ;
	
//	cmd.Open(m_session,"",m_pSet) ;
	hr=cmd.Open(m_session,"insert into version(v_no,v_date) values('aaaaaa','2004-11-10')") ;
	m_session.Commit() ;
	cmd.Close() ;
}

void COleDbTestDlg::MyTitle(char *szTitle)
{
  MessageBox(szTitle,"提示",MB_ICONWARNING|MB_OK) ;
}

void COleDbTestDlg::OnButton3() 
{
  	CCommand<CDynamicAccessor,CRowset> cmd2;
 //	CCommand<CAccessor,CRowset> cmd2;
	char szTrace[512] ;

 	if(!bOpen)
	{
		MessageBox("数据库尚未连接成功！","提示",MB_ICONWARNING|MB_OK) ;
		return ;
	}

	DBCOLUMNINFO *m_pColsInfo;
	ULONG	m_nColCount;
	LPOLESTR m_pStrBuffer;

	hr=cmd2.Open(m_session,"select * from Employee") ;
	if(!FAILED(hr))
	{
		//----显示列总数
	    sprintf(szTrace,"column count %d", cmd2.GetColumnCount()) ;
		TRACE0(szTrace) ;

		//-----显示列名
		for(int i=0;i<3;i++)
		{
		  //sprintf(szTrace,"%s",(char*)(_t_str)cmd2.GetColumnName(i)) ;
		}
	}

    char szData[256] ;
	char szType[256] ;
    WCHAR wData[250] ;

	cmd2.GetColumnInfo(&m_nColCount,&m_pColsInfo,&m_pStrBuffer) ;
 
	for(unsigned int i=0;i<m_nColCount;i++)
	{
		wcscpy(wData,m_pColsInfo[i].pwszName) ;
        sprintf(szType,"%d",m_pColsInfo[i].wType) ;
		TRACE0(szType) ;
		TRACE0(",") ;
	}

 	hr=cmd2.MoveFirst() ;
	
 	while(hr==S_OK)
	{
        for(int i=1;i<7;i++)
		{
         //if( m_pColsInfo[i-1].wType==3)
		  strcpy(szData,(char*)cmd2.GetValue(i));
		 // memcpy((char*)&id,(char*)cmd2.GetValue(i),4);
         //if( m_pColsInfo[i-1].wType==129)
		 // strcpy(szData,(char*)(_bstr_t)cmd2.GetValue(i));

		 TRACE0(szData) ;
		}
		TRACE0("\n") ;
		hr=cmd2.MoveNext() ;
	}
	cmd2.Close() ;

}

//以列名处理
void COleDbTestDlg::OnButton4() 
{
   	CCommand<CAccessor<CEmployee2> > cmd2; //定义模板

	strcpy(szSQL,"Select * from Employee2") ;
	hr=cmd2.Open(m_session,szSQL) ;
	cmd2.MoveFirst() ;
    while(hr==S_OK)
	{
		sprintf(szSQL,"%d---",cmd2.m_myID) ;
        strcat(szSQL,cmd2.m_Name) ;
		strcat(szSQL,"\n");
		TRACE0(szSQL) ;
		cmd2.ClearRecord() ;
		hr=cmd2.MoveNext() ;
	}
}

//以列序号处理
void COleDbTestDlg::OnButton5() 
{
 	CCommand<CAccessor<CEmployee3> > cmd2; //定义模板
	TCHAR szErrorMsg[1024] ;
	
	strcpy(szSQL,"Select * from Employee2") ;

	hr=cmd2.Open(m_session,szSQL) ;

	if(hr==S_OK)
	{
		cmd2.MoveFirst() ;
		while(hr==S_OK)
		{
			for(int i=1;i<6;i++)
			{
				if(i==1)
				{
					strcpy(szSQL,cmd2.m_Column[i-1]) ;
					strcat(szSQL,",") ;
				}
				else
				{
					strcat(szSQL,cmd2.m_Column[i-1]);
					strcat(szSQL,",") ;
				}
			}
			TRACE0(szSQL) ;
			TRACE0("\n") ;
			AfxMessageBox(szSQL) ;
			cmd2.ClearRecord() ;
			hr=cmd2.MoveNext() ;
		}
	} else
		GetHRRESULTMessage(hr,szErrorMsg) ;

}

void COleDbTestDlg::OnButton6() 
{
 	CDBErrorInfo dbErrorInfo ;

	try
	{
		m_Conn.Execute("insert into TestTable(bm,name,Address,old,Salary,birth,birth2) values('10001','罗世振1','广东深圳1',20,555.55,'2003-12-23','2003-12-30 20:12:22')") ;
	}catch(CSypODLException e)
	{
		e.DisplayError();
	}
}

void COleDbTestDlg::GetHRRESULTMessage(HRESULT hr, TCHAR *msg)
{
	LPVOID 	lpMsgBuf;
	HMODULE	hModule = LoadLibrary(_T("C:\\Program Files\\Common Files\\System\\ole db\\MSDAERR.DLL"));

	if(hModule)
	{
		long l = FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_HMODULE |
			FORMAT_MESSAGE_FROM_SYSTEM, hModule, hr, MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
			(LPTSTR) &lpMsgBuf, 0, NULL);

		lstrcpy(msg, (LPTSTR)lpMsgBuf);

		// Free the buffer.
		LocalFree( lpMsgBuf );

		FreeLibrary(hModule);

		return;
	}
}

BOOL COleDbTestDlg::OpenDb2(char *szDNS, char *szUid, char *szPwd)
{
	CDBPropSet	dbinit(DBPROPSET_DBINIT);

	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, szDNS);
	dbinit.AddProperty(DBPROP_AUTH_USERID, szUid);
	dbinit.AddProperty(DBPROP_AUTH_PASSWORD, szPwd);
	dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
	dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);

	hr = m_db.OpenWithServiceComponents(m_Provider, &dbinit);
	if (FAILED(hr))
	{
		return false;
	}

	hr = m_session.Open(m_db);
	if (FAILED(hr))
	{
		m_db.Close();
 		return false;
	}

    bOpen = TRUE ;

	return TRUE ;
}

void COleDbTestDlg::OnButton7() 
{
  if(OpenDb2("WatchServer","dba","sql"))
	  AfxMessageBox("OK") ;
  else
	  AfxMessageBox("NO") ;
}

void COleDbTestDlg::OnButton8() 
{
   	CCommand<CDynamicAccessor,CRowset> cmd2;
 	DBCOLUMNINFO *m_pColsInfo;
	ULONG	m_nColCount;
	LPOLESTR m_pStrBuffer;

	char szSQL[256] ;
    TCHAR szValue[256] ;
	ULONG nLong ;
 	LPVOID lpVoid ;
	DBTIMESTAMP dbTime ;
	ULONG dwDouble2 ;
	DB_NUMERIC dbNumeric ;


	strcpy(szSQL,"Select * from TestTable") ;
    hr=cmd2.Open(m_session,szSQL) ;
	hr=cmd2.MoveFirst() ;

	cmd2.GetColumnInfo(&m_nColCount,&m_pColsInfo,&m_pStrBuffer) ;
    for(int i=0;i<7;i++)
	{
		sprintf(szSQL,"%d,",m_pColsInfo[i].wType) ;
		TRACE0(szSQL) ;
	}
 
    while(hr==S_OK)
	{
		if(hr==S_OK)
		{
          for(int i=1;i<=7;i++)
		  {
			 if(i>=1&&i<=3)
			 {
             lstrcpy(szValue,(LPCTSTR)cmd2.GetValue(i)) ;
			 TRACE0(szValue) ;
			 TRACE0(",") ;
			 }else if(i==4)
			 {
				 memcpy((char*)&nLong,cmd2.GetValue(i),4) ;
				 lpVoid = cmd2.GetValue(i) ;
			 }else if(i==5)
			 {
				 memcpy((char*)&dbNumeric,cmd2.GetValue(i),sizeof(DB_NUMERIC)) ;
				 memcpy((char*)&dwDouble2,dbNumeric.val,sizeof(ULONG)) ;
				// memcpy((char*)&dbDecimal,cmd2.GetValue(i),sizeof(DECIMAL)) ;
			 }else 
			 {//DBTIMESTAMP
				 memcpy((char*)&dbTime,cmd2.GetValue(i),sizeof(DBTIMESTAMP)) ;
				 lpVoid = cmd2.GetValue(i) ;
			 }
		  }
			 TRACE0("\n") ;
		}
		hr=cmd2.MoveNext() ;
	}
}

void COleDbTestDlg::OnButton9() 
{
	unsigned char szHex[16] ;
    long nLong =0;
//	double dDouble ;
	int nLen,nPos=0 ;

	memset(szHex,0x00,sizeof(szHex)) ;
	szHex[0]=0xAB ;
	szHex[1]=0xCD ;

	nLong = szHex[0]*256 ;
	nLong +=szHex[1] ;
  
	nLong = 0 ;
	for(nPos =0;nPos<nLen;nPos++)
	{
		if(nPos==0)
		{
         nLong +=256*(nLen-1) ;
		}
	}
}