如何将xml文件数据插入Mysql数据库表 [英] How to insert xml file data into Mysql Database Table

问题描述

我有一个（形成良好的）XML文件，需要将其数据插入到MYSQL数据库表中。

Plz提供一些代码或任何想法...

它正在做什么...

i hv a (Well Formed)XML file and need to insert its data into MYSQL Database table.
Plz Provide m some code or any idea...
its what i m doing...

OpenFileDialog fd = new OpenFileDialog();
 fd.Title = "Select file to be upload";  if (fd.ShowDialog() == DialogResult.OK)
                {
                    txtFilePath.Text = fd.FileName.ToString();//xml file path
GlobalDs.ReadXml(txtFilePath.Text);
                    GVPreview.DataSource = GlobalDs;
                    GVPreview.DataMember = GlobalDs.Tables[0].ToString();
}

推荐答案

好吧，你有两个基本选项。

1.你可以存储xml内容为文本。这样，您的表数据将是nvarchar（max），您将把文件读作标准文本文件：

well, you have two basic options.
1. You can store the xml contents as text. This way, your table data will be nvarchar(max) and you will read the file as a standard text file:

using System;
using System.IO;

class Test
{
    public static void Main()
    {
        try
        {
            using (StreamReader sr = new StreamReader("TestFile.txt"))
            {
                String line = sr.ReadToEnd();
                Console.WriteLine(line);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("The file could not be read:");
            Console.WriteLine(e.Message);
        }
    }
}

然后像往常一样（通过ADO或其他方式）将line插入数据库。从DB读取时，您可以按如下方式将字符串解析为XML：

Then you insert "line" to your database as usual (by ADO or other means). When reading from DB, you can parse the string to XML as follows:

using System;
using System.Xml;

namespace StringToXml
{
 class Program
 {
  static void Main(string[] args)
  {
   string s = "hello";
   XmlDocument xm = new XmlDocument();
   xm.LoadXml(string.Format("<root>{0}</root>", s));

   Console.WriteLine(xm.InnerXml);
   Console.ReadKey();
  }
 }
}

2.您将文件作为二进制数据插入。 />


如何插入数据库

2. You insert the file as binary data.

How to insert to DB

public static void databaseFilePut(string varFilePath) {
    byte[] file;
    using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read)) {
        using (var reader = new BinaryReader(stream)) {
            file = reader.ReadBytes((int) stream.Length);       
        }          
    }
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlWrite = new SqlCommand("INSERT INTO Raporty (RaportPlik) Values(@File)", varConnection)) {
        sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;
        sqlWrite.ExecuteNonQuery();
    }
}

如何阅读DB

How to read from DB

public static void databaseFileRead(string varID, string varPathToNewLocation) {
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write)) 
                    fs.Write(blob, 0, blob.Length);
            }
    }
}

如何从内存流中插入

How to insert from memory stream

public static int databaseFilePut(MemoryStream fileToPut) {
        int varID = 0;
        byte[] file = fileToPut.ToArray();
        const string preparedCommand = @"
                    INSERT INTO [dbo].[Raporty]
                               ([RaportPlik])
                         VALUES
                               (@File)
                        SELECT [RaportID] FROM [dbo].[Raporty]
            WHERE [RaportID] = SCOPE_IDENTITY()
                    ";
        using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
        using (var sqlWrite = new SqlCommand(preparedCommand, varConnection)) {
            sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

            using (var sqlWriteQuery = sqlWrite.ExecuteReader())
                while (sqlWriteQuery != null && sqlWriteQuery.Read()) {
                    varID = sqlWriteQuery["RaportID"] is int ? (int) sqlWriteQuery["RaportID"] : 0;
                }
        }
        return varID;
    }

如何从DB读入内存流

How to read from DB into memory stream

public static MemoryStream databaseFileRead(string varID) {
    MemoryStream memoryStream = new MemoryStream();
    using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails))
    using (var sqlQuery = new SqlCommand(@"SELECT [RaportPlik] FROM [dbo].[Raporty] WHERE [RaportID] = @varID", varConnection)) {
        sqlQuery.Parameters.AddWithValue("@varID", varID);
        using (var sqlQueryResult = sqlQuery.ExecuteReader())
            if (sqlQueryResult != null) {
                sqlQueryResult.Read();
                var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);
                //using (var fs = new MemoryStream(memoryStream, FileMode.Create, FileAccess.Write)) {
                memoryStream.Write(blob, 0, blob.Length);
                //}
            }
    }
    return memoryStream;
}

这篇关于如何将xml文件数据插入Mysql数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！