tsqldlg.cpp

将dbf文件批量转换成本地或局域网SQLSERVE表

// TSQLDlg.cpp : 实现文件
//

#include "stdafx.h"
#include "TSQL.h"
#include "TSQLDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// 用于应用程序“关于”菜单项的 CAboutDlg 对话框

class CAboutDlg : public CDialog
{
public:		
	CAboutDlg();

// 对话框数据
	enum { IDD = IDD_ABOUTBOX };

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV 支持

// 实现
protected:
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
END_MESSAGE_MAP()


// CTSQLDlg 对话框




CTSQLDlg::CTSQLDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CTSQLDlg::IDD, pParent)
	, m_uid(_T("sa"))
	, m_pwd(_T(""))
{
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CTSQLDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST2, m_list);
	DDX_Control(pDX, IDC_COMBO1, m_CBox);
	DDX_Text(pDX, IDC_EDIT2, m_uid);
	DDX_Text(pDX, IDC_EDIT3, m_pwd);
	DDX_Control(pDX, IDC_COMBO2, m_DBbox);
}

BEGIN_MESSAGE_MAP(CTSQLDlg, CDialog)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	//}}AFX_MSG_MAP
	ON_BN_CLICKED(IDC_BUTTON1, &CTSQLDlg::OnBnClickedButton1)
	ON_NOTIFY(NM_DBLCLK, IDC_LIST2, &CTSQLDlg::OnNMDblclkList2)
	ON_BN_CLICKED(IDC_BUTTON2, &CTSQLDlg::OnBnClickedButton2)
END_MESSAGE_MAP()


// CTSQLDlg 消息处理程序

BOOL CTSQLDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// 将“关于...”菜单项添加到系统菜单中。

	// IDM_ABOUTBOX 必须在系统命令范围内。
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// 设置此对话框的图标。当应用程序主窗口不是对话框时，框架将自动
	//  执行此操作
	SetIcon(m_hIcon, TRUE);			// 设置大图标
	SetIcon(m_hIcon, FALSE);		// 设置小图标

	// TODO: 在此添加额外的初始化代码

	DWORD dwStyle = m_list.GetStyle();
	dwStyle |= LVS_EX_CHECKBOXES;
	m_list.SetExtendedStyle(dwStyle);

	/*//控制某个服务器的启动关闭
	SQLDMO::_SQLServerPtr m_ssp;
	if(FAILED(m_ssp.CreateInstance(__uuidof(SQLDMO::SQLServer))))
	{
        AfxMessageBox(_T("不能创建SQLServerPtr对象。"));
		return FALSE;
	}
    
	try
	{
	    m_ssp->PutLoginTimeout(10);
		m_ssp->PutApplicationName("ddkdk");
		m_ssp->PutHostName("aahostName");
		m_ssp->PutNetPacketSize(1024);

		CString strServer(_T("(local)"));
		CString strUserName(_T("sa"));
		CString strPassword("hwh");

	}
	catch(_com_error e)
	{
         try
		 {
              m_ssp->Start(FALSE,_variant_t(strServer),_variandt_t(strUserName),_variant_t(strPassword));

		 }
		 catch(_com_error pe)
		 {
			 AfxMessageBox(pe.Description());
			 m_ssp.Release();
             return FALSE;
		 }
	}*/

    GetSQLServer();
	
	return TRUE;  // 除非将焦点设置到控件，否则返回 TRUE
}

void CTSQLDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// 如果向对话框添加最小化按钮，则需要下面的代码
//  来绘制该图标。对于使用文档/视图模型的 MFC 应用程序，
//  这将由框架自动完成。

