SQL error C# - Parameter already defined

Question

Hey there. I have a c# application that parses txt files and imports the data from them into a sql db. I was using sqlite and am now working on porting it to sql server. It was working fine with sqlite but now with sql i am getting an error when it is processing the files. It added the first row of data to the db and then says "parameter @PartNumber has already been declared. Variable names must be unique within a batch or stored procedure". Here is my whole code and SQL table layout ... the error comes at the last insertCommand.ExecuteNonQuery() instance at the end of the code...

SQL TABLE:

CREATE TABLE Import (
  RowId int PRIMARY KEY IDENTITY,
  PartNumber text,
  CMMNumber text,
  Date text,
  FeatType text,
  FeatName text,
  Value text,
  Actual text,
  Nominal text,
  Dev text,
  TolMin text,
  TolPlus text,
  OutOfTol text,
  FileName  text   
);

CODE:

using System; 
using System.Data; 
using System.Data.SQLite; 
using System.IO;
using System.Text.RegularExpressions;
using System.Threading;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;


namespace JohnDeereCMMDataParser 
{ 
    internal class Program 
    {


        public static List<string> GetImportedFileList()
        {
            List<string> ImportedFiles = new List<string>();
            using (SqlConnection connect = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES"))
            {
                connect.Open();
                using (SqlCommand fmd = connect.CreateCommand())
                {

                    fmd.CommandText = @"IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import'))  BEGIN SELECT DISTINCT FileName FROM Import; END";
                    fmd.CommandType = CommandType.Text;
                    SqlDataReader r = fmd.ExecuteReader();
                    while (r.Read())
                    {
                        ImportedFiles.Add(Convert.ToString(r["FileName"]));

                    }
                }
            }
            return ImportedFiles;
        } 




        private static void Main(string[] args) 
        {


            Console.Title = "John Deere CMM Data Parser";
            Console.WriteLine("Preparing CMM Data Parser... done");
            Console.WriteLine("Scanning for new CMM data... done");
            Console.ForegroundColor = ConsoleColor.Gray;

            using (SqlConnection con = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES"))
            {

                con.Open();

                using (SqlCommand insertCommand = con.CreateCommand())
                {

                    SqlCommand cmdd = con.CreateCommand();
                    string[] files = Directory.GetFiles(@"C:\Documents and Settings\js91162\Desktop\ ", "R303717*.txt*", SearchOption.AllDirectories);



                        List<string> ImportedFiles = GetImportedFileList();

                        foreach (string file in files.Except(ImportedFiles)) 


                        {

                            string FileNameExt1 = Path.GetFileName(file);


                            cmdd.CommandText =
                                @" 
                    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import')) BEGIN SELECT COUNT(*) FROM Import WHERE FileName = @FileExt; END";
                            cmdd.Parameters.Add(new SqlParameter("@FileExt", FileNameExt1));

                            int count = Convert.ToInt32(cmdd.ExecuteScalar());
                            con.Close();
                            con.Open();

                            if (count == 0)
                            {
                                Console.WriteLine("Parsing CMM data for SQL database... Please wait.");


                                insertCommand.CommandText =
                                    @"
                    INSERT INTO Import  (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, PartNumber, CMMNumber, Date, FileName) 
                    VALUES     (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @PartNumber, @CMMNumber, @Date, @FileName);";

                                insertCommand.Parameters.Add(new SqlParameter("@FeatType", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@FeatName", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@Value", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@Actual", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@Nominal", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@Dev", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@TolMin", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@TolPlus", DbType.Decimal));
                                insertCommand.Parameters.Add(new SqlParameter("@OutOfTol", DbType.Decimal));





                                string FileNameExt = Path.GetFullPath(file);
                                string RNumber = Path.GetFileNameWithoutExtension(file);

                                string RNumberE = RNumber.Split('_')[0];

                                string RNumberD = RNumber.Split('_')[1];
                                string RNumberDate = RNumber.Split('_')[2];

                                DateTime dateTime = DateTime.ParseExact(RNumberDate, "yyyyMMdd", Thread.CurrentThread.CurrentCulture);
                                string cmmDate = dateTime.ToString("dd-MMM-yyyy");
                                string[] lines = File.ReadAllLines(file);
                                bool parse = false;

                                foreach (string tmpLine in lines)
                                {


                                    string line = tmpLine.Trim();
                                    if (!parse && line.StartsWith("Feat. Type,"))
                                    {
                                        parse = true;
                                        continue;
                                    }
                                    if (!parse || string.IsNullOrEmpty(line))
                                    {
                                        continue;
                                    }

                                    Console.WriteLine(tmpLine);
                                    foreach (SqlParameter parameter in insertCommand.Parameters)
                                    {
                                        parameter.Value = null;
                                    }

                                    string[] values = line.Split(new[] { ',' });

                                    for (int i = 0; i < values.Length - 1; i++)
                                    {
                                        SqlParameter param = insertCommand.Parameters[i];
                                        if (param.DbType == DbType.Decimal)
                                        {
                                            decimal value;
                                            param.Value = decimal.TryParse(values[i], out value) ? value : 0;
                                        }
                                        else
                                        {
                                            param.Value = values[i];
                                        }
                                    }

                                    insertCommand.Parameters.Add(new SqlParameter("@PartNumber", RNumberE));
                                    insertCommand.Parameters.Add(new SqlParameter("@CMMNumber", RNumberD));
                                    insertCommand.Parameters.Add(new SqlParameter("@Date", cmmDate));
                                    insertCommand.Parameters.Add(new SqlParameter("@FileName", FileNameExt));
                                    // 
                                    insertCommand.ExecuteNonQuery();

                                }


                            }

                        }
                        Console.WriteLine("CMM data successfully imported to SQL database...");

                    }
                    con.Close();

            }
        } 
    } 
} 

Answer 1

Try this statement at start of the foreach loop:

insertCommand.Parameters.Clear();

Answer 2

You are creating your insertCommand variable once and reusing it each time around the loop, but you are also adding the same parameters to it each time around the loop. This is why you get a duplicate parameter error the second time it goes round the loop.

Either:

• add the parameters once outside of the loop, keeping a reference to them, and then setting their values each time through the loop.
• call insertCommand.Parameters.Clear() at the start of the loop.

The first option is better as it is more efficient with object creation.