dblink.cpp

ODBC API访问数据库的文章却少之又少。虽然用ODBC访问数据库比较麻烦

#include "StdAfx.h"
#include ".\dblink.h"

CDbLink::CDbLink(void)
{
	m_strDSN = "master";
	m_strUSER = "hskj";
	m_strPWD = "newtech";

	henv = SQL_NULL_HANDLE;
	hdbc = SQL_NULL_HANDLE;

	m_bLink = FALSE;
	OpenDatabase();	
}

CDbLink::~CDbLink(void)
{
	if(m_bLink)
	{
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		m_bLink = FALSE;
	}
}

CDbLink::CDbLink(CString strDSN, CString strUSER, CString strPWD)
{
	henv = SQL_NULL_HANDLE;
	hdbc = SQL_NULL_HANDLE;

	m_strDSN = strDSN;
	m_strUSER = strUSER;
	m_strPWD = strPWD;

	m_bLink = FALSE;
	OpenDatabase();
}

BOOL CDbLink::OpenDatabase()
{
	SQLINTEGER cbLenth = 0 ;	
	SQLRETURN retcode;

	retcode = SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"SQL Server","DSN=master\0Server=(local)\0Database=master\0\0");
	if(!retcode)
	{
		AfxMessageBox("系统数据源配置失败!");
		return 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) 
		{
			retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
			if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
			{
				retcode = SQLConnect(hdbc, (SQLCHAR*)(LPCTSTR)m_strDSN, SQL_NTS, (SQLCHAR*)(LPCTSTR)m_strUSER, SQL_NTS, 
					(SQLCHAR*)(LPCTSTR)m_strPWD, SQL_NTS);
				if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
				{
					AfxMessageBox("数据库连接失败!") ;
					SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
					SQLFreeHandle(SQL_HANDLE_ENV, henv);
					return FALSE;
				}
				else
				{
					m_bLink = TRUE;
					return TRUE;
				}
			} 
			else
			{
				AfxMessageBox("连接句柄分配出错") ;
				SQLFreeHandle(SQL_HANDLE_DBC, hdbc); 
				SQLFreeHandle(SQL_HANDLE_ENV, henv);
				return FALSE;
			}
		}
		else
		{
			AfxMessageBox("属性设置出错！") ;
			SQLFreeHandle(SQL_HANDLE_ENV, henv);
			return FALSE;
		}
	}
	else
	{
		AfxMessageBox("环境变量分配出错！") ;
		SQLFreeHandle(SQL_HANDLE_ENV, henv);
		return FALSE;
	}
}

BOOL CDbLink::IsDatabaseExisted(CString strDbName)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	CString strSQL;
	strSQL.Format("SELECT * FROM sysdatabases WHERE name='%s'", strDbName);
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
	{	
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR)
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;
		}
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO))
		{		
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return  TRUE;
		}
		else
		{
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
			return FALSE;
		}			
	}
	else
	{	
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
		return FALSE;	
	}
}

BOOL CDbLink::CreateDatabase(CString strDbName)
{
	SQLHSTMT hstmt ;
	SQLRETURN retcode;
	SQLINTEGER cbLenth = 0 ;

	BOOL bIsExisted = IsDatabaseExisted(strDbName);
	if(bIsExisted)
	{
		return TRUE;
	}
	else
	{
		CString strSQL;
		strSQL.Format("CREATE DATABASE [%s]  ON (NAME = N'%s_dat', FILENAME = N'D:\\导出数据库文件\\%s.mdf' ,SIZE = 39, FILEGROWTH = 2) LOG ON (NAME = N'%s_log', FILENAME = N'D:\\导出数据库文件\\%s.ldf' , SIZE = 2, FILEGROWTH = 1) COLLATE Chinese_PRC_CI_AS", 
			strDbName,strDbName,strDbName,strDbName,strDbName);
		retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
		{	
			if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR)
			{
				SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
				return TRUE;
			}
			else
			{
				SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	
				return FALSE;
			}			
		}
		else
		{	
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
			return FALSE;	
		}
	}
}