Full acces to a database VB.Net Exercise - Visual Basic Programming Course


 Exercise

Full acces to a database

Objetive

Create a program that allows the user to enter information about books and browse the existing data. It should handle the case where the data file does not exist when the program starts.

Code

Imports System
Imports System.Data.SQLite
Imports System.IO
Class Program
    Private Shared Sub Main(ByVal args As String())
        Dim finish As Boolean = False
        Dim [option] As String
        Dim title, author, genere, sumary As String
        Dim cmd As SQLiteCommand
        Dim conection As SQLiteConnection = New SQLiteConnection("Data Source=ejemplo01.sqlite;Version=3;New=True;Compress=True;")
        conection.Open()

        If Not File.Exists("ejemplo01.sqlite") Then
            Console.WriteLine("Creando la base de datos...")
        End If

        Dim creacion As String = "CREATE TABLE IF NOT EXISTS books(title varchar(20)," & " author varchar(20),genere varchar(20), sumary varchar(20));"
        cmd = New SQLiteCommand(creacion, conection)
        cmd.ExecuteNonQuery()

        Do
            Console.WriteLine()
            Console.WriteLine("Books database")
            Console.WriteLine()
            Console.WriteLine("1.- Add a new Book")
            Console.WriteLine("2.- View all Books")
            Console.WriteLine("0.- Exit")
            Console.WriteLine()
            Console.Write("Choose an Option: ")
            [option] = Console.ReadLine()

            Select Case [option]
                Case "0"
                    finish = True
                Case "1"
                    Dim continueOption As String

                    Do
                        Console.Write("Enter the title: ")
                        title = Console.ReadLine()
                        Console.Write("Enter the author: ")
                        author = Console.ReadLine()
                        Console.Write("Enter the genere: ")
                        genere = Console.ReadLine()
                        Console.Write("Enter the sumary: ")
                        sumary = Console.ReadLine()
                        Dim insercion As String = "insert into books values ('" & title & "'," & " '" & author & "', '" & genere & "', '" & sumary & "');"
                        cmd = New SQLiteCommand(insercion, conection)
                        Dim cantidad As Integer = cmd.ExecuteNonQuery()
                        If cantidad < 1 Then Console.WriteLine("Insert Fails")
                        Console.Write("Enter another book (y/n): ")
                        continueOption = Console.ReadLine()
                    Loop While continueOption.ToString().ToLower() = "y"

                Case "2"
                    Dim consulta As String = "select * from books"
                    cmd = New SQLiteCommand(consulta, conection)
                    Dim datos As SQLiteDataReader = cmd.ExecuteReader()
                    Dim rowCount As Integer = 1
                    Console.WriteLine()

                    While datos.Read()
                        title = Convert.ToString(datos(0))
                        author = Convert.ToString(datos(1))
                        genere = Convert.ToString(datos(2))
                        sumary = Convert.ToString(datos(3))
                        System.Console.WriteLine("{0}- Title: {1}," & " Author: {2}, Genere: {3}, Sumary: {4}", rowCount, title, author, genere, sumary)
                        rowCount += 1
                    End While

                    Console.WriteLine()
            End Select
        Loop While Not finish

        Console.WriteLine("Bye!!")
        conection.Close()
    End Sub
End Class


Juan A. Ripoll - Systems Tutorials and Programming Courses © 2024 All rights reserved.  Legal Conditions.