数据库操作1

DBOperation.h

#pragma once

#include "db\db_access.h"

class DBOperation
{
public:
static BOOL m_dbEnablelog ;
public:
static DBOperation* CreateCon();
int Connectdb();
void closedb();
BOOL ping();
int FetchRowCount(char *queryString);
int InsertRecord(char *queryString);
int DeleteRecord(char *queryString);
int ModifiRecord(char *queryString);
int GetQueryResult(char *queryString,MYSQL_RES **result,unsigned int *rowCount);
int StmtExecution(MYSQL_BIND *binds,int bindLength,const char *sqlString);
void setDbInfo(char*,char*,char*,char*,int,int flag=0);
static void releaseDB();
public:
~DBOperation(void);

private:
DBOperation(void);
static DBOperation* db;
MYSQL *conn;
//DI_SQLInfo info;
MYSQL_RES *result;
DI_SQLInfo info;
static CRITICAL_SECTION dbcritic_section;

};

DBOperation.cpp

#include "StdAfx.h"
#include "DBOperation.h"


CRITICAL_SECTION DBOperation::dbcritic_section ;
BOOL DBOperation::m_dbEnablelog;
DBOperation* DBOperation::db = NULL;

DBOperation::DBOperation(void)
{
m_dbEnablelog = FALSE;
result = NULL;
conn=NULL;
InitializeCriticalSection(&dbcritic_section);
}


DBOperation* DBOperation::CreateCon(){
if (db == NULL){
db = new DBOperation();
return db;
}
return db;
}


DBOperation::~DBOperation(void)
{
closedb();
DeleteCriticalSection(&dbcritic_section);
}


int DBOperation::Connectdb(){
if(NULL == conn)
{
EnterCriticalSection(&dbcritic_section);
conn = DI_Init(&info);
if (conn == NULL)
{
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}


m_dbEnablelog = TRUE;
LeaveCriticalSection(&dbcritic_section); 
}
return DI_OK;
}

void DBOperation::closedb(){
if (conn != NULL)
DI_CloseConnection(conn);
conn = NULL;
}

int DBOperation::FetchRowCount(char *queryString)
{
unsigned int rowCount = 0;
if (conn == NULL) return DI_FAILED;
EnterCriticalSection(&dbcritic_section);
if (DI_QueryDB(conn,queryString,&result) == DI_FAILED){
//AfxMessageBox(_T("error at addRecordToDB()"));
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}
if (DI_FAILED == DI_NumOfRows(result,&rowCount))
{
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}

DI_FreeResult(result);
LeaveCriticalSection(&dbcritic_section); 
return rowCount;
}

int DBOperation::InsertRecord(char *queryString){
if (conn == NULL) return DI_FAILED;
    EnterCriticalSection(&dbcritic_section);
    if (DI_AddRecordToDB(conn,queryString) == DI_FAILED){
        //AfxMessageBox(_T("error at addRecordToDB()"));
LeaveCriticalSection(&dbcritic_section); 
        return DI_FAILED;
    }
    LeaveCriticalSection(&dbcritic_section); 
    return DI_OK;
}

int DBOperation::DeleteRecord(char *queryString){
if (conn == NULL) return DI_FAILED;
EnterCriticalSection(&dbcritic_section);
if (DI_DelRecordToDB(conn,queryString) == DI_FAILED){
//AfxMessageBox(_T("error at addRecordToDB()"));
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}
LeaveCriticalSection(&dbcritic_section); 
return DI_OK;
}

int DBOperation::ModifiRecord(char *queryString)
{
if (conn == NULL) return DI_FAILED;
EnterCriticalSection(&dbcritic_section);
if (DI_ModifyRecordToDB(conn,queryString) == DI_FAILED){
//AfxMessageBox(_T("error at ModifiRecord()"));
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}
LeaveCriticalSection(&dbcritic_section); 
return DI_OK;
}

int DBOperation::GetQueryResult(char *queryString,MYSQL_RES **result,unsigned int *rowCount)
{
if (NULL == conn) return DI_FAILED;
EnterCriticalSection(&dbcritic_section);
if (DI_QueryResult(conn,queryString,result,rowCount) == DI_FAILED){
//AfxMessageBox(_T("error at GetResult()"));
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}
LeaveCriticalSection(&dbcritic_section); 
return DI_OK;
}

int DBOperation::StmtExecution(MYSQL_BIND *binds,int bindLength,const char *queryString)
{
if (NULL == conn) return DI_FAILED;
EnterCriticalSection(&dbcritic_section);
if (DI_StmtExecution(conn,binds,bindLength,queryString) == DI_FAILED){
//AfxMessageBox(_T("error at addRecordToDB()"));
LeaveCriticalSection(&dbcritic_section); 
return DI_FAILED;
}
LeaveCriticalSection(&dbcritic_section); 
return DI_OK;
}

