使用VC6进行ADO编程，使用SQL SERVER 2000+WINDOWS SERVER 2003+VC6(第三部分)
目的是将SUN等服务器产生的日志文件--文本格式，写入到SQL SERVER数据库,实现功能为：
1.修改源日志格式为以逗号分割字段，写入成一个新的日志文件。
2.程序读出新日志文件，写入到数据库（分别利用RecordSet和存储过程实现）。
由于是初学VC，不当之处请高人指正，万分感谢！
第三部分
由于工作需要，本人初学VC6，在网上找了很多关于VC6进行ADO编程文章，但大部分都以ACCESS为数据库，即使是使用SQL SERVER 2000也没有用ODBC编程，不利于初学者学习，本人历时一个月，钻研并作出以下程序，望能抛砖引玉，对中国程序员初学者尽自己的一份力！
重要提示：请大家安装SQL SERVER 2000 SP3及VISUAL STUDIO SP6，否则程序可能不能正常运行！
本程序功能已用纯C及VB6混合编程实现，但为了提高程序的执行效率，所以用VC6重写。
如下为vcadoDlg.cpp的完整源代码：
//
#include "stdafx.h"
#include "vcado.h"
#include "vcadoDlg.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif
extern CVcadoApp theApp;
/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About
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()
/////////////////////////////////////////////////////////////////////////////
// CVcadoDlg dialog
CVcadoDlg::CVcadoDlg(CWnd* pParent /*=NULL*/)
: CDialog(CVcadoDlg::IDD, pParent)
{
//{{AFX_DATA_INIT(CVcadoDlg)
m_datetime = COleDateTime::GetCurrentTime();
//}}AFX_DATA_INIT
// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
m_nCurrentSel = -1;
m_bAutoSave = TRUE;
m_bSuccess = FALSE;
}
void CVcadoDlg::DoDataExchange(CDataExchange* pDX)
{
CDialog::DoDataExchange(pDX);
//{{AFX_DATA_MAP(CVcadoDlg)
DDX_Control(pDX, IDC_DATETIME, m_datetime2);
DDX_Control(pDX, IDC_NAME2, m_name2);
DDX_Control(pDX, IDC_NAME, m_name);
DDX_Control(pDX, IDC_EXPLAIN, m_explain);
DDX_Control(pDX, IDC_IP, m_ip);
DDX_Control(pDX, IDC_FILEOPEN, m_fileopen);
DDX_Control(pDX, IDC_OUT, m_out);
DDX_Control(pDX, IDC_IN, m_in);
DDX_Control(pDX, IDC_STRINGADD, m_stringadd);
DDX_Control(pDX, IDC_LIST1, m_list);
DDX_Control(pDX, IDC_ADODC1, m_adodc);
DDX_Control(pDX, IDC_DATAGRID1, m_datagrid);
DDX_Text(pDX, IDC_DATETIME, m_datetime);
//}}AFX_DATA_MAP
}
BEGIN_MESSAGE_MAP(CVcadoDlg, CDialog)
//{{AFX_MSG_MAP(CVcadoDlg)
ON_WM_SYSCOMMAND()
ON_WM_PAINT()
ON_WM_QUERYDRAGICON()
ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
ON_BN_CLICKED(IDC_BUTTON2, Oninsertdb)
ON_BN_CLICKED(IDC_BUTTON3, Onexit)
ON_BN_CLICKED(IDC_BUTTON4, Onfilereform)
ON_BN_CLICKED(IDC_BUTTON5, Onstringadd)
ON_BN_CLICKED(IDC_BUTTON7, Onspinsert)
ON_BN_CLICKED(IDC_BUTTON8, Onoutinput)
//}}AFX_MSG_MAP
END_MESSAGE_MAP()
/////////////////////////////////////////////////////////////////////////////
// CVcadoDlg message handlers
BOOL CVcadoDlg::OnInitDialog()
{
CDialog::OnInitDialog();
HRESULT hr;
try
{
hr = m_pConnection1.CreateInstance("ADODB.Connection");///创建Connection对象
if(SUCCEEDED(hr))
{
hr = m_pConnection1->Open("DSN=test;UID=;PWD=;","","",adModeUnknown);///连接数据库
}
AfxMessageBox("存储过程连接数据库成功!");
}
}
catch(_com_error e)///捕捉异常
{
CString errormessage;
errormessage.Format("存储过程连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///显示错误信息
return FALSE;
}
CString path;
path="d:\\";
SetCurrentDirectory(path);
AfxMessageBox("当前工作路径为d:\\");
pedit1=(CEdit*)GetDlgItem(IDC_IN);
pedit2=(CEdit*)GetDlgItem(IDC_OUT);
m_in.GetDlgItem (IDC_IN);
m_out.GetDlgItem (IDC_OUT);
m_fileopen.GetDlgItem (IDC_FILEOPEN);
pfileopen=(CEdit*)GetDlgItem(IDC_FILEOPEN);
pstringadd=(CStatic*)GetDlgItem(IDC_STRINGADD);
CString edit1,edit2,fileopen;
edit1="in2.txt";
edit2="out2.txt";
fileopen="out2.txt";
pedit1->SetWindowText(edit1);
pedit2->SetWindowText(edit2);
pfileopen->SetWindowText(fileopen);
//////////为列表控件添加列//////////
m_list.InsertColumn(0,"datetime",LVCFMT_LEFT,130);
m_list.InsertColumn(1,"name",LVCFMT_LEFT,50);
m_list.InsertColumn(2,"name2",LVCFMT_LEFT,50);
m_list.InsertColumn(3,"ip",LVCFMT_LEFT,100);
m_list.InsertColumn(4,"explain",LVCFMT_LEFT,700);
//////////读取数据库中的信息添加到列表控件///////////
_variant_t vdatetime,vname,vname2,vip,vexplain;
vdatetime.vt =VT_DATE;
vdatetime.date =datetime;
try
{
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open("SELECT * FROM T2",_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
m_bSuccess = TRUE;
while(!m_pRecordset->adoEOF)
{
vdatetime = m_pRecordset->GetCollect("datetime");
vname = m_pRecordset->GetCollect("name");
vname2 = m_pRecordset->GetCollect("name2");
vip = m_pRecordset->GetCollect("ip");
vexplain = m_pRecordset->GetCollect("explain");
nItem=m_list.InsertItem(0xffff,(_bstr_t)vdatetime);
m_list.SetItem(nItem,1,1,(_bstr_t)vname,NULL,0,0,0);
m_list.SetItem(nItem,2,1,(_bstr_t)vname2,NULL,0,0,0);
m_list.SetItem(nItem,3,1,(_bstr_t)vip,NULL,0,0,0);
m_list.SetItem(nItem,4,1,(_bstr_t)vexplain,NULL,0,0,0);
m_pRecordset->MoveNext();
}
AfxMessageBox("RecordSet读取数据库成功!");
}
catch(_com_error e)///捕捉异常
{
AfxMessageBox("RecordSet读取数据库失败!");///显示错误信息
}
// 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
// TODO: Add extra initialization here
return TRUE; // return TRUE unless you set the focus to a control
}
void CVcadoDlg::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 CVcadoDlg::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 CVcadoDlg::OnQueryDragIcon()
{
return (HCURSOR) m_hIcon;
}
void CVcadoDlg::OnOK()
{
if(m_bSuccess)
{
m_pRecordset->Update();
m_pRecordset->Close();
}
m_pConnection1->Close();
CoUninitialize();
CDialog::OnOK();
}
void CVcadoDlg::OnButton1()
{
// TODO: Add your control notification handler code here
//WinExec(strcat("notepad.exe ",m_fileopen),SW_SHOW);
MessageBeep(0xFFFFFFFF);
CString fileopen,text;
pfileopen->GetWindowText(fileopen);
text="notepad.exe "+fileopen;
WinExec(text,SW_SHOW);
}
void CVcadoDlg::Oninsertdb()
{
// TODO: Add your control notification handler code here
if(UpdateData())
if(m_name.GetWindowTextLength () >0)
{
m_pRecordset->MoveLast ();
m_pRecordset->AddNew();
if(!m_pRecordset->adoEOF && nItem>= 0 && m_bAutoSave)
{
m_datetime2.GetWindowText (datetime2);
m_name.GetWindowText (name);
m_name2.GetWindowText (name2);
m_ip.GetWindowText (ip);
m_explain.GetWindowText (explain);
m_pRecordset->PutCollect ("datetime",(_bstr_t)datetime2);
m_pRecordset->PutCollect ("name",(_bstr_t)name);
m_pRecordset->PutCollect ("name2",(_bstr_t)name2);
m_pRecordset->PutCollect ("ip",(_bstr_t)ip);
m_pRecordset->PutCollect ("explain",(_bstr_t)explain);
m_list.SetItem (nItem,0,LVIF_TEXT,(_bstr_t)datetime2,NULL,0,0,0);
m_list.SetItem (nItem,1,LVIF_TEXT,(_bstr_t)name,NULL,0,0,0);
m_list.SetItem (nItem,2,LVIF_TEXT,(_bstr_t)name2,NULL,0,0,0);
m_list.SetItem (nItem,3,LVIF_TEXT,(_bstr_t)ip,NULL,0,0,0);
m_list.SetItem (nItem,4,LVIF_TEXT,(_bstr_t)explain,NULL,0,0,0);
}
m_list.SetItemState(nItem,LVIS_SELECTED|LVIS_FOCUSED,LVIS_SELECTED|LVIS_FOCUSED);
m_list.SetHotItem(nItem);
m_list.SetFocus();
}
else
AfxMessageBox("请输入用户名!");
nItem=nItem+1;
m_adodc.Refresh ();
m_datagrid.Refresh ();
}
void CVcadoDlg::Onexit()
{
// TODO: Add your control notification handler code here
m_pConnection1->Close();
CoUninitialize();
CDialog::OnOK();
}
void CVcadoDlg::Onfilereform()
{
// TODO: Add your control notification handler code here
FILE *in,*out;
char ch,ch1,ch2,ch3,ch4;
int n=1;
int e=-1;
/*if (argc!=3)
{printf("You forgot to enter a filename!\n");
return 0;
}*/
CString edit1,edit2,alltext;
pedit1->GetWindowText(edit1);
pedit2->GetWindowText(edit2);
if((in=fopen(edit1,"r"))==NULL)
{AfxMessageBox("不能读入日志文件!");
}
if((out=fopen(edit2,"w+"))==NULL)
{AfxMessageBox("不能写入数据缓冲文件!");
}
ch1='"';
ch2=',';
fputc(ch1,out);
while((ch=fgetc(in))!=EOF)
{
if(ch=='\n'&&(ch4=ch3=fgetc(in))==EOF)
{fputc(ch1,out);
fputc('\n',out);
n=1;
continue;
}
else if(ch=='\n')
{fputc(ch1,out);
fputc('\n',out);
fputc(ch1,out);
fputc(ch4,out);
n=1;
continue;
}
//printf("\nCurrent position is byte %d\n",ftell(in));
cout<<"\nCurrent position is byte "<<ftell(in)<<endl;
if(ch=='.'&&n==1)
{n=2;
fputc(ch1,out);
fputc(ch2,out);
fputc(ch1,out);
continue;
}
if(ch==' ')
{fputc(ch1,out);
fputc(ch2,out);
fputc(ch1,out);
continue;
}
putchar(ch);
fputc(ch,out);
}
fclose(in);
fclose(out);
AfxMessageBox("日志文件成功转换格式!");
}
void CVcadoDlg::Onstringadd()
{
// TODO: Add your control notification handler code here
CString edit1,edit2,alltext;
//pedit1->GetWindowText(edit1);
//pedit2->GetWindowText(edit2);
m_in.GetWindowText (edit1);
m_out.GetWindowText (edit2);
if(edit1=="王")
{MessageBeep(0xFFFFFFFF);
alltext=edit1+"中王"+":你好棒呦!!!!!";
}
else alltext="读入日志文件数据-->输出数据到缓冲文件-->从缓冲文件读出数据-->插入数据到数据库。";
pstringadd->SetWindowText(_T(alltext));
}
void CVcadoDlg::Onspinsert()
{
// TODO: Add your control notification handler code here
try
{
m_pCommand.CreateInstance ("ADODB.Command");
m_pCommand->ActiveConnection =m_pConnection1;
m_pCommand->CommandType =adCmdStoredProc;
m_pCommand->CommandText =_bstr_t("insert_T2_1");
//AfxMessageBox("Command建立成功!\n");
}
catch(_com_error eee)///捕捉异常
{
CString errormessage;
errormessage.Format("Command建立失败!\r\n错误信息:%s",eee.ErrorMessage());
AfxMessageBox(errormessage);///显示错误信息
}
m_datetime2.GetWindowText (datetime2);
m_name.GetWindowText (name);
m_name2.GetWindowText (name2);
m_ip.GetWindowText (ip);
m_explain.GetWindowText (explain);
try
{_ParameterPtr param;
param.CreateInstance ("ADODB.Parameter");
param=m_pCommand->CreateParameter ("datetime",adDBTimeStamp,adParamInput,sizeof(datetime2),_variant_t(_bstr_t(datetime2)));
m_pCommand->Parameters ->Append (param);
param=m_pCommand->CreateParameter ("name",adVarChar,adParamInput,50,_variant_t(_bstr_t(name)));
m_pCommand->Parameters ->Append (param);
param=m_pCommand->CreateParameter ("name2",adVarChar,adParamInput,50,_variant_t(_bstr_t(name2)));
m_pCommand->Parameters ->Append (param);
param=m_pCommand->CreateParameter ("ip",adVarChar,adParamInput,50,_variant_t(_bstr_t(ip)));
m_pCommand->Parameters ->Append (param);
param=m_pCommand->CreateParameter ("explain",adVarChar,adParamInput,300,_variant_t(_bstr_t(explain)));
m_pCommand->Parameters ->Append (param);
m_pCommand->Execute (NULL,NULL,adCmdStoredProc);
m_pCommand.Detach ();
m_adodc.Refresh ();
m_datagrid.Refresh ();
//AfxMessageBox("Parameter建立成功，存储过程插入数据库成功!");
}
catch(_com_error ee)///捕捉异常
{
CString Error=ee.ErrorMessage ();
AfxMessageBox("存储过程插入数据库失败!");///显示错误信息
AfxMessageBox(ee.ErrorMessage ());
}
//AfxMessageBox("Parameter建立成功，存储过程插入数据库成功!");
}
void CVcadoDlg::Onoutinput()
{
// TODO: Add your control notification handler code here
FILE *in,*out;
char ch,ch1,ch2,ch3,ch4;
int n=1;
int e=-1;
/*if (argc!=3)
{printf("You forgot to enter a filename!\n");
return 0;
}*/
CString edit1,edit2,alltext;
pedit1->GetWindowText(edit1);
pedit2->GetWindowText(edit2);
if((in=fopen(edit1,"r"))==NULL)
{AfxMessageBox("不能打开日志文件!");
}
if((out=fopen(edit2,"w+"))==NULL)
{AfxMessageBox("不能写入日志缓冲文件!");
}
ch1='"';
ch2=',';
//fputc(ch1,out);
while((ch=fgetc(in))!=EOF)
{
if(ch=='\n'&&(ch4=ch3=fgetc(in))==EOF)
{//fputc(ch1,out);
fputc('\n',out);
n=1;
continue;
}
else if(ch=='\n')
{//fputc(ch1,out);
fputc('\n',out);
//fputc(ch1,out);
fputc(ch4,out);
n=1;
continue;
}
//printf("\nCurrent position is byte %d\n",ftell(in));
cout<<"\nCurrent position is byte "<<ftell(in)<<endl;
if(ch=='.'&&n==1)
{n=2;
//fputc(ch1,out);
fputc(ch2,out);
//fputc(ch1,out);
continue;
}
if(ch==' ')
{//fputc(ch1,out);
fputc(ch2,out);
//fputc(ch1,out);
continue;
}
putchar(ch);
fputc(ch,out);
}
fclose(in);
fclose(out);
AfxMessageBox("日志文件格式转换成功!");
if((out=fopen(edit2,"r"))==NULL)
{AfxMessageBox("不能读入数据缓冲文件!");
}
fseek(out,0,SEEK_SET);
datetime2="";
name="";
name2="";
ip="";
explain="";
int i=0;
int flag=0;
int m=0;
while((ch=fgetc(out))!=EOF)
{if(ch!='\n')
{if(ch==',')
{
m=m+1;
i=0;
continue;
}
switch(m)
{case 0:datetime2.Insert(i++,ch);
flag=1;
break;
case 1:name.Insert(i++,ch);
flag=1;
break;
case 2:name2.Insert(i++,ch);
flag=1;
break;
case 3:ip.Insert(i++,ch);
flag=1;
break;
case 4:explain.Insert(i++,ch);
flag=1;
break;
}
}
if(ch=='\n'&&flag==1)
{//AfxMessageBox(datetime2);
//AfxMessageBox(name);
//AfxMessageBox(name2);
//AfxMessageBox(ip);
//AfxMessageBox(explain);
m_datetime2.SetWindowText (datetime2);
m_name.SetWindowText (name);
m_name2.SetWindowText (name2);
m_ip.SetWindowText (ip);
m_explain.SetWindowText (explain);
Onspinsert();
datetime2="";
name="";
name2="";
ip="";
explain="";
m=0;
i=0;
flag=0;
}
}
fclose(out);
AfxMessageBox("Command建立成功!");
AfxMessageBox("Parameter建立成功，存储过程插入数据库成功!");
}
未完，持续，下次为第四部分
