使用 C# 和存储过程解析文本文件并将其加载到数据库中

Question

我遇到了持续错误

“查找过程或函数‘Cup_INSFuneralHome’需要参数‘@funeralhomename’，但未提供该参数。”

的问题。我做了很多修改，但没有任何效果，所以我必须从我在错误区域中寻找的情况中吸取教训。

当我调试时，它似乎以正确的顺序提取正确的信息。原始文本文件格式为：

公司名称
地址
城邦邮政编码
电话

我对此很困惑。我浏览了其他帖子，但无法找到我需要的内容，或者无法从大量“有些”类似的帖子中提取我需要的内容。恐怕问题就摆在我面前，但我就是看不到它。我需要一双新的眼睛。所以我提前道歉。

过程代码：

ALTER procedure [dbo].[Cup_INSFuneralHome]
@funeralhomename nvarchar(50),
@addressone nvarchar(50),
@addresstwo nvarchar(50),
@CityName nvarchar(50),
@State nvarchar(10),
@Zipcode nchar(10),
@Telephone nchar(15),
@PrimaryContact nvarchar (50),
@EmailAddress nvarchar (50)
as

declare @cityid uniqueidentifier
declare @stateid uniqueidentifier 

select @cityid = (select cityid from [city] where CityName=(@CityName)) 
select @stateid = (select stateid from [State] where StateCode=ltrim(rtrim(@State)))

insert funeralhome(funeralhomename, addressone, addresstwo, cityid, stateid, zipcode,  telephone, PrimaryContact, EmailAddress)
values (@funeralhomename, @addressone, @addresstwo, @CityName, @State, @ZipCode,     @Telephone, @PrimaryContact, @EmailAddress)

C#代码：

namespace abc
{
class Program
{
    static void Main(string[] args)
    {
        address myclass = new address();
        string dbConnection = "xyz";
        string path = "D:\\docs\\someDocument.txt";
        StreamReader sr = new StreamReader(path);

        int intcount=0;
        string sLine = "";
        ArrayList fhome = new ArrayList();
        ArrayList Ads = new ArrayList();

        while (sLine != null)
        {
            sLine = sr.ReadLine();
            if (sLine != null)
                fhome.Add(sLine);
            intcount=intcount+1;
        }

        sr.Close();

        int startcount=0;

        for (int n = 0; n < fhome.Count; n++)
        {
            char[] delim = {',', ' '};

            try
            {
                if (startcount == 0)
                {
                    myclass = new address();
                }

                if (fhome[n].ToString().Trim().Length > 0)
                {
                    if (!fhome[n].ToString().Contains("Funeral Home Profile"))
                    {
                        switch (startcount)
                        {
                            case 0:
                                myclass.company = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 1:
                                myclass.address1 = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 2:
                                myclass.address2 = fhome[n].ToString().Trim();
                                startcount = startcount + 1;
                                break;

                            case 3:
                                myclass.telephone = fhome[n].ToString().Trim();
                                startcount = 0;
                                Ads.Add(myclass);
                                break;
                        }
                    }
                }
            }               
            catch { }
        }

       SqlConnection conn = new SqlConnection(dbConnection);

       for(int n=0;n< Ads.Count;n++)
        {           
           address tclass = (address)Ads[n];

           int comloc;
           comloc = tclass.address2.IndexOf(",");             

              string funeralhomename = tclass.company.ToString();
              string street = tclass.address1.ToString();
              string street2 = "";
              string city = tclass.address2.Substring(0, comloc);
              string state = tclass.address2.Substring(comloc + 1, 3);
              string zip = tclass.address2.Substring(comloc + 4);
              string tel = tclass.telephone.Replace("Local:", "");
              string PrimaryContact = "";
              string EmailAddress = "";

              string tsql = ""; 

               tsql = (funeralhomename + ',' +
                      street + ',' + street2 + city + ',' +
                      state + zip + tel + PrimaryContact + EmailAddress);

           conn.Open();

           try
           {
               SqlCommand cmd = new SqlCommand("Cup_INSFuneralHome", conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter[] param = new SqlParameter[9];
               param[0] = new SqlParameter("@funeralhomename", SqlDbType.NVarChar);
               param[0].Value = funeralhomename;
               param[1] = new SqlParameter("@addressone", SqlDbType.NVarChar);
               param[1].Value = street;
               param[2] = new SqlParameter("@addresstwo", SqlDbType.NVarChar);
               param[2].Value = street2;
               param[3] = new SqlParameter("@cityname", SqlDbType.NVarChar);
               param[3].Value = city;
               param[4] = new SqlParameter("@State", SqlDbType.NVarChar);
               param[4].Value = state;
               param[5] = new SqlParameter("@zipCode", SqlDbType.NChar);
               param[5].Value = zip;
               param[6] = new SqlParameter("@Telephone", SqlDbType.NChar);
               param[6].Value = tel;
               param[7] = new SqlParameter("@PrimaryContact", SqlDbType.NVarChar);
               param[7].Value = PrimaryContact;
               param[8] = new SqlParameter("@EmailAddress", SqlDbType.NVarChar);
               param[8].Value = EmailAddress;

               cmd.ExecuteNonQuery();
           }

           catch
           {
               Debug.Print("Error with.." + tclass.company);
           }

           finally
           {
               conn.Close();
           }
           Debug.Print(tsql);
        }   
    }        
}
}
public class address 
{
private string _company;
private string _address1;
private string _address2;
private string _telephone;

public string company
{
    get { return _company; }
    set { _company = value; }
}

public string address1
{
    get { return _address1; }
    set { _address1 = value; }
}

public string address2
{
    get { return _address2; }
    set { _address2 = value; }
}

public string telephone
{
    get { return _telephone; }
    set { _telephone = value; }
}
}

Answer 1

您没有将 sql 参数添加到 cmd 对象中；-)

