public void Can_execute_simple_SQL_statement_with_object()
{
// Assign
string sqlQuery = "SELECT TOP 10 [incident].[naam] AS [Incident] FROM incident";
var connectionStringName = "TOPdesk577";
var configManager = new IncidentConfigurationManager();
Assert.IsNotNull(configManager, "SQL Connection is null.");
// Act
// Use the configManager to get a connection string.
var configString = configManager.GetConnectionString(connectionStringName);
var incidentList = new List <Incident>();
// Using the connection string, get a connection to the database inside a Using statement.
using (var sqlConnection = new SqlConnection(configString))
{
// Assert
Assert.IsNotNull(sqlConnection, "SQL connection is null");
//try
//{
sqlConnection.Open();
// set the SQL connection's SQL query text.
using (var command = new SqlCommand(sqlQuery, sqlConnection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
incidentList.Add(new Incident {
IncidentName = reader["Incident"].ToString()
});
}
}
}
//}
//catch (Exception exception)
//{
//}
//finally
//{
// sqlConnection.Close();
//}
}
foreach (var item in incidentList)
{
Assert.IsFalse(string.IsNullOrEmpty(item.IncidentName));
}
incidentList.ForEach(x => Assert.IsFalse(string.IsNullOrEmpty(x.IncidentName)));
}
public void Can_get_connectionString_from_SqlConnection()
{
var sqlConnection = new IncidentConfigurationManager();
Assert.IsNotNull(sqlConnection, "SqlConnnection is null");
var connectionManager = sqlConnection.GetConfigManager();
Assert.IsNotNull(connectionManager, "Connection Strings Collection is null");
Assert.IsTrue(connectionManager.Count > 0, "Connection String Collection doesn't contain any Connection Strings");
}
// [TestMethod]
// public void Can_Call_TOPDesk577_SQL_StoredProcedure_USP_Get_SubjectFromIncident()
// {
// // Assign.
// var sqlInstanceName = "TOPdesk577";
// var configManager = new IncidentConfigurationManager();
// string connectionString = configManager.GetConnectionString(sqlInstanceName);
// string storedProcedureName = "USP_Get_SubjectFromIncident";
// string expectedSubject = "Subject: Monitoring generated Counter ALARM at 8:24:25 am 9-Jul-18 on pcpri-tml01.reading.paperchase N/A";
// string incidentRequest = "";
// // Example Incident?
// var incident = new Incident;
// incident.IncidentNumber = "I1706-14426";
// // Action
// // Assert
// }
public void Can_Get_Request_From_Incident()
{
// Request comes from the incident.verzoek field.
string sqlInstanceName = "TOPdesk577";
var configManager = new IncidentConfigurationManager();
// The IncidentConfigurationManager returns an SQL Connection string for the specified SQL Instance.
string connectionString = configManager.GetConnectionString(sqlInstanceName);
string request = "";
string sqlStatement = "SELECT i.verzoek from incident where i.naam = @incidentNumber";
var incident = new Incident();
incident.IncidentNumber = "I1706-14426";
}
public void Can_Call_TOPDesk577_SQL_StoredProcedure_USP_Get_Incident_Is_Breached()
{
// Assign
var connectionStringName = "TOPdesk577";
var configManager = new IncidentConfigurationManager();
var connectionString = configManager.GetConnectionString(connectionStringName);
var storeProcedureName = "USP_Get_Incident_Is_Breached";
var incident = new Incident()
{
IncidentNumber = "I1805-0834"
};
var expectedResult = 1;
// Act
// Assert
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
var cmd = new SqlCommand(storeProcedureName, sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter();
param = cmd.Parameters.Add("@strIncident", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = incident.IncidentNumber;
//Add the output parameter to the command object
SqlParameter returnValueParam = new SqlParameter();
returnValueParam.ParameterName = "@breachedIncident";
returnValueParam.SqlDbType = SqlDbType.Int;
returnValueParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValueParam);
// Calling stored procedure with return value
// https://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value
cmd.ExecuteNonQuery();
int returnValue = (int)cmd.Parameters["@breachedIncident"].Value; //(int)outPutParameter.Value;
int.TryParse(cmd.Parameters["@breachedIncident"].Value.ToString(), out int returnValue2);
Assert.IsNotNull(returnValue, "Return value is null");
Assert.IsTrue(returnValue == expectedResult, "Return value is not as expected " + returnValue.ToString());
sqlConnection.Close();
}
}
public void Can_create_SqlConnection()
{
// Assign
var connectionStringName = "TOPdesk577";
var sqlConnection = new IncidentConfigurationManager();
var connectionString = sqlConnection.GetConnectionString(connectionStringName);
Assert.IsFalse(string.IsNullOrEmpty(connectionString), "Could not get Named connection string from Connection Manager");
// Act
SqlConnection connection = new SqlConnection(connectionString);
Assert.IsNotNull(connection, "SQL Connection is null.");
// Assert
}
public void Can_get_connectionString_from_SqlConnection_Connection()
{
var connectionStringName = "TOPdesk577";
var sqlConnection = new IncidentConfigurationManager();
Assert.IsNotNull(sqlConnection, "SqlConnnection is null");
var connectionManager = sqlConnection.GetConfigManager();
Assert.IsNotNull(connectionManager, "Connection Strings Collection is null");
Assert.IsTrue(connectionManager.Count > 0, "Connection String Collection doesn't contain any Connection Strings");
var connectionString = sqlConnection.GetConnectionString(connectionStringName);
Assert.IsFalse(string.IsNullOrEmpty(connectionString), "Could not get Named connection string from Connection Manager");
}
public void Can_execute_simple_SQL_statement()
{
// Assign
string sqlQuery = "SELECT TOP 10 [incident].[naam] AS [Incident] FROM incident";
var connectionStringName = "TOPdesk577";
var configManager = new IncidentConfigurationManager();
Assert.IsNotNull(configManager, "SQL Connection is null.");
// Act
// Use the configManager to get a connection string.
var configString = configManager.GetConnectionString(connectionStringName);
// Using the connection string, get a connection to the database inside a Using statement.
using (var sqlConnection = new SqlConnection(configString))
{
// Assert
Assert.IsNotNull(sqlConnection, "SQL connection is null");
//try
//{
sqlConnection.Open();
// set the SQL connection's SQL query text.
using (var command = new SqlCommand(sqlQuery, sqlConnection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var readText = reader["Incident"].ToString();
Assert.IsFalse(string.IsNullOrEmpty(readText), "Could not read text from Query " + sqlQuery);
}
}
}
//}
//catch (Exception exception)
//{
//}
//finally
//{
// sqlConnection.Close();
//}
}
}
public void Can_create_SqlConnection_and_open()
{
// Assign
var connectionStringName = "TOPdesk577";
var configManager = new IncidentConfigurationManager();
var connectionString = configManager.GetConnectionString(connectionStringName);
Assert.IsFalse(string.IsNullOrEmpty(connectionString), "Could not get Named connection string from Connection Manager");
// Act
using (var connection = new SqlConnection(connectionString))
{
Assert.IsNotNull(connection, "SQL Connection is null.");
connection.Open();
// Assert
Assert.IsTrue(connection.State == System.Data.ConnectionState.Open, "Could not open SQL connection. : " + connectionString);
connection.Close();
}
}
