Transcript Lab10

Lab 10
Working With a Database
McGraw-Hill
© 2006 The McGraw-Hill Companies, Inc. All rights reserved.
Database Concept
Database: An integrated collection of
data.
Table: One data file (Set of records).
Dbase: Collection of tables.
Record: Set of fields.
Field: Set of characters.
Index: A key field with unique values for
every record.
Database Concept …[CONT]
Flat-File (Table)
Row - Record
Fields - Columns
Database Concept …[CONT]
 Relational Database: Collection of tables in which no
duplicate data is found.
 Database file Extension: FileName.MDB
 Ms-Database(MDB) Contents:
 Tables
 Reports
 Forms
 Queries
ADO.Net
 ADO : ActiveX Data Object
 Enables connection with the databases.
 Each database type has its own protocol:
 (ODBC for SQL Server.
 OleDb for Ms-Access
 The .Net Provider: provides a common way for handling
with data bases.
ADO.Net…..Cont
 Data Set: A memory resident representation of data.
 Data Reader: read-only ADO record set
 Data Adapter: used to populate data reader.
Linking to a Database
Imports System.Data
Imports System.Data.OleDb
Variables
Public Class Form1
Dim ConStr As String =
"Provider=Microsoft.Jet.OLEDB 4.0;" & _
"Data Source =" & Application.StartupPath & "
\db1.mdb"
DB Path
Dim Conn As New OleDbConnection(ConStr)
Dim dataset1 As DataSet
Dim SQLstr As String = "SELECT * FROM Table1“
End Class
Copy Table Data to the DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Conn.Open()
Dim DataAdapter1 As New
OleDbDataAdapter(SQLstr, Conn)
DataAdapter1.Fill(dataset1, "Table1")
Conn.Close()
End Sub
Move Data to Form Control
Moves data from Name, City, Mobile which are in
Table1 to TextBox1 ….
Navigating Records/ Selecting first
record
Navigating Records/
Selecting the next record
Navigating Records/
Selecting Previous record
Navigating Records/
Selecting Last record
Me.BindingContext(DataSet1, “Table1”). Position =
Me.BindingContext(DataSet1, “table1”).Count - 1
Add New Record
SavInto.Connection = Conn
SavInto.CommandType = CommandType.Text
SavInto.CommandText = "INSERT INTO Table1(Name,City,Mobile)" & _
"values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text &
"') "
Conn.Open()
SavInto.ExecuteNonQuery()
Conn.Close()
Delete Record
Dim SavInto As New OleDb.OleDbCommand
Dim DataAdapter1 As New OleDbDataAdapter(SQLstr, Conn)
SavInto.Connection = Conn
SavInto.CommandType = CommandType.Text
SavInto.CommandText = "DELETE FROM Table1 WHERE mobile
='" & _
Trim(TextBox3.Text) & "'"
Conn.Open()
SavInto.ExecuteNonQuery()
DataSet1.Clear()
DataAdapter1.Fill(DataSet1, "Table1")
Conn.Close()
MsgBox("IJBKL MJNJOPDE QRSJT U cdXE ZO^_S `a")
ChangePosition()
End