Ado net in vb net
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

ADO.NET in VB.NET PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on
  • Presentation posted in: General

ADO.NET in VB.NET. ITE 370. 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:

Download Presentation

ADO.NET in VB.NET

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ado net in vb net

ADO.NET in VB.NET

ITE 370

University of South Alabama School of CIS


What is ado net

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


Providers and their namespaces

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


How clients net data providers dbmss fit together

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


Types of ado net objects

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


Working with udl files to build connection strings

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


Datareader class

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


Datareader example

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


Dataset w multiple tables providers

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


Dataadapter class

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


Dataadapter command properties

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


Dataadapter command properties cont

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


Dataadapter fill method

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


Dataadapter update method

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


Datagrid class

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


Binding the datagrid

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


Current row and cell

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


Datagrid properties

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


Hiding a column in datagrid

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


  • Login