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());
}
}
/// <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);
}
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();
}
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);
}
}
public override void WriteValue(IDbCommand command, IDataParameter parameter, object value, DBColumn column)
{
base.WriteValue(command, parameter, value, column);
if (value == null)
{
if (column.IsPrimaryKey)
{
parameter.Direction = ParameterDirection.InputOutput;
}
return;
}
var dbParameter = (OracleParameter)parameter;
switch (column.DBDataType)
{
case DBDataType.ByteArray:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.Raw;
if (value != null)
{
dbParameter.Value = new OracleBinary((byte[])value);
}
break;
case DBDataType.Blob:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.Blob;
if (value != null)
{
var blob = new OracleBlob((OracleConnection)command.Connection);
blob.Write((byte[])value, 0, ((byte[])value).Length);
blob.Position = 0L;
dbParameter.Value = blob;
}
break;
case DBDataType.Clob:
dbParameter.Direction = ParameterDirection.Input;
dbParameter.OracleDbType = OracleDbType.NClob;
if (value != null)
{
var clob = new OracleClob((OracleConnection)command.Connection, false, true);
clob.Write(((string)value).ToCharArray(), 0, ((string)value).Length);
clob.Position = 0L;
dbParameter.Value = clob;
}
break;
case DBDataType.String:
dbParameter.Size = column.Size;
break;
case DBDataType.Decimal:
if (column.Size > 0)
{
dbParameter.Precision = (byte)column.Size;
}
if (column.Scale > 0)
{
dbParameter.Scale = (byte)column.Scale;
}
break;
case DBDataType.Float:
dbParameter.OracleDbType = OracleDbType.BinaryFloat;
break;
case DBDataType.Double:
dbParameter.OracleDbType = OracleDbType.BinaryDouble;
break;
case DBDataType.Bool:
dbParameter.DbType = DbType.Decimal;
dbParameter.Precision = 1;
dbParameter.Value = (bool)value ? 1 : 0;
break;
////case DBDataType.ShortInt:
//dbParameter.DbType = DbType.Decimal;
//dbParameter.Precision = 5;
//break;
//case DBDataType.Int:
//dbParameter.DbType = DbType.Decimal;
//dbParameter.Precision = 10;
//break;
}
}
private static Stream CopyStream(Byte[] bytes, OracleCommand cmd)
{
OracleBlob ret = new OracleBlob(cmd.Connection);
ret.Write(bytes, 0, bytes.Length);
return ret;
}
/// <summary>
///
/// </summary>
/// <param name="spName"></param>
/// <param name="Params"></param>
/// <param name="intLob_ParamCnt"></param>
/// <param name="fi"></param>
/// <param name="evtP"></param>
private void excute_StoredProcedure(string spName, OracleParameter[] Params, int intLob_ParamCnt, FileInfo fi, delExcuteProcedure_Progress evtP)
{
try
{
this.DB_isOpen();
if (spName.Trim() == "")
{
throw new Exception("프로시져 이름이 공백 입니다.");
}
Dbcmd.Parameters.Clear();
this.Dbcmd.Connection = this.Dbconn;
Dbcmd.CommandType = CommandType.StoredProcedure;
Dbcmd.CommandText = spName;
foreach (OracleParameter param in Params)
{
Dbcmd.Parameters.Add(param);
}
OracleBlob Lob = new OracleBlob(Dbconn);
int intBlockSize = 15000;
//int intFileSize = Convert.ToInt32(fi.Length);
FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
try
{
byte[] bytes = new byte[intBlockSize];
int intBytes;
int intTotalBytes = 0;
while ((intBytes = br.Read(bytes, 0, bytes.Length)) > 0)
{
Lob.Write(bytes, 0, intBytes);
intTotalBytes += intBytes;
if (evtP != null)
{
evtP(int.Parse(fi.Length.ToString()), intTotalBytes);
}
}
}
catch
{
throw;
}
finally
{
br.Close();
fs.Close();
fs.Dispose();
}
Dbcmd.Parameters[intLob_ParamCnt].Value = Lob; //DBNull.Value; //Lob;
Dbcmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
throw new Exception(string.Format("SP 실행 Err(Oracle) : {0} / SP : {1}", ex.Message, spName), ex);
}
catch (Exception ex)
{
throw new Exception(string.Format("DB Open Err(일반) : {0}", ex.Message), ex);
}
finally
{
if (!this.isTaransaction)
{
this.DB_Close();
}
}
}
/// <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;
}
public void Write(byte[] buffer, int offset, int count)
{
EnsureLobIsNotNull();
lob.Write(buffer, offset, count);
}
private void excute_StoredProcedure(string spName, OracleParameter[] Params, int intLob_ParamCnt, FileInfo fi, bool writelog = false)
{
try
{
this.DB_isOpen();
if (spName.Trim() == "")
{
throw new Exception("프로시져 이름이 공백 입니다.");
}
string log = string.Empty;
Dbcmd.Parameters.Clear();
this.Dbcmd.Connection = this.Dbconn;
Dbcmd.CommandType = CommandType.StoredProcedure;
Dbcmd.CommandText = spName;
foreach (OracleParameter param in Params)
{
Dbcmd.Parameters.Add(param);
if (Log != null && writelog)
{
log += string.Format("\r\n\t\t'{1}'\t\t\t--PARAMNAME:{0}", param.ParameterName, param.Value);
}
}
if (Log != null && writelog)
{
log = string.Format("[OracleDB 프로시져 실행]{0}({1});", spName, log);
Log.WLog(log);
}
OracleBlob Lob = new OracleBlob(Dbconn);
int intBlockSize = 15000;
//int intFileSize = Convert.ToInt32(fi.Length);
FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
try
{
byte[] bytes = new byte[intBlockSize];
/*
* int intReadSize = 0;
*
* if (intFileSize > intBlockSize)
* intReadSize = intBlockSize;
* else
* intReadSize = intFileSize;
*/
int intBytes;
while ((intBytes = br.Read(bytes, 0, bytes.Length)) > 0)
{
Lob.Write(bytes, 0, intBytes);
}
}
catch
{
throw;
}
finally
{
br.Close();
fs.Close();
fs.Dispose();
}
Dbcmd.Parameters[intLob_ParamCnt].Value = Lob; //DBNull.Value; //Lob;
Dbcmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
throw new Exception(string.Format("SP 실행 Err(Oracle) : {0} / SP : {1}", ex.Message, spName), ex);
}
catch (Exception ex)
{
throw new Exception(string.Format("DB Open Err(일반) : {0}", ex.Message), ex);
}
finally
{
if (!this.isTaransaction)
{
this.DB_Close();
}
}
}