void CTSQLDlg::OnPaint()
{
	if (IsIconic())
	{
		CPaintDC dc(this); // 用于绘制的设备上下文

		SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0);

		// 使图标在工作矩形中居中
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// 绘制图标
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

//当用户拖动最小化窗口时系统调用此函数取得光标显示。
//
HCURSOR CTSQLDlg::OnQueryDragIcon()
{
	return static_cast<HCURSOR>(m_hIcon);
}


void CTSQLDlg::OnBnClickedButton1()
{
	// TODO: 在此添加控件通知处理程序代码
	this->UpdateData(true);
	_ConnectionPtr pcon;
	if(FAILED(pcon.CreateInstance(__uuidof(Connection))))
	{     
		//::SetSystemCursor(OCR_NORMAL);
		AfxMessageBox(_T("不能创建Connection对象。"));

		return;
	}

	CString cbstr;
    m_CBox.GetWindowTextW(cbstr);
	CString dbstr;
	this->m_DBbox.GetWindowTextW(dbstr);
	CString str;
	if(cbstr!=""&&dbstr!="")
	    str.Format(_T("Provider=SQLOLEDB;Server=%s;DATABASE=%s;UID=%s;PWD=%s"),cbstr,dbstr,m_uid,m_pwd);
    try
	{			
		pcon->Open(str.AllocSysString(),"","",adModeUnknown);
	}
    catch(_com_error e)
	{	
		//::SetSystemCursor();
		AfxMessageBox(e.Description());

		return ;
	}

	_CommandPtr pcom;
	if(FAILED(pcom.CreateInstance(__uuidof(Command))))
	{     
		//::SetSystemCursor(OCR_NORMAL);
		AfxMessageBox(_T("不能创建Command对象。"));

		return;
	}

	pcom->ActiveConnection = pcon;
    pcom->CommandType = adCmdText;

	int count = m_list.GetItemCount();

	int landr;
	str = "";
    CString sqlstr;
	CString rstr;
	int leng;
	for(int i = 0;i<count;i++)
	{
		if(m_list.GetCheck(i))
		{
			str = m_list.GetItemText(i,0);
			leng = str.GetLength();
			landr = this->Getpie(leng,str);
			leng--;
			rstr = str.Right(leng-landr);
			rstr.Replace(_T(".dbf"),_T(""));
			sqlstr.Format(_T("select * into %s from openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=%s;SourceType=DBF','select * from %s')"),rstr,str.Left(landr),rstr);
			pcom->CommandText = sqlstr.AllocSysString();
			try
			{
				pcom->Execute(NULL,NULL,adCmdText);
			}
			catch(_com_error e)
			{
				if(MessageBox(e.Description() + _T("是否先保留？"),_T("提示"),MB_YESNO|MB_ICONQUESTION) == IDYES)
					continue; 
			}
			if(m_list.DeleteItem(i))
			{
			   i--;
			   count--;
			}
		}
	}
	//strsql = _T("select * into ");
	//CString filesql;
	//CString dd;
	//CString filename;
		/*dd = fd.GetPathName();
		filesql = fd.GetFileTitle();
		filename = fd.GetFileName();
		dd.Replace(filename,0);
		strsql += filesql;
        strsql += _T(" from openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=");

		strsql += dd;
		strsql += _T(";SourceType=DBF','select * from ");
		strsql += filesql;
		strsql += _T("')");*/
	/*this->UpdateData(true);

		AfxMessageBox(strsql);
        try
		{
		   theApp.pConn->Execute(strsql.AllocSysString(),NULL,adCmdText);
		}
		catch(_com_error e)
		{
			this->MessageBox(_T("导不入!"));
		}
	}*/
	/*_SQLServerPtr ssp;
	if(FAILED(ssp.CreateInstance(__uuidof(SQLServer))))
	{
		AfxMessageBox(_T("生成对象失败，请重启。"));
	}

	try
	{
		ssp->PutLoginTimeout(10);
		ssp->
	}
	catch(_com_error e)
	{

	}*/
}

void CTSQLDlg::OnNMDblclkList2(NMHDR *pNMHDR, LRESULT *pResult)
{
	// TODO: 在此添加控件通知处理程序代码

	CFileDialog fd(TRUE,0,0,4|2|OFN_ALLOWMULTISELECT,_T("*.dbf|*.dbf*|"));//OFN_ENABLEHOOK
	if(fd.DoModal() == IDOK)
	{
		CString sh;
		POSITION ps = fd.GetStartPosition();
		int i =0;
		while(ps)
		{
		   sh = fd.GetNextPathName(ps);
		   m_list.InsertItem(i,sh);
		   m_list.SetCheck(i);
		   i++;
		}
	}    
	  *pResult = 0;
}

BOOL CTSQLDlg::GetSQLServer(void)
{
	SQLDMO::_ApplicationPtr m_sqlapp;
	if(FAILED(m_sqlapp.CreateInstance(__uuidof(SQLDMO::Application))))
	{
        AfxMessageBox(_T("不能创建Application对象。"));
		return FALSE;
	}
	try
	{
		SQLDMO::NameListPtr serverlist = m_sqlapp->ListAvailableSQLServers();

		for(int i =0;i<serverlist->Count;i++)
		{
			this->m_CBox.AddString(serverlist->Item(i));
		}
		if(serverlist->Count>0)
			m_CBox.SetWindowTextW(_T("(local)"));
	}
	catch(_com_error ee)
	{
		AfxMessageBox(ee.Description());
        return FALSE;
	}
	return TRUE;
}

void CTSQLDlg::OnBnClickedButton2()
{
	// TODO: 在此添加控件通知处理程序代码
	//::SetSystemCursor(theApp.LoadCursorW(IDC_WAIT),OCR_WAIT);

	this->UpdateData(true);
	_ConnectionPtr pcon;
	if(FAILED(pcon.CreateInstance(__uuidof(Connection))))
	{     
		//::SetSystemCursor(OCR_NORMAL);
		AfxMessageBox(_T("不能创建Connection对象。"));

		return;
	}

	CString cbstr;
    m_CBox.GetWindowTextW(cbstr);
	CString str;
	str.Format(_T("Provider=SQLOLEDB;Server=%s;DATABASE=master;UID=%s;PWD=%s"),cbstr,m_uid,m_pwd);
    try
	{			
		pcon->Open(str.AllocSysString(),"","",adModeUnknown);
	}
    catch(_com_error e)
	{	
		//::SetSystemCursor();
		AfxMessageBox(e.Description());

		return ;
	}

    _RecordsetPtr prset;
	if(FAILED(prset.CreateInstance(__uuidof(Recordset))))
	{
		//::SetSystemCursor();
		AfxMessageBox(_T("不能创建Recordset对象。"));
		return;
	}

	try
	{
		prset = pcon->Execute("select name from sysdatabases",NULL,adCmdText);
	}
	catch(_com_error e)
	{
		//::SetSystemCursor();
		AfxMessageBox(e.Description());
		return ;
	}


	m_DBbox.ResetContent();
	LPCTSTR astr;
	while(!prset->EndofFile)
	{
		astr = (LPCTSTR)(_bstr_t)prset->GetCollect("name");
		m_DBbox.AddString(astr);
		prset->MoveNext();
	}
	m_DBbox.SetWindowTextW(astr);
	//::SetSystemCursor();
}

int CTSQLDlg::Getpie(int i,CString fenstr)
{
	i=i-4;
	for(i;i>1;i--)
	{
		if(fenstr.GetAt(i) == '\\')
			return i;
	}
	return 0;
}