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