SQL处理
1
2 '-------------------------------------------------------
3 '
4 ' 获取数据库连接状态
5 '
6 '
7 '-------------------------------------------------------
8
9
10
11
Public Shared Function GetSQLConnectStatus(ByVal connStr As String) As String
12
13 Dim connectStatus As String
14 Dim DeconnectionString As String = WebSecurity.DeCryptString(connStr)
15
16
17 connectStatus = DeconnectionString
18
19 Return connectStatus
20
End Function
21
22 '-------------------------------------------------------
23 '
24 '处理SQL语句
25 '插入、更新、删除
26 '-------------------------------------------------------
27 Public Shared Function ExecuteNonQuery(ByVal InsertSQL As String) As Integer
28 Dim connStrClass As New connStr
29 Dim connStr As String = connStrClass.ConnectString
30
31 Dim con As New OleDbConnection(connStr)
32 Dim cmd As New OleDbCommand(InsertSQL, con)
33
34
35 Dim Added As Integer
36 Try
37 con.Open()
38
39 Added = cmd.ExecuteNonQuery
40
41
42 Catch err As Exception
43 'lblInfo.Text = "Insert_delay()" & err.Message
44 Finally
45 If (Not con Is Nothing) Then
46 con.Close()
47
48 End If
49 End Try
50
51 Return Added
52
53 End Function
54
55 '-------------------------------------------------------
56 '
57 '处理SQL语句
58 '查询行数
59 '-------------------------------------------------------
60 Public Shared Function ExecuteQuery(ByVal QuerySQL As String) As Integer
61 Dim connStrClass As New connStr
62 Dim connStr As String = connStrClass.ConnectString
63
64 Dim con As New OleDbConnection(connStr)
65 Dim cmd As New OleDbCommand(QuerySQL, con)
66 Dim DataReader As OleDbDataReader
67
68
69 Dim Result As Integer = 0
70 Try
71 con.Open()
72
73 DataReader = cmd.ExecuteReader()
74
75
76 Do While DataReader.Read()
77 Result += 1
78 Loop
79 Catch err As Exception
80 Result = 0
81 Finally
82 If (Not con Is Nothing) Then
83 con.Close()
84
85 End If
86 End Try
87
88 Return Result
89 End Function
90
91 '-------------------------------------------------------
92 '
93 '处理SQL语句
94 '查询
95 '-------------------------------------------------------
96
97 Public Shared Function ExecuteDataReaderQuery(ByVal QuerySQL As String) As OleDbDataReader
98
99 Dim connStrClass As New connStr
100 Dim connStr As String = connStrClass.ConnectString
101
102 Dim con As New OleDbConnection(connStr)
103 Dim cmd As New OleDbCommand(QuerySQL, con)
104 Dim DataReader As OleDbDataReader
105
106
107
108
109 'Try
110 con.Open()
111
112 Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
113
114 'DataReader.Close()
115 'con.Close()
116
117
118
119
120 End Function
121
122
123
124 '-------------------------------------------------------
125 '
126 '处理SQL语句
127 '查询
128 '-------------------------------------------------------
129 Public Shared Function ExecuteDataAdapter(ByVal QuerySQL As String) As DataSet
130
131 Dim connStrClass As New connStr
132 Dim connStr As String = connStrClass.ConnectString
133 Dim con As New OleDbConnection(connStr)
134 Dim cmd As New OleDbCommand(QuerySQL, con)
135 Dim dtAdapter As New OleDbDataAdapter(cmd)
136 Dim ds As New DataSet
137
138 dtAdapter.Fill(ds)
139
140
141 con.Close()
142 con.Dispose()
143
144 Return ds
145 End Function
2
'-------------------------------------------------------3
'4
' 获取数据库连接状态5
'6
'7
'-------------------------------------------------------8
9
10
11
Public Shared Function GetSQLConnectStatus(ByVal connStr As String) As String
12
13
Dim connectStatus As String14
Dim DeconnectionString As String = WebSecurity.DeCryptString(connStr)15
16
17
connectStatus = DeconnectionString18
19
Return connectStatus20
End Function21
22
'-------------------------------------------------------23
'24
'处理SQL语句 25
'插入、更新、删除26
'-------------------------------------------------------27
Public Shared Function ExecuteNonQuery(ByVal InsertSQL As String) As Integer28
Dim connStrClass As New connStr29
Dim connStr As String = connStrClass.ConnectString30
31
Dim con As New OleDbConnection(connStr)32
Dim cmd As New OleDbCommand(InsertSQL, con)33
34
35
Dim Added As Integer36
Try37
con.Open()38
39
Added = cmd.ExecuteNonQuery40
41
42
Catch err As Exception43
'lblInfo.Text = "Insert_delay()" & err.Message44
Finally45
If (Not con Is Nothing) Then46
con.Close()47
48
End If49
End Try50
51
Return Added52
53
End Function54
55
'-------------------------------------------------------56
'57
'处理SQL语句 58
'查询行数59
'-------------------------------------------------------60
Public Shared Function ExecuteQuery(ByVal QuerySQL As String) As Integer61
Dim connStrClass As New connStr62
Dim connStr As String = connStrClass.ConnectString63
64
Dim con As New OleDbConnection(connStr)65
Dim cmd As New OleDbCommand(QuerySQL, con)66
Dim DataReader As OleDbDataReader67
68
69
Dim Result As Integer = 070
Try71
con.Open()72
73
DataReader = cmd.ExecuteReader()74
75
76
Do While DataReader.Read()77
Result += 178
Loop79
Catch err As Exception80
Result = 081
Finally82
If (Not con Is Nothing) Then83
con.Close()84
85
End If86
End Try87
88
Return Result89
End Function90
91
'-------------------------------------------------------92
'93
'处理SQL语句 94
'查询95
'-------------------------------------------------------96
97
Public Shared Function ExecuteDataReaderQuery(ByVal QuerySQL As String) As OleDbDataReader98
99
Dim connStrClass As New connStr100
Dim connStr As String = connStrClass.ConnectString101
102
Dim con As New OleDbConnection(connStr)103
Dim cmd As New OleDbCommand(QuerySQL, con)104
Dim DataReader As OleDbDataReader105
106
107
108
109
'Try110
con.Open()111
112
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)113
114
'DataReader.Close()115
'con.Close()116
117
118
119
120
End Function121
122
123
124
'-------------------------------------------------------125
'126
'处理SQL语句 127
'查询128
'-------------------------------------------------------129
Public Shared Function ExecuteDataAdapter(ByVal QuerySQL As String) As DataSet130
131
Dim connStrClass As New connStr132
Dim connStr As String = connStrClass.ConnectString133
Dim con As New OleDbConnection(connStr)134
Dim cmd As New OleDbCommand(QuerySQL, con)135
Dim dtAdapter As New OleDbDataAdapter(cmd)136
Dim ds As New DataSet137
138
dtAdapter.Fill(ds)139
140
141
con.Close()142
con.Dispose()143
144
Return ds145
End Function
