private static void load2DParcels(float y0, float x0, Oracle.ManagedDataAccess.Client.OracleCommand command)
{
string result = "";
command.CommandText = "SELECT "
+ "b.bfsid, "
+ "c.sequence, "
+ "p.point.SDO_POINT.y, "
+ "p.point.SDO_POINT.x " +
"FROM tile_2D_SU suidx, la_spatialunit su, boundary b, corner c, la_point p " +
"WHERE suidx.roundy = " + (int)y0 + " AND suidx.roundx = " + (int)x0 + " AND " +
"suidx.suid = su.suid AND " +
"su.suid = b.suid AND " +
"b.bfsid = c.boundaryid AND " +
"c.pid = p.pid " +
"GROUP BY b.bfsid, c.sequence, p.point.SDO_POINT.y, p.point.SDO_POINT.x " +
"ORDER BY b.bfsid, c.sequence";
Oracle.ManagedDataAccess.Client.OracleDataReader dataReader = command.ExecuteReader();
int j = 0;
while (dataReader.Read()) // && j++ < 10)
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
result += dataReader[i].ToString() + ";";
}
result += "n";
}
dataReader.Close();
Console.WriteLine(result);
}
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);
}
}
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);
}
}
}
private static void load3DParcels(float y0, float x0, Oracle.ManagedDataAccess.Client.OracleCommand command)
{
string result = "";
command.CommandText = "SELECT "
+ "su.suid, " //0
+ "su.cislo_par, " //1
+ "b.bfid, " //2
+ "b.direction, " //3
+ "c.sequence, " //4
+ "p.point.SDO_POINT.y, " //5
+ "p.point.SDO_POINT.x, " //6
+ "c.elevation " + //7
"FROM tile_3D_SU suidx, la_spatialunit su, boundary3D b, corner c, la_point p " +
"WHERE suidx.roundy = " + (int)y0 + " AND suidx.roundx = " + (int)x0 + " AND " +
"suidx.suid = su.suid AND " +
"su.suid = b.suid AND " +
"b.bfid = c.boundaryid AND " +
"c.pid = p.pid " +
"GROUP BY su.suid, su.cislo_par, b.bfid, b.direction, c.sequence, p.point.SDO_POINT.y, p.point.SDO_POINT.x, c.elevation " +
"ORDER BY su.suid, b.bfid, c.sequence";
//Oracle.ManagedDataAccess.Types.
//ListDictionaryInternal
Oracle.ManagedDataAccess.Client.OracleDataReader dataReader = command.ExecuteReader();
//STRUCT strukturaBodu = (oracle.sql.STRUCT)dataReader.getObject(3);
//JGeometry geometrieBodu = JGeometry.load(strukturaBodu);
int j = 0;
while (dataReader.Read()) // && j++ < 10)
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
result += dataReader[i].ToString() + ";";
}
result += "n";
}
dataReader.Close();
Console.WriteLine(result);
}
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();
}
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) => RealCommand.ExecuteReader(behavior);
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;
}
}
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;
}
}