private void getSessionInfo()
{
m_sessionInfo.Clear();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = m_conn;
cmd.CommandText = "select sys_context('userenv','db_unique_name') dbname, s.* from v$session s where s.audsid = sys_context('userenv','sessionid')";
cmd.CommandType = CommandType.Text;
OracleDataReader r = cmd.ExecuteReader();
try
{
if (r.HasRows)
{
r.Read();
for (int i = 0; i < r.FieldCount; i++)
{
if (r.IsDBNull(i))
{
continue;
}
string field = r.GetName(i);
string val = String.Empty;
switch (r.GetDataTypeName(i))
{
case "Decimal": val = Convert.ToString(r.GetDecimal(i)); break;
case "Varchar2": val = r.GetString(i); break;
case "Date": val = Convert.ToString(r.GetDateTime(i)); break;
default: val = r.GetDataTypeName(i); break;
}
if (!String.IsNullOrWhiteSpace(val))
{
m_sessionInfo.Add(field, val);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
r.Close();
}
}
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
cmd = new OracleCommand("Select EmployeeID From Employees Where FirstName = 'Oved'", con);
cmd.CommandType = CommandType.Text;
con.Open();
rdr = cmd.ExecuteReader();
rdr.Read();
typeName = rdr.GetDataTypeName(0);
con.Close();
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
}
}
private static IEnumerable <DoOutput> GetTypes(OracleDataReader reader)
{
for (var i = 0; i < reader.FieldCount; i++)
{
yield return(GetType(reader.GetDataTypeName(i)));
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="sourceName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetColumnMetadataFromSql(
string sql,
DbConnectionMx conn)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata md;
List <DbColumnMetadata> mdList = new List <DbColumnMetadata>();
string sql2 = sql + " where 1=2"; // make execution fast
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = conn;
cmd.PrepareUsingDefinedConnection(sql2);
OracleDataReader rdr = cmd.ExecuteReader() as OracleDataReader;
for (int fi = 0; fi < rdr.FieldCount; fi++)
{
md = new DbColumnMetadata();
md.Name = rdr.GetName(fi);
md.Type = rdr.GetDataTypeName(fi); //
mdList.Add(md);
}
rdr.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(mdList);
}
private static string ParseOracleDate(OracleDataReader reader, int index, string dateFormat)
{
if (string.IsNullOrWhiteSpace(dateFormat))
{
return(reader.GetValue(index).ToString());
}
var dateType = reader.GetDataTypeName(index);
var dateString = ""; // Formatted output date.
switch (dateType)
{
case "Date":
var oDate = reader.GetOracleDate(index);
if (!oDate.IsNull)
{
var dt = new DateTime(oDate.Year, oDate.Month, oDate.Day, oDate.Hour, oDate.Minute, oDate.Second);
dateString = dt.ToString(dateFormat);
}
break;
case "TimeStamp":
var oTimeStamp = reader.GetOracleTimeStamp(index);
if (!oTimeStamp.IsNull)
{
// Is this the best way to get milliseconds from double to int?
int.TryParse(oTimeStamp.Millisecond.ToString("000").Substring(0, 3), out int msOut);
var dt = new DateTime(oTimeStamp.Year, oTimeStamp.Month, oTimeStamp.Day, oTimeStamp.Hour, oTimeStamp.Minute, oTimeStamp.Second, msOut);
dateString = dt.ToString(dateFormat);
}
break;
case "TimeStampLTZ":
var oTimeStampLTZ = reader.GetOracleTimeStampLTZ(index);
if (!oTimeStampLTZ.IsNull)
{
// Is this the best way to get milliseconds from double to int?
int.TryParse(oTimeStampLTZ.Millisecond.ToString("000").Substring(0, 3), out int msOut);
var dt = new DateTime(oTimeStampLTZ.Year, oTimeStampLTZ.Month, oTimeStampLTZ.Day, oTimeStampLTZ.Hour, oTimeStampLTZ.Minute, oTimeStampLTZ.Second, msOut);
dateString = dt.ToString(dateFormat);
}
break;
case "TimeStampTZ":
var oTimeStampTZ = reader.GetOracleTimeStampTZ(index);
if (!oTimeStampTZ.IsNull)
{
// Is this the best way to get milliseconds from double to int?
int.TryParse(oTimeStampTZ.Millisecond.ToString("000").Substring(0, 3), out int msOut);
var dt = new DateTime(oTimeStampTZ.Year, oTimeStampTZ.Month, oTimeStampTZ.Day, oTimeStampTZ.Hour, oTimeStampTZ.Minute, oTimeStampTZ.Second, msOut);
dateString = dt.ToString(dateFormat);
}
break;
default:
throw new Exception("Trying to parse unknown date type.");
}
return(dateString);
}
//Helper of generalSqlCommand reads in command
private List <string> generalReader(OracleCommand cmd)
{
//Execute the command and use DataReader grab return statement
OracleDataReader reader = cmd.ExecuteReader();
List <string> output = new List <string>();
try
{
while (reader.Read())
{
//for each entry checking type of object given then translates it to string and adds it to list
for (int i = 0; i < reader.FieldCount; i++)
{
//Console.WriteLine(reader.GetDataTypeName(i));
//if entry is null return "NIL"
if (reader.IsDBNull(i))
{
output.Add("NIL");
}
else
{
if (reader.GetDataTypeName(i) == "Decimal")
{
output.Add(reader.GetDecimal(i).ToString());
}
else if (reader.GetDataTypeName(i).StartsWith("Int"))
{
output.Add(reader.GetDecimal(i).ToString());
}
else
{
output.Add(reader.GetString(i));
}
}
}
}
}
finally { reader.Close(); }
return(output);
}
private void button2_Click(object sender, EventArgs e)
{
string StringConnection = @"Data Source =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
);User id=smallschema; password=smallschema;";
OracleConnection sqlConnection = new OracleConnection(StringConnection);
sqlConnection.Open();
// OracleCommand oCommand = new OracleCommand(@" IF( 0 = :DiscardRenderRequest )
// BEGIN
// select * from customer where 1=1
// END
// ELSE
// BEGIN
// select * from customer where 1=2
// END ");
OracleCommand oCommand = new OracleCommand(@"select * from table1");
oCommand.Connection = sqlConnection;
OracleDataReader reader = oCommand.ExecuteReader();
int columnCount = reader.FieldCount;
int i = 0;
while (i < columnCount)
{
MessageBox.Show("column " + i + ": " + reader.GetDataTypeName(i));
i++;
}
reader.Close();
return;
oCommand.CommandType = CommandType.Text;
OracleParameter p1 = new OracleParameter(":DiscardRenderRequest", OracleDbType.Int16, 0);
p1.Direction = ParameterDirection.Input;
oCommand.Parameters.Add(p1);
DataTable dtResult = new DataTable();
using (OracleDataAdapter dataAdapter = new OracleDataAdapter(oCommand))
{
dataAdapter.Fill(dtResult);
}
richTextBox1.Text = dtResult.ToString();
}
public override object GetValue(IDataReader reader, int columnIndex)
{
object value = null;
if (reader.IsDBNull(columnIndex))
{
return(null);
}
OracleDataReader rdr = (OracleDataReader)reader;
Type type = rdr.GetFieldType(columnIndex);
string datatypeName = rdr.GetDataTypeName(columnIndex);
if (datatypeName.ToLower().Equals("interval year to month"))
{
value = rdr.GetOracleMonthSpan(columnIndex);
return(value);
}
switch (type.Name.ToLower())
{
case "int32":
value = rdr.GetInt32(columnIndex);
break;
case "decimal":
try {
value = rdr.GetDecimal(columnIndex);
} catch (Exception e) {
value = rdr.GetOracleNumber(columnIndex);
}
break;
case "datetime": value = rdr.GetDateTime(columnIndex);
break;
case "string": value = rdr.GetString(columnIndex);
break;
case "timespan": value = rdr.GetTimeSpan(columnIndex);
break;
default: value = rdr.GetValue(columnIndex);
break;
}
return(value);
}
// private because it does not make sense to use it if it only falls back to the sync version
private static async Task <Field[][]> ReadAsync(string ExtractSql)
{
var connStr = ConfigurationManager.ConnectionStrings["EndurDB"].ConnectionString;
// https://stackoverflow.com/questions/63907271/is-oracle-openasync-etc-not-a-truly-async-method
List <Field[]> ret = new List <Field[]>();
await Task.Run(async() =>
{
using (OracleConnection conn = new OracleConnection(connStr))
{
await conn.OpenAsync();
string viewschema = ConfigurationManager.AppSettings["ViewSchema"];
using (OracleCommand cmd = new OracleCommand("alter session set CURRENT_SCHEMA = " + viewschema, conn))
{
int res = await cmd.ExecuteNonQueryAsync(); // -1 is ok
//Console.WriteLine("session set result: " + res);
}
using (OracleCommand cmd = new OracleCommand(ExtractSql, conn))
{
// https://stackoverflow.com/questions/63907271/is-oracle-openasync-etc-not-a-truly-async-method
OracleDataReader DR = (OracleDataReader)await cmd.ExecuteReaderAsync();
int count = 0;
while (await DR.ReadAsync())
{
count++;
Field[] fields = new Field[DR.FieldCount];
for (int i = 0; i < DR.FieldCount; i++)
{
Field field = new Field();
field.RowCount = count;
field.FieldCount = i;
field.Name = DR.GetName(i);
field.DataType = DR.GetDataTypeName(i);
field.ObjValue =
field.DataType == "Decimal" ?
DR.GetDouble(i) :
DR.GetValue(i);
fields[i] = field;
}
ret.Add(fields);
}
}
}
});
return(ret.ToArray());
}
private string getAbschnittLength(string id)
{
string select = "select sum(round(sqrt((xe - xa) * (xe - xa) + (ye - ya) * (ye - ya)))) as länge from netz" +
" inner join teilstrecke on teilstrecke.ID = netz.ID_CHILD" +
" inner join abschnitt on abschnitt.ID = netz.ID_PARENT" +
" connect by prior ID_PARENT = ID_CHILD start WITH netz.ID_PARENT = '?'";
string length = "";
OracleCommand command = new OracleCommand(select.Replace("?", id), this.connection);
OracleDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
length = reader.GetDataTypeName(0);
}
}
return(length);
}
public static IEnumerable <Field[]> Read(string ExtractSql)
{
var connStr = ConfigurationManager.ConnectionStrings["EndurDB"].ConnectionString;
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
string viewschema = ConfigurationManager.AppSettings["ViewSchema"];
using (OracleCommand cmd = new OracleCommand("alter session set CURRENT_SCHEMA = " + viewschema, conn))
{
int res = cmd.ExecuteNonQuery(); // -1 is ok
//Console.WriteLine("session set result: " + res);
}
using (OracleCommand cmd = new OracleCommand(ExtractSql, conn))
{
OracleDataReader DR = cmd.ExecuteReader();
int count = 0;
while (DR.Read())
{
count++;
Field[] fields = new Field[DR.FieldCount];
for (int i = 0; i < DR.FieldCount; i++)
{
Field field = new Field();
field.RowCount = count;
field.FieldCount = i;
field.Name = DR.GetName(i);
field.DataType = DR.GetDataTypeName(i);
// decimal overflow workaround
field.ObjValue =
field.DataType == "Decimal"?
DR.GetDouble(i) :
DR.GetValue(i);
fields[i] = field;
}
yield return(fields);
}
}
}
}
public void ExportToCSV(SqlDataSource dataSrc, string fileName)
{
//Add Response header
Response.Clear();
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.csv", fileName));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
//GET Data From Database
OracleConnection cn = new OracleConnection(dataSrc.ConnectionString);
string query =
dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");
OracleCommand cmd = new OracleCommand(query, cn);
cmd.CommandTimeout = 999999;
cmd.CommandType = CommandType.Text;
try
{
cn.Open();
OracleDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
//CSV Header
for (int count = 0; count < dr.FieldCount; count++)
{
if (dr.GetName(count) != null)
{
sb.Append(dr.GetName(count));
}
if (count < dr.FieldCount - 1)
{
sb.Append(",");
}
}
Response.Write(sb.ToString() + "\n");
Response.Flush();
//CSV Body
while (dr.Read())
{
sb = new StringBuilder();
for (int col = 0; col < dr.FieldCount - 1; col++)
{
if (!dr.IsDBNull(col))
{
if (dr.GetDataTypeName(col).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(col).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
}
}
sb.Append(",");
}
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (dr.GetDataTypeName(dr.FieldCount - 1).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(dr.FieldCount - 1).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
}
}
}
Response.Write(sb.ToString() + "\n");
Response.Flush();
}
dr.Dispose();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
cmd.Connection.Close();
cn.Close();
}
Response.End();
}
/// <summary>
/// Build list of column to property/field mappings for a given C# class type and a data reader
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <param name="mapByPosition"></param>
/// <param name="allowUnmappedColumns"></param>
/// <returns></returns>
private static List <ColumnMapping> BuildMappings <T>(OracleDataReader reader, bool mapByPosition, bool allowUnmappedColumns)
{
// extract all settable public properties of the type (includes inherited)
PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
// if mapping by name, extract all settable protected and private fields (includes inherited)
FieldInfo[] fields = null;
if (!mapByPosition)
{
fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
}
// when mapping by position, filter out properties that do not have a map position set in the attribute
if (mapByPosition)
{
properties = properties.Select(x => new {
Property = x,
Attribute = (HydratorMapAttribute)Attribute.GetCustomAttribute(x, typeof(HydratorMapAttribute))
})
.Where(x => x.Attribute != null && x.Attribute.Position >= 0).OrderBy(x => x.Attribute.Position) // important to order by position
.Select(x => x.Property).ToArray();
// Never allow the number of properties with the position attribute to be greater than the column count. This means the BO is looking for
// a column that is not there.
if (properties.Length > reader.FieldCount)
{
throw new Exception("Hydrator.BuildMappings<T>() - number of settable properties with a position attribute in " + typeof(T).FullName
+ "(" + properties.Length + ") is greater than the number of columns in the reader(" + reader.FieldCount + ").");
// If not allowed, the number of properties with the position attribute cannot be less than the column count, meaning no column should be ignored.
}
else if (!allowUnmappedColumns && properties.Length < reader.FieldCount)
{
throw new Exception("Hydrator.BuildMappings<T>() - number of settable properties with a position attribute in " + typeof(T).FullName
+ "(" + properties.Length + ") is less than the number of columns the reader(" + reader.FieldCount + "), meaning a column has been unmapped.");
}
}
List <ColumnMapping> mappings = new List <ColumnMapping>(); // holds all column mappings for a result set
for (int c = 0; c < reader.FieldCount; c++) // loop reader columns
{
PropertyInfo property;
// map by position works only with properties (not fields)
if (mapByPosition)
{
if (c > properties.Length - 1)
{
break; // there are more columns than map properties, so our mapping is complete
}
else
{
property = properties[c]; // get the next property to be mapped
}
// Always check property position against column position at this point since the properties are sorted. If they
// don't match, something is definitely wrong in the attribute settings.
int propertyMapPosition = ((HydratorMapAttribute)Attribute.GetCustomAttribute(property, typeof(HydratorMapAttribute))).Position;
if (c != propertyMapPosition)
{
throw new Exception("Hydrator.BuildMappings<T>() - property map position mismatch with reader columns near property position " + propertyMapPosition.ToString()
+ " on class " + typeof(T).FullName + "." + " Check for duplicate or missing position values on properties.");
}
// valid property found, add completed mapping to our list
mappings.Add(new ColumnMapping(new Column(reader.GetName(c), reader.GetProviderSpecificFieldType(c), reader.GetDataTypeName(c)), property));
// mapping by name
}
else
{
// look first for an _underscorePrefixedCamelCase field, and then a camelCase field (both are non-public)
FieldInfo field = Array.Find(fields, f => f.Name == CaseConverter.ConvertSnakeCaseToCamelCasePrefixedWithUnderscore(reader.GetName(c)))
?? Array.Find(fields, f => f.Name == CaseConverter.ConvertSnakeCaseToCamelCase(reader.GetName(c)));
if (field != default(FieldInfo))
{
// valid field found, add completed column mapping to our list
mappings.Add(new ColumnMapping(new Column(reader.GetName(c), reader.GetProviderSpecificFieldType(c), reader.GetDataTypeName(c)), field));
}
else // otherwise, look for a PascalCase property (public) since field not found
{
property = Array.Find(properties, p => p.Name == CaseConverter.ConvertSnakeCaseToPascalCase(reader.GetName(c)));
if (property != default(PropertyInfo)) // this is equivalent to a "not null" compare
// valid property found, add completed mapping to our list
{
mappings.Add(new ColumnMapping(new Column(reader.GetName(c), reader.GetProviderSpecificFieldType(c), reader.GetDataTypeName(c)), property));
}
else
{
// a property does not exist, so the column has neither property nor field to map to
if (allowUnmappedColumns)
{
continue; // unmapped column will be ignored - a "silent failed mapping"
}
else
{
throw new Exception("Hydrator.BuildMappings() - Could not find an _underscorePrefixedCamelCase non-public field, "
+ "a camelCase non-public field, nor a PascalCase public property on " + typeof(T).FullName + " for column " + reader.GetName(c));
}
}
} // if field not found
} // mapping by name
} // loop reader columns
return(mappings);
}
/// <summary>
/// Create dictionary of column name and Oracle type of all columns in reader
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
internal static List <Column> GetReaderColumnTypes(OracleDataReader reader)
{
List <Column> cols = new List <Column>();
for (int c = 0; c < reader.FieldCount; c++)
{
cols.Add(new Column(reader.GetName(c), reader.GetProviderSpecificFieldType(c), reader.GetDataTypeName(c)));
}
return(cols);
}
internal static IEnumerable<object[]> FetchRecordsFromReader(OracleDataReader reader, int rowCount, bool prefetch)
{
if (!CanFetchFromReader(reader))
{
yield break;
}
var fieldTypes = new string[reader.FieldCount];
var internalTypes = reader.GetInternalDataTypes();
for (var i = 0; i < reader.FieldCount; i++)
{
var fieldType = reader.GetDataTypeName(i);
fieldTypes[i] = internalTypes[i] == OracleRowId.InternalCode ? OracleRowId.TypeName : fieldType;
}
for (var i = 0; i < rowCount; i++)
{
if (!CanFetchFromReader(reader))
{
yield break;
}
object[] values;
try
{
if (reader.Read())
{
values = BuildValueArray(reader, fieldTypes, prefetch);
}
else
{
reader.Close();
yield break;
}
}
catch
{
if (!reader.IsClosed)
{
reader.Close();
}
throw;
}
yield return values;
}
}
public static void Main(string[] args)
{
// Build the connection string
OracleConnectionStringBuilder connBuilder = new OracleConnectionStringBuilder();
connBuilder.DataSource = String.Format("{0}:{1}/{2}", db_host, db_port, db_srvname);
connBuilder.UserID = db_username;
connBuilder.Password = db_password;
try
{
using (OracleConnection conn = new OracleConnection(connBuilder.ConnectionString))
{
// Connect the database
conn.Open();
Console.WriteLine("Data source: {0}", conn.DataSource);
Console.WriteLine("Server version: {0}", conn.ServerVersion);
Console.WriteLine("Connection timeout: {0}", conn.ConnectionTimeout);
Console.WriteLine("State: {0}", conn.State);
Console.WriteLine();
// UPDATE statement
string new_comment = "C# " + GetNow();
string sql0 = String.Format("update {0} set {1}=:{2} where {3}!=:{4}", db_table, db_update_column, db_update_column_variable, db_column, db_column_variable);
Console.WriteLine(sql0);
using (var cmd = new OracleCommand(sql0, conn))
{
OracleParameter par1 = new OracleParameter(db_update_column_variable, new_comment);
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter(db_column_variable, db_column_value);
cmd.Parameters.Add(par2);
int updatedRows = cmd.ExecuteNonQuery();
Console.WriteLine("Total updated rows: {0}", updatedRows);
}
Console.WriteLine();
// Full SELECT statement
string sql1 = String.Format("select * from {0}", db_table);
Console.WriteLine(sql1);
using (var cmd = new OracleCommand(sql1, conn))
using (OracleDataReader reader = cmd.ExecuteReader())
{
int columns = reader.FieldCount;
Console.WriteLine("Total columns: {0}", columns);
for (int ii = 0; ii < columns; ii++)
{
Console.WriteLine(" - {0} {1}", reader.GetName(ii), reader.GetDataTypeName(ii));
}
int number = 0;
while (reader.Read())
{
number++;
Console.Write(number);
for (int ii = 0; ii < columns; ii++)
{
string type = reader.GetDataTypeName(ii);
string value = "?";
if (!reader.IsDBNull(ii))
{
if (type.EndsWith("Varchar2"))
{
value = reader.GetString(ii);
}
else if (type.Equals("TimeStamp"))
{
value = reader.GetDateTime(ii).ToString();
}
else if (type.Equals("Int16"))
{
value = reader.GetInt16(ii).ToString();
}
else if (type.Equals("Int32"))
{
value = reader.GetInt32(ii).ToString();
}
}
else
{
value = "(null)";
}
Console.Write(" '{0}'", value);
}
Console.WriteLine();
}
}
Console.WriteLine();
// SELECT WHERE statement
string sql2 = String.Format("select count(*) from {0} where {1}!=:{2}", db_table, db_column, db_column_variable);
Console.WriteLine(sql2);
using (var cmd = new OracleCommand(sql2, conn))
{
OracleParameter par1 = new OracleParameter(db_column_variable, db_column_value);
cmd.Parameters.Add(par1);
Object result = cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
Console.WriteLine();
// SELECT package function statement
string sql3 = String.Format("select calculator.factorial(:{0}) from dual", db_factorial_variable);
Console.WriteLine(sql3);
using (var cmd = new OracleCommand(sql3, conn))
{
OracleParameter par1 = new OracleParameter(db_factorial_variable, db_factorial_value);
cmd.Parameters.Add(par1);
Object result = cmd.ExecuteScalar();
Console.WriteLine("Result: {0}", result);
}
Console.WriteLine();
// CALL package procedure statement
string sql4 = String.Format("calculator.add_and_subtract");
Console.WriteLine(sql4);
using (var cmd = new OracleCommand(sql4, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter par1 = new OracleParameter(db_add_and_subtract_a_variable, db_add_and_subtract_a_value);
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter(db_add_and_subtract_b_variable, db_add_and_subtract_b_value);
cmd.Parameters.Add(par2);
OracleParameter par3 = new OracleParameter(db_add_and_subtract_x_variable, OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add(par3);
OracleParameter par4 = new OracleParameter(db_add_and_subtract_y_variable, OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add(par4);
cmd.ExecuteNonQuery();
Console.WriteLine("Result x: {0}", cmd.Parameters[db_add_and_subtract_x_variable].Value);
Console.WriteLine("Result y: {0}", cmd.Parameters[db_add_and_subtract_y_variable].Value);
}
}
}
catch (OracleException oex)
{
Console.WriteLine("Oracle database error: {0}", oex.Message);
}
}
public void SaveShardAsCsv(Shard shard, string csvFolder, string csvSeparator, string encoding)
{
string stepid = Guid.NewGuid().ToString();
Logger.LogStepStart(stepid, shard.Name, "DOWNLOADING " + shard.Name);
Console.WriteLine($"Downloading {shard.Name} on thread {Thread.CurrentThread.ManagedThreadId}");
OracleConnection oraCon = new OracleConnection(this.oracleSourceConnectionString);
oraCon.Open();
OracleCommand cmd = new OracleCommand(shard.Sql, oraCon);
OracleDataReader reader = cmd.ExecuteReader();
string path = csvFolder + shard.TableName + "\\";
string fileName = path + "\\" + shard.Name + ".csv";
Directory.CreateDirectory(path);
StreamWriter sw = null;
if (encoding == "UTF8")
{
sw = new StreamWriter(fileName, false, Encoding.UTF8);
}
else
{
sw = new StreamWriter(fileName, false, Encoding.Unicode);
}
object[] output = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
output[i] = reader.GetName(i);
}
sw.WriteLine(string.Join(",", output));
while (reader.Read())
{
reader.GetValues(output);
string row = "";
int counter = 0;
foreach (object o in output)
{
string val = "";
if (reader.GetDataTypeName(counter) == "varchar" || reader.GetDataTypeName(counter) == "nvarchar")
{
val = "\"" + o.ToString().Replace("\"", "\"\"") + "\"";
}
else if (o.GetType() == typeof(bool))
{
val = "\"" + (bool.Parse(o.ToString()) == false ? 0 : 1).ToString() + "\"";
}
else if (reader.GetDataTypeName(counter) == "Double")
{
val = "\"" + o.ToString().Replace(",", ".") + "\"";
}
else if (reader.GetDataTypeName(counter) == "Decimal")
{
val = "\"" + o.ToString().Replace(",", ".") + "\"";
}
else if (reader.GetDataTypeName(counter) == "Numeric")
{
val = "\"" + o.ToString().Replace(",", ".") + "\"";
}
else if (o.GetType() == typeof(byte[]))
{
val = "\"\"";
}
else
{
val = o.ToString().Replace(",", ".").Replace("\"", "");
}
if (counter++ != output.Length - 1)
{
val += csvSeparator;
}
row += val;
}
sw.WriteLine(row);
}
sw.Flush();
Log.CsvBytesWritten += sw.BaseStream.Length;
sw.Close();
reader.Close();
oraCon.Close();
Logger.LogStepEnd(stepid);
}
public string GetDataTypeName(int i)
{
return(reader.GetDataTypeName(i));
}
//
//
// with a sql statement
// and a connection string
// return the result on a dynamic list
// plus a string with
// pos1 error 0-ok 1-error
// pos2 list empty 0-ok 1-list is empty
// pos3... message return code from non-select or error message
public static List<dynamic> sqldo
(string pi_sql
, string pi_connstring
, out string msgout
)
{
// variables
string sql = pi_sql;
var lista = new List<dynamic>();
int retcode;
msgout = "0";
//
string ConnString = pi_connstring;
//
//
//
// Microsoft SqlServer
if (SqlFlavor == "Ms")
{
using (SqlConnection con = new SqlConnection(ConnString))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
if (sqltype == "R")
{
SqlDataReader reada = cmd.ExecuteReader();
string datatype = "-";
string colname = "-";
while (reada.Read())
{
var obj = new ExpandoObject();
var d = obj as IDictionary<String, object>;
//
for (int index = 0; index < reada.FieldCount; index++)
{
datatype = reada.GetDataTypeName(index);
colname = reada.GetName(index);
bool isnul = reada.IsDBNull(index);
if (!isnul)
{
// add datatypes as needed
switch (datatype)
{
case "int":
d[colname] = reada.GetValue(index);
break;
case "varchar":
d[colname] = reada.GetString(index);
break;
case "nvarchar":
d[colname] = reada.GetString(index);
break;
case "date":
d[colname] = reada.GetDateTime(index);
break;
default:
d[colname] = reada.GetString(index);
break;
}
}
else
{
d[colname] = "";
}
}
lista.Add(obj);
}
reada.Close();
}
}
catch (Exception ex)
{
msgout = "11" + ex.Message.ToString();
}
}
}
//
// Oracle
if (SqlFlavor == "Oa")
{
// Or uses a "
sql = sql.Replace("[", "\"");
sql = sql.Replace("]", "\"");
using (OracleConnection con = new OracleConnection(ConnString))
{
try
{
con.Open();
//
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader reada = cmd.ExecuteReader();
string datatype = "-";
string colname = "-";
while (reada.Read())
{
var obj = new ExpandoObject();
var d = obj as IDictionary<String, object>;
// browse every column
for (int index = 0; index < reada.FieldCount; index++)
{
datatype = reada.GetDataTypeName(index);
colname = reada.GetName(index);
bool isnul = reada.IsDBNull(index);
if (!isnul)
{
// add datatypes as needed
switch (datatype)
{
case "Decimal":
d[colname] = reada.GetValue(index);
break;
case "Varchar":
d[colname] = reada.GetString(index);
break;
default:
d[colname] = reada.GetString(index);
break;
}
}
else
{
d[colname] = "";
}
}
lista.Add(obj);
}
reada.Close();
//
}
catch (Exception ex)
{
msgout = "11" + ex.Message.ToString();
}
}
}
//
//
//
return lista;
}
static void Main(string[] args)
{
StreamWriter
fstr_out = null;
OracleConnection
conn = null;
OracleCommand
cmd=null;
OracleDataAdapter
da=null;
OracleDataReader
rdr = null;
OracleTransaction
tr = null;
DataTable
tmpDataTable = null;
object
tmpObject;
string
FieldName,
tmpString;
long
tmpLong;
int
tmpInt,
i;
bool
tmpBool;
DateTime
tmpDateTime;
try
{
try
{
fstr_out = new StreamWriter("log.log", false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
string
ConnectionString = "User ID=corp_yk_user;Password=google;Data Source=sm";
//ConnectionString = "User ID=aspnetuser;Password=aspnet;Data Source=ASM";
//ConnectionString = "User ID=sqlpretension;Password=impression;Data Source=SM";
conn = new OracleConnection(ConnectionString);
#if TEST_DBMS_OUTPUT
conn.StateChange += new StateChangeEventHandler(conn_StateChange);
conn.InfoMessage += new OracleInfoMessageEventHandler(conn_InfoMessage);
#endif
conn.Open();
#if TEST_NULL
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from testtabletypes";
rdr=cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
int
idx = rdr.GetOrdinal(FieldName = "FINT");
while (rdr.Read())
{
tmpInt = !rdr.IsDBNull(idx) ? Convert.ToInt32(rdr[idx]) : int.MinValue;
tmpInt = rdr[idx] != DBNull.Value ? Convert.ToInt32(rdr[idx]) : int.MaxValue;
tmpInt = !rdr[idx].Equals(DBNull.Value) ? Convert.ToInt32(rdr[idx]) : int.MinValue;
tmpInt = !Convert.IsDBNull(rdr[idx]) ? Convert.ToInt32(rdr[idx]) : int.MaxValue;
}
}
} while (rdr.NextResult());
rdr.Close();
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
foreach (DataRow r in tmpDataTable.Rows)
{
tmpInt = !r.IsNull(FieldName = "FINT") ? Convert.ToInt32(r[FieldName]) : int.MinValue;
tmpInt = r[FieldName] != DBNull.Value ? Convert.ToInt32(r[FieldName]) : int.MaxValue;
tmpInt = !r[FieldName].Equals(DBNull.Value) ? Convert.ToInt32(r[FieldName]) : int.MinValue;
tmpInt = !Convert.IsDBNull(r[FieldName]) ? Convert.ToInt32(r[FieldName]) : int.MaxValue;
}
#endif
#if TEST_NLS_NUMERIC_CHARACTERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select cast('0,123' as number(10,6)) from dual";
tmpObject = cmd.ExecuteScalar();
#endif
#if CAST_VS_CONVERT
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from dual";
try
{
tmpInt = (int)cmd.ExecuteScalar();
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
tmpObject = cmd.ExecuteScalar(); // decimal
tmpInt = Convert.ToInt32(tmpObject);
tmpObject = cmd.ExecuteOracleScalar(); // OracleNumber
OracleNumber
tmpOracleNumber = (OracleNumber)tmpObject;
#endif
#if TEST_TYPES
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
tmpDateTime = DateTime.Now;
cmd.CommandText = @"
update
testtabletypes
set
ftimestamp=:ftimestamp_in,
ftimestamp_w_tz=:ftimestamp_w_tz_in,
ftimestamp_w_l_tz=:ftimestamp_w_l_tz_in
";
cmd.Parameters.Clear();
cmd.Parameters.Add("ftimestamp_in", OracleType.Timestamp).Value = tmpDateTime;
cmd.Parameters.Add("ftimestamp_w_tz_in", OracleType.TimestampWithTZ).Value = tmpDateTime;
cmd.Parameters.Add("ftimestamp_w_l_tz_in", OracleType.TimestampLocal).Value = tmpDateTime;
tmpInt=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "select * from TestTableTypes";
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
if (tmpDataTable.Rows.Count > 0)
{
tmpObject = tmpDataTable.Rows[0]["FNumber_Asterisk_0"];
if(!Convert.IsDBNull(tmpObject)) // System.InvalidCastException: Object cannot be cast from DBNull to other types.
tmpBool = Convert.ToBoolean(tmpObject);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateTestTableTypes";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["ID_IN"].Value = 1;
//cmd.Parameters["FCLOB_IN"].Value = new string('я', 2001); //"CLOB";
//cmd.Parameters["FNCLOB_IN"].Value = new string('я', 2001); //"NCLOB";
//cmd.Parameters["FSMALLINT_IN"].Value = false;
//cmd.Parameters["FINT_IN"].Value = Enum4TestTypes.Second;
cmd.ExecuteNonQuery();
#endif
#if TEST_DBMS_OUTPUT
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TestProcedureWithDBMSOutput";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
#endif
#if TEST_CLOB
if (cmd == null)
cmd = conn.CreateCommand();
//tmpString = "<P style=\"FONT-SIZE: large\" align=center>Спасибо, что пользуетесь нашей системой заказов!</P><P style=\"FONT-SIZE: large\" align=center>С 3 декабря изменился интерфейс \"онлайн заказов\" для клиентов компании.</P><P style=\"FONT-SIZE: large\" align=center>Просьба о всех пожеланиях и возможных проблемах сообщать на эл. адрес <A href=\"mailto:[email protected]\">[email protected]</A></P><P>Для создания заказа воспользуйтесь пунктом меню \"Каталог товаров\", либо загрузите заказ из <IMG src=\"http://corp.mtr.com.ua/OrdersApp/images/excel.gif\"> Excel через меню \"Загрузить заказ\"</P><P>Более подробно о том, как делать заказы и пользоваться системой вы можете почитать в <A href=\"http://corp.mtr.com.ua/OrdersApp/client/manual/help%20b2b.doc\">документации</A>.</P><P align=center><B>Уважаемые партнёры:</B></P><P align=center>Не упустите возможность закупить продукцию «Медиа Трейдинг» по очень хорошим ценам.</P><P align=center>Только раз в неделю, каждую пятницу, мы предлагаем специальные цены на несколько позиций из своего ассортимента. Размер партии может быть любой. Условие только одно: <B>Полная оплата закупки в тот же день*.</B><BR><BR>Способ оплаты может быть любой.<BR><SPAN style=\"COLOR: rgb(255,0,0); FONT-WEIGHT: bold\">ВНИМАНИЕ: АССОРТИМЕНТ ТОВАРОВ ОБНОВЛЯЕТСЯ КАЖДЫЙ ЧЕТВЕРГ В 16:00.</SPAN><BR></P><P><B>*Пояснение к условию:</B><BR>Вы можете получить товар по объявленной цене при условии оплаты в этот же день.<BR>Это значит, что нужно <B>«принести» (любым способом: безнал, предоплата) деньги в офис или представительство компании с 9:00 до 17:00 пятницы.</B> При этом <B>у Вас не должно быть вообще никаких задолженностей перед компанией, <U>независимо от размеров и срока давности</U>. Баланс либо нулевой, либо плюс.</B><BR><B><U>Без подтверждения оплаты товар отгружен не будет!</U></B><BR><BR>В случае, если товара нет на складе представительства, заказ будет доставлен с центрального склада ближайшей отгрузкой.</P><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p>";
//tmpString = new string('я', 2000);
tmpString = new string('я', 2001);
//tmpString = new string('я', 3000);
//tmpString = new string('я', 3999);
//tmpString = new string('я', 4000);
//tmpString = new string('я', 4001);
//tmpString = new string('я', 0x0ffff);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update TestTableTypes set FClob = :FClob, FNClob = :FNClob";
//cmd.Parameters.Add(":FClob", OracleType.Clob).Value = tmpString;
//cmd.Parameters.Add(":FNClob", OracleType.NClob).Value = tmpString;
//cmd.Parameters.AddWithValue(":FClob", tmpString);
//cmd.Parameters.AddWithValue(":FNClob", tmpString);
OracleParameter
p;
p = new OracleParameter();
p.ParameterName = ":FClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);
p = new OracleParameter();
p.ParameterName = ":FNClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);
tmpInt=cmd.ExecuteNonQuery();
#endif
#if TEST_EXECUTE_SCALAR
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from user_objects";
tmpObject = cmd.ExecuteScalar();
// tmpInt=(int)tmpObject; // Wrong!!!
tmpInt = Convert.ToInt32(tmpObject);
#endif
#if TEST_FUNCTION
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "get_actions_count";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["application_id_in"].Value = 128;
cmd.Parameters["date_actual"].Value = new DateTime(1900, 1, 1);
tmpObject = cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value);
cmd.CommandText = "get_zonder_duration";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["volume_in"].Value = 0.1;
tmpObject = cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value);
#endif
#if GET_STORED_PROCEDURE_PARAMETERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.utilfunctions.updategextra"; // "typhoon.Utilization_Util.ImportUtilization"; //"typhoon.pkg_claim.get_doc_status"; // "typhoon.movegoodstoresrevforinetf"; //"typhoon.setdocpropvalue"; // "typhoon.GET_URL_OPENDOC"; //"typhoon.HO_Utils.makeDocument";
OracleCommandBuilder.DeriveParameters(cmd);
Console.WriteLine(cmd.CommandText);
for (int ii = 0; ii < cmd.Parameters.Count; ++ii)
Console.WriteLine("ParameterName: " + cmd.Parameters[ii].ParameterName + " OracleType: " + cmd.Parameters[ii].OracleType.ToString() + " Direction: " + cmd.Parameters[ii].Direction.ToString());
Console.ReadLine();
#endif
#if TEST_SEND_E_MAIL
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.mail_task_util.sendmail"; //"typhoon.mail_task_util.sendmail2";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["M_SENDER"].Value = "*****@*****.**"; //"*****@*****.**"; // "*****@*****.**"
cmd.Parameters["M_RECIPIENT"].Value = "[email protected]; [email protected]"; // "*****@*****.**";
cmd.Parameters["M_SUBJECT"].Value = "Тест e-mail посредством оракула";
cmd.Parameters["M_MESSAGE"].Value = "<p><center><img src=\"http://b2b.mtr.com.ua/images/logo.gif\" /></center></p><p><center><b>Дистрибуция Медиа Продукции</b></center></p><p>Тест e-mail посредством оракула <a href=\"http://google.com/\">google.com</a></p>";
//cmd.Parameters["M_CC_RECIPIENT"].Value = DBNull.Value;
//cmd.Parameters["M_BCC_RECIPIENT"].Value = DBNull.Value;
cmd.Parameters["M_ADDRDELIM"].Value = ";";
cmd.ExecuteNonQuery();
#endif
#if TEST_STORED_PROCEDURE_IN_CIRCLE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TableWithSequenceSave";
OracleCommandBuilder.DeriveParameters(cmd);
for (i = 0; i < 10; ++i)
{
cmd.Parameters["id_cur"].Value = 0;
cmd.Parameters["val_new"].Value = i;
cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["id_new"].Value);
}
#endif
#if TEST_BLOB
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
cmd.CommandText = "update TableWithBLOB set FBlob=:FBlob where Id=:Id";
cmd.Parameters.Clear();
cmd.Parameters.Add("Id", OracleType.Number).Value = 1;
cmd.Parameters.Add("FBlob", OracleType.Blob);
fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
fs.Close();
cmd.Parameters["FBlob"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
#if TEST_BLOB_SAVE_BY_SP
// http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
fs.Close();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob
tmpOracleLob;
tmpOracleLob = (OracleLob)cmd.Parameters["tempblob"].Value;
tmpOracleLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tmpOracleLob.Write(Blob, 0, Blob.Length);
tmpOracleLob.EndBatch();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TableWithBLOBSave";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["ID_IN"].Value = 2;
cmd.Parameters["FBLOB_IN"].Value = tmpOracleLob;
tmpInt = cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
cmd.CommandType = CommandType.Text;
#endif
cmd.CommandText = "select * from TableWithBLOB";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
tmpInt = rdr.GetOrdinal("FBlob");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
File.Delete(tmpString);
Blob = (byte[])rdr["FBlob"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
File.Delete(tmpString);
Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
} while (rdr.NextResult());
rdr.Close();
#endif
#if TEST_SELECT
/* if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType=CommandType.Text;
cmd.CommandText = @"
select
ew.ew_id as ""Id"",
ew.ew_name as ""Description""
from
typhoon.tbl_extra_what ew
where
(ew.ew_fs=typhoon.get_parent_cfo(:cfo))
and (coalesce(ew.ew_is_closed,0)=0)
order by ew.ew_id
";
cmd.Parameters.Clear();
cmd.Parameters.Add("cfo", OracleType.Number).Value = 150140;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
*/ /*
tmpObject = cmd.ExecuteScalar();
if (tmpObject != null && !Convert.IsDBNull(tmpObject))
tmpLong = Convert.ToInt64(tmpObject);
*/
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText="select * from tmp_docs_badrows";
cmd.Parameters.Clear();
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
if (tmpDataTable.PrimaryKey == null)
tmpDataTable.PrimaryKey = new DataColumn[] { tmpDataTable.Columns["doc_id"], tmpDataTable.Columns["g_id"] };
#endif
#if TEST_SELECTED_STORED_PROCEDURE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcedureForPaging";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["start_in"].Value = 2;
cmd.Parameters["limit_in"].Value = 2;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
FieldName="id";
foreach(DataRow r in tmpDataTable.Rows)
{
Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToInt32(r[FieldName]).ToString() : "null");
}
#endif
#if TEST_STORED_PROCEDURE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.GET_URL_OPENDOC";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["P_DOCID"].Value = 33711025;
cmd.Parameters["P_FULLHTMLCODE"].Value = DBNull.Value;
cmd.ExecuteNonQuery();
tmpString = Convert.ToString(cmd.Parameters["RETURN_VALUE"].Value);
#endif
#if TEST_MULTI_STATEMENTS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "alter session set NLS_TERRITORY = 'CIS';alter session set NLS_SORT = 'RUSSIAN';alter session set NLS_NUMERIC_CHARACTERS = '.,'";
cmd.CommandText = "begin execute immediate 'alter session set NLS_TERRITORY = ''CIS'''; execute immediate 'alter session set NLS_SORT = ''RUSSIAN'''; execute immediate 'alter session set NLS_NUMERIC_CHARACTERS = ''.,'''; end;";
cmd.ExecuteNonQuery();
#endif
#if TEST_PARAMETERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from dual where :a=:b";
cmd.Parameters.Add("a", OracleType.Int32).Value = 1;
cmd.Parameters.Add("b", OracleType.Int32).Value = 1;
//cmd.Parameters.Add("c", OracleType.Int32).Value = 2;
tmpObject = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.CommandText = "select k_id as id, k_name as val from typhoon.tbl_kontragents where k_name like :q order by k_name";
tmpString = "FOTO";
cmd.Parameters.Add("q", OracleType.NVarChar).Value = tmpString + "%";
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
cmd.Parameters.Clear();
cmd.CommandText = @"
select
hoty_id,
param_name,
param_value
from
order_params
where
(hoty_id = :hoty_id)
";
cmd.Parameters.Add("hoty_id", OracleType.Number).Value = 2219;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
FieldName = "param_name";
foreach (DataRow r in tmpDataTable.Rows)
{
Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToString(r[FieldName]).ToString() : "null");
}
#endif
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
if (rdr != null && !rdr.IsClosed)
rdr.Close();
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
if (fstr_out != null)
fstr_out.Close();
}
}
public void ExportToCSV_Zip(SqlDataSource dataSrc, string fileName)
{
string path = WebTools.SessionDataPath();
string excel_file = path + fileName + ".csv";
string zip_file = path + fileName + ".zip";
OracleConnection cn = new OracleConnection(dataSrc.ConnectionString);
string query =
dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");
OracleCommand cmd = new OracleCommand(query, cn);
StreamWriter sr = new StreamWriter(excel_file);
cmd.CommandTimeout = 999999;
cmd.CommandType = CommandType.Text;
try
{
cn.Open();
OracleDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
//CSV Header
for (int count = 0; count < dr.FieldCount; count++)
{
if (dr.GetName(count) != null)
{
sb.Append(dr.GetName(count));
}
if (count < dr.FieldCount - 1)
{
sb.Append(",");
}
}
sr.WriteLine(sb.ToString());
//CSV Body
while (dr.Read())
{
sb = new StringBuilder();
for (int col = 0; col < dr.FieldCount - 1; col++)
{
if (!dr.IsDBNull(col))
{
if (dr.GetDataTypeName(col).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(col).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
}
}
sb.Append(",");
}
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (dr.GetDataTypeName(dr.FieldCount - 1).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(dr.FieldCount - 1).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
}
}
}
sr.WriteLine(sb.ToString());
}
dr.Dispose();
sr.Close();
//ZipFile
using (ZipFile zip = new ZipFile())
{
zip.AddFile(excel_file, "CSV");
zip.Save(zip_file);
Response.AppendHeader("content-disposition", "attachment; filename=" + fileName + ".zip");
Response.ContentType = "application/zip";
Response.WriteFile(zip_file);
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
cmd.Connection.Close();
cn.Close();
sr.Dispose();
}
}
public override string GetDataTypeName(int ordinal)
{
return(source.GetDataTypeName(ordinal));
}
private static DataSet DebugFillDataSet(OracleCommand cmd)
{
_Logging.LogDebug("Entered DebugFillDataSet.");
OracleDataReader rdr = cmd.ExecuteReader();
_Logging.LogDebug("Executed command and returned DataReader.", 1);
DataSet ds = new DataSet("DebugSet");
DataTable dt = ds.Tables.Add("DebugTable");
_Logging.LogDebug("Created Debug DataSet and DataTable.", 1);
for (int i = 0; i < rdr.FieldCount; i++)
{
_Logging.LogDebug(string.Format("Column {0}: {1}, Type {2}.", i, rdr.GetName(i), rdr.GetDataTypeName(i)), 2);
dt.Columns.Add(rdr.GetName(i), rdr.GetFieldType(i));
}
while (rdr.Read())
{
DataRow dr = dt.NewRow();
_Logging.LogDebug(string.Format("Created row {0}, Size {1}.", dt.Rows.Count, rdr.RowSize), 1);
for (int col = 0; col < dt.Columns.Count; col++)
{
_Logging.LogDebug(string.Format("Setting column {0} ({1}) to {2}.", col, dt.Columns[col].ColumnName, rdr.GetValue(col)), 2);
dr.SetField(dt.Columns[col], rdr.GetValue(col));
}
dt.Rows.Add(dr);
_Logging.LogDebug(string.Format("Added row {0} to table.", dt.Rows.Count), 1);
}
return(ds);
}
/// <summary>
/// Write query results to json string or file
/// </summary>
/// <param name="command"></param>
/// <param name="output"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public static async Task <string> ToJsonAsync(this OracleCommand command, OutputProperties output, CancellationToken cancellationToken)
{
command.CommandType = CommandType.Text;
using (OracleDataReader reader = await command.ExecuteReaderAsync(cancellationToken) as OracleDataReader)
{
var culture = String.IsNullOrWhiteSpace(output.JsonOutput.CultureInfo) ? CultureInfo.InvariantCulture : new CultureInfo(output.JsonOutput.CultureInfo);
// utf-8 as default encoding
Encoding encoding = string.IsNullOrWhiteSpace(output.OutputFile?.Encoding) ? Encoding.UTF8 : Encoding.GetEncoding(output.OutputFile.Encoding);
// create json result
using (var fileWriter = output.OutputToFile ? new StreamWriter(output.OutputFile.Path, false, encoding) : null)
using (var writer = output.OutputToFile ? new JsonTextWriter(fileWriter) : new JTokenWriter() as JsonWriter)
{
writer.Formatting = Newtonsoft.Json.Formatting.Indented;
writer.Culture = culture;
// start array
await writer.WriteStartArrayAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
while (reader.Read())
{
// start row object
await writer.WriteStartObjectAsync(cancellationToken);
for (var i = 0; i < reader.FieldCount; i++)
{
// add row element name
await writer.WritePropertyNameAsync(reader.GetName(i), cancellationToken);
// add row element value
switch (reader.GetDataTypeName(i))
{
case "Decimal":
// FCOM-204 fix; proper handling of decimal values and NULL values in decimal type fields
OracleDecimal v = reader.GetOracleDecimal(i);
var FieldValue = OracleDecimal.SetPrecision(v, 28);
if (!FieldValue.IsNull)
{
await writer.WriteValueAsync((decimal)FieldValue, cancellationToken);
}
else
{
await writer.WriteValueAsync(string.Empty, cancellationToken);
}
break;
default:
await writer.WriteValueAsync(reader.GetValue(i) ?? string.Empty, cancellationToken);
break;
}
cancellationToken.ThrowIfCancellationRequested();
}
await writer.WriteEndObjectAsync(cancellationToken); // end row object
cancellationToken.ThrowIfCancellationRequested();
}
// end array
await writer.WriteEndArrayAsync(cancellationToken);
if (output.OutputToFile)
{
return(output.OutputFile.Path);
}
else
{
return(((JTokenWriter)writer).Token.ToString());
}
}
}
}
public static List <List <string> > ToTableList(this OracleDataReader reader)
{
//所有行
var rows = new List <List <string> >();
if (reader.HasRows)
{
while (reader.Read())
{
//逐行添加内容
var cols = new List <string>();
for (var i = 0; i < reader.FieldCount; i++)
{
var value = "";
#region 根据格式取值
var type = reader.GetDataTypeName(i).ToLower();
if (reader.IsDBNull(i))
{
value = "";
}
else if (type == ("smallint"))
{
value = reader.GetInt16(i) + "";
}
else if (type == ("int64"))
{
value = reader.GetInt64(i) + "";
}
else if (type.Contains("int"))
{
value = reader.GetInt32(i) + "";
//
//var v = reader.GetInt32(i);
//if (v > 1000000000)
//{
// value = reader.GetInt32(i).ToDateTime().ToStr();
//}
//else
//{
// value = reader.GetInt32(i) + "";
//}
}
else if (type.Contains("clob"))
{
value = reader.GetOracleClob(i).Value + "";
}
else if (type.Contains("float"))
{
value = reader.GetDouble(i) + "";
}
else if (type.Contains("datetime") || type.Contains("date"))
{
value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm") + "";
}
else if (type.Contains("char") || type.Contains("text") || type.Contains("varchar"))
{
value = reader.GetString(i) + "";
}
else if (type.Contains("decimal"))
{
value = reader.GetOracleDecimal(i) + "";
}
else if (type.Contains("date"))
{
var temp = reader.GetOracleDate(i);
if (!temp.IsNull)
{
value = temp.Value.FormatTime();
}
}
else
{
value = reader.GetString(i) + "";
// Replace("%", "");//2016-09-05 配合报表转义规则 2016-09-10 更新规则
//if (value.Contains("%"))
// throw new Exception("替换不完全！");
}
#endregion
cols.Add(value);
}
rows.Add(cols);
}
}
reader.Close();
reader.Dispose();
return(rows);
}
public IEnumerable <object> GetTable(String owner, String entityName, String condition)
{
List <object> results = new List <object>();
String whereClause = " WHERE ";
if (String.IsNullOrEmpty(condition))
{
whereClause = "";
}
else
{
whereClause += condition;
}
Assembly datacontractAssembly = this.GetType().Assembly;
Type entityType = datacontractAssembly.GetType("OracleDbCrawler.ObjectEntity." + entityName);
if (entityType == null)
{
foreach (Type t in datacontractAssembly.GetTypes())
{
//Logging.WriteLog(t.Name);
}
}
object[] tableAttributes = entityType.GetCustomAttributes(typeof(TableNameAttribute), false);
String tableName = "";
if (tableAttributes == null || tableAttributes.Count() != 1)
{
return(null);
}
tableName = (tableAttributes.First() as TableNameAttribute).TableName;
OracleConnection conn = Conn;
OracleCommand cmd = new OracleCommand(String.Format("SELECT * FROM {0}.{1}{2}", owner, tableName, whereClause), conn);
cmd.InitialLONGFetchSize = 1;
try
{
conn.Open();
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = Activator.CreateInstance(entityType);
for (int colIndex = 0; colIndex < reader.FieldCount; colIndex++)
{
string columnname = reader.GetName(colIndex);
PropertyInfo pi = entityType.GetProperties().ToList().Find(p =>
{
ColumnAttribute[] columns = p.GetCustomAttributes(typeof(ColumnAttribute), false) as ColumnAttribute[];
if (columns == null || columns.Count() == 0)
{
return(false);
}
if (columns[0].ColumnName == columnname)
{
return(true);
}
return(false);
});
if (!reader.IsDBNull(colIndex))
{
switch (reader.GetDataTypeName(colIndex))
{
case "Decimal": pi.SetValue(item, reader.GetDecimal(colIndex), null); break;
case "Date": pi.SetValue(item, reader.GetDateTime(colIndex), null); break;
case "Varchar2": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Raw":
byte[] data = new byte[16];
reader.GetBytes(colIndex, 0, data, 0, 16);
pi.SetValue(item, data, null); break;
case "NVarchar2": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Clob": pi.SetValue(item, reader.GetOracleClob(colIndex).Value, null); break;
case "Char": pi.SetValue(item, reader.GetString(colIndex), null); break;
case "Int16": pi.SetValue(item, reader.GetDecimal(colIndex), null); break;
case "Int64": pi.SetValue(item, reader.GetInt64(colIndex), null); break;
default: break;
}
}
}
results.Add(item);
}
}
catch (Exception ex)
{
// ex.Log();
}
finally
{
conn.Close();
}
return(results);
}