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