Updating SQL column based on text file (foreach line in it)

Question

What I have is an extremely large text file that needs to go into a specific column at a specific raw. The file is around 100k lines. So what I want to do is read the whole file, and for each line append into that specific SQL column the line. Here's what I have but i really need help on the SQL query

string[] primaryfix = File.ReadAllLines(dinfo+"\\"+filex);
            string filename = filex.ToString();
            string[] spltifilename = filename.Split('.');
            foreach (string primary in primaryfix)
            {
                string sqltable = ("dbo.amu_Textloadingarea");
                string sql = "update " + sqltable + " set [Text] = [Text] + '" + primary + "' where begbates = '" + spltifilename[0] + "'";

                SqlConnection con = new SqlConnection("Data Source= Corvette ;Initial Catalog= GSK_Avandia_SSECASE;Integrated Security= SSPI");
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader reader = cmd.ExecuteReader();
                con.Close();

            }

everything is fine except for the string sql, it doesn't update the way I would like it to.

Any help is always appreciated.

Answer 1

The first thing I see is that you are not escaping the input from the text file; any SQL escape characters (like a single quote) will break that command. I'd recommend using parameters so you needn't worry about escapes at all.

Other than that, nothing pops to mind that would suggest why the command isn't working, but I do wonder if it might not cause fewer problems if it's such a large file to read it line-by-line rather than all at once.

Answer 2

Look likes you're trying to read from the database with that code inside the loop. SqlDataReader provides a way to read rows from the database, but not the other way around.

Replace

SqlDataReader reader = cmd.ExecuteReader();

with

cmd.ExecuteNonQuery();