private IEnumerable <EstructuraTablas> GetEstructuraDb(string conexStrg)
{
var lista = new List <EstructuraTablas>();
const string sqlString = "SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, " +
" INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, INFORMATION_SCHEMA.TABLES.TABLE_TYPE " +
" FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES ON " +
" INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME ORDER BY INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, " +
" INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME";
var myConnection = new SqlConnection(conexStrg);
myConnection.Open();
var myCommand = new SqlCommand(sqlString, myConnection);
var myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var estructuraModel = new EstructuraTablas
{
ColumnName = myReader.GetString(myReader.GetOrdinal("COLUMN_NAME")),
TableName = myReader.GetString(myReader.GetOrdinal("TABLE_NAME")),
TableType = myReader.GetString(myReader.GetOrdinal("TABLE_TYPE")),
IsNullable = myReader.GetString(myReader.GetOrdinal("IS_NULLABLE")) == "YES",
DataType = myReader.GetString(myReader.GetOrdinal("DATA_TYPE")),
};
lista.Add(estructuraModel);
}
myConnection.Close();
return(lista);
}
private IEnumerable <EstructuraTablas> GetFuncionDb(string conexStrg)
{
var lista = new List <EstructuraTablas>();
const string sqlString =
"SELECT name, xtype FROM sysobjects WHERE " +
" (xtype <> 'U') AND (xtype <> 'V') AND " +
" (xtype <> 'TR')AND (xtype <> 'D') " +
" ORDER BY name";
var myConnection = new SqlConnection(conexStrg);
myConnection.Open();
var myCommand = new SqlCommand(sqlString, myConnection);
var myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var tipo = myReader.GetString(myReader.GetOrdinal("xtype")).Trim();
if (tipo == "TR")
{
tipo = "TRIGGER";
}
if (tipo == "F")
{
tipo = "REL.FORANEA";
}
if (tipo == "PK")
{
tipo = "REL.PRIMARIA";
}
if (tipo == "P")
{
tipo = "PROC.ALMACENADO";
}
if (tipo == "IF" || tipo == "TF")
{
tipo = "FUNC.TABLA";
}
if (tipo == "FN")
{
tipo = "FUNC.ESCALAR";
}
var estructuraModel = new EstructuraTablas
{
ColumnName = myReader.GetString(myReader.GetOrdinal("name")),
TableType = tipo,
};
lista.Add(estructuraModel);
}
const string sqlString2 =
"SELECT sys.sysobjects.name AS name, OBJECT_NAME(sys.sysobjects.parent_obj) " +
" AS table_name FROM sys.sysobjects INNER JOIN sys.tables AS t " +
" ON sys.sysobjects.parent_obj = t.object_id WHERE (sys.sysobjects.type = 'TR')";
myReader.Close();
var myCommand2 = new SqlCommand(sqlString2, myConnection);
var myReader2 = myCommand2.ExecuteReader();
while (myReader2.Read())
{
var estructuraModel = new EstructuraTablas
{
ColumnName = myReader2.GetString(myReader2.GetOrdinal("name")),
TableType = "TRIGGER",
TableName = myReader2.GetString(myReader2.GetOrdinal("table_name")),
};
lista.Add(estructuraModel);
}
myConnection.Close();
return(lista);
}
