使用VB.NET与SQLServer2000进行数据库开发实战

server2000

5星 · 超过95%的资源需积分: 0 6 浏览量更新于2024-07-24 收藏 10.6MB PDF 举报

"这篇文档是关于使用VB.NET与SQL Server 2000进行数据库开发的教程，涵盖了从基础的数据库连接到高级的T-SQL编程、视图、存储过程、用户定义函数和触发器的使用。" 在VB.NET与SQL Server 2000的集成开发中，开发者可以利用强大的ADO.NET框架来实现高效的数据访问和管理。ADO.NET是.NET Framework的一部分，它提供了数据访问组件，如DataSet、DataTable、DataAdapter和Command对象，这些组件用于在应用程序和数据库之间交换数据。 1. **Visual Studio .NET 和 VB.NET IDE**: 开发环境Visual Studio .NET为VB.NET程序员提供了一个集成的开发界面，支持代码编写、调试、设计和数据库集成。开发者可以通过IDE中的Server Explorer直接连接到SQL Server 2000，创建和管理数据库对象。 2. **ADO.NET能力概述**: ADO.NET的核心组件包括Connection对象（用于建立数据库连接）、Command对象（执行SQL语句）、DataReader（用于快速读取数据流）和DataAdapter（在内存中创建DataSet的副本）。DataSet提供了一种断开连接的数据处理方式，允许在不保持与数据库的持续连接的情况下操作数据。 3. **查询分析器**: Query Analyzer是SQL Server 2000提供的一个工具，用于编写、执行和优化SQL查询。开发者可以使用它来测试T-SQL代码并查看结果。 4. **表格和数据类型**: 数据库设计的基础是表格和数据类型的选择。了解SQL Server 2000支持的数据类型，如INT、VARCHAR、DATE等，是创建有效表结构的关键。此外，脚本表格可以帮助自动化数据库的创建和维护。 5. **T-SQL编程**: T-SQL（Transact-SQL）是SQL Server的扩展，用于执行数据操作、查询和事务控制。基础的T-SQL包括SELECT、INSERT、UPDATE和DELETE语句，而进阶的特性包括聚合函数（如SUM、COUNT）、分组（GROUP BY）、日期处理和联接（JOIN）。 6. **视图和存储过程**: 视图是虚拟表，基于一个或多个表的查询结果。它们可以简化复杂查询，提供安全性，并用于远程和异构源的数据访问。存储过程是预编译的T-SQL代码集合，可执行复杂的业务逻辑，支持参数化，可以提高性能并减少网络流量。 7. **用户定义函数和触发器**: 用户定义函数（UDF）允许创建自定义的计算功能，分为标量UDF（返回单个值）和表值UDF（返回数据集）。触发器则是在特定数据库操作（如INSERT、UPDATE、DELETE）前后自动执行的T-SQL代码，用于实现复杂的业务规则和数据完整性。这个教程将引导开发者通过VB.NET和SQL Server 2000实现全面的数据库应用程序开发，从简单的数据访问到复杂的业务逻辑处理。通过掌握这些知识，开发者能够构建高效、可靠和可扩展的数据库系统。

N ot e

Not all the project template types in Table 1-1 are available

with the non-Enterprise (or Standard) editions of Visual

Studio .NET. In addition to the em pty projects, the Standard

editions make available the Windows Application, ASP.NET

Web Application, ASP.NET Web Service, and Console

Application templates.

