private void RunSectionQuestion()
{
chkSectionQuestion.BackColor = SystemColors.MenuHighlight;
using (var mysqlProvider = new MySqlDataProvider(_connectionString))
{
string script, strScriptSqlite;
DataTable table;
var total = mysqlProvider.GetCount(ScriptMySqlHelper.TableSectionQuestionCount);
_skip = 0;
if (total > Take)
{
while (_skip < total)
{
script = string.Format(ScriptMySqlHelper.TableSectionQuestion, _skip, Take);
table = mysqlProvider.GetDataTable(script);
GenerateScriptHelper.SaveSqliteDb(ScriptSqliteHelper.TableSectionQuestion, txtSaveSQLiteFile.Text);
strScriptSqlite = ScriptSqliteHelper.InsertSectionQuestionSchedule + table.GetScript(ScriptSqliteHelper.InsertSectionQuestionValue, 24);
GenerateScriptHelper.SaveSqliteDb(strScriptSqlite, txtSaveSQLiteFile.Text);
_skip += Take;
}
}
else
{
script = string.Format(ScriptMySqlHelper.TableSectionQuestion, _skip, total);
table = mysqlProvider.GetDataTable(script);
GenerateScriptHelper.SaveSqliteDb(ScriptSqliteHelper.TableSectionQuestion, txtSaveSQLiteFile.Text);
strScriptSqlite = ScriptSqliteHelper.InsertSectionQuestionSchedule + table.GetScript(ScriptSqliteHelper.InsertSectionQuestionValue, 24);
GenerateScriptHelper.SaveSqliteDb(strScriptSqlite, txtSaveSQLiteFile.Text);
}
}
chkSectionQuestion.BackColor = SystemColors.Control;
}
/// <summary>
/// Get predicate "x => x.Address.RegionId operation value"
/// </summary>
/// <param name="fieldValue"></param>
/// <param name="operation"></param>
/// <returns></returns>
private Expression <Func <StatisticalUnit, bool> > GetRegionPredicate(object fieldValue, OperationEnum operation)
{
var regionIds = fieldValue;
if (operation == OperationEnum.Equal || operation == OperationEnum.NotEqual)
{
var provider = Configuration
.GetSection(nameof(ConnectionSettings))
.Get <ConnectionSettings>()
.ParseProvider();
IDbDataProvider dataProvider;
switch (provider)
{
case ConnectionProvider.SqlServer:
dataProvider = new MsSqlDbDataProvider();
break;
case ConnectionProvider.PostgreSql:
dataProvider = new PostgreSqlDbDataProvider();
break;
case ConnectionProvider.MySql:
dataProvider = new MySqlDataProvider();
break;
default: throw new Exception(Resources.Languages.Resource.ProviderIsNotSet);
}
regionIds = string.Join(",", dataProvider.GetRegionChildren(DbContext, fieldValue));
}
return(GetMultipleRegionsPredicate(regionIds, operation));
}
/// <summary>
/// Get predicate "x => x.ActivitiesUnits.Any(y => y.Activity.ActivityCategoryId == value)"
/// </summary>
/// <param name="fieldValue"></param>
/// <param name="operation"></param>
/// <returns></returns>
private Expression <Func <StatisticalUnit, bool> > GetActivityPredicate(object fieldValue, OperationEnum operation)
{
var subCategoriesIds = fieldValue;
if (operation == OperationEnum.Equal || operation == OperationEnum.NotEqual || operation == OperationEnum.InList)
{
var provider = Configuration
.GetSection(nameof(ConnectionSettings))
.Get <ConnectionSettings>()
.ParseProvider();
IDbDataProvider dataProvider;
switch (provider)
{
case ConnectionProvider.SqlServer:
dataProvider = new MsSqlDbDataProvider();
break;
case ConnectionProvider.PostgreSql:
dataProvider = new PostgreSqlDbDataProvider();
break;
case ConnectionProvider.MySql:
dataProvider = new MySqlDataProvider();
break;
default: throw new Exception(Resources.Languages.Resource.ProviderIsNotSet);
}
subCategoriesIds = string.Join(",", operation == OperationEnum.InList
? dataProvider.GetActivityChildren(DbContext, null, fieldValue)
: dataProvider.GetActivityChildren(DbContext, fieldValue, null));
}
var outerParameter = Expression.Parameter(typeof(StatisticalUnit), "x");
var property = Expression.Property(outerParameter, nameof(StatisticalUnit.ActivitiesUnits));
var innerParameter = Expression.Parameter(typeof(ActivityStatisticalUnit), "y");
var categoryId = Expression
.Property(innerParameter, typeof(ActivityStatisticalUnit)
.GetProperty(nameof(ActivityStatisticalUnit.Activity)));
categoryId = Expression
.Property(categoryId, typeof(Activity)
.GetProperty(nameof(Activity.ActivityCategoryId)));
var value = GetConstantValue(subCategoriesIds, categoryId,
operation == OperationEnum.Equal
? OperationEnum.InList : operation == OperationEnum.NotEqual
? OperationEnum.NotInList : operation);
var innerExpression = GetExpressionForMultiselectFields(categoryId, value, operation);
var call = Expression.Call(typeof(Enumerable), "Any", new[] { typeof(ActivityStatisticalUnit) }, property,
Expression.Lambda <Func <ActivityStatisticalUnit, bool> >(innerExpression, innerParameter));
return(Expression.Lambda <Func <StatisticalUnit, bool> >(call, outerParameter));
}
public void InitailizeTest()
{
mockConnection = new Mock <MySqlDatabaseConnection>();
mockSqlCommand = new Mock <IDbCommand>();
mockDataReader = new Mock <IDataReader>();
mySqlDataProvider = new MySqlDataProvider(mockConnection.Object);
mockConnection.Setup(instance => instance.CreateCommand()).Returns(mockSqlCommand.Object);
}
public void Initialize(MySqlDataProvider provider)
{
_provider = provider;
ArrivalTimes = new List <string>();
List <string[]> times = provider.ReadArrivalTimeForStation(Id);
foreach (var item in times)
{
ArrivalTimes.Add(item[1]);
}
}
public void Initialize(MySqlDataProvider provider)
{
Stations = new Dictionary <int, Station>();
List <string[]> stations = provider.ReadStationsForLine(this.Id);
foreach (var item in stations)
{
Station station = new Station(this, item);
station.Initialize(provider);
Stations.Add(station.Order, station);
}
}
public void Initialize(MySqlDataProvider provider)
{
_provider = provider;
Lines = new List <Line>();
List <string[]> lines = provider.ReadAllDataFromTable("Line");
foreach (var item in lines)
{
Line line = new Line(item);
line.Initialize(provider);
Lines.Add(line);
}
}
static void Main(string[] args)
{
string connection = "server = localhost; uid = florin; pwd=!Password1; persistsecurityinfo = True; database = transport";
MySqlDataProvider provider = new MySqlDataProvider(connection);
Schedule schedule = new Schedule();
schedule.Initialize(provider);
schedule.Print();
Console.ReadKey();
// schedule.PrintNextArrivalTime("Yonge", Direction.North, DateTime.Now.ToString("HH:mm"));
schedule.PrintNextArrivalTimeDirect("Yonge", Direction.North, "8:17");
Console.ReadKey();
schedule.PrintNextArrivalTime("Yonge", Direction.North, "8:17");
Console.ReadKey();
schedule.PrintArrivalTimesForStation("Yonge");
Console.ReadKey();
}
public void Acc_CorrectSchemaLoad()
{
MySqlDataProvider prov = (MySqlDataProvider)DataService.Providers["Southwind"];
TableSchema.Table products = prov.GetTableSchema("Products", TableType.Table);
//Assert.IsTrue(products.GetColumn("IsDeleted").IsNullable == true); Not in MySql schema
Assert.IsTrue(products.GetColumn("Discontinued").IsNullable == false);
TableSchema.TableColumn col = products.GetColumn("Discontinued");
ICodeLanguage lang = new CSharpCodeLanguage();
string varTypeNonNull = Utility.GetVariableType(col.DataType, col.IsNullable, lang);
Assert.IsTrue(varTypeNonNull == "bool");
//Not in MySql Schema
//col = products.GetColumn("IsDeleted");
//string varTypeNullable = Utilities.Utility.GetVariableType(col.DataType, col.IsNullable, lang);
//Assert.IsTrue(varTypeNullable == "bool?");
}
private async Task <List <ReportTree> > GetReportsTreeByProvider(NSCRegDbContext context, string sqlWalletUser)
{
List <ReportTree> queryResult;
var provider = _configuration
.GetSection(nameof(ConnectionSettings))
.Get <ConnectionSettings>()
.ParseProvider();
try
{
IDbDataProvider dbDataProvider;
switch (provider)
{
case ConnectionProvider.SqlServer:
dbDataProvider = new MsSqlDbDataProvider();
break;
case ConnectionProvider.PostgreSql:
dbDataProvider = new PostgreSqlDbDataProvider();
break;
case ConnectionProvider.MySql:
dbDataProvider = new MySqlDataProvider();
break;
default: throw new Exception(Resource.ProviderIsNotSet);
}
queryResult = await dbDataProvider.GetReportsTree(_ctx, sqlWalletUser, _configuration);
return(queryResult);
}
catch (Exception e)
{
throw new Exception($"An error occured while trying get data of reports from database. Message: {e.Message}");
}
}
public void Register(ContainerBuilder builder, ITypeFinder typeFinder, AppConfig config)
{
IMySqlDataProvider dataProvider = new MySqlDataProvider();
ICacheManager cacheManager = new MemoryCacheManager();
MySqlDbContext dbContext = (MySqlDbContext)dataProvider.GetDbContext(
new ConnectionParams()
{
Host = config.Host,
Database = config.Database,
Username = config.DbUsername,
Password = config.DbPassword,
Port = config.DbPort
});
builder.RegisterInstance <MySqlDbContext>(dbContext).As <IMySqlDbContext>().SingleInstance();
builder.RegisterInstance <IMySqlDataProvider>(dataProvider).As <IMySqlDataProvider>().SingleInstance();
builder.RegisterInstance <ICacheManager>(cacheManager).As <ICacheManager>().SingleInstance();
builder.RegisterType <MySqlDbClient>().As <IMySqlDbClient>();
builder.RegisterType <ApplicationContext>().As <IWorkContext>();
builder.RegisterType <Vault>().As <IVault>();
builder.RegisterType <VaultSqlHelper>().As <IVaultData>();
cacheManager.Set(Keys.DatabaseKey, dbContext, 1080);
}
/// <summary>
/// Get connection string of EA database
/// </summary>
/// <param name="rep"></param>
/// <param name="provider"></param>
/// <returns></returns>
/// string dsnName = "DSN=MySqlEa;Trusted_Connection=Yes;";
// dsnName = "DSN=MySqlEa;";
public static string GetConnectionString(EA.Repository rep, out IDataProvider provider)
{
provider = null;
var(connectionString, dbType) = GetConnectionStringFromRepository(rep);
string dsnConnectionString;
// EAP file
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\hoData\Work.eap;"
switch (dbType)
{
case "JET":
provider = new AccessDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
if (connectionString.ToLower().EndsWith(".eap") || connectionString.ToLower().EndsWith(".eapx"))
{
return($"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={connectionString};");
}
break;
case @"SQLSVR":
provider = new SqlServerDataProvider("", SqlServerVersion.v2012);
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "MYSQL":
provider = new MySqlDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ACCESS2007":
provider = new AccessDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ASA":
provider = new SybaseDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "ORACLE":
provider = new OracleDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
case "POSTGRES":
provider = new PostgreSQLDataProvider();
dsnConnectionString = GetConnectionStringForDsn(connectionString);
if (dsnConnectionString != "")
{
return(dsnConnectionString);
}
return(FilterConnectionString(connectionString));
}
MessageBox.Show($"Database: {rep.RepositoryType()}\r\nConnectionString:{connectionString} ", "DataBase not supported, only Access (*.eap/*.eapx), SqlServer and MySQL");
return("");
}
public DataSourceBase()
{
provider = new MySqlDataProvider();
}
