public ActionResult Connection(Connections model) { if (model.SelectedDB != null) model.Tables = repo.GetTablesForDatabaseX(model); if (model.SelectedTable != null) { repo.ConnectionDemo(model); model.SelectedTable = null; } return View(model); }
internal List<string> GetTablesForDatabaseX(Connections model) { string query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" + $" WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{model.SelectedDBName}'"; List<string> tables = new List<string>(); using (SqlConnection cn = new SqlConnection(model.SelectedDB)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = query; cmd.Connection = cn; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { tables.Add(dr["table_name"].ToString()); } } } return tables; }
internal void ConnectionDemo(Connections model) { string query = $"SELECT TOP 5 * FROM [{model.SelectedTable}]"; using (SqlConnection cn = new SqlConnection(model.SelectedDB)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = query; cmd.Connection = cn; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { int j = 0; while (dr.Read()) { string key; if (model.Values.ContainsKey(dr[0].ToString())) key = dr[0] + $"{j}"; else key = dr[0].ToString(); model.Values.Add(key, new List<string>()); j++; for (int i = 1; i < 4; i++) { if (i < dr.FieldCount) model.Values[key].Add(dr[i].ToString()); else model.Values[key].Add("-"); } } } } }
public ActionResult Connection() { Connections model = new Connections(); return View(model); }