Answer 2

看起来您需要在执行查询之前将参数添加到查询中。

您可以像这样快捷方式：

cmd.Parameters.Add("@thing", SqlDbType.Type).Value = value;

在您的情况下：

cmd.Parameters.Add("@funeralhomename", SqlDbType.NVarChar).Value = funeralhomename;
cmd.Parameters.Add("@addressone", SqlDbType.NVarChar).Value = street;
cmd.Parameters.Add("@addresstwo", SqlDbType.NVarChar).Value = street2;
cmd.Parameters.Add("@cityname", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@State", SqlDbType.NVarChar).Value = state;
cmd.Parameters.Add("@zipCode", SqlDbType.NChar).Value = zip;
cmd.Parameters.Add("@Telephone", SqlDbType.NChar).Value = tel;
cmd.Parameters.Add("@PrimaryContact", SqlDbType.NVarChar).Value = PrimaryContact;
cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = EmailAddress;

Answer 3

您创建了 SqlParameter[] 参数，但该集合不是 SqlCommand 在执行过程时使用的集合。删除整个集合并使用命令的 参数 属性代替。

cmd.Parameters.AddWithValue("@funeralhomename", funeralhomename)

并使用查询中的所有参数执行此操作

Answer 4

您需要使用 SqlCommand.Parameters.AddRange 方法将参数添加到 SqlCommand 对象：

SqlCommand cmd = new SqlCommand("Cup_INSFuneralHome", conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter[] param = new SqlParameter[9];
               param[0] = new SqlParameter("@funeralhomename", SqlDbType.NVarChar);
               param[0].Value = funeralhomename;
               param[1] = new SqlParameter("@addressone", SqlDbType.NVarChar);
               param[1].Value = street;
               param[2] = new SqlParameter("@addresstwo", SqlDbType.NVarChar);
               param[2].Value = street2;
               param[3] = new SqlParameter("@cityname", SqlDbType.NVarChar);
               param[3].Value = city;
               param[4] = new SqlParameter("@State", SqlDbType.NVarChar);
               param[4].Value = state;
               param[5] = new SqlParameter("@zipCode", SqlDbType.NChar);
               param[5].Value = zip;
               param[6] = new SqlParameter("@Telephone", SqlDbType.NChar);
               param[6].Value = tel;
               param[7] = new SqlParameter("@PrimaryContact", SqlDbType.NVarChar);
               param[7].Value = PrimaryContact;
               param[8] = new SqlParameter("@EmailAddress", SqlDbType.NVarChar);
               param[8].Value = EmailAddress;

cmd.Parameters.AddRange(param );