/// <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); }