adodatabase.cpp

Visual C++ 通过Ado 方式SQL Server的简单操作（包括

// AdoDatabase.cpp : implementation file
//

#include "stdafx.h"
#include "SQLClient.h"
#include "AdoDatabase.h"

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

/////////////////////////////////////////////////////////////////////////////
// CAdoDatabase

IMPLEMENT_DYNCREATE(CAdoDatabase, CDocument)

CAdoDatabase::CAdoDatabase()
{
}

BOOL CAdoDatabase::OnNewDocument()
{
	if (!CDocument::OnNewDocument())
		return FALSE;
	return TRUE;
}

CAdoDatabase::~CAdoDatabase()
{
}


BEGIN_MESSAGE_MAP(CAdoDatabase, CDocument)
	//{{AFX_MSG_MAP(CAdoDatabase)
		// NOTE - the ClassWizard will add and remove mapping macros here.
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAdoDatabase diagnostics

#ifdef _DEBUG
void CAdoDatabase::AssertValid() const
{
	CDocument::AssertValid();
}

void CAdoDatabase::Dump(CDumpContext& dc) const
{
	CDocument::Dump(dc);
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CAdoDatabase serialization

void CAdoDatabase::Serialize(CArchive& ar)
{
	if (ar.IsStoring())
	{
		// TODO: add storing code here
	}
	else
	{
		// TODO: add loading code here
	}
}

/////////////////////////////////////////////////////////////////////////////
// CAdoDatabase commands

bool CAdoDatabase::ConnectDatabase()
{
	HRESULT   hr;   
	hr=m_pConnection.CreateInstance("ADODB.Connection");   
	if(!SUCCEEDED(hr))   
	{
		//   创建   Connection   对象失败  
		AfxMessageBox("Connection false!");
		return false;
	}  
	//   创建   Recordset   对象   
	hr   =   m_pRecordset.CreateInstance("ADODB.Recordset");   
	if(!SUCCEEDED(hr))   
	{   
	//   创建   Recordset   对象失败  
		AfxMessageBox("Recordset false!");
		return false;
	}   

	if(!OpenDatabase())
	{
		AfxMessageBox("Open database flie false!");
	    return false;
	}
	else
	{
	//	AfxMessageBox("Connect succed !");  // Debug
		return true;
	}	
}

void CAdoDatabase::SetAdoDatabaseInfo(AdoDatabaseInfo adodatabaseinfo)
{
	g_TableName=adodatabaseinfo.TableName;
	g_DatabaseName=adodatabaseinfo.DatabaseName;
	g_DataSouceName=adodatabaseinfo.DataSouceName;
}

AdoDatabaseInfo CAdoDatabase::GetAdoDatabaseInfo()
{
	AdoDatabaseInfo adodatabaseinfo;

	adodatabaseinfo.TableName=g_TableName;
	adodatabaseinfo.DatabaseName=g_DatabaseName;
	adodatabaseinfo.DataSouceName=g_DataSouceName;

	return adodatabaseinfo;
}

bool CAdoDatabase::OpenDatabase()
{
	// TODO: Add your control notification handler code here

	CString StrOpenDat;

	StrOpenDat="Provider=SQLOLEDB.1;Integrated Security=SSPI;Database="+g_DatabaseName+\
		";Data Source="+g_DataSouceName; 	

	// Provider                  :  SQL OLE DB.1


	// Integrated Security/Trusted Connection   : true, SSPI = false 
	//  如果用户能够进入Windows 也可以访问 SQL   

	// Database/Initial Catalog  :  数据库的名称。

	// uID  用户ID  Password/Pwd  用户密码

	// Data Source/Server/Address/Addr/Network Address
	// SQL Server实例的名称或网络地址

	// Persist Security Info   :  true/false  保持安全信息
	
	try     
	{
		if(m_pConnection->State) 
		{
			m_pConnection->Close(); 
		}

	    m_pConnection->ConnectionTimeout = 5;

		m_pConnection->Open(_bstr_t(StrOpenDat), "","",adModeUnknown);  
	}  
	catch(_com_error  e)   
	{  
		AfxMessageBox(e.Description());   
		m_pConnection->Close();
		return false;   
	}   

	return true;
}

bool CAdoDatabase::DeleteData(int SelectRow)
{
	int RecordCount=0;

	try   
    {   
		m_pRecordset.CreateInstance("ADODB.Recordset");   
		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch*)m_pConnection,true),\
							adOpenDynamic,adLockOptimistic,adCmdText); 
		
		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();
			RecordCount++;
		}
		
		if(!RecordCount)							 // 数据库中没有数据
		{
			AfxMessageBox("There Is No Record in Database ! ");
			return false;
		}

		if(SelectRow>=RecordCount)
		{
			AfxMessageBox("The Select Row Over Max RecordCount ! ");
			return false;
		}

		m_pRecordset->MoveFirst();
		m_pRecordset->Move(SelectRow);  
		m_pRecordset->Delete(adAffectCurrent); 
		m_pRecordset->Close();

    }  
	catch   (_com_error   e)
	{
		AfxMessageBox(e.Description());
		return false;
	} 	
	return true;
}

