private byte[] FetchValue() { var buffers = new List <byte[]>(); var offset = 0; var totalSize = 0L; while (true) { var buffer = new byte[BufferSize]; var bytesLength = _reader.GetBytes(_columnIndex, offset, buffer, 0, BufferSize); totalSize += bytesLength; buffers.Add(buffer); if (bytesLength < BufferSize) { break; } offset += BufferSize; } var result = new byte[totalSize]; for (var i = 0; i < buffers.Count; i++) { Array.Copy(buffers[i], 0L, result, i * BufferSize, BufferSize); buffers[i] = null; } return(result); }
private void WritePage(OracleDataReader reader, string filename, string ImportFolder) { Trace.TraceInformation("Сохраняем файл в папку , его имя: " + filename); OracleBlob blob = reader.GetOracleBlob(0); byte[] buffer = new byte[blob.Length]; reader.GetBytes(0, 0, buffer, 0, buffer.Length); File.WriteAllBytes(ImportFolder + filename, buffer); if (filename.Substring(filename.Length - 4) == ".pdf" || filename.Substring(filename.Length - 4) == ".jpg") { } else { /*string swiftText = Encoding.UTF8.GetString(buffer); * * string[] real = new string[] { "ә", "ң", "ғ", "ү", "ұ", "қ", "ө", "һ", "Ә", "Ң", "Ғ", "Ү", "Ұ", "Қ", "Ө", "Һ" }; * string[] replaced = new string[] { "ј", "ѕ", "є", "ї", "ў", "ќ", "ґ", "ћ", "Ј", "Ѕ", "Є", "Ї", "Ў", "Ќ", "Ґ", "Ћ" }; * for (int i = 0; i < real.Length; i++) * { * swiftText = swiftText.Replace(real[i], replaced[i]); * } * Trace.TraceInformation("Swift to string: " + swiftText);*/ } }
public static void DownloadNew(string newfile) { SplashScreenManager.ShowDefaultWaitForm("请等待", "下载中...."); string sql = "select ufile from fv01 where verId=(select max(verId) from fv01)"; //从数据库取 OracleCommand cmd = new OracleCommand(sql, SqlAssist.conn); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess); int bufferSize = 10240; if (dr.Read()) { string path = Directory.GetCurrentDirectory() + "\\" + newfile + ".zip"; //需要预先在项目文件夹中建立此目录 FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write); byte[] buf = new byte[bufferSize]; long bytesRead = 0; long startIndex = 0; //LargePhoto的数据比较大,因此分批次读出,分别写入文件 while ((bytesRead = dr.GetBytes(0, startIndex, buf, 0, bufferSize)) > 0) { fs.Write(buf, 0, (int)bytesRead); startIndex += bytesRead; } ; fs.Flush(); fs.Close(); UnZip(path, newfile); File.Delete(path); SplashScreenManager.CloseDefaultWaitForm(); } }
public static void toTextFile(OracleConnection connection, String filename, String resultPath) { OracleCommand cmd = new OracleCommand(); cmd.Connection = connection; cmd.CommandText = "select filedata from blob_file where filename = :filename"; cmd.Parameters.Add(new OracleParameter("filename", filename)); OracleDataReader reader = cmd.ExecuteReader(); long CurrentIndex = 0; int BufferSize = 10; long BytesReturned; byte[] Blob = new byte[BufferSize]; while (reader.Read()) { FileStream fs = new FileStream(resultPath, FileMode.OpenOrCreate, FileAccess.Write); BinaryWriter writer = new BinaryWriter(fs); CurrentIndex = 0; BytesReturned = reader.GetBytes(0, CurrentIndex, Blob, 0, BufferSize); while (BytesReturned == BufferSize) { writer.Write(Blob); writer.Flush(); CurrentIndex += BufferSize; BytesReturned = reader.GetBytes(0, CurrentIndex, Blob, 0, BufferSize); } writer.Write(Blob, 0, (int)BytesReturned); writer.Flush(); writer.Close(); fs.Close(); Blob = new byte[BufferSize]; } reader.Close(); }
/// <summary> /// execute command, use for transform Blob to Byte in database /// </summary> /// <param name="cmd">the parameters of command statement</param> /// <returns>byte format binary stream</returns> protected byte[] BlobToByte(OracleCommand cmd) { long blobDataSize = 0; //BLOD full-size long readStartByte = 0; //where begin to write on Blod data int bufferStartByte = 0; // where begin to write on buffer array int hopeReadSize = 1024; //expected the size of every time read form BLOB long realReadSize = 0; // the actual size of every time read from BLOB //CommandBehavior.SequentialAccess make OracleDataReader load BLOB data as stream try { cmd.Connection = Conn; OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess); byte[] buffer = null; if (dr.Read()) { blobDataSize = dr.GetBytes(0, 0, null, 0, 0); //get the BLOB data of total size buffer = new byte[blobDataSize]; realReadSize = dr.GetBytes(0, readStartByte, buffer, bufferStartByte, hopeReadSize); while ((int)realReadSize == hopeReadSize)//use for cyclic reading 1024bytes { bufferStartByte += hopeReadSize; readStartByte += realReadSize; realReadSize = dr.GetBytes(0, readStartByte, buffer, bufferStartByte, hopeReadSize); } //read the rest of data which are less than 1024 bytes from BLOB //after reading complete , the BLOB data transform to buffer as binary dr.GetBytes(0, readStartByte, buffer, bufferStartByte, (int)realReadSize); } else { buffer = null; } return(buffer); } catch (OracleException ex) { throw new Exception(ex.Message); } finally { this.close(); } }
private static string GetBytes(int index) { var size = reader.GetBytes(index, 0, null, 0, 0); var destination = new MemoryStream(); var buffer = new byte[8040]; long offset = 0; long read; while ((read = reader.GetBytes(index, offset, buffer, 0, buffer.Length)) > 0) { offset += read; destination.Write(buffer, 0, (int)read); if (size == offset) { break; } } return(System.Convert.ToBase64String(destination.ToArray())); }
public byte[] RetrieveAttachment(string topic, int type, string uid, OracleConnection conn) { byte[] buffer = null; OracleCommand cmd = new OracleCommand("select data from SYSATTACHMEM1 where application='incidents' and topic=:topic and type=:type and \"UID\"=:UIDOVSC order by segment", conn); cmd.Parameters.Add(new OracleParameter("topic", topic)); cmd.Parameters.Add(new OracleParameter("type", type)); cmd.Parameters.Add(new OracleParameter("UIDOVSC", uid)); OracleDataReader dataReader = cmd.ExecuteReader(); MemoryStream attachmentStream = new MemoryStream(); //Because a attachment may store in multi segments, so we need to loop and read all the segments. while (dataReader.Read()) { long readStartByte = 0; int bufferStartByte = 0; int hopeReadSize = 20480; long blobDataSize = dataReader.GetBytes(0, 0, null, 0, 0); buffer = new byte[blobDataSize]; long realReadSize = dataReader.GetBytes(0, readStartByte, buffer, bufferStartByte, hopeReadSize); while ((int)realReadSize == hopeReadSize) { bufferStartByte += hopeReadSize; readStartByte += realReadSize; realReadSize = dataReader.GetBytes(0, readStartByte, buffer, bufferStartByte, hopeReadSize); } dataReader.GetBytes(0, readStartByte, buffer, bufferStartByte, (int)realReadSize); int offsetIndex = DBBlobUtil.GetOffsetIndex(buffer); //Write data to stream attachmentStream.Write(buffer, offsetIndex, buffer.Length - offsetIndex); } //Decompress the data, because in database the attachment data is compressed. byte[] decompressedData = DBBlobUtil.Decompress(attachmentStream.ToArray()); return(decompressedData); }
public byte[] ExceuteByteReader(string strQuery) { con = new OracleConnection(strOracleConn); OracleCommand cmd = new OracleCommand(); try { con.Open(); if (con.State == ConnectionState.Open) { cmd.Connection = con; cmd.CommandText = strQuery; OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); Byte[] byteDoc = new Byte[(dr.GetBytes(0, 0, null, 0, int.MaxValue))]; dr.GetBytes(0, 0, byteDoc, 0, byteDoc.Length); dr.Close(); return(byteDoc); } else { throw new Exception("database connection not found"); } } catch (Exception ex) { throw new Exception(ex.ToString() + "\n\n" + "Query:" + "\n" + strQuery); } finally { if (con.State == ConnectionState.Open) { con.Close(); } cmd = null; con = null; } }
/// <summary> /// 下载数据 /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>Byte【】</returns> public static Byte[] GetFromUpdateExeData(string FileName, int pos, int size) { using (OracleConnection connection = new OracleConnection(connectionStringOra)) { OracleCommand cmd = null; OracleDataReader reader = null; try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = "SELECT DATA FROM PDA_UPDATEEXETABLE WHERE Name=:Name "; OracleParameter par1 = new OracleParameter("Name", OracleType.NVarChar); par1.Value = FileName; cmd.Parameters.Add(par1); reader = cmd.ExecuteReader(); if (reader.Read() == true) { Byte[] bts = new Byte[size]; long readesize = reader.GetBytes(0, pos, bts, 0, size); if (readesize == size) { return(bts); } else { throw new Exception("数据读取异常"); } } else { throw new Exception("数据未查询到" + FileName); } } finally { if (reader != null) { reader.Close(); reader = null; } if (cmd != null) { cmd.Parameters.Clear(); cmd.Dispose(); cmd = null; } } } }
public Message GetById(string id) { Message msg = null; try { using (OracleCommand cmd = base.CurrentConnection.CreateCommand()) { cmd.CommandText = "SELECT ID_MAIL, MAIL_FILE, MAIL_SERVER_ID FROM MAIL_INBOX WHERE ID_MAIL = :p_id_mail"; cmd.Parameters.Add("p_id_mail", id); using (OracleDataReader r = cmd.ExecuteReader()) { while (r.Read()) { msg = new Message(); msg.Id = (int)r.GetInt64("ID_MAIL"); msg.Uid = r.GetString("MAIL_SERVER_ID"); long lobSize = r.GetBytes(r.GetOrdinal("MAIL_FILE"), 0, null, 0, 0); OracleClob file = r.GetOracleClob(r.GetOrdinal("MAIL_FILE")); msg.OriginalData = new byte[lobSize]; file.Read(msg.OriginalData, 0, (int)lobSize); } } } } catch (Exception e) { //TASK: Allineamento log - Ciro if (!e.GetType().Equals(typeof(ManagedException))) { ManagedException mEx = new ManagedException("Errore nel metodo GetById(string id). Dettaglio: " + e.Message, "E004", string.Empty, string.Empty, e.InnerException); ErrorLogInfo err = new ErrorLogInfo(mEx); //err.userID = id; _log.Error(err); } //ManagedException m = new ManagedException(e.Message, "E004", id, "GetById", e); //ErrorLog error = new ErrorLog(m); //_log.Error(error); } return(msg); }
public void DoTestTypes(DbTypeParametersCollection row) { testTypesInvocations++; exp = null; string rowId = "43966_" + this.testTypesInvocations.ToString(); OracleConnection con = null; OracleDataReader rdr = null; try { row.ExecuteInsert(rowId); row.ExecuteSelectReader(rowId, out rdr, out con); while (rdr.Read()) { //Run over all the columns in the result set row. //For each column, try to read it as a byte array. for (int i = 0; i < row.Count; i++) { if (row[i].Value.GetType() == typeof(byte[])) //The value in the result set should be a byte array. { try { BeginCase(string.Format("Calling GetBytes() on a field of dbtype {0}", row[i].DbTypeName)); byte[] origBytes = (byte[])row[i].Value; byte[] retBytes = new byte[origBytes.Length]; rdr.GetBytes(i, 0, retBytes, 0, origBytes.Length); Compare(origBytes, retBytes); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } } else //The value in the result set should NOT be byte array. In this case an Invalid case exception should be thrown. { try { BeginCase(string.Format("Calling GetBytes() on a field of dbtype {0}", row[i].DbTypeName)); byte[] retBytes = new byte[1]; rdr.GetBytes(i, 0, retBytes, 0, 1); ExpectedExceptionNotCaught("InvalidCastException"); } catch (InvalidCastException ex) { ExpectedExceptionCaught(ex); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } } } } } finally { row.ExecuteDelete(rowId); if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (con != null && con.State != ConnectionState.Closed) { con.Close(); } } }
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); }
private void loadEmployee() { textBox4.Text = id.ToString(); comboBox1.Enabled = false; string query = "SELECT * FROM employee WHERE eid = " + textBox4.Text; command = new OracleCommand(query, connection); connection.Open(); reader = command.ExecuteReader(); if (reader.HasRows) { reader.Read(); textBox1.Text = reader.GetString(1); textBox2.Text = reader.GetString(2); textBox3.Text = reader.GetString(3); if (reader.GetString(4).Equals("M")) { comboBox3.SelectedIndex = 0; } else if (reader.GetString(4).Equals("F")) { comboBox3.SelectedIndex = 1; } else if (reader.GetString(4).Equals("O")) { comboBox3.SelectedIndex = 2; } dateTimePicker1.Value = reader.GetDateTime(5); if (!DBNull.Value.Equals(reader.GetValue(6))) { textBox7.Text = reader.GetString(6); } if (!DBNull.Value.Equals(reader.GetValue(7))) { richTextBox1.Text = reader.GetString(7); } if (!DBNull.Value.Equals(reader.GetValue(8))) { textBox5.Text = reader.GetDecimal(8).ToString(); } if (reader.GetDecimal(9) == 1) //Wroking { comboBox2.SelectedIndex = 0; } else if (reader.GetDecimal(9) == 2)//Retired { comboBox2.SelectedIndex = 1; } else if (reader.GetDecimal(9) == 3)//Resigned { comboBox2.SelectedIndex = 2; } if (!DBNull.Value.Equals(reader.GetValue(11))) { byte[] imageBytes = new byte[10485760];//System.InvalidOperationException reader.GetBytes(11, 0, imageBytes, 0, 10485760); Bitmap image = (Bitmap)(new ImageConverter()).ConvertFrom(imageBytes); pictureBox1.Image = image; } if (reader.GetDecimal(10) == 1)//ADmin { comboBox1.SelectedIndex = 0; } else if (reader.GetDecimal(10) == 2)//Doctor { comboBox1.SelectedIndex = 1; query = "SELECT * FROM doctor WHERE eid = " + textBox4.Text; command = new OracleCommand(query, connection); reader = command.ExecuteReader(); reader.Read(); textBox6.Text = reader.GetString(1); textBox8.Text = reader.GetDecimal(2).ToString(); } else if (reader.GetDecimal(10) == 3)//Nurse { comboBox1.SelectedIndex = 2; query = "SELECT * FROM nurse WHERE eid = " + textBox4.Text; command = new OracleCommand(query, connection); reader = command.ExecuteReader(); reader.Read(); decimal nurse = reader.GetDecimal(1); decimal ward = reader.GetDecimal(2); var j = nurse_type.GetEnumerator(); int i = 0; while (j.MoveNext()) { if (j.Current == nurse) { comboBox5.SelectedIndex = i; break; } i++; } j = ward_id.GetEnumerator(); i = 0; while (j.MoveNext()) { if (j.Current == ward) { comboBox4.SelectedIndex = i; break; } i++; } connection.Close(); } else if (reader.GetDecimal(10) == 4)//Janitor { comboBox1.SelectedIndex = 3; query = "SELECT * FROM janitor WHERE eid = " + textBox4.Text; command = new OracleCommand(query, connection); reader = command.ExecuteReader(); reader.Read(); decimal ward = reader.GetDecimal(1); var j = ward_id.GetEnumerator(); int i = 0; while (j.MoveNext()) { if (j.Current == ward) { comboBox6.SelectedIndex = i; break; } i++; } connection.Close(); } } else { MessageBox.Show("Error While loading Data"); } connection.Close(); }
private void GetSwiftData(OracleDataReader reader, Document Doc) { Trace.TraceInformation("Начали фукнцию GetSwiftData(ListsReader, Doc)"); OracleBlob blob = reader.GetOracleBlob(0); byte[] buffer = new byte[blob.Length]; reader.GetBytes(0, 0, buffer, 0, buffer.Length); /*string encoding = string.Empty; * Stream fs = new FileStream(@"D:\Service.txt", FileMode.Open); * using (StreamReader sr = new StreamReader(fs, true)) * encoding = sr.CurrentEncoding.ToString(); * Trace.TraceInformation("Current encoding: " + encoding);*/ string swiftText = Encoding.Default.GetString(buffer); //string swiftText = Encoding.UTF8.GetString(buffer); if (swiftText.Contains("Ѓ") || swiftText.Contains("ђ")) { swiftText = Encoding.UTF8.GetString(buffer); } if (!swiftText.Contains("/NUM/")) { swiftText = Encoding.Unicode.GetString(buffer); } string[] real = new string[] { "ј", "ѕ", "є", "ї", "ў", "ќ", "ґ", "ћ", "Ј", "Ѕ", "Є", "Ї", "Ў", "Ќ", "Ґ", "Ћ" }; string[] replaced = new string[] { "ә", "ң", "ғ", "ү", "ұ", "қ", "ө", "һ", "Ә", "Ң", "Ғ", "Ү", "Ұ", "Қ", "Ө", "Һ" }; for (int i = 0; i < real.Length; i++) { swiftText = swiftText.Replace(real[i], replaced[i]); } Trace.TraceInformation("Переписали файл в стринг"); Trace.TraceInformation(swiftText); /*using (FileStream fstream = new FileStream(@"D:\Service.txt", FileMode.OpenOrCreate)) * { * // преобразуем строку в байты * byte[] array = System.Text.Encoding.Default.GetBytes(swiftText); * // запись массива байтов в файл * fstream.Write(array, 0, array.Length); * //Console.WriteLine("Текст записан в файл"); * }*/ string[] list; List <string> list1 = new List <string>(); string numberInList = ""; string sum = ""; string fm = ""; string nm = ""; string ft = ""; string dt = ""; string iik = ""; string iin = ""; string period = ""; string totalSum = ""; string valDate = ""; string fmAll = ""; //разделили основную часть и списки string[] main_plus_swift = swiftText.Split(new[] { ":21:" }, StringSplitOptions.None); for (int i = 1; i != main_plus_swift.Length; i++) { main_plus_swift[i] = ":21:" + main_plus_swift[i]; } Trace.TraceInformation("Разделили по :21:"); //Основной документ string[] mainDoc = main_plus_swift[0].Split(new[] { Environment.NewLine }, StringSplitOptions.None); Trace.TraceInformation("Разделили первую часть по строкам"); Trace.TraceInformation("Длина main_plus_swift: " + main_plus_swift.Length); //делим информацию по каждому человеку в списке. for (int i = 1; i != main_plus_swift.Length; i++) { Trace.TraceInformation("Я зашел в цикл по перебору списка"); list = main_plus_swift[i].Split(new[] { Environment.NewLine }, StringSplitOptions.None); Trace.TraceInformation("потом разделил Iтый список по строкам"); Trace.TraceInformation("list.length: " + list.Length); for (int j = 0; j != list.Length - 1; j++) { Trace.TraceInformation("Зашел в цикл по перебору строк в Iтом списке"); Trace.TraceInformation("list " + j + ": " + list[j] + ", "); //list[j].Substring(0, 4): :32B:KZT600,00 if (list[j].Substring(0, 1).Equals("-")) { break; } else if (list[j].Length < 4) { //пропуск строки, в которой меньше чем 4 знака. } else { if (list[j].Substring(0, 4).Equals(":21:")) { numberInList = list[j].Substring(4); } else if (list[j].Substring(0, 4).Equals(":32B")) { sum = list[j].Substring(8); } else if (list[j].Substring(0, 4).Equals("/FM/")) { fm = list[j].Substring(4); } else if (list[j].Substring(0, 4).Equals("/NM/")) { nm = list[j].Substring(4); } else if (list[j].Substring(0, 4).Equals("/FT/")) { ft = list[j].Substring(4); } else if (list[j].Substring(0, 4).Equals("/DT/")) { dt = list[j].Substring(4); // /DT/19900806 06.80.00 // /DT/1978924 4.24.89 // /DT/19770702 02.70.70 dt = dt.Substring(6) + "." + dt.Substring(4, 2) + "." + dt.Substring(2, 2); } else if (list[j].Substring(0, 4).Equals("/IDN")) { iin = list[j].Substring(5); } else if (list[j].Substring(0, 4).Equals("/PER")) { period = list[j].Substring(8); } else if (list[j].Substring(0, 4).Equals("/LA/")) { iik = list[j].Substring(4); } else if (list[j].Substring(0, 4).Equals(":32A")) { valDate = list[j].Substring(5, 6); valDate = valDate.Substring(4) + "." + valDate.Substring(2, 2) + "." + valDate.Substring(0, 2); totalSum = list[j].Substring(14); } Trace.TraceInformation(numberInList + " " + sum + " " + fm + " " + dt + " " + iin + " " + period); } } fmAll = fm + "+" + nm + "+" + ft; Trace.TraceInformation("Перед записывание в Док файл"); Doc.SwiftLists.Add(new SwiftList(numberInList, sum, fmAll, dt, iik, iin, period)); numberInList = ""; sum = ""; fm = ""; nm = ""; ft = ""; dt = ""; iik = ""; iin = ""; period = ""; fmAll = ""; Trace.TraceInformation("Записали Док файл"); } Trace.TraceInformation("Замутили список"); //работаем с основным документом string number = ""; string date = ""; string iinPayer = ""; string iikPayer = ""; string namePayer = ""; string bikPayer = ""; string bikRecip = ""; string iinRecip = ""; string iikRecip = ""; string nameRecip = ""; string assign = ""; string knp = ""; string irsRecip = ""; string secoRecip = ""; string irsPayer = ""; string secoPayer = ""; string kod = ""; string kbe = ""; string chief = ""; string mainbk = ""; string periodMain = ""; Trace.TraceInformation("Начали работу с main doc"); for (int i = 4; i != mainDoc.Length - 1; i++) { Trace.TraceInformation("mainDoc " + i + ": " + mainDoc[i]); if (mainDoc[i].Length >= 4) { if (mainDoc[i].Substring(0, 4).Equals("/NUM")) { number = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals(":70:") && mainDoc[i].Length != 4) { number = mainDoc[i].Substring(9); } else if (mainDoc[i].Substring(0, 4).Equals("/DAT")) { date = mainDoc[i].Substring(6); date = date.Substring(4) + "." + date.Substring(2, 2) + "." + date.Substring(0, 2); } else if (mainDoc[i].Substring(0, 4).Equals("/IDN") && iinPayer == "") { iinPayer = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals(":50:")) { iikPayer = mainDoc[i].Substring(7); } else if (mainDoc[i].Substring(0, 4).Equals("/NAM") && namePayer == "") { namePayer = mainDoc[i].Substring(6); } else if (mainDoc[i].Substring(0, 4).Equals(":52B")) { bikPayer = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals(":57B")) { bikRecip = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals("/IDN")) { iinRecip = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals(":59:")) { iikRecip = mainDoc[i].Substring(4); } else if (mainDoc[i].Substring(0, 4).Equals("/NAM")) { nameRecip = mainDoc[i].Substring(6); } else if (mainDoc[i].Substring(0, 4).Equals("/ASS")) { assign = mainDoc[i].Substring(8); } else if (mainDoc[i].Substring(0, 4).Equals("/KNP")) { knp = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals("/IRS") && irsPayer == "") { irsPayer = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals("/SEC") && secoPayer == "") { secoPayer = mainDoc[i].Substring(6); } else if (mainDoc[i].Substring(0, 4).Equals("/IRS")) { irsRecip = mainDoc[i].Substring(5); } else if (mainDoc[i].Substring(0, 4).Equals("/SEC")) { secoRecip = mainDoc[i].Substring(6); } else if (mainDoc[i].Substring(0, 4).Equals("/CHI")) { chief = mainDoc[i].Substring(7); } else if (mainDoc[i].Substring(0, 4).Equals("/MAI")) { mainbk = mainDoc[i].Substring(8); if (mainbk == "") { mainbk = "НЕ ПРЕДУСМОТРЕН"; } } else if (mainDoc[i].Substring(0, 4).Equals("/PER")) { periodMain = mainDoc[i].Substring(8); } else if (!mainDoc[i].Substring(0, 1).Equals("/") && !mainDoc[i].Substring(0, 1).Equals("{") && !mainDoc[i].Substring(0, 1).Equals(":")) { assign = assign + mainDoc[i]; } } else { assign = assign + mainDoc[i]; } Trace.TraceInformation(number + date + iinPayer + iikPayer + namePayer + bikPayer + iinRecip + iikRecip + nameRecip + assign + knp + kod + kbe + chief + mainbk); } kod = irsPayer + secoPayer; kbe = irsRecip + secoRecip; Trace.TraceInformation(kod + " " + kbe); Doc.Swift.Add(new Swift(number, date, iinPayer, iikPayer, namePayer, bikPayer, bikRecip, iinRecip, iikRecip, nameRecip, assign, knp, kod, kbe, chief, mainbk, totalSum, periodMain)); Trace.TraceInformation("Замутили освновной документ"); }
public ResultList <Message> GetAllMessageByAccount(string account, int da, int per) { ResultList <Message> listMex = null; using (OracleCommand oCmd = base.CurrentConnection.CreateCommand()) { StringBuilder sb = new StringBuilder("BEGIN"); sb.Append(" OPEN :cur_1 FOR SELECT count(*) FROM mail_inbox WHERE mail_account = :p_account;"); sb.Append(" OPEN :cur_2 FOR SELECT PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY msg_length ASC)") .Append(" FROM mail_inbox WHERE mail_account = :p_account;"); sb.Append(" END;"); oCmd.CommandType = System.Data.CommandType.Text; oCmd.CommandText = sb.ToString(); oCmd.BindByName = true; oCmd.Parameters.Add("p_account", account); oCmd.Parameters.Add("cur_1", OracleDbType.RefCursor, System.Data.ParameterDirection.Output); oCmd.Parameters.Add("cur_2", OracleDbType.RefCursor, System.Data.ParameterDirection.Output); int lobMaxSize = -1; try { oCmd.ExecuteNonQuery(); OracleDataReader r1 = ((OracleRefCursor)oCmd.Parameters["cur_1"].Value).GetDataReader(); r1.Read(); int tot = Convert.ToInt32(r1.GetDecimal(0)); OracleDataReader r2 = ((OracleRefCursor)oCmd.Parameters["cur_2"].Value).GetDataReader(); r2.Read(); lobMaxSize = (int)r2.GetDecimal(0); r1.Close(); r2.Close(); r1.Dispose(); r2.Dispose(); listMex = new ResultList <Message>(); listMex.Da = da; listMex.Per = (tot > per) ? per : tot; listMex.Totale = (tot > per) ? tot : per; if (tot == default(int)) { listMex.List = null; return(listMex); } } catch (Exception e) { //TASK: Allineamento log - Ciro if (!e.GetType().Equals(typeof(ManagedException))) { ManagedException mEx = new ManagedException("Errore nel metodo GetAllMessageByAccount(). Dettaglio: " + e.Message, "E002", string.Empty, string.Empty, e.InnerException); ErrorLogInfo err = new ErrorLogInfo(mEx); _log.Error(err); listMex = null; throw mEx; } //ManagedException m = new ManagedException(e.Message, "E002", account, "GetAllMessageByAccount", e); //ErrorLog error = new ErrorLog(m); //_log.Error(error); listMex = null; throw e; //throw m; } sb = new StringBuilder(); sb.Append("SELECT * FROM (") .Append("SELECT ID_MAIL, MAIL_SERVER_ID, MAIL_FILE, rownum as \"rn\" FROM MAIL_INBOX WHERE mail_account = :p_account order by id_mail") .Append(") WHERE \"rn\" between :p_da and :p_a"); oCmd.CommandText = sb.ToString(); oCmd.InitialLOBFetchSize = lobMaxSize; oCmd.BindByName = true; da = (da == 0) ? 1 : da; int a = da + per - 1; oCmd.Parameters.Add("p_da", da); oCmd.Parameters.Add("p_a", a); try { using (OracleDataReader r = oCmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) { if (r.HasRows) { listMex.List = new List <Message>(); byte[] buffer = null; long charRead, readStartIndex = 0, bufferStartIndex = 0; while (r.Read()) { Message mex = new Message(); mex.Id = r.GetInt32("ID_MAIL"); mex.Uid = r.GetString("MAIL_SERVER_ID"); long clobDataSize = r.GetBytes(0, 0, null, 0, 0); buffer = new byte[clobDataSize]; charRead = r.GetBytes(0, readStartIndex, buffer, (int)bufferStartIndex, BUFFER_SIZE); while (charRead == BUFFER_SIZE) { bufferStartIndex += BUFFER_SIZE; readStartIndex += charRead; charRead = r.GetBytes(0, readStartIndex, buffer, (int)bufferStartIndex, BUFFER_SIZE); } mex.OriginalData = buffer; listMex.List.Add(mex); } } } } catch (Exception e) { //TASK: Allineamento log - Ciro if (!e.GetType().Equals(typeof(ManagedException))) { ManagedException mEx = new ManagedException("Errore nel metodo GetAllMessageByAccount(). Dettaglio: " + e.Message, "E003", string.Empty, string.Empty, e.InnerException); ErrorLogInfo err = new ErrorLogInfo(mEx); //err.userID = account; _log.Error(err); listMex = null; throw mEx; } //ManagedException m = new ManagedException(e.Message, "E003", account, "GetAllMessageByAccount", e); //ErrorLog error = new ErrorLog(m); //_log.Error(error); listMex.List = null; throw e; } } return(listMex); }
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length) { return(reader.GetBytes(i, fieldOffset, buffer, bufferoffset, length)); }
private DownFileResult SoftUpDate(FileAdr Adress) { DownFileResult message = new DownFileResult(); try { string sqlType = Adress.sqlType; Stream Content; GetConfig(ref sqlType); Byte[] blob = null; DataSet dataSet = new DataSet(); string strSql = "select " + Adress.blobCont + " from " + Adress.dbName + " where " + Adress.primaryKey + " = " + Adress.keyValues; long num = 0; switch (sqlType.Trim().ToUpper()) { case "SQLSERVER": SvrCon.Open(); SqlCommand svrcmd = new SqlCommand(strSql, SvrCon); SqlDataReader svrsdr = svrcmd.ExecuteReader(); SvrCon.Close(); bool svrRead = svrsdr.Read(); blob = new Byte[(svrsdr.GetBytes(0, 0, null, 0, int.MaxValue))]; num = svrsdr.GetBytes(0, 0, blob, 0, blob.Length); svrsdr.Close(); if (num > 0) { Content = new MemoryStream(blob); message.FileStream = Content; message.FileSize = Content.Length; message.Message = num.ToString(); message.IsSuccess = true; } else { Content = null; message.Message = num.ToString(); message.IsSuccess = false; } break; case "ORACLE": OraCon.Open(); OracleCommand oracmd = new OracleCommand(strSql, OraCon); OracleDataReader orasdr = oracmd.ExecuteReader(); OraCon.Close(); bool oraRead = orasdr.Read(); blob = new Byte[(orasdr.GetBytes(0, 0, null, 0, int.MaxValue))]; num = orasdr.GetBytes(0, 0, blob, 0, blob.Length); orasdr.Close(); if (num > 0) { Content = new MemoryStream(blob); message.FileStream = Content; message.FileSize = Content.Length; message.Message = num.ToString(); message.IsSuccess = true; } else { Content = null; message.Message = num.ToString(); message.IsSuccess = false; } break; default: message.Message = "找不到所需的数据库连接类型"; message.IsSuccess = false; break; } return(message); } catch (Exception e) { error = e.Message; message.IsSuccess = false; message.Message = error; return(message); } }
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(); } }
private void listView1_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e) { if (!e.IsSelected) { return; } decimal id = decimal.Parse(e.Item.Text); try { string query = "SELECT * FROM PATIENT WHERE u_id = " + id; command = new OracleCommand(query, connection); command.CommandType = CommandType.Text; connection.Open(); reader = command.ExecuteReader(); while (reader.Read()) { textBox4.Text = reader.GetOracleNumber(0).ToString(); textBox1.Text = reader.GetString(1); textBox2.Text = reader.GetString(2); textBox3.Text = reader.GetString(3); dateTimePicker1.Value = new DateTime(reader.GetOracleDateTime(4).Year, reader.GetOracleDateTime(4).Month, reader.GetOracleDateTime(4).Day); richTextBox1.Text = reader.GetOracleString(5).ToString(); string gender = reader.GetOracleString(6).ToString(); if (gender.Equals("M")) { radioButton1.Checked = true; } else if (gender.Equals("F")) { radioButton2.Checked = true; } else if (gender.Equals("O")) { radioButton3.Checked = true; } textBox5.Text = reader.GetOracleNumber(7).ToString(); try { byte[] imageBytes = new byte[10485760];//System.InvalidOperationException reader.GetBytes(8, 0, imageBytes, 0, 10485760); Bitmap image = (Bitmap)(new ImageConverter()).ConvertFrom(imageBytes); pictureBox1.Image = image; } catch (System.InvalidOperationException) { pictureBox1.Image = pictureBox1.InitialImage; } } add = false; textBox4.Enabled = false; } catch (OracleException) { } finally { connection.Close(); reader.Close(); } }
public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) { return(source.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length)); }