Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class clsDatabase
Private objConn As SqlConnection
Private objCmd As SqlCommand
Private Trans As SqlTransaction
Private strConnString As String
Public Sub New()
strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
End Sub
Public Function QueryDataReader(ByVal strSQL As String) As SqlDataReader
Dim dtReader As SqlDataReader
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New SqlCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
Return dtReader '*** Return DataReader ***'
End Function
Public Function QueryDataSet(ByVal strSQL As String) As DataSet
Dim ds As New DataSet
Dim dtAdapter As New SqlDataAdapter
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New SqlCommand
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds)
Return ds '*** Return DataSet ***'
End Function
Public Function QueryDataTable(ByVal strSQL As String) As DataTable
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
Return dt '*** Return DataTable ***'
End Function
Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New SqlCommand
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Return True '*** Return True ***'
Catch ex As Exception
Return False '*** Return False ***'
End Try
End Function
Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
Dim obj As Object
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New SqlCommand
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
obj = objCmd.ExecuteScalar() '*** Return Scalar ***'
Return obj
Catch ex As Exception
Return Nothing '*** Return Nothing ***'
End Try
End Function
Public Function TransStart()
objConn = New SqlConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
End Function
Public Function TransExecute(ByVal strSQL As String) As Boolean
objCmd = New SqlCommand
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
End Function
Public Function TransRollBack()
Trans.Rollback()
End Function
Public Function TransCommit()
Trans.Commit()
End Function
Public Sub Close()
objConn.Close()
objConn = Nothing
End Sub
End Class
#Region ".NET base class name space imports"
Imports System.Collections.Generic
Imports Microsoft.VisualBasic
Imports System
Imports System.Security
Imports System.Security.Permissions
Imports System.Threading
#End Region
Public Class Caragent
Dim add_car As New addCar
Public Function addCaragent(ByVal clsadditem As clscar) 'เพิ่มข้อมูล
Try
Return add_car.caradd(clsadditem)
Catch bex As Exception
Throw New Exception(bex.Message)
End Try
End Function
Public Function carupdateagent(ByVal clsadditem As clscar) 'แก้ไข
Try
Return add_car.carcheck_Up(clsadditem)
Catch bex As Exception
Throw New Exception(bex.Message)
End Try
End Function
Public Function carlistagent(ByVal clsadditem As clscar) As DataTable 'ค้นหา'
Try
Return add_car.carcheck_Sea(clsadditem).Tables(0)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function carcheckagent(ByVal clsadditem As clscar) As DataTable 'เช็คID'
Try
Return add_car.carcheckMe(clsadditem).Tables(0)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function cardeleteagent(ByVal clsadditem As clscar) 'ลบ'
Try
Return add_car.cardeletemem(clsadditem)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
End Class
อันนี้ คือ component
#Region "using .net class namespace"
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Common
#End Region
Public Class addCar
Public Function caradd(ByVal clsadditem As clscar) As Boolean
Dim db As Database
Dim Dbcmd As DbCommand
Dim spName As String = "addCar" 'Declare Store Procedure''เพิ่มข้อมูล
Try
db = DatabaseFactory.CreateDatabase() 'Use Database From Default'
Dbcmd = db.GetStoredProcCommand(spName)
db.AddInParameter(Dbcmd, "@ID_car", DbType.String, clsadditem.ID_CAR)
db.AddInParameter(Dbcmd, "@NAME_CUSTOMER", DbType.String, clsadditem.NAME_CUSTOMER)
db.AddInParameter(Dbcmd, "@LASTNAME_CUSTOMER", DbType.String, clsadditem.LASTNAME_CUSTOMER)
db.AddInParameter(Dbcmd, "@NAME_CAR", DbType.String, clsadditem.NAME_CAR)
db.AddInParameter(Dbcmd, "@KIND", DbType.String, clsadditem.KIND)
db.AddInParameter(Dbcmd, "@GENERATION_CAR", DbType.String, clsadditem.GENERATION_CAR)
db.AddInParameter(Dbcmd, "@YEAR_CAR", DbType.String, clsadditem.YEAR_CAR)
db.AddInParameter(Dbcmd, "@ENGINE", DbType.String, clsadditem.ENGINE)
db.AddInParameter(Dbcmd, "@COLOR_CAR", DbType.String, clsadditem.COLOR_CAR)
db.ExecuteNonQuery(Dbcmd)
Return True
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function carcheck_Up(ByVal update As clscar) As Boolean
Dim db As Database
Dim Dbcmd As DbCommand
Dim spName As String = "UPDATECARNAME" 'Declare Store Procedure''แก้ไขข้อมูล
Try
db = DatabaseFactory.CreateDatabase() 'Use Database From Default'
Dbcmd = db.GetStoredProcCommand(spName)
db.AddInParameter(Dbcmd, "@ID_car", DbType.String, update.ID_CAR)
db.AddInParameter(Dbcmd, "@NAME_CUSTOMER", DbType.String, update.NAME_CUSTOMER)
db.AddInParameter(Dbcmd, "@LASTNAME_CUSTOMER", DbType.String, update.LASTNAME_CUSTOMER)
db.AddInParameter(Dbcmd, "@NAME_CAR", DbType.String, update.NAME_CAR)
db.AddInParameter(Dbcmd, "@KIND", DbType.String, update.KIND)
db.AddInParameter(Dbcmd, "@GENERATION_CAR", DbType.String, update.GENERATION_CAR)
db.AddInParameter(Dbcmd, "@YEAR_CAR", DbType.String, update.YEAR_CAR)
db.AddInParameter(Dbcmd, "@ENGINE", DbType.String, update.ENGINE)
db.AddInParameter(Dbcmd, "@COLOR_CAR", DbType.String, update.COLOR_CAR)
db.ExecuteNonQuery(Dbcmd)
Return True
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function carcheck_Sea(ByVal clsadditem As clscar) As System.Data.DataSet 'Seach
Dim db As Database 'ตรวจสอบรหัส
Dim Dbcmd As DbCommand
Dim spName As String = "search_CAR" 'Declare Store Procedure
Try
db = DatabaseFactory.CreateDatabase() 'Use Database from Default ConnectionString
Dbcmd = db.GetStoredProcCommand(spName)
db.AddInParameter(Dbcmd, "@ID_SEARCHCAR", DbType.String, clsadditem.ID_CAR)
db.ExecuteNonQuery(Dbcmd)
Return db.ExecuteDataSet(Dbcmd)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function carcheckMe(ByVal clsadditem As clscar) As System.Data.DataSet 'เช็คID'
Dim db As Database 'ตรวจสอบรหัส
Dim Dbcmd As DbCommand
Dim spName As String = "check_Car" 'Declare Store Procedure
Try
db = DatabaseFactory.CreateDatabase() 'Use Database from Default ConnectionString
Dbcmd = db.GetStoredProcCommand(spName)
db.AddInParameter(Dbcmd, "@ID_CAR", DbType.String, clsadditem.ID_CAR)
db.ExecuteNonQuery(Dbcmd)
Return db.ExecuteDataSet(Dbcmd)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Public Function cardeletemem(ByVal clsadditem As clscar) As Boolean 'ลบ'
Dim db As Database
Dim Dbcmd As DbCommand
Dim spName As String = "deleteCAR" 'Declare Store Procedure
Try
db = DatabaseFactory.CreateDatabase() 'Use Database from Default ConnectionString
Dbcmd = db.GetStoredProcCommand(spName)
db.AddInParameter(Dbcmd, "@ID_DELETE", DbType.String, clsadditem.ID_CAR)
db.ExecuteNonQuery(Dbcmd)
Return True
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
End Class
และ อันนี้ คือ entity
Public Class clscar
Private pID_CAR As String
Private pNAME_CUSTOMER As String
Private pLASTNAME_CUSTOMER As String
Private pNAME_CAR As String
Private pKIND As String
Private pGENERATION_CAR As String
Private pYEAR_CAR As String
Private pENGINE As String
Private pCOLOR_CAR As String
#Region "Properties of clscar"
Public Property ID_CAR() As String
Get
Return pID_CAR
End Get
Set(ByVal value As String)
pID_CAR = value
End Set
End Property
Public Property NAME_CUSTOMER() As String
Get
Return pNAME_CUSTOMER
End Get
Set(ByVal value As String)
pNAME_CUSTOMER = value
End Set
End Property
Public Property LASTNAME_CUSTOMER() As String
Get
Return pLASTNAME_CUSTOMER
End Get
Set(ByVal value As String)
pLASTNAME_CUSTOMER = value
End Set
End Property
Public Property YEAR_CAR() As String
Get
Return pYEAR_CAR
End Get
Set(ByVal value As String)
pYEAR_CAR = value
End Set
End Property
Public Property GENERATION_CAR() As String
Get
Return pGENERATION_CAR
End Get
Set(ByVal value As String)
pGENERATION_CAR = value
End Set
End Property
Public Property ENGINE() As String
Get
Return pENGINE
End Get
Set(ByVal value As String)
pENGINE = value
End Set
End Property
Public Property COLOR_CAR() As String
Get
Return pCOLOR_CAR
End Get
Set(ByVal value As String)
pCOLOR_CAR = value
End Set
End Property
Public Property NAME_CAR() As String
Get
Return pNAME_CAR
End Get
Set(ByVal value As String)
pNAME_CAR = value
End Set
End Property
Public Property KIND() As String
Get
Return pKIND
End Get
Set(ByVal value As String)
pKIND = value
End Set
End Property
#End Region
End Class