MicroSoft SQL Server Manager 通过ODBC与VS进行连接操作示例程序

建立数据源的时候，ODBC是多少位的VS工程就应该是多少位的，我这里用的是32位的ODBC，因此VS工程也是32位的。

#ifndef __ODBC_H__
#define __ODBC_H__

#include<windows.h>  
#include<stdio.h>  
#include<stdlib.h>  
#include<string> 
#include<iostream>  
#include "sql.h"  
#include "sqltypes.h"  
#include "sqlext.h"    

class MyODBCAPI
{
public:
    MyODBCAPI();

    ~MyODBCAPI();
    SQLHENV henv; //环境句柄
    SQLHDBC hdbc; //连接句柄
    SQLHSTMT hstmt; //语句句柄
    SQLRETURN retcode;
    BOOL bInit;
    BOOL InitODBC();
    //连接ODBC数据源
    BOOL Connect(char* odbcName, char* userID, char* pass);
    BOOL ExecSQL(char* sql);
    void QueryCustomer(int iMode,char* Field, char* Target);
    void InsertCustomer(char* CustomerID, char* CompanyName);
    void DeleteCustomer(char* CustomerID);

};


#endif

#include<windows.h>  
#include<stdio.h>  
#include<stdlib.h>  
#include<string>  
#include<iostream>  
#include "sql.h"  
#include "sqltypes.h"  
#include "sqlext.h"  
using namespace std;
#include "odbc.h"

MyODBCAPI::MyODBCAPI()
{
    InitODBC();  //初始化ODBC数据源
}

MyODBCAPI::~MyODBCAPI()
{
    if (hstmt != NULL)
    {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    }
    if (hdbc != NULL)
    {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    if (henv != NULL)
    {
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
    }
}

BOOL MyODBCAPI::InitODBC()
{
    henv = NULL;
    hdbc = NULL;
    hstmt = NULL;
    bInit = false;
    //分配环境句柄
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    {
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
        if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
        {
            bInit = true;
        }
    }
    return bInit;
}

BOOL MyODBCAPI::Connect(char* odbcName, char* userID, char* pass)
{
    if (!bInit)
    {
        cout<<"Inite ODBC API failed"<<endl;
        return false;
    }
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

    if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    {
        SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)10, 0);
        retcode = SQLConnect(hdbc, (SQLCHAR*)odbcName, SQL_NTS, (SQLCHAR*)userID, SQL_NTS, (SQLCHAR*)pass, SQL_NTS);
        if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
        {
            cout<<"connect db successed!"<<endl;
            return true;
        }
        else
        {
            cout<<"connect db failed!"<<endl;
            return false;
        }
    }
    else
    {
        cout<<"allocate handle failed！"<<endl;
        return false;
    }

}
BOOL MyODBCAPI::ExecSQL(char* sql)
{
    cout<<sql<<endl;
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
    {
        retcode = SQLExecDirect(hstmt, (SQLCHAR*)sql, SQL_NTS);

        if ((retcode ==  SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
        {
            cout<<"Exec sql statement successed!"<<endl;
            return true;
        }
        else
        {
            cout<<"Exec sql statement failed!"<<endl;
            return false;
        }
    }
    else
    {
        cout<<"Allocate sql statement handle failed!"<<endl;
        return false;
    }
}
void MyODBCAPI::QueryCustomer(int iMode, char* Field, char* Target)
{ 
    if (!Connect("yyc", "Test123", "Yycwto103"))
    {
        cout<<"Connect db failed!"<<endl;
        return;
    }
    char sql[100];
    if (iMode == 0)//全部查询
    {
        if (!ExecSQL("SELECT * FROM dbo.Customer"))
        {
            cout<<"Exec sql statement failed!"<<endl;
            return;
        }
    }
    else if (iMode == 1)//匹配查询
    {
        sprintf(sql,"SELECT * FROM dbo.Customer where %s='%s'",Field,Target);
        if (!ExecSQL(sql))
        {
            cout<<"Exec sql statement failed!"<<endl;
            return;
        }
    }
    else
    {
        return;
    }
    long cbNameLen = 500;
    char* CustomerID[500];
    SQLBindCol(hstmt, 1, SQL_C_CHAR,(void*)CustomerID, cbNameLen, &cbNameLen);
    char* CompanyName[500];
    SQLBindCol(hstmt, 2, SQL_C_CHAR,(void*)CompanyName, cbNameLen, &cbNameLen);
    int i = 0;
    while (SQLFetch(hstmt) == SQL_SUCCESS && i < 100)
    {
        i++;
        if (retcode == SQL_NO_DATA_FOUND)
            break;
        printf("[%d]%s, %s\r\n",i,CustomerID,CompanyName);
    }

}
void MyODBCAPI::InsertCustomer(char* CustomerID, char* CompanyName)
{
    if (!Connect("yyc", "Test123", "Yycwto103"))
    {
        cout<<"Connect db failed!"<<endl;
        return;
    }
    char sql[100];
    sprintf(sql,"INSERT INTO Customer VALUES('%s', '%s') SELECT * FROM Customer",CustomerID,CompanyName);

    if (!ExecSQL(sql))
    {
        cout<<"Insert customer info failed!"<<endl;
        return;
    }
    cout<<"Insert customer info successed!"<<endl;
    return;
}
void MyODBCAPI::DeleteCustomer(char* CustomerID)
{
    if (!Connect("yyc", "Test123", "Yycwto103"))
    {
        cout<<"Connect db failed!"<<endl;
        return;
    }
    char sql[100];
    sprintf(sql,"DELETE FROM Customer WHERE CustomerID='%s'",CustomerID);
    if (!ExecSQL(sql))
    {
        cout<<"Delete customer info failed!"<<endl;
        return;
    }
    cout<<"Delete customer info successed!"<<endl;
}

#include<windows.h>  
#include<stdio.h>  
#include<stdlib.h>  
#include<string>  
#include<iostream> 
#include <time.h>   
using namespace std;  
#include "odbc.h"   


int main()  
{
    int i = 10;
    char* odbcName = "yyc";   //数据源名称  
    char* userID = "Test123";  //用户名  
    char* pass = "Yycwto103";  //密码
    time_t t;

    MyODBCAPI odbc;
    odbc.InitODBC();
    bool bRtn = odbc.Connect(odbcName, userID, pass);//连接数据库
    odbc.InsertCustomer("hpe","test");//插入一条记录
    odbc.DeleteCustomer("hpe");//删除带有某一字段的记录
    odbc.QueryCustomer(0,NULL,NULL);//全部打印记录
    odbc.QueryCustomer(1,"CompanyName","test");//筛选打印记录

    getchar();

    return 0;     
}