Archive

Posts Tagged ‘SQL server’

How to send a serialized object to SQL server 2005 table

November 25, 2008 Leave a comment
I am actually working on project where I am building a data aquisition system where any incoming data needs to be store to an SQL server database.
The problem I was facing is that I did not know in advance how many parameters I will have to store to database. So far I was using few of them (less than 10) that I was passing though SQL parameters when building my data access from .Net SQL.Client class. Handling more parameters was not handy from SQL side and not quite dynamic. Then I have read on different post that XML can be used with SQL server 2005. Based on that I have to say that it is a great solution for dynamic storage of data.
 
The way I have implemented such solution is through object serialization.
I have defined a simple class named HistoryLog which serve as my data caracteristics as follow :
Serializable()]
[XmlRoot(“LogParam”)]

public class LogParameter

{
private string name; // parameter name
private string value; // parmaeter value

public LogParameter() { }

public LogParameter(string Name, string Value)

{name = Name;value = Value;}

[XmlAttribute(“Name”)]
public string Name
{
get { return name; }
set { name = value; }
}

[XmlAttribute(“value”)]
public string Value
{
get { return value; }
set { value = value; }
}
 From the class above, I will build a list of LogParameter that I will then serialize for used in store procedure.
For serialising it I am using code snipet below :

 XmlSerializer xmlSerialization = new System.Xml.Serialization.XmlSerializer(HistLog.GetType());
MemoryStream m_memStream = new System.IO.MemoryStream();
xmlSerialization.Serialize(m_memStream, HistLog);
string xmlDoc = System.Text.Encoding.UTF8.GetString(m_memStream.ToArray());

The resulting xmlDoc object will be then pass as string to SQL store procedure parameter.

From SQL side :

The use of OPENXML feature of SQL server 2005 was quite straight forward as long as you know where to start.
My store procedure is used as follow :

— Call stored procedure to create the memory tree

 

FROM
OPENXML(@docHandle, ‘/HistoryLog/LogParam, 2)
WITH
(
 [Name]
CHAR(50) ‘@Name’,
 
[Measure] DECIMAL(18,2) ‘@Value’
)

EXEC
sp_xml_removedocument  @docHandle  
 The content of your XML serialized object will be then copied into respective fields of your myTable

Hoping this point will be valuable for you as it has been for me Smile
You discover new things at the time you have a problem to solve

EXEC sp_xml_preparedocument @docHandle OUTPUT, @MeasureListDocument
INSERT INTO mytable
Name,
Measure)

SELECT
Name,Measure

Advertisements
Categories: SQL server Tags: