public virtual void Add(T item)
{
var fieldNamesAndValues = GetFieldNamesAndValuesForInsertOrUpdate(item);
SqlCommand command = SqlServerCommandHelper.GetInsertCommand(GetTableName(), fieldNamesAndValues, ID);
item.SetId(SqlServerCommandHelper.ExecuteSqlCommand(command, outputIdValue: true));
}
public void DeleteAll()
{
string sql = SqlServerCommandHelper.GetSimpleDeleteStatement(GetTableName());
SqlCommand command = new SqlCommand(sql);
SqlServerCommandHelper.ExecuteSqlCommand(command);
}
public ICollection <DatabaseFile> GetDatabaseFiles()
{
string sql = "SELECT name, physical_name, state_desc, (size * 8.0)*1000 AS size, CASE WHEN max_size = -1 THEN 9999999 ELSE (max_size * 8.0)*1000 END AS max_size FROM sys.database_files;";
SqlCommand command = new SqlCommand(sql);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
var items = new List <DatabaseFile>();
while (reader.Read())
{
items
.Add
(
new DatabaseFile
(
name:
reader.GetColumnValue("name").ToString(),
physicalFileName:
reader.GetColumnValue("physical_name").ToString(),
sizeInBytes:
Convert.ToInt64(reader.GetColumnValue("size")),
stateDesc:
reader.GetColumnValue("state_desc").ToString()
)
);
}
return(items);
}
public virtual void Delete(int id)
{
string sql = SqlServerCommandHelper.GetSimpleDeleteStatement(GetTableName()) + WhereIdEquals();
SqlCommand command = new SqlCommand(sql);
AddIdParameterToCommand(command, id);
SqlServerCommandHelper.ExecuteSqlCommand(command);
}
public bool Exists(int id)
{
string sql = "select 1 from " + GetTableName() + WhereIdEquals();
SqlCommand command = new SqlCommand(sql);
AddIdParameterToCommand(command, id);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
return(reader.Read());
}
public bool InUse(string userId)
{
string sql = "select 1 from vw_user_ids_in_use where userid = @userid";
SqlCommand command = new SqlCommand(sql);
command.Parameters.AddWithValue("@userid", userId);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
return(reader.Read());
}
public T Get(int id)
{
string sql = GetSelectStatement() + WhereIdEquals();
SqlCommand command = new SqlCommand(sql);
AddIdParameterToCommand(command, id);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
reader.Read();
return(GetItemFromDataReader(reader));
}
public virtual void Update(T item)
{
int id = item.Id;
var fieldNamesAndValues = GetFieldNamesAndValuesForInsertOrUpdate(item);
var whereClause = WhereIdEquals();
var whereClauseParameterNamesAndValues = new Dictionary <string, object>();
whereClauseParameterNamesAndValues.Add(ID, id);
SqlCommand command = SqlServerCommandHelper.GetUpdateCommand(GetTableName(), fieldNamesAndValues, whereClause, whereClauseParameterNamesAndValues);
SqlServerCommandHelper.ExecuteSqlCommand(command);
}
public ICollection <T> GetAll()
{
string sql = GetSelectStatement() + ";";
SqlCommand command = new SqlCommand(sql);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
var items = new List <T>();
while (reader.Read())
{
T item = GetItemFromDataReader(reader);
items.Add(item);
}
return(items);
}
public ICollection <T> GetMatchingOn(int foreignKeyId)
{
string sql = GetSelectStatement() + WhereForeignKeyEquals() + ";";
SqlCommand command = new SqlCommand(sql);
AddIdParameterToCommand(command, foreignKeyId);
IDataReader reader = SqlServerCommandHelper.ExecuteSqlQuery(command);
var items = new List <T>();
while (reader.Read())
{
T item = GetItemFromDataReader(reader);
items.Add(item);
}
return(items);
}
protected string GetViewSelectStatement()
{
var fieldNames = GetFieldNamesForSelectStatement();
return(SqlServerCommandHelper.GetSimpleSelectStatement(GetViewName(), fieldNames));
}
