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; } }