public ICollection <UsageObject> GetUsages(DateTime from)
{
var result = new List <UsageObject>();
using (var connection = _connection.GetConnection()) {
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "select * from sys.resource_stats where start_time > @from";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("from", from));
cmd.CommandTimeout = (int)TimeSpan.FromSeconds(5).TotalSeconds;
try {
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.AddRange(GetResultFromReader(reader));
}
}
}
catch (SqlException e) {
//swallow the exception, it might be we still got results..
//this is a fixed for a problem where sql azure throws an error "Unable to retrieve Azure SQL Database telemetry data" (error code 25745), but still returns results
_logger.Log(LogLevel.Error, "Reading resource stats failed", e);
}
}
return(result);
}
public ICollection <UsageObject> GetUsages(DateTime from)
{
var result = new List <UsageObject>();
using (var connection = _connection.GetConnection()) {
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = "select * from sys.resource_stats where start_time > @from";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("from", from));
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result.AddRange(GetResultFromReader(reader));
}
}
}
return(result);
}
private ICollection <UsageObject> RunQuery()
{
var result = new List <UsageObject>();
using (var connection = _connection.GetConnection()) {
var cmd = connection.CreateCommand();
cmd.CommandText = "select * from sys.dm_db_resource_stats order by end_time asc";
connection.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
var time = DateTime.SpecifyKind((DateTime)reader["end_time"], DateTimeKind.Utc);
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
if (NotCounterColumns.Contains(name))
{
continue;
}
if (reader.IsDBNull(i))
{
/* New column "avg_login_rate_percent" in sys.dm_db_resource_stats currently has NULL as
* value, so just skip if that happens.
*/
continue;
}
var value = Convert.ToDecimal(reader[i]);
result.Add(new UsageObject {
Timestamp = time.ToString("o"),
Value = (double)value,
GraphiteCounterName = new GraphiteCounterName("Azure.SQLRealTime", _connection.Servername, _connection.Database, name).ToString()
});
}
}
}
return(result);
}
}
public static ICollection<string> ListUserDatabases(string servername,string username,string password)
{
var connection = new SQLDatabaseConnection(servername,username,password,"master");
var res = new List<string>();
using(var conn = connection.GetConnection()) {
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = " select * from sys.databases";
using(var reader = cmd.ExecuteReader()) {
while(reader.Read()) {
var name = (string)reader["name"];
if(name=="master") {
continue;
}
res.Add(name);
}
return res;
}
}
}
public static ICollection <string> ListUserDatabases(string servername, string username, string password)
{
var connection = new SQLDatabaseConnection(servername, username, password, "master");
var res = new List <string>();
using (var conn = connection.GetConnection()) {
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = " select * from sys.databases";
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
var name = (string)reader["name"];
if (name == "master")
{
continue;
}
res.Add(name);
}
return(res);
}
}
}