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
|
