我在 c# 控制台应用程序中的这个插入存储过程中做错了什么

2024-05-20 • 问答

我在 c# 控制台中制作了一个应用程序，它读取 xml 文件并插入一些需要存储的关键属性。我正在尝试使用一个存储过程来完成它，该过程进入在我的 Program.cs 中调用的 Dataaccess 类。我将首先展示我的 Dataaccess 类以及我如何制作 SP_Insert

class Dataaccess
{
    SqlConnection connection = null;

    public Dataaccess()
    {
        connection = new SqlConnection(@"Persist Security Info=False;Integrated Security=true; Initial Catalog=Teste;Data Source=Teste;Server=localhost\sqlexpress");
    }

    private void ManageConnectionState()
    {
        if (connection == null || connection.ConnectionString.Equals(""))
        {
            connection = new SqlConnection(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = Teste; Data Source = Teste; Server = localhost\sqlexpress");
            connection.Open();
        }
        else
          if (connection.State.Equals(ConnectionState.Closed))
        {
            connection.Open();
        }
        else
        {
            connection.Dispose();
            connection.Close();
        }
    }

    #region Insert
    public void SP_Insert(string XMLPath,string XMLName,string BatchClassname,string BatchName,string BatchDescription,string Error,string ErrorImagePath,string Done)
    {
        try
        {
            ManageConnectionState();
            SqlCommand command = new SqlCommand("Sp_Insert",connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@XMLPath",XMLPath);
            command.Parameters.AddWithValue("@XMLName",XMLName);
            command.Parameters.AddWithValue("@BatchClassname",BatchClassname);
            command.Parameters.AddWithValue("@BatchName",BatchName);
            command.Parameters.AddWithValue("@BatchDescription",BatchDescription);
            command.Parameters.AddWithValue("@Error",Error);
            command.Parameters.AddWithValue("@ErrorImagePath",ErrorImagePath);
            command.Parameters.AddWithValue("@Done",Done);
            command.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Erro: " + ex.Message);
        }
        finally
        {
            ManageConnectionState();
            connection.Close();
        }

    }
    #endregion
}

}

我认为这部分都是正确的，接下来的两个是我不知道的现在这是我的 Progam.cs 文件

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using system.xml;
using System.Data;

namespace XMLReaderConsoleAPP1
{
    class Program
    {
        static void Main()
        {
            Dataaccess da = new Dataaccess();
            try
            {
                var path = @"C:\XMLFiles";
                DirectoryInfo di = new DirectoryInfo(path);

                foreach (var file in Directory.GetFiles(path,"*.xml"))
                {
                    ProcessFile(file);
                }
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Erro: {0}",ex.Message);
                return;
            }

            void ProcessFile(string Filename)
            {
            bool temErro = false;
            Console.WriteLine("A processar xml: " + Filename);
            XmlDocument xml = new XmlDocument();
            xml.Load(Filename);

            //XmlNodeList xnLista = xml.SelectNodes(@"//Pages/Page");
            //Console.WriteLine($"Selected {xnLista.Count} nodes");



            XmlNodeList xnLista3 = xml.SelectNodes(@"//ImportSession/Batches");
            XmlNodeList xnLista2 = 
            xml.SelectNodes(@"//ImportSession/Batches/Batch/Documents/Document/Pages/Page");
            //Console.WriteLine($"Selected {xnLista3.Count} nodes");
            //Console.WriteLine($"Selected {xnLista2.Count} nodes");
            //int j = 0;
            foreach (XmlNode xn in xnLista2)
            {
                //Console.WriteLine($"{++i} {xn.Name}: {xn.Attributes["ImportFileName"].Value}");
                string processed = "@//Batch[Processed]";
                //Console.WriteLine($"{++j} {xn.Name}: {xn.Attributes["Value"].Value}");
                string BatchClassname = xn.Attributes["BatchClassname"]?.Value;
                string BatchName = xn.Attributes["Name"]?.Value;
                string BatchDescription = xn.Attributes["Description"]?.Value;
                //string process = xn.Attributes["Processed"]?.Value;
                string error = xn.Attributes["ErrorMessage"]?.Value;
                string errorImagePath = xn.Attributes["ErrorCode"]?.Value;
                if (!string.IsnullOrEmpty(processed)) //xml já foi processado
                {
                    if (!string.IsnullOrEmpty(error))
                    {
                        string page = xn.Attributes["ImportFileName"].Value;
                        Console.WriteLine("Página com erro: " + page);
                        temErro = true;
                        da.SP_Insert(page,Filename,BatchClassname,BatchName,BatchDescription,error,errorImagePath,processed);

                       
                    }
                }
            }

            if (temErro)
                Console.WriteLine("Ficheiro com erro: " + Filename);
            else
                Console.WriteLine("Ficheiro processado: " + Filename);

            //Console.WriteLine(Filename);
        }


    }

}

}

我一直在使用 xn.Attributes 来获取我需要的信息，但由于某种原因，某些字符串没有返回值（从 BatchClassname 到结尾）

我觉得很奇怪，因为前两个确实显示了信息。

现在这是我的存储过程

