public ActionResult Index(LinkedServer id)
{
if (id == null)
{
ViewBag.Message = "Error: Must provide a Linked Server and Database Owner";
return RedirectToAction("Index", "Home", new {Message = "Must Select a Linked Server to continue!"});
}
else
{
var includeEmptyTables = id.IncludeEmptyTables ?? false;
var linkedServerName = id.Name as string ?? "SIS";
ViewBag.Message = linkedServerName + " Database Owners";
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString))
{
conn.Open();
var loginId = User.Identity.Name;
var bannerUserList = conn.Query(string.Format("SELECT LoginId FROM BannerLoginIds WHERE LoginId = '{0}'", loginId)).ToList();
var includeBannerItems = bannerUserList.Count() == 1 ? true : false;
var databaseOwners = conn.Query<DatabaseOwner>(
@"EXEC usp_GetLinkedServerDatabaseOwnersAndTableCount @LinkedServerNames = @linkedServerName, @IncludeEmptyTables = @includeEmptyTables, @IncludeBannerItems = @includeBannerItems",
new
{
@linkedServerName = linkedServerName,
@includeEmptyTables = includeEmptyTables,
@includeBannerItems = includeBannerItems
}).ToList();
return View(databaseOwners);
}
}
}
//public ActionResult Index()
//{
// ViewBag.Message = "Banner Tables";
// using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString))
// {
// conn.Open();
// IEnumerable<Table> resultList = conn.Query<Table>(@"EXEC usp_GetBannerTableNamesAndComments");
// return View(resultList);
// }
//}
// id = LinkedServer
public ActionResult Index(DatabaseOwner oid)
{
if (oid == null)
{
ViewBag.Message = "Error: Must provide a Linked Server and Database Owner";
return RedirectToAction("Index", "Home", new {Message = "Must Select a Linked Server to continue!"});
}
else
{
var id = new LinkedServer();
var includeEmptyTables = oid.IncludeEmptyTables ?? false;
var linkedServerName = oid.LinkedServerName as string ?? "SIS";
//var databaseOwners = oid.Owner //as List<DatabaseOwner>;
var databaseOwnerName = oid.Owner ?? "SATURN";
//if (databaseOwners == null || !databaseOwners.Any())
//{
// databaseOwners = new List<DatabaseOwner>
// {
// new DatabaseOwner {LinkedServerName = linkedServerName, Owner = databaseOwnerName}
// };
//}
//else
//{
// databaseOwners = id.DatabaseOwners as List<DatabaseOwner>;
// var first = databaseOwners.FirstOrDefault();
// if (first != null) databaseOwnerName = first.Owner;
//}
var message = " Tables";
//if (databaseOwners.First().Owner.Equals("SATURN"))
if (databaseOwnerName.Equals("SATURN"))
{
message = "Banner" + message;
}
else
{
message = linkedServerName +'.' + databaseOwnerName + message;
}
ViewBag.Message = message;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDb"].ConnectionString))
{
conn.Open();
var loginId = User.Identity.Name;
var bannerUserList = conn.Query(string.Format("SELECT LoginId FROM BannerLoginIds WHERE LoginId = '{0}'", loginId)).ToList();
var includeBannerItems = bannerUserList.Count() == 1 ? true : false;
IList<Table> tables =
conn.Query<Table>(@"EXEC usp_GetLinkedServerTableNamesAndComments
@Owners = @databaseOwnerName,
@LinkedServerNames = @linkedServerName,
@IncludeEmptyTables = @includeEmptyTables,
@IncludeBannerItems = @includeBannerItems",
new { @databaseOwnerName = databaseOwnerName, @linkedServerName = linkedServerName, @includeEmptyTables = includeEmptyTables, @IncludeBannerItems = includeBannerItems}).ToList();
if (tables.Count == 1)
{
// means we got back a single matching table => jump to table details page:
var table = tables.FirstOrDefault();
return RedirectToAction("Details", "Tables", new { table.LinkedServerName, table.Owner, table.TableName, IncludeEmptyTables=includeEmptyTables});
}
return View(tables);
}
}
}
