FAW1zgCx

1    Public Function CheckDatabaseExists()
2        konekAwal()
3        Dim bRet As Boolean = False
4        Dim cmdText As String = ("select * from master.dbo.sysdatabases where name='" + (NmDBase + "'"))
5        Using sqlCmd As SqlCommand = New SqlCommand(cmdText, conAwal)
6            Using reader As SqlDataReader = sqlCmd.ExecuteReader
7                bRet = reader.HasRows
8                If bRet = True Then
9                    'MsgBox("Database sudah ada")
10                    Cek_Table()
11                Else
12                    'MsgBox("Database belum ada")
13                    BuatDatabase()
14                End If
15            End Using
16        End Using
17        conAwal.Close()
18        Return bRet
19    End Function
20
21    Public Sub Cek_Table()
22        NMTabel = "DB_User"
23        TQuery = "CREATE TABLE dbo." + NMTabel + "(" +
24            "Username varchar(50) NOT NULL, " +
25            "Password varchar(50) NOT NULL, " +
26            "Lvl_User varchar(50) NOT NULL,	" +
27            "GroupID varchar(50) NOT NULL, " +
28            "NRank numeric(18, 0) NULL, " +
29            "Delable varchar(1) NOT NULL, " +
30            "Editable varchar(1) NOT NULL, " +
31            "Status varchar(5) NOT NULL)"
32        DoesTableExist(NMTabel, "tabel", TQuery)
33    End Sub
34
35    Public Sub BuatDatabase()
36        konekAwal()
37        TQuery = "CREATE DATABASE " + NmDBase
38        Using sqlCmd As SqlCommand = New SqlCommand(TQuery, conAwal)
39            Try
40                sqlCmd.CommandType = CommandType.Text
41                sqlCmd.CommandText = TQuery
42                sqlCmd.ExecuteNonQuery()
43                con.Close()
44                Cek_Table()
45            Catch ex As Exception
46                MsgBox("Database sudah ada !")
47                conAwal.Close()
48                Exit Sub
49            End Try
50        End Using
51    End Sub
52
53    Public Function DoesTableExist(tblName As String, Maksud As String, TQuery As String) As Boolean
54        Try
55            Koneksi()
56            ' http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx
57
58            Dim restrictions(3) As String
59            restrictions(2) = tblName
60            Dim dbTbl As DataTable = con2.GetSchema("Tables", restrictions)
61
62            If dbTbl.Rows.Count = 0 Then
63                If Maksud = "View" Then
64                    BuatdboView(tblName, TQuery)
65                Else
66                    BuatTable(tblName, TQuery)
67                End If
68            Else
69                'Table exists
70                DoesTableExist = True
71                'MsgBox("Tabel : " & tblName & " sudah ada !")
72            End If
73            dbTbl.Dispose()
74            con.Close()
75        Catch ex As Exception
76            MsgBox(ex.Message)
77            End
78        End Try
79    End Function
80
81    Public Sub BuatTable(tblName As String, Tquery As String)
82        Koneksi()
83        Try
84            Using cmd = New SqlCommand(Tquery, con)
85                cmd.ExecuteNonQuery()
86            End Using
87            con.Close()
88        Catch ex As Exception
89            MsgBox(tblName & " Gagal dibuat !", vbCritical, "Build Table")
90            Exit Sub
91        End Try
92    End Sub