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);
}
/// <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);
}
}
/// <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 "";
}
}
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 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 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);
}
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 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();
}
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 DbCommand CreateCommand(TypeDatabase typeOfDatabase, String cmdText, DbConnection connection)
{
DbCommand cmd = null;
switch (typeOfDatabase)
{
case TypeDatabase.Postgresql:
cmd = new Npgsql.NpgsqlCommand(cmdText, (Npgsql.NpgsqlConnection)connection);
break;
case TypeDatabase.Oracle:
cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(cmdText, (Oracle.ManagedDataAccess.Client.OracleConnection)connection);
break;
case TypeDatabase.Firebird:
cmd = new FirebirdSql.Data.FirebirdClient.FbCommand(cmdText, (FbConnection)connection);
break;
default:
throw new NotImplementedException();
}
return(cmd);
}
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;
}
}
}
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;
}
}
/// <summary>
/// Called from <see cref="OracleConnection.CreateCommand"/>.
/// </summary>
/// <param name="connection"></param>
internal OracleCommand(OracleConnection connection)
{
_connection = connection;
RealCommand = connection.RealConnection.CreateCommand();
}
/// <summary>
/// 刷新。
/// </summary>
/// <param name="id">归属的抢救室病例ID。</param>
public ActionResult Refresh(Guid rescueRoomInfoId)
{
var db = new EiSDbContext();
var target = db.RescueRoomInfos.Find(rescueRoomInfoId);
if (target == null)
{
return(HttpNotFound());
}
Oracle.ManagedDataAccess.Client.OracleConnection connection;
Oracle.ManagedDataAccess.Client.OracleCommand command;
Oracle.ManagedDataAccess.Client.OracleDataAdapter dataAdapter;
connection = new Oracle.ManagedDataAccess.Client.OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.9)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hzsydb)));User Id=pacsinterface;Password=pubpacs;");
command = new Oracle.ManagedDataAccess.Client.OracleCommand(string.Format("select * from pacstations.PACS_CHECK_VIEW where cureid='{0}' AND chktime>= to_date('{1}','yyyy-mm-dd')", target.OutPatientNumber, target.InDepartmentTime.ToString("yyyy-MM-dd")), connection);
var dataSet = new System.Data.DataSet();
dataAdapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter(command);
dataAdapter.Fill(dataSet);
foreach (System.Data.DataRow row in dataSet.Tables[0].Rows)
{
var newRescueRoomImageRecord = new RescueRoomImageRecord();
newRescueRoomImageRecord.BOOKID = (string)row["BOOKID"];
if (db.RescueRoomImageRecords.Any(c => c.BOOKID == newRescueRoomImageRecord.BOOKID) || (target.OutDepartmentTime.HasValue && target.OutDepartmentTime.Value <= (DateTime?)row["CHKTIME"]))
{
continue;
}
newRescueRoomImageRecord.RescueRoomImageRecordId = Guid.NewGuid();
newRescueRoomImageRecord.RescueRoomInfoId = target.RescueRoomInfoId;
newRescueRoomImageRecord.BookTime = (DateTime?)row["BOOKDATE"];
newRescueRoomImageRecord.CheckTime = (DateTime?)row["CHKTIME"];
newRescueRoomImageRecord.ReportTime = (DateTime?)row["REPTIME"];
newRescueRoomImageRecord.Part = (string)row["CHKPARTS"];
newRescueRoomImageRecord.Category = (string)row["CHKTYPENAME"];
var originCode = row["IMGTYPE"].ToString();
var imageCategory = db.ImageCategories.FirstOrDefault(c => c.OriginCode == originCode);
newRescueRoomImageRecord.ImageCategoryId = imageCategory.ImageCategoryId;
newRescueRoomImageRecord.UpdateTime = DateTime.Now;
db.RescueRoomImageRecords.Add(newRescueRoomImageRecord);
db.SaveChanges();
}
//删除检查时间早于入室时间的影像项
if (false)
{
var listRescueRoomImageRecord = db.RescueRoomImageRecords.Where(c => c.RescueRoomInfoId == target.RescueRoomInfoId && c.CheckTime < target.InDepartmentTime).ToList();
db.RescueRoomImageRecords.RemoveRange(listRescueRoomImageRecord);
db.SaveChanges();
}
//删除检查时间超过离室时间的影像项
if (target.OutDepartmentTime.HasValue)
{
var listRescueRoomImageRecord = db.RescueRoomImageRecords.Where(c => c.RescueRoomInfoId == target.RescueRoomInfoId && target.OutDepartmentTime <= c.CheckTime).ToList();
db.RescueRoomImageRecords.RemoveRange(listRescueRoomImageRecord);
db.SaveChanges();
}
return(RedirectToAction("IndexPartial", new { rescueRoomInfoId = rescueRoomInfoId }));
}
void BadQueries(string name, string password)
{
var command1 = new System.Data.Odbc.OdbcCommand("SELECT AccountNumber FROM Users " + // Noncompliant {{Make sure to sanitize the parameters of this SQL command.}}
// ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
"WHERE Username='******' AND Password='******'");
command1.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
// ^^^^^^^^^^^^^^^^^^^^
"WHERE Username='******' AND Password='******'";
var command2 = new System.Data.Odbc.OdbcDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command3 = new System.Data.OleDb.OleDbCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command3.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command4 = new System.Data.OleDb.OleDbDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command5 = new Oracle.ManagedDataAccess.Client.OracleCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command5.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command6 = new Oracle.ManagedDataAccess.Client.OracleDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command7 = new System.Data.SqlServerCe.SqlCeCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command7.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command8 = new System.Data.SqlServerCe.SqlCeDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
var command9 = new System.Data.SqlClient.SqlCommand("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'");
command9.CommandText = "SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'";
var command10 = new System.Data.SqlClient.SqlDataAdapter("SELECT AccountNumber FROM Users " + // Noncompliant
"WHERE Username='******' AND Password='******'", "");
}
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;
}
}
