public static IDatabase GetDatabase(string key, DataBaseType type)
{
var connectionString = System.Configuration.ConfigurationManager.AppSettings[key];
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentException("数据库连接字符串不能为空！");
}
IDbConnection cnn = null;
IDapperExtensionsConfiguration config = null;
switch (type)
{
case DataBaseType.Orcale:
config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new OracleDialect());
cnn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
break;
case DataBaseType.SqlServer:
config = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new SqlServerDialect());
cnn = new System.Data.SqlClient.SqlConnection(connectionString);
break;
default:
throw new NotImplementedException("尚未实现对该数据库的支持！");
}
var sqlGenerator = new SqlGeneratorImpl(config);
return(new Database(cnn, sqlGenerator));
}
private IDbConnection OpenConnection()
{
var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(this.settings.ConnectionString);
conn.Open();
return(conn);
}
public Machine GetMachineFromMes(string Name)
{
Machine mach = null;
string ConStr = Static.Secrets.OracleConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string str = string.Format("SELECT * FROM QMES_FO_MACHINE WHERE MACHINE_NR='{0}'", Name);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
mach = new Machine();
while (reader.Read())
{
mach.MesId = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_ID")]);
mach.MesNumber = Name;
mach.Name = reader[reader.GetOrdinal("MACHINE_NAME")].ToString();
mach.MesType = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_TYPE_ID")]);
}
}
return(mach);
}
int ManagedOracleClient()
{
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder connBuilder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.ConnectionTimeout = 300;
connBuilder.ConnectionLifeTime = 10;
connBuilder.MinPoolSize = 0;
int rows = 0;
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connBuilder.ConnectionString))
{
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
conn.Open();
using (Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
private async Task ReadApexItemsAsync(Func <ApexItemDto, CancellationToken, Task> onReadItemAsync,
CancellationToken cancellationToken = default(CancellationToken))
{
EnsureArg.IsNotNull(onReadItemAsync);
using (var con = new Oracle.ManagedDataAccess.Client.OracleConnection(_options.Value.ApexConnectionString))
{
var cmd = con.CreateCommand();
cmd.CommandText = "SELECT BTIR_ID, BTIR_CREATED_BY, BTIR_BUSS_CONTACT_EMAIL, BTIR_PROJECT_NAME, BTIR_PROJECT_DESC FROM OCI.BTIR_REQUEST";
con.Open();
var reader = cmd.ExecuteReader();
try
{
while (await reader.ReadAsync(cancellationToken))
{
await onReadItemAsync(new ApexItemDto()
{
ApexId = reader.GetInt32(0),
Requestor3and3 = reader.GetString(1),
BusinessContactEmail = reader.GetString(2),
ProjectName = reader.GetString(3),
ProjectDescription = reader.GetString(4)
}, cancellationToken);
}
}
finally
{
con.Close();
}
}
}
/// <summary>
/// 从Oracle数据库查询数据，返回类型为DateSet
/// </summary>
/// <param name="oraStr">Oracle查询字符串</param>
/// <returns></returns>
/*public static DataSet SelectByOracle(string oraStr)
* {
* //select info by condition
* SqlParameter[] para = new SqlParameter[1];
* oraStr = oraStr.Replace("'", "''");
* para[0] = new SqlParameter("@oraStr", oraStr);
*
* return DbAccess.DataAdapterByCmd(CommandType.StoredProcedure, "SelectByOracle", para);
*
* //2010年11月9日修改
* //OracleConnection oraconn = new OracleConnection(connOral);
* //try
* //{
* // if (oraconn.State != ConnectionState.Open)
* // oraconn.Open();
* // OracleDataAdapter da = new OracleDataAdapter(oraStr, oraconn);
* // DataSet ds = new DataSet();
* // da.Fill(ds);
* // return ds;
* //}
* //catch(Exception ex)
* //{
* // throw new Exception(ex.Message);
* //}
* //finally
* //{
* // oraconn.Close();
* //}
* }
*/
public static DataSet SelectByOracle(string oraStr)
{
//2010年11月9日修改
Oracle.ManagedDataAccess.Client.OracleConnection oraconn = new Oracle.ManagedDataAccess.Client.OracleConnection(connOral);
try
{
if (oraconn.State != ConnectionState.Open)
{
oraconn.Open();
}
Oracle.ManagedDataAccess.Client.OracleDataAdapter da = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(oraStr, oraconn);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
oraconn.Close();
}
}
/// <summary>
/// atualiza somente a situação atual da solicitação.
/// </summary>
/// <param name="codSituacaoAtual"></param>
/// <returns></returns>
public string AtualizarSituacao(int codSolicitacao, int codSituacaoAtual)
{
var connStr = ConfigurationManager.ConnectionStrings["Sapiens"].ToString();
using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connStr))
{
var cmdText = "UPDATE USU_TSOLCLI SET USU_CODSIT = " + codSituacaoAtual + " WHERE USU_CODSOL = " + codSolicitacao;
var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, conn);
if (conn.State == System.Data.ConnectionState.Closed)
{
try
{
conn.Open();
cmd.ExecuteNonQuery();
return "OK";
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
conn.Close();
}
}
return "";
}
}
public IEnumerable <INVLinesModel> GetInv(string invoice_num)
{
var data = new List <INVLinesModel>();
string sqlStatement = @"select invoice_num,line_number,amount,account_segment,description
from WF_AP_INV_LINES_INTERFACE_HIS where invoice_num=:inv_num order by line_number";
using (var cn = new Oracle.ManagedDataAccess.Client.OracleConnection(this.ConnectionString))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = sqlStatement;
cmd.Parameters.Add(new Oracle.ManagedDataAccess.Client.OracleParameter("@nv_num", invoice_num));
var dr = cmd.ExecuteReader();
while (dr.Read())
{
data.Add(new INVLinesModel
{
invoice_num = dr["invoice_num"].ToString(),
line_number = (dr["line_number"] == null) ? 0 : int.Parse(dr["line_number"].ToString()),
amount = int.Parse(dr["amount"].ToString()),
description = dr["description"].ToString(),
account_segment = dr["account_segment"].ToString()
});
}
}
return(data.Take(10));
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
public static IDbConnection GetConnection()
{
IDbConnection connection = null;
//获取配置进行转换
string stype = Common.Helper.ConfigManager.Configuration["DBConnectoin:DBType"];
//DefaultDatabase 根据这个配置项获取对应连接字符串
string strConn = Common.Helper.ConfigManager.Configuration["DBConnectoin:" + stype];
var dbType = GetDataBaseType(stype);
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
break;
case DatabaseType.Oracle:
connection = new Oracle.ManagedDataAccess.Client.OracleConnection(strConn);
//connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
}
return(connection);
}
public static void LogMessage(string msg)
{
Oracle.ManagedDataAccess.Client.OracleConnection db = new Oracle.ManagedDataAccess.Client.OracleConnection();
db.Dispose();
db = null;
Console.WriteLine(msg);
}
public DataTable OracleExecuteDataTable(string SqlStatment)
{
Oracle.ManagedDataAccess.Client.OracleConnection Cn = new Oracle.ManagedDataAccess.Client.OracleConnection(SQL_CONN_STRING);
try
{
if (Cn.State != ConnectionState.Open)
{
Cn.Open();
}
Oracle.ManagedDataAccess.Client.OracleDataAdapter adtp = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(SqlStatment, Cn);
DataSet Ds = new DataSet();
adtp.Fill(Ds);
Cn.Close();
return(Ds.Tables[0]);
}
catch (Exception)
{
Cn.Close();
return(null);
}
finally
{
Cn.Close();
}
}
/// <summary>
/// EF SQL 语句返回 dataTable
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable SqlQueryForDataTatable(this Database db,
string sql,
SqlParameter[] parameters)
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(db.Connection.ConnectionString);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, conn);
if (parameters.Length > 0)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
}
Oracle.ManagedDataAccess.Client.OracleDataAdapter adapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
return(table);
}
public void InitDB()
{
try
{
g_OraConnection = new Oracle.ManagedDataAccess.Client.OracleConnection();
g_OraConnection.ConnectionString = m_ConnectionString;
g_OraConnection.Open();
if (g_OraConnection.State == System.Data.ConnectionState.Open)
{
g_OraCommand = new Oracle.ManagedDataAccess.Client.OracleCommand();
g_OraCommand.Connection = g_OraConnection;
}
else
{
Console.WriteLine("InitDB :" + "DB Connect Failed");
//Logger.Logging("ERROR", "InitDB : " + "DB Connect Failed.", EWSLog.LOGTYPE.TEXT);
}
}
catch (Exception ex)
{
Console.WriteLine("ERROR :" + "InitDB " + ex.Message);
//Logger.Logging("ERROR", "InitDB : " + ex.Message, EWSLog.LOGTYPE.TEXT);
}
}
//bool TestMySQL(string server, string port, string database, string userID, string password, int connectionTimeout)
//{
//MySql.Data.MySqlClient.MySqlConnectionStringBuilder builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder();
//builder.Server = server;
// uint portMySQL = Convert.ToUInt32(port);
//builder.Port = portMySQL;
// builder.Database = database;
// uint connectionTimeoutMySQL = Convert.ToUInt32(connectionTimeout);
//builder.ConnectionTimeout = connectionTimeoutMySQL;
// if (!string.IsNullOrWhiteSpace(userID))
// {
// builder.UserID = userID;
// builder.Password = password;
// }
// else
// {
// builder.IntegratedSecurity = true;
// }
// using (var conn = new MySql.Data.MySqlClient.MySqlConnection(builder.ToString()))
// {
// return TestConnection(conn);
// }
//}
bool TestOracle(string server, string port, string database, string userID, string password, int connectionTimeout)
{
Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder builder = new Oracle.ManagedDataAccess.Client.OracleConnectionStringBuilder();
//If userID is set to "/", password is not needed
if (string.IsNullOrWhiteSpace(password) && (userID == "/"))
{
builder.UserID = userID;
}
if (!string.IsNullOrWhiteSpace(password) && (!string.IsNullOrWhiteSpace(userID)))
{
builder.UserID = userID;
builder.Password = password;
}
//If Port is filled in
if (!string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, port, database, userID, password);
}
//if port is not filled in, set default port to 1521
if (string.IsNullOrWhiteSpace(port))
{
builder.ConnectionString = string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User ID={3};Password={4};", server, 1521, database, userID, password);
}
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(builder.ConnectionString);
using (conn)
{
return(TestConnection(conn));
}
}
public Conexion2(String comm)
{
this._connString = "DATA SOURCE=localhost:1521/xe; USER ID=AVOT; PASSWORD=paso;";
this._conn = new OracleConnection(_connString);
this._conn.Open();
this._adapter = new OracleDataAdapter();
this._comm = new OracleCommand(comm, _conn);
}
public void BindByName()
{
var con = new Oracle.ManagedDataAccess.Client.OracleConnection();
var cmd = con.CreateCommand();
var wcon = new LoggingConnection(con);
var wcmd = wcon.CreateCommand();
Assert.False(cmd.BindByName);
Assert.True(wcmd.TryCast<Oracle.ManagedDataAccess.Client.OracleCommand>().BindByName);
}
private static IDbAccess CreateOracle(string connStr)
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connStr);
IDbAccess iDb = new OracleIDbAccess()
{
conn = conn,
ConnectionString = connStr,
DataBaseType = DataBaseType.ORACLE
};
return(iDb);
}
/// <summary>
/// 打开数据连接.
/// </summary>
/// <param name="conStr">数据库连接字符串</param>
public static void OpenConnection(string conStr)
{
try
{
ConnectOracle._connection = new Oracle.ManagedDataAccess.Client.OracleConnection(conStr);
ConnectOracle._connection.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static string GetMyVersion()
{
int a = Common.Constants.CONSTANTY.C_NEJAKA_VERZE;
var test = new Oracle.ManagedDataAccess.Client.OracleConnection();
//Debug.Assert(test == null);
var test2 = new NUnit.Framework.Constraints.AndOperator();
System.Reflection.Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();
System.Diagnostics.FileVersionInfo fvi = System.Diagnostics.FileVersionInfo.GetVersionInfo(assembly.Location);
return($"AdapterDB.OracleVer={fvi.FileVersion}{Environment.NewLine}Using EXTERNAL Oracle.ManagedDataAccess 19.9{Environment.NewLine}Using NUNIT and CommonId: {a}");
}
public static XVar Exec(dynamic strSQL)
{
Oracle.ManagedDataAccess.Client.OracleConnection connection = new Oracle.ManagedDataAccess.Client.OracleConnection();
connection.ConnectionString = DB.connectionString;
connection.Open();
Oracle.ManagedDataAccess.Client.OracleCommand dbCommand = new Oracle.ManagedDataAccess.Client.OracleCommand(strSQL, connection);
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.ExecuteNonQuery();
dbCommand.Dispose();
dbCommand = null;
connection.Close();
return(null);
}
private void CheckDbConn()
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(Common.Runtime.OracleConnStrLocal);
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
Common.Helper.LogWrite("Db success!");
}
else
{
Common.Helper.LogWrite("Db failure!");
}
conn.Clone();
}
public OracleManaged()
{
Console.WriteLine("Creating Oracle.ManagedDataAccess.Client.OracleConnection Object");
try
{
var obj = new Oracle.ManagedDataAccess.Client.OracleConnection();
Console.WriteLine("SUCCESS!");
}
catch (Exception ex)
{
Console.WriteLine("FAILED");
Console.WriteLine(ex.Message);
}
}
public IEnumerable <INVLinesModel> GetInv(string invoice_num)
{
string sqlStatement = @"select invoice_num,line_number,amount,account_segment,description
from WF_AP_INV_LINES_INTERFACE_HIS where invoice_num=:inv_num order by line_number";
using (var cn = new Oracle.ManagedDataAccess.Client.OracleConnection(this.ConnectionString))
{
var result = cn.Query <INVLinesModel>(sqlStatement, new
{
inv_num = invoice_num
});
return(result);
}
}
static void Main(string[] args) //2D Y0 X0
{
string dbServer = args[0];
string dbPort = args[1];
string dbSID = args[2];
string dbUsername = args[3];
string dbPassword = args[4];
try
{
float y0 = 0; // -1102200; //-478400;
float x0 = 0; // -478400; //-1102200;
if (args.Length > 2)
{
float.TryParse(args[7], out y0);
float.TryParse(args[6], out x0);
}
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbServer + ")(PORT=" + dbPort + "))(CONNECT_DATA=(SID=" + dbSID + ")));User Id=" + dbUsername + "; Password="******";";
var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
connection.Open();
var command = new Oracle.ManagedDataAccess.Client.OracleCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
switch (args[5].ToCharArray()[0])
{
case '2':
{
load2DParcels(y0, x0, command);
break;
}
case '3':
{
load3DParcels(y0, x0, command);
break;
}
default:
{
break;
}
}
connection.Close();
} catch (Exception e)
{
Console.WriteLine("Chyba v loaderu: " + e.Message);
}
}
protected override void ExecuteInternal(JobExecutionContext context)
{
var startTime = DateTime.UtcNow;
var logEntity = new SCHEDULERLOG {
STARTTIME = startTime
};
var strInfo = new StringBuilder();
string connStr = ConfigurationManager.AppSettings["mergeData"];
strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", "CusteelService",
"http://db.custeel.com/services/dataCenterMTOMVerifyService");
strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", "Oracle",
connStr);
var conn = new OracleConnection(connStr);
conn.Open();
var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
#region 执行数据同步程序
var ch = new CusteelData();
ch.SyncCusteelData(strInfo);
#endregion
var merge = new MergeData();
merge.ExecuteMetals(conn, tran);
tran.Commit();
var endTime = DateTime.UtcNow;
logEntity.ENDTIME = endTime;
logEntity.JobStatus = JobStatus.Success;
logEntity.RUNDETAIL = strInfo.ToString();
WriteLogEntity(logEntity);
}
catch (Exception exception)
{
tran.Rollback();
logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1);
logEntity.JobStatus = JobStatus.Fail;
logEntity.RUNDETAIL = strInfo + "\n" + exception;
WriteLogEntity(logEntity);
}
finally
{
conn.Close();
}
}
static void Main()
{
MySqlConnection con = new MySqlConnection();
Type t = con.GetType();
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection();
Type t1 = conn.GetType();
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
DevExpress.Skins.SkinManager.EnableFormSkins();
DevExpress.UserSkins.BonusSkins.Register();
Application.Run(new WinFormTest());
}
public static Boolean ConnectToOracle(DBEnvironment _environment)
{
try
{
var connectionString = GetOracleConnectionStringByEnvironment(_environment);
using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
{
connection.Open();
return(true);
}
}
catch (Exception)
{
throw;
}
}
private List <LgObject> GetTablesAndViews()
{
var lgObjects = new List <LgObject>();
#if ORACLE
using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionString))
{
connection.Open();
using (DataTable tables = connection.GetSchema("Tables"))
{
foreach (DataRow row in tables.Rows.Cast <DataRow>())
{
if (!string.Equals(((string)row["OWNER"]), DefaultSchema, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
var obj = new LgObject
{
IsView = false,
ObjectName = (string)row["TABLE_NAME"]
};
lgObjects.Add(obj);
}
}
using (DataTable views = connection.GetSchema("Views"))
{
foreach (DataRow row in views.Rows)
{
if (!string.Equals(((string)row["OWNER"]), DefaultSchema, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
var obj = new LgObject
{
IsView = true,
ObjectName = (string)row["VIEW_NAME"]
};
lgObjects.Add(obj);
}
}
}
#endif
return(lgObjects);
}
/// <summary>
/// A simple function that takes a string and does a ToUpper
/// </summary>
/// <param name="input"></param>
/// <param name="context"></param>
/// <returns></returns>
public string GetStringFromDB(ILambdaContext context)
{
Oracle.ManagedDataAccess.Client.OracleConnection _con = new Oracle.ManagedDataAccess.Client.OracleConnection();
string connectionString = "";
connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=lattuceorcl.cdbrh6uczknc.eu-west-1.rds.amazonaws.com)(Port=1521))(CONNECT_DATA=(SID=orcl)));User Id=davidken;Password=DefaultPwd;Persist Security Info=True;";
_con.ConnectionString = connectionString;
// _con.Open();
// Oracle.ManagedDataAccess.Client.OracleCommand cmd = _con.CreateCommand();
// cmd.CommandText = "SELECT test_column FROM test_table";
// string val = cmd.ExecuteScalar().ToString();
//return val;
return("Hello World");
}
public static Lpp.Dns.DataMart.Model.PCORIQueryBuilder.DataContext CreatePCORIDataContext(Model.Settings.SQLProvider dbProvider, string connectionString, bool logToConsole = true)
{
string defaultSchema = "";
System.Data.Common.DbConnection connection;
if (dbProvider == Settings.SQLProvider.SQLServer)
{
connection = new System.Data.SqlClient.SqlConnection(connectionString);
}
else if (dbProvider == Settings.SQLProvider.ODBC)
{
connection = new System.Data.Odbc.OdbcConnection(connectionString);
}
else if (dbProvider == Settings.SQLProvider.PostgreSQL)
{
defaultSchema = "dbo";
connection = new Npgsql.NpgsqlConnection(connectionString);
}
else if (dbProvider == Settings.SQLProvider.MySQL)
{
defaultSchema = "dbo";
connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
}
else if (dbProvider == Settings.SQLProvider.Oracle)
{
defaultSchema = "C##PCORNETUSER";
connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
}
else
{
throw new NotSupportedException("The specified SQLProvider of " + dbProvider + " is not supported.");
}
var db = new Lpp.Dns.DataMart.Model.PCORIQueryBuilder.DataContext(connection, defaultSchema);
if (logToConsole)
{
db.Database.Log = (s) =>
{
Console.WriteLine(s);
};
}
return(db);
}
///<summary>
///获取数据库连接
///</summary>
///<return>IDbConnection</return>
public IDbConnection CreateConnection()
{
IDbConnection connection = null;;
//获取配置进行转换
//var type = JsonConfigurationHelper.GetAppSettingSingle("ComponentDbType");
//IServiceProvider serviceProvider = new ServiceCollection().BuildServiceProvider();
//var config = serviceProvider.GetService<IConfiguration>();
var type = JsonConfigurationHelper.GetAppSettingSingle("ComponentDbType");
var dbType = GetDataBaseType(type);
//DefaultDatabase 根据这个配置项获取对应连接字符串
var database = JsonConfigurationHelper.GetAppSettingSingle("DefaultDatabase");
//var database = JsonConfigurationHelper.GetAppSettingSingle("DefaultDatabase",Config);
if (string.IsNullOrEmpty(database))
{
database = "mysql";//默认配置
}
//var jsonConfigurationHelper = JsonConfigurationHelper.GetAppSettings<ConnectionStrings>("ConnectionStrings");
var strConn = JsonConfigurationHelper.GetAppSettingSingle("ConnectionStrings:" + database);
//var strConn = JsonConfigurationHelper.GetAppSettingSingle("ConnectionStrings:" + database,Config);
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
break;
case DatabaseType.Oracle:
connection = new Oracle.ManagedDataAccess.Client.OracleConnection(strConn);
// connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
default:
connection = null;
break;
}
return(connection);
}
///<summary>
///获取数据库连接
///</summary>
///<return>IDbConnection</return>
public IDbConnection CreateConnection()
{
IDbConnection connection = null;
//获取配置进行转换
var type = ConnectionStrings.ComponentDbType;
var dbType = GetDataBaseType(type);
//DefaultDatabase 根据这个配置项获取对应连接字符串
var database = ConnectionStrings.DefaultDatabase;
if (string.IsNullOrEmpty(database))
{
database = "mysql";//默认配置
}
string strConn = ConnectionStrings.ConnectionString;
if (string.IsNullOrWhiteSpace(strConn))
{
throw new Exception("db connection string is null");
}
switch (dbType)
{
case DatabaseType.SqlServer:
connection = new System.Data.SqlClient.SqlConnection(strConn);
break;
case DatabaseType.MySql:
connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
break;
case DatabaseType.Oracle:
connection = new Oracle.ManagedDataAccess.Client.OracleConnection(strConn);
// connection = new System.Data.OracleClient.OracleConnection(strConn);
break;
default:
throw new Exception("db type is undefinded");
}
return(connection);
}
public void LoadFromMes(int[] Types = null)
{
string ConStr = Static.Secrets.OracleConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
string str = "";
Machines = new List <Machine>();
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
if (Types != null)
{
str = string.Format("SELECT * FROM QMES_FO_MACHINE WHERE MACHINE_TYPE_ID IN ({0})", string.Join(",", Types));
}
else
{
str = string.Format("SELECT * FROM QMES_FO_MACHINE");
}
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(str, Con);
var reader = Command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Machine mach = new Machine();
mach.MesId = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_ID")]);
mach.Name = reader[reader.GetOrdinal("MACHINE_NAME")].ToString();
mach.MesNumber = reader[reader.GetOrdinal("MACHINE_NR")].ToString();
mach.MesType = Convert.ToInt32(reader[reader.GetOrdinal("MACHINE_TYPE_ID")]);
Machines.Add(mach);
}
}
}
public String ObterDataUltimaSolicitacaoNoSICADV2(int codCliente)
{
Oracle.ManagedDataAccess.Client.OracleDataReader dr = null;
DateTime dataUltimaSolicitacao = new DateTime();
var connStr = ConfigurationManager.ConnectionStrings["Sapiens"].ToString();
using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connStr))
{
var cmdText = String.Format("select max(usu_dataini) as dataini from usu_tsolicit where usu_codcli = {0}",codCliente);
var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, conn);
if (conn.State == System.Data.ConnectionState.Closed)
{
try
{
conn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
dataUltimaSolicitacao = Convert.ToDateTime(dr["dataini"]);
}
return dataUltimaSolicitacao.ToShortDateString();
}
catch (Exception ex)
{
return "Não Disponível";
}
finally
{
conn.Close();
}
}
return null;
}
}
