SQL错误C#-参数已定义 [英] SQL error C# - Parameter already defined
本文介绍了SQL错误C#-参数已定义的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嘿.我有一个解析txt文件并将其数据导入sql db的c#应用程序.我正在使用sqlite，现在正在将其移植到sql server.它与sqlite一起正常工作，但现在与sql处理文件时出现错误.它将第一行数据添加到数据库，然后说参数@PartNumber已经声明.变量名称在批处理或存储过程中必须唯一".这是我的整个代码和SQL表的布局...错误出现在代码末尾的最后一个 insertCommand.ExecuteNonQuery()实例...
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表:
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
);
代码:
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();
}
}
}
}
推荐答案
在foreach循环开始时尝试以下语句:
Try this statement at start of the foreach loop:
insertCommand.Parameters.Clear();
这篇关于SQL错误C#-参数已定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
