public override List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null)
{
if (includeDetails)
{
try
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("databasesizes.sql"), linkedInfo);
using (var reader = cmd.ExecuteReader())
{
var res = new List <DatabaseOverviewInfo>();
while (reader.Read())
{
var item = new DatabaseOverviewInfo();
item.Name = reader["DatabaseName"].SafeToString();
item.RowSizeKB = long.Parse(reader["RowSizeKB"].SafeToString());
item.LogSizeKB = long.Parse(reader["LogSizeKB"].SafeToString());
item.Collation = reader["Collation"].SafeToString();
item.RecoveryModel = reader["RecoveryModel"].SafeToString();
bool isSnapshot = reader["SnapshotIsolation"].SafeToString() == "1";
bool isReadCommitedSnapshot = reader["IsReadCommitedSnapshot"].SafeToString()?.ToLower() == "true";
if (isSnapshot)
{
if (isReadCommitedSnapshot)
{
item.Concurrency = "High";
}
else
{
item.Concurrency = "Middle";
}
}
else
{
item.Concurrency = "Low";
}
res.Add(item);
}
return(res);
}
}
}
catch (Exception err)
{
// use variant without details
ServiceProvider.LogError <SqlServerInterface>(err, "Error fetching database details");
}
}
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer("SELECT name FROM [SERVER].sys.databases order by name", linkedInfo);
using (var reader = cmd.ExecuteReader())
{
var res = new List <DatabaseOverviewInfo>();
while (reader.Read())
{
var item = new DatabaseOverviewInfo();
item.Name = reader["name"].SafeToString();
res.Add(item);
}
return(res);
}
}
}
public SqlServerDialect(SqlServerDatabaseFactory factory)
: base(factory)
{
}
protected override void DoGetModifications()
{
var existingObjects = new HashSet <string>();
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("modifications.sql"), LinkedServerName, DatabaseName);
using (var reader = cmd.ExecuteReader())
{
int modifyIndex = reader.GetOrdinal("modify_date");
while (reader.Read())
{
string id = reader.SafeString("object_id");
DateTime modify = reader.GetDateTime(modifyIndex);
string stype = reader.SafeString("type");
string name = reader.SafeString("name");
string schema = reader.SafeString("schema");
existingObjects.Add(id);
DatabaseObjectType type;
switch (stype.Trim())
{
case "U":
type = DatabaseObjectType.Table;
break;
case "V":
type = DatabaseObjectType.View;
break;
case "P":
type = DatabaseObjectType.StoredProcedure;
break;
case "IF":
case "FN":
case "TF":
type = DatabaseObjectType.Function;
break;
case "TR":
type = DatabaseObjectType.Trigger;
break;
default:
continue;
}
var obj = Structure.FindObjectById(id);
if (obj == null)
{
var item = new DatabaseChangeItem
{
Action = DatabaseChangeAction.Add,
ObjectId = id,
ObjectType = type,
NewName = new NameWithSchema(schema, name),
};
ChangeSet.Items.Add(item);
}
else
{
if (obj.ModifyDate == null || Math.Abs((obj.ModifyDate.Value - modify).TotalSeconds) >= 1)
{
var item = new DatabaseChangeItem
{
Action = DatabaseChangeAction.Change,
ObjectId = id,
ObjectType = type,
OldName = ((NamedObjectInfo)obj).FullName,
NewName = new NameWithSchema(schema, name),
};
ChangeSet.Items.Add(item);
}
}
}
}
}
AddDeletedObjectsById(Structure.Tables, existingObjects);
AddDeletedObjectsById(Structure.Views, existingObjects);
AddDeletedObjectsById(Structure.StoredProcedures, existingObjects);
AddDeletedObjectsById(Structure.Functions, existingObjects);
}
