protected override void RunIteration(Stopwatch sw)
{
this.TruncateTables();
sw.Start();
List <decimal> idList = new List <decimal>(this._rowCount);
List <OracleBlob> blobList = new List <OracleBlob>(this._rowCount);
for (int i = 0; i < this._rowCount; i++)
{
idList.Add(++this._id);
OracleBlob b = new OracleBlob(this._conn);
b.Write(this._blob, 0, this._blob.Length);
blobList.Add(b);
}
_idParam.Value = idList.ToArray();
_blobParam.Value = blobList.ToArray();
this._savePosition.ArrayBindCount = this._rowCount;
this._savePosition.ExecuteNonQuery();
sw.Stop();
}
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);*/
}
}
private void DGV_Equipes_SelectionChanged(object sender, EventArgs e)
{
image = null;
PB_Equipe.Image = null;
if (DGV_Equipes.SelectedRows.Count > 0)
{
OracleCommand oraImage = oracon.CreateCommand();
oraImage.CommandText = "SELECT Logo FROM Equipe WHERE NomEquipe=:NomEquipe";
oraImage.Parameters.Add(new OracleParameter(":NomEquipe", DGV_Equipes.SelectedRows[0].Cells[0].Value.ToString()));
using (OracleDataReader oraReader = oraImage.ExecuteReader())
{
if (oraReader.Read())
{
OracleBlob oraBlob = oraReader.GetOracleBlob(0); // à voir l'index du blob
if (!oraBlob.IsNull)
{
using (MemoryStream ms = new MemoryStream())
{
byte[] buffer = new byte[8 * 1024];
int read = 0;
while ((read = oraBlob.Read(buffer, 0, 8 * 1024)) > 0)
{
ms.Write(buffer, 0, read);
}
image = ms.ToArray();
PB_Equipe.Image = Image.FromStream(ms);
}
}
}
}
}
}
/// <summary>
/// Select an Oracle Blob value
/// </summary>
/// <param name="table"></param>
/// <param name="matchCol"></param>
/// <param name="typeCol"></param>
/// <param name="contentCol"></param>
/// <param name="matchVal"></param>
public static void SelectOracleBlob(
string table,
string matchCol,
string typeCol,
string contentCol,
string matchVal,
out string typeVal,
out byte[] ba)
{
typeVal = null;
ba = null;
string sql =
"select " + typeCol + ", " + contentCol + " " +
"from " + table + " " +
"where " + matchCol + " = :0";
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter(sql, 1);
for (int step = 1; step <= 2; step++) // try two different forms of matchVal
{
if (step == 2) // try alternate form of spaces
{
if (matchVal.Contains("%20"))
{
matchVal = matchVal.Replace("%20", " "); // convert html spaces to regular spaces
}
else
{
matchVal = matchVal.Replace(" ", "%20"); // convert regular spaces to html spaces
}
}
drd.ExecuteReader(matchVal);
if (!drd.Read())
{
continue;
}
typeVal = drd.GetString(0);
if (drd.Rdr.IsDBNull(1))
{
break;
}
OracleBlob ob = drd.OracleRdr.GetOracleBlob(1);
if (ob != null && ob.Length >= 0)
{
ba = new byte[ob.Length];
ob.Read(ba, 0, (int)ob.Length);
}
break; // have value
}
drd.Dispose();
return;
}
private void button2_Click(object sender, EventArgs e)
{
//We first read the full contents of the file into a byte array
byte[] _fileContents = System.IO.File.ReadAllBytes(txtFilepath.Text);
string _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
int _recordsAffected;
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "INSERT INTO ProductFiles(ProductID, FileAttachment) VALUES(:ProductID,:FileAttachment)";
_cmdObj.Parameters.Add(new OracleParameter("ProductID", txtProductID.Text));
OracleBlob _blob = new OracleBlob(_connObj);
_blob.Write(_fileContents, 0, _fileContents.Length);
_cmdObj.Parameters.Add(new OracleParameter("FileAttachment", _blob));
_recordsAffected = _cmdObj.ExecuteNonQuery();
if (_recordsAffected == 1)
{
MessageBox.Show("File uploaded!");
}
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private byte[] GetSinoSZDefineReport_Excel(MD_ReportItem _reportItem)
{
byte[] cbuffer = null;
using (OracleConnection cn = OracleHelper.OpenConnection())
{
long actual = 0;
OracleCommand _cmd = new OracleCommand();
_cmd.CommandText = SQL_GetSinoSZDefineReport_Excel;
_cmd.CommandType = CommandType.Text;
_cmd.Connection = cn;
_cmd.Parameters.Add(":BBMC", _reportItem.ReportName.ReportName);
_cmd.Parameters.Add(":TJDW", _reportItem.ReportDWID);
_cmd.Parameters.Add(":KSRQ", _reportItem.StartDate);
_cmd.Parameters.Add(":JZRQ", _reportItem.EndDate);
OracleDataReader myOracleDataReader = _cmd.ExecuteReader();
bool _readflag = myOracleDataReader.Read();
OracleBlob myOracleClob = myOracleDataReader.GetOracleBlob(0);
long lobLength = myOracleClob.Length;
cbuffer = new byte[lobLength];
actual = myOracleClob.Read(cbuffer, 0, cbuffer.Length);
myOracleDataReader.Close();
return(cbuffer);
}
}
private void BtnAfisareImagine_Click(object sender, EventArgs e)
{
try
{
con.Open();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
OracleCommand cmd = new OracleCommand("psAfisare", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("vid", OracleDbType.Int32);
cmd.Parameters.Add("flux", OracleDbType.Blob);
cmd.Parameters[0].Direction = ParameterDirection.Input;
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters[0].Value = Convert.ToInt32(txtBoxIdAfisare.Text);
try
{
cmd.ExecuteScalar();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
OracleBlob temp = (OracleBlob)cmd.Parameters[1].Value;
pictureBox1.Image = Image.FromStream((System.IO.Stream)temp);
con.Close();
}
public byte[] GetDownloadFileFromDocTable(string fileName)
{
string sql = "select blob_content from " + _dadConfig.DocumentTableName + " where name = :b1";
OracleCommand cmd = new OracleCommand(sql, _conn);
OracleParameter p = cmd.Parameters.Add("b1", OracleDbType.Varchar2, 256, fileName, ParameterDirection.Input);
logger.Debug("Executing SQL: " + cmd.CommandText);
try
{
OracleDataReader dr = cmd.ExecuteReader();
byte[] byteData = new byte[0];
while (dr.Read())
{
OracleBlob blob = dr.GetOracleBlob(0);
byteData = (byte[])blob.Value;
}
_lastError = "";
return(byteData);
}
catch (OracleException e)
{
logger.Error("Command failed: " + e.Message);
_lastError = e.Message;
return(new byte[0]);
}
}
private void btnDisplay_Click(object sender, System.EventArgs e)
{
// create our standard connection
string connStr = "User Id=oranetuser; Password=demo; Data Source=oranet";
OracleConnection con = new OracleConnection(connStr);
con.Open();
string sql = "select blob_data from blob_test where blob_id = 1";
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader dataReader = cmd.ExecuteReader();
// read the single row result
dataReader.Read();
// use typed accessor to retrieve the blob
OracleBlob blob = dataReader.GetOracleBlob(0);
// we are done with the reader now, so we can close it
dataReader.Close();
// create a memory stream from the blob
MemoryStream ms = new MemoryStream(blob.Value);
// set the image property equal to a bitmap
// created from the memory stream
pictureBox1.Image = new Bitmap(ms);
}
public List <MODEL.risk_project> getProjectList()
{
string sql = @"select * from RISK_PROJECT";
OracleParameter parameter = null;
try
{
using (OracleDataReader myReader = SqlHelper.ExecuteQuery(sql, parameter))
{
List <MODEL.risk_project> PrjList = new List <MODEL.risk_project>();
while (myReader.Read())
{
MODEL.risk_project project = new MODEL.risk_project();
project.Prj_id = Int32.Parse(myReader["PRJ_ID"].ToString());
project.Prj_name = myReader["PRJ_NAME"].ToString();
project.Prj_describe = myReader["PRJ_DESCRIBE"].ToString();
project.Prj_date = myReader.GetDateTime(3);
OracleBlob PrjXML = myReader.GetOracleBlob(4);
project.Prj_xml = Encoding.Default.GetString(PrjXML.Value);
PrjList.Add(project);
}
return(PrjList);
}
}
catch (System.Exception ex)
{
throw ex;
}
}
public byte[] GetXdbResourceFile()
{
logger.Debug("Getting XDB resource " + XdbResourceName);
string sql = "select xdburitype(:b1).getBlob() from dual";
OracleCommand cmd = new OracleCommand(sql, _conn);
OracleParameter p = cmd.Parameters.Add("b1", OracleDbType.Varchar2, 2000, XdbResourceName, ParameterDirection.Input);
logger.Debug("Executing SQL: " + cmd.CommandText);
try
{
OracleDataReader dr = cmd.ExecuteReader();
byte[] byteData = new byte[0];
while (dr.Read())
{
OracleBlob blob = dr.GetOracleBlob(0);
byteData = (byte[])blob.Value;
}
_lastError = "";
return(byteData);
}
catch (OracleException e)
{
logger.Error("Command failed: " + e.Message);
_lastError = e.Message;
return(new byte[0]);
}
}
public static byte[] ExecuteDataReader(string sql, string connStr, params OracleParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection(connStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
OracleDataReader reader = cmd.ExecuteReader();
MemoryStream ms = new MemoryStream();
if (reader.Read())
{
OracleBlob blob = (OracleBlob)reader.GetOracleBlob(0);
Byte[] buffer = new Byte[blob.Length];
blob.Read(buffer, 0, Convert.ToInt32(blob.Length));
ms.Write(buffer, 0, Convert.ToInt32(blob.Length));
blob.Close();
}
reader.Close();
ms.Position = 0;
byte[] result = new byte[ms.Length];
ms.Read(result, 0, result.Length);
return(result);
}
}
}
/// <summary>
/// Establece los parametros para la conexión actual. Es necesario para los valores de tipo BLOB
/// </summary>
/// <param name="parameters"></param>
/// <param name="context"></param>
/// <returns></returns>
private List <Oracle.DTO.OracleParameter> GetParametersForCurrentContext(List <Oracle.DTO.OracleParameter> parameters, OracleConnection context)
{
var finalParameters = new List <Oracle.DTO.OracleParameter>();
try
{
if (parameters != null)
{
for (int i = 0; i < parameters.Count; i++)
{
if (parameters[i].OracleDbType.Equals(Oracle.DTO.OracleDbType.Blob) &&
parameters[i].Value != null &&
parameters[i].Value.GetType().Equals(typeof(byte[])))
{
var value = (byte[])parameters[i].Value;
var blob = new OracleBlob(context);
blob.Erase();
blob.Write(value, 0, value.Length);
parameters[i] = new Oracle.DTO.OracleParameter(parameters[i].ParameterName, Oracle.DTO.OracleDbType.Blob, blob, parameters[i].Direction);
}
finalParameters.Add(parameters[i]);
}
}
}
catch (Exception er)
{
log.Error("GetParametersForCurrentContext()", er);
}
return(finalParameters);
}
private void EnsureLobIsNotNull()
{
if (lob != null)
{
return;
}
lob = new OracleBlob(connection);
}
public void OracleProcedure(string ConnectionString, string ProcedureName, string OutBlobParam)
{
OracleConnection OracleCon = new OracleConnection(ConnectionString);
//GIVE PROCEDURE NAME
OracleCommand cmd = new OracleCommand(ProcedureName, OracleCon);
cmd.CommandType = CommandType.StoredProcedure;
//ASSIGN PARAMETERS TO BE PASSED
//cmd.Parameters.Add("sdisk", OracleDbType.Char).Value = "D";
//cmd.Parameters.Add("PARAM2", OracleDbType.Varchar2).Value = "VAL2";
//THIS PARAMETER MAY BE USED TO RETURN RESULT OF PROCEDURE CALL
cmd.Parameters.Add(OutBlobParam, OracleDbType.Blob);
cmd.Parameters[OutBlobParam].Direction = ParameterDirection.Output;
//USE THIS PARAMETER CASE CURSOR IS RETURNED FROM PROCEDURE
//cmd.Parameters.Add("vCHASSIS_RESULT", OracleDbType.RefCursor, ParameterDirection.InputOutput);
try
{
OracleCon.Open();
//CALL PROCEDURE
OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();
/* 1й способ */
OracleBlob myBlob = (OracleBlob)(cmd.Parameters[OutBlobParam].Value);
byte[] MyData = new byte[myBlob.Length];
myBlob.Read(MyData, 0, (int)myBlob.Length);
FileStream fs = new FileStream(path + "\\" + Program.reportFileName, FileMode.Create, FileAccess.Write);
fs.Write(MyData, 0, (int)myBlob.Length);
fs.Close();
/* 2й способ */
//byte[] MyData = new byte[0];
//MyData = (byte[])((OracleBlob)(cmd.Parameters["bblob"].Value)).Value;
//int ArraySize = new int();
//ArraySize = MyData.GetUpperBound(0);
//FileStream fs = new FileStream(@"C:\report.xlsx", FileMode.Create, FileAccess.Write);
//fs.Write(MyData, 0, ArraySize);
//fs.Close();
}
catch (OracleException oe)
{
logger.Info("Ошибка подключения к БД Oracle." + '\n' + oe.Message);
}
finally
{
if (OracleCon != null)
{
OracleCon.Close();
OracleCon.Dispose();
}
}
}
private void DGV_Matchs_SelectionChanged(object sender, EventArgs e)
{
PB_EquipeHome.Image = null;
PB_EquipeAway.Image = null;
if (DGV_Matchs.SelectedRows.Count > 0)
{
OracleCommand oraImage = oracon.CreateCommand();
oraImage.CommandText = "SELECT (SELECT Logo FROM Equipe WHERE NomEquipe=:NomEquipe1), (SELECT Logo FROM Equipe WHERE NomEquipe=:NomEquipe2) FROM DUAL";
oraImage.Parameters.Add(new OracleParameter(":NomEquipe1", DGV_Matchs.SelectedRows[0].Cells[1].Value.ToString()));
oraImage.Parameters.Add(new OracleParameter(":NomEquipe2", DGV_Matchs.SelectedRows[0].Cells[2].Value.ToString()));
FillStats();
LB_NbButsHome.Text = DGV_Matchs.SelectedRows[0].Cells[5].Value.ToString();
LB_NbButsAway.Text = DGV_Matchs.SelectedRows[0].Cells[6].Value.ToString();
TB_Receveur.Text = DGV_Matchs.SelectedRows[0].Cells[1].Value.ToString();
TB_Visiteur.Text = DGV_Matchs.SelectedRows[0].Cells[2].Value.ToString();
using (OracleDataReader oraReader = oraImage.ExecuteReader())
{
if (oraReader.Read())
{
OracleBlob oraBlob = oraReader.GetOracleBlob(0);
if (!oraBlob.IsNull)
{
using (MemoryStream ms = new MemoryStream())
{
byte[] buffer = new byte[8 * 1024];
int read = 0;
while ((read = oraBlob.Read(buffer, 0, 8 * 1024)) > 0)
{
ms.Write(buffer, 0, read);
}
PB_EquipeHome.Image = Image.FromStream(ms);
}
}
oraBlob = oraReader.GetOracleBlob(1);
if (!oraBlob.IsNull)
{
using (MemoryStream ms = new MemoryStream())
{
byte[] buffer = new byte[8 * 1024];
int read = 0;
while ((read = oraBlob.Read(buffer, 0, 8 * 1024)) > 0)
{
ms.Write(buffer, 0, read);
}
PB_EquipeAway.Image = Image.FromStream(ms);
}
}
}
}
}
}
private void FreeLob()
{
if (lob == null)
{
return;
}
lob.Close();
lob.Dispose();
lob = null;
}
protected override void FromOracleParamInternal(OracleParameter param)
{
if (IsNull(param.Value) == true)
{
ParamValue = null;
}
else
{
OracleBlob blob = (OracleBlob)param.Value;
ParamValue = blob.Value;
}
}
protected override void CreateInternal(CommonDataContext context, DocumentData item)
{
item.Identifier = item.Identifier ?? Guid.NewGuid().ToString();
using (var blobContent = new OracleBlob(context.DbConnection as OracleConnection))
{
item.Content.CopyTo(blobContent);
context.DocumentDataCreate(item.Identifier, item.ContentType, item.Filename, item.IsTemporal, blobContent, out long documentId);
item.DocID = documentId;
}
}
private void loadImage(string albumId, string imageId)
{
try
{
// con = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString());
con.Open();
OracleDataReader dr;
//con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
String selectQuery = "";
selectQuery = "SELECT AA.PHOTO FROM AIS_IMAGES AA " +
" WHERE AA.ACCID=" + albumId + " AND AA.ID=" + imageId;
cmd.CommandText = selectQuery;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["PHOTO"] != System.DBNull.Value)
{
OracleBlob blob = dr.GetOracleBlob(0);
Response.ContentType = "image/jpeg";
Response.BinaryWrite(blob.Value);
Response.End();
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
private void FormEmployConsuInfo_Load(object sender, EventArgs e)
{
consumerTableAdapter1.Fill(dataSet11.CONSUMER);
ConsumerTable = dataSet11.Tables["consumer"];
fineTableAdapter1.Fill(dataSet11.FINE);
FineTable = dataSet11.Tables["fine"];
string str = "consu_id='" + name + "'";
DataRow[] foundRows = ConsumerTable.Select(str);
foreach (DataRow mydataRow in foundRows)
{
textBoxID.Text = mydataRow["consu_id"].ToString();
textBoxName.Text = mydataRow["consu_name"].ToString();
textBoxEmail.Text = mydataRow["consu_email"].ToString();
textBoxMileage.Text = mydataRow["consu_mileage"].ToString();
if (mydataRow["consu_blackconsumer"].ToString().Equals("T"))
{
checkBox1.Checked = true;
}
}
oracleConnection1.Open();
oracleCommand2.Connection = oracleConnection1;
oracleCommand2.CommandText = "SELECT * FROM ConsuPictures WHERE consu_id = '" + textBoxID.Text + "'";
OracleDataReader rdr = oracleCommand2.ExecuteReader();
if (rdr.HasRows) // 검색 결과가 있으면
{
while (rdr.Read())
{
//BLOB_DEMO (blob_id, file_name, media_file)
OracleBlob blobData = rdr.GetOracleBlob(2); // 0, 1, 2 즉 3번째 컬럼이 blob
byte[] conten = new Byte[blobData.Length];
int i = blobData.Read(conten, 0, Convert.ToInt32(blobData.Length));
System.IO.MemoryStream memStream = new System.IO.MemoryStream(conten);
// blob에서 가져온 이미지를 화면에 출력
pictureBox3.Image = Image.FromStream(memStream);
pictureBox3.SizeMode = PictureBoxSizeMode.StretchImage;
}
}
oracleConnection1.Close();
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
consumerTableAdapter1.Fill(dataSet11.CONSUMER);
ConsuTable = dataSet11.Tables["CONSUMER"];
string str = "consu_id='" + listBox1.Items[listBox1.SelectedIndex] + "'";
DataRow[] foundRows = ConsuTable.Select(str);
foreach (DataRow mydataRow in foundRows)
{
textBoxID.Text = mydataRow["consu_id"].ToString();
textBoxName.Text = mydataRow["consu_name"].ToString();
textBoxEmail.Text = mydataRow["consu_email"].ToString();
dateTimePickerBirth.Value = Convert.ToDateTime(mydataRow["consu_birth"]);
}
oracleConnection1.Open();
oracleCommand2.Connection = oracleConnection1;
oracleCommand2.CommandText = "SELECT * FROM ConsuPictures WHERE consu_id = '" + textBoxID.Text + "'";
OracleDataReader rdr = oracleCommand2.ExecuteReader();
if (rdr.HasRows) // 검색 결과가 있으면
{
while (rdr.Read())
{
//BLOB_DEMO (blob_id, file_name, media_file)
OracleBlob blobData = rdr.GetOracleBlob(2);
byte[] conten = new Byte[blobData.Length];
int i = blobData.Read(conten, 0, Convert.ToInt32(blobData.Length));
System.IO.MemoryStream memStream = new System.IO.MemoryStream(conten);
// blob에서 가져온 이미지를 화면에 출력
pictureBox4.Image = Image.FromStream(memStream);
pictureBox4.SizeMode = PictureBoxSizeMode.StretchImage;
}
}
else
{
pictureBox4.Image = System.Drawing.Image.FromFile("C:\\boss2.png");
}
oracleConnection1.Close();
}
public Oferta retornaOferta(int id)
{
Oferta oferta = new Oferta();
conn.Open();
DataSet ds = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd = new OracleCommand("SELECT * FROM oferta where idOferta=:id", conn);
cmd.Parameters.Add(new OracleParameter(":id", id));
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
OracleDataReader dr = cmd.ExecuteReader();
//byte[] ima = (byte[])cmd.ExecuteScalar();
while (dr.Read())
{
oferta.IdOferta = dr.GetInt32(0);
oferta.Nombre = String.Format("{0}", dr[1]);
oferta.Descripcion = String.Format("{0}", dr[2]);
oferta.PrecioNormal = dr.GetInt32(3);
oferta.PrecioOfeta = dr.GetInt32(4);
oferta.CantidadMin = dr.GetInt32(5);
oferta.CantidadMax = dr.GetInt32(6);
oferta.Producto.IdProducto = dr.GetInt32(7);
OracleBlob blob = dr.GetOracleBlob(8);
Byte[] Buffer = (Byte[])(dr.GetOracleBlob(8)).Value;
oferta.Imagen = Buffer;
oferta.Estado = String.Format("{0}", dr[9]);
}
conn.Close();
return(oferta);
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
using (var blob = new OracleBlob((OracleConnection)transaction.Connection))
{
await value.CopyToAsync(blob);
var command = (OracleCommand)transaction.AddCommand(@"begin
select db_lob_seq.nextval into :oid = next from dual;
insert into db_lob (oid, lob_data) values (:oid, :lob_data);
select :oid;");
var oidParameter = command.Parameters.Add(":oid", OracleDbType.Long);
oidParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(":lob_data", OracleDbType.Blob, -1).Value = blob;
await transaction.ExecuteQueryAsync(command, DBExecuteType.NoReader);
var oid = (long)oidParameter.Value;
return((uint)oid);
}
}
public List <Oferta> retornaOfertaPuublicadaList()
{
List <Oferta> list = new List <Oferta>();
conn.Open();
DataSet ds = new DataSet();
OracleCommand cmd = new OracleCommand();
cmd = new OracleCommand("SELECT * FROM oferta where estado = :estado", conn);
cmd.Parameters.Add(":estado", "Publicado");
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Oferta oferta = new Oferta();
oferta.IdOferta = dr.GetInt32(0);
oferta.Nombre = String.Format("{0}", dr[1]);
oferta.Descripcion = String.Format("{0}", dr[2]);
oferta.PrecioNormal = dr.GetInt32(3);
oferta.PrecioOfeta = dr.GetInt32(4);
oferta.CantidadMin = dr.GetInt32(5);
oferta.CantidadMax = dr.GetInt32(6);
OracleBlob blob = dr.GetOracleBlob(8);
Byte[] Buffer = (Byte[])(dr.GetOracleBlob(8)).Value;
oferta.Imagen = Buffer;
oferta.Estado = String.Format("{0}", dr[9]);
list.Add(oferta);
}
conn.Close();
return(list);
}
//procedura afisare
private void btnAfisareCaine_Click(object sender, EventArgs e)
{
btnConnection_Click(sender, e);
try
{
oracleConnection.Open();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
OracleCommand oracleCommand = new OracleCommand("PROCEDURA_AFISARE", oracleConnection);
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("v_id", OracleDbType.Int32);
oracleCommand.Parameters.Add("flux", OracleDbType.Blob);
oracleCommand.Parameters[0].Direction = ParameterDirection.Input;
oracleCommand.Parameters[1].Direction = ParameterDirection.Output;
oracleCommand.Parameters[0].Value = Convert.ToInt32(tbIdAfisareCaine.Text);
try
{
oracleCommand.ExecuteScalar();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
OracleBlob temp = (OracleBlob)oracleCommand.Parameters[1].Value;
pictureBox1.Image = Image.FromStream((System.IO.Stream)temp);
oracleConnection.Close();
}
public async Task <int> InsData(object parametros, string query, Dictionary <string, object> paramBlob)
{
try
{
var customParametros = (OracleDynamicParameters)parametros;
var rowsAffected = 0;
using (IDbConnection conn = GetConnection())
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
#region Blob
if (paramBlob != null && paramBlob.Count > 0)
{
//byte[] newvalue = System.Text.Encoding.Unicode.GetBytes("1");
//var blob = new OracleBlob((OracleConnection)conn);
//blob.Write(newvalue, 0, newvalue.Length);
//((OracleDynamicParameters)parametros).Add("adjunto", OracleDbType.Blob, ParameterDirection.Input, blob);
byte[] newvalue = (byte[])paramBlob["adjunto"];
var blob = new OracleBlob((OracleConnection)conn);
blob.Write(newvalue, 0, newvalue.Length);
customParametros.Add("adjunto", OracleDbType.Blob, ParameterDirection.Input, blob);
}
#endregion
}
if (conn.State == ConnectionState.Open)
{
rowsAffected = await SqlMapper.ExecuteAsync(conn, query, param : customParametros, commandType : CommandType.StoredProcedure);
}
return(rowsAffected);
}
}
catch (Exception err)
{
throw new Exception(err.Message);
}
}
static byte[] OracleLoadBlob(OracleConnection conn, int id, int startPos, int length)
{
using (OracleCommand cmd = new OracleCommand("BBL", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.FetchSize = 4 * 1024 * 1024;
OracleParameter idParam = new OracleParameter("p_ID", OracleDbType.Decimal);
idParam.Value = id;
cmd.Parameters.Add(idParam);
OracleParameter startPosParam = new OracleParameter("p_START_POS", OracleDbType.Int32);
startPosParam.Value = startPos + 1;
cmd.Parameters.Add(startPosParam);
OracleParameter lengthParam = new OracleParameter("p_LENGTH", OracleDbType.Int32);
lengthParam.Value = length;
cmd.Parameters.Add(lengthParam);
OracleParameter dataParam = new OracleParameter("p_DATA", OracleDbType.Blob);
dataParam.Direction = ParameterDirection.InputOutput;
dataParam.Value = new byte[1];
cmd.Parameters.Add(dataParam);
OracleParameter dataLengthParam = new OracleParameter("p_DATA_LENGTH", OracleDbType.Int32);
dataLengthParam.Value = length;
dataLengthParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dataLengthParam);
cmd.ExecuteNonQuery();
OracleBlob b = dataParam.Value as OracleBlob;
if (b != null && !b.IsNull)
{
int dataLength = ((OracleDecimal)dataLengthParam.Value).ToInt32();
byte[] result = b.Value;
return(dataLength == length ? result : OracleCopyBuffer(result, dataLength));
}
else
{
return(null);
}
}
}
static void Main(string[] args)
{
// create and open our standard connection
string connStr = "User Id=oranetuser; Password=demo; Data Source=oranet";
OracleConnection con = new OracleConnection(connStr);
con.Open();
// retrieve the blob from the database
string sql = "select blob_data from blob_test where blob_id = 1";
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader dataReader = cmd.ExecuteReader();
// this will hold the time taken
DateTime dtStart;
DateTime dtEnd;
double totalSeconds = 0;
dtStart = DateTime.Now;
// read the single row result
cmd.InitialLOBFetchSize = 32767;
dataReader.Read();
// use typed accessor to retrieve the blob
OracleBlob blob = dataReader.GetOracleBlob(0);
dtEnd = DateTime.Now;
// calculate the total time take to fetch
totalSeconds = dtEnd.Subtract(dtStart).TotalSeconds;
// we are done with the reader now, so we can close it
dataReader.Close();
// display some info about the time take to perform
// the operation
Console.WriteLine("Fetch time: {0} seconds.", totalSeconds.ToString());
}
private Bitmap ProductImage(string proSeqNo)
{
Bitmap result = null;
try
{
ProcParam param = new ProcParam(2)
{
ProcedureName = "INFO.MATERIAL_IMAGE"
};
param.AddParamReturn(0, "ReturnValue", OracleDbType.Blob, 255);
param.AddParamInput(1, "strNo", proSeqNo);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleBlob blobDB = (OracleBlob)param.ReturnValue(0);
if (!blobDB.IsNull)
{
using (MemoryStream ms = new MemoryStream(blobDB.Value))
{
result = (Bitmap)Bitmap.FromStream(ms);
}
return(result);
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw ex;
}
}
private static Stream CopyStream(Byte[] bytes, OracleCommand cmd)
{
OracleBlob ret = new OracleBlob(cmd.Connection);
ret.Write(bytes, 0, bytes.Length);
return ret;
}
/// <summary>
/// 利用流(Stream)写入单个大字段数据
/// </summary>
/// <param name="item">要写入的数据项目</param>
/// <param name="parameters">写入数据的参数</param>
/// <returns>写入流是否成功</returns>
public bool WriteSingleStream(string item,NameObjectList parameters)
{
string ls_key;
if(item!=this._item)
this.SetItem(item);
if(this._item.Length<1)
throw(new Exception("设置更新规则失败!"));
if(this._adapter.UpdateCommand==null)
throw(new Exception("没有设置更新大字段的规则"));
OracleCommand cmd=this._adapter.UpdateCommand;
OracleParameter param;Stream stream;
NameObjectList treams=new NameObjectList(); //大字段使用流
try
{
//大字段类型的数据是引用方式的赋值
_conn.Open();
for(int i=0;i<parameters.Count;i++)
{
ls_key=parameters.Keys[i];
param=cmd.Parameters[ls_key];
if(param==null) continue; //传入没有的参数时忽略
stream=parameters[ls_key] as Stream;
if(stream!=null) //如果是流,以大字段处理
{
byte[] lbtData = new byte[stream.Length];
OracleBlob blob=new OracleBlob(this._conn);
stream.Read(lbtData,0,System.Convert.ToInt32(stream.Length));
cmd.Parameters[i].Value=blob;
blob.Write(lbtData,0,System.Convert.ToInt32(stream.Length));
stream.Close();
}
else
OdpQueryDAO.ConvertParam(param,parameters[ls_key]);
}
cmd.ExecuteNonQuery();
}
catch ( Exception ex )
{
ExceptionManager.Publish( ex );
_conn.Close();
return false;
}
finally
{
_conn.Close();
}
return true;
}
