ADO.NET in VB.NET 2005

1. ADO.NET in VB.NET 2005 ITE 370

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 (MS-Access, SQL-Server, and Oracle) • Spreadsheets and other file formats • XML

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

4. SQL Server SQL .NET Data Provider MS Access client OLE DB .NET Data Provider other .NET data providers How clients, .NET data providers &DBMSs fit together other DBMS

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 • TableAdapter – pulls data from one or more database tables and passes it to your program • DataSet – an in-memory copy of data pulled from the database tables (by the Table Adapter) • 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

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

7. Create Data Source String for Connection Object Example of how to create the Data Source String

8. DataReader Class • The DataReader • Provides forward-only, read-only access to data • Analogous to reading a sequential file • Read from beginning of file in order to the end of file • Fast access to data • Uses little memory • Requires connection and command objects to use

9. 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 (connection string from UDL file). Public Const ConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Persist Security Info=False;Data Source=“ ‘* notice we removed name of file 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 '* 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

10. View Sample Project Using Data Reader • Create New VB.Net Application • Add ListTeams.VB • Project Properties  StartUP • Sub Main ( ) • Run Sample Application Using Data Reader • Attempt to Add a Form and Have the Data Display within the Form instead of to the Console

11. Using Table Adapters / Data Set • Table Adapter – Retrieves data from the data source and updates the data in the data source • Data Connection – Connecting to the Data Source • Built into the Table Adapter Object • Data Set – In memory copy of records / data tables • Separate from the data source • Data tables have columns and rows • Allows you to work with the data from the data source • Changes are not permanent in external data source unless directed by the Table Adapter • Data is transferred from the data source to the data set and vice-versa through the Table Adapter

12. DataSet w/Multiple Tables & Providers 1) Fill(DataSet, “TableX”) SQL Server 3) Return DataTable SqlTableAdapter DataSet SelectCommand 2) Execute query TableX TableY Oracle OleDbTableAdapter 6) Return DataTable SelectCommand 5) Execute query 4) Fill(DataSet, “TableY”)

13. TableAdapter Class • Provides communication between the application and database • Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source based on queries • Can contain multiple queries – called like methods • Created with • Code • Dataset Designer • During Creation of a New Dataset – Data Source Configuration Wizard • Server Explorer / Dataset Designer

14. TableAdapter Class • __Command properties (Created through Queries): • Select Query – Used in Fill Method • Insert Command, Update Command, Delete Command • Can be Created by default if enough information is available in the Select query (Fill Method) • Methods: • Fill – populated the Data Table with the result of the select command • Update – Sends changes back to the database • Insert – creates a new row in the data table • ClearBeforeFill – Clears the data table before executing a fill method

15. TableAdapter: __Command Properties • Contain command objects to perform operations against the underlying database • Select Command • Populates a DataTable with the results of a query • Uses SQL SELECT statement typically • Insert Command • Inserts rows added to a DataTable into database • Uses SQL INSERT statement

16. TableAdapter: __Command Properties (cont.) • Update • Writes changes made to a DataTable into the underlying database • Uses SQL UPDATE statement • Delete Command • Makes deletions of rows in a DataTable permanent by deleting those rows from the database • Uses SQL DELETE statement

17. 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" taEmployee.Fill(dsCompany.AllEmployees) Name for Data Table Table Adapter Method Data Set

18. TableAdapter: 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" taEmployee.UpdateCommand(dsEmployee)

19. Table Adapter – Through IDE • Server Explorer • Click on Connect to DB Icon • Add Connection • Provide Data Source Type (Access, SQL Server) • Provide Database File • Test the Connection • Add DataSet • Data Menu  Add New Data Source • Project  Add New Item  DataSet • Select Items from Open Connection in Server Explorer • Creates the Data Set and Table Adapter

20. Binding Source • Data Aware Controls • Controls that can automatically display data from a table cell in the DataSet (most VB.net controls are data aware) • Controls must be Bound to the DataSet • DataBindings Property of Control • Arrow next to Text Field (of DataBindings) • Add or Select Data Source • Complete Data Source Configuration Wizard

21. Currency Manager • Object that gives a way of navigating the rows of the DataTable. One row of the DataTable is always the current row • Data Aware controls display the data from the current row • Currency Manager objects are included in BindingSource components • Position Property  Number of the Current Row (zero based) • Count Property  How many rows are in the table

22. Sample Code • Create a Visual Basic 2005 Application that will access the Baseball3 database and display the player information in bound textboxes. The program should use a Table Adapter, Data Set , Binding Source and Currency Manager to manipulate through the table data.

