Wednesday, May 12, 2010

Run Simple SQL queries from VB.NET


I recently had to create an application that updates SQL tables. But I needed a user to be able to execute it from his/her system. This application simply purges applications from a context Menu running in the system tray.


Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Public Class frmService
Dim oSQLConn As SqlConnection = New SqlConnection()
Dim SilentMode, DataSource, InitialCatalog, Username, Password, Trusted

Dim ds As DataSet

Dim sql
Dim conn
Dim Comm
Dim dataadapter As SqlDataAdapter
Dim Connstr As String
Sub UpdateStats()
oSQLConn.ConnectionString = _
DataSource & _
InitialCatalog & _
Username & _
Password & _
Trusted
End Sub
Private Sub EToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
UpdateStats()
End Sub
Sub RefreshMe()

End Sub

Private Sub frmService_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
SystrayMenuContext.ShowBalloonTip(2000)

Init()

Connstr = _
DataSource & _
InitialCatalog & _
Username & _
Password & _
Trusted

RefreshMe()
End Sub

Sub LoadSchedule()

End Sub

Sub OpenValidConnection()
oSQLConn.ConnectionString = _
DataSource & _
InitialCatalog & _
Username & _
Password & _
Trusted
End Sub

Sub Init()
Try
Dim fst = New FileStream(Environ("WINDIR") & "\config.ini", FileMode.Open, FileAccess.Read)
Dim dt As New StreamReader(fst)
dt.BaseStream.Seek(0, SeekOrigin.Begin)

While dt.Peek() > -1
DataSource = dt.ReadLine() 'name of server
InitialCatalog = dt.ReadLine()
Username = dt.ReadLine()
Password = dt.ReadLine()
Trusted = dt.ReadLine()

End While
dt.Close()
Catch
'MsgBox("Sorry, but the config.ini is unaccessible. Check your path to verify that " & Environ("WINDIR") & "\config.ini is accessible. Using Default Settings.", MsgBoxStyle.Information, "Connection Problem")
DataSource = "Data Source=ndataameif.mil;" 'name of server
InitialCatalog = "Initial Catalog=ctlg1;"
Username = "User Id=sa;"
Password = "Password=S*S*S*S!; "
Trusted = "Trusted_Connection=False;"
End Try
End Sub

Private Sub ToolStripMenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem2.Click
Dim EXP
EXP = 0

Dim XAttribute
OpenValidConnection()
oSQLConn.Open()
Dim SqlCommand2 As String
SqlCommand2 = "SELECT COUNT(*) AS Counter FROM dms_doc WHERE o_mimetype LIKE '%unknown%' AND o_filename LIKE '%.pdf%'"
Dim command2 As New SqlCommand(SqlCommand2, oSQLConn)
Dim dr2 As SqlDataReader

dr2 = command2.ExecuteReader()
Do While dr2.Read()
EXP = dr2("Counter")
Loop

dr2.Close()


XAttribute = MsgBox("" & EXP & " records found. Are you Sure you want to correct MIME type for PDF?", MsgBoxStyle.YesNo, "BentleyPurge Application")

oSQLConn.Close()

If XAttribute = 6 Then
Try
OpenValidConnection()
oSQLConn.Open()
Dim SqlCommand1 As String
SqlCommand1 = "UPDATE dms_doc SET dms_doc.o_mimetype = 'application/pdf' WHERE (o_mimetype LIKE '%unknown%') AND (o_filename LIKE '%.pdf%')"
Dim command As New SqlCommand(SqlCommand1, oSQLConn)
Dim dr As SqlDataReader
dr = command.ExecuteReader()
oSQLConn.Close()
MsgBox("dms_doc Table -> PDF has been updated!", MsgBoxStyle.Information, "Success")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
End Try
End If





End Sub


Private Sub ExitServiceToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitServiceToolStripMenuItem.Click
Dim XAttribute
XAttribute = MsgBox("Would you like to exit BentleyPurge Service", MsgBoxStyle.YesNo, "BentleyPurge Application")
If XAttribute = 6 Then End
End Sub

Private Sub ContextMenuStrip1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles ContextMenuStrip1.DoubleClick
SystrayMenuContext.ShowBalloonTip(2000)
End Sub


End Class

No comments:

Post a Comment