/// <summary> /// Metóda sa používa na vykonanie dotazu SQL (select) a na čítanie každého riadku pre všetky stĺpce. /// </summary> /// <param name="command">SQL query.</param> /// <returns>DataTable všetkých dát pre stĺpec.</returns> public static DataTable ExecuteSqlCommand(string command, EnvironmentSettings DatabaseSettings = null) { var resultList = new DataTable(); using (OracleConnection oOracleConnection = GetDatabaseConnection(DatabaseSettings)) { oOracleConnection.Open(); OracleGlobalization info = oOracleConnection.GetSessionInfo(); info.DateFormat = "DD.MM.RR HH24:MI:SS"; oOracleConnection.SetSessionInfo(info); using (var oracleCommand = new OracleCommand(command, oOracleConnection)) { using (var oracleDataReader = oracleCommand.ExecuteReader()) { if (!oracleDataReader.HasRows) { return(resultList); } resultList.Load(oracleDataReader); Dispose(oOracleConnection, oracleDataReader); } } } if (resultList.Rows.Count == 0) { throw new Exception(string.Format(CultureInfo.CurrentCulture, "No result for: {0}", command)); } return(resultList); }
private void button3_Click(object sender, EventArgs e) { String _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.Language = "ITALIAN"; _connObj.SetSessionInfo(info); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT NonExistentField FROM Products"; OracleDataReader _reader = _cmdObj.ExecuteReader(); _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } }
public override IDbConnection GetConnection() { try { if (conn == null) { conn = new OracleConnection(this.connStr); } if (conn.State != ConnectionState.Open) { conn.Open(); OracleGlobalization oracleInfo = ((OracleConnection)conn).GetSessionInfo(); oracleInfo.Language = "AMERICAN"; ((OracleConnection)conn).SetSessionInfo(oracleInfo); } } catch (Exception e) { throw new Exception("Get Oracle connection fail.", e); } return(conn); }
public string ToSqlLiteral() { if (IsNull) { return(TerminalValues.Null); } var literalValue = ToString(); #if !ORACLE_MANAGED_DATA_ACCESS_CLIENT if (!_oracleDecimal.IsInt) { var decimalSeparator = OracleGlobalization.GetThreadInfo().NumericCharacters[0]; if (decimalSeparator != '.') { var index = literalValue.LastIndexOf(decimalSeparator); if (index != -1) { return(literalValue.Remove(index, 1).Insert(index, ".")); } } } #endif return(literalValue); }
/****************************************************************** * This method is called to alter session information on database * connection. The method modifies the format in which date data * is received from database. * Note:This method is re-executed when TAF re-establishes the * database connection. * ****************************************************************/ private void alterSessionInfo() { // obtain default session settings OracleGlobalization oraGlob = conn.GetSessionInfo(); // alter the date format for current session oraGlob.DateFormat = "Dd Month yyyy"; // set the session information using connection object conn.SetSessionInfo(oraGlob); }
public void Check() { //open connection if (this.OpenConnection() == true) { OracleGlobalization og = connection.GetSessionInfo(); Console.WriteLine(og.DateFormat + " " + og.Territory + " " + og.Language); //close connection this.CloseConnection(); } }
/// <summary> /// Metóda sa používa na vykonanie dotazu SQL (select) a na čítanie každého riadku zo stĺpca. /// </summary> /// <param name="command">SQL query.</param> /// <param name="connectionString">Server, user, pass</param> /// <param name="column">Názov stĺpca.</param> /// <returns>Dictionary všetkých dát pre stĺpec.</returns> public static Dictionary <string, string> ExecuteSqlCommand(string command, IEnumerable <string> columns, EnvironmentSettings DatabaseSettings = null) { var resultList = new Dictionary <string, string>(); var resultTemp = new Dictionary <string, string>(); using (OracleConnection oOracleConnection = GetDatabaseConnection(DatabaseSettings)) { oOracleConnection.Open(); OracleGlobalization info = oOracleConnection.GetSessionInfo(); info.DateFormat = "DD.MM.RR HH24:MI:SS"; oOracleConnection.SetSessionInfo(info); using (var oracleCommand = new OracleCommand(command, oOracleConnection)) { using (var oracleDataReader = oracleCommand.ExecuteReader()) { if (!oracleDataReader.HasRows) { return(resultList); } while (oracleDataReader.Read()) { for (int i = 0; i < oracleDataReader.FieldCount; i++) { resultTemp[oracleDataReader.GetName(i)] = oracleDataReader.GetValue(i).ToString(); } } Dispose(oOracleConnection, oracleDataReader); } } } foreach (string column in columns) { string keyValue; if (resultTemp.TryGetValue(column, out keyValue)) { resultList[column] = keyValue; } else { throw new Exception(string.Format(CultureInfo.CurrentCulture, "Exception while trying to get results from sql query, lack of column '{0}'", column)); } } return(resultList); }
public List<XX_OPM_BCI_ITEMS_V> GetInventoryItemList() { //return Task.Run(() => //{ using (var dbConnection = GetConnection()) { dbConnection.Open(); OracleGlobalization oracleGlobalization = dbConnection.GetSessionInfo(); oracleGlobalization.Language = "LATIN AMERICAN SPANISH"; dbConnection.SetSessionInfo(oracleGlobalization); return dbConnection.QueryAsync<XX_OPM_BCI_ITEMS_V>("Select * FROM APPS.XX_OPM_BCI_ITEMS_V ORDER BY DESCRIPCION_ITEM").Result.ToList(); } //}).Result; }
private void InitializeObjects() { try { connection = new OracleConnection(conecctionString); connection.Open(); OracleGlobalization info = connection.GetSessionInfo(); info.Language = "SPANISH"; info.Territory = "CHILE"; connection.SetSessionInfo(info); transaction = connection.BeginTransaction(); } catch { throw new ConnectionFailedException( "El servidor de base de datos no se encuentra disponible, comunique este problema al administrador del servicio de base de datos."); } }
public System.Data.Common.DbConnection NewConnection(string connectionString) { OracleConnection cn = new OracleConnection(connectionString); cn.Open(); using (var cmd = cn.CreateCommand()) { // cmd.CommandText = "set dateformat mdy"; // cmd.ExecuteNonQuery(); OracleGlobalization SessionGlob = cn.GetSessionInfo(); // SetSessionInfo updates the Session with the new value SessionGlob.DateFormat = "MM/DD/YYYY"; cn.SetSessionInfo(SessionGlob); } return(cn); }
static void Main(string[] args) { string cs = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=dbserver.mif.pg.gda.pl)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED) (SERVICE_NAME=ORACLEMIF)));User Id={0};Password={1};"; //pobranie loginu i hasla Console.WriteLine("Podaj login:"******"Podaj hasło:"); string pass = Console.ReadLine(); //nadpisanie CS cs = String.Format(cs, login, pass); OracleConnection oraconn = new OracleConnection(cs); try { oraconn.Open(); OracleGlobalization og = oraconn.GetSessionInfo(); Console.WriteLine(og.DateFormat + " " + og.Territory + " " + og.Language); string sql = "INSERT INTO"; // Create command. OracleCommand cmd = new OracleCommand(); // Set connection for command. cmd.Connection = oraconn; cmd.CommandText = sql; cmd.ExecuteNonQuery(); Console.WriteLine("Row inserted !! "); oraconn.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); } }
private List<XX_OPM_BCI_PESADAS_ALL> GetPesadas(String where) { //return Task.Run(() => //{ using (var dbConnection = GetConnection()) { dbConnection.Open(); OracleGlobalization oracleGlobalization = dbConnection.GetSessionInfo(); oracleGlobalization.Language = "LATIN AMERICAN SPANISH"; dbConnection.SetSessionInfo(oracleGlobalization); return dbConnection.QueryAsync<XX_OPM_BCI_PESADAS_ALL>("SELECT p.*, COALESCE(v.ESTADO, 'Pendiente') AS ESTADO, COALESCE(v.DISPOSICION, 'Pendiente') AS DISPOSICION " + "FROM XX_OPM_BCI_PESADAS_ALL p " + "LEFT JOIN XX_OPM_BCI_PESADAS_ESTADOS_V v " + "ON p.PESADA_ID = v.PESADA_ID" + where).Result.ToList(); } //}).Result; }
public override System.Data.IDbConnection CreateDbConnection() { var connection = new OracleConnection(connectionString); // See: Case-insensitive searching in Oracle http://stackoverflow.com/a/5391234/1796930 if (enableCaseInsensitiveSearching) { connection.Open(); // The connection has to be open before we can set any session info OracleGlobalization info = connection.GetSessionInfo(); info.Sort = "BINARY_CI"; // NLS_SORT: info.Comparison = "LINGUISTIC"; // NLS_COMP: connection.SetSessionInfo(info); } return(connection); }
public XX_OPM_BCI_PESADAS_ALL getLatestPesada() { //return Task.Run(() => //{ using (var dbConnection = GetConnection()) { dbConnection.Open(); OracleGlobalization oracleGlobalization = dbConnection.GetSessionInfo(); oracleGlobalization.Language = "LATIN AMERICAN SPANISH"; dbConnection.SetSessionInfo(oracleGlobalization); return dbConnection.QueryAsync<XX_OPM_BCI_PESADAS_ALL>("SELECT p.*, COALESCE(v.ESTADO, 'Pendiente') AS ESTADO, COALESCE(v.DISPOSICION, 'Pendiente') AS DISPOSICION " + "FROM XX_OPM_BCI_PESADAS_ALL p " + "LEFT JOIN XX_OPM_BCI_PESADAS_ESTADOS_V v " + "ON p.PESADA_ID = v.PESADA_ID " + "WHERE p.CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM XX_OPM_BCI_PESADAS_ALL)").Result.FirstOrDefault(); } //}).Result; }
static ManagerDB() { try { _con = new OracleConnection(QSettings.ConnentionString); _con.Open(); OracleGlobalization info = _con.GetSessionInfo(); info.NumericCharacters = ",."; info.DateFormat = "dd.mm.yyyy"; info.Language = "UKRAINIAN"; _con.SetSessionInfo(info); } catch (Exception ex) { ExceptionHandler("ManagerDB()", ex.Message); } }
private void button9_Click(object sender, EventArgs e) { string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; string _result; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.Territory = "Sweden"; info.Language = "Swedish"; OracleGlobalization.SetThreadInfo(info); _connObj.SetSessionInfo(info); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT TO_CHAR(Price,'L99G999D99') PriceDefCurrency, TO_CHAR(Price,'U99G999D99') PriceDualCurrency, TO_CHAR(ExpiryDate,'DL') ExpiryDate FROM Products WHERE ID='E1'"; OracleDataReader _reader = _cmdObj.ExecuteReader(); if (_reader.HasRows) { if (_reader.Read()) { String _priceDefCurrency = _reader.GetString(_reader.GetOrdinal("PriceDefCurrency")); String _priceDualCurrency = _reader.GetString(_reader.GetOrdinal("PriceDualCurrency")); String _expiryDate = _reader.GetString(_reader.GetOrdinal("ExpiryDate")); _result = _priceDefCurrency + "\n" + _priceDualCurrency + "\n" + _expiryDate; MessageBox.Show(_result); } } _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void button4_Click(object sender, EventArgs e) { string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; string _result; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.DateLanguage = "FINNISH"; info.DateFormat = "DD-MON-YYYY"; OracleGlobalization.SetThreadInfo(info); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT ExpiryDate FROM Products ORDER BY ExpiryDate ASC"; OracleDataReader _reader = _cmdObj.ExecuteReader(); _result = "Results:"; if (_reader.HasRows) { while (_reader.Read()) { OracleDate _odate = _reader.GetOracleDate(_reader.GetOrdinal("ExpiryDate")); _result = _result + "\n" + _odate.ToString(); } } MessageBox.Show(_result); _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public XX_OPM_BCI_PESADAS_ALL GetPesadaByID(int id) { //return Task.Run(() => //{ using (var dbConnection = GetConnection()) { var param = new DynamicParameters(); param.Add("PESADA_ID", id); dbConnection.Open(); OracleGlobalization oracleGlobalization = dbConnection.GetSessionInfo(); oracleGlobalization.Language = "LATIN AMERICAN SPANISH"; dbConnection.SetSessionInfo(oracleGlobalization); return dbConnection.Query<XX_OPM_BCI_PESADAS_ALL>("SELECT p.*, COALESCE(v.ESTADO, 'Pendiente') AS ESTADO, COALESCE(v.DISPOSICION, 'Pendiente') AS DISPOSICION " + "FROM XX_OPM_BCI_PESADAS_ALL p " + "LEFT JOIN XX_OPM_BCI_PESADAS_ESTADOS_V v " + "ON p.PESADA_ID = v.PESADA_ID " + "WHERE p.PESADA_ID = :PESADA_ID", param).FirstOrDefault(); } //}).Result; }
private void button8_Click(object sender, EventArgs e) { String _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; string _result; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT Name FROM Products ORDER BY Name ASC"; OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.Sort = "SPANISH_M"; _connObj.SetSessionInfo(info); OracleDataReader _reader = _cmdObj.ExecuteReader(); _result = "Results:"; if (_reader.HasRows) { while (_reader.Read()) { String _Name = _reader.GetString (_reader.GetOrdinal("Name")); _result = _result + "\n" + _Name.ToString(); } } MessageBox.Show(_result); _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void button6_Click(object sender, EventArgs e) { string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; string _result; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.ISOCurrency = "AUSTRALIA"; _connObj.SetSessionInfo(info); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT TO_CHAR(Price,'C99G999D99') Price FROM Products"; OracleDataReader _reader = _cmdObj.ExecuteReader(); _result = "Results:"; if (_reader.HasRows) { while (_reader.Read()) { String _price = _reader.GetString (_reader.GetOrdinal("Price")); _result = _result + "\n" + _price.ToString(); } } MessageBox.Show(_result); _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public void AbrirConexion() { try { if (conexion != null) { throw new Exception("Ya hay una conexión activa."); } conexion = new OracleConnection(this.cadenaConexion); conexion.Open(); OracleGlobalization info = conexion.GetSessionInfo(); info.DateFormat = "yyyy/mm/dd hh24:mi:ss"; conexion.SetSessionInfo(info); } catch (Exception ex) { throw new Exception(ConcatenarException(ex)); } }
public IActionResult Get() { string emailSvr = Environment.GetEnvironmentVariable("EmailServer"); string connectionString = Environment.GetEnvironmentVariable("ConnectionString"); string name = ""; string error_message = ""; //connectionString = "User Id = scott; Password = 12345; Data Source = localhost:1521/orcl;"; try { using (OracleConnection connection = new OracleConnection(connectionString)) { string queryString = "Select * from emp"; OracleCommand command = new OracleCommand(queryString, connection); command.Connection.Open(); OracleGlobalization info = command.Connection.GetSessionInfo(); //this.Connection.GetSessionInfo(); info.TimeZone = "America/New_York"; command.Connection.SetSessionInfo(info); //this.Connection.SetSessionInfo(info); //command.ExecuteNonQuery(); OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { name = reader.GetString(1); break; } } } catch (Exception ex) { error_message = ex.Message.ToString(); } return(Ok("hello world " + name + " " + emailSvr + " " + connectionString + " ### " + error_message)); //string OC_Evariable = Environment.GetEnvironmentVariable("OC_HC_URL"); //return Ok("hello world " + emailSvr+" "+connectionString+" "+OC_Evariable); }
private void button7_Click(object sender, EventArgs e) { string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;"; try { OracleConnection _connObj = new OracleConnection(); _connObj.ConnectionString = _connstring; _connObj.Open(); OracleGlobalization info = OracleGlobalization.GetClientInfo(); info.Territory = "Hong Kong"; info.TimeZone = "Asia/Hong_Kong"; OracleGlobalization.SetThreadInfo(info); _connObj.SetSessionInfo(info); OracleCommand _cmdObj = _connObj.CreateCommand(); _cmdObj.CommandText = "SELECT LaunchDate AT LOCAL LaunchDateLocal FROM Products"; OracleDataReader _reader = _cmdObj.ExecuteReader(); if (_reader.HasRows) { if (_reader.Read()) { OracleTimeStampTZ _launchDate = _reader.GetOracleTimeStampTZ (_reader.GetOrdinal("LaunchDateLocal")); MessageBox.Show(_launchDate.ToString()); } } _reader.Dispose(); _cmdObj.Dispose(); _connObj.Dispose(); _reader.Close(); _connObj.Close(); _reader = null; _connObj = null; _cmdObj = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private bool SetGlobalizationParams() { try { // Get the default thread setting OracleGlobalization threadGlob = OracleGlobalization.GetThreadInfo(); //modify the NLS_LANGUAGE parameter AMERICAN_AMERICA threadGlob.Language = "AMERICAN"; // "THAI"; // modify the NLS_TERRITORY parameter threadGlob.Territory = "AMERICA"; // "THAILAND"; // modify the NLS_DATE_FORMAT parameter threadGlob.DateFormat = "Day:Dd Month yyyy";//"dd/MM/yyyy"; // set the modified NLS parameters for thread //Thread's NLS settings are used by any data retrieved //as .NET String type. OracleGlobalization.SetThreadInfo(threadGlob); //Get session's default NLS settings OracleGlobalization sessionGlob = this.Connection.GetSessionInfo(); // modify the NLS_TERRITORY parameter sessionGlob.Territory = "AMERICA";//"THAI"; // set the modified NLs parameters for session // Session's NLS settings are used by data retrieved using //TO_CHAR function used in SELECT statements. this.Connection.SetSessionInfo(sessionGlob); } catch (Exception e) { return(false); } return(true); }
/// <summary> /// Metóda sa používa na vykonanie dotazu SQL (select) a na čítanie každého riadku zo stĺpca. /// </summary> /// <param name="command">SQL query.</param> /// <param name="connectionString">Server, user, pass</param> /// <param name="column">Názov stĺpca.</param> /// <returns>Collections všetkých dát pre stĺpec.</returns> public static ICollection <string> ExecuteSqlCommand1(string command, string column, EnvironmentSettings DatabaseSettings = null) { var resultList = new List <string>(); using (OracleConnection oOracleConnection = GetDatabaseConnection(DatabaseSettings)) { oOracleConnection.Open(); OracleGlobalization info = oOracleConnection.GetSessionInfo(); info.DateFormat = "DD.MM.RR HH24:MI:SS"; oOracleConnection.SetSessionInfo(info); using (var oracleCommand = new OracleCommand(command, oOracleConnection)) { using (var oracleDataReader = oracleCommand.ExecuteReader()) { if (!oracleDataReader.HasRows) { return(resultList); } while (oracleDataReader.Read()) { resultList.Add(oracleDataReader[column].ToString()); } Dispose(oOracleConnection, oracleDataReader); } } } if (resultList.Count == 0) { throw new Exception(string.Format(CultureInfo.CurrentCulture, "No result for: {0} \n {1}", command, column)); } return(resultList); }
/******************************************************************* * The method sets the NLS parameters for the current thread and session. * These NLS values will be used while retrieving and updating the data into * the database. ********************************************************************/ private bool setGlobalizationParams() { try { // Get the default thread setting OracleGlobalization threadGlob = OracleGlobalization.GetThreadInfo(); //modify the NLS_LANGUAGE parameter threadGlob.Language = Config.language; // modify the NLS_TERRITORY parameter threadGlob.Territory = Config.territory; // modify the NLS_DATE_FORMAT parameter threadGlob.DateFormat = Config.dateformat; // set the modified NLS parameters for thread //Thread's NLS settings are used by any data retrieved //as .NET String type. OracleGlobalization.SetThreadInfo(threadGlob); //Get session's default NLS settings OracleGlobalization sessionGlob = ConnectionManager.conn.GetSessionInfo(); // modify the NLS_TERRITORY parameter sessionGlob.Territory = Config.territory; // set the modified NLs parameters for session // Session's NLS settings are used by data retrieved using //TO_CHAR function used in SELECT statements. ConnectionManager.conn.SetSessionInfo(sessionGlob); } catch (Exception e) { MessageBox.Show(e.Message); return(false); } return(true); }
/// <summary> /// This method alters the session's globalization settings with all the property values specified in the provided OracleGlobalization object.. /// </summary> /// <param name="globe">An instance of <see cref="OracleGlobalization"/></param> public void SetSessionInfo(OracleGlobalization globe) { //Return this back to the caller Connection.SetSessionInfo(globe); }
public static DBContextRegionalInfo BuildRegionalInfo( OracleGlobalization configurationInfo) =>
/// <summary> /// The main entry point for the application. /// </summary> static void Main(string[] args) { int rows = 0; string[] KeyColumnsList = null; string[] UpdateColumnsList = null; Stream stream = null; StreamReader sr = null; // Define the XSL document for doing the transform. string xslstr = "<?xml version='1.0'?>\n" + "<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">\n" + " <xsl:output encoding=\"utf-8\"/>\n" + " <xsl:param name=\"param1\">default</xsl:param>\n" + " <xsl:param name=\"param2\">default</xsl:param>\n" + " <xsl:template match=\"/\">\n" + " <ROWSET>\n" + " <xsl:apply-templates select=\"EMPLOYEES\"/>\n" + " </ROWSET>\n" + " </xsl:template>\n" + " <xsl:template match=\"EMPLOYEES\">\n" + " <xsl:apply-templates select=\"EMPLOYEE\"/>\n" + " </xsl:template>\n" + " <xsl:template match=\"EMPLOYEE\">\n" + " <ROW>\n" + " <EMPNO>\n" + " <xsl:apply-templates select=\"EMPLOYEE_ID\"/>\n" + " </EMPNO>\n" + " <ENAME>\n" + " <xsl:apply-templates select=\"EMPLOYEE_NAME\"/>\n" + " </ENAME>\n" + " <HIREDATE>\n" + " <xsl:value-of select=\"$param1\"/>\n" + " </HIREDATE>\n" + " <JOB>\n" + " <xsl:value-of select=\"$param2\"/>\n" + " </JOB>\n" + " </ROW>\n" + " </xsl:template>\n" + "</xsl:stylesheet>\n"; // Create the connection. string constr = "User Id=scott;Password=tiger;Data Source=oracle"; OracleConnection con = new OracleConnection(constr); con.Open(); // Set the date, and timestamp formats for Oracle 9i Release 2, or later. // This is just needed for queries. if (!con.ServerVersion.StartsWith("9.0") && !con.ServerVersion.StartsWith("8.1")) { OracleGlobalization sessionParams = con.GetSessionInfo(); sessionParams.DateFormat = "YYYY-MM-DD\"T\"HH24:MI:SS"; sessionParams.TimeStampFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3"; sessionParams.TimeStampTZFormat = "YYYY-MM-DD\"T\"HH24:MI:SS.FF3"; con.SetSessionInfo(sessionParams); } // Create the command. OracleCommand cmd = new OracleCommand("", con); // Set the XML command type to insert. cmd.XmlCommandType = OracleXmlCommandType.Insert; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<EMPLOYEES>\n" + " <EMPLOYEE>\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " <EMPLOYEE_NAME>Smith</EMPLOYEE_NAME>\n" + " </EMPLOYEE>\n" + "</EMPLOYEES>\n"; // Set the XML save properties. UpdateColumnsList = new string[4]; UpdateColumnsList[0] = "EMPNO"; UpdateColumnsList[1] = "ENAME"; UpdateColumnsList[2] = "HIREDATE"; UpdateColumnsList[3] = "JOB"; cmd.XmlSaveProperties.Table = "emp"; cmd.XmlSaveProperties.RowTag = "ROW"; cmd.XmlSaveProperties.KeyColumnsList = null; cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList; cmd.XmlSaveProperties.Xslt = xslstr; cmd.XmlSaveProperties.XsltParams = "param1=\"2003-1-1T0:0:0.000\";param2=\"CLERK\""; // Do the inserts. rows = cmd.ExecuteNonQuery(); Console.WriteLine("Rows inserted: " + rows); // Do a query for the inserted employee. Console.WriteLine("Do a query for the inserted employee."); cmd.XmlCommandType = OracleXmlCommandType.Query; cmd.CommandText = "select * from emp e where e.empno = 1234"; stream = cmd.ExecuteStream(); sr = new StreamReader(stream, Encoding.Unicode); Console.WriteLine(sr.ReadToEnd()); // Set the XML command type to update. cmd.XmlCommandType = OracleXmlCommandType.Update; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<EMPLOYEES>\n" + " <EMPLOYEE>\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " <EMPLOYEE_NAME>Adams</EMPLOYEE_NAME>\n" + " </EMPLOYEE>\n" + "</EMPLOYEES>\n"; // Set the XML save properties. KeyColumnsList = new string[1]; KeyColumnsList[0] = "EMPNO"; UpdateColumnsList = new string[1]; UpdateColumnsList[0] = "ENAME"; cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList; cmd.XmlSaveProperties.UpdateColumnsList = UpdateColumnsList; // Do the updates. rows = cmd.ExecuteNonQuery(); Console.WriteLine("Rows updated: " + rows); // Do a query for the updated employee. Console.WriteLine("Do a query for the updated employee."); cmd.XmlCommandType = OracleXmlCommandType.Query; cmd.CommandText = "select * from emp e where e.empno = 1234"; stream = cmd.ExecuteStream(); sr = new StreamReader(stream, Encoding.Unicode); Console.WriteLine(sr.ReadToEnd()); // Set the XML command type to delete. cmd.XmlCommandType = OracleXmlCommandType.Delete; // Set the XML document. cmd.CommandText = "<?xml version=\"1.0\"?>\n" + "<EMPLOYEES>\n" + " <EMPLOYEE>\n" + " <EMPLOYEE_ID>1234</EMPLOYEE_ID>\n" + " </EMPLOYEE>\n" + "</EMPLOYEES>\n"; // Set the XML save properties. KeyColumnsList = new string[1]; KeyColumnsList[0] = "EMPNO"; cmd.XmlSaveProperties.KeyColumnsList = KeyColumnsList; cmd.XmlSaveProperties.UpdateColumnsList = null; // Do the deletes. rows = cmd.ExecuteNonQuery(); Console.WriteLine("Rows deleted: " + rows); // Do a query for the deleted employee. Console.WriteLine("Do a query for the deleted employee."); cmd.XmlCommandType = OracleXmlCommandType.Query; cmd.CommandText = "select * from emp e where e.empno = 1234"; stream = cmd.ExecuteStream(); sr = new StreamReader(stream, Encoding.Unicode); Console.WriteLine(sr.ReadToEnd()); // Clean up. cmd.Dispose(); con.Close(); con.Dispose(); }
/// <summary> /// To Do => Get datasets from ORACLE - use this override when columns in query and in class T is diferent and use prepared parameters /// </summary> /// <param name="Sql_ora"></param> /// <param name="Task_name"></param> /// <param name="D_columns"></param> /// <param name="P_columns"></param> /// <returns></returns> public async Task <List <T> > Get_Ora(string Sql_ora, string Task_name, Dictionary <string, int> D_columns, Dictionary <int, string> P_columns, Dictionary <int, Type> P_types, ORA_parameters _parameters) { List <T> Rows = new List <T>(); try { using (OracleConnection conO = new OracleConnection(Str_oracle_conn)) { await conO.OpenAsync(); OracleGlobalization info = conO.GetSessionInfo(); info.DateFormat = "YYYY-MM-DD"; conO.SetSessionInfo(info); bool list_columns = false; T Row = new T(); IPropertyAccessor[] Accessors = Row.GetType().GetProperties() .Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray(); using OracleCommand cust = new OracleCommand(Sql_ora, conO); using OracleDataReader reader = cust.ExecuteReader(); reader.FetchSize = cust.RowSize * 200; while (await reader.ReadAsync()) { if (!list_columns) { if (D_columns.Count == 0) { for (int col = 0; col < reader.FieldCount; col++) { string nam = reader.GetName(col).ToLower(); D_columns.Add(nam, col); } } list_columns = true; } Row = new T(); int counter = 0; foreach (var Accessor in Accessors) { string metod = P_columns[counter]; if (D_columns.ContainsKey(metod)) { int col = D_columns[metod]; object readData = reader.GetValue(D_columns[metod]); if (readData != System.DBNull.Value) { Type pt = P_types[counter]; Accessor.SetValue(Row, Convert.ChangeType(readData, Nullable.GetUnderlyingType(pt) ?? pt, null)); } } counter++; } Rows.Add(Row); } } Rows.Sort(); return(Rows); } catch (Exception e) { Loger.Log("Błąd modyfikacji tabeli:" + Task_name + e); return(Rows); } }