开通博客赚积分
发布资源赚积分
分类

源码开发语言/平台
当前位置：首页 > 源码/资料 > 数据库系统 > SQL Server > 查看源码
SelectTables.cpp
资源名称：ISQL_src.zip　[点击查看]
上传用户：jsxglz
上传日期：2007-01-03
资源大小：117k
文件大小：12k
源码类别：

SQL Server

开发平台：

Visual C++

SelectTables.cpp：源码内容
  1. // SelectTables.cpp : implementation file
  2. //
  4. #include "stdafx.h"
  5. #include "interactivesql.h"
  6. #include "SelectTables.h"
  7. #include "MainFrm.h"
  9. #ifdef _DEBUG
  10. #define new DEBUG_NEW
  11. #undef THIS_FILE
  12. static char THIS_FILE[] = __FILE__;
  13. #endif
  15. const CString c_strSection = "Tables";
  16. const CString c_strViewsEntry = "Views";
  17. const CString c_strSystemTablesEntry = "System_Tables";
  18. const CString c_strTextOnlyEntry = "Text Only";
  19. /////////////////////////////////////////////////////////////////////////////
  20. // CSelectTables dialog
  22. CSelectTables::CSelectTables(CWnd* pParent /*=NULL*/)
  23. : CDialog(CSelectTables::IDD, pParent)
  24. {
  25. //{{AFX_DATA_INIT(CSelectTables)
  26. m_bTextOnly = FALSE;
  27. m_strSearch = _T("");
  28. //}}AFX_DATA_INIT
  30. m_bSort = true;
  31. }
  33. void CSelectTables::DoDataExchange(CDataExchange* pDX)
  34. {
  35. CDialog::DoDataExchange(pDX);
  36. //{{AFX_DATA_MAP(CSelectTables)
  37. DDX_Control(pDX, IDC_TEXT_ONLY, m_buttonTextOnly);
  38. DDX_Control(pDX, IDC_SYSTEM_TABLES, m_buttonSystemTables);
  39. DDX_Control(pDX, IDC_VIEWS, m_buttonViews);
  40. DDX_Control(pDX, IDC_LIST1, m_ctrlList);
  41. DDX_Check(pDX, IDC_TEXT_ONLY, m_bTextOnly);
  42. DDX_Text(pDX, IDC_SEARCH, m_strSearch);
  43. //}}AFX_DATA_MAP
  44. }
  46. BEGIN_MESSAGE_MAP(CSelectTables, CDialog)
  47. //{{AFX_MSG_MAP(CSelectTables)
  48. ON_NOTIFY(NM_DBLCLK, IDC_LIST1, OnDblclkList1)
  49. ON_NOTIFY(LVN_COLUMNCLICK, IDC_LIST1, OnColumnclickList1)
  50. ON_BN_CLICKED(IDC_SYSTEM_TABLES, OnSystemTables)
  51. ON_BN_CLICKED(IDC_VIEWS, OnViews)
  52. ON_EN_CHANGE(IDC_SEARCH, OnChangeSearch)
  53. ON_BN_CLICKED(IDC_TEXT_ONLY, OnTextOnly)
  54. //}}AFX_MSG_MAP
  55. END_MESSAGE_MAP()
  57. /////////////////////////////////////////////////////////////////////////////
  58. // CSelectTables message handlers
  60. BOOL CSelectTables::OnInitDialog() 
  61. {
  62. CWaitCursor wait;
  64. m_pFrame = (CMainFrame*)AfxGetMainWnd();
  65. ASSERT(m_pFrame);
  66. m_pFrame->m_wndStatusBar.SetPaneText(m_pFrame->m_nResultSetPaneNo, "Executing..."); 
  68. CDialog::OnInitDialog();
  70. LV_COLUMN lvcColumn;
  71. lvcColumn.mask = LVCF_FMT | LVCF_SUBITEM | LVCF_TEXT | LVCF_WIDTH;
  72. lvcColumn.fmt = LVCFMT_LEFT;
  74. CRect rect;
  75. m_ctrlList.GetClientRect(&rect);
  76. CSize size = rect.Size();
  77. int nWidth = size.cx/3;
  78. lvcColumn.cx = nWidth;
  79. lvcColumn.pszText = "Name";
  80. lvcColumn.iSubItem = 0;
  81. m_ctrlList.InsertColumn(0, &lvcColumn);
  83. lvcColumn.cx = nWidth;
  84. lvcColumn.pszText = "Type";
  85. lvcColumn.iSubItem = 1;
  86. m_ctrlList.InsertColumn(1, &lvcColumn);
  88. lvcColumn.cx = nWidth;
  89. lvcColumn.pszText = "Owner";
  90. lvcColumn.iSubItem = 2;
  91. m_ctrlList.InsertColumn(2, &lvcColumn);
  93. CWinApp* pApp = AfxGetApp();
  94. BOOL bCheck = pApp->GetProfileInt(c_strSection, c_strSystemTablesEntry, 0);
  95. m_buttonSystemTables.SetCheck(bCheck);
  97. bCheck = pApp->GetProfileInt(c_strSection, c_strViewsEntry, 0);
  98. m_buttonViews.SetCheck(bCheck);
  100. bCheck = pApp->GetProfileInt(c_strSection, c_strTextOnlyEntry, 0);
  101. m_buttonTextOnly.SetCheck(bCheck);
  102. m_buttonTextOnly.EnableWindow((m_buttonViews.GetCheck() &&
  103. (m_pFrame->m_bIsTSQLSupported || m_pFrame->m_bIsJetDriver ||
  104. !m_pFrame->m_strDBMS.CompareNoCase(m_pFrame->m_strOracle))));
  106. if(!PopulateList())
  107. SendMessage(WM_CLOSE);
  108. else
  109. {
  110. m_ctrlList.SetFullRowSel(TRUE);
  111. if(m_pFrame->m_bIsMultiSetSupported)
  112. m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
  114. if(m_buttonTextOnly.IsWindowEnabled())
  115. OnTextOnly();
  116. }
  118. return TRUE;  // return TRUE unless you set the focus to a control
  119.               // EXCEPTION: OCX Property Pages should return FALSE
  120. }
  122. BOOL CSelectTables::PopulateList()
  123. {
  124. BOOL bRet = FALSE;
  126. try
  127. {
  128. m_ctrlList.DeleteAllItems();
  130. char lpszType[64];
  131. strcpy(lpszType, "'TABLE'");
  132. if(m_buttonViews.GetCheck())
  133. strcat(lpszType, ",'VIEW'");
  134. if(m_buttonSystemTables.GetCheck())
  135. strcat(lpszType, ",'SYSTEM TABLE'");
  137. CTables set(&m_pFrame->m_database);
  138. bRet = set.Open(NULL, NULL, NULL, lpszType, CRecordset::forwardOnly);
  139. if(bRet)
  140. {
  141. if(!set.IsBOF())
  142. {
  143. CString sBuff;
  144. int nPos = -1;
  145. while(!set.IsEOF())
  146. {
  147. if(m_pFrame->m_bIsJetDriver)
  148. sBuff += "[" + set.m_strTableName + "]|";
  149. else
  150. {
  151. nPos = set.m_strTableName.Find(' ');
  152. if(nPos != -1) // MS SQL Server scenario
  153. sBuff += "[" + set.m_strTableName + "]| ";
  154. else // All other
  155. sBuff += set.m_strTableName + "| ";
  156. }
  158. sBuff += set.m_strTableType + "|";
  159. sBuff += set.m_strTableOwner + "|";
  161. CHelpers::Insert(&m_ctrlList, sBuff);
  163. set.MoveNext();
  164. }
  166. m_ctrlList.SetFocus();
  167. m_ctrlList.SetItemState(0, LVIS_SELECTED | LVIS_FOCUSED,
  168. LVIS_SELECTED | LVIS_FOCUSED);
  169. }
  170. }
  171. }
  172. catch(CDBException* e)
  173. {
  174.    if(e)
  175.    {
  176. CString sMsg;
  178. if(!e->m_strError.IsEmpty())
  179. sMsg.Format("%s%s", (LPCTSTR)e->m_strError, (LPCTSTR)e->m_strStateNativeOrigin);
  180. else
  181. sMsg = e->m_strStateNativeOrigin;
  183. e->Delete();
  185. m_pFrame->OnExceptionClear(sMsg);
  186. m_pFrame->m_strStatusText.Format("CDBException::m_nRetCode: %dn", e->m_nRetCode);
  187.    }
  188. }
  189. catch(CMemoryException* e)
  190. {
  191. m_pFrame->m_strStatusText = "Out-of-memory.";
  192. m_pFrame->OnExceptionClear(m_pFrame->m_strStatusText);
  194. if(e)
  195. e->Delete();
  196. }
  197. catch(const char* e)
  198. {
  199. m_pFrame->m_strStatusText.Format("DSN: %s", (const char*)m_pFrame->m_strODBCDSN);
  200. m_pFrame->OnExceptionClear(e);
  201. }
  203. if(!m_ctrlList.GetItemCount())
  204. {
  205. GetDlgItem(IDOK)->EnableWindow(FALSE);
  206. GetDlgItem(IDCANCEL)->SetWindowText(_T("&Close"));
  207. }
  208. else
  209. {
  210. GetDlgItem(IDOK)->EnableWindow(TRUE);
  211. GetDlgItem(IDCANCEL)->SetWindowText(_T("Cancel"));
  212. }
  214. return bRet;
  215. }
  217. void CSelectTables::OnOK() 
  218. {
  219. CWaitCursor wait;
  221. UpdateData(TRUE);
  223. BOOL bRet = TRUE;
  225. CString sTableName;
  226. int nItem = m_ctrlList.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
  227. m_strSQL = "nn";
  228. m_strType = m_ctrlList.GetItemText(nItem, 1);
  229. m_strType.MakeUpper();
  230. if(m_bTextOnly && m_strType.Find("VIEW") != -1 && (m_pFrame->m_bIsTSQLSupported ||
  231. m_pFrame->m_bIsJetDriver || !m_pFrame->m_strDBMS.CompareNoCase(m_pFrame->m_strOracle)))
  232. {
  233. if(m_ctrlList.GetNextItem(nItem, LVNI_ALL | LVNI_SELECTED) != -1)
  234. {
  235. AfxMessageBox("Multiple selections are not allowed with the current option.");
  236. bRet = FALSE;
  237. }
  238. else
  239. {
  240. sTableName = m_ctrlList.GetItemText(nItem, 0);
  241. if(m_pFrame->m_bIsTSQLSupported)
  242. m_strSQL += "sp_helptext " + sTableName + "nn";
  244. if(m_pFrame->m_bIsJetDriver)
  245. {
  246. if(sTableName.Find('[') != -1)
  247. {
  248. int nLength = sTableName.GetLength(); 
  249. sTableName = sTableName.Mid(1, nLength-2);
  250. }
  251. m_strSQL += sTableName + "nn";
  252. }
  254. if(!m_pFrame->m_strDBMS.CompareNoCase(m_pFrame->m_strOracle))
  255. {
  256. m_strSQL += "select TEXT from ALL_SOURCE where type = 'VIEW' and NAME = '";
  257. m_strSQL += sTableName + "'" ;
  258. }
  259. }
  260. }
  261. else
  262. {
  263. CString sSQL;
  264. while(nItem  != -1 )
  265. {
  266. sTableName = m_ctrlList.GetItemText(nItem, 0);
  267. sSQL = "SELECT " + GetColumnList(sTableName) + " FROM ";
  268. sSQL += sTableName + "nn";
  269. m_strSQL += sSQL;
  270. nItem = m_ctrlList.GetNextItem(nItem, LVNI_ALL | LVNI_SELECTED);
  271. }
  272. }
  274. if(bRet)
  275. {
  276. m_strSQL = m_strSQL.Left(m_strSQL.GetLength()-2);
  278. CWinApp* pApp = AfxGetApp();
  279. pApp->WriteProfileInt(c_strSection, c_strViewsEntry, m_buttonViews.GetCheck());
  280. pApp->WriteProfileInt(c_strSection, c_strSystemTablesEntry, m_buttonSystemTables.GetCheck());
  281. pApp->WriteProfileInt(c_strSection, c_strTextOnlyEntry, m_buttonTextOnly.GetCheck());
  283. CDialog::OnOK();
  284. }
  285. }
  287. void CSelectTables::OnDblclkList1(NMHDR* pNMHDR, LRESULT* pResult) 
  288. {
  289. CWaitCursor wait;
  291. if(GetDlgItem(IDOK)->IsWindowEnabled())
  292. OnOK();
  294. *pResult = 0;
  295. }
  297. void CSelectTables::OnColumnclickList1(NMHDR* pNMHDR, LRESULT* pResult) 
  298. {
  299. CWaitCursor wait;
  301. NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
  303. CSortColumn sort(&m_ctrlList, pNMListView->iSubItem, false);
  304. sort.Sort(m_bSort = !m_bSort);
  306. *pResult = 0;
  307. }
  309. void CSelectTables::OnSystemTables() 
  310. {
  311. CWaitCursor wait;
  313. UpdateData(TRUE);
  315. m_ctrlList.SetRedraw(FALSE);
  316. PopulateList();
  317. m_ctrlList.SetRedraw();
  318. }
  320. void CSelectTables::OnViews() 
  321. {
  322. OnSystemTables();
  324. m_buttonTextOnly.EnableWindow(m_buttonViews.GetCheck() &&
  325. (m_pFrame->m_bIsTSQLSupported || m_pFrame->m_bIsJetDriver ||
  326. !m_pFrame->m_strDBMS.CompareNoCase(m_pFrame->m_strOracle)));
  327. }
  329. CString CSelectTables::GetColumnList(const CString& sTableName)
  330. {
  331. CString sTable, sColumns;
  332. sTable = sTableName; 
  334. try
  335. {
  336. if(sTable.Find('[') != -1)
  337. {
  338. int nLength = sTable.GetLength(); 
  339. sTable = sTable.Mid(1, nLength-2);
  340. }
  342. if(sTable.GetLength() > 8 && m_pFrame->IsTextDataSource())
  343. sColumns = GetColumnListForTextDataSrc(sTableName);
  344. else
  345. {
  346. CColumns set(&m_pFrame->m_database);
  347. if(set.Open(NULL, NULL, sTable, NULL))
  348. {
  349. if(!set.IsBOF())
  350. {
  351. CString sBuff;
  352. int nPos = -1;
  353. while(!set.IsEOF())
  354. {
  355. if(m_pFrame->m_bIsJetDriver)
  356. {
  357. nPos = set.m_strColumnName.Find('.');
  358. if(nPos == -1)
  359. sColumns += "[" + set.m_strColumnName + "], ";
  360. else
  361. {
  362. sColumns += "[" + set.m_strColumnName.Left(nPos) + "].";
  363. sColumns += "[" + set.m_strColumnName.Mid(nPos+1) + "], ";
  364. }
  365. }
  366. else
  367. {
  368. nPos = set.m_strColumnName.Find(' ');
  369. if(nPos != -1) // MS SQL Server scenario
  370. sColumns += "[" + set.m_strColumnName + "], ";
  371. else // All other
  372. sColumns += set.m_strColumnName + ", ";
  373. }
  375. set.MoveNext();
  376. }
  378. sColumns = sColumns.Left(sColumns.GetLength()-2);
  379. }
  380. }
  381. }
  382. }
  383. catch(CDBException* e)
  384. {
  385.    if(e)
  386.    {
  387. CString sMsg;
  389. if(!e->m_strError.IsEmpty())
  390. sMsg.Format("%s%s", (LPCTSTR)e->m_strError, (LPCTSTR)e->m_strStateNativeOrigin);
  391. else
  392. sMsg = e->m_strStateNativeOrigin;
  394. e->Delete();
  396. m_pFrame->m_strStatusText.Format("CDBException::m_nRetCode: %dn", e->m_nRetCode);
  397. m_pFrame->OnExceptionClear(sMsg);
  398.    }
  399. }
  400. catch(CMemoryException* e)
  401. {
  402. m_pFrame->m_strStatusText = "Out-of-memory.";
  403. m_pFrame->OnExceptionClear(m_pFrame->m_strStatusText);
  405. if(e)
  406. e->Delete();
  407. }
  408. catch(const char* e)
  409. {
  410. m_pFrame->m_strStatusText.Format("DSN: %s", (const char*)m_pFrame->m_strODBCDSN);
  411. m_pFrame->OnExceptionClear(e);
  412. }
  414. return sColumns;
  415. }
  417. void CSelectTables::OnChangeSearch() 
  418. {
  419. UpdateData(TRUE);
  421. LVFINDINFO findInfo;
  422. findInfo.flags = LVFI_PARTIAL | LVFI_STRING;
  423. findInfo.psz = (LPCTSTR)m_strSearch;
  424. int nItem = m_ctrlList.FindItem(&findInfo);
  425. m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
  426. m_ctrlList.SetItemState(nItem, LVIS_SELECTED, LVIS_SELECTED);
  427. if(m_pFrame->m_bIsMultiSetSupported)
  428. m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
  429. m_ctrlList.EnsureVisible(nItem, TRUE);
  430. }
  432. void CSelectTables::OnTextOnly() 
  433. {
  434. if(m_buttonViews.GetCheck())
  435. {
  436. if(m_buttonTextOnly.GetCheck())
  437. m_ctrlList.ModifyStyle(0, LVS_SINGLESEL);
  438. else
  439. {
  440. if(m_pFrame->m_bIsMultiSetSupported)
  441. m_ctrlList.ModifyStyle(LVS_SINGLESEL, 0);
  442. }
  443. }
  445. m_ctrlList.SetFocus();
  446. }
  448. CString CSelectTables::GetColumnListForTextDataSrc(const CString& sTableName)
  449. {
  450. CWaitCursor wait;
  452. CString sColumns;
  453. CRecordsetEx set(&m_pFrame->m_database);
  454. if(set.ExecDirect(CString("SELECT * FROM " + sTableName + " WHERE 1=2")))
  455. {
  456. CODBCFieldInfo fieldInfo;
  457. int nCols = set.GetODBCFieldCount();
  458. for(int n = 0; n < nCols; n++)
  459. {
  460. set.GetODBCFieldInfo(n, fieldInfo);
  461. sColumns += "[" + fieldInfo.m_strName + "], ";
  462. }
  464. sColumns = sColumns.Left(sColumns.GetLength()-2);
  465. }
  467. return sColumns;
  468. }