23. Visual Studio.Net Wisdom • Update Command TableAdapter.Update (Dataset.DataTable) taEmployee.Fill(dsCompany.AllEmployees) • Updates the database • Updates the “local” copy in the Bin directory • Copied over by Visual Studio the next time the program is run • Don’t want you to corrupt your databases • How to accomplish a True Update?????

24. Update Command • Cannot use the IDE to develop your application • Must create the code by hand (Within Class Code) • Connection Object • DataAdapter Object • DataSet Object • DataTable Object • BindingSource Object • CommandBuilder Object • Import System.Data.OleDb (will be System.Data.SQL for SqlServer)

25. Connection Object • Create the connection to the data source • Uses the Connection String Information Dim myConnection as New OleDb.Connection (connectionString)

26. Data Adapter Object • Table Adapter is Not an Option Here, we must use a Data Adapter Dim myDataAdapter as New OleDbDataAdapter (“sqlCommand”, connectionObject)

27. Data Set Object • Create the Data Set Dim myDataSet as New DataSet (“DataSetName”) • Name of DataSet is a String, it must be in Quotation Marks

28. Data Table Object • Create the Data Table • Will Store the Data for the Data Set in Table form Dim myDataTable as New DataTable (“DataTableName”) • Data Table Name is a string, it must be in Quotation Marks

29. Binding Source • You must create a Binding Source Object to bind your data controls to the DataSet Dim myBindingSource as New BindingSource

30. Command Builder • Fill Command is automatically created for the Data Adapter Object • In an OleDb Connection a Command Builder object is needed to create the additional commands including Update Dim myBuilder as New OldDbCommandBuilder (NameOfDataAdapterObject)

31. Sample Code

32. Form Load Event • Open the Connection • connectionObject.Open( ) • Add the DataTable to the DataSet • dataSetObject.Tables.Add(dataTableObject) • Fill the DataSet through the DataAdapter • dataAdapterObject.Fill(dataSetObject, “StringName of DataTable”) • Bind the DataTable to the BindingSource • bindingSourceObject.DataSource = dataTableObject

33. Form Load Events (Con’t) • Bind the Controls to the DataTable • Allows Bound Controls to Display Data from DataTable controlName.DataBindings.Add (“text”, bindingSourceObject, “FieldName”) • “text” must be typed exactly as seen • “FieldName” is the name of the column from the Data Table – typically the name of the field in the Database (unless an alias is created by the SQL command)

34. Sample Code

35. BindingSource Object • BindingSource Object can manipulate through the DataTable • CurrencyManager is NOT Needed • BindingSource.Position • BindingSource.Count • BindingSource.MoveNext( ) • BindingSource.MovePrevious( ) • BindingSource.MoveFirst( ) • BindingSource.MoveLast( )

36. Finalizing Update • EndEdit on the BindingSource • Stops the Editing through the Binding Source bindingSourceObject.EndEdit ( ) • Update the DataAdapter • Send Changes back to Original Data Source dataAdapterObject.Update (dataSetObject, “stringNameOfDataTable”) • Close the Connection connectionObject.Close ( )

37. DataGridView class • The Windows Forms DataGridView control • Provides a user interface to ADO.NET datasets • displays tabular data • allows for updates to the data source

38. Creating DataGridView Program Connection Object DataAdapter Object DataSet Object DataTable Object CommandBuilder Object No BindingSource object needed – Property of DataGridView

39. Sample Code

40. Binding the DataGrid • The DataGrid can receive data from any of the following data sources: • DataTable class • DataView class • DataSet class • DataViewManager class dtgObject.DataSource=dataTable dtgPlayer.DataSource = myPlayerTable

41. Binding the DataGridView

42. Current row and cell • Users can navigate on the DataGridViewer • CurrentRow.Index • Holds zero-based integer for highlighted row • CurrentCell property • Gets or sets which cell has the focus • Contains DataGridViewCell object • Has ColumnNumber and RowNumber properties • Item property holds cell content ‘* display cell contents of third column Console.WriteLine(dtgTeam.Item(dtgTeam.CurrentRowIndex, 2))

43. Data in DataGridViewer • Read Only dtgObject.ReadOnly = True • Storing a column in the DataGridView that you don’t want users to see (eg. Primary key field) dtgObject.Columns(index).Visible = False

44. Data Grid Example - Homework • Use Baseball Database (Baseball3.mdb) • DataGridView Example • Create Form with DataGridView Object • Create Needed Objects (in Code) • Hidden Column (Primary Key) • TeamID Column – Read Only • Update Database on Exit Button • Bonus: User OpenFileDialog box to Select Database