'A way to connect to SQL with VB.Net using Classes
'This must be a class on its own
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Public Class frmConn
Public Shared Function ConnSQL() As SqlConnection
Dim connectionString As String
Dim cnn As SqlConnection
connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\test_db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\PC4\My Documents\Visual Studio 2008\Projects\morevariables\morevariables\App_Data\test_db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'connetionString = "Data Source=test_db;Initial Catalog=items_table;User ID=;Password="
cnn = New SqlConnection(connectionString)
Try
cnn.Open()
Return cnn
Catch ex As Exception
MsgBox("Can not open connection ! ")
Finally
cnn.Close()
End Try
End Function
End Class
'Select another new class
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Net
Imports System.Net.Mail
Imports System.IO
Imports System.Text
'The class
Public Class ClassClass
Public Shared Function SelectRows(ByVal query As String) As DataTable
On Error Resume Next
Dim Conn As New SqlConnection
Conn = frmConn.ConnSQL
Dim da As New SqlDataAdapter
Dim dt As New DataTable
dt.Clear()
da.SelectCommand = New SqlCommand(query, Conn)
'Try
da.Fill(dt)
Return dt
'Catch ex As Exception
' 'MsgBox(ex.Message)
'End Try
End Function
'Function for inserting
Public Shared Sub SQL_Insert(ByVal sql_Insert1 As String, ByVal sql_Insert2 As String)
Dim Conn As SqlConnection
Conn = frmConn.ConnSQL
Conn.Close()
Conn.Open()
Dim sqlcmd_Insert As New SqlCommand(sql_Insert1 + sql_Insert2, Conn)
Try
sqlcmd_Insert.ExecuteNonQuery()
'MsgBox("The New Record has been saved.")
Catch ex As Exception
'MsgBox(ex.Message)
Finally
Conn.Close()
End Try
End Sub
'Function for updating
Public Shared Sub SQL_Update(ByVal sql_Update As String)
Dim Conn As SqlConnection
'Conn_web = formz.connec.ConnSQL
Conn = frmConn.ConnSQL
Conn.Close()
Conn.Open()
Dim sqlcmd_Update As New SqlCommand(sql_Update, Conn)
Try
sqlcmd_Update.ExecuteNonQuery()
'MsgBox("The Current Record has been saved.")
Catch ex As Exception
MsgBox(ex.Message)
Finally
Conn.Close()
End Try
End Sub
'Function for deleting
Public Shared Sub Delete_Str(ByVal sql_Delete As String)
Dim Conn As SqlConnection
'Conn_web = formz.connec.ConnSQL
Conn = frmConn.ConnSQL
Conn.Close()
Conn.Open()
Dim sqlcmd_Delete As New SqlCommand(sql_Delete, Conn)
Try
sqlcmd_Delete.ExecuteNonQuery()
MsgBox("The Selected Record has been Deleted.")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
