关于SQL server的数据库操作
1.导入两个文件
以下是:(DatabaseAccess.h 文件)
// DatabaseAccess.h: interface for the CDatabaseAccess class.
//
//
#if !defined(AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_)
#define AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#include <Afxtempl.h>
// 描述_ParameterPtr对象信息的结构
typedef struct tagParameterInfo{ // 参数信息
_bstr_t Name; // 参数名字
enum DataTypeEnum Type; // 参数类型
enum ParameterDirectionEnum Direction; // 参数方向(输入、输出)
long Size; // 参数尺寸
_variant_t Value; // 参数值。
}PARAMETERINFO, *PPARAMETERINFO;
// 使用前在StdAfx.h中加入#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
// 访问数据库前先用::CoInitialize(NULL)初始化COM环境。
// 程序退出时使用::CoUninitialize()释放资源。
class CDatabaseAccess
{
private:
bool m_bIsOracle; //ORACLE与SQLServer切换标志。默认是ORACLE数据库
_ConnectionPtr m_pCon; //连接对象
public:
int GetRecordSetCount(_RecordsetPtr m);
//连接本地数据库(无密码)
BOOL ConnectDB(char *DatabaseName);
//连接本地数据库(有密码) 通过ODBC方式连接
BOOL ConnectDB(const char *DatabaseName,char *PassWord);
CDatabaseAccess();
virtual ~CDatabaseAccess();
// 连接到数据库服务器,DSN方式 1成功 0失败
BOOL ConnectDBSQL(char *pcDSNName,char *pcUserName,char *pcUserPassword);
// 连接到数据库服务器,需要IP,端口和Oracle全局数据库名或SID的连接方式。1成功 0失败
BOOL ConnectDB(const char *pcUserName ,const char *pcUserPassword ,const char *pcServerIP ,const char *pcServerPort="1024" ,const char *pcConnectServiceName = "MFD");
//关闭与数据库的连接
void CloseConnect();
//设置数据库类型(ORACLE与SQLServer)
void SetDatabaseType(bool bIsOracle=true);
// 执行存储过程(普通存储过程,即非包中的),非零-正确执行;0-参数个数为负数
BOOL ExecuteProcedure(const char *pcProcedureName , PARAMETERINFO aPrameterInfo[],BYTE bCount);
// 执行存储过程返回记录集的地址。在此函数中使用约定包名:mfd_callpkg。字段列表用"逗号"隔开如:"empid,empname"
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bParametersCount , const char *pcFieldsList , const char *pcPackageName = "mfd_callpkg");
// 无参数的存储过程(普通存储过程,非包中)
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName);
// 无参数的存储过程
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , const char *pcFieldsList);
// 执行SQL语句
_RecordsetPtr *ExecuteSQLSentence( const char *pcSentence);
};
#endif // !defined(AFX_DATABASEACCESS_H__19163588_441F_4B21_B354_877412B2B46A__INCLUDED_)
以下是(DatabaseAccess.cpp 文件)
// DatabaseAccess.cpp: implementation of the CDatabaseAccess class.
//
//
#include "stdAfx.h"
#include "DatabaseAccess.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
//
// Construction/Destruction
//
CDatabaseAccess::CDatabaseAccess()
{
CoInitialize(NULL);
m_bIsOracle=true; //默认是使用ORACLE数据库
try
{
m_pCon.CreateInstance(_uuidof(Connection));
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::CDatabaseAccess()-- 创建COM实例失败%s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
return;
}
catch (...) {
TRACE("--CDatabaseAccess::CDatabaseAccess()-- 未知错误,创建COM实例失败\n");
return;
}
}
CDatabaseAccess::~CDatabaseAccess()
{
CloseConnect();
}
// 关闭与数据库的连接
void CDatabaseAccess::CloseConnect()
{
try
{
if(m_pCon ->State)
m_pCon ->Close();
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::CloseConnect()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
return;
}
catch (...) {
TRACE("--CDatabaseAccess::CloseConnect()-- 不明错误\n");
return;
}
}
// 设置数据库类型
void CDatabaseAccess::SetDatabaseType(bool bIsOracle/*=true*/)
{
m_bIsOracle=bIsOracle;
}
//连接到数据库服务器,采用系统DSN方式 1成功 0失败
BOOL CDatabaseAccess::ConnectDBSQL(char *pcDSNName,char *pcUserName,char *pcUserPassword)
{
//连接Sql Server数据库
if (!m_bIsOracle)
{
try
{
CString strConnection; //连接字符串
strConnection.Format("DSN=%s;UID=%s;PWD=%s" ,pcDSNName ,
pcUserName ,pcUserPassword);
HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
strConnection.ReleaseBuffer();
if(SUCCEEDED(hResult))
return 1; //连接成功!
else
return 0; //连接失败!
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
return 0; //连接失败
}
catch (...) {
TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
return 0;
}
}
}
// 连接到数据库服务器,需要IP,端口和Oracle全局数据库名或SID的连接方式。1成功 0失败.
BOOL CDatabaseAccess::ConnectDB(const char *pcUserName ,const char *pcUserPassword ,const char *pcServerIP ,const char *pcServerPort ,const char *pcGlobalDatabaseName/* = "MFD"*/)
{
if(!m_bIsOracle)
{
try
{
CString strConnection; //连接字符串
strConnection.Format("Provider=SQLOLEDB.1;Password=%s;Persist Security Info=True;User ID=%s;Initial Catalog=%s;Data Source=%s" ,
pcUserPassword,pcUserName,pcGlobalDatabaseName,pcServerIP);
TRACE("%s\n" , strConnection.GetBuffer(0));
HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
strConnection.ReleaseBuffer();
if(SUCCEEDED(hResult))
return 1; //连接成功!
else
return 0; //连接失败!
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
return 0; //连接失败
}
catch (...) {
TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
return 0;
}
}
else
{
try
{
CString strConnection; //连接字符串
strConnection.Format("Provider=MSDAORA.1;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = %s)(PORT = %s)))(CONNECT_DATA =(SERVICE_NAME = %s)));User ID=%s;Password=%s" ,
pcServerIP ,pcServerPort ,pcGlobalDatabaseName ,pcUserName , pcUserPassword);
TRACE("%s\n" , strConnection.GetBuffer(0));
HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0),"","",adModeUnknown);
strConnection.ReleaseBuffer();
if(SUCCEEDED(hResult))
return 1; //连接成功!
else
return 0; //连接失败!
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
return 0; //连接失败
}
catch (...) {
TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
return 0;
}
}
}
// 执行存储过程(普通存储过程,即非包中的),非零-正确执行;0-失败(参数个数为负数)。
BOOL CDatabaseAccess::ExecuteProcedure(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bCount)
{
_CommandPtr pCmd;
HRESULT hRet = pCmd.CreateInstance(_uuidof(Command));
if(FAILED(hRet))
{
TRACE("--CDatabaseAccess::ExecuteProcedure()-- 创建COMMAND对象失败!");
return 0;
}
_ParameterPtr *aParameterPtr = NULL;
if(bCount > 0)
{
// 动态创建参数对象
aParameterPtr = new _ParameterPtr[bCount];
memset(aParameterPtr , 0 ,sizeof(_ParameterPtr) * bCount);
}
try
{
_bstr_t bstrSPName(pcProcedureName); // 存储过程名
if(bCount<=0) // 无参数
{
if(bCount<0)
{
return 0; // 出错
}
pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
pCmd->CommandText = bstrSPName;
pCmd->CommandType = adCmdStoredProc;
// 动态创建记录集对象。
_RecordsetPtr *ppRs = new _RecordsetPtr();
(*ppRs) = pCmd->Execute(NULL ,NULL ,NULL); // 执行存储过程
return (BOOL)ppRs;
}
pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
pCmd->CommandText = bstrSPName;
pCmd->CommandType = adCmdStoredProc;
//创建参数实例并添加之
for(int i=0; i < bCount; i++)
{
aParameterPtr[i] = pCmd->CreateParameter(aParameterInfo[i].Name , aParameterInfo[i].Type , aParameterInfo[i].Direction , aParameterInfo[i].Size);
pCmd->Parameters->Append(aParameterPtr[i]);
}
if(!m_bIsOracle) // SQLServer中需要!
pCmd->Parameters->Refresh(); // 资源集中的操作
// 对输入参数的处理
for(i=0; i < bCount; i++)
{
if(aParameterInfo[i].Direction==adParamInput || aParameterInfo[i].Direction == adParamInputOutput)
{
if(m_bIsOracle) //oracle数据库
{
pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value = aParameterInfo[i].Value;
}
else //SQLserver数据库
{
CString str;
str.Format("@%s",((char *)aParameterInfo[i].Name));
pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value = aParameterInfo[i].Value;
}
}
}
pCmd->Execute(NULL ,NULL ,NULL); //通过COMMAND对象来执行存储过程。
// 对输出参数的处理
for(i=0; i < bCount; i++)
{
if(aParameterInfo[i].Direction==adParamOutput || aParameterInfo[i].Direction == adParamInputOutput)
{
if(m_bIsOracle) //oracle数据库
{
aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value;
}
else //SQLserver数据库
{
CString str;
str.Format("@%s",((char *)aParameterInfo[i].Name));
aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value;
}
}
}
if(bCount > 0)
delete []aParameterPtr;
return 1;
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ExecuteProcedure()-- %s,%s\n",(char *)e.Description(),(char *)e.ErrorMessage());
if(bCount > 0)
delete []aParameterPtr;
return 0;
}
catch (...) {
TRACE("--CDatabaseAccess::ExecuteProcedure()-- 不明错误\n");
if(bCount > 0)
delete []aParameterPtr;
return 0;
}
}
// 执行存储过程返回记录集的地址。在此函数中使用约定包名:mfd_callpkg。字段列表用"逗号"隔开如:"empid,empname"。
// 存储过程格式约定:包名.存储过程名(输入参数 , 输入参数 , 输出参数 , 结果集)。
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , PARAMETERINFO aParameterInfo[] , BYTE bParametersCount ,
const char *pcFieldsList , const char *pcPackageName/* = "mfd_callpkg"*/)
{
_RecordsetPtr *ppRs = new _RecordsetPtr; // 动态创建记录集对象
HRESULT hpRecordset = (*ppRs).CreateInstance(_uuidof(Recordset));
if(FAILED(hpRecordset))
{
TRACE("--CDatabaseAccess::ExecProGetRecordset()-- 创建RECORDSET对象失败!");
return NULL;
}
_CommandPtr pCmd;
HRESULT hRet = pCmd.CreateInstance(_uuidof(Command));
if(FAILED(hRet))
{
TRACE("--CDatabaseAccess::ExecProGetRecordset()-- 创建COMMAND对象失败!");
return NULL;
}
const CString strResultsetNumber = "1000"; // 结果集中记录的个数.好像是上限
CString strCallScript; // 调用格式.
strCallScript = "{call ";
if(strlen(pcPackageName)) // 如果在包中。则连入包名。
{
strCallScript += pcPackageName;
strCallScript += ".";
}
strCallScript += pcProcedureName;
strCallScript += "(";
for(int i=1 ; i<=bParametersCount ; i++)
{
strCallScript += "?,";
}
strCallScript += "{resultset ";
strCallScript += strResultsetNumber;
strCallScript += ",";
strCallScript += pcFieldsList;
strCallScript += "})}";
TRACE("%s\n" , strCallScript.GetBuffer(0));
_ParameterPtr *aParameterPtr = NULL;
if(bParametersCount > 0)
{ // 动态创建参数对象
aParameterPtr=new _ParameterPtr[bParametersCount];
memset(aParameterPtr , 0 ,sizeof(_ParameterPtr) * bParametersCount);
}
try
{
if(bParametersCount <= 0) // 无参数
{
if(bParametersCount < 0)
{
delete ppRs;
return (_RecordsetPtr*)NULL; // 出错
}
pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
pCmd->CommandText = strCallScript.AllocSysString();
pCmd->CommandType = adCmdText;
_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR); //变体类型空值
(*ppRs)->CursorLocation = adUseClient;
(*ppRs)->Open((IDispatch*)pCmd , vtEmpty, adOpenKeyset, adLockOptimistic,adOpenRecordUnspecified);
return ppRs;
}
pCmd->ActiveConnection = m_pCon; // 设置COMMAND对象的属性
pCmd->CommandText = strCallScript.AllocSysString();
pCmd->CommandType = adCmdText;
//创建参数实例并添加之
for(int i=0; i < bParametersCount; i++)
{
aParameterPtr[i] = pCmd->CreateParameter(aParameterInfo[i].Name , aParameterInfo[i].Type , aParameterInfo[i].Direction , aParameterInfo[i].Size);
pCmd->Parameters->Append(aParameterPtr[i]);
}
if(!m_bIsOracle) // SQLServer中需要!
pCmd->Parameters->Refresh(); // 资源集中的操作
// 对输入参数的处理
for(i=0; i < bParametersCount; i++)
{
if(aParameterInfo[i].Direction==adParamInput || aParameterInfo[i].Direction == adParamInputOutput)
{
if(m_bIsOracle) //oracle数据库
{
pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value = aParameterInfo[i].Value;
}
else //SQLserver数据库
{
CString str;
str.Format("@%s",((char *)aParameterInfo[i].Name));
pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value = aParameterInfo[i].Value;
}
}
}
_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR); //变体类型空值
(*ppRs)->CursorLocation = adUseClient;
(*ppRs)->Open((IDispatch*)pCmd , vtEmpty, adOpenKeyset/*adOpenForwardOnly*/, adLockOptimistic,adOpenRecordUnspecified);
// 对输出参数的处理
for(i=0; i < bParametersCount; i++)
{
if(aParameterInfo[i].Direction==adParamOutput || aParameterInfo[i].Direction == adParamInputOutput)
{
if(m_bIsOracle) //oracle数据库
{
aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(aParameterInfo[i].Name) )->Value;
}
else //SQLserver数据库
{
CString str;
str.Format("@%s",((char *)aParameterInfo[i].Name));
aParameterInfo[i].Value = pCmd->Parameters->GetItem(_variant_t(_bstr_t(str.AllocSysString())))->Value;
}
}
}
if(bParametersCount > 0)
delete []aParameterPtr;
return ppRs; //返回记录集对象的指针.
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ExecProGetRecordset()-- %s,%s %x\n",(char *)e.Description(),(char *)e.ErrorMessage(),e.Error());
if(e.Error() == 0x80004005)
{
TRACE("到数据库的网络连接可能已经断开。\n");
}
if(bParametersCount > 0)
delete []aParameterPtr;
delete ppRs;
return NULL;
}
catch (...) {
TRACE("--ExecProGetRecordset()-- 不明错误\n");
if(bParametersCount > 0)
delete []aParameterPtr;
delete ppRs;
return NULL;
}
}
// 无参数的存储过程(普通存储过程,非包中)。
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName)
{
return (_RecordsetPtr *)ExecuteProcedure(pcProcedureName, (PARAMETERINFO *)NULL , 0);
}
// 无参数的存储过程
_RecordsetPtr *CDatabaseAccess::ExecProGetRecordset(const char *pcProcedureName , const char *pcFieldsList)
{
return ExecProGetRecordset(pcProcedureName, (PARAMETERINFO *)NULL , 0 , pcFieldsList);
}
// 执行SQL语句
_RecordsetPtr *CDatabaseAccess::ExecuteSQLSentence( const char *pcSentence)
{
_RecordsetPtr *ppRs=new _RecordsetPtr; // 动态创建记录集对象。
try
{
(*ppRs).CreateInstance(_uuidof(Recordset));
(*ppRs)->Open(_variant_t(pcSentence) , m_pCon.GetInterfacePtr() , adOpenKeyset , adLockOptimistic, adCmdText);
return ppRs;
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ExecuteSQLSentence()-- %s,%s\n", (char *)e.Description() ,(char *)e.ErrorMessage());
delete ppRs;
return NULL;
}
catch (...) {
TRACE("--CDatabaseAccess::ExecuteSQLSentence()-- 不明错误\n");
delete ppRs;
return NULL;
}
}
//连接内地数据库方式(无用户名和密码格式)(Access数据库)
BOOL CDatabaseAccess::ConnectDB(char *DatabaseName)
{
if (!m_bIsOracle)
{
try
{
CString strConnection; //连接字符串
strConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False",DatabaseName);
HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
strConnection.ReleaseBuffer();
if(SUCCEEDED(hResult))
return 1; //连接成功!
else
return 0; //连接失败!
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
return 0; //连接失败
}
catch (...) {
TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
return 0;
}
}
}
BOOL CDatabaseAccess::ConnectDB(const char *DatabaseName,char *PassWord)
{
//连接ACCESS数据库,带密码
if (!m_bIsOracle)
{
try
{
CString strConnection; //连接字符串
strConnection.Format("DSN=%s;PWD=%s",DatabaseName,PassWord);
HRESULT hResult=m_pCon->Open(strConnection.GetBuffer(0) ,"" ,"" ,adModeUnknown);
strConnection.ReleaseBuffer();
if(SUCCEEDED(hResult))
return 1; //连接成功!
else
return 0; //连接失败!
}
catch(_com_error &e)
{
TRACE("--CDatabaseAccess::ConnectDB()-- %s,%s\n",(char *)e.Description() ,(char *)e.ErrorMessage());
return 0; //连接失败
}
catch (...) {
TRACE("--CDatabaseAccess::ConnectDB()-- 不明错误\n");
return 0;
}
}
}
int CDatabaseAccess::GetRecordSetCount(_RecordsetPtr m)
{
int i=0;
if(!(m->GetRecordCount()==0))
{
m->MoveFirst();
while (!m->adoEOF) {
i++;
m->MoveNext();
}
}
return i;
}
2.在stfAfx.h文件中导入
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
在APP () 中 定义 CDatabaseAccess ado;
3.在APP的Initlnstance()
中添加
BOOL CTextApp::InitInstance()
{
// Standard initialization
if (!AfxOleInit())
{
//AfxMessageBox(IDP_OLE_INIT_FAILED);
return FALSE;
}
ado.SetDatabaseType(false);
ado.ConnectDB("sa","root","172.20.3.23","1433","student");
AfxEnableControlContainer();
4.添加
void CTextDlg::OnButton1() //添加
{
// TODO: Add your control notification handler code here
UpdateData(TRUE);
CString strSQL;
_variant_t var;
_RecordsetPtr ptr;
int index=0;
int age=atoi(m_nAge);
int nScore=atoi(m_nScore);
strSQL.Format("insert into student values('%s','%s',%d,%d)",m_strID,m_strName,age,nScore);// 添加语句
if (AfxMessageBox("你确定要添加这条记录吗?",MB_OKCANCEL,0)==IDOK)
{ //zhongdian
ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);//执行语句
}
}
5.删除记录
void CTextDlg::OnButton2() //删除
{
// TODO: Add your control notification handler code here
UpdateData(true);
CString strSQL;
_RecordsetPtr ptr;//额外添加
int nitem;
strSQL.Format("delete from student where ID=%s",m_strID);
if (AfxMessageBox("你确定要添删除这条记录吗?",MB_OKCANCEL,0)==IDOK)
{
ptr=((CTextApp*)AfxGetApp())->ado.ExecuteSQLSentence(strSQL);//执行语句
}
}