SQL Server 2000 应用编程

1. SQL Server 2000应用编程

2. 1 编程概述 • 使用T-SQL对数据库进行的查询和修改难以满足用户的业务需求。用户大量的问题要通过更方便和灵活的编程语言来解决。为此，SQL Server提供了丰富的应用编程接口(API)。 • 常规数据库访问API • 数据库服务API。对数据库服务的管理配置。 • 分析服务API。实现对OLAP和数据挖掘服务和工具的访问。 • 元数据服务API。实现对元数据的访问。 • 英语查询API。将输入的英语句子传递给英语查询引擎，由该引擎返回SQL语句。 其中最常用的常规数据库访问API。常规数据库访问API支持以下各种编程方式： • ADO 最流行的开发方式 • URL • OLE DB • ODBC • 内嵌SQL的C语言编程

3. 1.1 ODBC概述 • ODBC(Open Data Base Connectivity ) • ODBC是一种数据库访问API,它独立与数据库，建立在SQL调用层接口上。 • 在使用ODBC开发数据库应用程序时，调用的是ODBC API函数和SQL语句。而数据的底层操作则由不同的数据库驱动程序来完成。

4. 1.1.1 ODBC体系结构 数据库应用程序 驱动程序管理器 ODBC … SQL Server 驱动程序 ORACLE 驱动程序 ACCESS 驱动程序 DB2 驱动程序 … ACCESS 数据源 DB2 数据源 SQL Server 数据源 ORACLE 数据源

5. 1.1.2 ODBC数据库的组成 • 1.应用程序 • 2.驱动程序管理器 • 3.驱动程序 • 4.数据源(Data Source Name, DSN) • 配置ODBC数据源

6. 1.2 OLE DB和ADO • OLE DB创建于OLE(对象的连接与嵌入 )技术基础上，其来源被称作provider • ADO(ActiveX Data Objects)并不是访问数据的底层解决方案。OLE Db是使用ADO的基础，ADO只是OLE提供服务的漂亮包装，实际上也是使用OLE DB进行数据库开发，但是使用ADO进行开发的速度要快得多。

7. 1.2.1 OLE DB的体系结构 应用程序(OLE DB消费者) OLE DB界面（例如ADO等） OLE DB提供者 数据源

8. 1.2.2 ADO的体系结构 数据库应用程序 ADO ODBC的 OLE DB 提供者 (MSDASQL) SQL server的 OLE DB 提供者 SQLOLEDB EXCEL的 OLE DB 提供者 OLE DB 提供者 …… ODBC 驱动程序 EXCEL 其它 数据源 SQL Server ODBC 数据源

9. 2 ADO概述 • ADO是数据库应用编程接口，它是为了实现与OLE-DB兼容的数据源，例如SQL Server的连接，应用程序通过ADO实现对数据库的连接，对数据库的查询和更新。 • ADO的主要的对象组成，每个对象有若干属性和方法。对象分别如下： • Connection 启用数据的交换。 • Command 包含 SQL 语句。 • Parameter 包含 SQL 语句参数。 • Recordset 启用数据的定位和操作。 • Field 包含 Recordset对象列。 • Error 包含连接错误。 • Property 包含 ADO 对象特性。

10. 2.1 ADO的Connection 对象 • 为应用程序访问数据库服务器中的数据库建立一个通道。使用Connection时要提供：数据库服务器名字、数据库名字、用户名字和访问的密码。每个连接只属于应用程序。 • 使用Initial Catalog定义数据库名 • 使用Data Source定义服务器名 • 使用User ID和Password属性来使用SQL Server的身份验证方式。

11. 2.2 Command 对象 • 定义在数据源上执行的命令和查询。通过连接发送一个查询的命令到数据库服务器上，数据库服务器返回查询结果(记录)到Recordset中。 • 使用 Command 对象的集合、方法、属性进行下列操作: • 使用 CommandText 属性定义命令（例如，SQL 语句）的可执行文本。 • 可使用 Execute 方法执行命令并在适当的时候返回 Recordset 对象。 • 通过设置 ActiveConnection 属性使打开的连接与 Command 对象关联。

