textdlg.cpp

evc+中文模拟器操作sqlite数据库

						fieldname += " ";
					}
					iCount++;
					ptrTable->MoveNext();
				}

				if(!ptrTable->BOF)
					ptrTable->MoveFirst();
				
				//判断字段是否相同 相同则按照表中数据同步---------------------------------------------------
				if(fieldname == strMiddleName)
				{
					//获得sql server字段信息
					ptrTableInfo = ado.ExecuteSQLSentence("SELECT * FROM " + tempName);
					if(!ptrTableInfo->BOF)
						ptrTableInfo->MoveFirst();
					
					fieldname = "";
					
					while(!ptrTableInfo->adoEOF)
					{
						sql = "";
						try
						{
							var = ptrTableInfo->GetCollect("ID");
							if(var.vt!=VT_NULL)
							{
								fieldname = "";
								fieldname = (LPCSTR)_bstr_t(var);

								//要插入ID和sqlite里当前表的ID比较
								for(int k = TempColumn; k < TempRow*TempColumn+TempColumn; k = k + TempColumn)
								{
									if(fieldname == fieldResult[k])
									{
										sql = "";
										//拼接字符串
										_variant_t tempvar;
										CString Tempfieldname;
										sql = "update " + tempName + " set ";

										if(!ptrTable->BOF)
										ptrTable->MoveFirst();
										long Fieldsum = ptrTableInfo->Fields->Count;
										for(long l = 0; l < Fieldsum; l++)
										{
											tempvar = ptrTable->GetCollect("name");
											if(tempvar.vt!=VT_NULL)
											{
												Tempfieldname = "";
												Tempfieldname = (LPCSTR)_bstr_t(tempvar);
												sql += Tempfieldname;
												sql += "='";
												ptrTable->MoveNext();
											}

											var = ptrTableInfo->GetCollect(tempvar);
											if (var.vt!=VT_NULL)
											{
												Tempfieldname = (LPCSTR)_bstr_t(var);
												Tempfieldname.TrimLeft();
												Tempfieldname.TrimRight();
												sql += Tempfieldname;
												sql += "',";
											}
											else
											{
												sql += "',";
											}

										}//for(long l = 0;...end

										sql = sql.Left(strlen(sql)-1);
										sql += "where id = '";
										sql += fieldname;
										sql += "'";
										CString fileName="ttttt";
										WriteLog(fileName, sql.GetBuffer(0), sql.GetLength());

										char* zErrMsg = 0;//执行更新语句
										CString temp = sql;
										char* Tempsql = NULL;
										Tempsql = temp.GetBuffer(0);
										temp.ReleaseBuffer();
										
										if(SQLITE_OK!=sqlite3_exec(db,Tempsql,0,0,&zErrMsg))
										{
											::MessageBox(NULL,sqlite3_errmsg(db),NULL,MB_OK | MB_ICONERROR);
											sqlite3_close(db);
											return;
										}
									}
								}//for(int k = Tem...end

								//当ID没有相同的时候-----------------------------------------------------
								if (sql == "")
								{
									if(!ptrTable->BOF)
										ptrTable->MoveFirst();
									_variant_t tempvar;
									sql = "insert into " + tempName + "(";
									CString Tempfieldname;
									Tempfieldname = "";
									long Fieldsum = ptrTableInfo->Fields->Count;
									int l;
									for(l = 0; l < Fieldsum; l++)
									{
										tempvar = ptrTable->GetCollect("name");
										if(tempvar.vt!=VT_NULL)
											Tempfieldname = (LPCSTR)_bstr_t(tempvar);
										
										sql += Tempfieldname;
										sql += ",";
										ptrTable->MoveNext();
									}
									sql = sql.Left(strlen(sql)-1);
									sql += ") values(";
									
									if(!ptrTable->BOF)
										ptrTable->MoveFirst();
									
									for(l = 0; l < Fieldsum; l++)
									{
										tempvar = ptrTable->GetCollect("name");
										ptrTable->MoveNext();
										var = ptrTableInfo->GetCollect(tempvar);
										if (var.vt!=VT_NULL)
										{
											Tempfieldname = (LPCSTR)_bstr_t(var);
											Tempfieldname.TrimLeft();
											Tempfieldname.TrimRight();
											sql += "'";
											sql += Tempfieldname;
											sql += "',";
										}
										else
										{
											sql += "'";
											sql += "',";
										}
									}
									
									sql = sql.Left(strlen(sql)-1);
									sql += ")";
									
									CString fileName="ttttt";
									WriteLog(fileName, sql.GetBuffer(0), sql.GetLength());
									
									//执行sql语句-------------------------------------------------------------
									char* zErrMsg = 0;
									CString temp = sql;
									char* Tempsql = temp.GetBuffer(0);
									temp.ReleaseBuffer();
									
									if(SQLITE_OK!=sqlite3_exec(db,Tempsql,0,0,&zErrMsg))
									{
										::MessageBox(NULL,sqlite3_errmsg(db),NULL,MB_OK | MB_ICONERROR);
										sqlite3_close(db);
										return;
									}
								}//if (sql == "")end

							}//if(var.vt!=VT_NULL) end
						}
						catch (...)
						{
							MessageBox("插入或更新数据时出错");
							sqlite3_close(db);
							return;
						}
						ptrTableInfo->MoveNext();
					}//while(!ptrTableInfo->adoEOF) end
				}

				//字段名不相同-----------------------------------------------------------------------
				else
					MessageBox("您输入的表名在sqlite里边存在但字段不同","无法插入");

			}//if(m_edit_values == tempName) end

		}//循环于sqlite表完成

		//要插入表名在sqlite不存在——创建-----------------------------------------------------
		if(!tableexist)
		{
			CString temp = "create table " + m_edit_values + "(";
			
			ptrTable = ado.ExecuteSQLSentence("SELECT * FROM " + m_edit_values);
			long Fieldsum = ptrTable->Fields->Count;
			for(long num = 0; num < Fieldsum; num++)
			{
				var = ptrTable->Fields->Item[num]->Name;
				if(var.vt!=VT_NULL)
				{
					temp += (LPCSTR)_bstr_t(var);
					temp += " varchar(10),";
				}
			}
			temp = temp.Left(strlen(temp)-1);
			temp += ")";
			
			char* zErrMsg = 0;
			char* sql = temp.GetBuffer(0);
			temp.ReleaseBuffer();
			
			if(SQLITE_OK!=sqlite3_exec(db,sql,0,0,&zErrMsg))
			{
				::MessageBox(NULL,sqlite3_errmsg(db),NULL,MB_OK | MB_ICONERROR);
				sqlite3_close(db);
				return;
			}
			sqlite3_close(db);
			OpenSqlite();
			
			tempName = m_edit_values;
			goto xunhuan;
		}
		

	}


