Import dat file and insert to database using Dapper in C# and VB.Net

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.