When to use ADODB.Command Object in ADO Programming 
Author Message
 When to use ADODB.Command Object in ADO Programming

I want to know when to use ADODB.Command Object in ADO Programming? For
example, the following 2 approaches are working fine, but I don't know
what's the differences, and which approach should I use in ADO programming?
Also, I am confused which cursor type and lock type I should use, different
books use different types but don't have clear explainations.

Approach #1: don't use ADODB.Command object

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'step 1: open a database connection
cn.Open connectionString
'step 2: open a recordset
rs.Open sqlstatement, cn, adOpenKeyset, adLockReadonly
'step 3: able to access recordset fields
Print.Debug rs.Fields("field1")

Approach #2: use ADODB.Command object

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
'step 1: open a database connection
cn.Open connectionString
'step 2: set the command with SQL
Set cmd.ActiveConnection = conn
cmd.CommandText = sqlStatement
'step 3: open a recordset
rs.Open cmd, , adOpenKeyset, adLockReadonly
'step 4: able to access recordset fields
Print.Debug rs.Fields("field1")

Thanks!!
John



Mon, 03 Oct 2005 14:51:33 GMT  
 When to use ADODB.Command Object in ADO Programming
If you're creating recordsets it's mostly a matter of programming convenience;
but the command object tends to be less code for other kinds of database work.
For most apps you won't see a measurable performance difference.


Quote:
> I want to know when to use ADODB.Command Object in ADO Programming? For
> example, the following 2 approaches are working fine, but I don't know
> what's the differences, and which approach should I use in ADO programming?
> Also, I am confused which cursor type and lock type I should use, different
> books use different types but don't have clear explainations.

> Approach #1: don't use ADODB.Command object

> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: open a recordset
> rs.Open sqlstatement, cn, adOpenKeyset, adLockReadonly
> 'step 3: able to access recordset fields
> Print.Debug rs.Fields("field1")

> Approach #2: use ADODB.Command object

> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmd As New ADODB.Command
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: set the command with SQL
> Set cmd.ActiveConnection = conn
> cmd.CommandText = sqlStatement
> 'step 3: open a recordset
> rs.Open cmd, , adOpenKeyset, adLockReadonly
> 'step 4: able to access recordset fields
> Print.Debug rs.Fields("field1")

> Thanks!!
> John



Mon, 03 Oct 2005 16:28:32 GMT  
 When to use ADODB.Command Object in ADO Programming
There are actually several more ways to create a recordset besides these,
both the connection and command objects support the Execute method, plus
stored procedures in the connection's default database are mapped into the
connection object as methods.

Use a command object when you need to pass parameters to either a stored
procedure or a parameter query.  An example of a parameter query would be
"SELECT [name] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?",
passing the name of a table as a parameter.  Parameter queries will tend to
perform better, particularly if SQL Server is the db engine, and you're
using complex queries, because it's easier to cache the query plan for them.

Explicitly create a recordset object when you need to control the cursor
type, lock type, or you need to be able to disconnect the recordset.

If all you need is a read-only forward-scolling recordset, simply use the
connection object to execute a SQL statement, e.g.,

' assumes cn is an open connection object
Dim rs1, rs2
Set rs1 = cn.Execute("exec sp_lock")
Set rs2 = cn.Execute("SELECT * FROM MyTable")

Note however that once a connection has been used to create a read-only
forward-scolling (aka 'firehose') cursor, that connection can only be used
to create more firehoses, it must be destroyed and recreated before it will
open any other type of cursor.

-Mark


Quote:
> I want to know when to use ADODB.Command Object in ADO Programming? For
> example, the following 2 approaches are working fine, but I don't know
> what's the differences, and which approach should I use in ADO
programming?
> Also, I am confused which cursor type and lock type I should use,
different
> books use different types but don't have clear explainations.

> Approach #1: don't use ADODB.Command object

> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: open a recordset
> rs.Open sqlstatement, cn, adOpenKeyset, adLockReadonly
> 'step 3: able to access recordset fields
> Print.Debug rs.Fields("field1")

