sqlite3工作平台dlg.cpp

Sqlite3工作平台,使用sqlite引擎

void CSqlite3Dlg::OnSelchangeTableList() 
{
	// TODO: Add your control notification handler code here
	int currentrow=0;
	sqlite3_stmt *vm;
	char selectsql[1024]="";
	//char * errmsg;
	int rc=0;
	int Autoid=0;	
	int columntype=-1;
	
	int colIntData=0;
	double colFdata=0.00;
	CString colSdata="";
	UpdateData(TRUE);
	CString maxrow=m_MaxRow;
	
    sscanf((const char *)maxrow,"%d",&showMaxRow);
	currentrow=m_TableList.GetCurSel();
	m_TableList.GetText(currentrow,m_tablename);
	
	if(M_db==NULL)
	{
		MessageBox("数据库没有打开！",NULL,MB_OK);
		return;
	}
	sprintf(selectsql,"SELECT * FROM %s where 1",m_tablename);
	rc=sqlite3_prepare(M_db,selectsql,sizeof(selectsql),&vm,0);
	columnNum=sqlite3_column_count(vm);
	
	if(columnNum<=0)
	{
		MessageBox("该表为空数据表",NULL,MB_OK);
		return;
	}
	memset(Fieldsql,0,sizeof(Fieldsql));
	memset(setsql,0,sizeof(setsql));
	memset(valuesql,0,sizeof(valuesql));
	m_cListInfo.DeleteAllItems();

	//把列表中的数据全部删除
	int nColumnCount=m_cListInfo.GetHeaderCtrl()->GetItemCount();
	for (int i=0;i < nColumnCount;i++)
	{
		m_cListInfo.DeleteColumn(0);
	}
	
	m_cListInfo.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES |LVS_EX_SUBITEMIMAGES|LVS_EX_HEADERDRAGDROP);	
	
	//设置第0列
	m_cListInfo.InsertColumn(0,"AutoId");	
	m_cListInfo.SetColumnWidth(0,50);
	//设置第0行

	for( i=0;i<columnNum;i++)
	{
		
		TableName[i]=(char *)sqlite3_column_name(vm,i);
		strcat(Fieldsql,TableName[i]);
		strcat(setsql,TableName[i]);
		strcat(setsql,"=");
		if(i!=columnNum-1)
		{
			strcat(setsql,",");
			strcat(Fieldsql,",");
			strcat(valuesql,",");
		}
		m_cListInfo.InsertColumn(i+1,TableName[i]);	
		m_cListInfo.SetColumnWidth(i+1,colwidth[i+1]);
		//m_cListInfo.SetColumnWidth(i+1,LVSCW_AUTOSIZE_USEHEADER);
	}
	
//	m_DBResult.SetAllowAddNew(TRUE);
	while((rc=sqlite3_step(vm))==SQLITE_ROW)
	{
		++Autoid;
		CString str="";
		str.Format("%d", Autoid);
		if(Autoid<=showMaxRow)
		{
			m_cListInfo.InsertItem(
							LVIF_TEXT|LVIF_STATE, Autoid, str, 
							(Autoid%2)==0 ? LVIS_SELECTED : 0, LVIS_SELECTED,
							0, 0);
						
			for (int j=0; j<columnNum; j++)
			{
				
				m_cListInfo.SetColumnWidth(j+1,colwidth[j+1]);
				columntype=sqlite3_column_type(vm,j);
				switch(columntype) 
				{
				case SQLITE_INTEGER:
					colIntData=sqlite3_column_int(vm,j);
					if(j==0)
					{
						str.Format("%x",colIntData);
					}
					else
					{
						str.Format("%d",colIntData);
					}
					
					break;
				case SQLITE_FLOAT:
					colFdata=sqlite3_column_double(vm,j);
					str.Format("%f",colFdata);
					break;
				case SQLITE_TEXT:
					colSdata=sqlite3_column_text(vm,j);
					str.Format("%s",colSdata);
					break;
				default:
					break;
					//m_cListInfo.SetColumnWidth(j+1,str.GetAllocLength());
				}
				m_cListInfo.SetItemText(Autoid-1, j+1,(LPSTR)(LPCTSTR)str);
			}
		}
		
	}
	if(Autoid>showMaxRow)
	m_cListInfo.InsertItem(
			LVIF_TEXT|LVIF_STATE, showMaxRow+1, "more...", 
			(Autoid%2)==0 ? LVIS_SELECTED : 0, LVIS_SELECTED,
			0, 0);
			

	UpdateData(FALSE);
	rc=sqlite3_finalize(vm);
}

