public async Task <T> GetAsync(long id)
{
using (var connection = _connectionProvider.GetConnection())
{
return(await connection.QuerySingleOrDefaultAsync <T>(_queryBuilder.Select(id)));
}
}
public TodoListAggregate GetById(object listId)
{
const string sql = "SELECT list.[Id] 'Id', list.[ListId] 'ListId', list.[Name], item.[Id] 'Id', item.[ItemId] 'ItemId', item.[Text], item.[IsCompleted], item.[TodoList_Id] " +
"FROM TodoList list " +
"LEFT JOIN [TodoItem] item ON item.[TodoList_Id] = list.[Id] " +
"WHERE list.[ListId] = @listId";
TodoListDto todoList = null;
var items = new List <TodoListItemDto>();
using (var c = _sqlConnectionProvider.GetConnection())
{
c.Query <TodoListDto, TodoListItemDto, TodoListDto>(sql, (list, item) =>
{
if (todoList == null)
{
todoList = list;
}
if (item != null) //if there is element
{
items.Add(item);
}
return(todoList);
}, new { listId = listId }, splitOn: "Id");
}
if (todoList == null)
{
return(null);
}
return(TodoListAggregate.Map.From(todoList, items));
}
public void CreateTodoListTableIfNotExists()
{
using (var con = _connectionProvider.GetConnection())
{
con.Execute(@"IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[TodoList]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TodoList] ([Id] INTEGER IDENTITY(1,1) PRIMARY KEY, [ListId] UNIQUEIDENTIFIER, [Name] TEXT)
END");
}
}
public IEnumerable <Fee> GetAll()
{
List <Fee> ret = new List <Fee>();
using (var connection = _connectionProvider.GetConnection())
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Fees";
connection.Open();
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ret.Add(new Fee(reader.GetString(1), reader.GetString(2), reader.GetString(3),
reader.GetInt32(4), reader.GetBoolean(5)));
}
}
}
}
}
return(ret);
}
public System.Collections.Generic.IReadOnlyList<TableDefinition> GetDefinitions(ISqlConnectionProvider sqlConnectionProvider)
{
var conn = sqlConnectionProvider.GetConnection();
var dbName = conn.Database;
var serverConnection = new ServerConnection(conn);
var server = new Server(serverConnection);
var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = false;
scripter.Options.NoCollation = true;
var db = server.Databases[dbName];
var results = new List<TableDefinition>();
foreach (Table table in db.Tables)
{
if (table.IsSystemObject)
continue;
var id = table.ID;
var definitions = scripter.Script(new Urn[] {table.Urn});
var sb = new StringBuilder();
foreach (var definition in definitions)
{
sb.AppendLine(definition);
}
var flattened = sb.ToString();
results.Add(new TableDefinition(id, flattened));
}
return results;
}
public void ImportSpreadsheet(ImportData data)
{
using (var cnn = _connectionProvider.GetConnection())
{
using (var cmd = cnn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = _storedProcedure;
cmd.Parameters.Add("@parameter", SqlDbType.Structured).Value = data.Table;
cnn.Open();
cmd.ExecuteNonQuery();
}
}
}
