C++连接SQL Server数据库并且完成数据库的插入，删除，便利

1.配置环境，在windows搜索功能中打开ODBC Data Sources

 1.1在系统DSN下添加数据源，选择SQL Server

1.2 名称自拟，服务器名称为SQL Server中的服务器登录名称

 

 

1.3选择使用ID和密码的SQL Server验证，登录ID为sa，输入之前设置的密码

然后一直下一步下一步就行

• 最后数据源被添加，点击确认

 

2.打开visual studio，建立新项目，源文件新建项添加C++文件

//	VS2013 环境
#define _CRT_SECURE_NO_WARNINGS
#include<iostream>
#include<string>
#include<windows.h>
#include<sql.h>
#include<sqlext.h>
#include<sqltypes.h>
#include "FileName.h"
#define sno_length 11
#define sname_length 10
#define sdepart_length 30
#define ssex_length 5
using namespace std;
SQLRETURN ret;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
typedef struct {
	SQLCHAR sno[sno_length], sname[sname_length], sdept[sdepart_length], ssex[ssex_length];
	SQLSMALLINT sage, grade;
	SQLINTEGER csno, cbsname, cbdepart, cbsage, cbsex;
}SC;
SC sc;
//字符转为宽字符
wchar_t* trstring2wchar(const  char* str)
{
	int mystringsize = (int)(strlen(str) + 1);
	WCHAR* wchart = new wchar_t[mystringsize];
	MultiByteToWideChar(CP_ACP, 0, str, -1, wchart, mystringsize);
	return wchart;

}
//宽字符转换为字符串
void wchar2strstring(string& szDst, WCHAR* wchart)
{
	wchar_t* wtext = wchart;
	DWORD dwNmu = WideCharToMultiByte(CP_OEMCP, NULL, wtext, -1, NULL, 0, NULL, FALSE);
	char* psTest;
	psTest = new char[dwNmu];
	WideCharToMultiByte(CP_OEMCP, NULL, wtext, -1, psTest, dwNmu, NULL, FALSE);
	szDst = psTest;
	delete[]psTest;
}

//字符转换为wstring
wstring CharToWchar(const char* c, size_t m_encode = CP_ACP)
{
	std::wstring str;
	int len = MultiByteToWideChar(m_encode, 0, c, strlen(c), NULL, 0);
	wchar_t* m_wchar = new wchar_t[len + 1];
	MultiByteToWideChar(m_encode, 0, c, strlen(c), m_wchar, len);
	m_wchar[len] = '\0';
	str = m_wchar;
	delete m_wchar;
	return str;
}

//错误处理
void handleResult(SQLHANDLE hHandle, SQLSMALLINT hType, RETCODE  RetCode)
{
	SQLSMALLINT iRec = 0;
	SQLINTEGER iError;
	WCHAR wszMessage[1000];
	WCHAR wszState[SQL_SQLSTATE_SIZE + 1];
	//处理无效
	if (RetCode == SQL_INVALID_HANDLE)
	{
		fwprintf(stderr, L"Invalid handle!\n");
		return;
	}
	//	对于此处版本 vc 6.0 报错
	while (SQLGetDiagRec(hType, hHandle, ++iRec, wszState, &iError, wszMessage, (SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
		(SQLSMALLINT*)NULL) == SQL_SUCCESS) {
		// Hide data truncated.. 
		if (wcsncmp(wszState, L"01004", 5))
		{
			fwprintf(stderr, L"[%5.5s]---%s---(%d)\n", wszState, wszMessage, iError);
		}
	}
}
//输出错误
void error(SQLRETURN err) {
	printf("错误相关信息输出： ");
	switch (err) {
	case	SQL_SUCCESS:puts("****SQL_SUCCESS*****"); break;
	case	SQL_SUCCESS_WITH_INFO:puts("SQL_SUCCESS_WITH_INFO"); break;
	case	SQL_ERROR:puts("SQL_ERROR"); break;
	case	SQL_INVALID_HANDLE:puts("SQL_INVALID_HANDLE"); break;
	case	SQL_NO_DATA_FOUND:puts("SQL_NO_DATA_FOUND"); break;
	case	SQL_NEED_DATA:puts("SQL_NEED_DATA"); break;
	default:puts("err");
	}
}
//宽字符转字符指针
char* wideCharToMultiByte(wchar_t* pWCStrKey)
{
	//第一次调用确认转换后单字节字符串的长度，用于开辟空间
	int pSize = WideCharToMultiByte(CP_OEMCP, 0, pWCStrKey, wcslen(pWCStrKey), NULL, 0, NULL, NULL);
	char* pCStrKey = new char[pSize + 1];
	//第二次调用将双字节字符串转换成单字节字符串
	WideCharToMultiByte(CP_OEMCP, 0, pWCStrKey, wcslen(pWCStrKey), pCStrKey, pSize, NULL, NULL);
	pCStrKey[pSize] = '\0';
	return pCStrKey;
}

//连接
void init() {
	ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);//申请环境句柄
	ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
	ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);//申请数据库连接句柄
	
	ret = SQLConnect(hdbc, (SQLWCHAR*)L"test", SQL_NTS, (SQLWCHAR*)L"sa", SQL_NTS, (SQLWCHAR*)L"123456789", SQL_NTS);


	if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) {
		printf("连接数据库失败!\n");
		return ;
	}
	else {
		printf("连接成功！\n");
	}
}
//查询
void query() {
	
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	SQLWCHAR sql1[] = L"use test";
	SQLWCHAR sql2[] = L"select * from [test].[dbo].[student]";
	ret = SQLExecDirect(hstmt, sql1, SQL_NTS);
	ret = SQLExecDirect(hstmt, sql2, SQL_NTS);
	
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
	{ 
		SQLCHAR str1[100], str2[100], str3[100];
		//SQLINTEGER len_str1, len_str2, len_str3;
		SQLLEN len_str1, len_str2, len_str3;
		
		while (SQLFetch(hstmt) != SQL_NO_DATA)
		{
			/*SQLGetData函数的功能是提取结果集中当前记录的某个字段值*/
			SQLGetData(hstmt, 1, SQL_C_CHAR, str1, sizeof(str1),&len_str1);   //获取第一列数据
			SQLGetData(hstmt, 2, SQL_C_CHAR, str2, sizeof(str2),&len_str2);
			SQLGetData(hstmt, 3, SQL_C_CHAR, str3, sizeof(str3),&len_str3);
			printf(" %s \t %s \t %s \n", str1, str2, str3);
		}
	}
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

}



//插入记录
void insert() {
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	
	SQLWCHAR str[1024] = L"insert into [test].[dbo].[student] ([studentsNum],[studentName],[gender],[birthday],[phonrNo]) values( '012345678','唐僧',1,'2000-3-16','136416255')";
	ret = SQLExecDirect(hstmt, str, SQL_NTS);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		cout << "创建成功！" << endl;
	}
	else {
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout << "插入失败！" << endl;
	}
	error(ret);
	
}

//删除记录
void delRecord() {
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	
	int num = 1;
	string sql = "delete from [test].[dbo].[student] where [id] in(27)";

	ret = SQLExecDirect(hstmt, trstring2wchar(sql.c_str()), SQL_NTS);

	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		cout << "删除成功！" << endl;
	}
	else {
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout << "删除失败！" << endl;
	}

}

int main() {
	init();
	insert();
	query();
	delRecord();
	cout << "===================" << endl;
	query();

}