dynamiccreatetable.cpp

动态创建数据库

// DynamicCreateTable.cpp : implementation file
//

#include "stdafx.h"
#include "test3.h"
#include "DynamicCreateTable.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CDynamicCreateTable dialog


CDynamicCreateTable::CDynamicCreateTable(CWnd* pParent /*=NULL*/)
: CDialog(CDynamicCreateTable::IDD, pParent)
{
	//{{AFX_DATA_INIT(CDynamicCreateTable)
	m_strTableName = _T("");
	m_strSelectDatabase = _T("");
	m_strSearchState = _T("");
	m_strColumn1 = _T("");
	m_strColumn2 = _T("");
	m_strColumnAttribute1 = _T("");
	m_strColumnAttribute2 = _T("");
	//}}AFX_DATA_INIT
}


void CDynamicCreateTable::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDynamicCreateTable)
	DDX_Control(pDX, IDC_EDITTOADD, m_ctrlEditToAdd);
	DDX_Control(pDX, IDC_COLUMNATTRIBUTE2, m_ctrlColumnAttribute2);
	DDX_Control(pDX, IDC_COLUMNATTRIBUTE1, m_ctrlColumnAttribute1);
	DDX_Control(pDX, IDC_TABLENAME, m_ctrlTableName);
	DDX_Control(pDX, IDC_SEARCHSTATE, m_ctrlSearchState);
	DDX_Control(pDX, IDC_ADDEDLIST, m_ctrlAddedList);
	DDX_Control(pDX, IDC_TOADDLIST, m_ctrlToAddList);
	DDX_Control(pDX, IDC_SELECTDATABASE, m_ctrlSelectDatabase);
	DDX_Text(pDX, IDC_TABLENAME, m_strTableName);
	DDX_CBString(pDX, IDC_SELECTDATABASE, m_strSelectDatabase);
	DDX_Text(pDX, IDC_SEARCHSTATE, m_strSearchState);
	DDX_Text(pDX, IDC_COLUMN1, m_strColumn1);
	DDX_Text(pDX, IDC_COLUMN2, m_strColumn2);
	DDX_CBString(pDX, IDC_COLUMNATTRIBUTE1, m_strColumnAttribute1);
	DDX_CBString(pDX, IDC_COLUMNATTRIBUTE2, m_strColumnAttribute2);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CDynamicCreateTable, CDialog)
//{{AFX_MSG_MAP(CDynamicCreateTable)
ON_BN_CLICKED(IDC_EXIT, OnExit)
ON_CBN_SELCHANGE(IDC_SELECTDATABASE, OnSelchangeSelectdatabase)
ON_BN_CLICKED(IDC_EDITTOADD, OnEdittoadd)
ON_BN_CLICKED(IDC_BEGINCREATION, OnBegincreation)
ON_BN_CLICKED(IDC_ADDALL, OnAddall)
//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDynamicCreateTable message handlers

void CDynamicCreateTable::OnExit() 
{
	// TODO: Add your control notification handler code here
	exit(0);
	
}