bool CAdoDatabase::DeleteAllRecord()
{
	try   
    {   
		m_pRecordset.CreateInstance("ADODB.Recordset");   
		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch*)m_pConnection,true),\
							adOpenDynamic,adLockOptimistic,adCmdText); 
		
		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->Delete(adAffectCurrent); 
			m_pRecordset->MoveNext();
		}

		m_pRecordset->Close();

    }  
	catch   (_com_error   e)
	{
		AfxMessageBox(e.Description());
		return false;
	} 
	return true;
}

bool CAdoDatabase::ModifyData(CString VarName, CString NewValue, int SelectRow)
{
	int RecordCount=0;

	try   
    {   
		m_pRecordset.CreateInstance("ADODB.Recordset");   
		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch*)m_pConnection,true),\
							adOpenDynamic,adLockOptimistic,adCmdText); 
		
		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();
			RecordCount++;
		}

		if(!RecordCount)							 // 数据库中没有数据
		{
			AfxMessageBox("There Is No Record in Database ! ");
			return false;
		}

		if(SelectRow>=RecordCount)
		{
			AfxMessageBox("The Select Row Over Max RecordCount ! ");
			return false;
		}

		m_pRecordset->MoveFirst();
		m_pRecordset->Move(SelectRow);  
		m_pRecordset->PutCollect(_variant_t(VarName),_variant_t(NewValue)); 
		m_pRecordset->Update();	
		m_pRecordset->Close();
		return true;

    }  
	catch   (_com_error   e)
	{
		AfxMessageBox(e.Description());
		return false;
	} 
}

bool CAdoDatabase::ModifyData(CString StrSendData, int SelectRow)
{
	CString SendDat;				  //  RecordCount  //  The number of  Send	Data
	CString tmpStr;					  //  

	int     iPos,RecordCount=0;

	SendDat=StrSendData+"*";

	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);

		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();
			RecordCount++;
		}

		if(!RecordCount)							 // 数据库中没有数据
		{
			AfxMessageBox("There Is No Record in Database ! ");
			return false;
		}

		if(SelectRow>=RecordCount)
		{
			AfxMessageBox("The Select Row Over Max RecordCount ! ");
			return false;
		}

		FieldNum=m_pRecordset->GetFields()->Count;

		m_pRecordset->MoveFirst();
		m_pRecordset->Move(SelectRow);  //adBookmarkFirst

		for(int i=0;i<FieldNum;i++) 
		{
			iPos=SendDat.Find("*");      
			tmpStr=SendDat.Left(iPos);
			SendDat=SendDat.Right(SendDat.GetLength()-iPos-1);
			m_pRecordset->PutCollect(_variant_t((long)i),_variant_t(tmpStr));
		//	AfxMessageBox(tmpStr);     // Debug
		}

        m_pRecordset->Update();	   
	    m_pRecordset->Close();
    }
    catch(_com_error e)
    {
       AfxMessageBox(e.Description());
	}
	return false;
}

bool CAdoDatabase::AddNewData(CString StrAddData)
{	                                  //  StrAddData // Send Data format as var1*var2*var3*....
	CString StrSendDat;				  //  RecordCount  //  The number of  Send	Data
	CString tmpStr;					  //  

	int     iPos;

	StrSendDat=StrAddData+"*";

	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);