> Approach #2: use ADODB.Command object

> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim cmd As New ADODB.Command
> 'step 1: open a database connection
> cn.Open connectionString
> 'step 2: set the command with SQL
> Set cmd.ActiveConnection = conn
> cmd.CommandText = sqlStatement
> 'step 3: open a recordset
> rs.Open cmd, , adOpenKeyset, adLockReadonly
> 'step 4: able to access recordset fields
> Print.Debug rs.Fields("field1")

> Thanks!!
> John



Tue, 04 Oct 2005 15:43:38 GMT  
 When to use ADODB.Command Object in ADO Programming
Also, as an addition to what Mark just said, you could open the recordset
with

cn.Execute "exec sp_lock", rs1
cn.Execute "SELECT * FROM MyTable" , rs2



Quote:
> There are actually several more ways to create a recordset besides these,
> both the connection and command objects support the Execute method, plus
> stored procedures in the connection's default database are mapped into the
> connection object as methods.

> Use a command object when you need to pass parameters to either a stored
> procedure or a parameter query.  An example of a parameter query would be
> "SELECT [name] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?",
> passing the name of a table as a parameter.  Parameter queries will tend
to
> perform better, particularly if SQL Server is the db engine, and you're
> using complex queries, because it's easier to cache the query plan for
them.

> Explicitly create a recordset object when you need to control the cursor
> type, lock type, or you need to be able to disconnect the recordset.

> If all you need is a read-only forward-scolling recordset, simply use the
> connection object to execute a SQL statement, e.g.,

> ' assumes cn is an open connection object
> Dim rs1, rs2
> Set rs1 = cn.Execute("exec sp_lock")
> Set rs2 = cn.Execute("SELECT * FROM MyTable")

> Note however that once a connection has been used to create a read-only
> forward-scolling (aka 'firehose') cursor, that connection can only be used
> to create more firehoses, it must be destroyed and recreated before it
will
> open any other type of cursor.

> -Mark



> > I want to know when to use ADODB.Command Object in ADO Programming? For
> > example, the following 2 approaches are working fine, but I don't know
> > what's the differences, and which approach should I use in ADO
> programming?
> > Also, I am confused which cursor type and lock type I should use,
> different
> > books use different types but don't have clear explainations.

> > Approach #1: don't use ADODB.Command object

> > Dim cn As New ADODB.Connection
> > Dim rs As New ADODB.Recordset
> > 'step 1: open a database connection
> > cn.Open connectionString
> > 'step 2: open a recordset
> > rs.Open sqlstatement, cn, adOpenKeyset, adLockReadonly
> > 'step 3: able to access recordset fields
> > Print.Debug rs.Fields("field1")

> > Approach #2: use ADODB.Command object

> > Dim cn As New ADODB.Connection
> > Dim rs As New ADODB.Recordset
> > Dim cmd As New ADODB.Command
> > 'step 1: open a database connection
> > cn.Open connectionString
> > 'step 2: set the command with SQL
> > Set cmd.ActiveConnection = conn
> > cmd.CommandText = sqlStatement
> > 'step 3: open a recordset
> > rs.Open cmd, , adOpenKeyset, adLockReadonly
> > 'step 4: able to access recordset fields
> > Print.Debug rs.Fields("field1")

> > Thanks!!
> > John



Tue, 04 Oct 2005 19:11:54 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. When to use ADODB.Command Object in ADO Programming

2. Sending command to a FoxPro database using ADODB.Command Object

3. Using adodb command object with transferspreadsheet

4. Using browser-side ADODB.Command object?

5. Using browser-side ADODB.Command object?

6. Using where IN with a ADODB.Command object...

7. Err in using ADODB Command Object in VB.Net

8. Using browser-side ADODB.Command object?

9. Using the ADO Command Object to send an Execute command

10. ADO Command Object using Multiple Connection Objects

11. Error Using ADO Command object

12. ADO: Using Command Object

 

 
Powered by phpBB® Forum Software