Thursday, August 11, 2011

Table Schema

  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
Imports System.Data
Imports System.Data.Odbc
Imports System.IO

Public Class Table_Schema

Dim objConn As OdbcConnection
Dim objCmd As OdbcCommand

Private Sub Table_Schema_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim strConnString As String
strConnString = "Driver={MySQL ODBC 5.1 Driver};database=information_schema;option=3;server=5.46.208.197;port=3306;uid=app.user;pwd=user.1275"
objConn = New OdbcConnection(strConnString)
objConn.Open()
'insertClientName("grace")

' MsgBox(searchClientID("012 Smile").Tables(0).Rows.Count)
End Sub

'***insert client name***
Public Function insertClientName(ByVal clientname As String) As DataSet
Dim sql As String = "INSERT INTO carrierdetails (clientName) values ('" & clientname & "')"
Return sqldataset(sql)
End Function

Public Function sqldataset(ByVal sqlstr As String) As DataSet
Dim ds As New DataSet
Dim ssql As String = (sqlstr)
Dim cmd As OdbcCommand = New OdbcCommand(ssql, objConn)
Dim ad As OdbcDataAdapter = New OdbcDataAdapter
ad.SelectCommand = cmd
'objConn.Open()
ad.Fill(ds, "a")
objConn.Close()
Return ds
End Function

'***seach client ID***
Public Function searchClientID(ByVal clientname As String) As DataSet
Dim sql As String = "SELECT carrierDetailsID FROM carrierDetails WHERE clientname = '" & clientname & "'"
Return sqldataset(sql)
End Function


Private Sub btnMagic_Click(sender As System.Object, e As System.EventArgs) Handles btnMagic.Click
Dim table As String = txtTable.Text

txtInsert.Text = insertQuery(table)
txtUpdate.Text = updateQuery(table)
txtFunction.Text = functionQuery(table)


End Sub

Public Function insertQuery(ByVal table As String) As String
'*********INSERT*********'
Dim f As String = "("
Dim v As String = "("
Dim sql As String = "select column_name from columns where table_name='" & table & "'"
Dim insert As String = "INSERT INTO " & table & " "
Dim thisCommand As New OdbcCommand(sql, objConn)


Dim thisReader As OdbcDataReader = thisCommand.ExecuteReader()

While thisReader.Read
f = f & thisReader.GetString(0) & ", "
v = v & "'" & Chr(34) & " & " & thisReader.GetString(0) & " & " & Chr(34) & "', "
End While

thisReader.Close()

f = f.Remove(f.LastIndexOf(","), 1)
v = v.Remove(v.LastIndexOf(","), 1)
f = f & ")"
v = v & ")"

Dim fullinsert As String
fullinsert = insert & f & " values " & v

Return fullinsert

End Function

Public Function updateQuery(ByVal table As String) As String
'********UPDATE********'
Dim f As String

Dim sql As String = "select column_name from columns where table_name='" & table & "'"
Dim update As String = "UPDATE " & table & " SET "
Dim thisCommand As New OdbcCommand(sql, objConn)

Dim thisReader As OdbcDataReader = thisCommand.ExecuteReader()

While thisReader.Read
f = f & thisReader.GetString(0) & " = '" & Chr(34) & " & " & thisReader.GetString(0) & " & " & Chr(34) & "', "
End While

thisReader.Close()

f = f.Remove(f.LastIndexOf(","), 1)

Dim fullupdate As String
fullupdate = update & f & " WHERE "
Return fullupdate

End Function

Public Function functionQuery(ByVal table As String) As String
Dim f As String = "("

Dim sql As String = "select column_name from columns where table_name='" & table & "'"
Dim sqlfunction As String = "Public Function " & table
Dim thisCommand As New OdbcCommand(sql, objConn)

Dim thisReader As OdbcDataReader = thisCommand.ExecuteReader()

While thisReader.Read
f = f & "ByVal " & thisReader.GetString(0) & " As String, "
End While

thisReader.Close()

f = f.Remove(f.LastIndexOf(","), 1)

Dim fullfunction As String
fullfunction = sqlfunction & f & ") As DataSet"

Return fullfunction

End Function

End Class