void DBOperation::setDbInfo(char *host,char *user,char *pwd,char *db,int port,int flag)
{
    strcpy_s(info.hostName,sizeof(info.hostName),host);
    strcpy_s(info.userName,sizeof(info.userName),user);
    strcpy_s(info.passwd,sizeof(info.passwd),pwd);
    strcpy_s(info.dbName,sizeof(info.dbName),db);
    info.port = port;
    info.flags = flag;
}

void DBOperation::releaseDB()
{
if (db != NULL){
delete db;
db = NULL;
}
}

/*******************************************************************************
* filename: db_access.h
********************************************************************************
*/
#ifndef _DB_ACCESS_H_
#define _DB_ACCESS_H_

#ifdef __cplusplus
extern "C" {
#endif

#include "mysql.h"

#define   DI_SQL_NAME_LEN   25
#define   DI_OK       1     
#define   DI_FAILED   0

DWORD WINAPI pingProc(LPVOID pvParam);
WORD CheckConnection(MYSQL *connID);

typedef struct DI_SQLInfo
{
   char hostName[DI_SQL_NAME_LEN];  /* server host (default=localhost) */
   char userName[DI_SQL_NAME_LEN];  /* username (default=login name) */
   char passwd[DI_SQL_NAME_LEN];    /* password (default=none) */
   char sockName[DI_SQL_NAME_LEN];  /* socket name (use built-in value) */
     char dbName[DI_SQL_NAME_LEN];    /* database name (default=none) */
   int port;                 /* port number (use built-in value) */
   int flags;         /* connection flags (none) */
}DI_SQLInfo;

//Init and connect to the database
MYSQL* DI_Init(DI_SQLInfo *userInfo);

//Do query to the database
int DI_QueryDB(MYSQL *connID, char *queryString, MYSQL_RES **result);

//Add record to the database
int DI_AddRecordToDB(MYSQL *connID, char *queryString);

//Modify record of the database
int DI_ModifyRecordToDB(MYSQL *connID, char *queryString);

//delete record of the database
int DI_DelRecordToDB(MYSQL *connID, char *queryString);

//fetch fields of the database
int DI_FetchFields(MYSQL_RES *result, MYSQL_FIELD **fields);

//fetch rows of the database
int DI_FetchRow(MYSQL_RES *result, MYSQL_ROW *row); 

//get the number of fields of the database
int DI_NumOfFields(MYSQL_RES *result, unsigned int *num_fields);

//get the number of rows of the database
int DI_NumOfRows(MYSQL_RES *result, unsigned int *num_rows);

//combine DI_QueryDB() and DI_NumOfRows() 
int DI_QueryResult(MYSQL *connID, char *queryString, MYSQL_RES **result,unsigned int *num_rows);

//free the result
int DI_FreeResult(MYSQL_RES *result);

//close the connect to the database
int DI_CloseConnection(MYSQL *connID);

// call the prepare statement
int DI_StmtExecution(MYSQL *connID,MYSQL_BIND *binds,int bindLength,const char *sqlString);
#ifdef __cplusplus
}
#endif

#endif

/*******************************************************************************
* filename: db_access.c

********************************************************************************
*/
#include "stdafx.h"
#include <stdio.h>
#include "db_access.h"
#include "../wchar_conv.h"
#include <afxwin.h>


int connectError = FALSE;
/*******************************************************************************
* Function: DI_Init
* Input: userInfo: the pointer to data structure DI_SQLInfo;
* 
* Output: none
* Return: The pointer to MYSQL - success
          NULL - fails 
* Desc: Init and connect to the database       
*******************************************************************************/
MYSQL* DI_Init(DI_SQLInfo *userInfo)
{
connectError = FALSE;// 定义连接是否正常 默认没有问题
    MYSQL *ret;
my_bool val = 1;
    //judge whether the input parameters are valid
    if((userInfo == NULL))
    {
        printf("invalid input\n");
        return NULL;
    }
 
    //init the connection handle 
    ret = mysql_init(NULL); 
      
    if(ret == NULL) 
    {
        //AfxMessageBox(_T("Init database error!"));
        return NULL;                  
    }    
    //connect to the database
int RetNo;
RetNo = mysql_options(ret, MYSQL_OPT_RECONNECT, (my_bool *)&val); 
if(RetNo)
{
AfxMessageBox(_T("设置重连时间错误。"));
}
unsigned long timeout = 3;
RetNo = mysql_options(ret, MYSQL_OPT_WRITE_TIMEOUT, &timeout);
if(RetNo)
{
AfxMessageBox(_T("设置超时时间错误。1"));
}
RetNo = mysql_options(ret, MYSQL_OPT_READ_TIMEOUT, &timeout);
if(RetNo)
{
AfxMessageBox(_T("设置超时时间错误。2"));
}
    if (mysql_real_connect(ret, userInfo->hostName, 
                userInfo->userName, userInfo->passwd, 
                userInfo->dbName, userInfo->port, NULL,
                userInfo->flags) == NULL)
    {
       //AfxMessageBox(_T("Connect database error!"));
       return NULL; 
    }


     mysql_set_character_set(ret,"gbk"); 
     return ret; 
}    


