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