save directly to SQL

Posted by: quim on 8 September 2017, 2:39 pm EST

  • Posted 8 September 2017, 2:39 pm EST

    Hi

    Is it possible to save XML file from Spread directly to a SQL table without saving the file to the disk first?

    If possible, I need to save it to SQL and load it from SQL to Spread without creating any file in the disk.

     

    Regards

  • Replied 8 September 2017, 2:39 pm EST

    Are you saying you want to add a new table to your database that you created in spread? Or are you talking about a table that already existes in your Database? If you bind your spread to a Datasource, then whenever the Spread is updated the changes are saved back to the Database without creating a new file on disk.
  • Replied 8 September 2017, 2:39 pm EST

    is it's possible to have this in a VB.net code?

    I think I understand but not follow 100%  

  • Replied 8 September 2017, 2:39 pm EST

    Hi

     

    I need to save Spread as a template. What I've done in the past was spend some time formatting the Spread control and finally I save it to disk so that I could use it in order to fill it with data.

    With Spread I can save it as a XML file and open it from the file (using Sreadp.save(...) and Spread.Open(..)). Now I want to avoid files so that my client application can get any Template from database. This way I Save Spread directly to an existing table and I  Load Spread from this table, instead of saving and loading from a file.

     

    Regards

  • Replied 8 September 2017, 2:39 pm EST

    Yes, that is possible. You can use following methods. You can save the spread to a stream, then, store the content of the steam to database. To open the spread saved in the database, he just need to get the content saved before from the database and to create a stream from it, and then call Open method to open the spread.


      


    public bool Open(Stream stream)


     


    public bool Save(Stream stream, bool dataOnly)

  • Replied 8 September 2017, 2:39 pm EST

    ----- REMOVED from Lagen
  • Replied 8 September 2017, 2:39 pm EST

    Hi

     

    Thanks. It's working

     

    Regards

  • Replied 8 September 2017, 2:39 pm EST

    Hello,

    Yes, you can follow the similar approach in vb.net. Here is the documentation link that describes how you can use the Open and Save method of Spread in Vb.Net to save the Spread to a stream:

                  http://www.gcpowertools.com/help/spreadnet6/WF2/FarPoint.Win.Spread~FarPoint.Win.Spread.FpSpread~Save%28Stream,Boolean%29.html

                  http://www.gcpowertools.com/help/spreadnet6/WF2/FarPoint.Win.Spread~FarPoint.Win.Spread.FpSpread~Open%28Stream%29.html

    Once the spread is saved to a stream, you can save the stream content to a database table and later read the content from the database to create the stream again and load it into the Spread. You can find several articles describing this procedure for SQL, here is one of them:

                  http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream

    Hope it will help you. Please let me know if you have any queries further.

    Thanks,

    Manpreet Kaur

  • Replied 8 September 2017, 2:39 pm EST

    Hello,

    Yes, you are right Spread does not have a direct way to save Spread to SQL. All it has to do is to save the Spread in a stream as described in the documentation links above. Once it has been saved to the stream, you would need to create a new table into your database, this table would have a column or field of type FileStream. Once this table is created you can use the .Net code to convert your stream into a byte array and save that byte array into the FileStream field of the database. I do not have a direct code for the same, however the link provided in the last post describes all the steps along with the code needed for the same in detail. I would request you to go through the same and here is the another one, again describing the code that would be needed to save the stream to database:

                               http://weblogs.asp.net/aghausman/archive/2009/03/16/saving-and-retrieving-file-using-filestream-sql-server-2008.aspx

    Hope it will help you. Please let me know if you have any queries further.

    Thanks,

    Manpreet 

  • Replied 8 September 2017, 2:39 pm EST

    I success now.

     

    Thanks for your help :) 

  • Replied 8 September 2017, 2:39 pm EST

    I use this code, but then it's try to save to a file "..\bin\savespreadstream.xml"
     
    But I need it's save to the SQL database.
     
    ---------------------------------------------------------------------------------------------------------------------------- 
     
     
     
    Imports System.IO
    Imports System.Data.OleDb
    Public Class Form1
        Public Overloads Function Save( _
       ByVal stream As Stream, _
       ByVal dataOnly As Boolean _
    ) As Boolean
        End Function
        Dim SQL As String

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim fpSpread1 As New FarPoint.Win.Spread.FpSpread()
            Dim shv As New FarPoint.Win.Spread.SheetView()
            fpSpread1.Location = New Point(10, 10)
            fpSpread1.Height = 200
            fpSpread1.Width = 400
            Controls.Add(fpSpread1)
            fpSpread1.Sheets.Add(shv)
            fpSpread1.ActiveSheet.ColumnCount = 4
            fpSpread1.ActiveSheet.RowCount = 4
            Dim fileName As String
            fileName = "..\bin\savespreadstream.xml"
            Dim stream As System.IO.Stream
            stream = System.IO.File.Create(fileName, System.IO.FileMode.Create)
            fpSpread1.Save(stream, False)
            SQL = "insert into spreadsheets (name,xml) values('spread1'," & stream & ")"
    sqlWrite() 
        End Sub


        Private Sub sqlWrite()

            Dim connSQL As OleDbConnection = GetConnection()
            Dim cmd As New OleDbCommand
            Dim objCmd As New OleDbCommand

            connSQL.Open()
            objCmd = New OleDbCommand(SQL, connSQL)
            objCmd.ExecuteNonQuery()

            connSQL.Close()
            connSQL.Dispose()
        End Sub
    End Class
     
     
     
    -------------------------------------------
     
    Public Function GetConnection() As OleDb.OleDbConnection
            Return New OleDb.OleDbConnection("Provider=SQLOLEDB.1;User ID=user;Password=password;Initial Catalog=MyDatabase;Data Source=10.10.10.10")
    End Function  
  • Replied 8 September 2017, 2:39 pm EST

    Hello,

    Please have a look at the attached sample created using the implementation defined above and which is working fine at my end. To execute the sample at your end, you would need to enable FileStreamAccess for your SQL server and create a database with a datatable having a field to store filestream. You can follow the steps described in the following article to create a database and enable FileStreamAccess:

                  http://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream

    Once you are done with the creation of the database, all you need to do is replace the connection string in the attached sample with your connection string and the table name in the query to execute the attached sample, to see how you can save Spread with all its settings to a database and later load the same in the Spread.

    Hope it will help you. Please let me know if you have any queries further.

    Thanks,

    Manpreet Kaur


    2012/11/SaveToSql.zip
  • Replied 8 September 2017, 2:39 pm EST

    paul1960:

    Yes, that is possible. You can use following methods. You can save the spread to a stream, then, store the content of the steam to database. To open the spread saved in the database, he just need to get the content saved before from the database and to create a stream from it, and then call Open method to open the spread.


      


    public bool Open(Stream stream)


     


    public bool Save(Stream stream, bool dataOnly)

     

    Thanks for your help.

    But I think I give up now.

    Some have success and I was thinking fpoint have success to.

    I think for me no meaning spend more hours with this, better for me to try other way to solve this problem :(

    But anyway thanks for your help. 

  • Replied 8 September 2017, 2:39 pm EST

    you don't have any code that actually save a spread to SQL server.

    I think much more easy for me, I am not a native English speaker and understand code much better.

    or you see what is wrong in my code above? 

    I will be really happy if you could help me.

    I think I spend about 8 hours to fix this now, maybe for you it's more easy ;) 

    Thanks for your support even you get stupid customer as me.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels