Visual Basic Programming: Much Ado about ADO - Wasteful Creation of ADO Objects

(Page 4 of 5 )

As mentioned earlier, every ADO object has a definite scope and purpose. These objects should be created sparingly -- only when they are required. Creating ADO objects indiscriminately puts a lot of load on the system resources.

Connection object is indispensable. It has to be created. But you can certainly do away with the need of other two objects depending upon the requirement of the program. For instance, when you need an editable Recordset, all you need is Connection and Recordset objects. In the example that we discussed above, there was in fact no need to create the Recordset object. We discussed it just to pinpoint how sometimes you might end up creating a Recordset that is read-only and forward-only despite having declared proper cursor and lock types. For the purpose of creating an editable recordset, the following code could have fit the same bill, but with more efficiency: 

Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Rst = New ADODB.Recordset
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Rst.Open “Select * from Actors”, Con, adOpenDynamic, adLockOptimistic
End with
End Sub

Similarly, when you just need to fetch records in a recordset, and the recordset doesn’t have to be editable, all you need to do is to create a Connection object, and use its Execute method to run an SQL statement. Here it goes:

Private Sub Form_Load()
Set Con = New ADODB.Connection
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Set Rst  = Con.Execute (Select * from Actors)
End Sub

In the above example, we saved the system resources by eliminating the need of Command and Recordset objects. This will in turn make the program more efficient. Efficiency is the buzzword in the real world of programming. How a programmer uses system resources reflects his or her efficiency as a programmer, since execution of code is not the issue in the real world of programming. What matters most is the efficiency.

Next: The Purpose of Command Objects >>
 

More Visual Basic.NET Articles
More By Maneesh Makheeja

Comments On: Visual Basic Programming: Much Ado about ADO

>>> Be the FIRST to comment on this article!