protected void CreateConnection()
{
try
{
if (settings.UseIntegratedAuthentication)
{
connection = new SqlServerConnection(settings.ServerName);
}
else
{
connection = new SqlServerConnection(settings.ServerName, settings.UserName, settings.Password);
}
connection.Connect();
if (!connection.IsUserValid())
{
string msg = string.Format(
"The user is not valid for SQL Server {0}",
settings.ServerName);
throw new SqlExporterConnectionException(msg);
}
}
catch (Exception ex)
{
string msg = string.Format(
"There was an error while trying to connect to the SQL Server {0}",
settings.ServerName);
throw new SqlExporterConnectionException(msg, ex);
}
}
public void SqlServerConnectionConstructorAndConnectionTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
Assert.AreEqual(connection.IsConnected, true);
connection = new SqlServerConnection(ConnectionString, 0);
Assert.AreEqual(connection.IsConnected, true);
}
public void GameDatabaseCommandsTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
// Select All
var game = new Game();
var selectQuery = game.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
var 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
game = new Game(
0,
"Name",
"Description");
var insertCommand = game.GenerateInsertStatement();
Assert.IsFalse(string.IsNullOrEmpty(insertCommand));
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;
// Exists
var existsQuery = game.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 = game.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]);
Game foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGame);
Assert.AreNotSame(game, foundGame);
Assert.AreEqual(game.Id, foundGame.Id);
Assert.AreEqual(game.Name, foundGame.Name);
Assert.AreEqual(game.Description, foundGame.Description);
// Update
var updateGame = new Game(
newId,
"Name Edited",
"Description Edited");
var updateCommand = updateGame.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updateCommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updateCommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// Exists
existsQuery = updateGame.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 = updateGame.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]);
foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundGame);
Assert.AreNotSame(updateGame, foundGame);
Assert.AreEqual(updateGame.Id, foundGame.Id);
Assert.AreEqual(updateGame.Name, foundGame.Name);
Assert.AreEqual(updateGame.Description, foundGame.Description);
// Delete
var deleteCommand = game.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 = game.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 = game.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]);
foundGame = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundGame = Game.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundGame);
}
public void RatingDatabaseCommandTest()
{
var connection = new SqlServerConnection();
Assert.AreEqual(connection.IsConnected, false);
var result = connection.Connect(ConnectionString, 0);
Assert.AreEqual(result, true);
//select all
var rating = new Rating();
var selectQuery = rating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
var 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
rating = new Rating(0, "Name", "Description", "Symbol");
var insertcommand = rating.GenerateInsertStatment();
Assert.IsFalse(string.IsNullOrEmpty(insertcommand));
errorMessage = "";
var insertresult = connection.ExecuteCommand(insertcommand, ref errorMessage, out var newId);
Assert.IsTrue(insertresult);
Assert.IsTrue(newId > 0);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
rating.Id = newId;
// exist
var existsQuery = rating.GenerateExistQuery();
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 = rating.GenerateSelectQuery();
Assert.AreEqual(string.IsNullOrEmpty(selectQuery), true);
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.AreEqual(!string.IsNullOrEmpty(errorMessage), false);
Assert.IsNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
Rating foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundrating);
Assert.AreNotSame(rating, foundrating);
Assert.AreEqual(rating.Id, foundrating.Id);
Assert.AreEqual(rating.Name, foundrating.Name);
Assert.AreEqual(rating.Description, foundrating.Description);
Assert.AreEqual(rating.Symbol, foundrating.Symbol);
// update
var updaterating = new Rating(1, "Name", "Description", "Symbol");
var updatecommand = updaterating.GenerateUpdateStatement();
Assert.IsFalse(string.IsNullOrEmpty(updatecommand));
errorMessage = "";
var updateResult = connection.ExecuteCommand(updatecommand, ref errorMessage);
Assert.AreEqual(updateResult, true);
Assert.AreEqual(string.IsNullOrEmpty(errorMessage), true);
// exsits
existsQuery = updaterating.GenerateExistQuery();
Assert.IsFalse(string.IsNullOrEmpty(existsQuery));
errorMessage = "";
existsResultList = DataSetUtility.ToDictionaryList(existsResult.Tables[0]);
recordExists = existsResultList.Any(dictionary => (dictionary != null) && (dictionary.Count > 0));
Assert.AreEqual(recordExists, true);
// select
selectQuery = updaterating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
Assert.IsFalse(string.IsNullOrEmpty(errorMessage));
Assert.IsNotNull(selectResult);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNotNull(foundrating);
Assert.AreNotSame(rating, foundrating);
Assert.AreEqual(rating.Id, foundrating.Id);
Assert.AreEqual(rating.Name, foundrating.Name);
Assert.AreEqual(rating.Description, foundrating.Description);
Assert.AreEqual(rating.Symbol, foundrating.Symbol);
// delete
var deleteCommand = rating.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 = rating.GenerateExistQuery();
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 = rating.GenerateSelectQuery();
Assert.IsFalse(string.IsNullOrEmpty(selectQuery));
errorMessage = "";
selectResult = connection.ExecuteQuery(selectQuery, ref errorMessage);
selectResultList = DataSetUtility.ToDictionaryList(selectResult.Tables[0]);
foundrating = null;
if (selectResultList.Count > 0)
{
foreach (var dictionary in selectResultList.Where(dictionary => (dictionary != null) && (dictionary.Count > 0)))
{
foundrating = Rating.FromDictionary(dictionary);
break;
}
}
Assert.IsNull(foundrating);
}
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);
}