ADO.NET in VB.NET

1. ADO.NET in VB.NET ITE 370 University of South Alabama School of CIS

2. What is ADO.NET? • An acronym for the .NET version of ActiveX Data Objects • A .NET Framework Class Library • A group of types that reside in System.Data namespace • A technology used by client applications to work with stored data • Supported data formats include: • relational databases, such as MS-Access, SQL-Server, and Oracle • Spreadsheets and other file formats • XML University of South Alabama School of CIS

3. Providers and Their Namespaces • What is a “provider”? • .NET data provider • Software for accessing a specific source of data, such as SQL-Server • Optimized classes for SQL-Server • Namespace: System.Data.SqlClient • OLE DB client • For MS-Access, Excel, and others • Also has support for Oracle and SQL-Server • Namespace: System.Data.Oledb University of South Alabama School of CIS

4. How clients, .NET data providers &DBMSs fit together SQL Server SQL .NET Data Provider MS Access client OLE DB .NET Data Provider other DBMS other .NET data providers University of South Alabama School of CIS

5. Types of ADO.NET objects • Connection – establishes links to data sources • Command – stores and executes commands (i.e. queries and stored procedures with parameters) • DataReader – provides sequential, read-only access to data • DataAdapter – creates and populates DataSets • DataSet – an in-memory cache for storing data in various formats • DataTable – stores a relation—eg. the result of a query • DataRelation – defines a relationship between two DataTables • DataRow – stores a single row/record of a data table • DataColumn – represents schema in a column of a DataTable • DataView -- customized view of a DataTable for sorting, filtering, searching, editing, and navigation. • DataGrid – tabular control for displaying/editing a data source University of South Alabama School of CIS

6. Working with .udl files to build connection strings To create a UDL file: 1.      Open Windows Explorer or My Computer. 2.      Select the folder in which you want to save the .UDL file. 3.      On the File menu, click NEW and then click Text Document. 4.      Right-click on the text file you created in step 3, then click Rename. Type the new file name using a .udl file extension. Press Enter. For example. Myudl.UDL 5.      You may get a warning, message box, explaining that changing file extensions may cause files to become unusable. This is fine, click OK. Once you have created the UDL file, you must now configure it: • Double click on the UDL file to bring up the Data Link Properties Dialog Box. • Click on the Providers tab and select the driver you wish to use. • Click on the Connection tab and enter the connection properties, each driver will require different settings, so I will not go into much details. All drivers will require a user name and password. • Click on the “Test Connection” button to verify your connection is working correctly. Change accordingly if you get an error. • Select the advanced tab only if you require advanced settings. Source: K & K Consulting, http://kandkconsulting.tripod.com/VB/Tutorials/udl_file_tutorial.htm University of South Alabama School of CIS

7. DataReader Class • The DataReader • Provides forward-only, read-only access to data • Analogous to reading a sequential file • Fast access to data • Uses little memory • Requires connection and command objects to use University of South Alabama School of CIS

8. DataReader Example Imports System.Data.Oledb Public Class ConnDb '* Declare constant to hold base connection string information '* for connecting to an MS-Access baseball database. Public Const ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Persist Security Info=False;Data Source=" End Class '* Declare data access objects Dim conTeamDb As OleDbConnection '* connects to baseball database Dim dbCommand As OleDbCommand '* executes database query for team Public rdrTeams As OleDbDataReader '* provides forward-only, read-only access to team relation Dim OpenDbDialog As New OpenFileDialog ‘* If the user selects a database in the open dialog box... If OpenDbDialog.ShowDialog() = DialogResult.OK Then '* connect to the database conTeamDb = New OleDbConnection(ConnDb.ConnStr & OpenDbDialog.FileName) conTeamDb.Open() '* query the team table, assigning result to a data reader dbCommand = New OleDbCommand("SELECT Location, Nickname, Stadium, League FROM Team", conTeamDb) rdrTeams = dbCommand.ExecuteReader If rdrTeams.Read() '* success reading next team Console.WriteLine(rdrTeams.Item("Location") & " " & rdrTeams("Nickname")) End if rdrTeams.Close() conTeamDb.Close() End If University of South Alabama School of CIS

9. DataSet w/Multiple Tables & Providers 1) Fill(DataSet, “TableX”) SQL Server 3) Return DataTable SqlDataAdapter DataSet SelectCommand 2) Execute query TableX TableY Oracle OleDbDataAdapter 6) Return DataTable SelectCommand 5) Execute query 4) Fill(DataSet, “TableY”) University of South Alabama School of CIS

10. DataAdapter Class • Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source • Appears on the component tray • __Command properties: • Select, Insert, Update, Delete • Methods: • Fill, Update University of South Alabama School of CIS

11. DataAdapter: __Command Properties • Contain command objects to perform operations against the underlying database • SelectCommand • Populates a DataTable with the results of a query • Uses SQL SELECT statement typically • InsertCommand • Inserts rows added to a DataTable into database • Uses SQL INSERT statement University of South Alabama School of CIS

12. DataAdapter: __Command Properties (cont.) • UpdateCommand • Writes changes made to a DataTable into the underlying database • Uses SQL UPDATE statement • DeleteCommand • Makes deletions of rows in a DataTable permanent by deleting those rows from the database • Uses SQL DELETE statement University of South Alabama School of CIS

13. DataAdapter: Fill Method • Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name • Creates a DataTable named "Table" daEmployee.Fill(dsCompany, “AllEmployees”) University of South Alabama School of CIS

14. DataAdapter: Update Method • Used to persist changes to a DataTable (i.e. save them in the database) • Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table" daEmployee.Update(dsEmployee) University of South Alabama School of CIS

15. DataGrid class • The Windows Forms DataGrid control • Provides a user interface to ADO.NET datasets • displays tabular data • allows for updates to the data source University of South Alabama School of CIS

16. Binding the DataGrid • The DataGrid can receive data from any of the following data sources: • DataTable class • DataView class • DataSet class • DataViewManager class dtgTeams.DataSource=fTeams.tblTeam or dtgTeams.SetDataBinding(dsTeams, _ “Teams”) University of South Alabama School of CIS

17. Current row and cell • Users can navigate on the DataGrid • CurrentRowIndex • Holds zero-based integer for highlighted row • CurrentCell property • Gets or sets which cell has the focus • Has ColumnNumber and RowNumber properties • Item collection holds cell content ‘* display cell contents of third column Console.WriteLine( dtgTeam.Item(dtgTeam.CurrentRowIndex, 2)) University of South Alabama School of CIS

18. DataGrid properties • CaptionText – defines a title on top bar • ReadOnly – true means no edits in grid • AllowSorting – users can toggle asc/desc sorts of data for each column University of South Alabama School of CIS

19. Hiding a Column in DataGrid • Sometimes, you will want to store a column in the DataGrid that you don’t want users to see (eg. Primary key field) • The following code hides a column of the DataGrid: '* Declare a new DataGridTableStyle in the declarations area of your form. Dim TableStyle As DataGridTableStyle = New DataGridTableStyle Sub HideColumn(ByVal TableName As String, ByVal ColumnName As String) '* Set the DataGridTableStyle.MappingName property ' to the table in the data source to map to. TableStyle.MappingName = dtgPlayers.DataMember '* Add it to the datagrid's TableStyles collection dtgPlayers.TableStyles.Add(TableStyle) '* Hide the column with ColumnName by setting its width to zero dtgPlayers.TableStyles(TableName).GridColumnStyles(ColumnName).Width = 0 End Sub University of South Alabama School of CIS