Click here to Skip to main content
12,952,398 members (41,123 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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();
}
Posted 18-Aug-13 21:30pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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);
        }
    }
}

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. 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();
    }
}


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;
    }


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;
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,289
CHill60 3,490
Maciej Los 3,103
Jochen Arndt 1,975
ppolymorphe 1,900


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 19 Aug 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100