     USE [Teste]
   GO
/****** Object:  StoredProcedure [dbo].[SP_Insert]    Script Date: 22/01/2021 09:32:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_Insert]
@XMLPath nvarchar(250),@XMLName nvarchar(250),@BatchClassname nvarchar(250),@BatchName nvarchar(250),@BatchDescription nvarchar(250),@Error bit,@ErrorImagePath nvarchar(250),@Done bit
AS
BEGIN
Insert into T_XMLFiles (XMLPath,XMLName,Error,ErrorImagePath,Done)
Values(@XMLPath,@XMLName,@BatchClassname,@BatchName,@BatchDescription,@Error,@ErrorImagePath,@Done)
END

我还要留下一个 xml 文件是什么样子的例子，因为它们都遵循相同的结构

    <ImportSession>
  <Batches>
    <Batch Name="MSG_SaintGobainPam_20210118" Description="SaintGobain_20210118" BatchClassname="SAINTGOBAIN - Faturas Notas" Processed="1">
      <BatchFields>
        <BatchField Name="CAPALOTE" Value="0" />
        <BatchField Name="NCONTENTOR" Value="0" />
      </BatchFields>
      <Documents>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047804" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047804.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047842" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047842.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047843" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047843.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047849" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047849.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047853" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047853.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047854" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047854.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047855" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047855.pdf" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047860" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047860.pdf" ErrorCode="20" ErrorMessage="Illegal file format (\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047860.pdf)" />
          </Pages>
        </Document>
        <Document FormTypeName="DOC_SaintGobain_Faturas_Notas">
          <IndexFields>
            <IndexField Name="ETIQUeta" Value="ZE80047861" />
          </IndexFields>
          <Pages>
            <Page ImportFileName="\\umm\c$\acxmlaid\SaintGobain\20210118\ZE80047861.pdf" />
          </Pages>
        </Document>
      </Documents>
    </Batch>
  </Batches>
</ImportSession>

就像我一开始说的那样，我试图这样做，就好像我是在 Windows 窗体中制作它一样，但没有单击按钮并插入信息，如果你们中的任何人可以帮助我，我将不胜感激。谢谢大家，如果您需要更多信息来帮助我，请随时索取。

a19811204 回答：我在 c# 控制台应用程序中的这个插入存储过程中做错了什么

我想，在这一点上，反序列化和在对象图上工作：

using System;
using System.Xml;
using System.Xml.Serialization;
using System.IO;
                    
public class Program
{
    
    
    public static void Main()
    {
         foreach( string file in Directory.EnumerateFiles(@"C:\XMLFiles","*.xml"))
         {
              ProcessFile(file);
         }
    }

    public static void ProcessFile(string filePath)
    {
        XmlSerializer serializer = new XmlSerializer(typeof(ImportSession));

        // vv provide filestream from your path
        var fileStream = File.OpenRead(filePath);
        // https://docs.microsoft.com/en-us/dotnet/api/system.io.file.openread?view=net-5.0
        // let's assume you dealt with that.

        // Deserialize from XML. That means: we are running an engine against the xml
        // that will "translate" all the attributes and nested nodes into
        // into objects with properties for us,so we can use it as we would
        // normally do any other data structure.
        ImportSession session = (ImportSession)serializer.Deserialize(fileStream);

        // For each batch in the session ...
        foreach( Batch batch in session.Batches )
        {
            // For each doc inside the batch of the session...
            foreach( Document doc in batch.Documents )
            {
                // For each page in each doc in the batch of the session ...
                foreach( Page page in doc.Pages )
                {
                    // Your DB-Insert here.
                    // Inside this scope,we have all the values for
                    // - current session (can be only 1)
                    // - current batch ( could be more than one )
                    // - current document in batch 
                    // - current page of doc in batch

                    // I used Cosole.WriteLine just for Demo.
                    Console.WriteLine(
                        "BatchName='{0}',BatchDescription='{1}',BatchClassName='{2}'," + 
                        "ImportFileName={3},ErrorMessage={4},HasError={5},Processed={6}",batch.Name,batch.Description,batch.BatchClassName,page.ImportFileName,page.ErrorMessage,page.HasError,batch.Processed
                    );
                }
            }
        }
        
    }
}

public class ImportSession
{
    public Batch[] Batches {get; set;}
}

public class Batch
{
    [XmlAttribute]
    public string Name {get; set;}
    [XmlAttribute]
    public string Description {get; set;}
    [XmlAttribute]
    public string BatchClassName {get; set;}
    [XmlAttribute]
    public bool Processed {get; set;}
    
    // Let's ignore BatchFields for now
    
    public Document[] Documents {get; set;}
}

public class Document
{
    [XmlAttribute]
    public string FormTypeName {get; set;}
    public IndexField[] IndexFields {get; set;}
    public Page[] Pages {get; set;}
}

public class IndexField
{
    [XmlAttribute]
    public string Name {get; set;}
    [XmlAttribute]
    public string Value {get; set;}
}

public class Page
{
    [XmlAttribute]
    public string ImportFileName{get; set;}
    [XmlAttribute]
    public string ErrorCode {get; set;}
    [XmlAttribute]
    public string ErrorMessage {get; set;}
    [XmlIgnore]
    public bool HasError => !string.IsNullOrWhiteSpace(ErrorMessage);
}

见实战：https://dotnetfiddle.net/dNSU1K

一些参考资料供您阅读：

@Polyfun 指的是您的 DataAccess 的几个问题：

class DataAccess
{
    // We don't need this here.
    // SqlConnection connection = null;
    // better only keep the connection string
    private readonly string connectionString;

    // Maybe you want to inject the connectionstring for better 
    // configurability. You don't need to recompile for different DB.
    public DataAccess( string connectionString )
    {
        this.connectionString = connectionString;
        //connection = new SqlConnection(@"Persist Security Info=False;Integrated Security=true; Initial Catalog=Teste;Data Source=Teste;Server=localhost\sqlexpress");
    }

    /* We don't this _at all_
    private void ManageConnectionState()
    {
        if (connection == null || connection.ConnectionString.Equals(""))
        {
            connection = new SqlConnection(@"Persist Security Info = False; Integrated Security = true; Initial Catalog = Teste; Data Source = Teste; Server = localhost\sqlexpress");
            connection.Open();
        }
        else
          if (connection.State.Equals(ConnectionState.Closed))
        {
            connection.Open();
        }
        else
        {
            connection.Dispose();
            connection.Close();
        }
    }
    */

    public void SP_Insert(string XMLPath,string XMLName,string BatchClassName,string BatchName,string BatchDescription,bool Error,string ErrorImagePath,bool Done)
    {
        using var connection = new SqlConnection(connectionString);
        try
        {
            using var command = new SqlCommand("Sp_Insert",connection);
            command.CommandType = CommandType.StoredProcedure;

            // The other issue: "AddWithValue" can get you into trouble
            // so consider using e.g.
            command.Parameters.Add("@XMLPath",SqlDbType.NText).Value = XMLPath;
            command.Parameters.Add("@XMLName",SqlDbType.NText).Value = XMLName;
            command.Parameters.Add("@BatchClassName",SqlDbType.NText).Value = BatchClassName;
            command.Parameters.Add("@BatchName",SqlDbType.NText).Value = BatchName;
            command.Parameters.Add("@BatchDescription",SqlDbType.NText).Value = BatchDescription;
            command.Parameters.Add("@Error",SqlDbType.Bit).Value = Error;
            command.Parameters.Add("@ErrorImagePath",SqlDbType.NText ).Value = ErrorImagePath;
            command.Parameters.Add("@Done",SqlDbType.Bit).Value = Done;
            command.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Erro: " + ex.Message);
        }
    }

}

c#data-access sql-server stored-procedures xml

本文链接：https://www.f2er.com/1059661.html

我在 c# 控制台应用程序中的这个插入存储过程中做错了什么

a19811204 回答：我在 c# 控制台应用程序中的这个插入存储过程中做错了什么

大家都在问