internal Oracle.ManagedDataAccess.Client.OracleParameter[] GetOracleParameters(Oracle.ManagedDataAccess.Client.OracleConnection conn)
{
var result = new List <Oracle.ManagedDataAccess.Client.OracleParameter>();
foreach (var p in this)
{
if (p.Type == typeof(Guid[]) || p.Type == typeof(string[]))
{
Oracle.ManagedDataAccess.Types.OracleClob clob = null;
if (p.Type == typeof(Guid[]) && p.Value != null)
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
clob = new Oracle.ManagedDataAccess.Types.OracleClob(conn);
var data = string.Join(",", ((Guid[])p.Value).Select(g => g.ToRaw()));
clob.Write(data.ToArray(), 0, data.Length);
}
else if (p.Type == typeof(string[]) && p.Value != null)
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
clob = new Oracle.ManagedDataAccess.Types.OracleClob(conn);
var data = string.Join(",", (string[])p.Value);
clob.Write(data.ToArray(), 0, data.Length);
}
result.Add(new Oracle.ManagedDataAccess.Client.OracleParameter()
{
OracleDbType = p.GetOracleType(),
Direction = p.IsOutput ? System.Data.ParameterDirection.Output : System.Data.ParameterDirection.Input,
ParameterName = p.Name,
Value = clob ?? (object)DBNull.Value
});
}
else
{
result.Add(new Oracle.ManagedDataAccess.Client.OracleParameter()
{
OracleDbType = p.GetOracleType(),
Direction = p.IsOutput ? System.Data.ParameterDirection.Output : System.Data.ParameterDirection.Input,
ParameterName = p.Name,
Value = CheckOracleValue(p.Value, p.GetOracleType()) ?? (object)DBNull.Value
});
}
}
return(result.ToArray());
}
/// <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();
}
}
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);
}
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));
}
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>
/// 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);
}
}
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>
/// 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 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();
}
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(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);
}
}
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);
}
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 static dynamic Query(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;
try {
dbCommand.Prepare();
string commandStr = strSQL.ToLower().Substring(0, 6);
string[] stopCommandList = { "insert", "update", "delete", "create", "drop", "rename", "alter" };
if (stopCommandList.Any(x => commandStr.Substring(0, x.Length) == x))
{
dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
return(null);
}
else
{
RunnerDBReader rdr = dbCommand.ExecuteReader();
rdr.Connection = dbCommand.Connection;
return(new QueryResult(dbCommand.Connection, rdr));
}
} catch (Exception e) {
if (dbCommand != null)
{
dbCommand.Connection.Close();
}
throw e;
return(null);
}
dbCommand.Dispose();
dbCommand = null;
connection.Close();
}
public static System.Data.Common.DbConnection OpenConnection(IDictionary <string, object> settings, log4net.ILog logger, bool openConnection = true)
{
string connectionString = BuildConnectionString(settings, logger);
System.Data.Common.DbConnection conn = null;
var dataProvider = (Model.Settings.SQLProvider)Enum.Parse(typeof(Model.Settings.SQLProvider), settings.GetAsString("DataProvider", ""), true);
switch (dataProvider)
{
case SQLProvider.ODBC:
conn = new System.Data.Odbc.OdbcConnection(connectionString);
break;
case SQLProvider.SQLServer:
conn = new System.Data.SqlClient.SqlConnection(connectionString);
break;
case SQLProvider.PostgreSQL:
conn = new Npgsql.NpgsqlConnection(connectionString);
break;
case SQLProvider.Oracle:
conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);
break;
default:
throw new NotSupportedException("Only ODBC, SqlServer, PostgreSQL, and MySQL currently supported.");
}
if (openConnection)
{
conn.Open();
}
return(conn);
}
internal static void GetData(int num, bool readClob, bool readBlob)
{
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
//{
// DataTable table = new DataTable();
// SqlDataAdapter adp = new SqlDataAdapter(string.Format("select top {0} * from TEMP_TestTKK0719-- order by newid()", num), conn);
// adp.Fill(table);
//}
switch (ConfigurationManager.AppSettings["dbtype"].ToUpper().Trim())
{
case "SYSTEM.DATA.ORACLECLIENT":
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
case "ODP.NET":
using (IDbConnection conn = ODPClientFactory.CreateConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
IDbCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
case "ODAC":
using (ManagedOralceConnection conn = new ManagedOralceConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
ManagedOracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719 where rownum <= {0}", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (ManagedOracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
default:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from sys.tables t where t.name = 'TEMP_TestTKK0719'");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar(50) NOT NULL,
name varchar(100) NULL,
age int NULL,
birthday datetime NULL,
salary decimal(10, 2) NULL,
summary nvarchar(1000) NULL,
remark varchar(max) NULL,
extends varbinary(max) NULL,
created datetime,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("select top {0} code, name, age, birthday, salary, summary, created {1} {2} from TEMP_TestTKK0719", num, (readClob ? ", remark" : string.Empty), (readBlob ? ", extends" : string.Empty));
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
}
}
conn.Close();
}
break;
}
}
static void Main(string[] args)
{
//Devart.Data.Oracle.OracleMonitor monitor
// = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };
//--------------------------------------------------------------
// You use the capability for configuring the behavior of the EF-provider:
//Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig devartConfig =
// Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
// Now, you switch off schema name generation while generating
// DDL scripts and DML:
//devartConfig.Workarounds.IgnoreSchemaName = true;
//devartConfig.Workarounds.DisableQuoting = true;
//devartConfig.CodeFirstOptions.UseDateTimeAsDate = true;
//devartConfig.CodeFirstOptions.UseNonLobStrings = true;
//devartConfig.CodeFirstOptions.UseNonUnicodeStrings = true;
//devartConfig.CodeFirstOptions.TruncateLongDefaultNames = true;
//--------------------------------------------------------------
/*--------------------------------------------------------------
* You can set up a connection string for DbContext in different ways.
* It can be placed into the app.config (web.config) file.
* The connection string name must be identical to the DbContext descendant name.
*
* <add name="MyDbContext" connectionString="Data Source=ora1020;
* User Id=test;Password=test;" providerName="Devart.Data.Oracle" />
*
* After that, you create a context instance, while a connection string is
* enabled automatically:
* MyDbContext context = new MyDbContext();
* ---------------------------------------------------------------*/
/*--------------------------------------------------------------
* And now it is possible to create an instance of the provider-specific connection
* and send it to the context constructor, like we did in this application.
* That allows us to use the StateChange connection event to change the Oracle
* current schema on its occurrence. Thus, we can connect as one user and
* work on a schema owned by another user.
* ---------------------------------------------------------------*/
//DbConnection con = new Devart.Data.Oracle.OracleConnection(
// "user id=abp1;password=123;server=orcl;persist security info=True");
//DbConnection con = new Devart.Data.Oracle.OracleConnection(
// "User Id=abp1;Password=123;Data Source=orcl");
DbConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) ;Persist Security Info=True;User ID=abp1;Password=123;");
con.Open();
//con.StateChange += new StateChangeEventHandler(Connection_StateChange);
/*---------------------------------------------------------------*/
/*--------------------------------------------------------------
* You can choose one of database initialization
* strategies or turn off initialization:
* --------------------------------------------------------------*/
//System.Data.Entity.Database.SetInitializer
// <MyDbContext>(new MyDbContextDropCreateDatabaseAlways());
//System.Data.Entity.Database.SetInitializer
// <MyOracleContext>(new MyDbContextCreateDatabaseIfNotExists());
//System.Data.Entity.Database.SetInitializer
// <MyDbContext>(new MyDbContextDropCreateDatabaseIfModelChanges());
//System.Data.Entity.Database.SetInitializer<MyOracleContext>(null);
System.Data.Entity.Database.SetInitializer <MyDbContext>(new MigrateDatabaseToLatestVersion <MyDbContext, Configuration>());
//--------------------------------------------------------------
/*--------------------------------------------------------------
* Let's create MyDbContext and execute a database query.
* Depending on selected database initialization strategy,
* database tables can be deleted/added, and filled with source data.
* ---------------------------------------------------------------*/
using (MyDbContext context = new MyDbContext(con))
{
var query = context.Products.ToList();
//var query = context.Products.Include("Category")
// .Where(p => p.Price > 20.0)
// .ToList();
foreach (var product in query)
{
Console.WriteLine("{0,-10} | {1,-50} | {2}",
product.ProductID, product.ProductName, product.Category.CategoryName);
}
Console.ReadKey();
}
}
internal static void Insert(int num, bool writeClob, bool writeBlob)
{
Stopwatch sw = new Stopwatch();
Random rnd = new Random();
for (int i = 0; i < num; i++)
{
switch (ConfigurationManager.AppSettings["dbtype"].ToUpper().Trim())
{
case "SYSTEM.DATA.ORACLECLIENT":
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
OracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.Add(":age", rnd.Next(20, 65));
cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
case "ODP.NET":
using (IDbConnection conn = ODPClientFactory.CreateConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
IDbCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
//cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
var p0 = cmd.CreateParameter();
p0.DbType = DbType.String;
p0.ParameterName = ":code";
p0.Value = Guid.NewGuid().ToString();
cmd.Parameters.Add(p0);
//cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
var p1 = cmd.CreateParameter();
p1.DbType = DbType.String;
p1.ParameterName = ":name";
p1.Value = StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty);
cmd.Parameters.Add(p1);
//cmd.Parameters.Add(":age", rnd.Next(20, 65));
var p2 = cmd.CreateParameter();
p2.DbType = DbType.Int32;
p2.ParameterName = ":age";
p2.Value = rnd.Next(20, 65);
cmd.Parameters.Add(p2);
//cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
var p3 = cmd.CreateParameter();
p3.DbType = DbType.DateTime;
p3.ParameterName = ":birthday";
p3.Value = DateTime.Now.AddYears(-1 * rnd.Next(20, 60));
cmd.Parameters.Add(p3);
//cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
var p4 = cmd.CreateParameter();
p4.DbType = DbType.Double;
p4.ParameterName = ":salary";
p4.Value = Math.Round(50000 * rnd.NextDouble(), 2);
cmd.Parameters.Add(p4);
//cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
var p5 = cmd.CreateParameter();
p5.DbType = DbType.String;
p5.ParameterName = ":summary";
p5.Value = new string('x', rnd.Next(10, 200));
cmd.Parameters.Add(p5);
if (writeClob)
{
//cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
var p7 = cmd.CreateParameter();
p7.ParameterName = ":remark";
p7.DbType = DbType.String;
p7.Value = bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"]));
cmd.Parameters.Add(p7);
}
if (writeBlob)
{
//cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
var p8 = cmd.CreateParameter();
p8.ParameterName = ":extends";
p8.DbType = DbType.Binary;
p8.Value = Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"])));
cmd.Parameters.Add(p8);
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
case "ODAC":
using (ManagedOralceConnection conn = new ManagedOralceConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
ManagedOracleCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from user_tables t where t.table_name = upper('TEMP_TestTKK0719')");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar2(50) NOT NULL,
name varchar2(100) NULL,
age int NULL,
birthday timestamp NULL,
salary decimal(10, 2) NULL,
summary varchar2(1000) NULL,
remark clob NULL,
extends blob NULL,
created timestamp,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(:code, :name, :age, :birthday, :salary, :summary, sysdate {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", :remark" : string.Empty), (writeBlob ? ", :extends" : string.Empty));
cmd.Parameters.Add(":code", Guid.NewGuid().ToString());
cmd.Parameters.Add(":name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.Add(":age", rnd.Next(20, 65));
cmd.Parameters.Add(":birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.Add(":salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.Add(":summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.Add(":remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.Add(":extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
default:
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString))
{
SqlCommand cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = string.Format("select 1 from sys.tables t where t.name = 'TEMP_TestTKK0719'");
object tableInited = cmd.ExecuteScalar();
if (tableInited == null || tableInited == DBNull.Value)
{
cmd.CommandText = @"CREATE TABLE TEMP_TestTKK0719(
code varchar(50) NOT NULL,
name varchar(100) NULL,
age int NULL,
birthday datetime NULL,
salary decimal(10, 2) NULL,
summary nvarchar(1000) NULL,
remark varchar(max) NULL,
extends varbinary(max) NULL,
created datetime,
CONSTRAINT PK__TEMP_TestTKK0719 PRIMARY KEY (code)
)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_Name on TEMP_TestTKK0719(name)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_age on TEMP_TestTKK0719(age)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create index idx_TEMP_TestTKK0719_birthday on TEMP_TestTKK0719(birthday)";
cmd.ExecuteNonQuery();
}
conn.Close();
cmd.CommandText = string.Format("insert into TEMP_TestTKK0719(code, name, age, birthday, salary, summary, created {0} {1}) values(@code, @name, @age, @birthday, @salary, @summary, getdate() {2} {3})", (writeClob ? ", remark" : string.Empty), (writeBlob ? ", extends" : string.Empty), (writeClob ? ", @remark" : string.Empty), (writeBlob ? ", @extends" : string.Empty));
cmd.Parameters.AddWithValue("@code", Guid.NewGuid().ToString());
cmd.Parameters.AddWithValue("@name", StringUtils.GetRandomString(rnd.Next(10, 50), false, true, true, false, string.Empty));
cmd.Parameters.AddWithValue("@age", rnd.Next(20, 65));
cmd.Parameters.AddWithValue("@birthday", DateTime.Now.AddYears(-1 * rnd.Next(20, 60)));
cmd.Parameters.AddWithValue("@salary", Math.Round(50000 * rnd.NextDouble(), 2));
cmd.Parameters.AddWithValue("@summary", new string('x', rnd.Next(10, 200)));
if (writeClob)
{
cmd.Parameters.AddWithValue("@remark", bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('y', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["clobMax"]))) : new string('y', int.Parse(ConfigurationManager.AppSettings["clobMax"])));
}
if (writeBlob)
{
cmd.Parameters.AddWithValue("@extends", Encoding.Default.GetBytes(bool.Parse(ConfigurationManager.AppSettings["randomLob"]) ? new string('z', rnd.Next(1, int.Parse(ConfigurationManager.AppSettings["blobMax"]))) : new string('z', int.Parse(ConfigurationManager.AppSettings["blobMax"]))));
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
break;
}
}
}
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;
}
}