ODBC数据库连接常用步骤加演示代码（VC++学习笔记）

ODBC数据操作步骤：

１．设置一个成员变量，用来接收数据源：

CDatabase m_database;

２．建立记录集：

CPswdSet* m_recordset=new CPswdSet(&m_database);

３．发送SQL语句，返回从数据库里里得到的记录集．

 

strSQL.Format("select * from password where PASSWORD='%s'",m_password);

CDatabase类操作步骤：

１．       构造一个CDatabase对象：

 

CDatabase m_db; //在文档类中嵌入一个CDatabase对象

２．       调用Open成员函数，Open函数负责与数据源的连接．

virtual BOOL Open(
   LPCTSTR lpszDSN,
   BOOL bExclusive = FALSE,
   BOOL bReadOnly = FALSE,
   LPCTSTR lpszConnect = _T("ODBC;"),
   BOOL bUseCursorLib = TRUE
);
//连接一个名为Stutdent的数据源
m_db.Open(“Student”);
//在连接数据源的同时指定了用户账号和口令.
m_db.Open(NULL,FALSE,FASLE,”ODBC;DSN=Stutent;UID=hqlong;PWD=123456”);
//弹出一个函数源对话框
m_db.Open(NULL);

３．       使用Execute()函数执行SQL语句．

 

m_db->Exeucte(“SELECT * INTO StudentArchieve FROM Student”);

执行一条插入语句：

 

strSQL.Format("insert into person values(%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s')"
	,i,m_name,m_sex,m_relation,m_hometelephone,m_handphone, m_address,m_workplace,m_email,m_oicq);
m_database.ExecuteSQL(strSQL);

常用函数：

long GetRecordCount( ) const;

Return Value：

返回记录集中的记录数，记录集中没有记录，则返回0，如果记录数不能决定，则返回-1．

 

//将IDC_RADIO1,IDC_RADIO2之间的IDC_RADIO1单选按键选中

主窗口初始化函数：

 

BOOL CMainDlg::OnInitDialog(){
       CDialog::OnInitDialog();
	  // TODO: Add extra initialization here
       //默认为“精确查询”按钮被选中
       CDialog::CheckRadioButton(IDC_RADIO1,IDC_RADIO2,IDC_RADIO1);
       //默认“姓名”项被选中
       m_ctrlfield.SetCurSel(0);
       //让窗口出现时居中
       CenterWindow();
       //为标题栏加图标
       m_hIcon=AfxGetApp()->LoadIcon(IDR_MAINFRAME);
       this->SetIcon(m_hIcon,TRUE);
       //设定列的颜色
       m_ctrlperson.SetTextColor(RGB(100,0,100));
       m_ctrlperson.SetTextBkColor(RGB(240,247,233));
       //确定列名
       m_ctrlperson.InsertColumn(0,"序号");
       m_ctrlperson.InsertColumn(1,"姓名");
       m_ctrlperson.InsertColumn(2,"性别");
       m_ctrlperson.InsertColumn(3,"关系");
       m_ctrlperson.InsertColumn(4,"联系电话");
       m_ctrlperson.InsertColumn(5,"手机号码");
       m_ctrlperson.InsertColumn(6,"家庭住址");
       m_ctrlperson.InsertColumn(7,"工作单位");
       m_ctrlperson.InsertColumn(8,"E_mail地址");
       m_ctrlperson.InsertColumn(9,"OICQ号码");
       //重新分配列宽
       m_ctrlperson.SetColumnWidth(0,40);
       m_ctrlperson.SetColumnWidth(1,60);
       m_ctrlperson.SetColumnWidth(2,40);
       m_ctrlperson.SetColumnWidth(3,80);
       m_ctrlperson.SetColumnWidth(4,100);
       m_ctrlperson.SetColumnWidth(5,100);
       m_ctrlperson.SetColumnWidth(6,150);
       m_ctrlperson.SetColumnWidth(7,150);
       m_ctrlperson.SetColumnWidth(8,150);
       m_ctrlperson.SetColumnWidth(9,80);
       //扩展风格
       m_ctrlperson.SetExtendedStyle(LVS_EX_FULLROWSELECT| LVS_EX_GRIDLINES);//|LVS_SHOWSELALWAYS);
       //查找记录并按ID升序排列
       m_query.Format("select * from person order by ID ASC");
       RefreshData();
       //为操作区增加工具条提示
       m_addTip.Create(this);
       CButton* m_radio_add=(CButton*)GetDlgItem(IDC_RADIO_ADD);
       m_addTip.AddTool(m_radio_add,"单击此按钮，可以为通讯录增加记录。");
       m_modTip.Create(this);
       CButton* m_radio_mod=(CButton*)GetDlgItem(IDC_RADIO_MOD);
       m_modTip.AddTool(m_radio_mod,"先选取列表框中要修改的记录，再单击此按钮就可以修改相应的记录。");
       m_delTip.Create(this);
       CButton* m_radio_del=(CButton*)GetDlgItem(IDC_RADIO_DEL);
       m_delTip.AddTool(m_radio_del,"先选取列表框中要删除的记录，再单击此按钮就可以彻底将此记录删除。");
       m_searchTip.Create(this);
       CButton* m_radio_search=(CButton*)GetDlgItem(IDC_RADIO_SEARCH);
       m_searchTip.AddTool(m_radio_search,"单击此按钮可以查到你想要的记录。");
       //使对话框居中显示
       CRect dlgrect;
       GetWindowRect(&dlgrect);
       CRect desktoprect;
       GetDesktopWindow()->GetWindowRect(&desktoprect);
       CRect rect1,rect2;
       GetDlgItem(IDC_SEARCH_STATIC)->GetWindowRect(&rect1);
       GetDlgItem(IDC_FLAG_STATIC)->GetWindowRect(&rect2);
       m_nReduceHeight=rect1.Height()+(rect1.top-rect2.bottom)/2;//收缩后窗体的高度
       dlgrect.bottom-=(rect1.Height()-(rect1.top-rect2.bottom)/2);
       MoveWindow(&dlgrect);//移动对话框到新的坐标位置．
       m_bflag=false;
       m_bsearchflag=true;
       return TRUE;  // return TRUE unless you set the focus to a control

                     // EXCEPTION: OCX Property Pages should return FALSE

}