void CDynamicCreateTable::OnSelchangeSelectdatabase() 
{
	// TODO: Add your control notification handler code here
	//显示的速度太慢，只能在线程中去使用
	//	 m_ctrlSearchState.SetWindowText("搜索中,请等待...");
	//	 InvalidateRect(NULL);
	//	 m_strSearchState="搜索中,请等待...";
	//	 UpdateData(FALSE);
	UpdateData();
	// 在这里显示数据库中已经存在的表
	m_ctrlAddedList.ResetContent();	
	CString strConnection; 
	CString tempstr;
	
	strConnection.Format("Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;\
		User ID=sa;Initial Catalog=%s;Data Source=.",m_strSelectDatabase); //ERIC-PC
    //Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;
	//Initial Catalog=master;Data Source=ERIC-PC
	try
	{
		pConn->Open((_bstr_t)strConnection,"sa","sa",adModeUnknown);
		
		pRst =pConn->OpenSchema(adSchemaTables);
		while(!(pRst->ADOEOF)) 	
		{
			//获取表格摘要//测试使用
			//			_bstr_t table_schema = pRst->Fields->GetItem("TABLE_SCHEMA")->Value; 
			
			//获取表格目录//测试使用
			//			_bstr_t table_catalog = pRst->Fields->GetItem("TABLE_CATALOG")->Value; 
			
			//获取表格 
			_bstr_t table_name = pRst->Fields->GetItem("TABLE_NAME")->Value; 
			
			//获取表格类型 
			_bstr_t table_type = pRst->Fields->GetItem("TABLE_TYPE")->Value; 
			
			//过滤一下，只输出表格名称，其他的省略 
			if(strcmp(((LPCSTR)table_type),"TABLE")==0)
			{ 
				//				m_ctrlSearchState.SetWindowText("搜索结果显示");
				//				UpdateData(FALSE);
				//在下面开始显示表名
				tempstr.Format("%s",(LPCSTR)table_name); 
                m_ctrlAddedList.AddString(tempstr);
				
			} 
			pRst->MoveNext(); 
		}
		pRst->Close();
		pConn->Close();
		
		m_ctrlTableName.EnableWindow(TRUE);
		m_ctrlEditToAdd.EnableWindow(TRUE);
		
	}
	catch (_com_error &e)//捕捉异常
	{
		AfxMessageBox(e.Description()); 
	}
    UpdateData(FALSE);
	
	
}

BOOL CDynamicCreateTable::OnInitDialog() 
{
	CDialog::OnInitDialog();	
	// TODO: Add extra initialization here
	::CoInitialize(NULL);  
	m_hrConn=pConn.CreateInstance(_uuidof(Connection));
	pRst.CreateInstance(_uuidof(Recordset)); 
	pComm.CreateInstance(_uuidof(Command));
	//清空所有列表
	m_ctrlToAddList.ResetContent();
	m_ctrlSelectDatabase.ResetContent();
	m_ctrlAddedList.ResetContent();
	ShowDatabaseList();
	
	return TRUE;  // return TRUE unless you set the focus to a control
	// EXCEPTION: OCX Property Pages should return FALSE
}

//在下拉框中显示已经存在的数据库
void CDynamicCreateTable::ShowDatabaseList()
{
    UpdateData();
	ASSERT(m_ctrlSelectDatabase.GetCount() == 0);
	
    HRESULT tempHr=m_hrConn;
	CString strConnection; 
	CString tempstr;
	try 
	{ 
		if(SUCCEEDED(tempHr)) 
		{ 
		strConnection.Format("Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;\
			User ID=sa;Data Source=."); //ERIC-PC
		
		tempHr= pConn->Open((_bstr_t)strConnection,"sa","sa",adModeUnknown); 
		pRst =pConn->OpenSchema(adSchemaCatalogs); 
		
		while(!(pRst->ADOEOF)) 
		{ 
            //获取数据库名称
            _bstr_t database_name= pRst->Fields->GetItem("CATALOG_NAME")->Value;
			
			tempstr.Format("%s",(LPCSTR)database_name);
			//剔除系统数据库
			if(tempstr!="master"&&tempstr!="msdb"&&tempstr!="tempdb"&&tempstr!="model")
				m_ctrlSelectDatabase.AddString(tempstr);
			
			
			pRst->MoveNext(); 
		} 
		pRst->Close(); 
		} 
		pConn->Close(); 
	}
	catch (_com_error &e)//捕捉异常
	{
		AfxMessageBox(e.Description()); 
	}
	
    UpdateData(FALSE);
}