12. 2.3 Recordset 对象 • 可使用 Recordset 对象操作来自提供者的数据。使用 ADO 时，通过 Recordset 对象可对几乎所有数据进行操作。所有 Recordset 对象均使用记录（行）和字段（列）进行构造。由于提供者所支持的功能不同，某些 Recordset 方法或属性有可能无效。

13. 3 使用ADO控件连接访问SQL Server • 步骤： • 在VB工程中添加一个ADO数据库控件 • 使用ADO数据库控件连接一个SQL Server数据库 • 将ADO数据控件连接到一个或多个数据绑定控件。

14. 3.1 在VB工程中添加一个ADO数据库控件 • 步骤： • 从“工程”菜单中选择“部件”命令，在“控件”选项卡中选中“Microsoft ADO Data Control6.0(OLEDB)”复选框。 • 单击“确定”按钮将ADO数据控件添加到工具箱中。 • 此时，在VB工具箱中可以看到刚刚添加进来的这个ADO数据控件，用鼠标指针指向它时，出现“Adodc”字样。

15. 3.2 使用ADO数据库控件连接SQL Server数据库 • 步骤： • 1. 在工具箱中单击Adodc，然后在窗体上沿斜向拖动鼠标。此时，在窗体上出现Adodc控件“Adodc1”。调整Adodc1大小和位置。设置Align属性为2 (VbAlignBottom)。 • 2. 建立与SQL Server数据库连接。将Adodc1的ConnectionString属性设置为一个有效的“使用连接字符串” ： • 单击ConnectionString属性行中的对话按钮，进入“属性页”对话框。在该对话框中选择“使用连接字符串”选项。单击“生成”按钮。 • 在数据库连接属性对话框选择“Microsoft OLE DB Provider for SQL Server”作为OLE DB提供程序。单击下一步，进入“数据链接属性”对话框。 • 选择或输入想要连接的SQL Server服务器名称 • 选择“指定的用户名称和密码”选项，输入用户名称和密码，并选中“允许保存密码”复选框（如果不选，运行时将出现一个“登录”对话框）。 • 选择“在服务器上选择数据库”选项，从下拉列表中选择要访问的SQL Server数据库。可以选择“测试连接”按钮测试连接是否成功，当看见“”字样时，单击“确定”按钮。返回“属性页”对话框。在对话框中可以看到一个长长的字符串。单击“确定”按钮结束ConnectionString属性设置过程。

16. 3.2 使用ADO数据库控件连接SQL Server数据库 • 步骤： 3. 设置想要访问的记录源。 • 设置数据控件“Adodc1”的RecordSource属性.单击该属性的对话框按钮。进入“属性”页对话框。 • 在“属性”页对话框中的“命令类型”下拉式列表中选择： • 向服务器发送T-SQL语句：选择1－adCmdText • 从服务器上的一个数据库表中检索数据：选择2－adCmdTable • 调用服务器上的一个存储过程：选择4－adCmdStoredProc • 根据情况选择，选择后单击“确定”按钮，关闭“属性”页对话框，完成连接。 • 4. 使用数据绑定控件 • 使用DataGrid控件显示表中记录 • 使用Label和TextBox控件显示单行记录 • 使用DataList和DataCombo控件显示单列多值

17. 3.2.1 数据绑定控件 所谓数据绑定控件，就是任何具有Datasource属性的控件，例如Label、TextBox、DataList、DataCombo或DataGrid控件。访问SQL Server数据库时，ADO数据控件用于建立VB数据绑定控件和SQL Server数据库之间的连接，数据绑定控件则用于显示或更新数据。 使用数据绑定控件显示或更新 SQL Server数据库中的数据时，必须进行以下设置： • 将数据绑定控件的Datasource属性设置为一个ADO数据控件名称 • 将数据绑定控件的DataFeld属性设置为结果集之中的一个字段名称。