刷新记录处理函数．

 

void CMainDlg::RefreshData()
{
	//首先确保数据库打开
	if(!m_database.IsOpen())
	{
		//m_database.Open(_T("addresslist"));
	}
	//对列表控件的内容更新,清空原来的内容
	m_ctrlperson.DeleteAllItems();
	//创建记录集
	CPersonSet m_personset(&m_database);
	m_personset.Open(AFX_DB_USE_DEFAULT_TYPE,m_query);
	CDBVariant varValue;
	char buf[20];
	//用来记录当前记录的序号
	int i=0;
	//如果表中有记录，打开后将游标定在第一位,使记录集中的第一条记录成为当前记录
	if(m_personset.GetRecordCount()!=0)	m_personset.MoveFirst();

	while(!m_personset.IsEOF())
	{
		int temp=0;
		//对整型数字的处理
		m_personset.GetFieldValue(temp,varValue);
		sprintf(buf,"%d",varValue.m_lVal);
		m_ctrlperson.InsertItem(i,buf);
		//对字符串显示处理
		//m_personset.GetFieldValue(0,varValue);
		//m_ctrlperson.SetItemText(i,0,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(1,varValue);
		m_ctrlperson.SetItemText(i,1,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(2,varValue);
		m_ctrlperson.SetItemText(i,2,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(3,varValue);
		m_ctrlperson.SetItemText(i,3,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(4,varValue);
		m_ctrlperson.SetItemText(i,4,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(5,varValue);
		m_ctrlperson.SetItemText(i,5,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(6,varValue);
		m_ctrlperson.SetItemText(i,6,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(7,varValue);
		m_ctrlperson.SetItemText(i,7,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(8,varValue);
		m_ctrlperson.SetItemText(i,8,varValue.m_pstring->GetBuffer(1));

		m_personset.GetFieldValue(9,varValue);
		m_ctrlperson.SetItemText(i,9,varValue.m_pstring->GetBuffer(1));
		m_personset.MoveNext();
		i++;
	}
	//在标题栏中显示共有记录条数
	int counts=m_personset.GetRecordCount();
	CString str;
	str.Format("通讯录 V1.0 试用版     目前共有记录数： %d",counts);
	this->SetWindowText(str);
}

修改记录处理函数：

 

void CMainDlg::OnRadioMod()
{
	// TODO: Add your control notification handler code here
	m_database.Close();//本对话框断开与数据库的连接
	CModifyDlg dlg;
	dlg.m_database.Open(_T("addresslist"));
	int i=m_ctrlperson.GetSelectionMark();
	CString strSQL;
	int id=atoi(m_ctrlperson.GetItemText(i,0));
	CPersonSet m_recordset;
	CDBVariant varValue;
	if(i==-1)
	{
		MessageBox("请选择一条要修改的记录！","提示",MB_OK|MB_ICONINFORMATION);
	}
	else
	{
		int temp=0;
		strSQL.Format("select * from person where ID=%d",id);
		m_recordset.Open(AFX_DB_USE_DEFAULT_TYPE,strSQL);

		m_recordset.GetFieldValue(temp,varValue);
		dlg.m_modid=varValue.m_lVal;

		m_recordset.GetFieldValue(1,varValue);
		dlg.m_modname=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(2,varValue);
		dlg.m_modsex=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(3,varValue);
		dlg.m_modrelation=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(4,varValue);
		dlg.m_modtelephone=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(5,varValue);
		dlg.m_modhandphone=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(6,varValue);
		dlg.m_modaddress=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(7,varValue);
		dlg.m_modworkplace=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(8,varValue);
		dlg.m_modemail=varValue.m_pstring->GetBuffer(1);

		m_recordset.GetFieldValue(9,varValue);
		dlg.m_modoicq=varValue.m_pstring->GetBuffer(1);
		m_database.Close();//此处不能断开与数据库的连接
		dlg.DoModal();
		RefreshData();
	}
}

2007-5-10 机器人　于　北京