Hey there. I have a C # application that parses txt files and imports data from them into sql db. I used sqlite, and now I'm working on porting it to sql server. It worked fine with sqlite, but now with sql I get an error message while processing files. He added the first row of data to db, and then said: "The @PartNumber parameter is already declared. Variable names must be unique as part of a batch or stored procedure." Here is my whole code and table of SQL tables ... the error occurs in the last instance insertCommand.ExecuteNonQuery()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();
}
}
}
}