18. 3.2.2 使用DataGrid控件显示表中记录 DataGrid控件是一种类似于表格的控件，它用于显示并允许对一个结果集之中的一系列行和列进行操作。当把DataGrid控件的Datasource属性设置为一个ADO数据控件时，DataGrid控件就会用结果集中的数据来自动填充，且其列标题也用结果集之中的字段名来设置。使用DataGrid控件的这些功能，能够快速地建立一个数据库应用程序，用于游览和编辑一个完整的结果集。 • 步骤： • 在工具箱中添加DataGrid控件,方法同ADO数据控件Adodc。 • 在窗体上添加一个DataGrid 控件DataGridl，并将其Datasource 属性设置为Adodcl。 • 在“属性”窗刀中选择DataGridl控件，然后对着Align属性连续双击数次，使该控件充满整个窗体内部。 • 在“属性”窗口中，将 DataGridl控件的AllowAddNew、AllowDelete和AlowUpdate属性都设置为True，以便在程序运行期间通过数据网格来添加、删除和修改记录。 • 右击DataGridl控件，从弹出式菜单中选择“检索字段”命令，当提示是否要以新的字段定义替换现有的网格布局时，单击“是”，此时在数据网格的列标题自动地使用结果集之中的字段名称来填充。 • 按F5键，运行程序。

19. 3.2.3 使用Label和TextBox控件显示单行记录 Label和TextBox控件通过ADO数据控件绑定到数据库之后，都可用于显示当前记录的一个字段值。所不同的是，Label控件中的数据是只读的，TextBox控件中的数据是可修改的。通过单击ADO数据控件上的箭头按钮，在不同记录行之间移动时，Label或TextBox控件中显示的字段值自动刷新。若在TextBox控件中修改了字段值，又移到另外一行记录上，则这种变化自动地保存到数据库中。

20. 3.2.3.1 使用Label和TextBox控件显示单行记录步骤 步骤： • 将ADO数据控件添加到VB工程中。在窗体上添加ADO数据控件Adodcl。通过设置Connechonstring属性建立与SQL Server服务器上的SDB数据库的连接；在设置Datasource属性时，将命令类型设置为1（adCmdTxt），输入下面的命令文本：SELECT sno 学号，sn 姓名，sex 性别，age 年龄 FROM s • 在窗体上添加五个Label控件和一个Line控件。分别在五个Label控件的Caption属性中输入浏览学生记录、学号、姓名、性别和年龄。 • 在窗格上添加四个TextBOX控件，分别对Text属性名修改为txtStNO、txtStNAME、txtStSEX和txtStAGE,将它们的Datasource属性都设置为ADO数据控件名称Adodcl，将它们的DataFeld属性分别设置为结果集的字段名称，依次是“学号”、“姓名”、“性别”和“年龄”。 • 在窗体上双击Adodcl控件，以打开代码窗口，然后编写以下事件过程： 'Recordset对象表示的是来自基本表或命令执行结果的记录全集 'Recordset对象所指的当前记录均为集合内的单个记录 'AbsolutePosition属性给出Recordset对象当前记录的序号位置 'RecordCount属性给出指示Recordset对象中记录的当前数目 '下面的语句用于显示当前记录指针的位置 Adodc1.Caption = Adodc1.Recordset.AbsolutePosition & "/" & Adodc1.Recordset.RecordCount

21. 3.2.3.2 定义按钮 步骤： • 将Adodcl控件的Visible属性设置为False，将它们隐藏起来。 • 添加6个按钮，分别将Caption属性命名为记录头、上一条、下一条、记录尾。 • 单击“按钮”，输入对应的程序。 ‘记录头 Private Sub Command1_Click() Adodc1.Recordset.MoveFirst End Sub ‘上一条 Private Sub Command2_Click() Adodc1.Recordset.MovePrevious If Adodc1.Recordset.BOF Then Adodc1.Recordset.MoveFirst End If 'Form1.Refres End Sub ‘下一条 Private Sub Command3_Click() Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast End If End Sub ‘记录尾 Private Sub Command4_Click() Adodc1.Recordset.MoveLast End Sub

