/// <summary>
/// Gets a list of databases on the target server
/// </summary>
/// <param name="connData"></param>
/// <returns></returns>
public static DatabaseList GetDatabaseList(ConnectionData connData, out bool hasError)
{
hasError = false;
string dbName;
DatabaseList dbList = new DatabaseList();
//Add any manually entered databases
StringCollection manualDBs = SqlSync.DbInformation.Properties.Settings.Default.ManuallyEnteredDatabases;
for (int i = 0; i < manualDBs.Count; i++)
{
dbList.Add(manualDBs[i], true);
}
SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection("master", connData.SQLServerName, connData.UserId, connData.Password, connData.AuthenticationType, 5);
SqlCommand cmd = new SqlCommand("select distinct [name] from dbo.sysdatabases ORDER BY [name]", conn);
try
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
dbName = reader[0].ToString();
if (!manualDBs.Contains(dbName))
{
dbList.Add(dbName, false);
}
else
{
for (int i = 0; i < dbList.Count; i++)
{
if (dbList[i].DatabaseName == dbName)
{
dbList[i].IsManuallyEntered = false;
break;
}
}
}
}
reader.Close();
}
}
catch (Exception exe)
{
log.LogError(exe, "Error getting database list");
hasError = true;
}
dbList.Sort(new DatabaseListComparer());
return(dbList);
}
public static ServerSizeSummary GetServerDatabaseInfo(ConnectionData connData)
{
Regex nums = new Regex(@"\d{1,9}");
ServerSizeSummary data = new ServerSizeSummary();
string location;
Int64 dbSize;
connData.DatabaseName = "master";
DatabaseList dbList = GetDatabaseList(connData);
SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(connData);
string locationQuery = "select filename from {0}.dbo.sysfiles where filename like '%.mdf'";
string sizeQuery = "{0}.dbo.sp_spaceused";
SqlCommand cmdLoc = new SqlCommand();
cmdLoc.Connection = conn;
SqlCommand cmdSize = new SqlCommand();
cmdSize.Connection = conn;
string dbName = string.Empty;
for (int i = 0; i < dbList.Count; i++)
{
try
{
conn.Open();
if (!dbList[i].IsManuallyEntered)
{
dbName = dbList[i].DatabaseName;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmdLoc.CommandText = String.Format(locationQuery, dbName);
cmdSize.CommandText = string.Format(sizeQuery, dbName);
location = (string)cmdLoc.ExecuteScalar();
using (SqlDataReader reader = cmdSize.ExecuteReader())
{
reader.Read();
dbSize = Int64.Parse(nums.Match(reader[1].ToString()).ToString());
}
data.AddServerSizeSummaryRow(dbName, location, dbSize, DateTime.MinValue);
}
}
catch
{
data.AddServerSizeSummaryRow(dbName, string.Empty, 0, DateTime.MinValue);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
data.AcceptChanges();
AddDatabaseCreateDate(ref data, connData);
data.AcceptChanges();
return(data);
}