private List<Field> GetReturnColumnsFromProcedure(Procedure proc)
{
List<Field> Back = new List<Field>();
if (proc == null || String.IsNullOrEmpty(proc.Schema) || String.IsNullOrEmpty(proc.Name))
{
return Back;
}
using (SqlCommand command = new SqlCommand(String.Format("{0}.{1}", proc.Schema, proc.Name), Executor.Connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
try
{
command.Connection.Open();
SqlCommandBuilder.DeriveParameters(command);
SqlDataAdapter Daa = new SqlDataAdapter();
Daa.SelectCommand = command;
DataSet ds = new DataSet("buff");
Daa.GetFillParameters();
Daa.FillSchema(ds, SchemaType.Source, "buff");
foreach (DataTable tab in ds.Tables)
{
foreach (DataColumn col in tab.Columns)
{
if (SqlVsCSharp.ContainsKey(col.DataType.ToString()))
{
Field aux = new Field()
{
Name = col.Caption,
Type = col.DataType.ToString(),
CSharpType = SqlVsCSharp[col.DataType.ToString()]
};
if (!Back.Exists(x => x.Name.Equals(col.Caption, StringComparison.CurrentCultureIgnoreCase)))
{
Back.Add(aux);
}
}
}
}
}
catch (Exception ex)
{
command.Connection.Close();
MessageBox.Show(ex.Message, "Exception thrown when trying to pull the return data of the Stored Procedure", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
command.Connection.Close();
}
return Back;
}
public int LoadTables(bool FullLoad = false)
{
string sql;
List<Table> Tables = new List<Table>();
sql = @"
SELECT DISTINCT
ISNULL(sch.name, 'dbo') as 'Schema',
sobj.id as TableId,
sobj.name as TableName,
cols.colid as ColumnId,
cols.name as ColumnName,
type_name(cols.xusertype) as ColumnType,
isnull(cols.prec, 0) as Precision,
isnull(cols.Scale, 0) as Scale,
isnull(cols.isnullable, 1) as Nullable,
isnull(cols.iscomputed, 0) as Calculated,
isnull(comm.text, '') as DefaultValue,
case when pk.xtype is null then '0' else '1' end as PKey,
case when fk.fkey is null then '0' else '1' end as FKey,
isnull(fk.rkeyid, 0) as ReferenceID,
isnull(fk2.name, '') as ReferenceTable,
isnull(cols2.name, '') as ReferenceFieldName,
isnull(cols2.colid, 0) as ReferenceFieldId,
'' as IndexName,--isnull(indx.name, '') as IndexName,
isnull(COLUMNPROPERTY(sobj.id,cols.name,'IsIdentity'), 0) IsIdentity,
IDENT_SEED(sch.name + '.' + sobj.name) as Seed,
IDENT_INCR(sch.name + '.' + sobj.name) as Increment
FROM
sysobjects sobj INNER JOIN syscolumns cols ON sobj.id = cols.id
LEFT JOIN sysforeignkeys fk ON fk.fkeyid = cols.id AND fk.fkey = cols.colid
LEFT JOIN syscolumns cols2 ON cols2.id = fk.rkeyid AND cols2.colid = fk.rkey
LEFT JOIN sysobjects fk2 ON fk.rkeyid = fk2.id
LEFT JOIN syscomments comm ON cols.cdefault = comm.id OR (cols.id = comm.id and cols.colid = comm.number)
LEFT JOIN sysindexkeys ik ON ik.id = cols.id AND ik.colid = cols.colid
LEFT JOIN sysindexes indx ON indx.id = ik.id AND indx.indid = ik.indid
LEFT JOIN sysobjects pk ON indx.name = pk.name AND pk.parent_obj = indx.id AND pk.xtype = 'PK'
LEFT JOIN Sys.Objects ObjAux ON sobj.id = ObjAux.object_id
LEFT JOIN Sys.Schemas sch ON ObjAux.schema_id = sch.schema_id
WHERE
sobj.xtype = 'U'
and sobj.name <> 'sysdiagrams'
--and sobj.id = 709577566
order by
ISNULL(sch.name, 'dbo'), sobj.name, cols.colid";
//DbObjects.RemoveAll(X => X.Kind == ObjectType.Table);
//Get the schema for the tables
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql, Connection);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception)
{
cmd.Connection.Close();
return 0;
}
cmd.Dispose();
if (Tables != null)
Tables.Clear();
else
Tables = new List<Table>();
Table CurObj = null;
string curtable = "";
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!curtable.Equals(Info.Rows[i]["TableName"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{
CurObj = new Table()
{
Name = Info.Rows[i]["TableName"].ToString(),
Comment = "",
Id = Convert.ToInt32(Info.Rows[i]["TableId"]),
Schema = Info.Rows[i]["Schema"].ToString()
};
curtable = CurObj.Name;
Tables.Add(CurObj);
}
ISqlChild Exists;
Exists = CurObj.Childs.Where(X => X.Id == Convert.ToInt32(Info.Rows[i]["ColumnId"])).FirstOrDefault();
if (Exists != null && Exists.Id > 0)
{
if (!Exists.IsPrimaryKey)
Exists.IsPrimaryKey = Convert.ToInt32(Info.Rows[i]["PKey"]) == 1;
}
else
{
Field Field = new Field()
{
Comment = "",
Computed = Convert.ToInt32(Info.Rows[i]["Calculated"]) == 1,
DefaultValue = Info.Rows[i]["DefaultValue"].ToString(),
Id = Convert.ToInt32(Info.Rows[i]["ColumnId"]),
ForeignKey = Convert.ToInt32(Info.Rows[i]["ReferenceFieldId"]),
IsForeignKey = Convert.ToInt32(Info.Rows[i]["ReferenceFieldId"]) > 0,
IsIdentity = Convert.ToInt32(Info.Rows[i]["IsIdentity"]) == 1,
IsPrimaryKey = Convert.ToInt32(Info.Rows[i]["PKey"]) == 1,
Name = Info.Rows[i]["ColumnName"].ToString(),
Nullable = Convert.ToInt32(Info.Rows[i]["Nullable"]) == 1,
Parent = CurObj,
Precision = Convert.ToInt32(Info.Rows[i]["Precision"]),
Type = Info.Rows[i]["ColumnType"].ToString()
};
if (Field.IsForeignKey)
{
Field.ReferenceChildName = Info.Rows[i]["ReferenceFieldName"].ToString();
Field.ReferenceParentName = Info.Rows[i]["ReferenceTable"].ToString();
}
if (Field.IsIdentity)
{
Field.Increment = Convert.ToInt32(Info.Rows[i]["Increment"]);
Field.Seed = Convert.ToInt32(Info.Rows[i]["Seed"]);
}
else
{
Field.Increment = 0;
Field.Seed = 0;
}
CurObj.Childs.Add(Field);
}
}
if (FullLoad)
{
foreach (ISqlObject Table in Tables)
{
Table.LoadScript(null);
}
}
DbObjects.AddRange(Tables);
return Tables.Count;
}
public int LoadViews(bool FullLoad = false)
{
List<View> Views = new List<View>();
string sql;
sql = @"
SELECT
ISNULL(sch.name, 'dbo') as 'Schema',
sobj.id as ViewId,
sobj.name as ViewName,
cols.name as FieldName,
type_name(cols.xusertype) as Type,
isnull(cols.prec, 0) as Length,
isnull(cols.Scale, 0) as Scale,
isnull(cols.isnullable, 1) as Nullable,
isnull(cols.iscomputed, 0) as Calculated
FROM
sysobjects sobj INNER JOIN syscolumns cols ON sobj.id=cols.id
LEFT JOIN Sys.Objects ObjAux ON sobj.id = ObjAux.object_id
LEFT JOIN Sys.Schemas sch ON ObjAux.schema_id = sch.schema_id
WHERE
sobj.xtype = 'V'
ORDER BY
sobj.id, cols.colid";
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql, Connection);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception)
{
cmd.Connection.Close();
return 0;
}
cmd.Dispose();
//DbObjects.RemoveAll(X => X.Kind == ObjectType.View);
View CurObj = null;
string curview = "";
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!curview.Equals(Info.Rows[i]["ViewName"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{//agregar solo el campo
CurObj = new View()
{
Name = Info.Rows[i]["ViewName"].ToString(),
Id = Convert.ToInt32(Info.Rows[i]["ViewId"]),
Schema = Info.Rows[i]["Schema"].ToString(),
Comment = ""
};
curview = CurObj.Name;
Views.Add(CurObj);
}
Field Field = new Field()
{
Comment = "",
Computed = false,
DefaultValue = "",
ForeignKey = 0,
Id = 0,
IdentityScript = "",
Increment = 0,
IsForeignKey = false,
IsIdentity = false,
IsPrimaryKey = false,
Name = Info.Rows[i]["FieldName"].ToString(),
Nullable = true,
Parent = CurObj,
Precision = Convert.ToInt32(Info.Rows[i]["Length"]),
ReferenceChild = null,
ReferenceChildName = "",
ReferenceParent = null,
ReferenceParentName = "",
Seed = 0,
Type = Info.Rows[i]["Type"].ToString()
};
CurObj.Childs.Add(Field);
}
if (FullLoad)
{
cmd.Connection.Open();
try
{
foreach (ISqlObject View in Views)
{
View.LoadScript(cmd);
}
}
catch (Exception)
{
;
}
finally
{
cmd.Connection.Close();
}
}
DbObjects.AddRange(Views);
return Views.Count;
}
public int LoadTableFunctions(bool FullLoad = false)
{
List<TableFunction> Functions = new List<TableFunction>();
string sql = @"
SELECT
ISNULL(sch.name, 'dbo') as 'Schema',
sobj.id as FunctionId,
sobj.name as FunctionName,
cols.name as ParamName,
type_name(cols.xusertype) as Type,
isnull(cols.prec, 0) as Length,
isnull(cols.Scale, 0) as Scale,
isnull(cols.isnullable, 1) as Nullable,
isnull(cols.iscomputed, 0) as Calculated
FROM
sysobjects sobj LEFT OUTER JOIN syscolumns cols ON sobj.id=cols.id
LEFT JOIN Sys.Objects ObjAux ON sobj.id = ObjAux.object_id
LEFT JOIN Sys.Schemas sch ON ObjAux.schema_id = sch.schema_id
WHERE
sobj.xtype = 'TF'
ORDER BY
sobj.id, cols.colid";
DataTable Info = new DataTable();
SqlCommand cmd = new SqlCommand(sql, Connection);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.SelectCommand.Connection.Open();
da.Fill(Info);
da.SelectCommand.Connection.Close();
}
catch (Exception)
{
cmd.Connection.Close();
return 0;
}
cmd.Dispose();
//DbObjects.RemoveAll(X => X.Kind == ObjectType.ScalarFunction);
TableFunction CurObj = null;
string curfunc = "";
int i, rowcount = Info.Rows.Count;
for (i = 0; i < rowcount; i++)
{
if (!curfunc.Equals(Info.Rows[i]["FunctionName"].ToString(), StringComparison.CurrentCultureIgnoreCase))
{//agregar solo el campo
CurObj = new TableFunction()
{
Name = Info.Rows[i]["FunctionName"].ToString(),
Id = Convert.ToInt32(Info.Rows[i]["FunctionId"]),
Schema = Info.Rows[i]["Schema"].ToString(),
Comment = ""
};
curfunc = CurObj.Name;
Functions.Add(CurObj);
}
if (Info.Rows[i]["ParamName"] == DBNull.Value || String.IsNullOrEmpty(Info.Rows[i]["ParamName"].ToString()))
continue;
string name = Info.Rows[i]["ParamName"].ToString();
if (name.StartsWith("@"))
{
Parameter Param = new Parameter()
{
Comment = "",
Computed = false,
DefaultValue = "",
ForeignKey = 0,
Id = 0,
IdentityScript = "",
Increment = 0,
IsForeignKey = false,
IsIdentity = false,
IsPrimaryKey = false,
Name = Info.Rows[i]["ParamName"].ToString(),
Nullable = true,
Parent = CurObj,
Precision = Convert.ToInt32(Info.Rows[i]["Length"]),
ReferenceChild = null,
ReferenceChildName = "",
ReferenceParent = null,
ReferenceParentName = "",
Seed = 0,
Type = Info.Rows[i]["Type"].ToString()
};
CurObj.Childs.Add(Param);
}
else
{
Field Field = new Field()
{
Comment = "",
Computed = false,
DefaultValue = "",
ForeignKey = 0,
Id = 0,
IdentityScript = "",
Increment = 0,
IsForeignKey = false,
IsIdentity = false,
IsPrimaryKey = false,
Name = Info.Rows[i]["ParamName"].ToString(),
Nullable = true,
Parent = CurObj,
Precision = Convert.ToInt32(Info.Rows[i]["Length"]),
ReferenceChild = null,
ReferenceChildName = "",
ReferenceParent = null,
ReferenceParentName = "",
Seed = 0,
Type = Info.Rows[i]["Type"].ToString()
};
CurObj.Childs.Add(Field);
}
}
if (FullLoad)
{
cmd.Connection.Open();
try
{
foreach (ISqlObject Funct in Functions)
{
Funct.LoadScript(cmd);
}
}
catch (Exception)
{
;
}
finally
{
cmd.Connection.Close();
}
}
DbObjects.AddRange(Functions);
return Functions.Count;
}