/*******************************************************************************
* Function: DI_FreeResult
* Input: result: pointer to MYSQL_RES;
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: free the result         
*******************************************************************************/
int DI_FreeResult(MYSQL_RES *result)
{
    if(result!= NULL)
        mysql_free_result(result);
        
    return (DI_OK);
}


/*******************************************************************************
* Function: DI_CloseConnection
* Input: connID: pointer to MYSQL;
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: close the connect to the database
*******************************************************************************/
int DI_CloseConnection(MYSQL *connID)
{
    if(connID == NULL)
    {
        printf("invalid input\n");
        return(DI_FAILED);    
    }
    
    mysql_close(connID);
    
    return(DI_OK);
}


/*******************************************************************************
* Function: DI_QueryDB
* Input: connID: pointer to MYSQL;
*        result: point to pointer to MYSQL_RES;
*        queryString: pointer to the query string
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: Do query to the database         
*******************************************************************************/
int DI_QueryDB(MYSQL *connID, char *queryString, MYSQL_RES **result)
{
if (DI_FAILED == CheckConnection(connID))
{
return DI_FAILED;
}


//judge whether the input parameters are valid
    if(connID == NULL || queryString == NULL)
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }        
     
     if(mysql_query(connID, queryString))
     {
        printf("Failed to query database: Error: %s\n",
                     mysql_error(connID));
                 
        return(DI_FAILED);
     }
     
     //store the result 
     if ((*result = mysql_store_result(connID)) == NULL)
     {
        printf("Failed to query database: Error: %s\n",
                   mysql_error(connID));
              
        return(DI_FAILED);
     }
         
     return(DI_OK);         
}  


/*******************************************************************************
* Function: DI_AddRecordToDB
* Input: connID: pointer to MYSQL;
*        queryString: pointer to the query string
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: Add record to the database      
*******************************************************************************/
int DI_AddRecordToDB(MYSQL *connID, char *queryString)
{
if (DI_FAILED == CheckConnection(connID))
{
return DI_FAILED;
}


    //judge whether the input parameters are valid
    if((connID == NULL) || (queryString == NULL))
    {
        printf("invalid input\n");
        return(DI_FAILED);    
    }
    
// int result = mysql_ping(connID);
    if(mysql_query(connID, queryString))
    {
        const char *error = mysql_error(connID);
        CString errorMsg;
        CAnsiToWide::convert(errorMsg,error);
        return(DI_FAILED);
    }
 
    return(DI_OK);  
}


