Dear Sir,
I'm trying to Extract select query for double record database ms access with dapper in vb.net.
if there are any other methods with linq
Please Guide me.
Link database ms access
Imports System.ComponentModel
Imports System.Data.OleDb
Imports Dapper
Public Class Form1
Dim ABSENService As New ABSENService()
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DataGridView1.DataSource = ABSENService.GetDoubleFP()
End Sub
End Class
Public Class ABSENDOUBLEFP
Public Property ID As Integer
Public Property NAMEID As String
Public Property POSITIONID As String
Public Property [DATE] As DateTime
Public Property TIME As String
<DisplayName("IN/OUT")>
Public Property INOUT As String
Public Property Expr1 As Integer
End Class
Public Class ABSENService
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ABSEN.accdb;Persist Security Info=False;"
End Function
Private ReadOnly _conn As OleDbConnection
Private _connectionString As String = GetOledbConnectionString()
Public Sub New()
_conn = New OleDbConnection(_connectionString)
End Sub
Public Function GetDoubleFP() As IEnumerable(Of ABSENDOUBLEFP)
Dim sql2 = <sql>
SELECT ABSEN.ID, MASTERID.NAMEID AS [NAMEID],ABSEN.[DATE], MASTERID.POSITIONID AS [POSITIONID],ABSEN.INOUT,Count(*) AS Expr1
FROM ABSEN INNER JOIN MASTERID ON ABSEN.ID = MASTERID.ID
GROUP BY ABSEN.ID, MASTERID.NAMEID,ABSEN.[DATE],MASTERID.POSITIONID,ABSEN.INOUT,ABSEN.TIME,ABSEN.[DATE]
HAVING (((Count(*))>1));
</sql>.Value
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of ABSENDOUBLEFP)(sql2).ToList()
End Using
End Function
End Class
Desired result
ID NAMEID DATE POSITIONID TIME INOUT
5008 A 28-Apr-24 STAFF 08:00 IN
5008 A 28-Apr-24 STAFF 17:00 OUT
5009 B 29-Apr-24 STAFF 08:00 IN
5009 B 29-Apr-24 STAFF 17:00 OUT
Table Absen
ID DATE TIME INOUT
5008 28-Apr-24 08:00 IN
5008 28-Apr-24 17:00 OUT
5009 29-Apr-24 08:00 IN
5009 29-Apr-24 17:00 OUT
5010 29-Apr-24 08:00 IN
Table MASTERID
ID NAMEID POSITIONID
5008 A STAFF
5009 B STAFF
5010 C STAFF
