c#提取XML段并插入到SQL中

Question

我对c#相当陌生，但我对SQL有相当的了解

我有1000的XML文件在一个文件夹。我需要提取某些元素并将它们存储在SQL中。XMl将有段后裔，我需要存储在SQL中。例如，下面是一个仓库订单123456，它有许多订单行，每个订单行都有一个产品/代码段。

我有一个SQL表与5列-客户端，仓库，OrderReference, CustomerReference，类型和ProductCode。

例如

<UniversalShipment xmlns="http://www.cargowise.com/Schemas/Universal/2011/11" version="1.1">
<Shipment>
<Order>
<OrderNumber>123456</OrderNumber>
<ClientReference>abcdefg</ClientReference>
<DocketSubType>
<Code>ORD</Code>
<Description>ORDER</Description>
</DocketSubType>
<Warehouse>
<Name>Seb's Test Warehouse</Name>
</Warehouse>
<OrderLineCollection Content="Complete">
<OrderLine>
<Product>
<Code>8011</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8044</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8043</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8042</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8054</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8018</Code>
</Product>
</OrderLine>
<OrderLine>
<Product>
<Code>8061</Code>
</Product>
</OrderLine>
</OrderLineCollection>
</Order>
<OrganizationAddressCollection>
<OrganizationAddress>
<AddressType>SendersLocalClient</AddressType>
<CompanyName>The Client</CompanyName>
</OrganizationAddress>
</OrganizationAddressCollection>
</Shipment>
</UniversalShipment>

我有下面的工作，但它只会向SQL发送1行-第一个Orderline，它不会循环通过Orderline段

using System;
using System.Linq;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace Load_XML_To_SQL
{
class Program
{
static void Main(string[] args)
{
XDocument xmlDocument = XDocument.Load(@"C:UserssebbyDesktopMLLXML to SQLsample.xml");
XNamespace ns = xmlDocument.Root.GetDefaultNamespace();

Orders Order = new Orders();

Order.Client = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "OrganizationAddressCollection")
.Elements(ns + "OrganizationAddress")
.Elements(ns + "CompanyName")
.FirstOrDefault()?.Value;
Order.Warehouse = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "Warehouse")
.Elements(ns + "Name")
.FirstOrDefault()?.Value;
Order.OrderReference = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "OrderNumber")
.FirstOrDefault()?.Value;
Order.CustomerReference = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "ClientReference")
.FirstOrDefault()?.Value;
Order.DocketSubType = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "Type")
.Elements(ns + "Code")
.FirstOrDefault()?.Value;
Order.ProductCode = xmlDocument.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "OrderLineCollection")
.Elements(ns + "OrderLine")
.Elements(ns + "Product")
.Elements(ns + "Code")
.FirstOrDefault()?.Value;

string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
connetionString = "Data Source=My Server;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword";
sql = "Exec InsertXML @Client='" + Order.Client +
"',@Warehouse = '" + Order.Warehouse +
"',@OrderReference='" + Order.OrderReference +
"',@CustomerReference='" + Order.CustomerReference +
"',@DocketSubtype='" + Order.DocketSubType +
"'";

connection = new SqlConnection(connetionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine(dataReader.GetValue(0) + " - " + dataReader.GetValue(1) + " - " + dataReader.GetValue(2));
}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
}

internal class Orders
{
public string Client { get; set; }
public string Warehouse{ get; set; }
public string OrderReference { get; set; }
public string CustomerReference { get; set; }
public string DocketSubType { get; set; }
public string ProductCode { get; set; }


}

Answer 1

