Hi kana250688,
Please refer the below sample created to read the Dat file and insert into the database by filtering based on the two dates.
Install Dapper Library
You need to install the Dapper library from Nuget using the following command.
Install-Package Dapper -Version 2.1.35
The Dat file
The Dat file consists of following records.
Form Design
The Form consists of 2 DatePicker control, a DataGridView and 2 Buttons for filtering and Inserting the record in database.
Namespaces
You will need to import the following namespaces.
C#
using Dapper;
using System.IO;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports Dapper
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports System.Configuration
Property Class
The class consists of following properties.
C#
public class DatFile
{
public int ID { get; set; }
public DateTime DATE { get; set; }
public string TIME { get; set; }
public string FP { get; set; }
[DisplayName("IN/OUT")]
public string INOUT { get; set; }
public string OTHERS1 { get; set; }
public string OTHERS2 { get; set; }
}
VB.Net
Public Class DatFile
Public Property ID As Integer
Public Property [DATE] As DateTime
Public Property TIME As String
Public Property FP As String
<DisplayName("IN/OUT")>
Public Property INOUT As String
Public Property OTHERS1 As String
Public Property OTHERS2 As String
End Class
Code
C#
private void Form1_Load(object sender, EventArgs e)
{
this.ReadDatFile();
}
private void ReadDatFile()
{
openFileDialog1.InitialDirectory = @"E:\TestFile";
openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat";
openFileDialog1.RestoreDirectory = true;
openFileDialog1.ShowDialog();
this.Activate();
}
private void OnFilter(object sender, EventArgs e)
{
List<DatFile> datfiles = new List<DatFile>();
string[] lines = File.ReadAllLines(openFileDialog1.FileName);
for (int i = 0; i <= lines.Length - 1; i++)
{
if (!string.IsNullOrEmpty(lines[i]))
{
string[] data = lines[i].Split('\t');
datfiles.Add(new DatFile
{
ID = Convert.ToInt32(data[0]),
DATE = Convert.ToDateTime(data[1]),
TIME = data[2],
FP = data[3],
INOUT = data[4],
OTHERS1 = data[5],
OTHERS2 = data[6]
});
}
}
DateTime dtStart = Convert.ToDateTime(startDate.Value).Date;
DateTime dtEnd = Convert.ToDateTime(endDate.Value).Date;
datfiles = datfiles.Where(x => Convert.ToDateTime(x.DATE) >= dtStart && Convert.ToDateTime(x.DATE) <= dtEnd).ToList();
dataGridView1.DataSource = datfiles;
}
private void OnInsert(object sender, EventArgs e)
{
List<DatFile> datfiles = new List<DatFile>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
datfiles.Add(new DatFile
{
ID = Convert.ToInt32(row.Cells[0].Value),
DATE = Convert.ToDateTime(row.Cells[1].Value),
TIME = row.Cells[2].Value.ToString().Trim(),
FP = row.Cells[3].Value.ToString().Trim(),
INOUT = row.Cells[4].Value.ToString().Trim(),
OTHERS1 = row.Cells[5].Value.ToString().Trim(),
OTHERS2 = row.Cells[6].Value.ToString().Trim()
});
}
string query = "INSERT INTO datfiles VALUES(@ID, @DATE, @TIME, @FP, @INOUT, @OTHERS1, @OTHERS2)";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
con.Execute(query, datfiles);
}
}
VB.Net
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.ReadDatFile()
End Sub
Private Sub ReadDatFile()
openFileDialog1.InitialDirectory = "E:\TestFile"
openFileDialog1.Filter = "Dat files(*.Dat)|*.Dat"
openFileDialog1.RestoreDirectory = True
openFileDialog1.ShowDialog()
Me.Activate()
End Sub
Private Sub OnFilter(ByVal sender As Object, ByVal e As EventArgs) Handles btnFilter.Click
Dim datfiles As List(Of DatFile) = New List(Of DatFile)()
Dim lines As String() = File.ReadAllLines(openFileDialog1.FileName)
For i As Integer = 0 To lines.Length - 1
If Not String.IsNullOrEmpty(lines(i)) Then
Dim data As String() = lines(i).Split(vbTab)
datfiles.Add(New DatFile With {
.ID = Convert.ToInt32(data(0)),
.DATE = Convert.ToDateTime(data(1)),
.TIME = data(2),
.FP = data(3),
.INOUT = data(4),
.OTHERS1 = data(5),
.OTHERS2 = data(6)
})
End If
Next
Dim dtStart As DateTime = Convert.ToDateTime(startDate.Value).Date
Dim dtEnd As DateTime = Convert.ToDateTime(endDate.Value).Date
datfiles = datfiles.Where(Function(x) Convert.ToDateTime(x.DATE) >= dtStart AndAlso Convert.ToDateTime(x.DATE) <= dtEnd).ToList()
dataGridView1.DataSource = datfiles
End Sub
Private Sub OnInsert(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
Dim datfiles As List(Of DatFile) = New List(Of DatFile)()
For Each row As DataGridViewRow In dataGridView1.Rows
datfiles.Add(New DatFile With {
.ID = Convert.ToInt32(row.Cells(0).Value),
.DATE = Convert.ToDateTime(row.Cells(1).Value),
.TIME = row.Cells(2).Value.ToString().Trim(),
.FP = row.Cells(3).Value.ToString().Trim(),
.INOUT = row.Cells(4).Value.ToString().Trim(),
.OTHERS1 = row.Cells(5).Value.ToString().Trim(),
.OTHERS2 = row.Cells(6).Value.ToString().Trim()
})
Next
Dim query As String = "INSERT INTO datfiles VALUES(@ID, @DATE, @TIME, @FP, @INOUT, @OTHERS1, @OTHERS2)"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
con.Execute(query, datfiles)
End Using
End Sub
Screenshot
The Form with Filtered record
Record after inserted into database
Note: Modify the code as per your data and database schema.