ODBC连接数据库(转)
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函数负责与数据源的连接．
Code1 virtual BOOL Open(
2 LPCTSTR lpszDSN,
3 BOOL bExclusive = FALSE,
4 BOOL bReadOnly = FALSE,
5 LPCTSTR lpszConnect = _T("ODBC;"),
6 BOOL bUseCursorLib = TRUE
7 );
8 //连接一个名为Stutdent的数据源
9 m_db.Open(“Student”);
10 //在连接数据源的同时指定了用户账号和口令.
11 m_db.Open(NULL,FALSE,FASLE,”ODBC;DSN=Stutent;UID=hqlong;PWD=123456”);
12 //弹出一个函数源对话框
13
３． 使用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单选按键选中
主窗口初始化函数：
Code1 BOOL CMainDlg::OnInitDialog(){
2 CDialog::OnInitDialog();
3 // TODO: Add extra initialization here
4 //默认为“精确查询”按钮被选中
5 CDialog::CheckRadioButton(IDC_RADIO1,IDC_RADIO2,IDC_RADIO1);
6 //默认“姓名”项被选中
7 m_ctrlfield.SetCurSel(0);
8 //让窗口出现时居中
9 CenterWindow();
10 //为标题栏加图标
11 m_hIcon=AfxGetApp()->LoadIcon(IDR_MAINFRAME);
12 this->SetIcon(m_hIcon,TRUE);
13 //设定列的颜色
14 m_ctrlperson.SetTextColor(RGB(100,0,100));
15 m_ctrlperson.SetTextBkColor(RGB(240,247,233));
16 //确定列名
17 m_ctrlperson.InsertColumn(0,"序号");
18 m_ctrlperson.InsertColumn(1,"姓名");
19 m_ctrlperson.InsertColumn(2,"性别");
20 m_ctrlperson.InsertColumn(3,"关系");
21 m_ctrlperson.InsertColumn(4,"联系电话");
22 m_ctrlperson.InsertColumn(5,"手机号码");
23 m_ctrlperson.InsertColumn(6,"家庭住址");
24 m_ctrlperson.InsertColumn(7,"工作单位");
25 m_ctrlperson.InsertColumn(8,"E_mail地址");
26 m_ctrlperson.InsertColumn(9,"OICQ号码");
27 //重新分配列宽
28 m_ctrlperson.SetColumnWidth(0,40);
29 m_ctrlperson.SetColumnWidth(1,60);
30 m_ctrlperson.SetColumnWidth(2,40);
31 m_ctrlperson.SetColumnWidth(3,80);
32 m_ctrlperson.SetColumnWidth(4,100);
33 m_ctrlperson.SetColumnWidth(5,100);
34 m_ctrlperson.SetColumnWidth(6,150);
35 m_ctrlperson.SetColumnWidth(7,150);
36 m_ctrlperson.SetColumnWidth(8,150);
37 m_ctrlperson.SetColumnWidth(9,80);
38 //扩展风格
39 m_ctrlperson.SetExtendedStyle(LVS_EX_FULLROWSELECT| LVS_EX_GRIDLINES);//|LVS_SHOWSELALWAYS);
40 //查找记录并按ID升序排列
41 m_query.Format("select * from person order by ID ASC");
42 RefreshData();
43 //为操作区增加工具条提示
44 m_addTip.Create(this);
45 CButton* m_radio_add=(CButton*)GetDlgItem(IDC_RADIO_ADD);
46 m_addTip.AddTool(m_radio_add,"单击此按钮，可以为通讯录增加记录。");
47 m_modTip.Create(this);
48 CButton* m_radio_mod=(CButton*)GetDlgItem(IDC_RADIO_MOD);
49 m_modTip.AddTool(m_radio_mod,"先选取列表框中要修改的记录，再单击此按钮就可以修改相应的记录。");
50 m_delTip.Create(this);
51 CButton* m_radio_del=(CButton*)GetDlgItem(IDC_RADIO_DEL);
52 m_delTip.AddTool(m_radio_del,"先选取列表框中要删除的记录，再单击此按钮就可以彻底将此记录删除。");
53 m_searchTip.Create(this);
54 CButton* m_radio_search=(CButton*)GetDlgItem(IDC_RADIO_SEARCH);
55 m_searchTip.AddTool(m_radio_search,"单击此按钮可以查到你想要的记录。");
56 //使对话框居中显示
57 CRect dlgrect;
58 GetWindowRect(&dlgrect);
59 CRect desktoprect;
60 GetDesktopWindow()->GetWindowRect(&desktoprect);
61 CRect rect1,rect2;
62 GetDlgItem(IDC_SEARCH_STATIC)->GetWindowRect(&rect1);
63 GetDlgItem(IDC_FLAG_STATIC)->GetWindowRect(&rect2);
64 m_nReduceHeight=rect1.Height()+(rect1.top-rect2.bottom)/2;//收缩后窗体的高度
65 dlgrect.bottom-=(rect1.Height()-(rect1.top-rect2.bottom)/2);
66 MoveWindow(&dlgrect);//移动对话框到新的坐标位置．
67 m_bflag=false;
68 m_bsearchflag=true;
69 return TRUE; // return TRUE unless you set the focus to a control
70
71 // EXCEPTION: OCX Property Pages should return FALSE
72
73
刷新记录处理函数．
Code1 void CMainDlg::RefreshData()
2 {
3 //首先确保数据库打开
4 if(!m_database.IsOpen())
5 {
6 //m_database.Open(_T("addresslist"));
7 }
8 //对列表控件的内容更新,清空原来的内容
9 m_ctrlperson.DeleteAllItems();
10 //创建记录集
11 CPersonSet m_personset(&m_database);
12 m_personset.Open(AFX_DB_USE_DEFAULT_TYPE,m_query);
13 CDBVariant varValue;
14 char buf[20];
15 //用来记录当前记录的序号
16 int i=0;
17 //如果表中有记录，打开后将游标定在第一位,使记录集中的第一条记录成为当前记录
18 if(m_personset.GetRecordCount()!=0) m_personset.MoveFirst();
19
20 while(!m_personset.IsEOF())
21 {
22 int temp=0;
23 //对整型数字的处理
24 m_personset.GetFieldValue(temp,varValue);
25 sprintf(buf,"%d",varValue.m_lVal);
26 m_ctrlperson.InsertItem(i,buf);
27 //对字符串显示处理
28 //m_personset.GetFieldValue(0,varValue);
29 //m_ctrlperson.SetItemText(i,0,varValue.m_pstring->GetBuffer(1));
30
31 m_personset.GetFieldValue(1,varValue);
32 m_ctrlperson.SetItemText(i,1,varValue.m_pstring->GetBuffer(1));
33
34 m_personset.GetFieldValue(2,varValue);
35 m_ctrlperson.SetItemText(i,2,varValue.m_pstring->GetBuffer(1));
36
37 m_personset.GetFieldValue(3,varValue);
38 m_ctrlperson.SetItemText(i,3,varValue.m_pstring->GetBuffer(1));
39
40 m_personset.GetFieldValue(4,varValue);
41 m_ctrlperson.SetItemText(i,4,varValue.m_pstring->GetBuffer(1));
42
43 m_personset.GetFieldValue(5,varValue);
44 m_ctrlperson.SetItemText(i,5,varValue.m_pstring->GetBuffer(1));
45
46 m_personset.GetFieldValue(6,varValue);
47 m_ctrlperson.SetItemText(i,6,varValue.m_pstring->GetBuffer(1));
48
49 m_personset.GetFieldValue(7,varValue);
50 m_ctrlperson.SetItemText(i,7,varValue.m_pstring->GetBuffer(1));
51
52 m_personset.GetFieldValue(8,varValue);
53 m_ctrlperson.SetItemText(i,8,varValue.m_pstring->GetBuffer(1));
54
55 m_personset.GetFieldValue(9,varValue);
56 m_ctrlperson.SetItemText(i,9,varValue.m_pstring->GetBuffer(1));
57 m_personset.MoveNext();
58 i++;
59 }
60 //在标题栏中显示共有记录条数
61 int counts=m_personset.GetRecordCount();
62 CString str;
63 str.Format("通讯录 V1.0 试用版 目前共有记录数： %d",counts);
64 this->SetWindowText(str);
65 }
修改记录处理函数：
Code1 void CMainDlg::OnRadioMod()
2 {
3 // TODO: Add your control notification handler code here
4 m_database.Close();//本对话框断开与数据库的连接
5 CModifyDlg dlg;
6 dlg.m_database.Open(_T("addresslist"));
7 int i=m_ctrlperson.GetSelectionMark();
8 CString strSQL;
9 int id=atoi(m_ctrlperson.GetItemText(i,0));
10 CPersonSet m_recordset;
11 CDBVariant varValue;
12 if(i==-1)
13 {
14 MessageBox("请选择一条要修改的记录！","提示",MB_OK|MB_ICONINFORMATION);
15 }
16 else
17 {
18 int temp=0;
19 strSQL.Format("select * from person where ID=%d",id);
20 m_recordset.Open(AFX_DB_USE_DEFAULT_TYPE,strSQL);
21
22 m_recordset.GetFieldValue(temp,varValue);
23 dlg.m_modid=varValue.m_lVal;
24
25 m_recordset.GetFieldValue(1,varValue);
26 dlg.m_modname=varValue.m_pstring->GetBuffer(1);
27
28 m_recordset.GetFieldValue(2,varValue);
29 dlg.m_modsex=varValue.m_pstring->GetBuffer(1);
30
31 m_recordset.GetFieldValue(3,varValue);
32 dlg.m_modrelation=varValue.m_pstring->GetBuffer(1);
33
34 m_recordset.GetFieldValue(4,varValue);
35 dlg.m_modtelephone=varValue.m_pstring->GetBuffer(1);
36
37 m_recordset.GetFieldValue(5,varValue);
38 dlg.m_modhandphone=varValue.m_pstring->GetBuffer(1);
39
40 m_recordset.GetFieldValue(6,varValue);
41 dlg.m_modaddress=varValue.m_pstring->GetBuffer(1);
42
43 m_recordset.GetFieldValue(7,varValue);
44 dlg.m_modworkplace=varValue.m_pstring->GetBuffer(1);
45
46 m_recordset.GetFieldValue(8,varValue);
47 dlg.m_modemail=varValue.m_pstring->GetBuffer(1);
48
49 m_recordset.GetFieldValue(9,varValue);
50 dlg.m_modoicq=varValue.m_pstring->GetBuffer(1);
51 m_database.Close();//此处不能断开与数据库的连接
52 dlg.DoModal();
53 RefreshData();
54 }
55 }
浙公网安备 33010602011771号