MFC中如何用ADO连接SQL Server 2005

发布时间: 更新时间: 总字数:1563 阅读时间:4m 作者: 分享

MFC中如何用ADO连接SQL Server 2005

VC++ ADO连接数据库

第一步:(以下语句插入相应的文件中,ADOConn.h,可能代码中已经有了)

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

第二步:

在工程中增加以下两个文件(ADOConn.h, ADOConn.ccp),增加方法如下,在FILEVIEW中的Source Files,点右键,选择添加文件到工程目录.选择ALL FILES,将以上两个文件选中. 文件可以自建,自建代码附后,也可以在网上下载.

以下是程序的关键的连接代码,我在测试过程中这部分的代码一定要写对.服务器的IP地址:我们公司是192.168.3.4,数据库的名称,以下红色部分,填自己的信息

BOOL CADOConn::OnInitDBConnect()
{ 
 CString strConnection;
 strConnection = "Provider=SQLOLEDB.1;Server=" + theApp.m_strServerApp + ";Database=DB_TestDatabase";
 // theApp.m_strServerApp:192.168.3.4
 ::CoInitialize(NULL);
 try
 {
  m_pConnection.CreateInstance(__uuidof(Connection));///////////////////////////////////////////////////////////////////////
#if !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)
#define AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_
#include 
//#include 
//#include 
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#include "sqlext.h"
#import "c:Program FilesCommon FilesSystemadomsado15.dll"no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")
class CADOConn 
...{
public:
    CADOConn();
    virtual ~CADOConn();
public:
    //ADODB::_ConnectionPtr   m_pConnect   
    //_ConnectionPtr m_pConnection;
    //_RecordsetPtr  m_pRecordset;
public:
    int FindRecordSet(CString m_sql,CString strField,CString str);
    int DeleteRecordSet(CString m_sql);
    //初始化——连接数据库
    static void OnInitDBConnect();
    //执行查询
    static _RecordsetPtr& GetRecordSet(_bstr_t bstrSQL);
    //Insetr Update _variant_t
    static BOOL ExecuteSQL(_bstr_t bstrSQL);
    void ExitConnect();
    //获得表长
    int GetRecordCount(CString strTable, int &nCount, CString strCondition = _T(""));
    //sql语句,待查属性列,查询结果
    static int SelectRecordSet(CString m_sql,CString strField,CString &strResult);
    //修改属性值
    int UpdateRecordSet(CString m_sql, CString strField, CString str);
    int InsertRecord(CString strTable, int nCount, CString *strFields,    CString *strValue, 
        CString strkeyfield = _T(""), CString strkey = _T(""));
};
#endif // !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)
// ADOConn.cpp: implementation of the CADOConn class.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "MyClient.h"
#include "ADOConn.h"
//#include "ExamTest.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
_ConnectionPtr m_pConnection;
_RecordsetPtr  m_pRecordset;
extern CMyClientApp theApp;
////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CADOConn::CADOConn()
...{
}
CADOConn::~CADOConn()
...{
}
void CADOConn::OnInitDBConnect()
...{    
    CString strUser;
    CString strPwd;
    CString strservername;
    CString strdbname;
    CString strConnection;
    strUser = theApp.m_strDBUserApp;
    strPwd = theApp.m_strDBPwdApp;
    strConnection = "Provider=SQLOLEDB.1;Server=" + theApp.m_strServerApp+ ";Database=TroubleCheck";
    ::CoInitialize(NULL);
    try
    ...{
        m_pConnection.CreateInstance(__uuidof(Connection));//******************
        _bstr_t strConnect = (_bstr_t)strConnection;
        m_pConnection->Open(strConnect,(_bstr_t)strUser,(_bstr_t)strPwd,adModeUnknown);//****************
        theApp.isDBOK = TRUE;
    }
    catch(_com_error e)
    ...{
        //AfxMessageBox(e.Description()+"系统退出!");
        //exit(0);//***********************************
        AfxMessageBox("数据库连接失败,请重新配置连接数据库!");
        ::SendMessage(theApp.hwndmainDlg,WM_SETDBSERVER,0,0);
    }
}
_RecordsetPtr& CADOConn::GetRecordSet(_bstr_t bstrSQL)
...{
    try
    ...{
        if (m_pConnection == NULL)
        ...{
            OnInitDBConnect();
        }
        m_pRecordset.CreateInstance(_uuidof(Recordset));
        m_pRecordset->Open(bstrSQL,(_variant_t)m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
    }
    catch (_com_error e)
    ...{
        AfxMessageBox(e.Description()+"系统退出!");
        exit(0);
        //AfxMessageBox("2");
    }
    return m_pRecordset;
//     _variant_t var;
}
BOOL CADOConn::ExecuteSQL(_bstr_t bstrSQL)//************************
...{
    try
    ...{
        if (m_pConnection == NULL)
        ...{
            //AfxMessageBox("自动连接!");
            OnInitDBConnect();
        }
        m_pConnection->Execute(bstrSQL ,NULL,adCmdText);
    }
    catch (_com_error e)
    ...{
        AfxMessageBox(e.Description()+"系统退出!");
        exit(0);
    }
    return true;
}
void CADOConn::ExitConnect()
...{    
    if (m_pRecordset != NULL)
    ...{    
        m_pRecordset->Close();
        m_pConnection->Close();
        ::CoUninitialize();
    }
}
int CADOConn::SelectRecordSet(CString m_sql,CString strField,CString &strResult)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord != NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{
                varTemp = m_pRecord->GetCollect((_bstr_t)strField);
                if (varTemp.vt == VT_NULL)
                ...{
                    strResult = _T("");
                }
                strResult = (LPCSTR)_bstr_t(varTemp);
                //strResult.TrimRight();
                m_pRecord->MoveNext();        
            }
        }
        else
            return 0;
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败,请检查数据库!");
        return 0;
    }
    return 1;
}
ADOConn.cpp
int CADOConn::UpdateRecordSet(CString m_sql, CString strField, CString str)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{    
                m_pRecord->PutCollect((_bstr_t)strField,_variant_t(str));
                m_pRecord->Update();
                m_pRecord->MoveNext();        
            }
        }
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败,请检查数据库!");
        return 0;
    }
    return 1;
}
int CADOConn::FindRecordSet(CString m_sql, CString strField, CString str)
...{
    _variant_t    varTemp;
    _RecordsetPtr m_pRecord = NULL;
    CString strTemp;
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{    
                varTemp = m_pRecord->GetCollect((_bstr_t)strField);
                if (varTemp.vt == VT_NULL)
                ...{
                    strTemp = _T("");
                }
                strTemp = (LPCSTR)_bstr_t(varTemp);
                strTemp.TrimRight();
                if (!strcmp(strTemp,str))
                    return 1;
                m_pRecord->MoveNext();        
            }
        }
    }
    catch (...) 
    ...{
        AfxMessageBox("读取失败,请检查数据库!");
        return 0;
    }
    return 1;
}
int CADOConn::DeleteRecordSet(CString m_sql)
...{
    if(!ExecuteSQL(_bstr_t(m_sql)))
    ...{
        return 0;
    }
    else
    ...{
        AfxMessageBox("删除成功");
        return 1;
    }
}
int CADOConn::GetRecordCount(CString strTable, int &nCount, CString strCondition)
...{
    _RecordsetPtr m_pRecord = NULL;
    int nCounter = 0;
    CString m_sql;
    m_sql = "select * from " + strTable + strCondition;
    try
    ...{
        m_pRecord = GetRecordSet((_bstr_t)m_sql);
        if (m_pRecord!=NULL)
        ...{
            if (!m_pRecord->adoBOF)
            ...{
                m_pRecord->MoveFirst();
            }
            while(!m_pRecord->adoEOF)
            ...{
                m_pRecord->MoveNext();
                nCounter++;
            }
        }
        nCount = nCounter;
    }
    catch (...) 
    ...{
        AfxMessageBox("数据库错误");
        return 0;
    }
    return 1;
}
//return -1:主键冲突/////////////////注意m_Storedata=VT_NULL的情况,要做处理
//strTable:表名
//nCount:属性列数
//strFields:属性列名
//strValue:属性列值
//strKeyfield:主键名
//strKey:主键属性值
int CADOConn::InsertRecord(CString strTable, int nCount, CString *strFields,
                            CString *strValue,CString strKeyfield, CString strKey)