22. 3.2.4 使用DataList和DataCombo控件显示单列多值 Label和TextBox控件作为数据绑定控件使用时，只能显示当前记录中的一个字段值，也称为“单行单列”。若要显示一个字段在多行记录中的取值，即实现“多行单列”显示，可以使用DataEst和DataCombo控件来完成。DataList和DataCombo控件都是数据绑定控件，它们一起存储在文件Msdatlst．cox中。要在应用程序中使用这两个控件，就必须把这个、OCX文件添加到工程中。发送应用程序时，则需要把文件Msdatlst．cox安装到用户的Wndows系统的System或System32目录下。 若要使用一个数据源中的一个字段填充DataLSt控件或DataCOmbo控件时，应当在设计时或运行时设置以下两个属性： • 将DataList控件或DataColnbo控件的Rowsource属性设置为ADO控件名称； • 将DataList控件或DataCombo控件的Lisffoeld属性设置为结果集之中的一个字段名称

23. 3.2.4.1 步骤 设计一个查询系统功能要求是：在一个列表中选择学生姓名时，能够在另一个列表中显示这个学生所有课程的成绩。设计好的用户界面如图所示。其设计步骤如下 ： • 将ADO数据控件、DataList控件和DataCombo控件添加到工程中。操作方法同上。 • 在窗体上添加一个ADO数据控件Adodc1，并通过该控件建立应用程序与SQL Server 数据库SDB之间的连接，将其ConunandType属性设置为2（adCmdTable），并将其RecordSource属性设置为学生表“s” • 在窗体上添加一个ADO数据控件Adodc2，通过该控件建立应用程序与SQL Server数据库S之间的连接。该控件的 ConunandType和Recordsource属性留到运行时设置。 • 将Adodcl和Adodc2控件的Visible属性设置为False，将它们隐藏起来。 • 在窗体上添加三个Lable控件和一个Line控件。 输入相应的名称。 • 在窗体上添加一个DataList控件，其名称为DataList1，将其Rowsource属性设置为Adodc1，将其ListField属性设置为“SN”，这是基表中的一个字段名称。 • 在窗体上添加一个DataList控件，其名称为DataList2。该控件的Rowsource和ListFeld属性留到运行时设置。

24. 3.2.4.2 步骤 • 在窗体上双击DataListl控件，在代码窗口中编写下面的事件过程： Dim srs As String '设置Adodc2的命令类型为2(adCmdText)，以便向服务器发送SQL命令 Adodc2.CommandType = adCmdText '输入SQL命令 srs = "SELECT RTRIM(CN)+':'+ CONVERT(VARCHAR(3),GRADE) AS SS" srs = srs & vbCrLf & "FROM S JOIN SC ON S.SNO = SC.SNO JOIN C ON SC.CNO = C.CNO" srs = srs & vbCrLf & "WHERE SN = '" & DataList1.Text & "'" '设置ADO数据控件的记录源 Adodc2.RecordSource = srs '设置数据绑定控件DataList2的记录源 Set DataList2.RowSource = Adodc2 '通过执行Refresh方法，使该记录源生效 Adodc2.Refresh '设置数据绑定控件DataList2的填充字段 DataList2.ListField = "SS" • 按F5键，运行程序，并从“学生姓名”列表中选择一个学生。此时，在“成绩”列表中显示该生各门功课的成绩（图正互－28）。

25. 4 使用ADO对象访问SQL Server数据库 Visual Basic提供了ActiveX数据对象(ADO)、远程数据对象(RDO)和数据访问对象(DAO)三种数据访问接口。ADO是首选的数据访问接口。 步骤： • 引用ADO对象库 • 使用Connection对象 • 使用Recordset对象 • 使用Command对象

