txt批量mysql数据库_c++将txt数据批量导入mysql数据库的例子

包括多线程的使用，数据库的操作和打开文件对话框的使用。

#include "stdafx.h"

#include

#include

#include "resource.h"

#include "MainDlg.h"

#include //连接库：odbc32.lib odbccp32.lib

#include //数据库头文件

#include //数据库头文件

#include //OPENFILENAME 头文件

#include

#define LOGIN_TIMEOUT 30

#define MAXBUFLEN 255

///#define CHECKDBSTMTERROR(hwnd,result,hstmt)

char szFile[MAX_PATH];

BOOL ImportStatus = FALSE; //导入状态标志

SQLHENV henv = NULL;

SQLHDBC hdbc = NULL;

SQLHSTMT hstmt = NULL;

SQLRETURN result;

SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=127.0.0.1;UID=root;PWD=123456;DATABASE=Test;CharSet=gbk;";

SQLCHAR ConnStrOut[MAXBUFLEN];

void ShowDBError(HWND hwnd,SQLSMALLINT type,SQLHANDLE sqlHandle)

{

char pStatus[10], pMsg[101];

SQLSMALLINT SQLmsglen;

char error[200] = {0};

SQLINTEGER SQLerr;

long erg2 = SQLGetDiagRec(type, sqlHandle,1,

(SQLCHAR *)pStatus,&SQLerr,(SQLCHAR *)pMsg,100,&SQLmsglen);

wsprintf(error,"%s (%d)\n",pMsg,(int)SQLerr);

MessageBox(hwnd,error,TEXT("数据库执行错误"),MB_ICONERROR|MB_OK);

}

void ShowDBConnError(HWND hwnd,SQLHDBC hdbc)

{

ShowDBError(hwnd,SQL_HANDLE_DBC,hdbc);

}

void ShowDBStmtError(HWND hwnd,SQLHSTMT hstmt)

{

ShowDBError(hwnd,SQL_HANDLE_STMT,hstmt);

}

//查检是否有错误

BOOL CHECKDBSTMTERROR(HWND hwnd,SQLRETURN result,SQLHSTMT hstmt)

{

if(SQL_ERROR==result)

{

ShowDBStmtError(hwnd,hstmt);

return TRUE;

}

else

{

return FALSE;

}

}

BOOL WINAPI Main_Proc(HWND hWnd, UINT uMsg, WPARAM wParam, LPARAM lParam)

{

switch(uMsg)

{

HANDLE_MSG(hWnd, WM_INITDIALOG, Main_OnInitDialog);

HANDLE_MSG(hWnd, WM_COMMAND, Main_OnCommand);

HANDLE_MSG(hWnd,WM_CLOSE, Main_OnClose);

}

return FALSE;

}

BOOL Main_OnInitDialog(HWND hwnd, HWND hwndFocus, LPARAM lParam)

{

return TRUE;

}

//选择文件

void openfile(HWND hwnd)

{

OPENFILENAME ofn;//定义一个OPENFILENAME类型的结构体。

ZeroMemory(&ofn,sizeof(ofn));//使用结构体变量前把该结构体变量各个位清零。

ofn.lStructSize = sizeof(ofn);

ofn.lpstrFile = szFile;

ofn.lpstrFile[0] = TEXT('\0');

ofn.nMaxFile = sizeof(szFile);

ofn.lpstrFilter = TEXT("ALL\0*.*\0文本文档\0*.TXT\0");

ofn.nFilterIndex = 2;

ofn.lpstrFileTitle = NULL;

ofn.nMaxFileTitle = 0;

ofn.lpstrInitialDir = NULL;

ofn.Flags = OFN_EXPLORER |OFN_PATHMUSTEXIST | OFN_FILEMUSTEXIST;

if (GetOpenFileName(&ofn))

{

SetDlgItemText(hwnd,IDC_FILEINFO,szFile);

}

}

BOOL inport_begin(HWND hwnd)

{

//分配环境句柄

result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

//设置管理环境属性

result = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

//分配连接句柄

result = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

//设置连接属性

result = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)LOGIN_TIMEOUT, 0);

//连接数据库

result = SQLDriverConnect(hdbc,NULL,ConnStrIn,SQL_NTS,ConnStrOut,MAXBUFLEN,(SQLSMALLINT *)0,SQL_DRIVER_NOPROMPT);

