private static void LoadViews(Database database, string connectionString)
{
var dsView = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForViews());
var dsViewColumn = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForViewsColumns());
//Add the Views
if (dsView.Tables.Count > 0)
{
foreach (DataRow rowView in dsView.Tables[0].Rows)
{
var name = (string)rowView["name"];
var schema = (string)rowView["schemaname"];
var sql = SchemaModelHelper.GetViewBody((string)rowView["definition"]);
var view = database.ViewList.FirstOrDefault(x => x.Name == name);
if (view == null)
{
view = new View();
view.Name = name;
view.SQL = sql;
view.Schema = schema;
database.ViewList.Add(view);
}
}
}
//Add the columns
if (dsViewColumn.Tables.Count > 0)
{
foreach (DataRow rowView in dsViewColumn.Tables[0].Rows)
{
var viewName = (string)rowView["viewname"];
var columnName = (string)rowView["columnname"];
var dataType = DatabaseHelper.GetSQLDataType(rowView["system_type_id"].ToString(), database.UserDefinedTypes);
var length = int.Parse(rowView["max_length"].ToString());
var view = database.ViewList.FirstOrDefault(x => x.Name.ToLower() == viewName.ToLower());
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar))
{
length /= 2;
}
else if (dataType == SqlDbType.DateTime2)
{
length = int.Parse(rowView["scale"].ToString());
}
if (view != null)
{
var field = new Field();
field.Name = columnName;
field.DataType = dataType;
field.Length = length;
field.Scale = int.Parse(rowView["scale"].ToString());
field.Nullable = (bool)rowView["is_nullable"];
view.FieldList.Add(field);
}
}
}
}
public IEnumerable <string> GetViewList(string connectionString)
{
var retval = new List <string>();
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlForViews()))
{
while (tableReader.Read())
{
var newEntity = new View();
newEntity.Name = tableReader["name"].ToString();
retval.Add(newEntity.Name);
//newEntity.Schema = tableReader["schema"].ToString();
}
}
return(retval);
}