26. 4.1 使用ADO的Connection 对象的集合、方法和属性 使用 Connection 对象的集合、方法和属性执行下列操作： • 在打开连接前使用 ConnectionString、ConnectionTimeout 和 Mode 属性对连接进行配置 • 设置 CursorLocation 属性以便调用支持批更新的“客户端游标提供者”。 • 使用 DefaultDatabase 属性设置连接的默认数据库。使用 IsolationLevel 属性为在连接上打开的事务设置隔离级别。 • 使用 Provider 属性指定 OLE DB 提供者。使用 Open 方法建立到数据源的物理连接。使用 Close 方法将其切断。 • 使用 Execute 方法执行对连接的命令，并使用 CommandTimeout 属性对执行进行配置。

27. 4.1.1使用Connection 对象 1. 建立Connection 对象 Dim cn As New ADODB.Connection 或 Dim cn As ADODB.Connection Set cn = New ADODB.Connection 2.使用Connection对象建立SQL Server数据库连接 cn.Provide = “SQLOLEDB”| Cn.Provide = “MSDASQL” Connection.Open ConnectionString,UserID,Password 注：MSDASQL用于ODBC数据源 3.使用Connection对象处理数据 Connection.Execute CommandText 无返回结果 或 Set Recordset = Connection.Execute(CommandText )

28. 4.1.2 实例 • 步骤 • 启动VB集成开发环境，选择“工程”->“引用”命令，在对话框中选择“Microsoft AxtiveX Data Objects2.5” Private Sub Form_Load() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim s As String Dim sSql As String s = "Driver = {SQL Server};Server=BJFU-WUBG;UID=sa;PWD=12345;Database=SDB" '使用SQL Server的OLE DB Provider for SQL Server cn.Provider = "SQLOLEDB" cn.ConnectionString = s '建立与SQL Server数据库的连接 cn.Open sSql = "SELECT * FROM s" '用Execute方法执行SQL语句，并产生一个记录对象，用rs来引用该对象 Set rs = cn.Execute(sSql) While Not rs.EOF List1.AddItem rs("sno") & Space(3) & rs("sn") & Space(3) & rs("sex") & Space(3) & rs("age") rs.MoveNext Wend cn.Close Set cn = Nothing End Sub

29. 4.2 使用ADO的Recordset对象 1. 建立记录集 ‘声明Recordset对象变量 Dim rs As New ADODB.Recordset ‘执行Recordset对象的Open方法 Recordset.Open Source, ActiveConnection, CursorType,LockType 例如： s = "Driver = {SQL Server};Server=BJFU-WUBG;UID=sa;PWD=12345;Database=SDB" cn.Provider = "SQLOLEDB" cn.Open s sSql = "SELECT * FROM s" rs.Open sSql, cn cn.Open s rs.Open "s", cn cn.ConnectionString = s cn.Open sSql = "SELECT * FROM s" Set rs = cn.Execute(sSql)

30. 4.2.1 使用ADO的Recordset对象 • 2. 访问字段值 Print rs.Fields(“字段名”) Print rs.Fields(“字段名”).Value Print rs.Fields(“字段名的列号”) Print rs.Fields(“字段名的列号”).Value • 3.设置游标类型 • 4.设置封锁类型 • 5.记录导航

31. 4.3 使用ADO的Command对象 • 步骤 1.设置CommandType属性指定命令类型 2.设置CommandText属性定义命令的可执行文本 3.设置ActiveConnection属性，使打开的连接与Command对象关联 4.通过Parameter对象和Parameter集合定义参数化查询或存储过程参数 5.使用CommandTimeout属性设置提供者等待命令执行的秒数 6.使用Execute方法执行命令，并在适当的时候返回Recordset对象 将Command对象与SQL语句结合起来，可以很方便地完成记录的检索、添加、修改和删除等任务。