//----------------------------------------------------------------------
// 					//获取sql server数据库字段名
// 				ptr = ado.ExecuteSQLSentence("SELECT * FROM SysColumns WHERE id = Object_Id('"+ m_edit_values +"')");
// 				if(!ptr->BOF)
// 					ptr->MoveFirst();
// 				
// 				int icount = 0;
// 				CString fieldname;
// 				while(!ptr->adoEOF)
// 				{
// 					try
// 					{
// 						var = ptr->GetCollect("name");
// 						if(var.vt!=VT_NULL)
// 						{
// 							fieldname += (LPCSTR)_bstr_t(var);
// 							fieldname += ",";
// 						}
// 						
// 						UpdateData(FALSE);
// 						icount++;
// 						ptr->MoveNext();
// 					}
// 					catch (...)
// 					{
// 						MessageBox("查询字段名出错！");	
// 						return;
// 					}
// 				}
// 				fieldname = fieldname.Left(fieldname.GetLength()-1);
// 				if (fieldname.Left(2) == "ID")
// 				{
// 					fieldname = fieldname.Left(2) + " INTEGER PRIMARY KEY" + 
// 						fieldname.Right(fieldname.GetLength()-2);
// 				}
// 				CString strsql = "create table " + m_edit_values + "( " + fieldname + " )";
// 				
// 				

