How to run foreach loop using the same SQL Server connection using C#

Question

I have a string array in a C# program that I need to load into a temp table in SQL Server. I am currently using a foreach loop to run the insert query. When the SQL connection closes, the temp table disappears so I end up with a table that only has 1 row when there are hundreds in the array.

I have tried adding

using (SqlConnection sqlconnection2 = new SqlConnection()) 

statement at the top of the method (removing the existing connection line, inside the If(ConnSucceeds) statement, just inside the try block, and inside the foreach loop. when its inside the foreach loop i have the same issue. Putting it anywhere else I get an error stating that the connection isn't open.

Ultimately I will need to add at least one more foreach loop to run another SQL query to manipulate the data and then find some way to export it to a text file all using the same connection.

    private void ImportToTempTable()
    {
        this.GetProgramInfoForSQLQuery();
        this.GetInstallFolder();
        string config = this._InstallFolder + @"\" + this._Version + @"\" + this._brandName + @".exe.config";
        GetInstanceName(config);

        string connStr = "<proprietary conn string parameters>";
        bool ConnSucceeds = false;

        SqlConnection sqlConnection = new SqlConnection();
        StringBuilder errorMessages = new StringBuilder();

        if (!ConnSucceeds)
        {
            try
            {
                sqlConnection.ConnectionString = connStr;
                sqlConnection.Open();
                this.WriteNote("SQL Connection Succeeded");
                this.WriteNote("");
                ConnSucceeds = true;
            }
            catch (Exception ex)
            {
                ProjectData.SetProjectError(ex);
                int num = (int)Interaction.MsgBox((object)(@"Unable to connect to SQL Server:" + sqlConnection.ConnectionString + @"
                Does the " + this._brandName + " Database Live on this Machine?"), MsgBoxStyle.Exclamation, (object)"SQL Connection Error");
                ProjectData.ClearProjectError();
            }
        }

        if (ConnSucceeds)
        {
            string filename = @"C:\Program Folder\DC_Imports\dc_raw.txt";

            try
            {
                StreamReader s = new StreamReader(filename);
                string fileContents = s.ReadToEnd();
                int removeHeader = fileContents.IndexOf('\n');
                string contentsNoHeader = fileContents.Substring(removeHeader);
                string contentsFixed = contentsNoHeader.Replace("'", "''");
                string delim = "\n";
                string[] Rows = contentsFixed.Split(delim.ToCharArray());

                foreach (string row in Rows)
                {
                    string query = @"USE DBName IF (NOT EXISTS (SELECT * FROM tempdb.sys.tables WHERE name LIKE '%#DCImportTable%'))
BEGIN
CREATE TABLE #DCImportTable (Main varchar (8000));
INSERT INTO #DCImportTable (Main) VALUES ('" + row + @"');
END
ELSE 
INSERT INTO #DCImportTable (Main) VALUES ('" + row + "');";

                        SqlCommand command = new SqlCommand(query, sqlConnection);
                        command.ExecuteNonQuery();
                        this.WriteNote(row);                  
                }

                this.WriteNote("Check Table");
                this.WriteNote("");
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    errorMessages.Append("Error \n" +
                        "Message: " + ex.Errors[i].Message + "\n");
                }

                this.WriteNote(errorMessages.ToString());
                sqlConnection.Close();
                this.WriteNote("SQL Connection Terminated");
            }
        }
        else
        {
            this.WriteNote("SQL Login Incorrect");
            sqlConnection.Close();
            this.WriteNote("SQL Connection Terminated");
        }
}

Any help would be greatly appreciated! This is probably the most complex thing I've ever tried to code and I have to use temp tables to do it.

Answer 1

In order to show how this might look I need to move around the pieces of this code a little bit. It gets much easier if you separate as much of this as possible into different pieces. Otherwise the logic and flow become complicated and difficult to follow. There's more than one way to do this. This is just a high-level example to show how you can break this up.

First, read the rows:

public class FileRowReader
{
    public string[] ReadRows(string filename)
    {
        StreamReader s = new StreamReader(filename);
        string fileContents = s.ReadToEnd();
        int removeHeader = fileContents.IndexOf('\n');
        string contentsNoHeader = fileContents.Substring(removeHeader);
        string contentsFixed = contentsNoHeader.Replace("'", "''");
        string delim = "\n";
        return contentsFixed.Split(delim.ToCharArray());
    }
}

By itself that part is much easier to understand. (I only spent a few seconds on the names. It would be good to rename them to something more descriptive.)

Then, write the rows to SQL. Again, to keep it simple, this can be separate from all of the initialization and configuration. I'm not addressing the issue of whether it makes sense or not to execute a single command for each row, or the risk of SQL injection that happens when we build SQL by concatenating strings. The SQL should be modified so that it uses parameters.

public class SqlRowWriter
{
    private readonly string _connectionString;

    public SqlRowWriter(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void WriteRows(string[] rows)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            foreach (var row in rows)
            {
                // Write each row
            }
        }
    }
}

One of the benefits of this approach is that unrelated parts of the code are decoupled. The part that reads from a text file doesn't know what you're going to do with the text. It doesn't care. Its job it just to read and give you the text.

The part that writes to SQL doesn't care where the data came from. It just takes whatever data you give it and operates on it. Each part is simpler and easier to understand because it's doing one thing, not trying to juggle a few at once.

Another benefit is that both classes are now easier to test. If you want to verify that your reader works, test that. If you want to test that your writer works, test that. You don't have to test them both at the same time.

I didn't include the part that collects and writes individual SQL errors. You could add it back in. The reason I left it out is that it would be really difficult to figure out what to do next if you try to write 50 rows but 25 of them throw exceptions.

What I particularly recommend is trying to send all of the rows to SQL at once instead of sending it one row at a time. You would do that using a stored procedure on the SQL Server end (instead of passing the whole SQL command as a string) and then passing all of the rows as a table-valued parameter. It's like sending rows of data in one execution instead of multiple executions. That's a slightly different answer, but by keeping the SQL part of this separate it becomes much easier to modify your code later to make that change. Here's an example.

Sending them all at once also means that you can either do one bulk insert or do separate inserts in a transaction. That way the whole thing succeeds or the whole thing fails. You're not left in a weird state where part of your data has been inserted.

Now the method you started with doesn't have to do all this stuff. It can just use these two other classes:

private void ImportToTempTable()
{
    // I don't know what this does. I just left it in because it's 
    // part of your original code.
    this.GetProgramInfoForSQLQuery();
    this.GetInstallFolder();
    string config = this._InstallFolder + @"\" + this._Version + @"\" + this._brandName + @".exe.config";
    GetInstanceName(config);

    string filename = "<whatever your file name is>";
    string connStr = "<proprietary conn string parameters>";
    var rowReader = new FileRowReader();
    var rows = rowReader.ReadRows(filename);
    var writer = new SqlRowWriter(connStr);
    writer.WriteRows(rows);
}

What if you read all of the rows but then you wanted to inspect them all to find out ahead of time if there was anything in them that you didn't want to write to SQL, like blank rows or headers? Now it's really easy to insert another class or method that takes an array of strings and filters them or cleans them up somehow.

I haven't included any exception handling yet. That's also easier if you do it in the outer method. If you want to handle all exceptions from reading or writing the same, you could just do this:

private void ImportToTempTable()
{
    // I don't know what this does. I just left it in because it's 
    // part of your original code.
    this.GetProgramInfoForSQLQuery();
    this.GetInstallFolder();
    string config = this._InstallFolder + @"\" + this._Version + @"\" + this._brandName + @".exe.config";
    GetInstanceName(config);

    string filename = "<whatever your file name is>";
    string connStr = "<proprietary conn string parameters>";
    try
    {
        var rowReader = new FileRowReader();
        var rows = rowReader.ReadRows(filename);
        var writer = new SqlRowWriter(connStr);
        writer.WriteRows(rows);
    }
    catch (Exception ex)
    {
        // log the exception
    }
}

Even if you don't add more detail, it will be easy to tell if it's an exception from reading the file or from writing to SQL. If it's SQL, it will be easy to tell if it was from opening the connection or from executing a command. Chances are that will tell you everything you need. If you need more detail you could put separate try/catch blocks around different method calls, or you could catch specific exception types like SqlException or IOException. But odds are that just catching and logging will tell you everything you need to know.