1 / 11

Programming using Database Object

Programming using Database Object. Why Database Automation?. All tables in the Web Application must populate from the database. REQUIREMENT SPECS. Database Automation Process. Steps in Database Automation:. Prepare the connection string. Create an instance of ADO database object.

duncan-day
Download Presentation

Programming using Database Object

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Programming using Database Object

  2. Why Database Automation? All tables in the Web Application must populate from the database REQUIREMENT SPECS.

  3. Database Automation Process Steps in Database Automation: • Prepare the connection string Create an instance of ADO database object 3 2 6 4 8 5 7 1 • Open the database connection • Create an ADO Recordset • Create SQL query • Open the Recordset • Close the Recordset and Database connection • Extract data from the Record set

  4. Step 1: Creating an ADO Connection to the Database Creation of ADO Connection  Creating the instance of ADO - database connection object by using the CreateObject() method ADO Active X Data Objects CreateObject(“ADODB.Connection”) It is a Microsoft Technology It acts as programming interface to access data in a database. Set objDBConnection = CreateObject(“ADODB.Connection”)

  5. Step 2: Preparing Connection String 'Use Fields and get the record set column count when we don't ‘know the 'column count 'Create an ADO connection to a database SetobjDBConnection = CreateObject("ADODB.Connection") 'Connection String for MSAccess Database - DSN less strAccessDBPath = "C:\Program Files\HP\QuickTestProfessional\samples\flight32.mdb" strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath Data Source Note: Note: Note: It is better to store the physical path of the database in a variable. The Provider details vary with the type of database we want to connect to, and location of the database. For the password protected database, you also need to provide valid database User ID and Password in the connection string. We need to specify the provider Provider is Microsoft Jet OLEDB 4.0.

  6. Step 3: Opening the Database Connection 'Use Fields and get the record set column count when we don't ‘know the 'column count 'Create an ADO connection to a database SetobjDBConnection = CreateObject("ADODB.Connection" ) 'Connection String for MSAccess Database - DSN less strAccessDBPath = "C:\Program Files\HP\QuickTestProfessional\samples\flight32.mdb" strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath 'Open the database connection objDBConnection.OpenstrConnString To open the database, we need to use the Open method of Database object.

  7. Step 4: Creating an ADO Recordset 'Use Fields and get the record set column count when we don't ‘know the 'column count 'Create an ADO connection to a database SetobjDBConnection = CreateObject("ADODB.Connection" ) 'Connection String for MSAccess Database - DSN less strAccessDBPath = "C:\Program Files\HP\QuickTestProfessional\samples\flight32.mdb" strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath 'Open the database connection objDBConnection.OpenstrConnString 'Create an ADO recordset Set objRecordSet = CreateObject("ADODB.Recordset") The ADO Record set object is used to hold a set of records from a database table. A Record set object consist of records and columns (fields). CreateObject(“ADODB.Recordset”)

  8. Step 5: Creating SQL Query 'Use Fields and get the record set column count when we don't ‘know the 'column count 'Create an ADO connection to a database SetobjDBConnection = CreateObject("ADODB.Connection" ) 'Connection String for MSAccess Database - DSN less strAccessDBPath = "C:\Program Files\HP\QuickTestProfessional\samples\flight32.mdb" strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath 'Open the database connection objDBConnection.OpenstrConnString 'Open the database connection Set objRecordSet = CreateObject("ADODB.Recordset") 'SQLQuery to get the data strQuery = "SELECT * FROM Orders WHERE Order_Number<=5"

  9. Step 6: Opening the Recordset 'Use Fields and get the record set column count when we don't ‘know the 'column count 'Create an ADO connection to a database SetobjDBConnection = CreateObject("ADODB.Connection" ) 'Connection String for MSAccess Database - DSN less strAccessDBPath = "C:\Program Files\HP\QuickTestProfessional\samples\flight32.mdb" strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath 'Open the database connection objDBConnection.OpenstrConnString 'Open the database connection Set objRecordSet = CreateObject("ADODB.Recordset") 'SQLQuery to get the data strQuery = "SELECT * FROM Orders WHERE Order_Number<=5“ 'Open the recordset objRecordSet.OpenstrQuery,objDBConnection

  10. Step 7: Extracting Data from the Recordset 'Open the database connection Set objRecordSet = CreateObject("ADODB.Recordset") 'SQLQuery to get the data strQuery = "SELECT * FROM Orders WHERE Order_Number<=5“ 'Open the recordset objRecordSet.OpenstrQuery,objDBConnection 'Record Set has only one row - Get column count of record set using Fields intColumnCount = objRecordSet.Fields.Count 'Read all the fields from record set ForintCol = 0 to intColumnCount-1 msgbox"Column Name:" & objRecordSet.Fields.Item(intCol).Name & vbNewLine&_ "Value:" & objRecordSet(intCol) Next

  11. Step 8: Closing Recordset and Database Connections 'Open the database connection Set objRecordSet = CreateObject("ADODB.Recordset") 'SQLQuery to get the data strQuery = "SELECT * FROM Orders WHERE Order_Number<=5“ 'Open the recordset objRecordSet.OpenstrQuery,objDBConnection 'Record Set has only one row - Get column count of record set using Fields intColumnCount = objRecordSet.Fields.Count 'Read all the fields from record set ForintCol = 0 to intColumnCount-1 msgbox"Column Name:" & objRecordSet.Fields.Item(intCol).Name & vbNewLine&_ "Value:" & objRecordSet(intCol) Next 'Close RecordSet and Database Connections objRecordSet.Close objDBConnection.Close

More Related