//---------------------------------------------------------------------------

// 	//执行除查询以外的sql语句
// 	char* zErrMsg = 0;
// 	CString temp = strsql;
// 	char* sql = temp.GetBuffer(0);
// 	temp.ReleaseBuffer();
// 	
// 	if(SQLITE_OK!=sqlite3_exec(db,sql,0,0,&zErrMsg))
// 	{
// 		::MessageBox(NULL,sqlite3_errmsg(db),NULL,MB_OK | MB_ICONERROR);
// 		sqlite3_close(db);
// 		return;
// 	}
//	sqlite3_close(db);
//----------------------------------------------------------------------------
// 	try
// 	{
// 		temp = "";
// 		//temp = "INSERT INTO " + m_edit_values + "VALUES(" + "dfg" + ")";
// 		temp = "select * from " + m_edit_values;
// 		ptr=ado.ExecuteSQLSentence(temp);
// 		if(!ptr->BOF)
// 			ptr->MoveFirst();
// 
// 		CString strtableinfo;
// 		while (ptr->adoEOF)
// 		{
// 			try
// 			{
// //				ptr->getrows();
// 			}
// 			catch (...){}
// 
// 
// 		}
// 
// 
// 	}
// 	catch (...)
// 	{
// 		MessageBox("sql server同步至sqlite的插入数据时出现错误！");
// 	}

// 	CString strSql;
// 	strSql.Format("select * from "+ m_edit_values);
// 	ptr=ado.ExecuteSQLSentence(strSql);	
// 	if(!ptr->BOF)
// 		ptr->MoveFirst();
// 
// 
// 	int i=0;
// 	CString strName,strPwd;
// 
// 	
// 	while(!ptr->adoEOF)
// 	{	
// 		try
// 		{
// 			var=ptr->GetCollect("id");
// 			strName="";
// 			if(var.vt!=VT_NULL	)
// 			{
// 				strName=(LPCSTR)_bstr_t(var);
// 			}
// 
// 			UpdateData(FALSE);
// 			i++;
// 			ptr->MoveNext();
// 		}
// 		catch(...)
// 		{
// 			MessageBox("插入错误");
// 			return;
// 		}
// 	}
	UpdateData(FALSE);

}


void CTextDlg::WriteLog(CString fileName, char* buff, int filelenth) 
{
	CString file = _T("");
	file.Format(_T("C:\\%s.log"), fileName);
	HANDLE hFile = CreateFile(file,
		GENERIC_WRITE, 
		0,
		NULL,
		CREATE_ALWAYS,
		FILE_ATTRIBUTE_NORMAL,
		NULL);
	DWORD dwBytesWritten;

	WriteFile(hFile, buff, filelenth, &dwBytesWritten, NULL);
	CloseHandle(hFile);
	//MessageBox(_T("OK..."));
}

void CTextDlg::OpenSqlite()
{
	//创建打开sqlite数据库
	sqlite3 *sdb;
	char buffer[MAX_PATH];
	::GetCurrentDirectory(MAX_PATH,buffer);
	strcat(buffer,"\\sqlite.db"); //创建名字为sqlite的数据库于工程目录下
	
	if(SQLITE_OK!=sqlite3_open(buffer,&sdb))
	{
		::MessageBox(NULL,sqlite3_errmsg(sdb),NULL,MB_OK | MB_ICONERROR);
		sqlite3_close(sdb);
		exit(1);
		return;
	}
	db=sdb;

}