/// <summary>
/// Gets the specified test as string xml
/// </summary>
/// <param name="adminId"></param>
/// <returns></returns>
public static string Get(Guid? testId)
{
string xml = string.Empty;
SqlXmlQuery query = new SqlXmlQuery(SqlXmlQueryType.SqlXmlStoredProc, "TestGet");
query.AddParameter("@TestId", testId);
DBManager.Execute(query, ref xml);
return xml;
}
/// <summary>
/// Executes a sql-xml query object returning the results as a string
/// </summary>
/// <param name="queryObject"></param>
/// <returns></returns>
public string ExecuteXml(SqlXmlQuery queryObject)
{
SqlCommand command = new SqlCommand();
SqlDataAdapter dataAdapter;
DataSet tempDataSet = new DataSet();
try
{
// Check the connection state
CheckConnection();
// Open the connection
mConnection.Open();
// Set the connection for the command object
command.Connection = mConnection;
// Set the timeout for the command object
command.CommandTimeout = queryObject.QueryTimeOut;
// Set the Command Type, if type is store procedure
if (queryObject.QueryType.Equals(SqlXmlQueryType.SqlXmlStoredProc))
{
command.CommandType = CommandType.StoredProcedure;
}
// Set the command text
command.CommandText = queryObject.QueryString.ToString();
// Set query parameters
SetParameters(queryObject, ref command);
// Execute the SQL
dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(tempDataSet);
// Get the xml from the dataset
StringBuilder returnXML = new StringBuilder("");
for (int x = 0; x <= tempDataSet.Tables.Count - 1; x++)
{
for (int i = 0; i <= tempDataSet.Tables[x].Rows.Count - 1; i++)
{
returnXML.Append(tempDataSet.Tables[x].Rows[i].ItemArray[0]);
}
}
// Return the xml as a string concatenated with the start tag and end tag properties
return queryObject.StartTag.ToString() + returnXML.ToString() + queryObject.EndTag.ToString();
}
catch (SqlException ex)
{
if (ex.State == 17)
{
throw new Exception(ex.Message);
}
else
{
throw ex;
}
}
finally
{
// Close DB Connection
mConnection.Close();
command = null;
dataAdapter = null;
tempDataSet = null;
}
}
/// <summary>
/// Executes a sql-xml query object returning the results as a string
/// </summary>
/// <param name="queryObject"></param>
/// <returns></returns>
public string ExecuteXml(SqlXmlQuery queryObject)
{
SqlCommand command = new SqlCommand();
SqlDataAdapter dataAdapter;
DataSet tempDataSet = new DataSet();
try
{
// Check the connection state
CheckConnection();
// Open the connection
mConnection.Open();
// Set the connection for the command object
command.Connection = mConnection;
// Set the timeout for the command object
command.CommandTimeout = queryObject.QueryTimeOut;
// Set the Command Type, if type is store procedure
if (queryObject.QueryType.Equals(SqlXmlQueryType.SqlXmlStoredProc))
{
command.CommandType = CommandType.StoredProcedure;
}
// Set the command text
command.CommandText = queryObject.QueryString.ToString();
// Set query parameters
SetParameters(queryObject, ref command);
// Execute the SQL
dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(tempDataSet);
// Get the xml from the dataset
StringBuilder returnXML = new StringBuilder("");
for (int x = 0; x <= tempDataSet.Tables.Count - 1; x++)
{
for (int i = 0; i <= tempDataSet.Tables[x].Rows.Count - 1; i++)
{
returnXML.Append(tempDataSet.Tables[x].Rows[i].ItemArray[0]);
}
}
// Return the xml as a string concatenated with the start tag and end tag properties
return(queryObject.StartTag.ToString() + returnXML.ToString() + queryObject.EndTag.ToString());
}
catch (SqlException ex)
{
if (ex.State == 17)
{
throw new Exception(ex.Message);
}
else
{
throw ex;
}
}
finally
{
// Close DB Connection
mConnection.Close();
command = null;
dataAdapter = null;
tempDataSet = null;
}
}
/// <summary>
/// Executes a sql xml query
/// </summary>
/// <param name="query"></param>
/// <param name="xmlString"></param>
public static void Execute(SqlXmlQuery query, ref string xmlString)
{
VerifySqlXmlQueryObject(query);
ISqlQuery sqlClass = new SQLServer(GetConnectionString());
xmlString = sqlClass.ExecuteXml(query);
}