1 / 90

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #9

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #9. Objectives. Understanding ADO Using DSN/DSN’less connection In-depth: Connection RecordSet Command. 3-Tier Architecture. The Data Access Layer.

arav
Download Presentation

Enterprise Development Using Visual Basic 6.0 Autumn 2002 Tirgul #9

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. Enterprise Development Using Visual Basic 6.0 Autumn 2002Tirgul #9 ‘Tirgul’ # 9

  2. Objectives • Understanding ADO • Using DSN/DSN’less connection • In-depth: • Connection • RecordSet • Command ‘Tirgul’ #9

  3. 3-Tier Architecture ‘Tirgul’ #9

  4. The Data Access Layer • Almost any enterprise application have some needs to access data (database, web, email, legacy data..) • Microsoft has developed a technology to make this task simple for us • VB makes it easy to use ‘Tirgul’ #9

  5. The Problem • Each data source has different way to access it • SQL Data • SQL Server • Oracle • Informix • DB2 • Fox Pro • Jet • Non SQL Data • Mail • Video • Text • Web • ... • Mainframe and Legacy Data ‘Tirgul’ #9

  6. The Solution • Single high level,efficient programming, paradigm to work with everything it is called:universal data access (UDA) • The programmer can use the same code to access any data! ‘Tirgul’ #9

  7. What is UDA? • UDO Consist of: • ActiveX Data Objects(ADO) • OLE DB • And other… Together these interfaces provide us the means to work with any data ‘Tirgul’ #9

  8. Data consumer UDA Architecture Application (VB, VC++,ASP) ADO 0 Data Providers OLE DB • SQL Data • SQL Server • Oracle • Fox Pro • Jet • Non SQL Data • Mail • Video • Text • Web Mainframe and Legacy Data ‘Tirgul’ #9

  9. What is ADO? • ADO is one of the components of the UDA model. • Other components: • Remote Data Services (RDS) • OLE DB is Microsoft's component database architecture that provides universal data integration over an enterprise's network— from mainframe to desktop - regardless of the data type • Open Database Connectivity (ODBC) provide a unified way to access relational data as part of the OLE DB specification ‘Tirgul’ #9

  10. ADO Layer ‘Tirgul’ #9

  11. Ado Object Model Overview • High level, language independent, data access interface • Provides layer to the underplaying data source (OLE DB Provider) • Object Oriented interface for accessing data • ADO is small, lightweight, fast, and feature complete – everything you need when you are programming either for the database applications or the Internet ‘Tirgul’ #9

  12. Object Functionality Connection Manages the connection with the data source Command Defines the commands that will be executed against the data source Recordset Contains the data that is retrieved from the data source ADO Objects • object-based interface that provides a logical set of objects you can access from code ‘Tirgul’ #9

  13. ADO Objects • ADO objects are not dependant on one another. you can create instances of objects independently of one another, for example, you can create a Recordset object without creating a connection object. • Unlike the older technologies, ADO is more flexible, ADO code is easier to write, read and maintain. • ADO is built on top of OLE DB and is capable of accessing any sort of data that is wrapped and exposed by an appropriate OLE DB provider. ‘Tirgul’ #9

  14. Basic ADO operations • Select Query • Update Query • Delete Query • Integrity Constraints (PK/FK) • Transactions (Begin, Commit, Rollback) • Stored Procedures ‘Tirgul’ #9

  15. ADO Objects • Connection • Recordset • Command • Parameter • Error • Field • Property • Stream ‘Tirgul’ #9

  16. The Connection Object • The connection object enable us to connect to data stores. • In this object we specify which: • OLE DB provider we wish to use. • Security details for the connection. • And other specific details. • NOTE: in some cases you do not have to define a connection object. Then ADO will create one for you. as a rule, always define connection object explicitly. ‘Tirgul’ #9

  17. The Command Object • The command object is designed for running command against a data store • You may also execute command using the connection object but these command have restrictions. ‘Tirgul’ #9

  18. The Recordset Object • The most commonly used object. • It contains a set of data we extract from the data stores. • The Recordset holds the record that are usually fetched from a query • It allows us to: • Change the data (update, delete, add). • Iterate the records. • Filter the record so that only a subset is shown. • A Recordset also has a fields collection where there is a filed object for each column in the Recordset ‘Tirgul’ #9

  19. DISPLAYING DATA WITH A RECORDSET (USING A CONNECTION OBJECT) Set Con = new ADODB.ConnectionSet RS = new ADODB.RecordsetCon.open “DSN=myDB;UID=sa;Password=;”RS.ActiceConnection = Con RS.open “select * from Users”whilenot RS.EOF   msgBox RS.fields(“fname”).values RS.MoveNext Wend RS.CloseCon.Close ‘Tirgul’ #9

  20. DISPLAYING DATA WITH A RECORDSET (USING A CONNECTION OBJECT 2) StrConnect = “DSN=myDB;UID=sa;Password=;” Set Con = new ADODB.Connection Con.open “DSN=myDB;UID=sa;Password=;” Set RS =new ADODB.Recordset RS.Open “select * from Users”, Con, adopenkeyset,adlockoptimistic whilenot objRec.EOF    msgBox RS.fields(“fname”).values   RS.MoveNext Wend RS.Close ‘Tirgul’ #9

  21. DISPLAYING DATA WITH A RECORDSET (WITHOUT A CONNECTION OBJECT) StrConnect = “DSN=myDB;UID=sa;Password=;” Set RS =new ADODB.Recordset RS.Open “select * from Users”,strConnect, adopenkeyset, adlockoptimistic whilenot objRec.EOF    msgBox RS.fields(“fname”).values   RS.MoveNext Wend RS.Close ‘Tirgul’ #9

  22. ADO 2.5 Extensions • Record Object • Stream Object • Not in this course scope • Mainly for handling non relation data such as File System, Email System XML Files.. ‘Tirgul’ #9

  23. ADO Collections • There are several collections in the ADO object library. • Fields collection. • This collection is a member of the Recordset object. • Each member of the collection is filed object. • In SQL structured data the field correspond to a column and contains information about the column: • Name, data type, length.. ‘Tirgul’ #9

  24. ADO Collections – Cont. • There several other collections: • Parameter – part of the command object • Errors – part of the connection object • Properties ‘Tirgul’ #9

  25. Connecting to Data Source • In order to access some data source, you have to connect to it using the connection object. • The actual method of connecting to the data store is the same for all stores. • The actual details may be different between different providers • There are several ways to connect to data source: • Using a connection string – DSN’less • Using ODBC data source – DSN • Using data link file ‘Tirgul’ #9

  26. DSN • DSN = 'Data Source Name' • DSN is an easy way to assign useful and meaningful names to data sources which may not be limited to databases alone (e.g Excel spread sheet etc.) ‘Tirgul’ #9

  27. Creating a DSN (1) • Database exists • ODBC32 exists (Check control panel) • Go into Control Panel • NT ONLY - Go into Administration Tools • Look for ODBC Data Sources (32-bit) or Data Sources (ODBC) ‘Tirgul’ #9

  28. Creating a DSN (2) • Go into Control Panel • Then Administrative Tools ‘Tirgul’ #9

  29. Creating a DSN (3) • Then ODBC Data Sources (or some sort of ODBC) ‘Tirgul’ #9

  30. Creating a DSN (4) • Then click on the System DSN tab and click on add User DSN - DSN's for you only. System DSN - The whole system can see these they are ones that everyone that accesses your computer can see. ‘Tirgul’ #9

  31. Creating a DSN (5) • Choose your driver ‘Tirgul’ #9

  32. Creating a DSN (6) • Set up the DSN Enter a Data Source Name, this is what we call your Database, so keep it short and without spaces. eg. myBooks Click on Select... to browse to the directory where your database is and choose it, this tells ODBC where you find the DB (well duh!) If you set a password for your database click on Advanced... ‘Tirgul’ #9

  33. Creating a DSN (7) • Passwords? Access only supports passwords, not usernames, so just enter the password and click OK. Click OK to the other dialog and you can see your DSN listed in the System DSN list, now you can use it to set up databases. ‘Tirgul’ #9

  34. Create the Northwind DSN • Northwind is a sample Access DB. • It comes with Microsoft office installation. • Use ‘Search’.. ‘Tirgul’ #9

  35. Add Northwind DSN– Select Access Driver Select Access Driver ‘Tirgul’ #9

  36. Add Northwind DSN – Select Data base Use Select to select database ‘Tirgul’ #9

  37. Add Northwind DSN – Browse for Northwind ‘Tirgul’ #9

  38. Add Northwind DSN – Set name and description ‘Tirgul’ #9

  39. Add Northwind DSN – Done, new in the list Northwind added ‘Tirgul’ #9

  40. DSN vs DSN less Database Connections • you can connect to DSN using following code: DSN Name only Other data is hidden in the DSN itself Dim con asnew ADODB.Connection con.Open "DSN=myDSN" …Use Connection con.Close Set con = Nothing ‘Tirgul’ #9

  41. DSN’less Connection • DSN’less connection does not require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. • We will now see how to connect to a database using Connection String in place of DSN name. ‘Tirgul’ #9

  42. DSN less Connection String Dim con asnew ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data“ & _ "Source=c:\path\to\database.mdb" …Use Connection con.Close Set con = Nothing All Connection Data ‘Tirgul’ #9

  43. Why to use DSN Connections ? • Provides meaningful data source names. • When there are lots of data sources to think of and you want a central repository to hold a collection of data sources without having to worrying about the actual configuration of the data source. ‘Tirgul’ #9

  44. Why to use DSN less Connections ? • When you can't register DSNs yourself e.g. when you are running a virtual hosting account on other's server. Stop emailing system administrator, connect to your databases directly. • Provides faster database access because it uses native OLE DB providers which provide faster database connection. ‘Tirgul’ #9

  45. ADO Connection String • For MS Access database:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\some-folder\mydatabase.mdb; User ID=admin; Password=; • For MS SQL Server:Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password; Network Library=dbmssocn; ‘Tirgul’ #9

  46. Building a Connection String • Create a UDL file • Right click on Desktop, New -> Text File, rename to filename.UDL • Double-click the UDL file • Select Microsoft.Jet.OLEDB.4.0 ‘Tirgul’ #9

  47. Building a Connection String (cont.) • Click Next >> • Browse for the Accessdatabase • Enter login informationif necessary ‘Tirgul’ #9

  48. Building a Connection String (cont.) • Click OK • Open the UDL file using Notepad ‘Tirgul’ #9

  49. Connection – Open Method • The connection object has an open method: • Connection.Open [connetionstring], [user id], [password], [options] ‘Tirgul’ #9

  50. Establishing a SQL Database Connection ‘Tirgul’ #9

More Related