public List <int> tablasRelacionadas(int id)
{
try
{
List <int> data = new List <int>();
using (var context = new BarandillasEntities())
{
var query = (from i in context.RelacionesDeTablas
where i.TablaID == id
select i.TablaRelacionada).ToList();
if (query.Count > 0)
{
foreach (var i in query)
{
data.Add(i);
}
}
else
{
data.Add(0);
}
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <Tuple <int, string> > selectTables()
{
try
{
List <Tuple <int, string> > tablas = new List <Tuple <int, string> >();
using (var context = new BarandillasEntities())
{
var query = (from t in context.Tablas_BD
orderby t.TablaID
select new
{
ID = t.TablaID,
Nombretabla = t.Nombre
}).ToList();
foreach (var i in query)
{
tablas.Add(new Tuple <int, string>(i.ID, i.Nombretabla));
}
return(tablas);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <Tuple <int, string> > tablaMaster()
{
try
{
List <Tuple <int, string> > data = new List <Tuple <int, string> >();
using (var context = new BarandillasEntities())
{
var query = (from i in context.RelacionesDeTablas
select new
{
ID = i.TablaID,
NombreTabla = i.Nombre
}).Distinct().ToList();
foreach (var d in query)
{
data.Add(new Tuple <int, string> (d.ID, d.NombreTabla));
}
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <Tuple <int, string> > tablaDetail(int id)
{
try
{
List <Tuple <int, string> > data = new List <Tuple <int, string> >();
using (var context = new BarandillasEntities())
{
var query = (from t1 in context.Tablas_BD
join t2 in context.RelacionesTablas_BD on t1.TablaID equals t2.TablaRelacionada
where t2.TablaBaseID == id
select new
{
NombreTabla = t1.Nombre,
ID = t2.RelacionID
});
foreach (var d in query)
{
data.Add(new Tuple <int, string>(d.ID, d.NombreTabla));
}
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Entidades.Helpers.Paginado SelectAll(int skip, int take)
{
try
{
var data = new Entidades.Helpers.Paginado();
List <Entidades.Helpers.Tablas> t = new List <Entidades.Helpers.Tablas>();
using (var context = new BarandillasEntities())
{
var query = (from p in context.Tablas_BD where p.Estatus == true
orderby p.TablaID
select p)
.Skip(skip)
.Take(take)
.ToList();
foreach (var i in query)
{
t.Add(new Entidades.Helpers.Tablas {
TablaID = i.TablaID, NombreTabla = i.Nombre, Descripcion = i.Descripcion, TipoTabla = i.TipoTabla, Estatus = i.Estatus
});
}
data.Customers = t;
data.TotalRecords = (from p in context.Tablas_BD
select p).Count();
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int Insert(RelacionesTablas_BD tablas)
{
try
{
using (var context = new BarandillasEntities())
{
context.RelacionesTablas_BD.Add(tablas);
context.SaveChanges();
return(tablas.RelacionID);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string Update(Tablas_BD tablas)
{
try
{
using (var context = new BarandillasEntities())
{
context.Entry(tablas).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
return(tablas.Nombre);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string Insert(Tablas_BD tablas)
{
try
{
using (var context = new BarandillasEntities())
{
context.Tablas_BD.Add(tablas);
context.SaveChanges();
return(tablas.Nombre);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string Delete(int id)
{
try
{
using (var context = new BarandillasEntities())
{
Tablas_BD tabla = context.Tablas_BD.Find(id);
tabla.Estatus = false;
context.Entry(tabla).State = System.Data.Entity.EntityState.Modified;
context.SaveChanges();
return(tabla.Nombre);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public List <string> selectColumn(string tableName)
{
try
{
DataTable dt = new DataTable();
string SqlString = "SELECT Column_Name from INFORMATION_SCHEMA.COLUMNS where table_name = @tableName";
List <string> data = new List <string>();
using (var context = new BarandillasEntities())
{
String con = System.Configuration.ConfigurationManager.ConnectionStrings["Prueba"].ConnectionString;
using (SqlConnection conn = new SqlConnection(con))
{
using (SqlCommand cmd = new SqlCommand(SqlString, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("tableName", tableName);
SqlDataAdapter sda = new SqlDataAdapter();
try
{
conn.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
data = dt.AsEnumerable().Select(n => n.Field <string>(0)).ToList();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
conn.Close();
cmd.Dispose();
sda.Dispose();
}
}
}
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public void DeleteCampo(string id)
{
try
{
using (var context = new BarandillasEntities())
{
var query = (from i in context.RelacionCamposTablas_BD
where i.CampoTB == id
select i).ToList();
foreach (var d in query)
{
context.RelacionCamposTablas_BD.Remove(d);
}
context.SaveChanges();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public Entidades.Helpers.PaginadoRelacion selectAll(int skip, int take)
{
try
{
var data = new Entidades.Helpers.PaginadoRelacion();
List <Relaciones> t = new List <Relaciones>();
using (var context = new BarandillasEntities())
{
var query = (from p in context.RelacionesDeTablas
group p by new { p.TablaID, p.Nombre } into i
select new
{
TablaID = i.Key.TablaID,
NombreTabla = i.Key.Nombre,
Count = i.Count()
})
.OrderBy(i => i.TablaID)
.Skip(skip)
.Take(take)
.ToList().Distinct();
foreach (var i in query)
{
t.Add(new Relaciones {
TablaID = i.TablaID, NombreTabla = i.NombreTabla, Count = i.Count
});
}
data.Customers = t;
data.TotalRecords = t.Count();
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string Delete(int id)
{
try
{
string data = null;
using (var context = new BarandillasEntities())
{
var query = (from i in context.RelacionesTablas_BD
where i.TablaBaseID == id
select i).ToList();
data = query[0].Tablas_BD.Nombre;
foreach (var d in query)
{
context.RelacionesTablas_BD.Remove(d);
}
context.SaveChanges();
return(data);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}