Disconnesso

Imports System.Data.OleDb
Module Module1

Sub Main()


Try



' 1) CONFIGURAZIONE OGGETTI

' ***************************************



' GESTIONE CONNESSIONE

' **********************************

Dim connessione As OleDbConnection
connessione = New OleDbConnection()
connessione.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\AlbergoScrivi.mdb;"
connessione.Open() 'apro connessione


' GESTIONE COMMAND SELECT

' **************************************

Dim comandoSQL As OleDbCommand = New OleDbCommand()
comandoSQL.Connection = connessione
comandoSQL.CommandText = "select * from camere"


' GESTIONE COMMAND INSERT

' ********************************************

Dim inserimentoSQL As OleDbCommand = New OleDbCommand()
inserimentoSQL.Connection = connessione
inserimentoSQL.CommandText = "INSERT INTO camere " & _
"(numeroCamera,tipo,prezzo,piano,televisione,bagno)" & _
"VALUES (?,?,?,?,?,?)"


' COSTRUISCO OGGETTI PARAMETER

'*****************************************************************

Dim parNumero As OleDbParameter = New OleDbParameter("Original_numeroCamera", _
OleDbType.Integer, _
0, _
ParameterDirection.Input, _
False, _
(CType(10, System.Byte)), _
(CType(0, System.Byte)), _
"numeroCamera", _
DataRowVersion.Original, _
Nothing)


Dim parTipo As OleDbParameter = New System.Data.OleDb.OleDbParameter("tipo", System.Data.OleDb.OleDbType.VarWChar, 50, "tipo")
Dim parPiano As OleDbParameter = New OleDbParameter("piano", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, (CType(10, System.Byte)), (CType(0, System.Byte)), "piano", System.Data.DataRowVersion.Current, Nothing)
Dim parPrezzo As OleDbParameter = New OleDbParameter("prezzo", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, (CType(10, System.Byte)), (CType(0, System.Byte)), "prezzo", System.Data.DataRowVersion.Current, Nothing)
Dim parTelevisione As OleDbParameter = New System.Data.OleDb.OleDbParameter("televisione", System.Data.OleDb.OleDbType.Boolean, 2, "televisione")
Dim parBagno As OleDbParameter = New OleDbParameter("bagno", System.Data.OleDb.OleDbType.Boolean, 2, "bagno")

' INSERISCO PARAMETERS NELL'OGGETTO COMMAND PER L'INSERT

inserimentoSQL.Parameters.Add(parNumero)
inserimentoSQL.Parameters.Add(parTipo)
inserimentoSQL.Parameters.Add(parPrezzo)
inserimentoSQL.Parameters.Add(parPiano)
inserimentoSQL.Parameters.Add(parTelevisione)
inserimentoSQL.Parameters.Add(parBagno)


' DATA ADAPTER

' ****************************************

Dim DAdapt As New OleDbDataAdapter
DAdapt.SelectCommand = comandoSQL
DAdapt.InsertCommand = inserimentoSQL


' //DATASET E DATATABLE

Dim ds As New DataSet
Dim Tabella As DataTable = New DataTable("camere")
ds.Tables.Add(Tabella)


' 2) AZIONI:

' *********************************************


' RIEMPIO TABELLA IN DATASET

DAdapt.Fill(ds, "camere")

' VISUALIZZO TABELLA OTTENUTA


'For Each riga As DataRow In Tabella.Rows


' Console.WriteLine(riga(1).ToString())


'Next



' 3) MODIFICO DB IN LOCALE

' **********************************************




' VALORI INSERIMENTO A RUN TIME

' **********************************

Console.WriteLine("******************************")
Console.WriteLine("INSERIMENTO NUOVA RIGA")
Console.WriteLine()
Console.WriteLine("inserire valori")

Console.WriteLine("numero camera")
Dim inp As String = Console.ReadLine()
Dim Ncamera As Integer = CInt(inp)


Console.WriteLine("tipo camera")
Dim TCamera As String = Console.ReadLine()

Console.WriteLine("prezzo camera")
inp = Console.ReadLine()
Dim Prcamera As Integer = CInt(inp)
' Console.WriteLine("prezzo:" & Prcamera)


Console.WriteLine("piano camera")
inp = Console.ReadLine()
Dim Pcamera As Integer = CInt(inp)




' ****************************************


' MODIFICA DB IN LOCALE COI DATI ACQUISITI

'**********************************************

' CREO E AGGIUNGO NUOVA RIGA CON VALORI INSERITI

Dim rigaAggiunta As DataRow = Tabella.NewRow()

' accedo in scrittura alle colonne della nuova riga

' attraverso la sua collection di DataColoumn

rigaAggiunta(0) = Ncamera
rigaAggiunta(1) = TCamera
rigaAggiunta(2) = Prcamera
rigaAggiunta(3) = Pcamera
' Console.WriteLine("valore passato in riga" & rigaAggiunta(3).ToString)


rigaAggiunta(4) = True
rigaAggiunta(5) = True

Tabella.Rows.Add(rigaAggiunta)

' RENDO PERSISTENTI LE MODIFICHE

Dim ris As Integer = DAdapt.Update(ds, "camere")
Console.WriteLine(ris)


'

Catch ECC As DataException
Console.WriteLine(ECC.Message)

Catch OleEcc As OleDbException
Console.WriteLine(OleEcc.Message)

End Try

Console.ReadLine()

End Sub

End Module