...{
    CString m_sql;
    _RecordsetPtr m_pRecord;
    _variant_t m_Storedata;
    try
    ...{
        if (strKeyfield != "")
        ...{
            m_sql = "select * from " + strTable;
            m_pRecord = GetRecordSet((_bstr_t)m_sql);
            if (m_pRecord!=NULL)
            ...{
                if (!m_pRecord->adoBOF)
                ...{
                    m_pRecord->MoveFirst();
                }
                while (!m_pRecord->adoEOF)
                ...{
                    m_Storedata = m_pRecord->GetCollect((_bstr_t)strKeyfield);
                    if ((LPCSTR)_bstr_t(m_Storedata)==strKey)
                    ...{
                        return 0;
                    }
                    m_pRecord->MoveNext();
                }
            }    
        }
        m_sql = "insert into " + strTable + " (";
        for (int i=0; i
        ...{
            m_sql += strFields[i] + ",";
        }
        m_sql += strFields[nCount-1] + ") ";
        m_sql += "values (";
        for (i=0; i
        ...{
            m_sql += strValue[i] + ",";
        }
        m_sql += strValue[nCount-1] + ") ";
        ExecuteSQL((_bstr_t)m_sql);
    }
    catch (...) 
    ...{
        AfxMessageBox("数据库错误");
        return 0;
    }
    return 1;
}

参考

Home Archives Categories Tags Docs