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