void CSqlite3Dlg::OnExecute() 
{
	// TODO: Add your control notification handler code here
	//fp=fopen("C:\\data.txt","w");
	CEdit *  CheckCondition=(CEdit *)GetDlgItem(IDC_CHECK);
	CEdit *  SQLMessage =(CEdit *) GetDlgItem(IDC_MESSAGE);
	sqlite3_stmt *vm;
	char delsql[1024]="";
	CString sql="";
	int rc=0;
	char errmsg [256]="";
	char *errmsg1;
	int changecount=0;
	char sqlResultMessage[1024]="";
	char gettablenamesql[1024]="";
	int nColumnCount=0;
	int i;
	int Autoid=0;	
	int columntype=-1;
	
	int colIntData=0;
	double colFdata=0.00;
	CString colSdata="";
	//FILE * fp;
	UpdateData(TRUE);
	CString maxrow=m_MaxRow;
	
    sscanf((const char *)maxrow,"%d",&showMaxRow);
	UpdateData(TRUE);
    m_Check.GetWindowText(sql);
	m_cListInfo.DeleteAllItems();
	
	//把列表中的数据全部删除
	nColumnCount=m_cListInfo.GetHeaderCtrl()->GetItemCount();
	for (i=0;i < nColumnCount;i++)
	{
		m_cListInfo.DeleteColumn(0);
	}
	switch(ConditionIndex) 
	{
		
	case 0:
		//fp=fopen("D:\\RecordData.txt","a+");
		rc=sqlite3_prepare(M_db,sql,sizeof(sql),&vm,0);
		columnNum=sqlite3_column_count(vm);
		if(rc != SQLITE_OK)
		{
			sprintf(errmsg,sqlite3_errmsg(M_db));
			MessageBox(errmsg,NULL,MB_OK);
			return;
		}
				
		m_cListInfo.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES |LVS_EX_SUBITEMIMAGES|LVS_EX_HEADERDRAGDROP);	
		
		//设置第0列
		m_cListInfo.InsertColumn(0,"AutoId");	
		m_cListInfo.SetColumnWidth(0,50);
		//设置第0行
		
		for( i=0;i<columnNum;i++)
		{
			TableName[i]=(char *)sqlite3_column_name(vm,i);
			m_cListInfo.InsertColumn(i+1,TableName[i]);	
			m_cListInfo.SetColumnWidth(i+1,colwidth[i+1]);//LVSCW_AUTOSIZE 
			//m_cListInfo.SetColumnWidth(i+1,LVSCW_AUTOSIZE);
		}
		while ((rc=sqlite3_step(vm))==SQLITE_ROW)
		{
			++Autoid;
			CString str="";
			//char autoid[10]="";
			//sprintf(autoid,"(%d).  ",Autoid);
			str.Format("%d", Autoid);
			//fwrite(autoid,sizeof(autoid),1,fp);
			if(Autoid<=showMaxRow)
			{
				m_cListInfo.InsertItem(
								LVIF_TEXT|LVIF_STATE, Autoid, str, 
								(Autoid%2)==0 ? LVIS_SELECTED : 0, LVIS_SELECTED,
								0, 0);
			

				for (int j=0; j<columnNum; j++)
				{
					
					columntype=sqlite3_column_type(vm,j);
					switch(columntype) 
					{
					case SQLITE_INTEGER:
						colIntData=sqlite3_column_int(vm,j);
						if(j==0)
						{
							str.Format("%x",colIntData);
						}
						else
						{
							str.Format("%d",colIntData);
						}
						
						break;
					case SQLITE_FLOAT:
						colFdata=sqlite3_column_double(vm,j);
						str.Format("%f",colFdata);
						break;
					case SQLITE_TEXT:
					
						colSdata=sqlite3_column_text(vm,j);
						str.Format("%s",colSdata);
						//rite(str,strlen((const char *)str),1,fp);
						break;
					default:
						break;
					}
					
					//从插入时，Autoid从0开始
					m_cListInfo.SetItemText(Autoid-1, j+1,(LPSTR)(LPCTSTR)str);
					
					
					//fwrite(str,strlen((const char *)str),1,fp);
					//fwrite(" ",1,1,fp);
				}
				//fwrite("\n",1,1,fp);
			}
		}
		if(Autoid>showMaxRow)//如果查询出来的记录条数大于指定的显示条数
			m_cListInfo.InsertItem(
				LVIF_TEXT|LVIF_STATE, showMaxRow+1, "more...", 
				(Autoid%2)==0 ? LVIS_SELECTED : 0, LVIS_SELECTED,
				0, 0);

		//fclose(fp);
		UpdateData(FALSE);
		rc=sqlite3_finalize(vm);
		break;
	case 1:
		rc=sqlite3_exec(M_db,sql,0,0, &errmsg1);
		changecount=sqlite3_changes(M_db);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		sprintf(sqlResultMessage,"%d Records have been Deleted Form %s!\n",changecount,m_tablename);
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		break;
	case 2:
		rc=sqlite3_exec(M_db,sql,0,0,&errmsg1);
		changecount=sqlite3_changes(M_db);
		sqlite3_exec(M_db,"vacuum",0,0,0);
		sqlite3_exec(M_db,"COMMIT",0,0,0);
		sqlite3_exec(M_db,"BEGIN",0,0,0);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		sprintf(sqlResultMessage,"%d Records Inserted Into %s Success!\n",changecount,m_tablename);
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		break;
	case 3:
		rc=sqlite3_exec(M_db,sql,0,0,&errmsg1);
		changecount=sqlite3_changes(M_db);
		sqlite3_exec(M_db,"vacuum",0,0,0);
		sqlite3_exec(M_db,"COMMIT",0,0,0);
		sqlite3_exec(M_db,"BEGIN",0,0,0);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		sprintf(sqlResultMessage,"%d Records have been Update In %s Success!\n",changecount,m_tablename);
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		break;
	case 4:
        sprintf(delsql,"DELETE FROM %s WHERE 1",m_tablename);
		rc=sqlite3_exec(M_db,delsql,0,0,&errmsg1);
		sqlite3_exec(M_db,"vacuum",0,0,0);
		sqlite3_exec(M_db,"COMMIT",0,0,0);
		sqlite3_exec(M_db,"BEGIN",0,0,0);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		rc=sqlite3_exec(M_db,sql,0,0,&errmsg1);
		sprintf(sqlResultMessage,"%s Table Droped Success!\n",m_tablename);
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		break;
	case 5:
		rc=sqlite3_exec(M_db,sql,0,0,&errmsg1);
		sqlite3_exec(M_db,"vacuum",0,0,0);
		sqlite3_exec(M_db,"COMMIT",0,0,0);
		sqlite3_exec(M_db,"BEGIN",0,0,0);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		sprintf(sqlResultMessage,"Create Table Success!\n");
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		break;
	case 6:
		sqlite3_close(M_db);
		if (sqlite3_open(m_filepath,&M_db)!=SQLITE_OK)
		{
			printf("Failed to open database: %s\n",m_filepath);
			return ;
		}
		
		
		rc=sqlite3_exec(M_db,sql,0,0,&errmsg1);
		changecount=sqlite3_changes(M_db);
		if(rc!=SQLITE_OK)
		{
			MessageBox(errmsg1,NULL,MB_OK);
			return;
		}
		sprintf(sqlResultMessage,"%d rows effected!\n",changecount);
		m_Message=sqlResultMessage;
		UpdateData(FALSE);
		sprintf(sqlResultMessage,"%s","");
		//sqlite3_close(M_db);
		break;
	default:
		break;
	}
	m_TableList.ResetContent();
	sprintf(gettablenamesql,"SELECT name FROM sqlite_master Where type=='table' ");
    rc = sqlite3_prepare(M_db, gettablenamesql, sizeof(gettablenamesql), &vm, 0);
	if(rc != SQLITE_OK)
	{
		return;
	}
	while ((rc=sqlite3_step(vm))==SQLITE_ROW)
	{
		m_TableList.AddString((CString)sqlite3_column_text(vm,0));
		
	}
	rc = sqlite3_finalize(vm);
	
	//fclose(fp);
}

void CSqlite3Dlg::OnKillfocusCheck() 
{
	// TODO: Add your control notification handler code here
}

//DEL void CSqlite3Dlg::OnClickListInfo(NMHDR* pNMHDR, LRESULT* pResult) 
//DEL {
//DEL 	// TODO: Add your control notification handler code here
//DEL 	*pResult = 0;
//DEL }