/// <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); }