/*******************************************************************************
* Function: DI_ModifyRecordToDB
* Input: connID: pointer to MYSQL;
*        queryString: pointer to the query string
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: Modify record of the database    
*******************************************************************************/
int DI_ModifyRecordToDB(MYSQL *connID, char *queryString)
{
if (DI_FAILED == CheckConnection(connID))
{
return DI_FAILED;
}


    //judge whether the input parameters are valid
    if((connID == NULL)||(queryString == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }
       
// mysql_ping(connID);mysql_ping function called before
    if(mysql_query(connID, queryString))
    {
       printf("Failed to modify the database: Error: %s\n",mysql_error(connID));
       return(DI_FAILED);
    }
          
    return(DI_OK);  
}


/*******************************************************************************
* Function: DI_DelRecordToDB
* Input: connID: pointer to MYSQL;
*        queryString: pointer to the query string
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: delete record of the database      
*******************************************************************************/
int DI_DelRecordToDB(MYSQL *connID, char *queryString)
{
if (DI_FAILED == CheckConnection(connID))
{
return DI_FAILED;
}


    //judge whether the input parameters are valid
    if((connID == NULL)||(queryString == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }


    if(mysql_query(connID, queryString))
    {
        printf("Failed to delete records of database: Error: %s\n",
                    mysql_error(connID));
        return(DI_FAILED);
    }
          
    return(DI_OK);  
}


/*******************************************************************************
* Function: DI_FetchFields
* Input: result: pointer to MYSQL_RES;
*        fields: pointer to point to MYSQL_FIELD
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: fetch fields of the database             
*******************************************************************************/
int DI_FetchFields(MYSQL_RES *result, MYSQL_FIELD **fields)
{
    if((result == NULL)||(fields == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }


    if((*fields = mysql_fetch_fields(result)) == NULL)
printf("No columns are left!\n");
       
    return(DI_OK);
}


/*******************************************************************************
* Function: DI_FetchRow
* Input: result: pointer to MYSQL_RES;
*        row: pointer to point to MYSQL_ROW
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: fetch rows of the database      
*******************************************************************************/
int DI_FetchRow(MYSQL_RES *result, MYSQL_ROW *row)  
{
    if((result == NULL)||(row == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }
        
    *row = mysql_fetch_row(result);
    if (*row == NULL ) return (DI_FAILED); 
    return(DI_OK);
}


/*******************************************************************************
* Function: DI_NumOfFields
* Input: result: pointer to MYSQL_RES;
*        num_fields: pointer to the number of fields
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: get the number of fields of the database         
*******************************************************************************/
int DI_NumOfFields(MYSQL_RES *result,unsigned int *num_fields)
{
  //unsigned int mysql_num_fields(MYSQL_RES *result)
    if((result == NULL)||(num_fields == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }
        
    *num_fields = mysql_num_fields(result);
    return(DI_OK);
}


/*******************************************************************************
* Function: DI_NumOfRows
* Input: result: pointer to MYSQL_RES;
*        num_rows: pointer to the number of rows
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: get the number of rows of the database  
*******************************************************************************/
int DI_NumOfRows(MYSQL_RES *result, unsigned int *num_rows)
{
    if((result == NULL)||(num_rows == NULL))
    {
       printf("invalid input\n");
       return(DI_FAILED);    
    }
        
    *num_rows = (unsigned int)mysql_num_rows(result);
     return(DI_OK);
}
/*******************************************************************************
* conbine DI_QueryDB() with DI_NumOfRows()
*
* gao 
*******************************************************************************/
int DI_QueryResult(MYSQL *connID, char *queryString, MYSQL_RES **result,unsigned int *num_rows)
{
if (DI_FAILED == CheckConnection(connID))
{
return DI_FAILED;
}


if((connID == NULL)||(queryString == NULL)||(result == NULL) || (num_rows == NULL))
{
printf("invalid input\n");
return(DI_FAILED);    
}


if(mysql_query(connID, queryString))
{
printf("Failed to query database: Error: %s\n",
mysql_error(connID));


return(DI_FAILED);
}
//store the result 
if ((*result = mysql_store_result(connID)) == NULL)
{
printf("Failed to query database: Error: %s\n",
mysql_error(connID));


return(DI_FAILED);
}
//store the rows
*num_rows = (unsigned int)mysql_num_rows(*result);


return(DI_OK);
}


/*******************************************************************************
* Function: DI_StmtExecution
* Input: connID: pointer to MYSQL_RES;
binds: pointer to the *MYSQL_BIND
bindLength: the size of binds array 
sqlString: sql string to execute
* Output: none
* Return: DI_FAILED or DI_OK 
* Desc: free the result  
*******************************************************************************/
int DI_StmtExecution(MYSQL *connID,MYSQL_BIND *binds,int bindLength,const char *sqlString)
{
MYSQL_STMT* stmt = mysql_stmt_init(connID);
if (mysql_stmt_prepare(stmt, sqlString, strlen(sqlString)))
{
printf("Mysql-Stmt-Error %d in function %s: %s\n",mysql_stmt_errno(stmt),"mysql_stmt_prepare",mysql_stmt_error(stmt));
return(DI_FAILED);
}
/* Get the parameter count from the statement */
int param_count= mysql_stmt_param_count(stmt);


if (mysql_stmt_bind_param(stmt, binds) != 0)
{
printf("Mysql-Stmt-Error %d in function %s: %s\n",mysql_stmt_errno(stmt),"mysql_stmt_bind_param",mysql_stmt_error(stmt));
return(DI_FAILED);
}
if (mysql_stmt_execute(stmt) != 0)
{
printf("Mysql-Stmt-Error %d in function %s: %s\n",mysql_stmt_errno(stmt),"mysql_stmt_execute",mysql_stmt_error(stmt));
return(DI_FAILED);
}
mysql_stmt_close(stmt);
return(DI_OK);
}


WORD CheckConnection(MYSQL *connID)
{
if (connectError)
return DI_FAILED; 


connectError = TRUE;
if (mysql_ping(connID))
{
DWORD threadID = 0;
CreateThread(NULL,0,(LPTHREAD_START_ROUTINE)pingProc,(LPVOID)connID,0,&threadID);
return DI_FAILED;
}
connectError = FALSE;
return DI_OK;
}


DWORD WINAPI pingProc(LPVOID pvParam)
{
MYSQL *mysql = (MYSQL *)pvParam;
while (connectError)
{
if (mysql_ping(mysql))
{
Sleep(8000);
}else
break;
}
connectError = FALSE;
return 0;
}