VS中MFC连接SQL SERVER

1.新建数据库 VCTest;

2.控制面板，管理工具，数据源ODBC，系统DSN，添加，VCTest，服务器DELL-PC，使用用户登录，wei，123456，更改默认数据库为VCTest，测试数据源，确定。

3.在stdafx.h头文件中添加下面代码，路径根据实际情况即可

#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

4.单例模式

#pragma once
class CMyDB
{
public:
    CMyDB(void);
    ~CMyDB(void);

public:
    int DBConnect();
    _ConnectionPtr m_pConnection;
    _RecordsetPtr m_pRst;
    static CMyDB *getInstance();
    _ConnectionPtr getDBConnect();

private:
    static CMyDB *myDBInstance;
};

#include "stdafx.h"
#include "MyDB.h"

CMyDB::CMyDB(void)
{
    m_pConnection=NULL;
}

CMyDB::~CMyDB(void)
{
}

CMyDB *CMyDB::myDBInstance=NULL;

CMyDB *CMyDB::getInstance()
{
    if (myDBInstance==NULL)
    {
        myDBInstance=new CMyDB();
    }
    return myDBInstance;
}

_ConnectionPtr CMyDB::getDBConnect()
{
    if (m_pConnection==NULL)
    {
        DBConnect();
    }   
    return m_pConnection;
}

int CMyDB::DBConnect()
{
    ::CoInitialize(NULL);//初始化数据库连接
    HRESULT hr=NULL;
    try{
        hr=m_pConnection.CreateInstance(_uuidof(Connection));
        if (SUCCEEDED(hr))
        {
            m_pConnection->ConnectionString=("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=wei;Password=453276;Initial Catalog =VCTest; Data Source = DELL-PC");
            m_pConnection->Open("","","",adConnectUnspecified);
            if (FAILED(hr))
            {
                AfxMessageBox(_T("数据库连接失败"));
                return 0;
            }else{
                return 1;
            }
        }else{
            return 0;
        }
    }catch(_com_error e){
        CString error_message;
        error_message.Format(TEXT("连接数据库失败 !\r\n 错误信息:%s(%ld)"),e.ErrorMessage(),e.Error());
        AfxMessageBox(error_message);
        return 0;
    }
}

5.操作

#pragma once
#include "MyDB.h"
#include "MyMSG.h"

class CMessageDao
{
public:
    CMessageDao(void);
    ~CMessageDao(void);

    CList<MyMSG> *getMSG();
    int deleteMSG(CString s_time);
    int insertMSG(CString s_time,CString s_message);

private:
    _ConnectionPtr conn;
    _RecordsetPtr rst;
    CMyDB *myDB;
};

#include "stdafx.h"
#include "MessageDao.h"

CMessageDao::CMessageDao(void)
{
    //连接数据库
    myDB=CMyDB::getInstance();
    conn=myDB->getDBConnect();
    rst=myDB->m_pRst;
}

CList<MyMSG> *CMessageDao::getMSG()
{
    CString sql;
    sql.Format(_T("select * from t_message"));
    CString s_time,s_message;
    rst=conn->Execute(_bstr_t(sql),NULL,adCmdText);

    CList<MyMSG> *msg_list=new CList<MyMSG>();

    while (!rst->adoEOF)
    {
        s_time=(TCHAR *)(_bstr_t)rst->GetFields()->GetItem("time")->Value;
        s_message=(TCHAR *)(_bstr_t)rst->GetFields()->GetItem("message")->Value;

        MyMSG myMsg;
        myMsg.s_time=s_time.Trim();
        myMsg.s_message=s_message.Trim();
        msg_list->AddTail(myMsg);

        rst->MoveNext();
    }

    return msg_list;
}

int CMessageDao::deleteMSG(CString s_time)
{
    CString sql;
    sql.Format(_T("delete from t_message where time='%s'"),s_time);
    conn->Execute(_bstr_t(sql),NULL,adCmdText);
    return 1;
}

int CMessageDao::insertMSG(CString s_time,CString s_message)
{
    CString sql;
    sql.Format(_T("insert into t_message(time,message) values('%s','%s')"),s_time,s_message);
    conn->Execute(_bstr_t(sql),NULL,adCmdText);

    return 1;
}

CMessageDao::~CMessageDao(void)
{
}