I just want to make sure I am on the right path, I am trying to load and return an xml document from c# method, by reading in XML from xml data type column in SQL server 2005. Please advise. Thanks.
Here is what i wrote:
private const string SQLGETCNFS = "SELECT EntityDefinitionXML FROM EntityDefinition WHERE EntityID = ";
/// <summary>
/// Method retrieves XML Entity Definition from the configuration database
/// </summary>
/// <param name="entityID"></param>
/// <returns>XmlDocument Type</returns>
public XmlDocument GetCustomerCNSFData(string entityID)
{
//connect to config database
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = SQLGETCNFS + entityID.ToString();
IDataReader reader = db.ExecuteReader(CommandType.Text, sqlCommand);
XmlDocument doc = new XmlDocument();
while (reader.Read())
{
string str = reader.GetString(2);
doc.LoadXml(str);
}
return doc;
}
Loading xmldocument by reading XML data type column from SQL Server
Little_Dice
Sure, I have implemented it so its using parameterized query. Here is the working code, to retrieve XML datatype from SQL Server 2005 utilizing Microsoft Enterprise Library. Thanks.
public class DataFormatter
{
private const string SQLGETCNFS = "SELECT CustomerEntityDefXML FROM tblCustomerEntityDef WHERE EntityID = @EntityID AND CustomerID = @CustomerID";
/// <summary>
/// Method retrieves XML Entity Definition from the configuration database
/// </summary>
/// <param name="entityID"></param>
/// <returns>XmlDocument Type</returns>
public XmlDocument GetCustomerCNSFData(int entityID, string customerID)
{
XmlDocument xmlDoc = new XmlDocument();
try
{
//Create a database object
Database db = DatabaseFactory.CreateDatabase();
//Specify a sql statement with parameters..
string sqlCommand = SQLGETCNFS;
//Get a GetSqlStringCommandWrapper to specify the query and parameters
DbCommand command = db.GetSqlStringCommand(sqlCommand);
// Set the parameter's value
db.AddInParameter(command, "@EntityID", DbType.Int32, entityID);
db.AddInParameter(command, "@CustomerID", DbType.String, customerID);
IDataReader reader = db.ExecuteReader(command);
while (reader.Read())
{
string str = reader.GetString(0);
xmlDoc.LoadXml(str);
}
}
catch (Exception ex)
{
ExceptionManager.Publish(ex);
}
return xmlDoc;
}
}
Maksim Simkin
1. Pass 0 to the GetString() call, since the xml column you are projecting will be the first (and only) column in the output.
2. I strongly suggest parameterizing the SQL to pass in the entityID. I.e.
private const string SQLGETCNFS = "SELECT EntityDefinitionXML FROM EntityDefinition WHERE EntityID = @entity_id";
and adapt your db.ExecuteReader call to handle parameters.