首先准备一条bcp语句,测试一下:
EXEC master..xp_cmdshell 'bcp myTest.dbo.firest out d:/tmp.csv /c /t /S /U sa /P sa'
这句话导出myTest数据库中的firest表到物理d盘下的tmp.csv文件中,在TSQL环境下测试成功
那么在VC++下呢,首先连接数据库,执行该语句,源码如下:
Exec函数中就是执行导出语句的的方法,当然任何合法的sql语句都能执行
EXEC master..xp_cmdshell 'bcp myTest.dbo.firest out d:/tmp.csv /c /t /S /U sa /P sa'
这句话导出myTest数据库中的firest表到物理d盘下的tmp.csv文件中,在TSQL环境下测试成功
那么在VC++下呢,首先连接数据库,执行该语句,源码如下:
- // DBTransaction.cpp : 实现文件
- //
- #include "stdafx.h"
- #include "shlobj.h"
- #include "ImportExport.h"
- #include "DBTransaction.h"
- // DBTransaction
- IMPLEMENT_DYNAMIC(DBTransaction, CWnd)
- DBTransaction::DBTransaction()
- {
- AfxOleInit();
- try
- {
- m_hrs = m_conn.CreateInstance("ADODB.Connection");
- if(m_hrs == S_OK)
- {
- m_hrs = m_conn->Open("driver={SQL Server};Server=.;DATABASE=master;UID=sa;PWD=sa","","",adModeUnknown);
- //this->MessageBox(L"数据库连接成功");
- }
- }
- catch(_com_error e)
- {
- CString msg;
- msg.Format(L"数据库连接失败,/n错误信息:%s",e.ErrorMessage());
- this->MessageBox(msg);
- }
- }
- DBTransaction::DBTransaction(CString IP,CString DB,CString UID,CString PWD)
- {
- // AfxOleInit();
- CString _IP(IP);
- CString _DB(DB);
- CString _UID(UID);
- CString _PWD(PWD);
- CString connstr = L"driver={SQL Server};Server=" + _IP + ";DATABASE=" + _DB + ";UID=" + _UID + ";PWD="+_PWD;
- try
- {
- m_hrs = m_conn.CreateInstance("ADODB.Connection");
- if(m_hrs == S_OK)
- {
- m_hrs = m_conn->Open(_bstr_t(connstr),"","",adModeUnknown);
- //this->MessageBox(L"数据库连接成功");
- }
- }
- catch(_com_error e)
- {
- CString msg;
- msg.Format(L"数据库连接失败,/n错误信息:%s",e.ErrorMessage());
- this->MessageBox(msg);
- }
- }
- HRESULT DBTransaction::TraceConn()
- {
- return this->m_hrs;
- }
- void DBTransaction::GetDataBases()
- {
- variant_t var;
- CString sql("select name from sysdatabases");
- _RecordsetPtr set;
- set.CreateInstance("ADODB.Recordset");
- set->Open((_variant_t)sql,_variant_t((IDispatch *)this->m_conn,true),adOpenStatic,adLockOptimistic,adCmdText);
- while(!set->EndOfFile)
- {
- var = (_bstr_t)set->GetCollect("name");
- if(var.vt != VT_NULL)
- this->databases.AddTail((LPCTSTR)_bstr_t(var));
- set->MoveNext();
- }
- }
- void DBTransaction::GetTables()
- {
- CString empty(" ");
- CString tablePropertis;
- variant_t var;
- CString sql("select name,(case when xtype='u' then '表' when xtype='v' then '视图' end) xtype,crdate from sysobjects where xtype='u' or xtype='v'");
- _RecordsetPtr set;
- set.CreateInstance("ADODB.Recordset");
- set->Open((_variant_t)sql,_variant_t((IDispatch *)this->m_conn,true),adOpenStatic,adLockOptimistic,adCmdText);
- while(!set->EndOfFile)
- {
- //循环读取数据库,并写入数组
- tablePropertis.Empty();
- var = (_bstr_t)set->GetCollect("name");
- if(var.vt != VT_NULL)
- tablePropertis += (LPCTSTR)_bstr_t(var);
- var = (_bstr_t)set->GetCollect("crdate");
- if(var.vt != VT_NULL)
- tablePropertis += empty + (LPCTSTR)_bstr_t(var);
- var = (_bstr_t)set->GetCollect("xtype");
- if(var.vt != VT_NULL)
- tablePropertis += empty + (LPCTSTR)_bstr_t(var);
- tables.AddTail(tablePropertis);
- set->MoveNext();
- }
- }
- void DBTransaction::Exec(CString sql)
- {
- _CommandPtr comm;
- comm.CreateInstance("ADODB.Command");
- comm->ActiveConnection = this->m_conn;
- comm->CommandText = _bstr_t(sql);
- //MessageBox(comm->CommandText);
- comm->CommandType = adCmdText;
- try
- {
- comm->Execute(NULL,NULL,adCmdUnknown);
- }
- catch(_com_error &e)
- {
- MessageBox(e.Description());
- }
- }
- void DBTransaction::CloseDataBase()
- {
- this->m_conn->Close();
- }
- DBTransaction::~DBTransaction()
- {
- }
- BEGIN_MESSAGE_MAP(DBTransaction, CWnd)
- END_MESSAGE_MAP()
- // DBTransaction 消息处理程序