if(SQL_ERROR==result)

{

ShowDBConnError(hwnd,hdbc);

return FALSE;

}

ImportStatus = TRUE;

return TRUE;

}

DWORD WINAPI ThreadFunc(LPVOID lpParam) //多线程任务

{

HWND hwnd = (HWND)lpParam;

char str[50];

char fname[11];

char fage[11];

int age;

int i,j,k;

int IntoNum = 0;

//TCHAR temp[256];

TCHAR info[256];

FILE * Input;

for (i=0;szFile[i]!='\0';++i) //处理绝对路径

{

if (szFile[i]=='\\')

{

szFile[i]='/';

}

}

if(strlen(szFile)==0){SetDlgItemText(hwnd,IDC_STATUS,"请选择文件！"); return 0;}

Input = fopen(szFile,"r"); //打开文本文档

BOOL hasError=FALSE;

/*创建新的数据库句柄，执行SQL语句*/

result = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

result = SQLPrepare(hstmt,(SQLCHAR *)"insert into T_Person(FName,FAge) values(?,?)",SQL_NTS);//两个占位符

SQLINTEGER iSQL_NTS=SQL_NTS;

while(!feof(Input)&&ImportStatus) //判断文件流是否到末尾，并判断导入标志的状态

{

ZeroMemory(fname,sizeof(fname)/sizeof(char));

ZeroMemory(fage,sizeof(fage)/sizeof(char));

ZeroMemory(str,sizeof(str)/sizeof(char));

//ZeroMemory(temp,sizeof(temp)/sizeof(TCHAR));

ZeroMemory(info,sizeof(info)/sizeof(TCHAR));

fgets(str,50,Input);

for (i=0,j=0,k=0;str[k]!='\0';++k) //将文件流中的姓名和年龄数据进行处理，存储到相应的变量中

{

if (str[k]>='A'&&str[k]<='Z' || str[k]>='a'&&str[k]<='z')

{

fname[i] = str[k];

++i;

}

else

{

if (str[k]>='0'&&str[k]<='9')

{

fage[j] = str[k];

++j;

}

}

}

fname[i] = '\0';

fage[j] = '\0';

age = atoi(fage);

if(CHECKDBSTMTERROR(hwnd,result,hstmt)==TRUE) {hasError=TRUE;break;}

//给两个占位符赋值

SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,strlen(fname),0,fname,0,&iSQL_NTS);

SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER,sizeof(int),0,&age,0,&iSQL_NTS);

result =SQLExecute(hstmt);

if(CHECKDBSTMTERROR(hwnd,result,hstmt)==TRUE) {hasError=TRUE;break;}

Sleep(30);

wsprintf(info,TEXT("正在导入第%d条数据"),++IntoNum);

SetDlgItemText(hwnd,IDC_STATUS,info);

}

fclose(Input);

SQLFreeStmt(hstmt,SQL_CLOSE);

SQLDisconnect(hdbc); //如果到达末尾，跳出，并关闭数据库

SQLFreeHandle(SQL_HANDLE_DBC,hdbc);

SQLFreeHandle(SQL_HANDLE_ENV,henv);

if(hasError==TRUE)

{

SetDlgItemText(hwnd,IDC_STATUS,TEXT("数据库操作出错，取消导入！"));

}

else if (ImportStatus)

{

SetDlgItemText(hwnd,IDC_STATUS,TEXT("恭喜您，所有的数据都已经导入成功！"));

}

else

{

SetDlgItemText(hwnd,IDC_STATUS,TEXT("您已经取消了数据库的导入！"));

}

return 0;

}

void Main_OnCommand(HWND hwnd, int id, HWND hwndCtl, UINT codeNotify)

{

switch(id)

{

case IDC_OK:

{

if(inport_begin(hwnd))

{

CreateThread(NULL,0,ThreadFunc,hwnd,0,0); //开始使用多线程调用函数

}

else

{

SetDlgItemText(hwnd,IDC_STATUS,TEXT("数据库连接出错，取消导入！"));

}

break;

}

case IDC_CANCEL:

{

ImportStatus = FALSE;

break;

}

case IDC_FILEOPEN:

openfile(hwnd);

break;

default:

break;

}

}

void Main_OnClose(HWND hwnd)

{

EndDialog(hwnd, 0);

}