/// <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