How to check if a specific data exist in a text file before insert it to SQL database table?

I have a Employee text file employee.txt which has some data in it.

For instance:

empID, FirstName, LastNme

I want to read the file and get the empId from it and pass it to a function to check if that empId already exists in the SQL database table.

Here’s my function:

public bool DoesEmployeeExist(int empId)
{
    bool ret;

    try
    {
        string sql = "Select empId from Employees where empId = '" + empId + "'";

        Object obj = _idac.GetSingleAnswer(sql);

        if (obj != null)
        {
            ret = true;
        }
        else
        {
            ret = false;
        }
    }
    catch (Exception)
    {
        throw;
    }

    return ret;
}

How do I get the empId from the file?

And how to insert the file in the SQL database table if a emptId is not already in it?

Should I pass the employee.txt file as string to a function like that and use Bulk Insert to add the data in the database:

public void InsertOrUpdateEmployee(string employees)
{
    string sql = "BULK INSERT Employee FROM '" + employees + "'" +
                 " WITH(FIELDTERMINATOR = ',', ROWTERMINATOR = 'n')";
}

Or pass it as a

List<Employees> employee = new <Employees>();

Answer

Based on your issue, you would like to insert the empId from the file to database if the

empId is not in database.

You can use the following code to get it.

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<Employee> list = new List<Employee>();
            var result = File.ReadAllLines("D:\new.txt");
            string[] s = { "," };
            foreach (var item in result)
            {
                string[] arr = item.Split(s, StringSplitOptions.RemoveEmptyEntries);
                list.Add(new Employee { EmpId = Convert.ToInt32(arr[0]), FirstName = arr[1], LastName = arr[2] });
            }
            foreach (Employee item in list)
            {
                bool t = DoesEmployeeExist(item.EmpId);
                if(t==true)
                {
                    MessageBox.Show("Database already has the record "+item.EmpId);
                }
                else
                {
                    string sql = string.Format("insert into Employees (empID,FirstName,LastName)values({0},'{1}','{2}')", item.EmpId, item.FirstName, item.LastName);
                    string connstring = @"";
                    SqlConnection connection = new SqlConnection(connstring);
                    connection.Open();
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.ExecuteNonQuery();
                    connection.Close();

                }
            }
            MessageBox.Show("Test");
        }

        public bool DoesEmployeeExist(int empId)
        {

            try
            {
                string sql = "Select empID from Employees where empID = '" + empId + "'";
                string connstring = @"";
                SqlConnection connection = new SqlConnection(connstring);
                connection.Open();
                SqlCommand command = new SqlCommand(sql,connection);
                SqlDataReader dr = command.ExecuteReader();
                if(dr.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch (Exception)
            {
                throw;
            }


        }
    }
    public class Employee
    {
     public int EmpId { get; set; }

     public string FirstName { get; set; }

     public string LastName { get; set; }

    }

The next is my txt file:

The next is my database record: