/// <summary>
/// This method executes a stored procedure. It should be used with FOR XML
/// commands only, without using Lock for concurrency
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
/// <param name="rootElementName">Name for the root element (it can be null)</param>
/// <param name="parameters">Parameters of the stored procedure</param>
/// <param name="returnValue">Value returned of the Stored Procedure</param>
/// <returns>An XmlNode with the return value of the stored procedure</returns>
protected virtual XmlNode ExecuteXml(string storedProcedureName,
string rootElementName, cParametroDatos[] parameters, out int returnValue)
{
IDataReader reader = null;
DbCommand command = null;
returnValue = NULL_VALUE;
try
{
IList<cParametroDatos> outputParameters;
Database dbAccess;
command = PrepareCommand(storedProcedureName, parameters,
out outputParameters, out dbAccess);
reader = dbAccess.ExecuteReader(command);
FillOutputParameters(ref outputParameters, command);
returnValue = (int)outputParameters[RETURN_VALUE_PARAM_INDEX].Value;
StringBuilder sb = new StringBuilder();
//Opens Root element
sb.Append(TAG_OPEN_START);
sb.Append(rootElementName);
sb.Append(TAG_CLOSE);
while (reader.Read())
{
sb.Append(reader.GetString(0));
}
//Closes Root element
sb.Append(TAG_OPEN_END);
sb.Append(rootElementName);
sb.Append(TAG_CLOSE);
XmlDocument xmlResult = new XmlDocument();
xmlResult.LoadXml(sb.ToString());
return xmlResult;
}
catch (Exception ex)
{
LastErrorMessage = ex.Message;
return null;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open && Transaction.Current == null)
{
command.Connection.Close();
}
}
}
/// <summary>
/// Execute a nonquery stored procedure and get its return value
/// </summary>
/// <param name="storedProcedureName">Stored procedure name using dbo.Name sintax</param>
/// <param name="parameters">Array of cParametroDatos</param>
/// <returns>Stored procedure return code</returns>
protected virtual int ExecuteScalar(string pStoredProcedureName, cParametroDatos[] pParameters)
{
DbCommand command = null;
try
{
IList<cParametroDatos> outputParameters;
Database dbAccess;
command = PrepareCommand(pStoredProcedureName, pParameters, out outputParameters, out dbAccess);
dbAccess.ExecuteNonQuery(command);
FillOutputParameters(ref outputParameters, command);
return (int)outputParameters[RETURN_VALUE_PARAM_INDEX].Value;
}
catch (Exception ex)
{
LastErrorMessage = ex.Message;
return NULL_VALUE;
}
finally
{
if (command != null && command.Connection != null && command.Connection.State == ConnectionState.Open && Transaction.Current == null)
{
command.Connection.Close();
}
}
}
/// <summary>
/// This method executes a stored procedure. It should be used with FOR XML
/// commands only, without using Lock for concurrency
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure to execute</param>
/// <param name="parameters">Parameters of the stored procedure</param>
/// <returns>An XmlNode with the return value of the stored procedure</returns>
protected XmlNode ExecuteXml(string pStoredProcedureName, cParametroDatos[] pParameters)
{
int returnValue;
XmlNode xmlNode = ExecuteXml(pStoredProcedureName, XML_ROOT_NODE, pParameters, out returnValue);
return (xmlNode == null) ? null : xmlNode.FirstChild.FirstChild;
}
/// <summary>
/// Extract one DataSet using all the parameters required
/// </summary>
/// <param name="storedProcedureName">Stored procedure name using dbo.Name sintax</param>
/// <param name="parameters">Array of cParametroDatos</param>
/// <returns>Full fill dataset</returns>
protected DataSet ExecuteDataSet(string pStoredProcedureName, cParametroDatos[] pParameters)
{
int returnValue;
return ExecuteDataSet(pStoredProcedureName, pParameters, out returnValue);
}