REM
REM Copyright(c) Vieka 2004
REM
REM Vieka eSQL.Net sample code
REM
REM Test basic database operations
REM

Imports System.Data
Imports Microsoft.VisualBasic

#If ESQL_NETCF Then
Imports System.Windows.Forms
#End If

Imports eSQL

Public Class Test

#If ESQL_NETCF Then
	Public Shared Sub WriteLine(s As String)
        MessageBox.Show(s)
    End Sub
#Else
    Public Shared Sub WriteLine(ByVal s As String)
        Console.WriteLine(s)
    End Sub
#End If

    Public Shared Sub TestCreate()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")
        conn.Open()

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "CREATE TABLE test (a int, b varchar (20));"

        Dim nRecordsAffected As Integer = cmd.ExecuteNonQuery()

        conn.Close()
    End Sub

    Public Shared Sub TestInsert()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")
        conn.Open()

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "INSERT INTO test (a, b) Values(1, 'column b');"

        Dim nRecordsAffected As Integer = cmd.ExecuteNonQuery()

        conn.Close()
    End Sub

    Public Shared Sub TestDelete()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")
        conn.Open()

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "DELETE FROM test;"

        Dim nRecordsAffected As Integer = cmd.ExecuteNonQuery()

        conn.Close()

    End Sub

    Public Shared Sub TestDrop()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")
        conn.Open()

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "DROP TABLE test;"

        Dim nRecordsAffected As Integer = cmd.ExecuteNonQuery()

        conn.Close()

    End Sub

    Public Shared Sub TestSelect()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "SELECT * FROM test;"

        conn.Open()

        Dim reader As SqlDataReader = cmd.ExecuteReader()

        Do While reader.Read()
            WriteLine(String.Format(vbTab & "{0}" & vbTab & "{1}", reader.GetInt32(0), reader.GetString(1)))
        Loop

        reader.Close()
        conn.Close()
    End Sub

    Public Shared Sub TestSelectAgain()
        Dim conn As SqlConnection = New SqlConnection("DSN=anyname;Database=\animal.db")

        Dim cmd As SqlCommand = conn.CreateCommand()

        cmd.CommandText = "SELECT * FROM word;"

        conn.Open()

        Dim reader As SqlDataReader = cmd.ExecuteReader()

        Dim nCount As Integer = 0

        Do While reader.Read()
            nCount = nCount + 1
#If ESQL_NETCF Then
            REM WriteLine(String.Format(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2)))
#Else
            WriteLine(String.Format(vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2)))
#End If
        Loop

        WriteLine("Total records: " & nCount)

        reader.Close()
        conn.Close()

    End Sub

    Public Shared Sub Main()
        TestCreate()
        TestInsert()
        TestSelect()
        TestDelete()
        TestDrop()

        TestSelectAgain()
    End Sub

End Class