public Schema GetSchema(Schema schema)
{
var systemFields = schema.Columns.Where(it => it.IsSystemField).Select(it => it.Name);
schema.Columns.Clear();
var tableName = GetTableName(schema);
var sql = String.Format(sqlGetColumnsFormat, tableName);
SqlCommand command = new SqlCommand()
{
CommandText = sql
};
var connection = new SqlConnection();
var fields = SQLServerHelper.ExecuteReader(schema.Repository, command, out connection) as SqlDataReader;
while (fields.Read())
{
var fieldName = fields[0].ToString();
if (!systemFields.Contains(fieldName, new equal()))
{
var column = new Column();
column.Name = fieldName;
schema.Columns.Add(column);
}
}
schema.Columns.RemoveAll(it => it.IsSystemField);
return(schema);
}
private T FirstOrDefault(SQLServerVisitor <T> visitor)
{
IEnumerable <Parameter> parameters;
var sql = BuildQuerySQL(visitor, out parameters);
string orderBy = "Id ASC";
if (visitor.OrderClauses != null && visitor.OrderClauses.Count > 0)
{
orderBy = ToOrderString(visitor);
}
if (visitor.Skip != 0)
{
sql = string.Format(@"SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY {0}) AS RowIndex
FROM ({1})content
)paging
WHERE (RowIndex > {2})", orderBy, sql, visitor.Skip);
}
sql = string.Format("SELECT TOP 1 * FROM ({0})T ORDER BY {1}", sql, orderBy);
var command = BuildCommand(sql, parameters);
T content = null;
SqlConnection connection;
using (var dataReader = SQLServerHelper.ExecuteReader(ContentQuery.Repository, command, out connection))
{
try
{
if (dataReader.Read())
{
content = new T();
dataReader.ToContent(content);
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(content);
}
private IEnumerable <T> List(SQLServerVisitor <T> visitor)
{
var command = BuildCommand(visitor);
SqlConnection connection;
List <T> list = new List <T>();
using (var dataReader = SQLServerHelper.ExecuteReader(ContentQuery.Repository, command, out connection))
{
try
{
while (dataReader.Read())
{
T content = new T();
list.Add(dataReader.ToContent(content));
}
}
finally
{
dataReader.Close();
connection.Close();
}
}
return(list);
}