7DEOH9LVXDO%DVLF1(73URMHFW7HPSODWH7\SHV

7HPSODWH1DPH &UHDWHV$

Windows

Application

Windows application with a form

Class Library

Windows application suitable for a library of classes without a

form

Windows Control

Library

Proj ect for developing custom reusable form controls for

Windows applications

ASP.NET Web

Application

Web application on a Web server

ASP.NET Web

Service

XML Web service on a Web server

Web Control Library

Proj ect for developing custom reusable controls for Web

applications

Console Application

Command line application that operates in an MS-DOS–style

window (the Console)

Windows Service Windows service, formerly NT service, application that runs

in the background without its own custom user interface

Em pty Project Local project with no custom style

Em pty Web Project Web project with no custom style

New Project I n

Existing Folder

Blank project in an existing folder

There are two main categories of templates: Web projects and local proj ects. Web

proj ects permit a browser to serve as the client for a proj ect. Web proj ects are

optim ized for form processing on the Web server. Local projects offer custom

form user interfaces with the capability of processing on a local workstation. Local

proj ects can provide richer environm ents more conducive to client-side

programm ing, but local projects don’t offer the wide accessibility of solutions

running from a Web server.

Crea t ing a nd Running a Console Applica t ion

When you select a Console Application tem plate and click OK to launch a new

proj ect, Visual St udio .NET responds by opening a project with a blank module. In

addition to the Module window, Visual Studio displays Solution Explorer and the

Properties window. You can enter code directly into the Module window, which

appears as a tab that you can select alternately with the Start Page. Figure 1-2

shows a code sample in the Main subroutine that prom pts for a first and second

nam e before com bining them and displaying them in the Console (the com puter’s

monit or). The code is also available as MyNam eI sFrom Console in the Chapter 1

folder on the com panion CD for this book. Although Visual Basic developers didn’t

previously have Console applications routinely available, this sample should be

on the Start Page for recent solutions. I f you start Visual St udio .NET and the

solution you want to open doesn’t appear on the Projects tab of the Start Page,

you can also open the solution by clicking Open Project. I n the Open Project

dialog box, choose the file with the .sln extension and the solution’s nam e

(MyNam eIsFromConsole). A solution can contain just one .sln file, but it can

contain m ultiple projects.

You also can run the solution and open t he Console window directly from

Windows Explorer without using Visual Studio .NET. Open t he bin subdirectory

within the directory containing the assembly folder for the solution. Then double-

click the MyNam eI sFrom Console.exe file. This opens the Console window with the

prom pt for a first name.

An Overview of AD O.N ET Ca pabilit ies

ADO.NET encapsulates the data access and data manipulation for the .NET

Fram ework. This section gives you an overview of t he topic that equips you for a

starter sam ple in the next section. Microsoft chose the nam e ADO.NET for t he

.NET Framework data access com ponent to indicate its association with the earlier

ADO t echnology for dat a access. While there are som e similarities in syntax

between ADO.NET and ADO (particularly for connection strings), m any will find

the differences are m ore obvious than the sim ilarities. These differences

substantially upgrade ADO.NET over ADO in two key areas— scalability and XML

(Extensible Markup Language) int eroperability. As a result, you will be able to

create database applications with ADO.NET that serve more users and share m ore

data than you did with ADO. See Chapter 10 for a more intensive examination of

ADO.NET. Chapter 12 explicitly explores interoperability between ADO.NET and

XML.

.NET Da t a Provider Types

Your .NET Fram ework solutions require .NET dat a providers to connect to data

sources. These providers are different from t hose used with ADO, but there are

distinct similarities in some of the ways you use them . With .NET data providers,

your solutions can connect, read, and execute com m ands against data sources.

The .NET providers also offer selected other functions, such as the m anagem ent

of input and output param eters, security, transactions, and database server

errors.

Visual St udio .NET ships with two .NET data providers— the SQL Server .NET data

provider and the OLE DB .NET data provider. In addition, you can download an

ODBC .NET data provider from the Microsoft MSDN download site

(http: / / m sdn.m icrosoft.com / downloads/ default .asp).

N ot e

As I write this chapter, the ODBC .NET data provider just becam e

available with the rollout of the shipping version of Visual Studio

.NET. You can download it from

http: / / msdn.m icrosoft.com/ downloads/ default.asp?url= / downloads/ s

ample.asp?url= / msdn-files/ 027/ 001/ 668/ msdncompositedoc.xml.

The URLs for resources som etimes change. You can always search

for the ODBC .NET data provider at the MSDN download site to

obtain its current download location.

The three providers taken together offer fast, highly focused access to selected

data sources as well as general access to a wide range of possible data sources.

The SQL Server .NET data provider is optim ized for SQL Server 7.0 and SQL

Server 2000. This data provider connects directly to a SQL Server instance.

The OLE DB .NET data provider connects to OLE DB data sources t hrough two

intermediate layers— the OLE DB Service Com ponent and the classic OLE DB

provider introduced along wit h ADO. The OLE DB Service Component manages

connection pooling and transaction services. The classic OLE DB provider, in turn,

directly connects to a database server. Microsoft explicitly tested the OLE DB .NET

data provider with SQL Server, Oracle, and Jet 4.0 databases. Use the OLE DB

.NET data provider to connect t o the SQL Server 6.5 version and earlier ones.

This provider is also good for connecting to your Microsoft Access solutions based

on the Jet 4.0 engine.

The OLE DB .NET data provider definitely doesn’t work with the OLE DB provider

for ODBC data sources (MSDASQL). Because the .NET OLE DB data provider

doesn’t connect to ODBC data sources, you require t he ODBC .NET data provider

for connecting to ODBC data sources from your .NET Fram ework solutions.

There are four m ain .NET data provider classes for interacting with a rem ote data

source. The nam es of these classes change slightly for each type of provider, but

each .NET data provider has the same four kinds of classes. The names for the

SQL Server .NET data provider classes for interacting with SQL Server instances

are SqlConnect ion, SqlCom m and, SqlDataReader, and SqlDat aAdapt er. You can

use t he SqlDat aReader class for read-only applications from a SQL Server data

source. Two especially convenient ways to display results with a SqlDataReader

class are in a m essage box or the Visual Studio .NET Output window. The

SqlDat aAdapt er class acts as a bridge between a remote SQL Server data source

and a DataSet class instance inside a Visual Basic .NET solution.

A data set in a Visual Studio solution is a fifth type of ADO.NET class. A data set

can contain m ultiple tables. A sixt h ADO.NET class is the Dat aView class, which

acts like a view based on a table within a Dat aSet object. Windows Forms in

Visual Basic .NET applications can bind only to tables within a DataSet object and

Dat aView objects. I exam ine the DataSet object later in this section. Chapter 10

includes a syst em atic summ ary of all six ADO.NET classes that reviews selected

properties and methods of each class. The overview of ADO.NET classes in

Chapter 10 is support ed by numerous code samples that illustrate how to

manipulate instances of the classes programm atically.

N ot e

In order to use abbreviated names, such as those listed in

this section for the SQL Server .NET data provider class

instances, your application needs a reference to the SqlClient

namespace. You can create such a reference with an

I m ports

System .Data.SqlClient statement just before a Module

declaration.

SqlConne ction Class

An instance of the SqlConnect ion class can interface directly with a SQL Server

data source. Use a constructor statem ent to instantiat e a SqlConnect ion object

from the SqlConnect ion class. The const ruct or st at em ent is a new type of sy-ntax

for .NET Framework solutions. This type of statem ent permits you to declare,

instantiat e, and pass startup param eters to an object based on a class. With the

SqlConnect ion constructor statem ent, you can specify a connection string as an

argum ent for the constructor statem ent. Alternat ively, you can assign the

connection string to the SqlConnect ion object after its instantiation with a

property assignm ent statement for the Connect ionString property. The following

line shows the syntax to instantiate a new SqlConnect ion object, My SQLCnn1,

with a connection string designating integrated security to the mydb database on

the myserver instance of SQL Server. You don’t have to explicitly indicat e a

provider because the constructor statem ent reveals the type of provider through

its reference to the SqlConnect ion class.

Dim MySQLCnn1 As New _

SqlConnection(“Integrated Security=SSPI;" & _

"Data Source=myserver;Initial Catalog=mydb")

After instantiating a SqlConnect ion object, you need to invoke its Open method

before the object can link another object based on one of the other SQL Server

.NET data provider classes, such as SqlCom m and, SqlDat aAdapter, or

SqlDat aReader, to a SQL Server instance. I nvoke the Close method to recover the

resources for a SqlConnect ion obj ect when your solution no longer needs it. The

Close method rolls back any pending transactions and releases t he connection to

the connection pool. The Dispose m ethod is also available for rem oving

connections, but it invokes the Close method and performs other .NET

adm inistrative functions. Microsoft recom m ends the Close m ethod for removing a

connection. Unclosed connections aren’t returned to the connection pool.

SqlCom m and a nd SqlDa t aReade r Cla sses

One way to put a connection to use is to em ploy it along wit h the SqlCom mand

and SqlDat aReader objects. A SqlDat aReader object can m aintain an open

forward-only, read-only connection with a SQL Server database. While the

SqlDat aReader using a SqlConnect ion object is open, you cannot use the

SqlConnect ion object for any other purpose except to close the connection.

Closing a SqlDat aReader object releases its associated SqlConnect ion object for

other uses. The SqlDataReader class doesn’t have a constructor statement. You

declare t he SqlDat aReader object with a Dim statement and assign a result set

from a SqlCom m and object to a SqlDat aReader with t he ExecuteReader method

of the SqlCom m and obj ect. Finally, invoke the SqlDataReader object Read

method t o open a row from the result set in t he SqlDat aReader.

The SqlCom m and object can serve multiple functions, including processing a T-

SQL statem ent against a connection. When used in this fashion, the SqlCom m and

can take two argum ents. The first can be a T-SQL data access statem ent, such as

SELECT * FROM MyTable . The second SqlCom m and argum ent designates the

source connection for the T-SQL statem ent. For exam ple, you can use the nam e

of a SqlConnect ion object, such as MySQLCnn1.

Figure 1-3 shows the route from a SQL Server data source to a SqlDataReader

object. Although the SqlConnection and SqlCom m and objects support two-way

interaction with a data source, the SqlDataReader object allows read-only access

to the result set from the T-SQL statem ent serving as an argument for a

SqlCom m and constructor. Because a SqlDat aReader object cannot specify its own

data source, a SqlDat aReader object m ust link to a SqlConnect ion object through

an intermediate SqlCom m and object.

Figure 1 - 3 . A sch e m at ic illu st rating t he r ou t e b y w h ich a SqlDataReade r

obj e ct ret urns values t o an applica t ion .

剩余529页未读，继续阅读

expeller

粉丝: 0
资源: 1

使用VB.NET与SQLServer2000进行数据库开发实战

C# 访问sqlserver工具类

自己用vb.net写的一个SQLServer的SQLHelper类

财务管理系统SQL+VB.NET

sql+vb.net系统设计

vb.net 连接sql servers

关于VB.Net对SQL Server连接，及基本操作

vb.net codesoft6

vb.net 连接sql数据库显示在datagridviewX控件上

vb.net中是否可以使用sql语句操作数组

vb.net学习笔记

最新资源