public IEnumerable <PictureSummary> GetTagPictureByName(string name, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM PictureTag tag
INNER JOIN PictureTagMap map ON map.tagId = tag.tagId
LEFT JOIN Picture pic ON pic.picId = map.picId
WHERE tag.tagName LIKE @tag", index, size),
new SqlParameter("@tag", $"%{name}%"),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> pictures = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
pictures.Add(new PictureSummary(item));
}
return(pictures);
}
public void Should_get_results()
{
var param = new TaskParams("path", "SELECT [dbms_id] FROM [msdb].[dbo].[MSdbms]", ConnectionString, "SqlServer", "name", "statsdudp");
var log = MockRepository.GenerateMock<ILog>();
var sqlServerClient = new SqlServerClient(log, param);
var results = sqlServerClient.Get();
Assert.That(results.Count, Is.EqualTo(8));
}
public void Create_ConnectionParamsIsNull_ReturnThrow(DbConnectionParams parameter)
{
var client = new SqlServerClient();
Action action = () => client.Create(parameter).Should().BeFalse();
action.Should()
.Throw <System.Exception>()
.WithMessage("Connection failed: Object reference not set to an instance of an object.");
}
public void ConnectToDB_SqlServer(string connectionName, SqlConnectionStringBuilder sqlConnectionString)
{
databaseController.Connections.Should().NotContainKey(connectionName, $"Connection \"{connectionName}\" already exists");
var connection = new SqlServerClient();
connection.Create(sqlConnectionString).Should().BeTrue();
connection.IsConnectAlive().Should().BeTrue();
databaseController.Connections.TryAdd(connectionName, (connection, TypeOfAccess.Local, sqlConnectionString.ConnectTimeout));
}
public void Create_CorrectConnectionParams_ReturnTrue()
{
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <string>())).Returns(true);
client._provider = mockSqlProvider.Object;
client.Create(dbConnectionParams).Should().BeTrue();
}
public void IsConnectAlive_CorrectConnectionParams_ReturnFalse()
{
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.IsConnectAlive()).Returns(false);
client._provider = mockSqlProvider.Object;
client.IsConnectAlive().Should().BeFalse();
}
public void ConnectToDB_SqlServer(string connectionName, DbConnectionParams connectionParams)
{
var(isValid, results) = Validate.ValidateModel(connectionParams);
isValid.Should().BeTrue(Message.CreateMessage(results));
databaseController.Connections.Should().NotContainKey(connectionName, $"Connection \"{connectionName}\" already exists");
var connection = new SqlServerClient();
connection.Create(connectionParams).Should().BeTrue();
connection.IsConnectAlive().Should().BeTrue();
databaseController.Connections.TryAdd(connectionName, (connection, connectionParams.Timeout));
}
public void Create_IncorrectConnectionParams_ReturnThrow()
{
var client = new SqlServerClient();
Action action = () => client.Create(dbConnectionParams).Should().BeFalse();
action.Should()
.Throw <ConnectSqlException>()
.Which.Message.Contains($"Connection failed. Connection with parameters: {Database.Helpers.Message.CreateMessage(dbConnectionParams)}");
action.Should()
.Throw <ConnectSqlException>()
.WithMessage("*A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.*");
}
public void Should_get_result()
{
var name = Guid.NewGuid().ToString();
var param = new TaskParams("path", "SELECT COUNT(*) as count FROM [master].[dbo].[spt_monitor]", ConnectionString, "SqlServer", name, "Statsdudp");
var log = MockRepository.GenerateMock<ILog>();
var sqlServerClient = new SqlServerClient(log, param);
//Test
var results = sqlServerClient.Get();
//Asset
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo(name));
Assert.That(results.Count, Is.EqualTo(1));
}
public async Task ShouldExecuteMethodInConnection()
{
SqlServerClient client = new SqlServerClient(_testEnvironment.SqlServerSettings);
bool methodDidExecute = await client.ExecuteInManagedConnectionAsync(async connection =>
{
IEnumerable <dynamic> result
= await connection.QueryAsync("SELECT TOP 1 * FROM sys.all_objects");
return(result.Any());
});
Assert.True(methodDidExecute);
}
public async Task <int> GetSqlDwActiveQueryCount()
{
try
{
SqlServerClient client = new SqlServerClient();
return(await client.GetActiveQueryCount(SqlServerDwConnectionString, sqlDwQueryTableName));
}
catch (Exception e)
{
Trace.TraceError("HomeController.GetSqlDwActiveQueryCount failed with the following exception: " + e.ToString());
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
}
public void Should_get_result_with_date_and_name_set_in_select()
{
var name = Guid.NewGuid().ToString();
var param = new TaskParams("path", "SELECT 234 , DATEADD(day,11,GETDATE()), 'someName'", ConnectionString, "SqlServer", name, "statsdudp");
var log = MockRepository.GenerateMock<ILog>();
var sqlServerClient = new SqlServerClient(log, param);
//Test
var results = sqlServerClient.Get();
//Asset
var futureDate = DateTime.Now.Add(new TimeSpan(11, 0, 0, 0));
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("someName"));
Assert.That(results[0].Path, Is.EqualTo("path"));
Assert.That(results[0].Value, Is.EqualTo(234));
}
public void Create_IncorrectConnectionParams_ReturnThrow()
{
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <string>())).Throws(new ConnectSqlException("test"));
client._provider = mockSqlProvider.Object;
Action action = () => client.Create(dbConnectionParams);
action.Should()
.Throw <ConnectSqlException>()
.WithMessage("Connection failed. test");
}
public void Should_get_results()
{
var metricName = Guid.NewGuid().ToString();
var serverClientParams = new SqlServerClient
{
ConnectionString = e.Encrypt(ConnectionString),
Path = SimplePath,
Sql = "SELECT [dbms_id] FROM [msdb].[dbo].[MSdbms]",
ClientName = "GraphiteTcpClient",
};
var sqlServerClient = new SqlServerClient(log, serverClientParams,encryption);
var results = sqlServerClient.Get();
Assert.That(results.Count, Is.EqualTo(8));
}
public void ExecuteNonQuery_IncorrectQuery_ReturnNull()
{
var query = "INSERT INTO test111 f1 VALUES (10)";
var mockSqlProvider = new Mock <ISqlProvider>();
var connect = new SqlConnection();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.SetupCommand(It.IsAny <string>(), null)).Returns(connect.CreateCommand);
client._provider = mockSqlProvider.Object;
var count = client.ExecuteNonQuery(query);
count.Should().Be(0);
}
public void ExecuteQuery_IncorrectParams_ReturnNull()
{
var mockSqlProvider = new Mock <IDbClient>();
var connectName = "NewConnect";
var query = "SELECT top 100 * test111";
IDbClient connection = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <DbConnectionParams>())).Returns(true);
connection = mockSqlProvider.Object;
this.databaseController.Connections.TryAdd(connectName, (connection, 30));
step.ExecuteQuery(QueryType.SELECT, connectName, query);
}
public void ExecuteQuery_IncorrectQuery_ReturnNull()
{
var query = "select top 1 * test111";
var mockSqlProvider = new Mock <ISqlProvider>();
var connect = new SqlConnection();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.SetupCommand(It.IsAny <string>(), null)).Returns(connect.CreateCommand);
client._provider = mockSqlProvider.Object;
var(outResult, count) = client.ExecuteQuery(query);
count.Should().Be(0);
}
public static void Before(VariableController variableController, DatabaseController databaseController)
{
var connectionsStringBuilder = new SqlConnectionStringBuilder
{
DataSource = variableController.GetVariableValueText("SOURCE"),
InitialCatalog = variableController.GetVariableValueText("DATABASE"),
UserID = variableController.GetVariableValueText("LOGIN"),
Password = variableController.GetVariableValueText("PASSWORD"),
ConnectTimeout = 60
};
var connection = new SqlServerClient();
connection.Create(connectionsStringBuilder);
connection.IsConnectAlive();
databaseController.Connections.TryAdd("QA", (connection, typeOfAccess: Molder.Infrastructures.TypeOfAccess.Global, connectionsStringBuilder.ConnectTimeout));
}
public void CreateMessage_CorrectCommand_ReturnString()
{
var query = "select * from test111";
var mockSqlProvider = new Mock <ISqlProvider>();
var connect = new SqlConnection();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.SetupCommand(It.IsAny <string>(), null)).Returns(connect.CreateCommand);
client._provider = mockSqlProvider.Object;
var command = client._provider.SetupCommand(query);
var result = command.CreateMessage();
result.Should().NotBeNullOrEmpty();
}
public void ExecuteScalar_CorrectQuery_ReturnObject()
{
var query = "select top 1 * from test111";
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider
.Setup(u => u.UsingTransaction(It.IsAny <Action <SqlTransaction> >(), It.IsAny <Action <Exception> >(), null))
.Callback((Action <SqlTransaction> action, Action <Exception> ex, Action success) => new object());
client._provider = mockSqlProvider.Object;
var outResult = client.ExecuteScalar(query);
outResult.Should().BeNull();
}
public void ExecuteNonQuery_CorrectQuery_ReturnTable()
{
var query = "INSERT INTO test111 (f1) VALUES (1)";
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider
.Setup(u => u.UsingTransaction(It.IsAny <Action <SqlTransaction> >(), It.IsAny <Action <Exception> >(), null))
.Callback((Action <SqlTransaction> action, Action <Exception> ex, Action success) => new DataTable());
client._provider = mockSqlProvider.Object;
var count = client.ExecuteNonQuery(query);
count.Should().Be(0); //TODO (not 0)
}
public void ExecuteQuery_CorrectQuery_ReturnTable()
{
var query = "select top 1 * from test111";
var mockSqlProvider = new Mock <ISqlProvider>();
var client = new SqlServerClient();
mockSqlProvider
.Setup(u => u.UsingTransaction(It.IsAny <Action <SqlTransaction> >(), It.IsAny <Action <Exception> >(), null))
.Callback((Action <SqlTransaction> action, Action <Exception> ex, Action success) => new DataTable());
client._provider = mockSqlProvider.Object;
var(outResult, count) = client.ExecuteQuery(query);
count.Should().Be(0); //TODO (not 0)
}
public void Should_get_result()
{
var metricName = Guid.NewGuid().ToString();
var serverClientParams = new SqlServerClient
{
ConnectionString = e.Encrypt(ConnectionString),
Path = SimplePath,
Sql = SimpleQuery,
ClientName = "GraphiteTcpClient",
};
var sqlServerClient = new SqlServerClient(log, serverClientParams, encryption);
//Test
var results = sqlServerClient.Get();
//Asset
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Path, Is.EqualTo(SimplePath));
Assert.That(results[0].Value, Is.EqualTo("234"));
}
public void Should_get_result_with_date()
{
var name = Guid.NewGuid().ToString();
var param = new TaskParams(
"path", "SELECT 234 , DATEADD(day,11,GETDATE())", ConnectionString, "SqlServer", name, "statsdudp");
var log = MockRepository.GenerateMock<ILog>();
var sqlServerClient = new SqlServerClient(log, param);
//Test
var results = sqlServerClient.Get();
//Asset
var futureDate = DateTime.Now.Add(new TimeSpan(11, 0, 0, 0));
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo(name));
Assert.That(results[0].Value, Is.EqualTo(234));
Assert.That(results[0].TimeStamp.Day, Is.EqualTo(futureDate.Day));
Assert.That(results[0].TimeStamp.Hour, Is.EqualTo(futureDate.Hour));
Assert.That(results[0].TimeStamp.Minute, Is.EqualTo(futureDate.Minute));
Assert.That(results[0].TimeStamp.Year, Is.EqualTo(futureDate.Year));
}
public void ExecuteQuery_CorrectParams_ReturnNewVariable()
{
var mockSqlProvider = new Mock <IDbClient>();
var varName = "newVariable";
var connectName = "NewConnect";
var query = "INSERT INTO test111 (f1) VALUES (1) ";
IDbClient connection = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <DbConnectionParams>())).Returns(true);
connection = mockSqlProvider.Object;
this.databaseController.Connections.TryAdd(connectName, (connection, 30));
step.ExecuteQuery(QueryType.INSERT, connectName, varName, query);
this.variableController.Variables.Should().NotBeEmpty();
}
public void CreateMessage_CommandIsEmpty_ReturnNull()
{
var query = "select * from test111";
var mockSqlProvider = new Mock <ISqlProvider>();
var connect = new SqlConnection();
var client = new SqlServerClient();
mockSqlProvider.Setup(c => c.SetupCommand(It.IsAny <string>(), null)).Returns(connect.CreateCommand);
client._provider.Value = mockSqlProvider.Object;
var command = client._provider.Value.SetupCommand(query, null);
command = null;
var result = Message.CreateMessage(command);
result.Should().BeNull();
}
public void ExecuteQuery_CorrectParams_ReturnNewVariable()
{
var mockSqlProvider = new Mock <IDbClient>();
var varName = "newVariable";
var connectName = "NewConnect";
var query = "INSERT INTO test111 (f1) VALUES (1) ";
IDbClient connection = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <SqlConnectionStringBuilder>())).Returns(true);
connection = mockSqlProvider.Object;
databaseController.Connections.TryAdd(connectName, (connection, TypeOfAccess.Local, 30));
step.ExecuteQueryTypeWithVarName(QueryType.INSERT, connectName, varName, new QueryParam {
Query = query
});
variableController.Variables.Should().NotBeEmpty();
}
public void ExecuteInsertQueryFromTable_CorrectParams_ReturnThrow()
{
var mockSqlProvider = new Mock <IDbClient>();
var tableName = "newVariable";
var connectName = "NewConnect";
var insertQuery = new List <Dictionary <string, object> >();
insertQuery.Add(new Dictionary <string, object>());
IDbClient connection = new SqlServerClient();
mockSqlProvider.Setup(c => c.Create(It.IsAny <DbConnectionParams>())).Returns(true);
mockSqlProvider.Setup(c => c.IsConnectAlive()).Returns(true);
connection = mockSqlProvider.Object;
this.databaseController.Connections.TryAdd(connectName, (connection, 30));
Action action = () => step.ExecuteInsertQueryFromTable(tableName, connectName, insertQuery);
action.Should()
.Throw <Xunit.Sdk.XunitException>();
}
public IEnumerable <PictureSummary> GetPictureByIntro(string keyword, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM Picture pic WHERE pic.picIntro LIKE @keyword", index, size),
new SqlParameter("@keyword", $"%{keyword}%"),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> pictures = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
pictures.Add(new PictureSummary(item));
}
return(pictures);
}
public IEnumerable <PictureSummary> GetChannelPicture(int id, int index, int size)
{
DataTable dataTable = DB.Client.ExecuteDataTable(SqlServerClient.PagingBuild(@"
SELECT
pic.picId,
pic.picSize,
pic.picNum,
pic.picName,
(SELECT TOP 1 picSrc.picPreview FROM PictureSource picSrc WHERE picSrc.picId = pic.picId) AS picPreview,
Row_Number() OVER(ORDER BY pic.picId) RowNumber
FROM Picture pic WHERE pic.picChannel = @channelId", index, size),
new SqlParameter("@channelId", id),
new SqlParameter("@pageSize", size),
new SqlParameter("@pageIndex", index));
List <PictureSummary> channels = new List <PictureSummary>();
foreach (DataRow item in dataTable.Rows)
{
channels.Add(new PictureSummary(item));
}
return(channels);
}
/// <summary>
/// Creates a new instance of the <see cref="SqlCigarRepository"/> configured
/// for testing.
/// </summary>
/// <returns></returns>
protected SqlCigarRepository CreateRepository()
{
var client = new SqlServerClient(_testEnvironment.SqlServerSettings);
return(new SqlCigarRepository(client, new JsonEntitySerializer()));
}
public void Should_get_result_with_date()
{
var metricName = Guid.NewGuid().ToString();
var serverClientParams = new SqlServerClient
{
ConnectionString = e.Encrypt(ConnectionString),
Path = SimplePath,
Sql = SimpleQuery,
ClientName = "GraphiteTcpClient",
};
var sqlServerClient = new SqlServerClient(log, serverClientParams,encryption);
//Test
var results = sqlServerClient.Get();
//Asset
var futureDate = DateTime.Now.Add(new TimeSpan(11, 0, 0, 0));
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Path, Is.EqualTo(SimplePath));
Assert.That(results[0].Value, Is.EqualTo("234"));
Assert.That(results[0].TimeStamp.Day, Is.EqualTo(futureDate.Day));
Assert.That(results[0].TimeStamp.Hour, Is.EqualTo(futureDate.Hour));
Assert.That(results[0].TimeStamp.Minute, Is.EqualTo(futureDate.Minute));
Assert.That(results[0].TimeStamp.Year, Is.EqualTo(futureDate.Year));
}
public void Should_get_result_with_date_and_name_set_in_select()
{
var metricName = Guid.NewGuid().ToString();
var serverClientParams = new SqlServerClient
{
ConnectionString = e.Encrypt(ConnectionString),
Path = SimplePath,
Sql = "SELECT 234 , DATEADD(day,11,GETDATE()), 'someName'",
ClientName = "GraphiteTcpClient",
};
var sqlServerClient = new SqlServerClient(log, serverClientParams, encryption);
//Test
var results = sqlServerClient.Get();
//Asset
var futureDate = DateTime.Now.Add(new TimeSpan(11, 0, 0, 0));
Assert.That(results.Count, Is.EqualTo(1));
Assert.That(results[0].Name, Is.EqualTo("someName"));
Assert.That(results[0].Path, Is.EqualTo(SimplePath));
Assert.That(results[0].Value, Is.EqualTo("234"));
}
