public void CreateWithCorrelation(BuildSqlDialect sqlDialect)
{
var saga = new SagaDefinition(
name: "theSaga",
tableSuffix: "theSaga",
correlationProperty: new CorrelationProperty
(
name: "CorrelationProperty",
type: CorrelationPropertyType.String
)
);
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
SagaScriptBuilder.BuildCreateScript(saga, sqlDialect, writer);
}
var script = builder.ToString();
if (sqlDialect == BuildSqlDialect.MsSqlServer)
{
SqlValidator.Validate(script);
}
Approver.Verify(script, scenario: "ForScenario." + sqlDialect);
}
/// <summary>
/// Pobiera ilość dni pracy aplikacji. Zwraca ilość dni z minusem jako string.
/// </summary>
/// <returns>Zwraca ilość dni jako string.</returns>
public string GetDayWorkingApplication()
{
string contentCommand = "SELECT DATEDIFF(DAY, GETDATE(), " + SqlValidator.Validate(GetDataRunApplication()) + ") as dateDifference";
string dateDifference = DataBase.GetListStringFromExecuteReader(contentCommand, "dateDifference")[0];
return(dateDifference);
}
public void CreateWithCorrelationAndTransitional(BuildSqlVariant sqlVariant)
{
var saga = new SagaDefinition(
tableSuffix: "theSaga",
name: "theSaga",
correlationProperty: new CorrelationProperty
(
name: "CorrelationProperty",
type: CorrelationPropertyType.String
),
transitionalCorrelationProperty: new CorrelationProperty
(
name: "TransitionalProperty",
type: CorrelationPropertyType.String
)
);
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
SagaScriptBuilder.BuildCreateScript(saga, sqlVariant, writer);
}
var script = builder.ToString();
if (sqlVariant == BuildSqlVariant.MsSqlServer)
{
SqlValidator.Validate(script);
}
using (ApprovalResults.ForScenario(sqlVariant))
{
Approvals.Verify(script);
}
}
public void BuildDropScript(BuildSqlDialect sqlDialect)
{
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
var saga = new SagaDefinition(
correlationProperty: new CorrelationProperty
(
name: "CorrelationProperty",
type: CorrelationPropertyType.String
),
tableSuffix: "theSaga",
name: "theSaga"
);
SagaScriptBuilder.BuildDropScript(saga, sqlDialect, writer);
}
var script = builder.ToString();
if (sqlDialect == BuildSqlDialect.MsSqlServer)
{
SqlValidator.Validate(script);
}
#if NET452
using (ApprovalResults.ForScenario(sqlDialect))
{
Approvals.Verify(script);
}
#endif
}
public void TestParseSimple()
{
using (var validator = new SqlValidator(_cstr_db))
{
Assert.IsTrue(validator.ParseSQLBasic("select * from Customers;"));
Assert.IsFalse(validator.ParseSQLBasic("select from Customers;"));
}
}
public void TestParseSimple()
{
using (var validator = new SqlValidator(_cstr_db))
{
Assert.IsTrue(validator.ParseSQLBasic("select * from Customers;"));
Assert.IsFalse(validator.ParseSQLBasic("select from Customers;"));
}
}
public void TestGetParametersAndValidate_GoodQuery()
{
using (var validator = new SqlValidator(_cstr_db))
{
IList<SqlError> errors;
var Parameters = validator.GetParametersAndValidate("select * from Customer where FirstName = @name;", out errors);
Assert.IsNull(errors);
Assert.IsTrue(Parameters.Any());
Assert.IsTrue(Parameters.Any(p => p.Name == "@name"));
}
}
public void TestGetParametersAndValidate_GoodQuery()
{
using (var validator = new SqlValidator(_cstr_db))
{
IList <SqlError> errors;
var Parameters = validator.GetParametersAndValidate("select * from Customer where FirstName = @name;", out errors);
Assert.IsNull(errors);
Assert.IsTrue(Parameters.Any());
Assert.IsTrue(Parameters.Any(p => p.Name == "@name"));
}
}
public void TestGetParametersAndValidate_BadQuery()
{
using (var validator = new SqlValidator(_cstr_db))
{
IList<SqlError> errors;
var Parameters = validator.GetParametersAndValidate("select * from NotARealTable where FirstName = @name;", out errors);
Assert.IsNotNull(errors);
Assert.AreEqual(errors.Count(), 1);
Assert.IsNull(Parameters);
foreach (var error in errors) Debug.WriteLine(error);
}
}
public static void ChangeNameActivity(string oldNameActivity, string newNameActivity)
{
string contentCommand = "UPDATE nameactivity SET NameActivity = " + SqlValidator.Validate(newNameActivity)
+ " WHERE NameActivity = " + SqlValidator.Validate(oldNameActivity);
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Nazwa aktywności " + SqlValidator.Validate(oldNameActivity) + " została zmieniona na " + SqlValidator.Validate(newNameActivity));
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się zamienić nazwy aktywności " + SqlValidator.Validate(oldNameActivity) + " na " + SqlValidator.Validate(newNameActivity));
}
}
public static bool Add(string name)
{
string contentCommand = "INSERT INTO membership (Title) VALUES (" + SqlValidator.Validate(name) + ")";
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się dodać nowej grupy");
return(false);
}
else
{
return(true);
}
}
public static SqlExpression operator |(SqlExpression left, SqlExpression right)
{
if (left.IsNullReference())
{
return(right);
}
if (right.IsNullReference())
{
return(left);
}
if (SqlValidator.IsBooleanExpression(left))
{
return(SqlDml.Or(left, right));
}
return(SqlDml.BitOr(left, right));
}
public void BuildDropScript(BuildSqlDialect sqlDialect)
{
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
OutboxScriptBuilder.BuildDropScript(writer, sqlDialect);
}
var script = builder.ToString();
if (sqlDialect == BuildSqlDialect.MsSqlServer)
{
SqlValidator.Validate(script);
}
Approver.Verify(script, scenario: "ForScenario." + sqlDialect);
}
public static bool AddFilterToConfiguration(int idGroup, string filter)
{
string contentCommand = "UPDATE membership SET Filter = " + SqlValidator.Validate(filter)
+ " WHERE Id = " + idGroup;
if (!DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportError("Nie udało się dodać filtru ",
ApplicationLog.LogService.GetNameCurrentMethod() + "()",
System.IO.Directory.GetParent(System.IO.Directory.GetCurrentDirectory()).Parent.FullName + @"\Membership_db.cs");
return(false);
}
else
{
return(true);
}
}
public void TestGetParametersAndValidate_BadQuery()
{
using (var validator = new SqlValidator(_cstr_db))
{
IList <SqlError> errors;
var Parameters = validator.GetParametersAndValidate("select * from NotARealTable where FirstName = @name;", out errors);
Assert.IsNotNull(errors);
Assert.AreEqual(errors.Count(), 1);
Assert.IsNull(Parameters);
foreach (var error in errors)
{
Debug.WriteLine(error);
}
}
}
public void BuildDropScript(BuildSqlVariant sqlVariant)
{
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
TimeoutScriptBuilder.BuildDropScript(writer, sqlVariant);
}
var script = builder.ToString();
if (sqlVariant == BuildSqlVariant.MsSqlServer)
{
SqlValidator.Validate(script);
}
using (ApprovalResults.ForScenario(sqlVariant))
{
Approvals.Verify(script);
}
}
public string CountApplicationInInterwalTime(string dateFrom, string dateTo)
{
string contentCommand = "SELECT COUNT(DISTINCT IdTitle) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " LEFT JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND (activeapplications.IdMembership IS NULL OR membership.AsOneApplication = 0) "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
int returnCount = Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
contentCommand = "SELECT COUNT(DISTINCT activeapplications.IdMembership) as countIdTitle FROM alldate "
+ " INNER JOIN activeapplications ON activeapplications.Id = alldate.IdTitle "
+ " INNER JOIN membership ON membership.Id = activeapplications.IdMembership "
+ " WHERE IdTitle > 2 AND membership.AsOneApplication = 1 "
+ " AND " + SqlValidator.Validate_BETWEEN(ColumnNames.Date, dateFrom, dateTo).Replace("Date", "alldate.Date");
returnCount += Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "countIdTitle")[0]);
return(returnCount.ToString());
}
public static void ChangeNameNonActiveWindow(string idNonActiveWindow, string newName)
{
if (!ActiveApplication_db.CheckIfExistTitle(SqlValidator.Validate(newName)))
{
string contentCommand = "INSERT INTO activeapplications (Title, IdNameActivity) VALUES ( " + SqlValidator.Validate(newName) + " , 1 )";
DataBase.ExecuteNonQuery(contentCommand);
string newIDApplication = ActiveApplication_db.GetIdActivityByName(SqlValidator.Validate(newName));
new AllData_db().UpdateIDApplication(newIDApplication, idNonActiveWindow);
DeleteNonActiveWindow(idNonActiveWindow);
}
else
{
string newIDApplication = ActiveApplication_db.GetIdActivityByName(SqlValidator.Validate(newName));
new AllData_db().UpdateIDApplication(newIDApplication, idNonActiveWindow);
DeleteNonActiveWindow(idNonActiveWindow);
}
}
public void BuildCreateScript(BuildSqlDialect sqlDialect)
{
var builder = new StringBuilder();
using (var writer = new StringWriter(builder))
{
TimeoutScriptBuilder.BuildCreateScript(writer, sqlDialect);
}
var script = builder.ToString();
if (sqlDialect == BuildSqlDialect.MsSqlServer)
{
SqlValidator.Validate(script);
}
#if NET452
using (ApprovalResults.ForScenario(sqlDialect))
{
Approvals.Verify(script);
}
#endif
}
public SqlFetch Fetch(SqlFetchOption option, SqlExpression rowCount, params ISqlCursorFetchTarget[] target)
{
if (!rowCount.IsNullReference())
{
if (option != SqlFetchOption.Absolute && option != SqlFetchOption.Relative)
{
throw new ArgumentException(Strings.ExInvalidUsageOfTheRowCountArgument, "rowCount");
}
SqlValidator.EnsureIsArithmeticExpression(rowCount);
}
else if (option == SqlFetchOption.Absolute || option == SqlFetchOption.Relative)
{
throw new ArgumentException(Strings.ExInvalidUsageOfTheOrientationArgument, "option");
}
if (target != null)
{
for (int i = 0, l = target.Length; i < l; i++)
{
ArgumentValidator.EnsureArgumentNotNull(target[i], "target");
}
}
return(new SqlFetch(option, rowCount, this, target));
}
public int GetTimeForNumberActivity(List <int> numbers, string startDate = "", string endDate = "", bool ifExcept = false)
{
string contentCommand = "SELECT SUM(ActivityTime) as sumTimeActivity FROM alldate INNER JOIN " +
"activeapplications ON alldate.IdTitle = activeapplications.Id " +
"WHERE 1 = 1";
for (int i = 0; i < numbers.Count; i++)
{
contentCommand += " AND activeapplications.IdNameActivity " + (ifExcept ? "!" : "") + "= " + numbers[i];
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
contentCommand += " AND " + SqlValidator.Validate(startDate) + " < alldate.Date AND alldate.Date < " + SqlValidator.Validate(endDate);
}
else if (!string.IsNullOrEmpty(startDate))
{
contentCommand += " AND alldate.Date = " + SqlValidator.Validate(startDate);
}
int returnValue = 0;
Int32.TryParse(DataBase.GetListStringFromExecuteReader(contentCommand, "sumTimeActivity")[0], out returnValue);
return(returnValue);
}
public static bool AddGroupToApplications(List <int> idApplications, string idGroup)
{
string contentCommand = "UPDATE activeapplications SET IdMembership = " + idGroup + ", AutoGrouping = 1 WHERE Id " + SqlValidator.Validate_IN(idApplications);
return(DataBase.ExecuteNonQuery(contentCommand));
}
public static bool CheckIfExistName(string name)
{
string contentCommand = "SELECT COUNT(*) AS ifExistName FROM membership WHERE Title = " + SqlValidator.Validate(name);
if (Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "ifExistName")[0]) > 0)
{
return(true);
}
else
{
return(false);
}
}
private static bool CheckIfExistTable(string nameTable, SqlCommand command)
{
bool returnValue = true;
string stringCommand = @"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = " + SqlValidator.Validate(nameTable);
command = new SqlCommand(stringCommand, Connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
returnValue = false;
}
reader.Close();
return(returnValue);
}
public static int GetIDForNameActivity(string nameActivity)
{
string contentCommand = "SELECT Id FROM nameactivity WHERE NameActivity = " + SqlValidator.Validate(nameActivity);
return(Convert.ToInt32(DataBase.GetListStringFromExecuteReader(contentCommand, "Id")[0]));
}
public void Update(string title)
{
if (!string.IsNullOrEmpty(title) && !string.Equals(title, "-"))
{
int idTitle = Convert.ToInt32(ActiveApplication_db.GetIdActivityByName(SqlValidator.Validate(title)));
nameGroup.SetContent(ActiveApplication_db.GetNameGroupByIdTitle(idTitle));
nameActivity.SetContent(ActiveApplication_db.GetNameActivityByIdTitle(idTitle));
}
}
public static string CheckParameters(CommandParameters parameters)
{
string query = string.Empty;
if (parameters.ID.Any())
{
query += SqlValidator.AND + ColumnNames.ID + SqlValidator.Validate_IN(parameters.ID);
}
if (parameters.Name.Any())
{
query += SqlValidator.AND + ColumnNames.Name + SqlValidator.Validate_IN(parameters.Name);
}
if (!string.IsNullOrEmpty(parameters.ActivityTimeFrom) && !string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + SqlValidator.Validate_BETWEEN(ColumnNames.ActivityTime, parameters.ActivityTimeFrom, parameters.ActivityTimeTo);
}
if (!string.IsNullOrEmpty(parameters.ActivityTimeFrom) && string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + ColumnNames.ActivityTime + SqlValidator.FromValue + parameters.ActivityTimeFrom;
}
if (string.IsNullOrEmpty(parameters.ActivityTimeFrom) && !string.IsNullOrEmpty(parameters.ActivityTimeTo))
{
query += SqlValidator.AND + ColumnNames.ActivityTime + SqlValidator.ToValue + parameters.ActivityTimeTo;
}
if (!string.IsNullOrEmpty(parameters.StartDate) && !string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + SqlValidator.Validate_BETWEEN(ColumnNames.Date, parameters.StartDate, parameters.EndDate);
}
if (!string.IsNullOrEmpty(parameters.StartDate) && string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + ColumnNames.Date + SqlValidator.FromValue + SqlValidator.Validate(parameters.StartDate);
}
if (string.IsNullOrEmpty(parameters.StartDate) && !string.IsNullOrEmpty(parameters.EndDate))
{
query += SqlValidator.AND + ColumnNames.Date + SqlValidator.ToValue + SqlValidator.Validate(parameters.EndDate);
}
if (!string.IsNullOrEmpty(parameters.IfActive))
{
query += SqlValidator.AND + ColumnNames.IfActive + SqlValidator.ToValue + parameters.IfActive;
}
if (!string.IsNullOrEmpty(parameters.IfConfiguration))
{
query += SqlValidator.AND + ColumnNames.IfConfiguration + SqlValidator.ToValue + parameters.IfConfiguration;
}
if (!string.IsNullOrEmpty(parameters.IfActiveConfiguration))
{
query += SqlValidator.AND + ColumnNames.IfActiveConfiguration + SqlValidator.ToValue + parameters.IfActiveConfiguration;
}
if (parameters.IdMembership > 0)
{
query += SqlValidator.AND + ColumnNames.IdMembership + SqlValidator.ToValue + parameters.IdMembership;
}
if (parameters.IdMembership == -1)
{
query += SqlValidator.AND + ColumnNames.IdMembership + SqlValidator.ISNULL;
}
if (parameters.IdNameActivity > 0)
{
query += SqlValidator.AND + ColumnNames.IdNameActivity + SqlValidator.ToValue + (int)parameters.IdNameActivity;
}
return(query);
}
public static bool AddNewActivity(string nameActivity)
{
string contentCommand = "INSERT INTO nameactivity (NameActivity) VALUES (" + SqlValidator.Validate(nameActivity) + ")";
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Została dodana nowa aktywność " + SqlValidator.Validate(nameActivity));
return(true);
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się dodać nowej aktywności " + SqlValidator.Validate(nameActivity));
return(false);
}
}
public ICollection <string> IsSqlQueryValid(string sql)
{
return(SqlValidator.IsSqlQueryValid(sql));
}
public static bool DeleteActivity(string nameActivity)
{
string contentCommand = "DELETE FROM nameactivity WHERE NameActivity = " + SqlValidator.Validate(nameActivity);
if (DataBase.ExecuteNonQuery(contentCommand))
{
ApplicationLog.LogService.AddRaportInformation("Została usunięta aktywność " + SqlValidator.Validate(nameActivity));
return(true);
}
else
{
ApplicationLog.LogService.AddRaportWarning("Nie udało się usunąć aktywności " + SqlValidator.Validate(nameActivity));
return(false);
}
}
public string GetTimeActivityForDateAndIdActivity(string date, int idTitle)
{
string contentCommand = "SELECT ActivityTime from alldate WHERE Date = " + SqlValidator.Validate(date)
+ " AND IdTitle = " + idTitle;
string returnValue = string.Empty;
if (DataBase.GetListStringFromExecuteReader(contentCommand, "ActivityTime").Any())
{
return(DataBase.GetListStringFromExecuteReader(contentCommand, "ActivityTime")[0]);
}
else
{
return("0");
}
}
