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