一、SQL Server数据库的安装
选择SQL Server 版本进行安装,安装步骤参考SQL Server 安装教程 - 知乎 (zhihu.com)
或者参考SQL Server 最详细安装教程 - 知乎 (zhihu.com)
需要注意的是,安装进行到一半,遇到提示需要先重启。这里需要修改一下注册表就能解决这个问题。
注册表进去的方法是:开始菜单/运行:输入regedit回车即可
找到HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Control/Session Manager/PENDINGFILE RENAME POERATIONS,删除即解决问题。
还有一个要注意的是数据库引擎配置,配置身份验证模式有Windows身份验证模式和混合模式,这里最好用混合模式,为SQL的sa账户设置密码,这一步是实现远程访问必须的,若这一步不小心忽略了,后期要另外设置。
我当时安装时选择的Windows身份验证模式,最后是找到这篇资料SQL Server如何设置用户名和密码登录_sqlserver设置用户名密码_仲瑿的博客-CSDN博客
重新设置用户名和密码。大概步骤就是右击local-->属性-->弹出的菜单中选择安全-->勾选SqlServer与Windows身份验证模式并确定关闭这个界面-->选择安全性-登录名-->在sa上右击属性,修改密码,去掉强制密码策略,点击确定-->重启完成。
二、SQL Server开启远程连接
参考这篇资料sql server 开启远程连接和测试教程 - 知乎 (zhihu.com)进行SQL Server的远程连接配置。
其中,使用UDL测试SQL Server连接问题可以参考这篇资料使用udl生成数据库连接字符串 - 百度文库 (baidu.com)
通过创建一个UDL(Universal Data Link)文件测试,步骤: 1. 创建一个txt文件 2. 将txt文件后缀名修改为UDL 3. 打开UDL会列出机器上的Provider 4. 选择一个Provider进行测试,这里我选择NativeClient 5. 输入服务器名称和连接信息进行测试 当然也可以通过ODBC连接测试,但是OLE DB Driver没有UDL的多 6. 将文本文件后缀名udl改回txt,查看文件中最后一行包含Provider等的字符串,即为远程连接数据库的字符串。
需要注意的是第5步中的服务器名称别忘填了,可以通过查看数据库连接属性,找到产品-->服务器名称,复制粘贴即可。
三、创建数据表
以创建一个简单的学生数据表为例,包括学号、姓名、状态、成绩、密码、权限等信息。SQL语句如下:
Use Master;
GO
CREATE Database MyDatabase1;
GO
Use MyDatabase1;
GO
if exists (select count(*) from [dbo].[student] S)
drop table [student];
go
CREATE TABLE student
(
sno char(8) PRIMARY KEY,
sname varchar(8) NOT NULL,
sstate varchar(8) CHECK( sstate = '在线' OR sstate = '实验中' OR sstate = '已完成' OR sstate = '离线'),
sresult varchar(8) CHECK( sresult = '合格' OR sresult = '不合格' OR sresult = '未考核'),
spassword varchar(8),
spermission varchar(8) CHECK( spermission = '普通' OR spermission = '管理员')
);
GO
INSERT INTO student VALUES('20102001',' 阎妮','在线', '未考核', '123', '管理员');
INSERT INTO student VALUES('20102002',' 张有来','实验中', '未考核', '123', '普通');
INSERT INTO student VALUES('20102003',' 王文喜','已完成', '合格', '123', '普通');
INSERT INTO student VALUES('20102004',' 赵敏','已完成', '不合格', '123', '普通');
INSERT INTO student VALUES('20102005',' 罗莎','实验中', '未考核', '123', '普通');
INSERT INTO student VALUES('20102006',' 蒙恬','已完成', '合格', '123', '普通');
GO
点击新建查询,编辑如上SQL语句后点击执行。生成表格dbo.student
四、开发操作数据库的系统
如果是开发一个单独的操作数据库的系统,可以直接在系统对应头文件中加入动态库文件msado15.dll即可。以MFC软件为例,在stdafx.h最后加入#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
代码如下
// stdafx.h : include file for standard system include files,
// or project specific include files that are used frequently, but
// are changed infrequently
//
#if !defined(AFX_STDAFX_H__EF60DE48_B8AF_4BAB_ACF2_B73D146C5073__INCLUDED_)
#define AFX_STDAFX_H__EF60DE48_B8AF_4BAB_ACF2_B73D146C5073__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#define VC_EXTRALEAN // Exclude rarely-used stuff from Windows headers
#include <afxwin.h> // MFC core and standard components
#include <afxext.h> // MFC extensions
#include <afxdisp.h> // MFC Automation classes
#include <afxdtctl.h> // MFC support for Internet Explorer 4 Common Controls
#ifndef _AFX_NO_AFXCMN_SUPPORT
#include <afxcmn.h> // MFC support for Windows Common Controls
#endif // _AFX_NO_AFXCMN_SUPPORT
//{{AFX_INSERT_LOCATION}}
// Microsoft Visual C++ will insert additional declarations immediately before the previous line.
#endif // !defined(AFX_STDAFX_H__EF60DE48_B8AF_4BAB_ACF2_B73D146C5073__INCLUDED_)
#include <icrsint.h> //Include support for VC++ Extensions
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "adoEOF")
操作数据库的库为ado,资源为操作SQLServer数据库的库ado资源-CSDN文库
完成连接、增、删、改、查等操作。以连接和查询代码为例。
BOOL CADOSQLDlg::OnInitDialog()
{
CDialog::OnInitDialog();
HRESULT hr;
try
{
hr = m_pConnection.CreateInstance("ADODB.Connection");
if(SUCCEEDED(hr))
{
hr = m_pConnection->Open("Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=sa;Initial Catalog=MyDatabase1;Data Source=KF-PC-LIUSHENG", "", "", adModeUnknown);
}
}
catch(_com_error e)
{
AfxMessageBox("Error!");
}
m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
m_pRecordset->Open("SELECT * FROM student",
m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
ListData();
// Add "About..." menu item to system menu.
// IDM_ABOUTBOX must be in the system command range.
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);
}
}
// Set the icon for this dialog. The framework does this automatically
// when the application's main window is not a dialog
SetIcon(m_hIcon, TRUE); // Set big icon
SetIcon(m_hIcon, FALSE); // Set small icon
// TODO: Add extra initialization here
return TRUE; // return TRUE unless you set the focus to a control
}
插入的代码示例
void CADOSQLDlg::OnInsert()
{
// TODO: Add your control notification handler code here
UpdateData();
if(m_sname == "" || m_sno == "")
{
AfxMessageBox("学号和姓名不能为空!");
return;
}
try
{
m_pRecordset->AddNew();
m_pRecordset->PutCollect("sno", _variant_t(m_sno));
m_pRecordset->PutCollect("sname", _variant_t(m_sname));
m_pRecordset->PutCollect("sstate", _variant_t(m_state));
m_pRecordset->PutCollect("sresult", _variant_t(m_result));
m_pRecordset->PutCollect("spermission", _variant_t(m_permission));
m_pRecordset->Update();
AfxMessageBox("插入成功!");
int nCurSel = m_list.GetCurSel();
ListData();
m_list.SetCurSel(nCurSel);
OnSelchangeList1();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
修改的代码示例
void CADOSQLDlg::OnChange()
{
// TODO: Add your control notification handler code here
UpdateData();
if(m_list.GetCount() == 0)
{
AfxMessageBox("???????!");
return;
}
else if(m_list.GetCurSel() < 0 || m_list.GetCurSel() > m_list.GetCount())
m_list.SetCurSel(0);
try
{
m_pRecordset->PutCollect("sno", _variant_t(m_sno));
m_pRecordset->PutCollect("sname", _variant_t(m_sname));
m_pRecordset->PutCollect("sstate", _variant_t(m_state));//atol
m_pRecordset->PutCollect("sresult", _variant_t(m_result));
m_pRecordset->PutCollect("spermission", _variant_t(m_permission));
m_pRecordset->Update();
int nCurSel = m_list.GetCurSel();
ListData();
m_list.SetCurSel(nCurSel);
OnSelchangeList1();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
删除的代码示例
void CADOSQLDlg::OnDelete()
{
// TODO: Add your control notification handler code here
if(m_list.GetCount() == 0)
return;
else if(m_list.GetCurSel() < 0 || m_list.GetCurSel() > m_list.GetCount())
m_list.SetCurSel(0);
try
{
m_pRecordset->Delete(adAffectCurrent);
m_pRecordset->Update();
int nCurSel = m_list.GetCurSel();
m_list.DeleteString(nCurSel);
if(nCurSel == 0 && (m_list.GetCount() != 0))
m_list.SetCurSel(nCurSel);
else if(m_list.GetCount() != 0)
m_list.SetCurSel(nCurSel-1);
OnSelchangeList1();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
查询的代码示例
//查找
void CADOSQLDlg::OnFind()
{
// TODO: Add your control notification handler code here
UpdateData(TRUE);
CString strSql;
if(!m_sname.IsEmpty() && !m_sno.IsEmpty())
strSql.Format("SELECT * FROM student WHERE sname = '%s' AND sno = '%s'",
m_sname,m_sno);
else if(!m_sname.IsEmpty() && m_sno.IsEmpty())
strSql.Format("SELECT * FROM student WHERE sname = '%s'",m_sname);
else if(m_sname.IsEmpty() && !m_sno.IsEmpty())
strSql.Format("SELECT * FROM student WHERE sno = '%s'",m_sno);
else
strSql = "SELECT * FROM student";
try
{
m_pRecordset->Close();
m_pRecordset->Open(strSql.AllocSysString(),
m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
ListData();
}
ListData()的代码示例
void CADOSQLDlg::ListData()
{
_variant_t var;
CString strname,strstate,strsno,strres,strpermission;
m_list.ResetContent();
strname = strstate = strsno = strres = strpermission = "";
try
{
if(!m_pRecordset->BOF)
m_pRecordset->MoveFirst();
else
{
AfxMessageBox("??????");
return;
}
while(!m_pRecordset->adoEOF)
{
var = m_pRecordset->GetCollect("sno");
if(var.vt != VT_NULL)
strsno = (LPCSTR)_bstr_t(var);
var = m_pRecordset->GetCollect("sname");
if(var.vt != VT_NULL)
strname = (LPCSTR)_bstr_t(var);
var = m_pRecordset->GetCollect("sstate");
if(var.vt != VT_NULL)
strstate = (LPCSTR)_bstr_t(var);
var = m_pRecordset->GetCollect("sresult");
if(var.vt != VT_NULL)
strres = (LPCSTR)_bstr_t(var);
var = m_pRecordset->GetCollect("spermission");
if(var.vt != VT_NULL)
strpermission = (LPCSTR)_bstr_t(var);
m_list.AddString( strsno + " --> " + strname + " --> "+ strstate + " --> "+ strres + " --> "+ strpermission);
m_pRecordset->MoveNext();
}
m_list.SetCurSel(0);
OnSelchangeList1();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
上面的代码也可以改写成动态库的形式,初始化Com口 ::CoInitialize(NULL);
例如连接访问数据库的代码接口示例
//连接访问数据库
extern "C" __declspec(dllexport) int connectDb(CString ConnectStr)
{
HRESULT hr;
try
{
hr = pConnection.CreateInstance("ADODB.Connection");
if (SUCCEEDED(hr))
{
hr = pConnection->Open(ConnectStr.AllocSysString(), "", "", adModeUnknown);
}
else
{
return 0;
}
}
catch (_com_error e)
{
MessageBox(NULL, _T("Error!"), NULL, MB_OK);
}
pRecordset.CreateInstance(__uuidof(Recordset));
try
{
pRecordset->Open("SELECT * FROM student",
pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch (_com_error* e)
{
MessageBox(NULL, e->ErrorMessage(), NULL, MB_OK);
}
return 1;
}
//查询数据库
extern "C" __declspec(dllexport) int QueryDb(CString strUser, CString strPassword, CString &strSno, CString &strSpassword, CString &strSpermission)
{
CString strSql;
strSql.Format(_T("SELECT * FROM student WHERE sno = '%s' AND spassword = '%s'"), strUser, strPassword);
try
{
if (pRecordset->State)
{
pRecordset->Close();
pRecordset.Release();
pRecordset = NULL;
pRecordset.CreateInstance(__uuidof(Recordset));
}
pRecordset->Open(strSql.AllocSysString(),
pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch (_com_error* e)
{
MessageBox(NULL, e->ErrorMessage(), NULL, MB_OK);
}
_variant_t var;
try
{
if (!pRecordset->BOF)
pRecordset->MoveFirst();
else
{
MessageBox(NULL, _T("??????"), NULL, MB_OK);
return 0;
}
while (!pRecordset->adoEOF)
{
var = pRecordset->GetCollect("sno");
if (var.vt != VT_NULL)
strSno = (LPCSTR)_bstr_t(var);
var = pRecordset->GetCollect("spassword");
if (var.vt != VT_NULL)
strSpassword = (LPCSTR)_bstr_t(var);
var = pRecordset->GetCollect("spermission");
if (var.vt != VT_NULL)
strSpermission = (LPCSTR)_bstr_t(var);
pRecordset->MoveNext();
}
}
catch (_com_error* e)
{
MessageBox(NULL, e->ErrorMessage(), NULL, MB_OK);
}
return 1;
}
为什么要改写成动态库?这个也是根据项目开发需要而来的,因为有的较大系统中,需要用到的数据库可能就登录、查询和信息修改这一类功能。直接加载msado15.dll会出错(出错原因欢迎知道的读者放在评论区),改写成动态库提供接口的方式,可以让系统间接访问到数据库,也能达到操作数据库的效果。
示例效果一:
示例效果二