Hello,
I am looking for sample code if any, to save configuration XML file to database instead of saving to file.
Code Sample:
UltraTilePanel1.SaveAsXml("PanelFile.XML")
Is there any way I can save it directly to database?
Please let me know if you need more information.
Thanks
Imran
Hello Imran,
There are different approaches to solve this task. One possible solution could be if you are using the code below:
#1 How to save your settings in database:
private void ultraButton1_Click(object sender, EventArgs e)
{
byte[] byteArray = new byte[2048];
ultraTilePanel1.SaveAsXml(new MemoryStream(byteArray)); ;
SqlConnection conn = new SqlConnection("Data Source=IGBGSOFDS22;Initial Catalog=Test;Integrated Security=True");
conn.Open();
SqlCommand command2 = new SqlCommand("UPDATE [Table_A] set Binary = @Binary WHERE [ID] = 1", conn);
command2.Parameters.Add("@Binary", SqlDbType.VarBinary);
command2.Parameters["@Binary"].SqlValue = byteArray;
command2.ExecuteNonQuery();
ms.Close();
conn.Close();
}
#2 How to load your settings from database:
private void ultraButton4_Click(object sender, EventArgs e)
SqlCommand command2 = new SqlCommand("SELECT [Binary] FROM [Table_A] WHERE [ID] = 1", conn);
SqlDataReader myReader = command2.ExecuteReader();
while(myReader.Read())
MemoryStream ms = new MemoryStream((byte[])myReader.GetSqlBinary(0));
ultraTilePanel1.LoadFromXml(ms);
myReader.Close();
Let me know if you have any questions.
Regards
Thanks Georgi for your reply.
I will verify and let you know my feedback.
Only concern I have, is there any way to store configuration as "XML" instead of "Binary" to database.
Cause in my situation, we are storing configuration as "XML" data type.
Hi Imran,
The approach with Binary columns works properly (if you need a sample, let me know) . To be honest I prefer Binary approach instead of XML solution. Nevertheless if you want to use XML, you could use the similar approach with XmlReader
Let me know if you have any further questions.
Thanks for your response and shared information with our community.
If you have any questions, feel free to write us.
Georgi,
While encoding Memory Stream to String( XML in String representation ) and loading back to XML I experienced following issue.
Detail:
Exception:
System.Xml.XMLException
Message : '.', hexadecimal value 0x00, is an invalid character. Line 47, position 1.
Resolution:
Used following blog and wrote equivalent VB.NET Code.
Reference:
http://social.msdn.microsoft.com/Forums/en-US/30d7cfd7-3de0-4082-930d-329b0265f174/solved-hexadecimal-value-0x00-is-an-invalid-character-xml
My VB.NET Equivalent Code:
Private Function NullRemover(ByVal bArray() As Byte) As Byte() Dim iv As Integer = 0 Dim temp() As Byte = New Byte(bArray.Length) {} For iv = 0 To bArray.Length - 1 Step 1 If bArray(iv) = &H0 Then Exit For End If temp(iv) = bArray(iv) Next Dim noNull() As Byte = New Byte(iv) {} For iv = 0 To noNull.Length - 1 Step 1 noNull(iv) = temp(iv) Next Return noNull End Function
Thanks,
Hello Imran
Imran Latif said:This approach to store XML to database is efficient ?
I`m not familiar with your final goals, so it is difficult for me to give an answer of your question, but the approach with XML in database has one big advantage and it is that, you are able to modify your XML on a database level. If you have such kind of intensions, then I think it is the right approach, otherwise you could use binary datatype
Thanks for your help.
That works for me.
Now to Store XML Configuration I doing following.
Dim byteArray(5000) As Byte UltraTilePanel1.SaveAsXml(New MemoryStream(byteArray))
Dim strXML As String = System.Text.Encoding.Default.GetString(byteArray)
Then I store strXML to database to field as XML.
For Loading I am simply using
UltraTilePanel1.LoadXml(XML as String)
My concern:
To set memory limit of byteArray you should know XML File which you are going to store to database or some guess work.
Question:
This approach to store XML to database is efficient ?
Thanks for your quick response.
I will verify and update you with feedback soon.