我修改了代码，使其对sql注入变得安全，添加了资源处置，并根据普遍接受的规范更改了名称。

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Xml.Linq;
namespace ConApp5
{
public class Program
{
// The connection string must be defined in a single place of the class, not in every method.
private static string _connectionString = @"...";
static void Main()
{
// Don't lump the code together, break it down into methods with talking names.
foreach (var path in Directory.EnumerateFiles(".", "*.xml"))
{
Order order = ParseXml(path);
InsertData(order);
}
}

static Order ParseXml(string path)
{
XDocument xmlDocument = XDocument.Load(path);
XNamespace ns = xmlDocument.Root.GetDefaultNamespace();
var shipmentNode = xmlDocument.Element(ns + "UniversalShipment").Element(ns + "Shipment");
// Reuse the same node.
var orderNode = shipmentNode.Element(ns + "Order");
Order order = new Order();
order.Client = shipmentNode
.Element(ns + "OrganizationAddressCollection")
.Element(ns + "OrganizationAddress")
.Element(ns + "CompanyName").Value;
order.Warehouse = orderNode
.Element(ns + "Warehouse")
.Element(ns + "Name").Value;
order.OrderReference = orderNode
.Element(ns + "OrderNumber").Value;
order.CustomerReference = orderNode
.Element(ns + "ClientReference").Value;
order.DocketSubType = orderNode
.Element(ns + "DocketSubType")
.Element(ns + "Code").Value;
order.ProductCodes = orderNode
.Element(ns + "OrderLineCollection")
.Elements(ns + "OrderLine")
.Elements(ns + "Product")
.Elements(ns + "Code")
.Select(x => x.Value).ToList();
return order;
}
static void InsertData(Order order)
{
// We must release resources such as the connection and the command. `using` operator do it.
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string sql = "InsertXML"; // stored procedure name
using (var command = new SqlCommand(sql, connection))
{
// Explicitly specify that the command calls a stored procedure.
command.CommandType = CommandType.StoredProcedure;
// The names of the sql parameters are the same as in the stored procedure.
// Instead of SqlDbType.NVarChar, specify the real type.
command.Parameters.Add("Client", SqlDbType.NVarChar).Value = order.Client;
command.Parameters.Add("Warehouse", SqlDbType.NVarChar).Value = order.Warehouse;
command.Parameters.Add("OrderReference", SqlDbType.NVarChar).Value = order.OrderReference;
command.Parameters.Add("CustomerReference", SqlDbType.NVarChar).Value = order.CustomerReference;
command.Parameters.Add("DocketSubtype", SqlDbType.NVarChar).Value = order.DocketSubType;
var parameterCode = command.Parameters.Add("Code", SqlDbType.NVarChar);
foreach (var code in order.ProductCodes)
{
parameterCode.Value = code;
_ = command.ExecuteNonQuery();
}
}
}
}
}
}
internal class Order // singular
{
public string Client { get; set; }
public string Warehouse { get; set; }
public string OrderReference { get; set; }
public string CustomerReference { get; set; }
public string DocketSubType { get; set; }
public List<string> ProductCodes { get; set; } // plural
}

还必须更改Order类中的类型，并将sql参数的类型更改为数据库表中使用的类型。

Answer 2

您需要遍历xmlDocument。请尝试下面的代码，虽然我不确定是否xe。元素可以以相同的方式访问，也可以不以相同的方式访问。

using System;
using System.Linq;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WindowsFormsApp1
{
class Program
{
static void Main(string[] args)
{
XDocument xmlDocument = XDocument.Load(@"C:UserssebbyDesktopMLLXML to SQLsample.xml");
XNamespace ns = xmlDocument.Root.GetDefaultNamespace();
Orders Order = new Orders();

foreach (XElement xe in xmlDocument.Nodes())
{

Order.Client = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "OrganizationAddressCollection")
.Elements(ns + "OrganizationAddress")
.Elements(ns + "CompanyName")
.FirstOrDefault()?.Value;
Order.Warehouse = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "Warehouse")
.Elements(ns + "Name")
.FirstOrDefault()?.Value;
Order.OrderReference = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "OrderNumber")
.FirstOrDefault()?.Value;
Order.CustomerReference = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "ClientReference")
.FirstOrDefault()?.Value;
Order.DocketSubType = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "Type")
.Elements(ns + "Code")
.FirstOrDefault()?.Value;
Order.ProductCode = xe.Elements(ns + "UniversalShipment")
.Elements(ns + "Shipment")
.Elements(ns + "Order")
.Elements(ns + "OrderLineCollection")
.Elements(ns + "OrderLine")
.Elements(ns + "Product")
.Elements(ns + "Code")
.FirstOrDefault()?.Value;
string connetionString = null;
SqlConnection connection;
SqlCommand command;
string sql = null;
SqlDataReader dataReader;
connetionString = "Data Source=My Server;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword";
sql = "Exec InsertXML @Client='" + Order.Client +
"',@Warehouse = '" + Order.Warehouse +
"',@OrderReference='" + Order.OrderReference +
"',@CustomerReference='" + Order.CustomerReference +
"',@DocketSubtype='" + Order.DocketSubType +
"'";
connection = new SqlConnection(connetionString);
try
{
connection.Open();
command = new SqlCommand(sql, connection);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine(dataReader.GetValue(0) + " - " + dataReader.GetValue(1) + " - " + dataReader.GetValue(2));
}
dataReader.Close();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
}
}

internal class Orders
{
public string Client { get; set; }
public string Warehouse { get; set; }
public string OrderReference { get; set; }
public string CustomerReference { get; set; }
public string DocketSubType { get; set; }
public string ProductCode { get; set; }
}