VC执行存储过程
最近由于工作之需,要利用VC进行一些高级的数据库操作,如执行存储过程等。遍寻网络资源发现好使的不多(经常#30XX错误,大部分应该是COM的VARIANT数据类型所致,其实有个讨巧的方法,请看下文),在自己的实验下小有所得。记下学习笔记以备参考:
1 进行ADO编程的必备步骤:引入msado15.dll
#import "c:/program files/common files/system/ado/msado15.dll" no_namespace rename("EOF","adoEOF")//避免与文件的结束符号混淆,将EOF改为adoEOF
当然要记得在所有#include之后加入啊
2 也是必备的:初始化Com库
::CoInitialize(NULL);///初始化COM库
3 建立数据库连接
_ConnectionPtr m_pConnection;//连接数据库的Com智能指针,可以自动Release ^_^
m_pConnection.CreateInstance("ADODB.Connection");//建立实例返回HRESULT 所以最好if一下看看成功否
4 连接数据库
m_pConnection->Open("Driver={SQL Server};Server=localhost;Database=Northwind;UID=sa;PWD=wxy","","",adModeUnknown);
最好用Try的,可以捕获错误啊,例如
try
{
m_pConnection->Open("Driver={SQL Server};Server=localhost;Database=Northwind;UID=sa;PWD=wxy","","",adModeUnknown);
.........//其他数据库操作
}
catch(_com_error e)
{
//处理错误吧
}
5 创建执行存储过程的命令对象
_CommandPtr m_pCommand;//还是智能指针
m_pCommand.CreateInstance("ADODB.Command");//实例
m_pCommand->ActiveConnection = m_pConnection;//设置连接,别忘了啊
m_pCommand->CommandText = "Test";//存储过程为Test
假设我的存储过程如下:
CREATE PROCEDURE Test
@id int,
@Name varchar(20),
@sdate datetime,
@ret char(1) output
AS
insert into VCStorproc values(@id,@Name,@sdate)
if @@error=0
set @ret=1
else
set @ret=0
go
6 建立传入存储过程的参数
存储过程Test中输入参数id,name,sdate 输出参数ret 记住他们的类型和大小啊
下面就来创建4个参数
VC中用_ParameterPtr智能指针来建立参数,具体如下:
_ParameterPtr m_pParam;
m_pParam.CreateInstance("ADODB.Parameter");
_ParameterPtr m_pParam1;//附加数字的命名习惯不好别学我啊
m_pParam1.CreateInstance("ADODB.Parameter");
_ParameterPtr m_pParam2;
m_pParam2.CreateInstance("ADODB.Parameter");
_ParameterPtr m_pParamRet;
m_pParamRet.CreateInstance("ADODB.Parameter");
别高兴还没有真正的建立好参数
m_pParam = m_pCommand->CreateParameter("id",adInteger,adParamInput,-1,(_variant_t)"10");//给参数设置各属性
m_pCommand->Parameters->Append(m_pParam);//加入到Command对象的参数集属性中
m_pParam1 = m_pCommand->CreateParameter("Name",adVarChar,adParamInput,20,(_variant_t)"songwenfeng");
m_pCommand->Parameters->Append(m_pParam1);
m_pParam2 = m_pCommand->CreateParameter("sdate",adVarChar,adParamInput,32,(_variant_t)"2004-6-8");
m_pCommand->Parameters->Append(m_pParam2);
//
m_pParamRet=m_pCommand->CreateParameter("ret",adChar,adParamOutput,1);
m_pCommand->Parameters->Append(m_pParamRet);
大功告成,执行吧
m_pCommand->Execute(NULL,NULL,adCmdStoredProc);
//
数据库里已经有了一条新记录了,看看
id name sdate
10 songwenfeng 2004-6-8
7 最后一步,关闭连接,释放Com,走人!!
m_pConnection->Close();
CoUninitialize();
adAsyncConnect异步打开数据库
adConnectUnspecified同步打开数据库
void CDatabaseDlg::Onbtnquery()
{
//.udl生成连接字符串
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
//open(连接字符串,用户名,密码,打开方式(同步/异步));
pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pConn.Release();
CoUninitialize();
}
void CDatabaseDlg:: Onbtnquery ()
{
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
// pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
pRst->Open("select * from notepad",_variant_t((IDispatch*)pConn),adOpenDynamic,adLockOptimistic,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pConn.Release();
CoUninitialize();
}
3.
void CDatabaseDlg:: Onbtnquery ()
{
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
_CommandPtr pCmd(__uuidof(Command));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
// pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
// pRst->Open("select * from notepad",_variant_t((IDispatch*)pConn),adOpenDynamic,adLockOptimistic,adCmdText);
pCmd->put_ActiveConnection(_variant_t((IDispatch*)pConn));
pCmd->CommandText="select * from notepad";
pRst=pCmd->Execute(NULL,NULL,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pCmd.Release();
pConn.Release();
CoUninitialize();
}
adAsyncConnect异步打开数据库
adConnectUnspecified同步打开数据库
void CDatabaseDlg::Onbtnquery()
{
//.udl生成连接字符串
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
//open(连接字符串,用户名,密码,打开方式(同步/异步));
pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pConn.Release();
CoUninitialize();
}
void CDatabaseDlg:: Onbtnquery ()
{
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
// pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
pRst->Open("select * from notepad",_variant_t((IDispatch*)pConn),adOpenDynamic,adLockOptimistic,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pConn.Release();
CoUninitialize();
}
3.
void CDatabaseDlg:: Onbtnquery ()
{
// TODO: Add your control notification handler code here
CoInitialize(NULL);
_ConnectionPtr pConn(__uuidof(Connection));
_RecordsetPtr pRst(__uuidof(Recordset));
_CommandPtr pCmd(__uuidof(Command));
pConn->ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E://notepad.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False";
pConn->Open("","","",adConnectUnspecified);
// pRst=pConn->Execute("select * from notepad",NULL,adCmdText);
// pRst->Open("select * from notepad",_variant_t((IDispatch*)pConn),adOpenDynamic,adLockOptimistic,adCmdText);
pCmd->put_ActiveConnection(_variant_t((IDispatch*)pConn));
pCmd->CommandText="select * from notepad";
pRst=pCmd->Execute(NULL,NULL,adCmdText);
while(!pRst->rsEOF)
{
(((CListBox*)GetDlgItem(IDC_LIST1)))->AddString((_bstr_t)pRst->GetCollect("username"));
pRst->MoveNext();
}
pRst->Close();
pConn->Close();
pRst.Release();
pCmd.Release();
pConn.Release();
CoUninitialize();
}