public SQLServer_Object GetByName(string Name)
{
SQLServer_Object Back = null;
try
{
Back = Tables.First(Table => Table.Name.Equals(Name, StringComparison.CurrentCultureIgnoreCase));
}
catch (Exception)
{
;
}
if (Back != null)
{
return(Back);
}
try
{
Back = Views.First(View => View.Name.Equals(Name, StringComparison.CurrentCultureIgnoreCase));
}
catch (Exception)
{
;
}
if (Back != null)
{
return(Back);
}
try
{
Back = Sps.First(View => View.Name.Equals(Name, StringComparison.CurrentCultureIgnoreCase));
}
catch (Exception)
{
;
}
return(Back);
}
public static int Compare(SQLServer_Object x, SQLServer_Object y)
{
return(String.Compare(x.Name, y.Name, true));
}
private void RefreshSPs()
{
//obtener lista de Sps de la BD actual
string sql_sp;
sql_sp = "select \n" +
" objs.id as DBId, \n"+
" objs.name as Name, \n"+
" isnull(cols.name, '') AS ParamName, \n"+
" type_name(cols.xusertype) AS ParamType, \n"+
" isnull(convert(int, length), 0) AS ParamLenght, \n"+
" isnull(isnullable, 1) as Nullable \n"+
"from \n" +
" sysobjects objs left outer join syscolumns cols on objs.id = cols.id \n"+
"where \n" +
" objs.xtype = 'P' and \n"+
" objs.category = 0 \n"+
"order by \n" +
" objs.name, cols.colid \n";
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql_sp, Conx);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
cmd.Connection.Close();
return;
}
cmd.Dispose();
if (Sps != null)
{
Sps.Clear();
}
else
{
Sps = new List <SQLServer_Object>();
}
SQLServer_Object CurObj = null;
string cursp = "", pname;
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!cursp.Equals(Info.Rows[i]["Name"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{//agregar solo el campo
CurObj = new SQLServer_Object(this, Convert.ToInt32(Info.Rows[i]["DBId"]), Info.Rows[i]["Name"].ToString(), "Procedimiento Almacenado", DataType.StoredProcedure);
cursp = CurObj.Name;
Sps.Add(CurObj);
}
pname = Info.Rows[i]["ParamName"].ToString();
if (!String.IsNullOrEmpty(pname))
{
SQLServer_Object Param;
Param = new SQLServer_Object(null, 0, pname, "Parametro de SP", DataType.Variable);
Param.LlaveForanea = false;
Param.LlavePrimaria = false;
Param.Tipo = Info.Rows[i]["ParamType"].ToString();
Param.Precision = Convert.ToInt32(Info.Rows[i]["ParamLenght"]);
Param.Nullable = Convert.ToInt32(Info.Rows[i]["Nullable"]) == 1;
Param.Calculado = false;
Param.Default = "";
Param.ReferenceID = 0;
Param.ReferenceTable = "";
Param.ReferenceField = "";
CurObj.Childs.Add(Param);
}
}
}
private void RefreshViews()
{
//obtener lista de vistas de la BD actual
string sql_views;
sql_views = "SELECT \n" +
" sobj.id as OwnerId, \n"+
" sobj.name as OwnerName, \n"+
" cols.name as Nombre, \n"+
" type_name(cols.xusertype) as Tipo, \n"+
" isnull(cols.prec, 0) as Longitud, \n"+
" isnull(cols.Scale, 0) as Escala, \n"+
" isnull(cols.isnullable, 1) as Nullable, \n"+
" isnull(cols.iscomputed, 0) as Calculated \n"+
"FROM \n" +
" sysobjects sobj INNER JOIN syscolumns cols ON sobj.id=cols.id \n"+
"WHERE \n" +
" sobj.xtype = 'V' \n"+
" and sobj.name <> 'sysdiagrams' \n"+
"order by \n" +
" sobj.name, cols.colid \n";
//obtener lista de vistas de la BD actual
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql_views, Conx);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
cmd.Connection.Close();
return;
}
cmd.Dispose();
if (Views != null)
{
Views.Clear();
}
else
{
Views = new List <SQLServer_Object>();
}
SQLServer_Object CurObj = null;
string curview = "";
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!curview.Equals(Info.Rows[i]["OwnerName"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{//agregar solo el campo
CurObj = new SQLServer_Object(this, Convert.ToInt32(Info.Rows[i]["OwnerId"]), Info.Rows[i]["OwnerName"].ToString(), "Vista", DataType.View);
curview = CurObj.Name;
Views.Add(CurObj);
}
SQLServer_Object Field;
Field = new SQLServer_Object(null, 0, Info.Rows[i]["Nombre"].ToString(), "Campo", DataType.Field);
Field.LlaveForanea = false;
Field.LlavePrimaria = false;
Field.Tipo = Info.Rows[i]["Tipo"].ToString();
Field.Precision = Convert.ToInt32(Info.Rows[i]["Longitud"]);
Field.Nullable = Convert.ToInt32(Info.Rows[i]["Nullable"]) == 1;
Field.Calculado = false;
Field.Default = "";
Field.ReferenceID = 0;
Field.ReferenceTable = "";
Field.ReferenceField = "";
CurObj.Childs.Add(Field);
}
}
private void RefreshTables()
{
string sql_tables;
sql_tables = "SELECT \n" +
" sobj.id as OwnerId, \n"+
" sobj.name as OwnerName, \n"+
" cols.name as Nombre, \n"+
" type_name(cols.xusertype) as Tipo, \n"+
" isnull(cols.prec, 0) as Longitud, \n"+
" isnull(cols.Scale, 0) as Escala, \n"+
" isnull(cols.isnullable, 1) as Nullable, \n"+
" isnull(cols.iscomputed, 0) as Calculated, \n"+
" isnull(comm.text, '') as DefaultValue, \n"+
" case when pk.xtype is null then '0' else '1' end as PKey, \n"+
" case when fk.fkey is null then '0' else '1' end as FKey, \n"+
" isnull(fk.rkeyid, 0) as ReferenceID, \n"+
" isnull(fk2.name, '') as ReferenceTable, \n"+
" isnull(cols2.name, '') as ReferenceField, \n"+
" isnull(indx.name, '') as IndexName, \n"+
" isnull(COLUMNPROPERTY(sobj.id,cols.name,'IsIdentity'), 0) IsIdentity, \n"+
" Seed = IDENT_SEED(sobj.name), \n"+
" Increment = IDENT_INCR (sobj.name) \n"+
"FROM \n" +
" sysobjects sobj INNER JOIN syscolumns cols ON sobj.id = cols.id \n"+
" LEFT JOIN sysforeignkeys fk ON fk.fkeyid = cols.id AND fk.fkey = cols.colid \n" +
" LEFT JOIN syscolumns cols2 ON cols2.id = fk.rkeyid AND cols2.colid = fk.rkey \n" +
" LEFT JOIN sysobjects fk2 ON fk.rkeyid = fk2.id \n" +
" LEFT JOIN syscomments comm ON cols.cdefault = comm.id OR (cols.id = comm.id and cols.colid = comm.number) \n"+
" LEFT JOIN sysindexkeys ik ON ik.id = cols.id AND ik.colid = cols.colid \n" +
" LEFT JOIN sysindexes indx ON indx.id = ik.id AND indx.indid = ik.indid \n"+
" LEFT JOIN sysobjects pk ON indx.name = pk.name AND pk.parent_obj = indx.id AND pk.xtype = 'PK' \n" +
"WHERE \n" +
" sobj.xtype = 'U' \n"+
" and sobj.name <> 'sysdiagrams' \n"+
"order by \n" +
" sobj.name, cols.colid \n";
//obtener lista de tablas de la BD actual
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql_tables, Conx);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
cmd.Connection.Close();
return;
}
cmd.Dispose();
if (Tables != null)
{
Tables.Clear();
}
else
{
Tables = new List <SQLServer_Object>();
}
SQLServer_Object CurObj = null;
string curtable = "";
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!curtable.Equals(Info.Rows[i]["OwnerName"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{//agregar solo el campo
CurObj = new SQLServer_Object(this, Convert.ToInt32(Info.Rows[i]["OwnerId"]), Info.Rows[i]["OwnerName"].ToString(), "Tabla", DataType.Table);
curtable = CurObj.Name;
Tables.Add(CurObj);
}
SQLServer_Object Field;
if (Convert.ToInt32(Info.Rows[i]["PKey"]) == 1)
{
Field = new SQLServer_Object(null, 0, Info.Rows[i]["Nombre"].ToString(), "Llave Primaria", DataType.KeyField);
}
else if (Convert.ToInt32(Info.Rows[i]["FKey"]) == 1)
{
Field = new SQLServer_Object(null, 0, Info.Rows[i]["Nombre"].ToString(), "Llave Foranea", DataType.ForeignKeyField);
}
else
{
Field = new SQLServer_Object(null, 0, Info.Rows[i]["Nombre"].ToString(), "Campo", DataType.Field);
}
Field.LlaveForanea = Convert.ToInt32(Info.Rows[i]["FKey"]) == 1;
Field.LlavePrimaria = Convert.ToInt32(Info.Rows[i]["PKey"]) == 1;
Field.Tipo = Info.Rows[i]["Tipo"].ToString();
Field.Precision = Convert.ToInt32(Info.Rows[i]["Longitud"]);
Field.Nullable = Convert.ToInt32(Info.Rows[i]["Nullable"]) == 1;
Field.Calculado = Convert.ToInt32(Info.Rows[i]["Calculated"]) == 1;
Field.Default = Info.Rows[i]["DefaultValue"].ToString();
Field.ReferenceID = Convert.ToInt32(Info.Rows[i]["ReferenceID"]);
Field.ReferenceTable = Info.Rows[i]["ReferenceTable"].ToString();
Field.ReferenceField = Info.Rows[i]["ReferenceField"].ToString();
if (Convert.ToInt32(Info.Rows[i]["IsIdentity"]) == 1)
{
Field.IsIdentity = true;
Field.Seed = Convert.ToInt32(Info.Rows[i]["Seed"]);
Field.Increment = Convert.ToInt32(Info.Rows[i]["Increment"]);
}
else
{
Field.IsIdentity = false;
Field.Seed = 0;
Field.Increment = 0;
}
CurObj.Childs.Add(Field);
}
}
