c# - 如何在 C# 控制台应用程序中使用 XmlTextReader 将 XML 数据插入 SQL Server 表？

Question

我有一个 4GB 的 XML 文件。我必须将它的一些值插入到数据库中的表中。我在 c# 控制台中编写了一个存储过程和代码，但它没有将数据插入数据库。此外，它不会引发任何错误。下面是我编写的代码以及存储过程。

static void Main(string[] args)
{
    string first_name=" ";
    string middle_name = "";
    string surname = "";
    string gender = "";
    string occ_title = "";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DawJoneList;Data Source=ARBENITA";

        conn.Open();

        using (XmlTextReader reader = new XmlTextReader("D:\\test.xml"))
        {
            while (reader.Read())
            {
                SqlCommand insertCommand = new SqlCommand("spInsertimiListes", conn); 

                if (reader.IsStartElement())
                {
                    first_name = " ";
                    middle_name = " ";
                    surname = " ";
                    gender = " ";
                    occ_title =  " ";

                    foreach (var item in reader.Name)
                    {
                        if (reader.Name == "FirstName")
                        {
                            first_name = reader.ReadString();
                        }
                        else if (reader.Name == "MiddleName")
                        {
                            middle_name = reader.ReadString();
                        }
                        else if (reader.Name == "Surname")
                        {
                            surname = reader.ReadString();
                        }
                        else if (reader.Name == "Gender")
                        {
                            gender = reader.ReadString();
                        }
                        else if (reader.Name == "OccTitle")
                        {
                            occ_title = reader.ReadString();
                        }
                    }
                }

                insertCommand.CommandType = CommandType.StoredProcedure;

                insertCommand.Parameters.AddWithValue("FirstName", first_name);
                insertCommand.Parameters.AddWithValue("MiddleName", middle_name);
                insertCommand.Parameters.AddWithValue("Surname", surname);
                insertCommand.Parameters.AddWithValue("Gender", gender);
                insertCommand.Parameters.AddWithValue("OccTitle", occ_title); 

                if (!((first_name == " " & surname == " " & middle_name == " " & gender == " " & occ_title == " ")))
                {
                    insertCommand.ExecuteNonQuery();
                }
            }
        }

        conn.Close();
    }
}

存储过程如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertimiListes]   
    @FirstName NCHAR(500),   
    @MiddleName NCHAR(500),
    @Surname NCHAR(500),
    @Gender NCHAR(100),
    @OccTitle NVARCHAR(MAX)  
AS   
    INSERT INTO Person_details (FirstName, MiddleName, Surname, Gender, OccTitle)
    VALUES (@FirstName, @MiddleName, @Surname, @Gender, @OccTitle) 

我用于测试的 XML 是：

<?xml version = "1.0"?>
<Person>
<Name_details>
    <FirstName>
        Nancy    
    </FirstName>
    <MiddleName>
        Ndov
    </MiddleName>
        <Surname>
        Davolio
        </Surname>
        <Gender>
        Female
        </Gender>
        <OccTitle>
        Author
        </OccTitle>
</Name_details>
<Name_details>
    <FirstName>
        Janet    
    </FirstName>
    <MiddleName>
        Leverling
    </MiddleName>
        <Surname>
        Loving
        </Surname>
        <Gender>
        Female
        </Gender>
        <OccTitle>
        Doctor
        </OccTitle>
</Name_details>
<Name_details>
    <FirstName>
        Margaret    
    </FirstName>
    <MiddleName>
        Jaanna
    </MiddleName>
        <Surname>
        Peacock
        </Surname>
        <Gender>
        Male
        </Gender>
        <OccTitle>
        Programer
        </OccTitle>
    </Name_details>
</Person> 

为什么这段代码没有将 XML 数据插入数据库？任何帮助，请！

标签： c#sql-serverxmlado.net

Answer 1

问题是reader.read()只会读取根节点并且只有一个节点，所以它不会移动到下一个节点。您应该使用ReadToDescendant方法导航到子节点。下面的代码可能对你有用。检查我在代码中的评论。

          using (XmlTextReader reader = new XmlTextReader("D:\\test.xml"))
                {
                    reader.ReadToFollowing("Person"); // it will read the first node
                    if (reader.ReadToDescendant("Name_details")) // it will read the first descendent of Person
                    {
                        do
                        {
                            SqlCommand insertCommand = new SqlCommand("spInsertimiListes", conn);

                            if (reader.IsStartElement())
                            {
                                first_name = " ";
                                middle_name = " ";
                                surname = " ";
                                gender = " ";
                                occ_title = " ";

                                foreach (var item in reader.Name)
                                {
                                    if (reader.Name == "FirstName")
                                    {
                                        first_name = reader.ReadString();
                                    }
                                    else if (reader.Name == "MiddleName")
                                    {
                                        middle_name = reader.ReadString();
                                    }
                                    else if (reader.Name == "Surname")
                                    {
                                        surname = reader.ReadString();
                                    }
                                    else if (reader.Name == "Gender")
                                    {
                                        gender = reader.ReadString();
                                    }
                                    else if (reader.Name == "OccTitle")
                                    {
                                        occ_title = reader.ReadString();
                                    }
                                }
                            }

                            insertCommand.CommandType = CommandType.StoredProcedure;

                            insertCommand.Parameters.AddWithValue("FirstName", first_name);
                            insertCommand.Parameters.AddWithValue("MiddleName", middle_name);
                            insertCommand.Parameters.AddWithValue("Surname", surname);
                            insertCommand.Parameters.AddWithValue("Gender", gender);
                            insertCommand.Parameters.AddWithValue("OccTitle", occ_title);

                            if (!((first_name == " " & surname == " " & middle_name == " " & gender == " " & occ_title == " ")))
                            {
                                insertCommand.ExecuteNonQuery();
                            }
                        } while (reader.ReadToNextSibling("Name_details")); // it will read next descendent of person
                    }
                }