必须声明标量变量 - 为什么我的参数不起作用？

Question

我正在使用旧的sqlclient用循环插入一些数据。我已经正确添加了所有参数，但是我得到了一个例外，说

"必须声明标量变量"@keyproject "。"

string query = @"INSERT INTO dbo.ImportedProjects 
                        (
                            KeyProject, 
                            KeyCompany, 
                            KeyCountry, 
                            KeyCustomer, 
                            KeyEmployeeProjectManager, 
                            KeyEmployeeProjectOwner, 
                            KeyOrganisation, 
                            ProjectNumber, 
                            ProjectName, 
                            ProjectOwnerNumber, 
                            ProjectManagerNumber, 
                            ProjectOwnerName, 
                            ProjectManagerName, 
                            ProjectOwnerInitials, 
                            ProjectManagerInitials, 
                            CustomerNumber, 
                            CustomerName, 
                            CreatedDate, 
                            ProjectStatus, 
                            ProjectOpenClosed
                        ) VALUES (
                            @KeyProject, 
                            @KeyCompany, 
                            @KeyCountry, 
                            @KeyCustomer, 
                            @KeyEmployeeProjectManager, 
                            @KeyEmployeeProjectOwner, 
                            @KeyOrganisation, 
                            @ProjectNumber, 
                            @ProjectName, 
                            @ProjectOwnerNumber, 
                            @ProjectManagerNumber, 
                            @ProjectOwnerName, 
                            @ProjectManagerName, 
                            @ProjectOwnerInitials, 
                            @ProjectManagerInitials, 
                            @CustomerNumber, 
                            @CustomerName, 
                            @CreatedDate, 
                            @ProjectStatus, 
                            @ProjectOpenClosed
                        )";
        SqlConnection sqlCon = new SqlConnection("Server=localhost;Database=DatabaseName;Trusted_Connection=True;");
        SqlCommand idInsertCms = new SqlCommand("SET IDENTITY_INSERT dbo.ImportedProjects ON", sqlCon);
        SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
        try
        {
            sqlCon.Open();
            idInsertCms.ExecuteNonQuery();
            foreach (var item in importedProjects)
            {
                sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
                sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
                sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
                sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
                sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
                sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
                sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
                sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
                sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
                sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
                sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
                sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
                sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
                sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
                sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
                sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
                sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
                sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
                sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
                sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
                sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
                sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
                sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
                sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
                sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;
                sqlCmd.Parameters.Clear();
                sqlCmd.ExecuteNonQuery();
            }
            idInsertCms.CommandText = "SET IDENTITY_INSERT dbo.ImportedProjects OFF";
            idInsertCms.ExecuteNonQuery();

我的代码怎么了？

Answer 1

如何为这样的每个迭代创建一个新的SqlCommand：

string query = @"INSERT INTO dbo.ImportedProjects 
                    (
                        KeyProject, 
                        KeyCompany, 
                        KeyCountry, 
                        KeyCustomer, 
                        KeyEmployeeProjectManager, 
                        KeyEmployeeProjectOwner, 
                        KeyOrganisation, 
                        ProjectNumber, 
                        ProjectName, 
                        ProjectOwnerNumber, 
                        ProjectManagerNumber, 
                        ProjectOwnerName, 
                        ProjectManagerName, 
                        ProjectOwnerInitials, 
                        ProjectManagerInitials, 
                        CustomerNumber, 
                        CustomerName, 
                        CreatedDate, 
                        ProjectStatus, 
                        ProjectOpenClosed
                    ) VALUES (
                        @KeyProject, 
                        @KeyCompany, 
                        @KeyCountry, 
                        @KeyCustomer, 
                        @KeyEmployeeProjectManager, 
                        @KeyEmployeeProjectOwner, 
                        @KeyOrganisation, 
                        @ProjectNumber, 
                        @ProjectName, 
                        @ProjectOwnerNumber, 
                        @ProjectManagerNumber, 
                        @ProjectOwnerName, 
                        @ProjectManagerName, 
                        @ProjectOwnerInitials, 
                        @ProjectManagerInitials, 
                        @CustomerNumber, 
                        @CustomerName, 
                        @CreatedDate, 
                        @ProjectStatus, 
                        @ProjectOpenClosed
                    )";
    SqlConnection sqlCon = new SqlConnection("Server=localhost;Database=DatabaseName;Trusted_Connection=True;");
    SqlCommand idInsertCms = new SqlCommand("SET IDENTITY_INSERT dbo.ImportedProjects ON", sqlCon);

    try
    {
        sqlCon.Open();
        idInsertCms.ExecuteNonQuery();
        foreach (var item in importedProjects)
        {
            SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
            sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
            sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
            sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
            sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
            sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
            sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
            sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
            sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
            sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
            sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
            sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
            sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
            sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
            sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
            sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
            sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
            sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
            sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
            sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
            sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
            sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
            sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
            sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
            sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;
            sqlCmd.ExecuteNonQuery();
        }
        idInsertCms.CommandText = "SET IDENTITY_INSERT dbo.ImportedProjects OFF";
        idInsertCms.ExecuteNonQuery();
SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
foreach (var item in importedProjects)
{
    sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
    sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
    sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
    sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
    sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
    sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
    sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
    sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
    sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
    sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
    sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
    sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
    sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
    sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
    sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
    sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
    sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
    sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
    sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
    sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;
    sqlCmd.ExecuteNonQuery();
}

，或者您可以回收sqlCmd并在循环外设置参数，然后将它们设置在循环中：

SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
foreach (var item in importedProjects)
{
    sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
    sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
    sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
    sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
    sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
    sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
    sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
    sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
    sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
    sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
    sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
    sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
    sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
    sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
    sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
    sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
    sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
    sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
    sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
    sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;
    sqlCmd.ExecuteNonQuery();
}

Answer 2

，而不是插入循环中，您可以使用其他方法，尝试创建表格并用您的值添加行，一旦完成将行添加到此表中，您就可以可以将此表作为参数发送到您的存储过程，在SQL Server中创建一个用户定义的表，与循环中创建的表相同的列，并将此类型用作参数中的类型，之后，您可以将插入插入到您的使用SELECT

表

var newDataRow = medicareBuyInBuyInDataTable.NewRow();
foreach (var value in values) {
    newDataRow["FieldName"] = value;
    ....
    ....
}
yourDataTable.Rows.Add(newDataRow);
// send your table as parameter
 var parameter = new List<KeyValuePair<string, object>>{
                new KeyValuePair<string, object>("@yourTableParam", medicareBuyInDataTable)
            };
 return DbAccess.ExecuteScalar(ConString, StoredProcedureName, parameter);

在SQL Server中创建一个类型，该类型在您发送的表中具有相同的值：

-- ================================
-- Create User-defined Table Type
-- ================================
USE <database_name, sysname, AdventureWorks>
GO
-- Create the data type
CREATE TYPE YourTableTypeName AS TABLE 
(
    <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, 
    <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, 
    <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, 
    PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

最后，在存储过程中，使用创建的类型" thabletypename"

CREATE PROCEDURE [dbo].[YourProcedureName]
    @yourTableParam [dbo].[YourTableTypeName] READONLY
AS
BEGIN
    INSERT INTO [dbo].[YourTable]
        SELECT * 
        FROM yourTableParam 
END