public void Should_handle_basic_CRUD_for_any_table()
{
const string schemaName = "dbo";
const string tableName = "TestTable";
var provider = new MssqlDataProvider();
// check for the test table
if (provider.TableExists(schemaName, tableName))
{
provider.DropTable(schemaName, tableName);
}
// create table
var createSql = string.Format(@"CREATE TABLE [{0}].[{1}](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL
CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED
([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]", schemaName, tableName);
provider.ExecuteSql(createSql);
// add to storage
var insertSql = string.Format("INSERT INTO [{0}].[{1}] (name) OUTPUT inserted.id VALUES (@Name)", schemaName, tableName);
var hierarchyId = provider.Update(insertSql, 0, new List <KeyValuePair <string, object> > {
new KeyValuePair <string, object>("@Name", "Test Name")
});
// retrieve from storage
var getSql = string.Format("SELECT id, name FROM [{0}].[{1}] WHERE id = @Id", schemaName, tableName);
var persistedRecord = provider.Get(getSql, hierarchyId, PopulateMethod);
Assert.That(persistedRecord, Is.Not.Null);
Assert.That(persistedRecord.Id, Is.EqualTo(hierarchyId));
Assert.That(persistedRecord.Name, Is.EqualTo("Test Name"));
// update, and save again
persistedRecord.Name = "New Name";
var updateSql = string.Format("UPDATE [{0}].[{1}] SET name=@Name WHERE id=@Id", schemaName, tableName);
var revisedId = provider.Update(updateSql, hierarchyId, new List <KeyValuePair <string, object> > {
new KeyValuePair <string, object>("@Name", "New Name")
});
Assert.That(revisedId, Is.EqualTo(hierarchyId));
// retrieve from storage
var revisedRecord = provider.Get(getSql, hierarchyId, PopulateMethod);
Assert.That(revisedRecord, Is.Not.Null);
Assert.That(revisedRecord.Id, Is.EqualTo(hierarchyId));
Assert.That(revisedRecord.Name, Is.EqualTo("New Name"));
// delete from storage
var deleteSuccessful = provider.Delete(schemaName, tableName, hierarchyId);
Assert.That(deleteSuccessful, Is.True);
// retrieve from storage
var deletedRecord = provider.Get(getSql, hierarchyId, PopulateMethod);
Assert.That(deletedRecord, Is.Null);
// drop the table
provider.DropTable(schemaName, tableName);
Assert.That(provider.TableExists(schemaName, tableName), Is.False);
}
