/// <summary>Converts value of given internal data type to string that can be used in VALUES or WHERE part of SQL query</summary>
/// <param name="type">Internal data type</param>
/// <param name="value"></param>
/// <returns>Data converted to string format and enquoted if needed</returns>
public static string DataValueToString(DataType type, object value)
{
switch (type)
{
case DataType.Double:
return(((double)value).ToString());
case DataType.Ansi:
return("'" + (string)value + "'");
case DataType.Unicode:
return("'" + (string)value + "'");
case DataType.Datetime:
return("'" + SqlServerProbe.DateTimeToString((DateTime)value) + "'");
case DataType.SmallInt:
return(((short)value).ToString());
default:
throw new Exception("Unknown data type");
}
}
/// <summary>Executes non reader SQL statement. Creates tables on exception</summary>
public static void ExecuteSql(string sqlStatement, int targetId, MetricGroup metricGroup)
{
int tryCount = 0;
bool canExit = false;
SqlConnection reposConn = null;
SqlCommand cmd = null;
/*
* If tables don't exist, will try to create them and rerun SQL statements
*/
while (!canExit && tryCount < 2)
{
try
{
reposConn = new SqlConnection(Configuration.GetReposConnectionString("Probe"));
cmd = reposConn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sqlStatement;
reposConn.Open();
int rowCount = cmd.ExecuteNonQuery();
_logger.Debug("Rows affected: {0}", rowCount);
canExit = true;
reposConn.Close();
} // end of try
catch (SqlException e)
{
if (e.Number == 208) // Invalid object
{
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
{
return;
}
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
}
else
{
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
}
} // end of catch
finally
{
if (cmd != null)
{
((IDisposable)cmd).Dispose();
}
if (reposConn != null)
{
((IDisposable)reposConn).Dispose();
}
}
tryCount++;
} // end of while
} // end of ExecuteSql function