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