Vatansever
Asistan
- Katılım
- 23 Ağustos 2007
- Mesajlar
- 405
- Reaksiyon puanı
- 1
- Puanları
- 18
Arkadaşlar elimde kendi programındaki access database'ni ve Luna 3.0.12.18 isimli programı kullanarak bir DAL kodu oluşturdum ama nasıl kullanacağımı tam anlayamadım yardım edermisiniz database'ye bilgiyi nasıl giricem güncelliycem silicem
LunaBaseClass içindeki kod
blgBank içindeki kod
blgBankDAO içindeki kod
LunaBaseClass içindeki kod
Kod:
#Region "Author"
'Class created with Luna 3.0.12.18
'Author: Diego Lunadei
'Date: 11/6/2011
#End Region
Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Data
Imports System.Data.OleDb
Namespace LUNA
Public Enum enLogicOperator
enAND = 0
enOR
End Enum
Public MustInherit Class LunaBaseClass
Protected Shared _cn As OleDb.OleDbConnection
Protected Shared _ConnectionString As String = String.Empty
Public Sub ManageError(ByVal ex As Exception)
End Sub
End Class
Public MustInherit Class LunaBaseClassDAO(Of T)
Inherits LunaBaseClass
Public Sub New()
'By default use ConnectionString in AppSettings
_ConnectionString = My.Settings("ConnectionString").ToString
OpenDBConnection()
End Sub
Public Sub New(ByVal Connection As Data.OleDb.OleDbConnection)
_cn = Connection
OpenDBConnection()
End Sub
Public Sub New(ByVal ConnectionString As string)
if ConnectionString.Length <>0 then
_ConnectionString = ConnectionString
OpenDBConnection()
End if
End Sub
Public MustOverride Function Read(ByVal Id As Integer) As T
Public MustOverride Function Save(ByRef obj As T) As Integer
Public MustOverride Sub Delete(ByVal Id As Integer)
Public MustOverride Sub Delete(ByRef obj As T, Optional ByRef ListaObj As List(Of T) = Nothing)
Public MustOverride Function Find(ByVal ParamArray Parameter() As Luna.LunaSearchParameter) As IEnumerable(Of T)
Public MustOverride Function GetAll(Optional ByVal OrderByField As String = "", Optional ByVal AddEmptyItem As Boolean = False) As IEnumerable(Of T)
Protected Function OpenDBConnection() As Integer
Dim Ris As Integer = 0
Try
If _cn Is Nothing Then _cn = New OleDb.OleDbConnection
if _cn.ConnectionString.Length =0 then _cn.ConnectionString = _ConnectionString
If _cn.State <> Data.ConnectionState.Open Then _cn.Open()
Catch ex As Exception
Ris = 1
End Try
Return Ris
End Function
Protected Function CloseDbConnection() As Integer
Dim Ris As Integer = 0
Try
If Not _cn Is Nothing Then
If _cn.State = ConnectionState.Open Then
_cn.Close()
End If
_cn = Nothing
End If
Catch ex As Exception
Ris = 1
End Try
Return Ris
End Function
Public Function Ap(ByVal Testo) As String
Dim str As String = String.Empty
If Not TypeOf Testo Is String Then
Str = " " & Testo.ToString
Else
Str = Testo.ToString
Str = Str.Replace("'", "''")
Str = " '" & Str & "'"
End If
Return Str
End Function
#Region "Serialization Method"
Public Function ReadSerialize(ByVal PathXMLSerial As String) as T
Dim cls As T
Try
Dim serialize As XmlSerializer = New XmlSerializer(GetType(T))
Dim deSerialize As IO.FileStream = New IO.FileStream(PathXMLSerial, IO.FileMode.Open)
cls = serialize.Deserialize(deSerialize)
Catch ex As Exception
ManageError(ex)
End Try
Return cls
End function
Public Sub SaveSerialize(Obj as T, ByVal PathXML As String)
Try
Dim serialize As XmlSerializer = New XmlSerializer(GetType(T))
Dim Writer As New System.IO.StreamWriter(PathXML)
serialize.Serialize(Writer, Obj)
Writer.Close()
Catch ex As Exception
ManageError(ex)
End Try
End Sub
#End Region
End Class
Public Class LunaSearchParameter
Public Sub New()
End Sub
Public Sub New(ByVal FieldName As String, ByVal Value As Object, Optional ByVal SqlOperator As String = "",Optional ByVal LogicOperator As enLogicOperator = enLogicOperator.enAND)
_FieldName = FieldName
_Value = Value
If SqlOperator.Length Then _SqlOperator = SqlOperator
_LogicOperator = LogicOperator
End Sub
Private _SqlOperator As String = " = "
Public Property SqlOperator As String
Get
Return _SqlOperator
End Get
Set(ByVal value As String)
_SqlOperator = value
End Set
End Property
Private _LogicOperator As enLogicOperator = enLogicOperator.enAND
Public Property LogicOperator As enLogicOperator
Get
Return _LogicOperator
End Get
Set(ByVal value As enLogicOperator)
_LogicOperator = value
End Set
End Property
Public ReadOnly Property LogicOperatorStr As String
Get
If _LogicOperator = enLogicOperator.enAND Then
Return " And "
Else
Return " Or "
End If
End Get
End Property
Private _FieldName As String
Public Property FieldName As String
Get
Return _FieldName
End Get
Set(ByVal value As String)
_FieldName = value
End Set
End Property
Private _Value
Public Property Value
Get
Return _Value
End Get
Set(ByVal value)
_Value = value
End Set
End Property
End Class
End Namespace
blgBank içindeki kod
Kod:
#Region "Author"
'Class created with Luna 3.0.12.18
'Author: Diego Lunadei
'Date: 11/6/2011
#End Region
Public Class blgBank
#Region "Logic Field"
#End Region
#Region "Method"
#End Region
End Class
blgBankDAO içindeki kod
Kod:
#Region "Author"
'Class created with Luna 3.0.12.18
'Author: Diego Lunadei
'Date: 11/6/2011
#End Region
Imports System
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Data
Imports System.Data.OleDb
Partial Public Class blgBank
Inherits LUNA.LunaBaseClass
'******IMPORTANT: Write your code in the Class object that use this Partial Class.
'******So you can replace DAOClass and EntityClass without lost your code
Public Sub New()
End Sub
#Region "Database Field Map"
Private _SIRA as Int32 = Nothing
<XmlElementAttribute("SIRA")> _
Public property SIRA() as Int32
Get
Return _SIRA
End Get
Set (byval value as Int32)
_SIRA= value
End Set
End property
Private _BASLIK as string = ""
<XmlElementAttribute("BASLIK")> _
Public property BASLIK() as string
Get
Return _BASLIK
End Get
Set (byval value as string)
_BASLIK= value
End Set
End property
Private _NOTLAR as string = ""
<XmlElementAttribute("NOTLAR")> _
Public property NOTLAR() as string
Get
Return _NOTLAR
End Get
Set (byval value as string)
_NOTLAR= value
End Set
End property
Private _TARIH as DateTime = Nothing
<XmlElementAttribute("TARIH")> _
Public property TARIH() as DateTime
Get
Return _TARIH
End Get
Set (byval value as DateTime)
_TARIH= value
End Set
End property
#End Region
#Region "Embedded Class"
#End Region
End Class
Public Class blgBankDAO
Inherits LUNA.LunaBaseClassDAO(Of blgBank)
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal Connection As Data.OleDb.OleDbConnection)
MyBase.New(Connection)
End Sub
Public Sub New(ByVal ConnectionString As string)
MyBase.New(ConnectionString)
End Sub
Public Overrides Function Read(Id as integer) as blgBank
Dim cls as new blgBank
Try
Dim myCommand As OleDbCommand = _cn.CreateCommand()
myCommand.CommandText = "SELECT * FROM Bilgibankasi where SIRA = " & Id
Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
myReader.Read()
if myReader.HasRows then
cls.SIRA= myReader("SIRA")
if not myReader("BASLIK") is DBNull.Value then
cls.BASLIK= myReader("BASLIK")
end if
if not myReader("NOTLAR") is DBNull.Value then
cls.NOTLAR= myReader("NOTLAR")
end if
if not myReader("TARIH") is DBNull.Value then
cls.TARIH= myReader("TARIH")
end if
End If
myReader.Close()
myCommand.Dispose()
Catch ex As Exception
ManageError(ex)
End Try
Return cls
End Function
Public Overrides Function Save(byRef cls as blgBank) as Integer
Dim Ris as integer=0 'in Ris torno l'id inserito
Dim DbCommand As OleDbCommand = New OleDbCommand()
Dim myTransaction As OleDbTransaction
Try
Dim sql As String
DbCommand.Connection = _cn
myTransaction = _cn.BeginTransaction
DbCommand.Transaction = myTransaction
If cls.SIRA = 0 Then
sql = "INSERT INTO Bilgibankasi("
sql &= "BASLIK,"
sql &= "NOTLAR,"
sql &= "TARIH"
sql &= ") VALUES ("
sql &= ap(cls.BASLIK) & ","
sql &= ap(cls.NOTLAR) & ","
sql &= ap(cls.TARIH)
sql &= ")"
Else
sql = "UPDATE Bilgibankasi SET "
sql &= "BASLIK = " & ap(cls.BASLIK) & ","
sql &= "NOTLAR = " & ap(cls.NOTLAR) & ","
sql &= "TARIH = " & ap(cls.TARIH)
sql &= " WHERE SIRA= " & cls.SIRA
End if
DbCommand.CommandText = sql
DbCommand.ExecuteNonQuery()
If cls.SIRA=0 Then
Dim IdInserito as integer = 0
Sql = "select @@[B][URL="http://forum.shiftdelete.net/member.php?u=5016"]identity[/URL][/B] "
DbCommand.CommandText = Sql
Idinserito = DbCommand.ExecuteScalar()
cls.SIRA = Idinserito
Ris = Idinserito
else
Ris = cls.SIRA
End If
myTransaction.Commit()
DbCommand.Dispose()
Catch ex As Exception
myTransaction.RollBack()
ManageError(ex)
End Try
Return Ris
End Function
Private Sub DestroyPermanently(Id as integer)
Try
Dim UpdateCommand As OleDbCommand = New OleDbCommand()
UpdateCommand.Connection = _cn
'******IMPORTANT: You can use this commented instruction to make a logical delete .
'******Replace DELETED Field with your logic deleted field name.
'Dim Sql As String = "UPDATE Bilgibankasi SET DELETED=True "
Dim Sql As String = "DELETE FROM Bilgibankasi"
Sql &= " Where SIRA = " & Id
UpdateCommand.CommandText = Sql
UpdateCommand.ExecuteNonQuery()
UpdateCommand.Dispose()
Catch ex As Exception
ManageError(ex)
End Try
End Sub
Public Overrides Sub Delete(Id as integer)
DestroyPermanently (Id)
End Sub
Public Overrides Sub Delete(byref obj as blgBank, Optional ByRef ListaObj as List (of blgBank) = Nothing)
DestroyPermanently (obj.SIRA)
If Not ListaObj Is Nothing Then ListaObj.Remove(obj)
End Sub
Public Overloads Function Find(ByVal OrderBy As String, ByVal ParamArray Parameter() As LUNA.LunaSearchParameter) as iEnumerable(Of blgBank)
Return FindReal(OrderBy, Parameter)
End Function
Public Overrides Function Find(ByVal ParamArray Parameter() As LUNA.LunaSearchParameter) as iEnumerable(Of blgBank)
Return FindReal("", Parameter)
End Function
Private Function FindReal(ByVal OrderBy As String, ByVal ParamArray Parameter() As LUNA.LunaSearchParameter) as iEnumerable(Of blgBank)
Dim Ls As New List(Of blgBank)
Try
Dim myCommand As OleDbCommand = _cn.CreateCommand()
Dim sql As String = "
sql ="SELECT SIRA,BASLIK,NOTLAR,TARIH from Bilgibankasi"
For Each Par As LUNA.LunaSearchParameter In Parameter
If Not Par Is Nothing Then
If Sql.IndexOf("WHERE") = -1 Then Sql &= " WHERE " Else Sql &= " " & Par.LogicOperatorStr & " "
Sql &= Par.FieldName & " " & Par.SqlOperator & " " & Ap(Par.Value)
End if
Next
If OrderBy.Length Then Sql &= " ORDER BY " & OrderBy
myCommand.CommandText = sql
Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
while myReader.Read
Dim classe as new blgBank
if not myReader("SIRA") is DBNull.Value then classe.SIRA = myReader("SIRA")
if not myReader("BASLIK") is DBNull.Value then classe.BASLIK = myReader("BASLIK")
if not myReader("NOTLAR") is DBNull.Value then classe.NOTLAR = myReader("NOTLAR")
if not myReader("TARIH") is DBNull.Value then classe.TARIH = myReader("TARIH")
Ls.Add(classe)
end while
myReader.Close()
myCommand.Dispose()
Catch ex As Exception
ManageError(ex)
End Try
Return Ls
End Function
Public Overrides Function GetAll(Optional OrderByField as string = "", Optional ByVal AddEmptyItem As Boolean = False) as iEnumerable(Of blgBank)
Dim Ls As New List(Of blgBank)
Try
Dim myCommand As OleDbCommand = _cn.CreateCommand()
Dim sql As String = "
sql ="SELECT SIRA,BASLIK,NOTLAR,TARIH from Bilgibankasi"
If OrderByField.Length Then
Sql &= " ORDER BY " & OrderByField
End If
myCommand.CommandText = sql
Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
If AddEmptyItem Then Ls.Add(New blgBank() With {.SIRA = Nothing ,.BASLIK = "" ,.NOTLAR = "" ,.TARIH = Nothing })
while myReader.Read
Dim classe as new blgBank
if not myReader("SIRA") is DBNull.Value then classe.SIRA = myReader("SIRA")
if not myReader("BASLIK") is DBNull.Value then classe.BASLIK = myReader("BASLIK")
if not myReader("NOTLAR") is DBNull.Value then classe.NOTLAR = myReader("NOTLAR")
if not myReader("TARIH") is DBNull.Value then classe.TARIH = myReader("TARIH")
Ls.Add(classe)
end while
myReader.Close()
myCommand.Dispose()
Catch ex As Exception
ManageError(ex)
End Try
Return Ls
End Function
End Class