public void SqlServerConnectionExecuteCommandAndExecuteQueryTest()
{
var connection = new SqlServerConnection(ConnectionString, 0);
Assert.Equal(connection.IsConnected, true);
var attachmentTypeGuid = Guid.NewGuid();
var errorMessage = "";
var result = connection.ExecuteCommand($"INSERT INTO AttachmentTypes ([AttachmentTypeGuid], [ParentGuid], [Name], [Description], [StoreCompressed], [StoreEncrypted], [OrderNumber], [AssociatedObjects], [ReplaceAssociatedObjects]) VALUES ('{attachmentTypeGuid}', '{Guid.NewGuid()}', 'Test Attachment Type', 'Test Description', 0, 0, 1, NULL, 0)", ref errorMessage);
Assert.Equal(result, true);
const string text = "abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz abcdefghijklmnopqrstuvwxyz ";
result = connection.WriteBlobData($"UPDATE AttachmentTypes SET Description=@Description WHERE AttachmentTypeGuid = '{attachmentTypeGuid}'", "Description", "varchar", text, null);
Assert.Equal(result, true);
var dataResult = connection.ReadBlobData($"SELECT Description FROM AttachmentTypes WHERE AttachmentTypeGuid = '{attachmentTypeGuid}'", 0);
Assert.NotNull(dataResult);
var textResult = Encoding.UTF8.GetString(dataResult);
Assert.Equal(textResult, text);
result = connection.ExecuteCommand($"DELETE AttachmentTypes WHERE AttachmentTypeGuid = '{attachmentTypeGuid}'", ref errorMessage);
Assert.Equal(result, true);
}
public void GameImageDatabaseCommandsTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
// Add a Games
var game = new Game(
0,
"Name",
"Description");
var insertCommand = game.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
var errorMessage = "";
var insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out var newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
game.Id = newId;
var updateGame = new Game(
0,
"Name1",
"Description1");
insertCommand = updateGame.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
errorMessage = "";
insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
updateGame.Id = newId;
// Select All
var gameImage = new GameImage();
var selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
var selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
var selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
if (selectResultList.Count > 0)
{
Assert.IsTrue(selectResultList.Count > 0);
}
// Insert
gameImage = new GameImage(0, game.Id, _imageData);
insertCommand = gameImage.GenerateInsertStatment();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
errorMessage = "";
insertResult = connection.ExecuteCommand(insertCommand, ref errorMessage, out newId);
Assert.IsTrue(insertResult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
gameImage.Id = newId;
// Add image BLOB
var blobUpdateCommand = connection.CreateBlobUpdateStatement(GameImage.TableName, "Image", gameImage.GeneratePrimaryKeyWhereClause());
var blobResult = connection.WriteBlobData(blobUpdateCommand, "Image", "varbinary", null, _imageData);
Assert.IsTrue(blobResult);
// Exists
var existsQuery = gameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
var existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
var existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
var recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
GameImage foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGameImage);
// Read BLOB Data
foundGameImage.Image = connection.ReadBlobData($"SELECT Image FROM {GameImage.TableName} WHERE Id = {foundGameImage.Id}", 0);
Assert.AreNotSame(gameImage, foundGameImage);
Assert.AreEqual(gameImage.Id, foundGameImage.Id);
Assert.AreEqual(gameImage.GameId, foundGameImage.GameId);
if (gameImage.Image != null)
{
Assert.IsTrue(gameImage.Image.SequenceEqual(foundGameImage.Image));
}
else
{
Assert.IsNull(gameImage.Image);
Assert.IsNull(foundGameImage.Image);
}
// Update
var updateGameImage = new GameImage(
newId,
updateGame.Id,
_updateImageData);
var updateCommand = updateGameImage.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updateCommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updateCommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Update image BLOB
blobUpdateCommand = connection.CreateBlobUpdateStatement(GameImage.TableName, "Image", updateGameImage.GeneratePrimaryKeyWhereClause());
blobResult = connection.WriteBlobData(blobUpdateCommand, "Image", "varbinary", null, _updateImageData);
Assert.IsTrue(blobResult);
// Exists
existsQuery = updateGameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// Select
selectQuery = updateGameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGameImage);
Assert.AreNotSame(updateGameImage, foundGameImage);
// Read BLOB Data
foundGameImage.Image = connection.ReadBlobData($"SELECT Image FROM {GameImage.TableName} WHERE Id = {foundGameImage.Id}", 0);
Assert.AreEqual(updateGameImage.Id, foundGameImage.Id);
Assert.AreEqual(updateGameImage.GameId, foundGameImage.GameId);
if (updateGameImage.Image != null)
{
Assert.IsTrue(updateGameImage.Image.SequenceEqual(foundGameImage.Image));
}
else
{
Assert.IsNull(updateGameImage.Image);
Assert.IsNull(foundGameImage.Image);
}
// Delete
var deleteCommand = gameImage.GenerateDeleteStatement();
var deleteGameImage = gameImage.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
var deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Exists
existsQuery = gameImage.GenerateExistsQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResult = connection.ExecuteQuery(existsQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(existsResult);
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.IsFalse(recordExists);
// Select
selectQuery = gameImage.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundGameImage = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGameImage = GameImage.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundGameImage);
// Delete the games
deleteCommand = game.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
deleteCommand = updateGame.GenerateDeleteStatement();
Assert.IsFalse(string.IsNullOrEmpty(deleteCommand));
errorMessage = "";
deleteResult = connection.ExecuteCommand(deleteCommand, ref errorMessage);
Assert.AreEqual(deleteResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
}