/*
	Open方法的原型是这样的:   
	HRESULT   Recordset15::Open   ( const   _variant_t   &   Source,  
									const   _variant_t   &   ActiveConnection,  
									enum   CursorTypeEnum   CursorType,  
									enum   LockTypeEnum   LockType,   
									long   Options   )     
	其中：   
	1 Source 是数据查询字符串 表名 
	2 ActiveConnection是已经建立好的连接（我们需要用Connection对象指针来构造一个_variant_t对象)     
	3 CursorType光标类型，它可以是以下值之一,请看这个枚举结构: 
	
    enum   CursorTypeEnum   
    {  
      adOpenUnspecified   =   -1,   //不作特别指定   
      adOpenForwardOnly   =   0,   //前滚静态光标。这种光标只能向前浏览记录集，比如用MoveNext向前滚动,这种方式可以提高浏览速度。但诸如BookMark,RecordCount,AbsolutePosition,AbsolutePage都不能使用   
      adOpenKeyset   =   1,     //采用这种光标的记录集看不到其它用户的新增、删除操作，但对于更新原有记录的操作对你是可见的。   
      adOpenDynamic   =   2,     //动态光标。所有数据库的操作都会立即在各用户记录集上反应出来。   
      adOpenStatic   =   3     //静态光标。它为你的记录集产生一个静态备份，但其它用户的新增、删除、更新操作对你的记录集来说是不可见的。   
	}; 
	
    4 LockType锁定类型，它可以是以下值之一，请看如下枚举结构：   
    enum   LockTypeEnum   
    {   
       adLockUnspecified   =   -1,     //未指定   
       adLockReadOnly   =   1,       //只读记录集   
       adLockPessimistic   =   2,     //悲观锁定方式。数据在更新时锁定其它所有动作，这是最安全的锁定机制   
       adLockOptimistic   =   3,     //乐观锁定方式。只有在你调用Update方法时才锁定记录。在此之前仍然可以做数据的更新、插入、删除等动作   
       adLockBatchOptimistic   =   4，   //乐观分批更新。编辑时记录不会锁定，更改、插入及删除是在批处理模式下完成。 
	}
 */
		FieldNum=m_pRecordset->GetFields()->Count;
		
		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();///移到下一条记录
		}


		if(!m_pRecordset->Supports(adAddNew)) 
			return false;

		m_pRecordset->AddNew();

		for(int i=0;i<FieldNum;i++) 
		{
			iPos=StrSendDat.Find("*");      
			tmpStr=StrSendDat.Left(iPos);
			StrSendDat=StrSendDat.Right(StrSendDat.GetLength()-iPos-1);
			m_pRecordset->PutCollect(_variant_t((long)i),_variant_t(tmpStr));
		//	AfxMessageBox(tmpStr);    // Debug
		}

        m_pRecordset->Update();	   
	    m_pRecordset->Close();
    }
    catch(_com_error e)
    {
       AfxMessageBox(e.Description());
	}
	return false;
}

CString CAdoDatabase::GetData(CString VarName,int SelectRow)
{
	int         RecordCount=0;				 
	_variant_t  _RcvDat; 
	CString     StrRcvDat;

	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);

		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();
			RecordCount++;
		}

		if(!RecordCount)							 // 数据库中没有数据
		{
			AfxMessageBox("There Is No Record in Database ! ");
			return "ERROR";
		}

		if(SelectRow>=RecordCount)                //  选择查看的记录索引号超过储存的数据总数
		{
			AfxMessageBox("The Select Row is Over Max RecordCount !");
			return "ERROR";
		}


		m_pRecordset->MoveFirst();
		m_pRecordset->Move(SelectRow);    

		_RcvDat=m_pRecordset->GetCollect(_variant_t(VarName)); // VarName   数据库用户表中的任意变量 
		StrRcvDat = (char*)_bstr_t(_RcvDat);         //_variant_t 转换成CString和char*类型
		StrRcvDat.TrimRight(" ");
	   
	    m_pRecordset->Close();
    }
    catch(_com_error e)
    {
       AfxMessageBox(e.Description());
	}

	return StrRcvDat;
}

CString CAdoDatabase::GetData(int SelectRow)
{
	
	int         RecordCount=0;
	_variant_t  _RcvDat; 	
	CString     StrRcvDat,tmpStr;

	try
	{
		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);

		FieldNum=m_pRecordset->GetFields()->Count;

		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();
			RecordCount++;
		}

		if(!RecordCount)							 // 数据库中没有数据
		{
			AfxMessageBox("There Is No Record in Database ! ");
			return "ERROR";
		}

		if(SelectRow>=RecordCount)                //  选择查看的记录索引号超过储存的数据总数
		{
			AfxMessageBox("The Select Row is Over Max RecordCount !");
			return "ERROR";
		}

		m_pRecordset->MoveFirst();
		m_pRecordset->Move(SelectRow);    

		for(int i=0;i<FieldNum;i++)
		{
			_RcvDat=m_pRecordset->GetCollect(_variant_t((long)i));  
			tmpStr = (char*)_bstr_t(_RcvDat);         //_variant_t 转换成CString和char*类型
			tmpStr.TrimRight(" ");
			StrRcvDat+=tmpStr+"*";
		}
		StrRcvDat=StrRcvDat.Left(StrRcvDat.GetLength()-1);  // 删除 StrRcvDat 的最后一个"*"
	    m_pRecordset->Close();
		
		
    }
    catch(_com_error e)
    {
		AfxMessageBox(e.Description());
	}
	
	return StrRcvDat;
}

int  CAdoDatabase::GetRecordCount()
{
	int iCount=0;

	try
	{

		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open(_variant_t("SELECT * FROM "+g_TableName),_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);


		m_pRecordset.CreateInstance("ADODB.Recordset");

		m_pRecordset->Open("SELECT * FROM Table_Test",_variant_t((IDispatch *)m_pConnection,true),\
			adOpenDynamic ,adLockPessimistic,adCmdText);
		
		while(!m_pRecordset->adoEOF)
		{
			m_pRecordset->MoveNext();///移到下一条记录
			iCount++;
		}
  
		 m_pRecordset->Close();
    }

    catch(_com_error e)
    {
       AfxMessageBox(e.Description());
	}

	return iCount;
}