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

    <SPAN style="COLOR:#1f497d;"><FONT size=3><FONT face=Calibri>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.<o:p></o:p></FONT></FONT></SPAN>


    <SPAN style="COLOR:#1f497d;"><o:p><FONT size=3 face=Calibri> </FONT></o:p></SPAN><SPAN style="COLOR:#1f497d;"><o:p><FONT size=3 face=Calibri> </FONT></o:p></SPAN>


    <SPAN style="FONT-FAMILY:'Verdana','sans-serif';COLOR:blue;FONT-SIZE:12pt;">public</SPAN><SPAN style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"> <SPAN style="COLOR:blue;">bool</SPAN> Open(<SPAN style="COLOR:#2b91af;">Stream</SPAN> stream)<o:p></o:p></SPAN>


    <SPAN style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"><o:p> </o:p></SPAN>


    <SPAN style="FONT-FAMILY:'Verdana','sans-serif';COLOR:blue;FONT-SIZE:12pt;">public</SPAN><SPAN style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"> <SPAN style="COLOR:blue;">bool</SPAN> Save(<SPAN style="COLOR:#2b91af;">Stream</SPAN> stream, <SPAN style="COLOR:blue;">bool</SPAN> dataOnly)<o:p></o:p></SPAN>

  • Replied 8 September 2017, 2:39 pm EST

    <div>----- REMOVED from Lagen</div>
  • 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

    <div>I use this code, but then it's try to save to a file "..\bin\savespreadstream.xml"</div><div> </div><div>But I need it's save to the SQL database.</div><div> </div><div>---------------------------------------------------------------------------------------------------------------------------- </div><div> </div><div> </div><div> </div><div>Imports System.IO</div><div>Imports System.Data.OleDb</div><div>Public Class Form1</div><div>    Public Overloads Function Save( _</div><div>   ByVal stream As Stream, _</div><div>   ByVal dataOnly As Boolean _</div><div>) As Boolean</div><div>    End Function</div><div>    Dim SQL As String</div><div>
    </div><div>    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click</div><div>        Dim fpSpread1 As New FarPoint.Win.Spread.FpSpread()</div><div>        Dim shv As New FarPoint.Win.Spread.SheetView()</div><div>        fpSpread1.Location = New Point(10, 10)</div><div>        fpSpread1.Height = 200</div><div>        fpSpread1.Width = 400</div><div>        Controls.Add(fpSpread1)</div><div>        fpSpread1.Sheets.Add(shv)</div><div>        fpSpread1.ActiveSheet.ColumnCount = 4</div><div>        fpSpread1.ActiveSheet.RowCount = 4</div><div>        Dim fileName As String</div><div>        fileName = "..\bin\savespreadstream.xml"</div><div>        Dim stream As System.IO.Stream</div><div>        stream = System.IO.File.Create(fileName, System.IO.FileMode.Create)</div><div>        fpSpread1.Save(stream, False)</div><div>        SQL = "insert into spreadsheets (name,xml) values('spread1'," & stream & ")"</div><div><span class="Apple-tab-span" style="white-space:pre;"> </span>sqlWrite() </div><div>    End Sub</div><div>
    </div><div>
    </div><div>    Private Sub sqlWrite()</div><div>
    </div><div>        Dim connSQL As OleDbConnection = GetConnection()</div><div>        Dim cmd As New OleDbCommand</div><div>        Dim objCmd As New OleDbCommand</div><div>
    </div><div>        connSQL.Open()</div><div>        objCmd = New OleDbCommand(SQL, connSQL)</div><div>        objCmd.ExecuteNonQuery()</div><div>
    </div><div>        connSQL.Close()</div><div>        connSQL.Dispose()</div><div>    End Sub</div><div>End Class</div><div> </div><div> </div><div> </div><div>-------------------------------------------</div><div> </div><div style="font-family:Tahoma, Arial, Helvetica;font-size:11.818181991577148px;">Public Function GetConnection() As OleDb.OleDbConnection</div><div style="font-family:Tahoma, Arial, Helvetica;font-size:11.818181991577148px;">        Return New OleDb.OleDbConnection("Provider=SQLOLEDB.1;User ID=user;Password=password;Initial Catalog=MyDatabase;Data Source=10.10.10.10")</div><div><span style="font-family:Tahoma, Arial, Helvetica;font-size:11.818181991577148px;">End Function </span> </div>
  • 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

    <BLOCKQUOTE><div><img src="/wp-content/uploads/CS_images/icon-quote.gif"> paul1960:</div><div>

    <span style="COLOR:#1f497d;"><font size="3"><font face="Calibri">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.<o:p></o:p></font></font></span>


    <span style="COLOR:#1f497d;"><o:p><font size="3" face="Calibri"> </font></o:p></span><span style="COLOR:#1f497d;"><o:p><font size="3" face="Calibri"> </font></o:p></span>


    <span style="FONT-FAMILY:'Verdana','sans-serif';COLOR:blue;FONT-SIZE:12pt;">public</span><span style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"> <span style="COLOR:blue;">bool</span> Open(<span style="COLOR:#2b91af;">Stream</span> stream)<o:p></o:p></span>


    <span style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"><o:p> </o:p></span>


    <span style="FONT-FAMILY:'Verdana','sans-serif';COLOR:blue;FONT-SIZE:12pt;">public</span><span style="FONT-FAMILY:'Verdana','sans-serif';FONT-SIZE:12pt;"> <span style="COLOR:blue;">bool</span> Save(<span style="COLOR:#2b91af;">Stream</span> stream, <span style="COLOR:blue;">bool</span> dataOnly)<o:p></o:p></span>

    </div></BLOCKQUOTE>

     

    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