MASTERING SQL SERVER 2000- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
40
lượt xem
4
download

MASTERING SQL SERVER 2000- P15

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p15', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P15

  1. 720 CHAPTER 18 • SECURITY AND SQL SERVER 2000 you have a well-designed security plan that incorporates growth, managing your user base can be a painless task. To limit administrative access to SQL Server at the server level, you learned that you can add users to a fixed server role. For limiting access in a specific database, you can add users to a database role, and if one of the fixed database roles is not to your liking, you can create your own. You can even go so far as to limit access to specific applications by creating an application role. Each database in SQL Server 2000 has its own independent permissions. You looked at the two types of user permissions: statement permissions, which are used to create or change the data structure, and object permissions, which manipulate data. Remember that statement permissions cannot be granted to other users. The next section in this chapter described the database hierarchy. You looked at the permissions available to the most powerful user—the sa—down through the lower-level database users. You then learned about chains of ownership. These are created when you grant permissions to others on objects you own. Adding more users who create dependent objects creates broken ownership chains, which can become complex and tricky to work with. You learned how to predict the permissions available to users at different locations within these ownership chains. You also learned that to avoid the broken ownership chains, you can add your users to either the db_owner or the db_ddladmin database role and have your users create objects as the DBO. Permissions can be granted to database users as well as database roles. When a user is added to a role, they inherit the permissions of the role, including the Public role, of which everyone is a member. The only exception is when the user has been denied permission, because Deny takes precedence over any other right, no matter the level at which the permission was granted. We then looked at remote and linked servers, and at how security needs to be set up to make remote queries work. We finished with a look at n-tier security and applications. Now that you have a better understanding of security and administration in gen- eral you are ready to start learning about programming with SQL Server. Let’s start in the next chapter by learning about ADO.
  2. PA R T V Development with SQL Server LEARN TO: • Use ADO • Use SQL-DMO • Use SQL Namespace • Use Data Transformation Services • Use the Web Assistant Wizard • Integrate SQL Server with Internet Information Server
  3. This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  4. CHAPTER 19 ADO and SQL Server F E AT U R I N G : The ADO Object Model 724 Understanding Cursors 728 Sample ADO Code 732 Other ADO Libraries 756 Summary 760
  5. I n most applications involving SQL Server, not all of the development is done on the server itself. This is the essence of client-server computing: Work is parti- tioned between a central server and distributed clients. To view and modify server-side data from a client application, one uses a client data-access library. Over the years, Microsoft has released a number of client data-access libraries that can use SQL Server data, including DB-Lib, Data Access Objects (DAO), and Remote Data Objects (RDO). Although all of these libraries are still in use, they’re no longer undergoing active development. Instead, Microsoft recommends that all new client applications use ActiveX Data Objects (ADO) to interact with the server. ADO is the only client data-access library that we’re going to cover in this book. Even if you’ve used another library for that purpose in the past, you should consider migrating to ADO to take advantage of current advances in the state of the art. In this chapter, we’ll start by describing the ADO object model and then take a look at what you can do with ADO. We’ll close with a brief section on ADOX, an ADO extension designed to help you work with schema information. ADO provides an object model atop the OLE DB interface, which is the low-level “plumbing” that SQL Server uses between its own components. Because of this inti- mate connection, ADO is a good choice for working with data stored in SQL Server. The ADO Object Model Figure 19.1 shows the ADO object model for ADO 2.6, the version that ships with SQL Server 2000. An object model lists the various objects that a library contains and shows their relationships. As you can see, the ADO object model is fairly simple. FIGURE 19.1 The ADO object Connection model Command Recordset Errors Parameters Record Fields Fields Stream
  6. THE ADO OBJECT MODEL 725 In addition to the objects shown in Figure 19.1, the Connection, Command, Parameter, Recordset, Record, and Field objects each have a Properties collection of Property objects. This enables your code to easily enumerate the properties of these objects. Objects shown in Figure 19.1 with multiple boxes are collections. For example, the Command object contains a Parameters collection containing individual Parameter objects. NOTE Despite the simplicity of the ADO object model, ADO offers quite a bit of complex- ity in its operations, because there are many alternatives for performing basic operations, as well as lots of optional arguments. In this chapter, we’ll provide the basics of ADO to get you started. For more details, refer to the Microsoft Data Access Components SDK. You can download a copy of this SDK, which has the complete documentation for all ADO objects, from the Microsoft Universal Data Access Web site at http://www microsoft com/data. PA R T V Understanding Objects Before we dig into the objects offered by ADO, let’s step back a bit and talk about the Development with concept of an object in programming. In software development, an object represents a package of functionality provided for client programs to use. Usually an object repre- sents some “thing” within a particular piece of software. Objects have methods (activ- SQL Server ities they can perform), properties (characteristics that describe the objects), and events (occurrences that can cause the object to invoke your code) that are set by the provider of the object. As an application developer, you can use those methods and properties to interact with the original product. For example, ADO includes an object called a Recordset. This object represents a set of records (for example, the results of a query) from a data provider. A Recordset object can be used to represent any set of records. The Recordset object has methods, such as MoveFirst (which positions an internal pointer to the first record in the Recordset) and MoveLast (which positions an internal pointer to the last record in the Recordset). It also has properties, such as RecordCount (the number of records in the Recordset) and EOF (a Boolean property that indicates the last record of the Recordset has been retrieved). The Recordset object also has events, such as FetchComplete, which occurs when all of the records from an asynchronous operation are available in the Recordset. Objects can be arranged in collections, which are groups of similar objects. For example, in ADO there is a Parameters collection of Parameter objects. You can use a
  7. 726 CHAPTER 19 • ADO AND SQL SERVER collection to view each object in turn of a similar group. This is called iterating through the collection. Objects can also contain other objects. For example, the ADO Recordset object contains a collection of Field objects, each of which represents one of the individual fields in a record in the Recordset. Objects provide an abstract view of the underlying software. It’s unlikely that there’s actually a data structure within SQL Server that you could point to and say, “This is a Recordset.” By manipulating Recordsets in your code, though, you can access many of the abilities of SQL Server to retrieve and modify data. The Recordset object provides a convenient abstraction for the underlying functionality of storing and modifying data. In the remainder of this section, we’ll discuss the objects that the ADO object model provides. We’ll keep the discussion on an abstract level, without presenting all of the methods and properties of each object. SQL Server Books Online includes an exhaustive list of these methods and properties, as well as those of the other object models that can be used with SQL Server. Connection and Error At the top of the ADO hierarchy, you’ll find the Connection object, which is associ- ated with an Errors collection. Neither of these objects provides a direct connection to data, but they’re both very important in working with other ADO objects. The Connection object represents an open connection to an OLE DB data source. You can create a Connection object and use it to create other objects further down the ADO object hierarchy. However, if you need only a single Recordset object from a par- ticular Connection, it’s probably more efficient to just create the Recordset directly, which will create a Connection object implicitly. You should reserve explicitly creat- ing actual Connection objects for situations where you’ll need to perform multiple, diverse operations on the connection. An Error object represents a single error. Because one data-access operation can generate multiple errors, Error objects are contained in an Errors collection. If the last operation succeeded, this collection will be empty. Otherwise, you can use the For Each operator to examine each Error in turn. Command and Parameter The Command and Parameter objects are the basic query-building objects of ADO. You can use them in various combinations to represent tables, SQL statements, or stored procedures. You can use Command objects both for commands that return
  8. THE ADO OBJECT MODEL 727 data and for commands that instruct SQL Server to do something, such as action queries. Think of a Command object as a single instruction to SQL Server to produce or alter data. The easiest way to use a Command object is to create an independent Command object, set its other properties, and then set its ActiveConnection property to a valid connection string. This will cause ADO to create an implicit Connection object for use by this Command only. However, if you’re going to execute multiple Commands on a single Connection, you should avoid this technique, because it will create a separate Connection object for each Command. Instead, you can set the ActiveConnection property to an existing Connection object. A Parameter object represents a single parameter for a Command object. This might be a runtime parameter in a SQL query, or an input or output parameter in a stored procedure. If you know the properties of a particular Parameter, you can use the CreateParameter method to make appropriate Parameter objects for a Command object, which allows you to initialize parameters without any server-side processing. PA R T Otherwise, you must call the Refresh method on the Command object’s Parameters V collection to retrieve parameter information from the server, a resource-intensive operation. Development with Recordset and Field The Recordset and Field objects are the actual data-containing objects in ADO. SQL Server A Recordset object represents a set of records retrieved from SQL Server. Because this is the object that allows you to directly retrieve data, it’s indispensable to ADO processing. ADO allows you to open a Recordset object directly, or to create one from a Connection or Command object. As you’ll see later in the chapter, Recordsets have a variety of properties and behaviors depending on how they’re created. A Field object represents a single column of data in a Recordset. Once you’ve retrieved a Recordset, you’ll usually work with the Fields collection to read the data in the Recordset. However, since the Fields collection is the default property of the Recordset object, you won’t often see its name in your code. For example, if you’re working in Visual Basic or a VBA host application, the following two lines of code produce an identical result: Recordset.Fields(0).Value Recordset(0)
  9. 728 CHAPTER 19 • ADO AND SQL SERVER Properties The Property object is the building block of the other ADO objects. That is, properties describe the other objects. Although you can iterate through the Properties collection of ADO objects, there’s usually not any reason to do so unless you’re writing special- ized tools to manipulate ADO code. Record and Stream For completeness, you should also know about two other objects introduced in ADO 2.5, although these objects are not useful in working with SQL Server data. The Record object is a dual-purpose object. It can represent a row in a Recordset. It can also represent a file or folder in a file system. However, it’s important to realize that these are not distinct features of the Record object. Rather, the Record object is designed to represent a row in a Recordset when the underlying OLE DB provider nat- urally supports a hierarchical data store. For example, Record objects can be used with providers that supply information from file systems or e-mail storage. Record objects can’t be used with providers that supply information from standard relational data- bases (even if there’s a hierarchy within the database). The Stream object represents binary data associated with a Record object. For example, if you have a Record object representing a file in a file system, its associated Stream object would represent the binary data in that file. Because SQL Server is a relational database, it doesn’t support Record or Stream objects. Understanding Cursors You learned about T-SQL cursors in Chapter 8. A cursor, you’ll recall, is a set of records along with a pointer that identifies one of these records as the current record. ADO also supports cursors, in the form of the Recordset object. When you open a Recordset object to contain a set of records, ADO identifies a particular record as the current record. Thus, if you talk of cursors in an ADO context, you’re normally talking about Recordsets. Unlike T-SQL cursors, though, ADO cursors can have a variety of different behav- iors, depending on the properties you set for the Recordset object. In this section, we’ll discuss the three key properties that control ADO cursor behavior: • CursorLocation • CursorType • LockType
  10. UNDERSTANDING CURSORS 729 CursorLocation The CursorLocation property can be set to either adUseServer, for server-side cursors, or adUseClient, for client-side cursors. A cursor is a set of records in memory, and of course some software has to be responsible for keeping track of this set of records. Server-side cursors are maintained by SQL Server using the same native cursors that you met in Chapter 8. Client-side cursors are maintained by the Microsoft Cursor Ser- vice for OLE DB, which attempts to level the playing field by supplying capabilities that are lacking in some servers. If no CursorLocation is specified, a server-side cursor is the default. Just because SQL Server supports server-side cursors doesn’t mean you have to use them. Some functionality is available only in client-side cursors—for example, re-sorting Recordsets or using an index to find records. If you need these capabili- ties, you should use client-side cursors. Otherwise, you may find that server-side cursors provide better performance. PA R T V CursorType The CursorType parameter further specifies the desired behavior of the Recordset object. You can specify one of four constants: Development with • To open a dynamic Recordset, use adOpenDynamic. A dynamic Recordset allows all types of movement through the Recordset and keeps you up-to-date SQL Server with changes made by other users. • To open a keyset Recordset, use adOpenKeyset. A keyset Recordset functions like a dynamic Recordset, except that you won’t see new records added or records deleted by other users. • To open a static cursor, use adOpenStatic. A static Recordset does not show you any changes made by other users while the Recordset is open and is therefore most useful for reporting or other applications that don’t need to be kept com- pletely up-to-date. • Finally, to open a forward-only cursor, use adOpenForwardOnly. A forward- only cursor is identical to a static cursor, except that you can only move for- ward in the Recordset to go to a different record. This offers the fastest performance of any of the cursor types, at the expense of flexibility. Some- times you’ll see a forward-only, read-only cursor called a firehose cursor.
  11. 730 CHAPTER 19 • ADO AND SQL SERVER NOTE The forward-only Recordset is more flexible than you might think at first. In addi- tion to using the MoveNext method, you can also use the Move method to skip intervening records, as long as you’re moving forward. A forward-only Recordset also supports the MoveFirst method, although this seems contradictory. Be aware, though, that this may be an expensive operation, because it might force the provider to close and reopen the Recordset. In general, if you stick to a cursor type that has no more functionality than you need in your application, you’ll get the best possible performance. If you don’t specify a cursor type, ADO defaults to the fastest type, which is a forward-only cursor. LockType Finally, you can use the LockType parameter to specify the record-locking behavior that will be used for editing operations. Here again you have four choices: • adLockReadOnly, for Recordsets that cannot be edited • adLockPessimistic, for pessimistic locking (record locks are taken for the dura- tion of all editing operations) • adLockOptimistic, for optimistic locking (record locks are taken only while data is being updated) • adLockBatchOptimistic, for Recordsets that will use the UpdateBatch method to update multiple records in a single operation If you don’t specify a lock type, ADO defaults to the fastest type, which is a read- only Recordset. WARN ING The default Recordset in ADO is server-side, forward-only, and read-only. If you want to move through records at random or edit records, you must specify the cursor type and lock type to use. Graceful Degradation Just to make things more interesting, what you ask for isn’t always what you get. Not every provider supports every possible combination of these parameters. In almost every case, though, you’ll get something close to what you asked for. The
  12. UNDERSTANDING CURSORS 731 ADO term for this process is graceful degradation. Rather than refuse to create a Recordset, ADO will always return some kind of Recordset. However, for example, if you try to open a client-side, static, pessimistic Recordset on a SQL Server data source, what you actually get will be a client-side, static, batch optimistic Recordset. If you aren’t sure what you’re getting, you need to check the values of the CursorType, CursorLocation, and LockType properties of the Recordset object after calling its Open method to see what ADO delivered. TI P You should also realize that different Recordsets can have very different perfor- mance implications. In general, the Recordsets with fewer capabilities are faster, but you’ll want to test this in your own application to determine the best type of Recordset to open. Table 19.1 shows the possible options you can choose when opening a Recordset PA R T using SQL Server data and the actual Recordsets that are delivered by ADO. V TABLE 19.1: GRACEFUL DEGRADATION OF RECORDSETS Requested Delivered Identical? Development with Server-side, forward-only, read-only Server-side, forward-only, read-only Yes SQL Server Server-side, forward-only, pessimistic Server-side, forward-only, pessimistic Yes Server-side, forward-only, optimistic Server-side, forward-only, optimistic Yes Server-side, forward-only, batch Server-side, forward-only, batch Yes optimistic optimistic Server-side, keyset, read-only Server-side, keyset, read-only Yes Server-side, keyset, pessimistic Server-side, keyset, pessimistic Yes Server-side, keyset, optimistic Server-side, keyset, optimistic Yes Server-side, keyset, batch optimistic Server-side, keyset, batch optimistic Yes Server-side, dynamic, read-only Server-side, dynamic, read-only Yes Server-side, dynamic, pessimistic Server-side, dynamic, pessimistic Yes Server-side, dynamic, optimistic Server-side, dynamic, optimistic Yes Server-side, dynamic, batch optimistic Server-side, dynamic, batch optimistic Yes Server-side, static, read-only Server-side, static, read-only Yes Server-side, static, pessimistic Server-side, keyset, pessimistic No Server-side, static, optimistic Server-side, keyset, optimistic No Server-side, static, batch optimistic Server-side, keyset, batch optimistic No
  13. 732 CHAPTER 19 • ADO AND SQL SERVER TABLE 19.1: GRACEFUL DEGRADATION OF RECORDSETS (CONTINUED) Requested Delivered Identical? Client-side, forward-only, read-only Client-side, static, read-only No Client-side, forward-only, pessimistic Client-side, static, batch optimistic No Client-side, forward-only, optimistic Client-side, static, optimistic No Client-side, forward-only, batch Client-side, static, batch optimistic No optimistic Client-side, keyset, read-only Client-side, static, read-only No Client-side, keyset, pessimistic Client-side, static, batch optimistic No Client-side, keyset, optimistic Client-side, static, optimistic No Client-side, keyset, batch optimistic Client-side, static, batch optimistic No Client-side, dynamic, read-only Client-side, static, read-only No Client-side, dynamic, pessimistic Client-side, static, batch optimistic No Client-side, dynamic, optimistic Client-side, static, optimistic No Client-side, dynamic, batch optimistic Client-side, static, batch optimistic No Client-side, static, read-only Client-side, static, read-only Yes Client-side, static, pessimistic Client-side, static, batch optimistic No Client-side, static, optimistic Client-side, static, optimistic Yes Client-side, static, batch optimistic Client-side, static, batch optimistic Yes Sample ADO Code Understanding the objects supplied by ADO is an important part of grasping this technology, but it’s no substitute for actually using those objects. In the rest of this chapter, we’ll demonstrate a number of basic ADO techniques for retrieving and working with data. TI P We can’t hope to cover all of ADO in a single chapter. The definitive reference for this technology is the Microsoft Data Access Components Software Development Kit (MDAC SDK). You can get to the MDAC SDK online by going to the Microsoft Universal Data Access Web site (www.microsoft.com/data) and following the Documentation link.
  14. SAMPLE ADO CODE 733 Creating a Connection To do anything with ADO, you need to create a Connection object and use it to con- nect to the database in which you’re interested. In some cases, such as when opening a Recordset directly, you won’t need to explicitly create the Connection object. There’s always a Connection object involved, even if you don’t explicitly create it. To connect to a database, you use the Connection object’s ConnectionString prop- erty and Open method. The ConnectionString property holds an OLE DB connection string. Connection strings are a standardized method of describing where a database is and what information should be used when connecting to the database. The Open method takes some optional arguments: Connection.Open ConnectionString, UserID, Password, Options All four of these arguments are optional: • The ConnectionString argument can be used to supply a connection string when PA R T calling the Open method. In this case, you don’t need to set the Connection- String property in advance. V • The UserID argument specifies the username to use with the data source. • The Password argument specifies the password to use with the data source. Development with • The Options argument can be set to adConnectUnspecified (the default) for a synchronous connection or adAsyncConnect for an asynchronous connection. SQL Server Once the connection is made, either type performs the same. The difference is that an asynchronous connection lets other code in your client application con- tinue running while the connection is being made. Of course, to build a connection string, you need to understand from what it’s made up. The basic syntax of an OLE DB connection string is as follows: keyword=value;keyword=value;keyword=value… Table 19.2 shows the keywords that you can use in a SQL Server connection string. TABLE 19.2: OLE DB CONNECTION STRING KEYWORDS FOR SQL SERVER Keyword Value Comments Provider SQLOLEDB Must be specified. This tells OLE DB the type of database with which you want to connect. Data Source Name of the SQL Server Must be specified. You can also use the special value “(local)” if the SQL Server is on the computer where the client code will run.
  15. 734 CHAPTER 19 • ADO AND SQL SERVER TABLE 19.2: OLE DB CONNECTION STRING KEYWORDS FOR SQL SERVER (CONTINUED) Keyword Value Comments Server Name of the SQL Server An alternative to the Data Source keyword. Initial Catalog Name of the database Must be specified. Database Name of the database An alternative to the Initial Catalog keyword. User ID Username This applies to SQL Server Authentica- tion only. uid Username An alternative to the User ID keyword. Password Password This applies to SQL Server Authentica- tion only. pwd Password An alternative to the Password keyword. Trusted_Connection Yes or No Setting to Yes enables Windows NT Authentication. Integrated Security SSPI An alternative to Trusted Connection=Yes. Current Language Language name Sets the language to use with this client session. Must be a language that’s actually installed on the server. Application Name Application name Sets the client application name, which can be inspected in SQL Server Enterprise Manager. Workstation Workstation name Sets the workstation name, which can be inspected in SQL Server Enterprise Manager. NOTE In addition to the keywords listed in Table 19.2, there are several more that deal with network settings. In general, you won’t need to worry about these more advanced keywords. Now that you have all the pieces, it’s just a matter of putting them together. For the simplest possible case, consider connecting to a server on the same computer where you’re running the ADO code, using Windows NT Authentication (this is likely to be the case if you’re using the MSDE version of SQL Server, for example): Dim conLocal As ADODB.Connection Set conLocal = New ADODB.Connection
  16. SAMPLE ADO CODE 735 conLocal.ConnectionString = _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=pubs;Trusted_Connection=Yes” conLocal.Open Alternatively, you can save a line of code by including the connection string with the Open method: Dim conLocal As ADODB.Connection Set conLocal = New ADODB.Connection conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=pubs;Trusted_Connection=Yes” It really doesn’t matter which of these formats you use to open a connection; you should choose the one that makes it easier for you to remember what the code is PA R T doing. V NOTE We’re using Visual Basic for the examples in this chapter. Because ADO is a COM Development with server, you can use it from any COM-aware language, but we feel that Visual Basic is the most widely understood and the easiest to read even if you don’t know its precise syntax. To use ADO in Visual Basic, you need to use the Project ➢ References menu item to set a SQL Server reference to the current version of the Microsoft ActiveX Data Objects Library. Connecting to a SQL Server across the network using a SQL Server user ID and password is just as simple. For example, to connect with the Northwind database on a server named BIGREDBARN as a user named test with a password of test, you could use this code: Dim conNetwork As ADODB.Connection Set conNetwork = New ADODB.Connection conNetwork.Open _ “Provider=SQLOLEDB;Server=BIGREDBARN;” & _ “Database=Northwind;User ID=test;pwd=test” Debug.Print conNetwork.ConnectionString
  17. 736 CHAPTER 19 • ADO AND SQL SERVER Executing a SQL Statement Once you’ve created and opened a Connection object, you’re ready to work with your server. One of the easiest tasks to do via ADO is to execute a SQL statement. ADO pro- vides two methods for doing this, either of which can be used for executing SQL state- ments or stored procedures: • The Connection.Execute method • The Command.Execute method Using the Connection Object One way to execute SQL statements is to use the Execute method of the Connection object. This method takes one required and two optional arguments: Connection.Execute CommandText, RecordsAffected, Options The CommandText argument is required. This can be either a SQL statement or the name of a stored procedure. In this section we’ll use only stored procedures that do not return records; we’ll discuss stored procedures that return records later, when we talk about the Recordset object. The RecordsAffected argument is a variable (not a constant). If you choose to sup- ply this argument, it will be filled in by SQL Server with the number of records that the command altered. The Options argument can either specify how the CommandText should be inter- preted or supply options for executing it. Some of the values you can supply for Options are as follows: • adCmdUnknown (the default) indicates that ADO should figure out for itself whether the command is a SQL statement or a stored procedure. • adCmdText indicates that the command is a SQL statement. • adCmdStoredProc indicates that the command is a stored procedure. • adAsyncExecute tells ADO to execute the command asynchronously. • adExecuteNoRecords indicates that the command does not return any rows. You don’t have to specify this, but it does make the method more efficient to do so. As a first example, here’s code to execute a SQL statement directly. This particular statement will create a stored procedure in the local copy of the Northwind database: Dim conLocal As ADODB.Connection Dim lngRows As Long Set conLocal = New ADODB.Connection
  18. SAMPLE ADO CODE 737 conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=Northwind;Trusted_Connection=Yes” conLocal.Execute _ “CREATE PROC NewPrices AS UPDATE Products “ & _ “SET UnitPrice = UnitPrice * 1.1”, lngRows, _ adCmdText + adExecuteNoRecords Debug.Print lngRows If you run this code, you’ll find that the lngRows variable is set to –1. That’s ADO’s way of telling you that the command didn’t return any rows at all. If it had returned an empty Recordset, lngRows would be set to zero instead. Once you’ve run the above procedure, you now have a stored procedure that you PA R T can execute directly: Dim conLocal As ADODB.Connection V Dim lngRows As Long Set conLocal = New ADODB.Connection Development with conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ SQL Server “Database=Northwind;Trusted_Connection=Yes” conLocal.Execute _ “NewPrices”, lngRows, _ adCmdStoredProc+ adExecuteNoRecords Debug.Print lngRows On a stock copy of the Northwind database, this code will end up setting lngRows to 77, the number of rows in the Products table. The code will work just as well if you omit the extra information: Dim conLocal As ADODB.Connection Set conLocal = New ADODB.Connection conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=Northwind;Trusted_Connection=Yes”
  19. 738 CHAPTER 19 • ADO AND SQL SERVER conLocal.Execute _ “NewPrices” In this case, of course, you won’t get any feedback as to the number of rows changed by the Execute method. ADO offers one more interesting syntactical twist. You can treat a named statement (such as a stored procedure) as a method of the Connection object. An example will make this more clear: Dim conLocal As ADODB.Connection Set conLocal = New ADODB.Connection conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=Northwind;Trusted_Connection=Yes” conLocal.NewPrices Assuming that there is a stored procedure named NewPrices in the Northwind database, this bit of code will execute that stored procedure. Once again, there’s no return value to tell you how many rows were altered. Using the Command Object The Command object also has an Execute method with three optional arguments: Command.Execute RecordsAffected, Parameters, Options The RecordsAffected argument is a variable (not a constant). If you choose to supply this argument, it will be filled in by SQL Server with the number of records that the command altered. The Parameters argument can be used to hold a variant array of parameters to be passed to the command being executed on the server. The Options argument can either specify how the command should be interpreted or supply options for executing it. Some of the values you can supply for Options are as follows: • adCmdUnknown (the default) indicates that ADO should figure out for itself whether the command is a SQL statement or a stored procedure. • adCmdText indicates that the command is a SQL statement. • adCmdStoredProc indicates that the command is a stored procedure. • adAsyncExecute tells ADO to execute the command asynchronously. • adExecuteNoRecords indicates that the command does not return any rows. You don’t have to specify this, but it does make the method more efficient to do so.
  20. SAMPLE ADO CODE 739 As you can see, this is very close to the Execute method of the Connection object. However, using a separate Command object to execute SQL statements adds some important extra capabilities. First, you can reexecute the same statement without additional overhead. Second, you can use the Command object’s Parameters collec- tion to supply parameters to a SQL Server stored procedure. TIP Although you can use either the Parameters collection or an array of Parameters in the Execute method to pass parameters, we recommend that you always use the Parameters collection. This is because output parameters do not function properly if passed in an array. To use a Command object to execute a SQL statement, you must create and open a Connection object and then use the Command object’s ActiveConnection property to associate the Command to the Connection. You also need to set the text of the com- PA R T mand into the Command object’s CommandText property. For example, this code will execute a SQL statement against the Northwind database on the local server: V Dim conLocal As ADODB.Connection Dim cmdProc As ADODB.Command Development with Dim lngRows As Long Set conLocal = New ADODB.Connection SQL Server conLocal.Open _ “Provider=SQLOLEDB;Server=(local);” & _ “Database=Northwind;Trusted_Connection=Yes” Set cmdProc = New ADODB.Command Set cmdProc.ActiveConnection = conLocal cmdProc.CommandText = _ “CREATE PROC NewPrices2 AS UPDATE Products “ & _ “SET UnitPrice = UnitPrice/1.1” cmdProc.Execute lngRows, , _ adCmdText + adExecuteNoRecords Debug.Print lngRows Of course, you can also execute a stored procedure via a Command object by set- ting the CommandText property to the name of the stored procedure: Dim conLocal As ADODB.Connection Dim cmdProc As ADODB.Command
Đồng bộ tài khoản