private static void ConxionBaseDeDatos()
{
//Bases de datos soportadas
//https://documentation.devexpress.com/#CoreLibraries/CustomDocument2114
//Ejemplos de conexion para todas las bases de XPO
//https://www.devexpress.com/Support/Center/Question/Details/K18445
//Conexion a access, el provedor esta en el namspace DevExpress.Xpo.DB, alli estan los demas proveedores, como OracleConnectionProvider o MSSqlConnectionProvider
//estos proveedores son clases estaticas que tiene un metodo para crear la cadena de conexion, esta cadena tambien se puede crear a mano segun los ejemplos de el link
//https://www.devexpress.com/Support/Center/Question/Details/K18445 o con la clase estatica segun los ejemplos de este link
https: //documentation.devexpress.com/#CoreLibraries/CustomDocument3243
//TODO quitar el comentario para usar access o mysql
//Access
//string conn = AccessConnectionProvider.GetConnectionString(@"EjemplosXPO.mdb");
//MySQL
string conn = MySqlConnectionProvider.GetConnectionString("localhost", "root", "JoseManuel16", "EjemplosXPO");
//TIP # 3. Explicitly set the XpoDefault.DataLayer property in the entry point of your application.
//https://www.devexpress.com/Support/Center/Question/Details/A2944
XpoDefault.DataLayer = XpoDefault.GetDataLayer(conn, AutoCreateOption.DatabaseAndSchema);
}
public async Task <byte[]> ExecuteQuery()
{
try
{
byte[] Bytes = null;
Bytes = await Request.GetRawBodyBytesAsync();
var Query = RestApiDataStore.GetObjectsFromByteArray <string>(Bytes);
//string myConnString = "User Id=shopcontroller;Password=Scvs!2012;server=192.163.193.68;Database=scaccess;Persist Security Info=True;";
var connectionString = MySqlConnectionProvider.GetConnectionString("192.163.193.68", "shopcontroller", "Scvs!2012", "scaccess");
// Create the default DAL which is used by the default Session and newly created objects to access a data store.
var Dal = XpoDefault.GetDataLayer(connectionString, AutoCreateOption.DatabaseAndSchema);
Session session = new Session(Dal);
var Result = session.ExecuteQuery(Query);
return(RestApiDataStore.ToByteArray(Result));
}
catch (Exception ex)
{
var test = ex.Message;
throw;
}
}
static void Main()
{
#if EASYTEST
DevExpress.ExpressApp.Win.EasyTest.EasyTestRemotingRegistration.Register();
#endif
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
EditModelPermission.AlwaysGranted = System.Diagnostics.Debugger.IsAttached;
Tracing.LocalUserAppDataPath = Application.LocalUserAppDataPath;
Tracing.Initialize();
Apollo11WindowsFormsApplication winApplication = new Apollo11WindowsFormsApplication();
// Refer to the https://documentation.devexpress.com/eXpressAppFramework/CustomDocument112680.aspx help article for more details on how to provide a custom splash form.
//winApplication.SplashScreen = new DevExpress.ExpressApp.Win.Utils.DXSplashScreen("YourSplashImage.png");
SecurityAdapterHelper.Enable();
//mysql connecton string
winApplication.ConnectionString = MySqlConnectionProvider.GetConnectionString("", "", "", "");
// if (ConfigurationManager.ConnectionStrings["ConnectionString"] != null) {
// winApplication.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// }
if (System.Diagnostics.Debugger.IsAttached && winApplication.CheckCompatibilityType == CheckCompatibilityType.DatabaseSchema)
{
winApplication.DatabaseUpdateMode = DatabaseUpdateMode.UpdateDatabaseAlways;
}
try {
winApplication.Setup();
winApplication.Start();
}
catch (Exception e) {
winApplication.HandleException(e);
}
}
public IEnumerable <LogIn> GetLogInsWhichIdIsBetween(long begin, long end)
{
const string kExecute = @"
select id
,agente
,dtini
,dtfim
,filas
,1000 as tipo_id
,dtfim >= dtini as loggedout
from login
where id between @begin and @end
order by id asc
";
var mysql_connection_provider =
new MySqlConnectionProvider(
"Data Source=192.168.203.207;Initial Catalog=acaconb1;User ID=callflex;Password=$callflex$;");
var mysql_executor =
new MySqlQueryExecutor(mysql_connection_provider);
/*using (
* var conn =
* new MySqlConnection(
* "Data Source=192.168.203.207;Initial Catalog=acaconb1;User ID=callflex;Password=$callflex$;")
* )
* using (var builder = new CommandBuilder(conn)) {
* IDbCommand cmd =
* builder
* .AddParameter("@begin", begin)
* .AddParameter("@end", end)
* .SetText(kExecute)
* .Build();
* conn.Open();
* using (IDataReader reader = cmd.ExecuteReader()) {
* int[] ordinals = new int[] {
* reader.GetOrdinal("id"), reader.GetOrdinal("tipo_id"),
* reader.GetOrdinal("dtini"), reader.GetOrdinal("agente"),
* reader.GetOrdinal("dtfim"), reader.GetOrdinal("filas"),
* reader.GetOrdinal("loggedout")
* };
* //return Map(reader);
* }
* conn.Close();
* }*/
using (IQueryMapper <LogIn> mapper =
mysql_executor
.ExecuteQuery(kExecute,
LogInDto,
builder =>
builder
.AddParameter("@begin", begin)
.AddParameter("@end", end))) {
IEnumerable <LogIn> logins = mapper.Map(false);
//IEnumerable<LogIn> logins = Map(((QueryMapper<LogIn>) mapper).Reader);
return(logins);
}
//return Enumerable.Empty<LogIn>();
}
private static IDataStore GetConnectionProvider()
{
IDisposable[] disposableList;
return(MySqlConnectionProvider.CreateProviderFromString(
"Server=127.0.0.1;User ID=root;Password=Maria.2897;Database=Library;Persist Security Info= true;Charset=utf8",
AutoCreateOption.DatabaseAndSchema,
out disposableList
));
}
private static void Main()
{
var providers = new AbstractConnectionProvider[2];
providers[0] = new SqlConnectionProvider();
providers[1] = new MySqlConnectionProvider();
foreach (var provider in providers)
{
var connection = provider.GetConnection();
Console.WriteLine($"Created a {connection.GetType().Name}.");
}
}
private void BootTaskConnectMySql()
{
StandardOut.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_PREPARE"));
MySqlConnectionProvider = new MySqlConnectionProvider
{
Host = Config.ValueAsString("/config/mysql/host"),
Port = Config.ValueAsUshort("/config/mysql/port", 3306),
User = Config.ValueAsString("/config/mysql/user"),
Password = Config.ValueAsString("/config/mysql/password"),
Database = Config.ValueAsString("/config/mysql/database")
};
StandardOut.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_READY"));
}
public void GenerateJsonFiles(string path, AutopartsDbContext db)
{
// var db = new AutopartsDbContext();
var autoParts = db.AutoParts.ToList();
var mysqlParser = new MySqlConnectionProvider();
foreach (var item in autoParts)
{
mysqlParser.AddContent(item);
var jsonObj = JsonConvert.SerializeObject(item, Formatting.Indented);
var adress = path + item.Id + ".json";
File.WriteAllText(adress, jsonObj.ToString());
}
Console.WriteLine("JSON file in Reports/JSON/ created.");
}
public void GenerateJsonFiles(string path, AutopartsDbContext db)
{
// var db = new AutopartsDbContext();
var autoParts = db.AutoParts.ToList();
var mysqlParser = new MySqlConnectionProvider();
foreach (var item in autoParts)
{
mysqlParser.AddContent(item);
var jsonObj = JsonConvert.SerializeObject(item, Formatting.Indented);
var adress = path + item.Id + ".json";
File.WriteAllText(adress, jsonObj.ToString());
}
Console.WriteLine("JSON file in Reports/JSON/ created.");
}
private bool BootTaskConnectMySql()
{
ConsoleManager.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_PREPARE"));
MySqlConnectionProvider = new MySqlConnectionProvider
{
Host = Config.GetValue("mysql:host", "localhost").Value,
Port = Config.GetValue <ushort>("mysql:port", 3306).Value,
User = Config.GetValue("mysql:username", "ihi").Value,
Password = Config.GetValue("mysql:password", "").Value,
Database = Config.GetValue("mysql:database", "ihi").Value,
MinimumPoolSize = Config.GetValue <uint>("mysql:minpoolsize", 1).Value,
MaximumPoolSize = Config.GetValue <uint>("mysql:maxpoolsize", 5).Value
};
MySqlConnectionProvider.HelperGetAction <int>("SELECT 1", null); // Testing the connection to the MySQL server
ConsoleManager.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_READY"));
return(true);
}
public void WithoutCredentials()
{
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=xxxx;Password=xxxx";
var options = new ConnectionProviderOptions
{
ConnectionString = connectionString,
Username = "",
Password = ""
};
var mock = new Mock <IOptions <ConnectionProviderOptions> >();
mock.Setup(x => x.Value).Returns(options);
var connectionProvider = new MySqlConnectionProvider(mock.Object, _encryptionHelper);
var connection = connectionProvider.Create();
// connection string should be unchanged
Assert.Equal(connection.ConnectionString, connectionString);
}
public void WithEncryptedCredentials()
{
var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=xxxx;Password=xxxx";
var options = new ConnectionProviderOptions
{
ConnectionString = connectionString,
Username = "******",
Password = "******"
};
var mock = new Mock <IOptions <ConnectionProviderOptions> >();
mock.Setup(x => x.Value).Returns(options);
var connectionProvider = new MySqlConnectionProvider(mock.Object, _encryptionHelper);
var connection = connectionProvider.Create();
var expected = "Server=myServerAddress;Database=myDataBase;User Id=testUsername;Password=testPassword";
// connection string should have username/password substituded in
Assert.Equal(connection.ConnectionString, expected);
}
/// <summary>
/// 尝试根据配置文件中在appSettings节下指定的设置构造数据库连接字符串。
/// 若失败则尝试获取connectionStrings节下指定的第一个连接字符串。
/// </summary>
/// <returns>构造或获取到的数据库连接字符串</returns>
public static string GetConnectionString()
{
try
{
AppSettingsReader config = new AppSettingsReader();
string serverType, server, database, user, password;
serverType = ((string)(config.GetValue("ServerType", typeof(string))));
server = ((string)(config.GetValue("Server", typeof(string))));
database = ((string)(config.GetValue("Database", typeof(string))));
user = ((string)(config.GetValue("User", typeof(string))));
password = ((string)(config.GetValue("Password", typeof(string))));
switch (serverType.ToUpper())
{
case "ACCESS":
return(AccessConnectionProvider.GetConnectionString(database, user, password));
case "MSSQL":
return(MSSqlConnectionProvider.GetConnectionString(server, user, password, database));
case "MYSQL":
return(MySqlConnectionProvider.GetConnectionString(server, user, password, database));
case "ORACLE":
return(OracleConnectionProvider.GetConnectionString(server, user, password));
// ... generate connection strings for other providers, e.g. Firebird, etc.
default:
return(ConfigurationManager.ConnectionStrings[0].ToString());
}
}
catch
{
return(ConfigurationManager.ConnectionStrings[0].ToString());
}
}
public DaoUserRole(MySqlConnectionProvider provider)
{
ConnectionProvider = provider;
}
/// <summary>
/// Подключение к БД.
/// </summary>
private void Connect()
{
switch (DataBaseType)
{
case SQLType.Access:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
AccessConnectionProvider.GetConnectionString(DataBase, User, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
AccessConnectionProvider.GetConnectionString(DataBase),
AutoCreateOption);
}
break;
case SQLType.Access2007:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
AccessConnectionProvider.GetConnectionStringACE(DataBase, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
AccessConnectionProvider.GetConnectionStringACE(DataBase, ""),
AutoCreateOption);
}
break;
case SQLType.MSSql:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
MSSqlConnectionProvider.GetConnectionString(Server, User, Password, DataBase),
AutoCreateOption);
//string connctionString = string.Format(@"Data Source={0};Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=qwe+ASDFG", Server);
//XpoDefault.DataLayer = XpoDefault.GetDataLayer(connctionString, AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
MSSqlConnectionProvider.GetConnectionString(Server, DataBase),
AutoCreateOption);
}
break;
case SQLType.MSSqlCE:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
MSSqlCEConnectionProvider.GetConnectionString(DataBase, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
MSSqlCEConnectionProvider.GetConnectionString(DataBase),
AutoCreateOption);
}
break;
case SQLType.MySQL:
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
MySqlConnectionProvider.GetConnectionString(Server, "", "", DataBase),
AutoCreateOption);
break;
case SQLType.Oracle:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
OracleConnectionProvider.GetConnectionString(Server, User, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
OracleConnectionProvider.GetConnectionString(Server, "", ""),
AutoCreateOption);
}
break;
case SQLType.Firebird:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
FirebirdConnectionProvider.GetConnectionString(Server, User, Password, DataBase),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
FirebirdConnectionProvider.GetConnectionString(Server, "", "", DataBase),
AutoCreateOption);
}
break;
case SQLType.PostgreSql:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
PostgreSqlConnectionProvider.GetConnectionString(Server, User, Password, DataBase),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
PostgreSqlConnectionProvider.GetConnectionString(Server, "", "", DataBase),
AutoCreateOption);
}
break;
case SQLType.SQLite:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
SQLiteConnectionProvider.GetConnectionString(DataBase, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
SQLiteConnectionProvider.GetConnectionString(DataBase),
AutoCreateOption);
}
break;
case SQLType.VistaDB:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
VistaDBConnectionProvider.GetConnectionString(DataBase, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
VistaDBConnectionProvider.GetConnectionString(DataBase),
AutoCreateOption);
}
break;
case SQLType.PervasiveSql:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
PervasiveSqlConnectionProvider.GetConnectionString(Server, User, Password, DataBase),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
PervasiveSqlConnectionProvider.GetConnectionString(Server, "", "", DataBase),
AutoCreateOption);
}
break;
case SQLType.DB2:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
DB2ConnectionProvider.GetConnectionString(Server, DataBase, User, Password),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
DB2ConnectionProvider.GetConnectionString(Server, DataBase, "", ""),
AutoCreateOption);
}
break;
case SQLType.NexusDB:
if (PasswordNeed)
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
NexusDBConnectionProvider.GetConnectionString(Server, User, Password, DataBase),
AutoCreateOption);
}
else
{
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
NexusDBConnectionProvider.GetConnectionString(Server, "", "", DataBase),
AutoCreateOption);
}
break;
default:
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
AccessConnectionProvider.GetConnectionString(DataBase),
AutoCreateOption.DatabaseAndSchema);
break;
}
}
private void BootTaskConnectMySql()
{
StandardOut.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_PREPARE"));
MySqlConnectionProvider = new MySqlConnectionProvider
{
Host = Config.ValueAsString("/config/mysql/host"),
Port = Config.ValueAsUshort("/config/mysql/port", 3306),
User = Config.ValueAsString("/config/mysql/user"),
Password = Config.ValueAsString("/config/mysql/password"),
Database = Config.ValueAsString("/config/mysql/database")
};
StandardOut.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_READY"));
}
public DaoExternNews(MySqlConnectionProvider provider)
{
ConnectionProvider = provider;
}
public PermissionService(MySqlConnectionProvider connectionProvider)
{
_connectionProvider = connectionProvider;
}
public DaoUserInfo(MySqlConnectionProvider provider)
{
ConnectionProvider = provider;
}
public DaoConfigDictionary(MySqlConnectionProvider provider)
{
ConnectionProvider = provider;
}
public static int Main(string[] args)
{
Log.Logger = new LoggerConfiguration()
.MinimumLevel.Debug()
.MinimumLevel.Override("Microsoft", LogEventLevel.Warning)
.MinimumLevel.Override("Microsoft.Hosting.Lifetime", LogEventLevel.Information)
.MinimumLevel.Override("System", LogEventLevel.Warning)
.MinimumLevel.Override("Microsoft.AspNetCore.Authentication", LogEventLevel.Information)
.Enrich.FromLogContext()
.WriteTo.File(
@"C:\logs\licensing.xenial.io\admin.licensing.xenial.io.log",
fileSizeLimitBytes: 1_000_000,
rollOnFileSizeLimit: true,
shared: true,
flushToDiskInterval: TimeSpan.FromSeconds(1))
.WriteTo.Console(outputTemplate: "[{Timestamp:HH:mm:ss} {Level}] {SourceContext}{NewLine}{Message:lj}{NewLine}{Exception}{NewLine}", theme: AnsiConsoleTheme.Code)
.CreateLogger();
try
{
Log.Information("Starting host...");
SQLiteConnectionProvider.Register();
MySqlConnectionProvider.Register();
if (ContainsArgument(args, "help") || ContainsArgument(args, "h"))
{
Console.WriteLine("Updates the database when its version does not match the application's version.");
Console.WriteLine();
Console.WriteLine($" {Assembly.GetExecutingAssembly().GetName().Name}.exe --updateDatabase [--forceUpdate --silent]");
Console.WriteLine();
Console.WriteLine("--forceUpdate - Marks that the database must be updated whether its version matches the application's version or not.");
Console.WriteLine("--silent - Marks that database update proceeds automatically and does not require any interaction with the user.");
Console.WriteLine();
Console.WriteLine($"Exit codes: 0 - {DBUpdater.StatusUpdateCompleted}");
Console.WriteLine($" 1 - {DBUpdater.StatusUpdateError}");
Console.WriteLine($" 2 - {DBUpdater.StatusUpdateNotNeeded}");
}
else
{
DevExpress.ExpressApp.FrameworkSettings.DefaultSettingsCompatibilityMode = DevExpress.ExpressApp.FrameworkSettingsCompatibilityMode.Latest;
var host = CreateHostBuilder(args).Build();
if (ContainsArgument(args, "updateDatabase"))
{
using var serviceScope = host.Services.CreateScope();
return(serviceScope.ServiceProvider.GetRequiredService <IDBUpdater>().Update(ContainsArgument(args, "forceUpdate"), ContainsArgument(args, "silent")));
}
else
{
Log.Information("Host run...");
host.Run();
}
}
return(0);
}
catch (Exception ex)
{
Log.Fatal(ex, "Host terminated unexpectedly.");
return(1);
}
finally
{
Log.CloseAndFlush();
}
}
static FeatureCenterBlazorApplication()
{
SQLiteConnectionProvider.Register();
MySqlConnectionProvider.Register();
}
public AppInfoService(MySqlConnectionProvider connectionProvider, ICacheStore store)
{
_connectionProvider = connectionProvider;
_store = store;
}
public DaoRolePermission(MySqlConnectionProvider provider)
{
ConnectionProvider = provider;
}
private bool BootTaskConnectMySql()
{
ConsoleManager.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_PREPARE"));
MySqlConnectionProvider = new MySqlConnectionProvider
{
Host = Config.GetValue("mysql:host", "localhost").Value,
Port = Config.GetValue<ushort>("mysql:port", 3306).Value,
User = Config.GetValue("mysql:username", "ihi").Value,
Password = Config.GetValue("mysql:password", "").Value,
Database = Config.GetValue("mysql:database", "ihi").Value,
MinimumPoolSize = Config.GetValue<uint>("mysql:minpoolsize", 1).Value,
MaximumPoolSize = Config.GetValue<uint>("mysql:maxpoolsize", 5).Value
};
MySqlConnectionProvider.HelperGetAction<int>("SELECT 1", null); // Testing the connection to the MySQL server
ConsoleManager.Notice("MySQL", StringLocale.GetString("CORE:BOOT_MYSQL_READY"));
return true;
}
