SQL Schema
internal class SqlSchema
{
private string SqlForGetStoreProcedures = "select distinct(name) from sysobjects where type='p' ";
public string SqlForGetViews = "select distinct(name) from sysobjects where type='v' ";
private Database.DatabaseType type;
public bool GetSchema(Database database)
{
bool flag;
using (new SqlConnection())
{
try
{
this.type = database.Type;
DBHelper helper = new DBHelper(DBHelper.DatabaseTypes.Sql, database.ConnectionString);
DataSet ds = helper.ExecuteQuery(CommandType.Text, this.SqlForGetTable, null);
database.Tables = this.GetSQLTableList(ds);
ds = helper.ExecuteQuery(CommandType.Text, this.SqlForGetViews, null);
database.Views = this.GetSQLViewList(ds);
ds = helper.ExecuteQuery(CommandType.Text, this.SqlForGetStoreProcedures, null);
database.StoreProcedures = this.GetSQLStoreProcedureList(ds);
flag = true;
}
catch
{
flag = false;
}
}
return flag;
}
private Field GetSQLField(DataRow r)
{
Field field = new Field();
field.AllowNull = ObjectHelper.GetBool(r["AllowNull"]);
field.DefaultValue = ObjectHelper.GetString(r["DefaultValue"]);
field.FieldDescn = ObjectHelper.GetString(r["FieldDescn"]);
field.FieldLength = ObjectHelper.GetInt(r["FieldLength"]);
field.FieldName = ObjectHelper.GetString(r["FieldName"]);
field.FieldNumber = ObjectHelper.GetInt(r["FieldNumber"]);
field.FieldSize = ObjectHelper.GetInt(r["FieldSize"]);
field.SqlTypeString = ObjectHelper.GetString(r["FieldType"]);
field.IsIdentifier = ObjectHelper.GetBool(r["IsIdentifier"]);
field.IsKeyField = ObjectHelper.GetBool(r["IsKeyField"]);
field.TableName = ObjectHelper.GetString(r["TableName"]);
return field;
}
private List<string> GetSQLStoreProcedureList(DataSet ds)
{
List<string> list = new List<string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(row[0].ToString());
}
return list;
}
private List<Table> GetSQLTableList(DataSet ds)
{
DataTable table = ds.Tables[0];
List<Table> list = new List<Table>();
foreach (DataRow row in table.Rows)
{
Field sQLField = this.GetSQLField(row);
bool flag = false;
foreach (Table table2 in list)
{
if (sQLField.TableName == table2.Fields[0].TableName)
{
table2.Fields.Add(sQLField);
flag = true;
break;
}
}
if (!flag)
{
Table table3 = new Table();
table3.Name = sQLField.TableName;
List<Field> list2 = new List<Field>();
list2.Add(sQLField);
table3.Fields = list2;
list.Add(table3);
}
}
return list;
}
private List<string> GetSQLViewList(DataSet ds)
{
List<string> list = new List<string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(row[0].ToString());
}
return list;
}
private string SqlForGetTable
{
get
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT d.name N'TableName',a.colorder N'FieldNumber',a.name N'FieldName', ");
builder.Append("(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IsIdentifier',");
builder.Append("(case when (SELECT count(*) ");
builder.Append(" FROM sysobjects WHERE (name in (SELECT name FROM sysindexes ");
builder.Append(" WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys");
builder.Append(" WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns");
builder.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 ");
builder.Append(" then '1' else '0' end) N'IsKeyField', b.name N'FieldType',a.length N'FieldSize', ");
builder.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'FieldLength', ");
builder.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'DecimalDigits', ");
builder.Append(" (case when a.isnullable=1 then '1'else '0' end) N'AllowNull', isnull(e.text,'') N'DefaultValue', ");
builder.Append(" isnull(g.[value],'') AS N'FieldDescn' ");
builder.Append(" FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d ");
builder.Append(" on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id ");
if (this.type == Database.DatabaseType.Sql2005)
{
builder.Append(" left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id order by object_name(a.id),a.colorder");
}
else
{
builder.Append(" left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder ");
}
return builder.ToString();
}
}
}
