Microsoft Visual Studio .NET 2005 I. Installations 1. Microsoft Viual studio .NET 2005 2. Oracle Client software 9i II. Setups 1. Create a "Net Service Name": * Oracle Client software -> Network -> Net Manager-> Click on "Net Service Name" -> click on "+" -> Fill in Net Service Name (e.g. DELPHIDB) -> Next -> Select TCP/IP (Internet Protocol) -> Fill in the service name (delphi.cs.csubak.edu) and Port number that DB Listerner is listening on (1521 is the starndard one) -> Next -> Fill in the Oracle 8i Release 8.1 Service Name ( "dbs01.cs.csubak.edu" is oracle db server the name used on delphi ) -> Next -> Test -> If test is successful, then click "Finish", otherwise go back to check the filled data. 2. Use Visual Studio .NET 2005 Data Server get a connect-string for database connection. 1. Select Oracle Database, 2. Microsoft driver for OLE Database in the driver selection box, 3. Fill "Server Name" box with "PROVERDB", 4. Fill Oracle user ID and password, 5. Test the connection, 6. Use "Advanced" button to get connection string. You may have to add "Password=c3m4p2s" in the string you have get. 7. use Debug to make sure you can connect to Oracle database on prover. III. Key Client Components for Accessing Database Data DataTable DataAdapter OleDbConnection Database <===> or <======> <=====> <====> Grid DataSet Object Object Service 1. Data Grid: UI component that displays the records result from a select statement. The records are stored in a table object. 2-1. DataTable Object: In-Memory Data structures for holding records. The records are filled by data adapter objects. After the data table object is filled by data adapter. Users can manipulate the data in data table through the grid. The updates, including insertions, deletions and updates, to the data through the grid are stored in the data table object. The updates can become permenant through a data adapter's update method. DataTable object is not connected to database, and connection to database can be disconnected and the resoruces used by the data adapter can be disposed after data is filled. 2-2. DataSet Object: Holds a set of DataTable objects in DataTableCollection (tables property). The individual data table object are filled by data adapter, and can be accessed by tables[i]. 3. DataAdapter Object: Using connection object, data adapter fills the data table or data set objects with records. The connection to the database can be disconnected and resources used by data adapter can be disposed after the data table object(s) are filled. To save updates in data table or data set, a connection and a data adapter object must be used to perform the update actoins. To save the resources of machine that runs the data adpater, a data adapter object should be disposed after the data retrieval and data updates. A DataAdapter object could have up to 4 commands in it; SelectCommand, UpdateCommand, DeleteCommand, and InsertionCommand. Those commands will be used create SQL statements based on the updates made to the disconnected DataTable object. When a DataAdapter object is instanciated, only SelectCommand field of the object is filled, and the other type of commands can be added by a CommandBuilder class object. 4. OleDbConnection: A connection bject connects an application to database server based on local Service Name. To save server resources, a connection should be closed after a data retrieval or data update. IV. Connecting to Databaes Through OLE Database Driver for Oracle 1. Define and initialize a OleDbConnection objecet with connection string. The connection string for OleDatabase Driver for Oracle is Dim strConnection As String = "Provider=MSDAORA.1;User ID=cs342;Password=student2;Data Source=PROVERDB2" 2. Select a connection class to define a connection object from one of the following classes - SqlConnection: an object that manages a connection to a SQL Server version 7.0 or later. It is optimized for use with SQL Server 7.0 or later by (among other things) bypassing the OLE DB layer. - OleDbConnection: an object that manages a connection to any data store accessible via OLE DB. - OdbcConnection: an object that manages a connection to a data source created by using a connection string or ODBC data source name (DSN). - OracleConnection: an object that manages a connection to Oracle databases. 3. Create a Database Data Adapter Object with a SQL string and a connection object with one of the following data adapter classes - System.Data.Odbc.OdbcDataAdapter - System.Data.OleDb.OleDbDataAdapter - System.Data.OracleClient.OracleDataAdapter - System.Data.SqlClient.SqlDataAdapter - System.Data.SqlServerCe.SqlCeDataAdapter 4. Define a DataTable or DataSet object and fill it with data adapter.fill( table ) 5. Set the data source field of the data grid component with data table object. The code snipplet is Dim strConnection As String = "Provider=MSDAORA.1;User ID=cs342;Password=student2;Data Source=PROVERDB2" Private Sub execSQL() Dim conn As OleDbConnection Dim adapter As OleDbDataAdapter Dim table As DataTable Try conn = New System.Data.OleDb.OleDbConnection(strConnection) adapter = New System.Data.OleDb.OleDbDataAdapter(Me.txtSQL.Text, conn) table = New System.Data.DataTable conn.Open() adapter.Fill(table) Me.dataGrid1.DataSource = table conn.Close() adapter.Dispose() Catch ex As OleDb.OleDbException If conn.State = ConnectionState.Open Then conn.Close() If Not adapter Is Nothing Then adapter.Dispose() MsgBox("Error in execSQL: " & ChrW(13) & ex.Message) End Try End Sub V. Display Query Result 1. Use DataTable object - A data table holds only one result set at a time. - A DataAdapter object is used to fill the DataTable object - Assign the data table object as data source of data grid to display the records in grid. - close connection, and dispose adapter object. 2. Remarks on Grid and DataTable Object - The grid is the data presentation while the table object is the data structure holding the data. - After the connection is closed, and data adapter is disposed, the data table is a disconnected record set. - Any operations(deletion, insertion, update) performed on the grid will affect the data/records in the disconnected data table object and will not change the data/record in the database. 3. Use DataSet Object - Use the data set object if multiple record sets/ data tables are obtained by the SQL statement. - A DataSet object or its individual table object can be used as data source of a data grid to display the data. VI. Update Database Through Data Grid 1. Updates to DataSet to DataTable - Updates to DataSet object are targeted at individual data table object. - Dataset and DataTable object are disconnected from the database. - Users can perform insertions, deletions and updates on the data/records in the table object (data set object's data table object) through the data grid. - To insert a new record into data table object, insert the new record at the last row of grid or through method of data table object. The record will be added into data table object (not into the database at this monent). - To delete a record in the data table object through data grid, set the record as current record, press DELETE key. The record will disappear from the grid and will be marked as deleted in table obejct. - To update the record through the data grid, edit the cells for the record. 2. Save Updates into Database - Changes made to DataTable object are local. The local changes must be saved to database before the changes become perminant. - The update method of a DataAdapter class is designed for that purpose. Notice that a new DataAdapter object has only SelectCommand (no UpdateCommand, INsertCommand or DeleteCommand) when it is instanciated. A CommandBuilder class object must be used to build the other commands so that updates in DataTable object can be save those commands. - Step to save updates * Make a connection to database, * Create DataAdapter object and a CommanBuilder object, and use the CommandBuilder object to build commands in adapter. * Call adapter's update method to updat the database. - Code for Saving Updates Dim strConnection As String = "Provider=MSDAORA.1;User ID=cs342;Password=student2;Data Source=PROVERDB2" Dim conn As OleDbConnection Dim adapter As OleDbDataAdapter Dim cmdBuilder As OleDbCommandBuilder Dim table As DataTable Private Sub execSave() Try Console.WriteLine("Time Starting timing: " & Now) conn = New OleDbConnection(strConnection) adapter = New OleDbDataAdapter(Me.txtSQL.Text, conn) cmdBuilder = New OleDbCommandBuilder(adapter) table = Me.dataGrid1.DataSource adapter.Update(table) MsgBox("Updates are done!") Catch ex As OleDbException MsgBox(ex.Message) Finally conn.Close() adapter.Dispose() cmdBuilder.Dispose() End Try End Sub