int g_TableIndex=0;
int g_PressBtnNo=0;
//编辑表名后加入到待加入列表
void CDynamicCreateTable::OnEdittoadd() 
{
	// TODO: Add your control notification handler code here
    //使用新数据库创建的表///////
    CString strTableName;
	CString strTemp="字段:";
	CString strTemp1;
	g_PressBtnNo++;
	//  CString strTempAttribute1;
    CString strTemp2;
	//	CString strTempAttribute2;
	UpdateData();
	if(!m_strTableName.IsEmpty())
	{
		if(g_PressBtnNo<=5)
		{
			
			m_strColumn1.TrimLeft();
			m_strColumn1.TrimRight();
			m_strColumnToCreate1=m_strColumn1;
			m_strColumn2.TrimLeft();
			m_strColumn2.TrimRight();
			m_strColumnToCreate2=m_strColumn2;
			m_strTableName.TrimLeft();
			m_strTableName.TrimRight();
			m_strTableNameToCreate=m_strTableName;
			
			
			TABLEBOJ *pTableObj=NULL;
			if(!m_ToCreateTableList.Lookup(m_strTableNameToCreate,(CObject*&)pTableObj))
				pTableObj=new TABLEBOJ;
			pTableObj->ColumnName1=m_strColumn1;
			pTableObj->ColumnName2=m_strColumn2;
			pTableObj->Table=m_strTableName;
			m_ToCreateTableList.SetAt(pTableObj->Table,(CObject*)pTableObj);
			
			
			//      strTempAttribute1=m_strColumnAttribute1;
			//		strTempAttribute2=m_strColumnAttribute2;
			
			strTemp1="    "+m_strColumn1+"  "+m_strColumnAttribute1+" "+"20";
			strTemp2="    "+m_strColumn2+"  "+m_strColumnAttribute2+" "+"20";
			strTableName="表:"+m_strTableName;
			m_ctrlToAddList.InsertString(g_TableIndex,strTableName);
			g_TableIndex++;
			m_ctrlToAddList.InsertString(g_TableIndex,strTemp);
			g_TableIndex++;
			m_ctrlToAddList.InsertString(g_TableIndex,strTemp1);
			g_TableIndex++;
			m_ctrlToAddList.InsertString(g_TableIndex,strTemp2);
			g_TableIndex++;
			m_strTableName="";
			m_strColumn1="";
			m_strColumn2="";
			
			UpdateData(FALSE);
			m_ctrlTableName.SetFocus();
		}
		else
		{
			AfxMessageBox("一次只能创建5张表",MB_USERDEFINE);
			m_ctrlTableName.EnableWindow(FALSE);
			m_ctrlEditToAdd.EnableWindow(FALSE);
			g_PressBtnNo=0;
			UpdateData(FALSE);
		}
	} 
	else
		AfxMessageBox("请输入要加入的表名",MB_USERDEFINE);	
}

//这个函数没什么用了
void CDynamicCreateTable::OnBegincreation() 
{
	// TODO: Add your control notification handler code here
	   try
	   {		
		   _bstr_t strConnect =" Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=MyDataBase;Data Source=ERIC-PC";	
		   pConn->Open(strConnect,"sa","sa",adModeUnknown);
		   
	   }
	   catch (_com_error &e)
	   {
		   AfxMessageBox(e.Description()); 
	   }
	   //创建要添加的
	   
}

void CDynamicCreateTable::OnAddall() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	CString strConnection; //连接字符串
	CString strSQL;//查询语句
	CString strtempTableName;//表
	CString strtempColumn1;//字段1
    CString strtempColumn2;//字段2
	POSITION pos;
	TABLEBOJ *pTableObj=NULL;
	strConnection.Format("Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;\
		User ID=sa;Initial Catalog=%s;Data Source=.",m_strSelectDatabase);
	try
	{
		pConn->Open((_bstr_t)strConnection,"sa","sa",adModeUnknown); 
		
		for( pos = m_ToCreateTableList.GetStartPosition(); pos != NULL; )
		{
			m_ToCreateTableList.GetNextAssoc(pos,strtempTableName, (CObject*&)pTableObj);
			
			strtempColumn1=pTableObj->ColumnName1;
			strtempColumn2=pTableObj->ColumnName2;
			
			strSQL.Format("CREATE TABLE %s(\
											 %s char(20) NULL ,\
											 %s char(20) NULL )",\
											 strtempTableName,strtempColumn1,\
											 strtempColumn2);
			
			_variant_t varSQL=_variant_t(strSQL);
			_bstr_t bstrConnectionstring=(_bstr_t)strConnection;
			
			pRst->Open(varSQL,bstrConnectionstring,adOpenStatic,adLockOptimistic,adCmdUnknown);
		}
		m_ctrlToAddList.ResetContent();
	}
	catch (_com_error &e)//捕捉异常
	{
		AfxMessageBox(e.Description()); 
	}
	
}