public bool CreateDocument(string name, string type, decimal? parent_document_id, int? sequenceNo, string username, string filename, Stream input)
{
if (String.IsNullOrEmpty(name))
{
name = filename;
}
using (OracleConnection myConnection = new OracleConnection(ConnectionString))
{
using (BinaryReader r = new BinaryReader(input))
{
myConnection.Open();
OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob);
int streamLength = (int)input.Length;
myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("DOCUMENT", OracleDbType.Blob);
myParam1.OracleValue = myLob;
OracleParameter myParam2 = dbcmd.Parameters.Add("PARENT_DOCUMENT_ID", OracleDbType.Number);
myParam2.OracleValue = parent_document_id;
OracleParameter myParam3 = dbcmd.Parameters.Add("DOCUMENT_NAME", OracleDbType.VarChar);
myParam3.OracleValue = name;
OracleParameter myParam4 = dbcmd.Parameters.Add("SEQUENCE_NO", OracleDbType.Integer);
myParam4.OracleValue = sequenceNo;
OracleParameter myParamType = dbcmd.Parameters.Add("TYPE", OracleDbType.VarChar);
myParamType.OracleValue = type;
OracleParameter myParamUser = dbcmd.Parameters.Add("UPDATE_USER", OracleDbType.VarChar, 50);
myParamUser.OracleValue = username;
OracleParameter myParamDate = dbcmd.Parameters.Add("UPDATE_DATE", OracleDbType.TimeStampLTZ);
myParamDate.OracleValue = DateTime.UtcNow;
OracleParameter myParamFilename = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar);
myParamFilename.OracleValue = filename;
dbcmd.CommandText = @"INSERT INTO TDOCUMENT (NAME, PARENT_DOCUMENT_ID,SEQUENCE_NO, DOCUMENT, TYPE, UPDATE_USER, UPDATE_DATE, FILENAME )
VALUES(:DOCUMENT_NAME, :PARENT_DOCUMENT_ID,:SEQUENCE_NO, :DOCUMENT, :TYPE, :UPDATE_USER, :UPDATE_DATE, :FILENAME)";
var result = dbcmd.ExecuteNonQuery();
}
}
}
return true;
}
public long CopyTo(OracleLob destination, long destinationOffset)
{
}
public string writeClob()
{
this.dboperator_0 = this.connectionConfig_0.getDBOperator();
this.dboperator_0.Open();
string str2 = "";
string strSour = new StreamReader(this.httpRequest_0.InputStream).ReadToEnd();
IDbCommand myComm = null;
IDbTransaction transaction = null;
try
{
myComm = this.dboperator_0.Connection.CreateCommand();
myComm.Connection = (this.dboperator_0.Connection);
transaction = this.dboperator_0.Connection.BeginTransaction();
myComm.Transaction = (transaction);
if (myComm is OleDbCommand)
{
str2 = "Update " + this.string_0 + " set " + this.string_1 + "=? where " + this.string_2 + "='" + this.string_3 + "'";
myComm.CommandText = (str2);
object[] objParams = new object[] { strSour };
this.dboperator_0.AddParams(myComm, objParams);
}
else if (myComm is OracleCommand)
{
myComm.Parameters.Clear();
OracleLob lob = PubFunction.strToClob((OracleCommand)myComm, strSour);
myComm.Parameters.Clear();
str2 = "Update " + this.string_0 + " set " + this.string_1 + "=:imgParam where " + this.string_2 + "='" + this.string_3 + "'";
myComm.CommandText = (str2);
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = ("imgParam");
parameter.OracleType = (OracleType)(4);
parameter.Value = (lob);
myComm.Parameters.Add(parameter);
}
myComm.ExecuteNonQuery();
transaction.Commit();
myComm.Dispose();
this.dboperator_0.Close();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
myComm.Dispose();
}
catch (Exception)
{
}
throw exception;
}
finally
{
try
{
this.dboperator_0.Close();
}
catch (Exception)
{
}
}
return("");
}
public static void Main(string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
OracleConnection connection = null;
connection = new OracleConnection(connectionString);
connection.Open();
Console.WriteLine("Setup test package and data...");
OracleCommand cmddrop = connection.CreateCommand();
cmddrop.CommandText = "DROP TABLE TESTTABLE";
try {
cmddrop.ExecuteNonQuery();
}
catch (OracleException e) {
Console.WriteLine("Ignore this error: " + e.Message);
}
cmddrop.Dispose();
cmddrop = null;
Console.WriteLine("Create table TESTTABLE...");
OracleCommand cmd = connection.CreateCommand();
// create table TESTTABLE
cmd.CommandText =
"create table TESTTABLE (\n" +
" col1 numeric(18,0),\n" +
" col2 varchar(32),\n" +
" col3 date, col4 blob)";
cmd.ExecuteNonQuery();
Console.WriteLine("Insert 3 rows...");
// insert some rows into TESTTABLE
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(45, 'Mono', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(136, 'Fun', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
Console.WriteLine("commit...");
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Update blob...");
// update BLOB and CLOB columns
OracleCommand select = connection.CreateCommand();
select.Transaction = connection.BeginTransaction();
select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
OracleDataReader readerz = select.ExecuteReader();
if (!readerz.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
// update blob_value
Console.WriteLine(" Update BLOB column on table testtable...");
OracleLob blob = readerz.GetOracleLob(1);
byte[] bytes = new byte[6] {
0x31, 0x32, 0x33, 0x34, 0x35, 0x036
};
blob.Write(bytes, 0, bytes.Length);
blob.Close();
readerz.Close();
select.Transaction.Commit();
select.Dispose();
select = null;
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Create package...");
// create Oracle package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
"AS\n" +
" TYPE T_CURSOR IS REF CURSOR;\n"+
"\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR);\n"+
"END TestTablePkg;";
cmd.ExecuteNonQuery();
// create Oracle package body for package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
" IS\n" +
" BEGIN\n" +
" OPEN tableCursor FOR\n" +
" SELECT *\n" +
" FROM TestTable;\n" +
" END GetData;\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
Console.WriteLine("Set up command and parameters to call stored proc...");
OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
Console.WriteLine("Execute...");
command.ExecuteNonQuery();
Console.WriteLine("Get OracleDataReader for cursor output parameter...");
OracleDataReader reader = (OracleDataReader)parameter.Value;
Console.WriteLine("Read data***...");
int r = 0;
while (reader.Read())
{
Console.WriteLine("Row {0}", r);
for (int f = 0; f < reader.FieldCount; f++)
{
Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString());
object val = "";
if (f == 3)
{
Console.WriteLine("blob");
//OracleLob lob = reader.GetOracleLob (f);
//val = lob.Value;
val = reader.GetValue(f);
if (((byte[])val).Length == 0)
{
val = "Empty Blob (Not Null)";
}
else
{
val = BitConverter.ToString((byte[])val);
}
}
else
{
val = reader.GetOracleValue(f);
}
Console.WriteLine(" Field {0} Value: {1}", f, val);
}
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Clean up...");
reader.Close();
reader = null;
command.Dispose();
command = null;
connection.Close();
connection = null;
}
static public void PackageLogon(OracleConnection connection, string username, string password)
{
XmlDocument logonDoc = new XmlDocument();
XmlElement scanworks = logonDoc.CreateElement("Scanworks");
logonDoc.AppendChild(scanworks);
XmlElement erp = logonDoc.CreateElement("ERP");
scanworks.AppendChild(erp);
XmlText ifs = logonDoc.CreateTextNode("IFS");
erp.AppendChild(ifs);
XmlElement api = logonDoc.CreateElement("API");
scanworks.AppendChild(api);
XmlAttribute name = logonDoc.CreateAttribute("Name");
name.Value = "esi_scanworks_logon.Logon";
api.Attributes.Append(name);
addParameter(api, "status", "");
addParameter(api, "username_", username);
addParameter(api, "password_", password);
XmlElement method = logonDoc.CreateElement("Method");
api.AppendChild(method);
XmlText methodName = logonDoc.CreateTextNode("Logon");
method.AppendChild(methodName);
StringWriter sw = new StringWriter();
logonDoc.Save(sw);
string xmlString = sw.ToString();
StringBuilder sql = new StringBuilder("begin :result := ").Append("esi_scanworks_logon").Append(".ApiCall(:parameters_); end; ");
OracleCommand cmd = new OracleCommand();
OracleParameter returnParameter = new OracleParameter();
returnParameter.ParameterName = "result";
returnParameter.OracleType = OracleType.Clob;
returnParameter.Direction = System.Data.ParameterDirection.Output;
// returnParameter.Size = 32000;
cmd.Parameters.Add(returnParameter);
OracleParameter oraParameter = new OracleParameter();
oraParameter.ParameterName = "parameters_";
oraParameter.Value = xmlString;
oraParameter.OracleType = OracleType.LongVarChar;
oraParameter.Direction = System.Data.ParameterDirection.Input;
oraParameter.Size = 32000;
cmd.Parameters.Add(oraParameter);
cmd.CommandText = sql.ToString();
cmd.Connection = connection;
cmd.ExecuteNonQuery();
string result = "";
if (returnParameter.Value.GetType().ToString().Equals("System.Data.OracleClient.OracleLob"))
{
OracleLob lob = (OracleLob)returnParameter.Value;
result = (string)lob.Value;
}
else
{
result = (string)returnParameter.Value;
}
logonDoc = new XmlDocument();
logonDoc.LoadXml(result);
}
public long CopyTo(OracleLob destination) {}
/// <summary>
/// This function takes somes inputs and uploads the file to database.
/// </summary>
/// <param name="pUserLandId">User windows id</param>
/// <param name="pApp">application id</param>
/// <param name="pFileName">file name that is going to be uploaded</param>
/// <param name="pFileContent">binary content of file</param>
public void InsertFileIntoDB(string pUserLandId, string pApp, string pFileName, byte[] pFileContent, ControlName pControlName)
{
string lQuery = string.Empty;
OracleProvider lProvider = new OracleProvider();
string lFileId = string.Empty;
string lVersionId = string.Empty;
OracleCommand lCommand = null;
string lControl = string.Empty;
try
{
if (pControlName == ControlName.UploadOrganizationGovernance)
{
lControl = "Organization Governance";
}
else if (pControlName == ControlName.UploadOrganizationBackground)
{
lControl = "Organization Background";
}
//Get File and Version Id
lFileId = lProvider.FetchDataFromDBUsingExecuteScaler("Select SEQ_DOCUMENT_ID.Nextval from dual");
lVersionId = lProvider.FetchDataFromDBUsingExecuteScaler("Select SEQ_VERSION_ID.Nextval from dual");
//Begin Transaction
lCommand = lProvider.OpenConnectionAndBeginTransaction();
lQuery = "Insert into T_APPLICATION_DOCS (DOCUMENT_ID,APPLICATION_ID, DOC_TITLE,DOC_TYPE,DOC_ASSOCIATE,UPDATED_ON, UPDATED_BY) " +
"values (" + lFileId + ",'" + pApp + "','" + lControl + "','I','G'," +
"SYSDATE,'" + pUserLandId + "')";
//Insert into Application Docs
lProvider.FExecuteNonQuery(lCommand, lQuery, null);
lQuery = "Insert into T_APPLICATION_DOCS_VERSIONS (DOCUMENT_ID,APPLICATION_ID,VERSION_ID,DOC_EXT,UPLOADED_ON, UPLOADED_BY) " +
"VALUES (" + lFileId + ",'" + pApp + "'," + lVersionId + ",'" + pFileName + "', SYSDATE,'" + pUserLandId + "')";
//Insert into Application_docs_version
lProvider.FExecuteNonQuery(lCommand, lQuery, null);
lQuery = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
OracleParameter lParam = new OracleParameter("tempblob", OracleType.Blob);
lParam.Direction = ParameterDirection.Output;
OracleParameterCollection lParamCollection = new OracleParameterCollection();
lParamCollection.Add(lParam);
lProvider.FExecuteNonQuery(lCommand, lQuery, lParamCollection);
OracleLob tempLob = (OracleLob)lCommand.Parameters[0].Value;
//Create temporary BLOB
OracleLob lLob = (OracleLob)lCommand.Parameters[0].Value;
int streamLength = pFileContent.Length;
//Transfer data to server
lLob.Write(pFileContent, 0, streamLength);
lParam = new OracleParameter("data", OracleType.Blob);
lParam.Value = lLob;
//Create a parameter collection
lParamCollection = new OracleParameterCollection();
lParamCollection.Add(lParam);
lQuery = "Update T_APPLICATION_DOCS_VERSIONS set DOC_CONTENT=:data where DOCUMENT_ID=" + lFileId;
//Execue the update query
lProvider.FExecuteNonQuery(lCommand, lQuery, lParamCollection);
//Commit
lProvider.CommitTransaction(lCommand);
}
catch (Exception ex)
{
if (lProvider != null && lCommand != null)
{
lProvider.RollBackTransaction(lCommand);
}
}
finally
{
lProvider = null;
lCommand = null;
}
}
public long CopyTo(long sourceOffset, OracleLob destination, long destinationOffset, long amount) {}
public void Insert(IList <Document> docs)
{
if (docs.Count <= 0)
{
return;
}
StringBuilder insertString = new StringBuilder();
//insertString.AppendLine("DECLARE");
insertString.Append("BEGIN ");
foreach (Hubble.Core.Data.Document doc in docs)
{
insertString.AppendFormat("Insert into {0} (DocId", _Table.DBTableName);
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
insertString.AppendFormat(", {0}", GetFieldName(fv.FieldName));
}
insertString.AppendFormat(") Values({0}", doc.DocId);
foreach (Data.FieldValue fv in doc.FieldValues)
{
if (fv.Value == null)
{
continue;
}
switch (fv.Type)
{
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.NChar:
if (fv.DataLength < 0)
{
insertString.Append(",N'A'");
}
else
{
insertString.AppendFormat(",N'{0}'", ReplaceTextValue(fv.Value.Replace("'", "''")));
}
break;
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.Char:
case Hubble.Core.Data.DataType.Data:
if (fv.DataLength < 0)
{
insertString.Append(",N'A'");
}
else
{
insertString.AppendFormat(",'{0}'", ReplaceTextValue(fv.Value.Replace("'", "''")));
}
break;
case Hubble.Core.Data.DataType.DateTime:
case Hubble.Core.Data.DataType.Date:
case Hubble.Core.Data.DataType.SmallDateTime:
DateTime dateTime;
if (!DateTime.TryParseExact(fv.Value, "yyyy-MM-dd HH:mm:ss", null,
System.Globalization.DateTimeStyles.None, out dateTime))
{
dateTime = DateTime.Parse(fv.Value);
}
insertString.AppendFormat(",to_date('{0}','yyyy-mm-dd HH24:MI:SS')", dateTime.ToString("yyyy-MM-dd HH:mm:ss"));
break;
default:
insertString.AppendFormat(",{0}", fv.Value);
break;
}
}
insertString.Append("); ");
}
insertString.Append(" END;");
using (OracleDataProvider sqlData = new OracleDataProvider())
{
sqlData.Connect(Table.ConnectionString);
sqlData.ExcuteSql(insertString.ToString());
string sql = string.Format("select * from {0} where docid >= {1} and docid <= {2} order by docid FOR UPDATE",
_Table.DBTableName, docs[0].DocId, docs[docs.Count - 1].DocId);
OracleCommand cmd;
using (OracleDataReader reader = sqlData.ExecuteReader(sql, out cmd))
{
cmd.Transaction = cmd.Connection.BeginTransaction();
for (int i = 0; i < docs.Count; i++)
{
reader.Read();
for (int j = 0; j < docs[i].FieldValues.Count; j++)
{
switch (docs[i].FieldValues[j].Type)
{
case Hubble.Core.Data.DataType.NVarchar:
case Hubble.Core.Data.DataType.NChar:
case Hubble.Core.Data.DataType.Varchar:
case Hubble.Core.Data.DataType.Char:
if (docs[i].FieldValues[j].DataLength < 0)
{
OracleLob clob = reader.GetOracleLob(reader.GetOrdinal(docs[i].FieldValues[j].FieldName));
byte[] head = new byte[2];
//head[0] = 0xCC;
//head[1] = 0xDD;
//clob.Write(head, 0, 2);
//clob.Position = 0;
byte[] buffer = Encoding.Unicode.GetBytes(docs[i].FieldValues[j].Value);
clob.Write(buffer, 0, buffer.Length);
//Console.WriteLine(clob.LobType + ".Write(" + buffer + ", 0, 0) => " + clob.Value);
//OracleLob templob = sqlData.CreateTempLob(clob.LobType);
//long actual = clob.CopyTo(templob);
//Console.WriteLine(clob.LobType + ".CopyTo(" + templob.Value + ") => " + actual);
}
break;
}
}
}
cmd.Transaction.Commit();
}
}
}
/// <summary>
/// Execute an oracle stored procedure using the
/// parameters passed in
/// </summary>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
private bool ExecuteOracleSP(bool bAudit,
string strSPName,
CParameterList ParamList,
out k_STATUS_CODE lStatusCode,
out string strStatus)
{
lStatusCode = k_STATUS_CODE.Success;
strStatus = String.Empty;
//return null if no conn
if (m_OracleConnection == null)
{
lStatusCode = k_STATUS_CODE.Failed;
strStatus = "Unable to connect to data source, Data Connection is null";
return(false);
}
//create a new command object and set the command objects connection, text and type
//must use OracleCommand or you cannot get back a ref cur out param which is how
//we do things in medbase
OracleCommand cmd = new OracleCommand();
cmd.Connection = m_OracleConnection;
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
//add the parameters from the parameter list to the command parameter list
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
//create a new oledb param from our param and add it to the list
//this follows how we currently do it in medbase
OracleParameter oraParameter = new OracleParameter();
oraParameter.ParameterName = parameter.ParameterName;
//set the parameter value, default to string. Probably a better way than the
//if then else, but this works and we can find it later,
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
oraParameter.Value = parameter.LongParameterValue;
oraParameter.OracleType = OracleType.Int32;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter)
{
oraParameter.Value = parameter.DoubleParameterValue;
oraParameter.OracleType = OracleType.Double;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter)
{
oraParameter.Value = parameter.IntParameterValue;
oraParameter.OracleType = OracleType.Int32;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
oraParameter.Value = parameter.DateParameterValue;
if (!CDataUtils.IsDateNull(parameter.DateParameterValue))
{
//if the date is not null then set the value
oraParameter.Value = parameter.DateParameterValue;
}
else
{
//set value to DBNull if date is null
oraParameter.Value = DBNull.Value;
}
oraParameter.OracleType = OracleType.DateTime;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.CLOBParameter)
{
//You must begin a transaction before obtaining a temporary LOB.
//Otherwise, the OracleDataReader may fail to obtain data later.
OracleTransaction transaction = m_OracleConnection.BeginTransaction();
//make a new command
OracleCommand command = m_OracleConnection.CreateCommand();
command.Connection = m_OracleConnection;
command.Transaction = transaction;
command.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
command.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
//get a temp lob
OracleLob tempLob = (OracleLob)command.Parameters[0].Value;
//begin batch
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
//convert string to byte array and write to lob,
//note:encoding must be set to match oracle encoding
//default encoding for oracle can be found by running
//SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
System.Text.ASCIIEncoding encASCII = new System.Text.ASCIIEncoding();
//ascii chars
byte[] buffAsciiBytes = encASCII.GetBytes(parameter.CLOBParameterValue);
//destination to convert to. convert from ascii to unicode
UnicodeEncoding encDest = new UnicodeEncoding();
byte[] buffDest = Encoding.Convert(encASCII, encDest, buffAsciiBytes);
//write the converted data to the lob
tempLob.Write(buffDest,
0,
buffDest.Length);
//end batch
tempLob.EndBatch();
//set the value of the param = lob
oraParameter.OracleType = OracleType.Clob;
//oraParameter.OracleType = OracleType.NClob;
oraParameter.Value = tempLob;
//all done so commit;
transaction.Commit();
}
else
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
oraParameter.Direction = parameter.Direction;
cmd.Parameters.Add(oraParameter);
}
}
//add in out params for stored proc, all sp's will return a status 0 = good, 1 = bad
//
//status
ParamList.AddOutputParameter("po_nStatusCode", 0);
OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode",
OracleType.Int32);
oraStatusParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraStatusParameter);
//
//comment
ParamList.AddOutputParameter("po_vStatusComment", String.Empty);
OracleParameter oraCommentParameter = new OracleParameter("po_vStatusComment",
OracleType.VarChar,
4000);
oraCommentParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraCommentParameter);
//
try
{
//execute the stored proc and move the out param values back into
//our list
cmd.ExecuteNonQuery();
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
if (parameter.Direction == ParameterDirection.Output ||
parameter.Direction == ParameterDirection.InputOutput)
{
foreach (OracleParameter oP in cmd.Parameters)
{
if (oP.ParameterName.Equals(parameter.ParameterName))
{
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
parameter.StringParameterValue = parameter.StringParameterValue.Trim();
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.LongParameterValue = Convert.ToInt64(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.DoubleParameterValue = Convert.ToDouble(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.BoolParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.BoolParameterValue = Convert.ToBoolean(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.IntParameterValue = Convert.ToInt32(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
if (!oP.Value.ToString().Equals(String.Empty))
{
parameter.DateParameterValue = Convert.ToDateTime(oP.Value);
}
}
}
}
else
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
parameter.StringParameterValue = parameter.StringParameterValue.Trim();
}
}
}
}
}
}
}
lStatusCode = ParamList.GetStatusCode();
strStatus = ParamList.GetStatusComment();
if (lStatusCode == k_STATUS_CODE.Success)
{
return(true);
}
return(false);
}
catch (InvalidOperationException e)
{
strStatus = e.Message;
lStatusCode = k_STATUS_CODE.Failed;
}
catch (OracleException e)
{
strStatus = e.Message;
lStatusCode = k_STATUS_CODE.Failed;
}
return(false);
}
public static void BLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table BLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE BLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException) {
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
//try {
if (File.Exists(infilename) == false)
{
Console.WriteLine("Filename does not exist: " + infilename);
return;
}
FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] bytes = null;
int bufferLen = 8192;
bytes = r.ReadBytes(bufferLen);
while (bytes.Length > 0)
{
Console.WriteLine("byte count: " + bytes.Length.ToString());
lob.Write(bytes, 0, bytes.Length);
bytes1 = ByteArrayCombine(bytes1, bytes);
if (bytes.Length < bufferLen)
{
break;
}
bytes = r.ReadBytes(bufferLen);
}
r.Close();
fs.Close();
//}
//catch (Exception e) {
// Console.WriteLine("The file could not be read:");
// Console.WriteLine(e.Message);
//}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
transaction = null;
lob = null;
reader.Dispose();
reader = null;
create = null;
insert = null;
select = null;
}
public decimal? CreateListingDocument(string name, string type, int? sequenceNo, string username, string filename, Stream input)
{
if (String.IsNullOrEmpty(name))
{
name = filename;
}
using (OracleConnection myConnection = new OracleConnection(ConnectionString))
{
using (BinaryReader r = new BinaryReader(input))
{
myConnection.Open();
OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob);
int streamLength = (int)input.Length;
myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("DOCUMENT", OracleDbType.Blob);
myParam1.OracleValue = myLob;
OracleParameter myParam3 = dbcmd.Parameters.Add("NAME", OracleDbType.VarChar);
myParam3.OracleValue = name;
OracleParameter myParam4 = dbcmd.Parameters.Add("SEQUENCE_NO", OracleDbType.Integer);
myParam4.OracleValue = sequenceNo;
OracleParameter myParamType = dbcmd.Parameters.Add("TYPE", OracleDbType.VarChar);
myParamType.OracleValue = type;
OracleParameter myParamUser = dbcmd.Parameters.Add("CURRENT_USER", OracleDbType.VarChar, 50);
myParamUser.OracleValue = username;
OracleParameter myParamDate = dbcmd.Parameters.Add("CURRENT_DATE", OracleDbType.TimeStampLTZ);
myParamDate.OracleValue = DateTime.UtcNow;
OracleParameter myParamFilename = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar);
myParamFilename.OracleValue = filename;
OracleParameter pkParam = dbcmd.Parameters.Add("RETPK", OracleDbType.Number, ParameterDirection.Output);
dbcmd.CommandText = @"INSERT INTO TLISTING_DOCUMENT (NAME, SEQUENCE_NO, DOCUMENT, TYPE, CREATE_USER, CREATE_DATE, FILENAME )
VALUES(:NAME,:SEQUENCE_NO, :DOCUMENT, :TYPE, :CURRENT_USER, :CURRENT_DATE, :FILENAME)
RETURNING ID INTO :RETPK";
var result = dbcmd.ExecuteNonQuery();
if (result ==1)
{
return pkParam.Value as decimal?;
}
}
}
}
return null;
}
public bool InsertOrUpdateBuildingPicture(decimal id, string username, string filename, Stream picture)
{
using (OracleConnection myConnection = new OracleConnection(ConnectionString))
{
myConnection.Open();
using (BinaryReader r = new BinaryReader(picture))
{
OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob);
int streamLength = (int)picture.Length;
myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("ID", OracleDbType.Number);
myParam1.OracleValue = id;
dbcmd.CommandText = "UPDATE TBUILDING_PICTURES SET STATUS='D' WHERE BUILDING_ID = :ID AND FLOOR_NO IS NULL";
dbcmd.ExecuteNonQuery();
}
string qry = @"INSERT INTO TBUILDING_PICTURES (BUILDING_ID,PICTURE, CREATE_DATE, CREATE_USER, FILENAME )
VALUES(:ID, :PICTURE, :ADATE, :AUSER, :FILENAME)";
using (OracleCommand myCommand = myConnection.CreateCommand(qry))
{
OracleParameter myParam1 = myCommand.Parameters.Add("ID", OracleDbType.Number);
myParam1.OracleValue = id;
OracleParameter myParam2 = myCommand.Parameters.Add("PICTURE", OracleDbType.Blob);
myParam2.OracleValue = myLob;
OracleParameter myParam3 = myCommand.Parameters.Add("FILENAME", OracleDbType.VarChar, 200);
myParam3.OracleValue = filename;
OracleParameter myParam4 = myCommand.Parameters.Add("AUSER", OracleDbType.VarChar, 50);
myParam4.OracleValue = username;
OracleParameter myParam5 = myCommand.Parameters.Add("ADATE", OracleDbType.TimeStampLTZ);
myParam5.OracleValue = DateTime.Now;
return myCommand.ExecuteNonQuery() == 1;
}
}
}
}
public long CopyTo(long sourceOffset, OracleLob destination, long destinationOffset, long amount)
{
}
public IReturnType execBlob(string sqlString, List <DBParams> arrParam, byte[] Image, string ImageName = "arg_file", bool useStandard = true)
{
List <DBParams> standardParams = new List <DBParams>();
if (useStandard)
{
standardParams.Add(new DBParams("intreturnCode", "I"));
standardParams.Add(new DBParams("strReturnMsg", "V"));
}
foreach (DBParams db in standardParams)
{
arrParam.Add(db);
}
cmd = SetCommand();
OracleTransaction tx = cmd.Connection.BeginTransaction();
cmd.Transaction = tx;
StringBuilder strCommand = new StringBuilder("declare xx blob; begin dbms_lob.createtemporary(xx, false, 0);");
strCommand.Append(" :tempblob1:= xx;");
cmd.Parameters.Add(new OracleParameter("tempblob1", OracleType.Blob)).Direction = ParameterDirection.Output;
strCommand.Append(" end;");
cmd.CommandType = CommandType.Text;
cmd.CommandText = strCommand.ToString();
cmd.ExecuteNonQuery();
OracleLob blob = cmd.Parameters["tempblob1"].Value as OracleLob;
blob.Write(Image, 0, Image.Length);
cmd.Parameters.Clear();
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.StoredProcedure;
AddParameters(arrParam);
cmd.Parameters.Add(ImageName, OracleType.Blob).Value = blob;
IReturnType rt = new DALReturnType();
cmd.ExecuteNonQuery();
if (cmd.Parameters["intreturnCode"].Value.ToString() != "")
{
rt.ReturnCode = (int?)cmd.Parameters["intreturnCode"].Value;
}
rt.ReturnMessage = cmd.Parameters["strReturnMsg"].Value.ToString();
tx.Commit();
conn.Close();
return(rt);
}
public long CopyTo(OracleLob destination)
{
}
public static void CLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table CLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE CLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException oe1) {
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
UnicodeEncoding encoding = new UnicodeEncoding();
try {
// read file "cs-parser.cs" into the oracle clob
using (StreamReader sr = new StreamReader(infilename)) {
string sbuff = sr.ReadToEnd();
byte[] evalue = encoding.GetBytes(sbuff);
lob.Write(evalue, 0, evalue.Length);
}
}
catch (Exception e) {
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
}
// Methods
public void Append(OracleLob source) {}
public static string[] Save(DBOperator oDb, XmlDocument xdoc, string djid, string djsn, string dj_name, string djlx, string djposition, string designtext, string xmltext, string userfunc, string sAddHtml, string databasetype, string userType)
{
int num = int.Parse(djid);
string message = "";
oDb.Open();
string str2 = "select djid from FC_BILLZL where djsn='" + djsn + "' and djid <> " + djid;
int num2 = num;
try
{
IDbCommand myComm = oDb.Connection.CreateCommand();
myComm.Connection = (oDb.Connection);
myComm.CommandText = (str2);
IDataReader reader = myComm.ExecuteReader();
if (reader.Read())
{
message = "保存成功！不过此表单的表单SN重名，请修改此表单的表单SN。";
}
reader.Close();
myComm.Dispose();
string str3 = "1";
myComm = oDb.Connection.CreateCommand();
IDbTransaction transaction = oDb.Connection.BeginTransaction();
myComm.Connection = (oDb.Connection);
myComm.Transaction = (transaction);
try
{
string str4 = "";
string str5 = "";
if (databasetype == "oracle")
{
str4 = "'',''";
str5 = "";
}
else
{
str4 = "'" + xmltext + "','" + designtext + "'";
str5 = ",xmltext='" + xmltext + "',designtext='" + designtext + "'";
}
if (num == 0)
{
num2 = Tools.getRecNumNo("BIL", myComm);
str2 = "insert into FC_BILLZL (djid,djsn,dj_name,djlx,djposition,userType,xmltext,designtext) values (" + Convert.ToString(num2) + ",'" + djsn + "','" + dj_name + "','" + djlx + "','" + djposition + "','" + userType + "'," + str4 + ")";
myComm.CommandText = (str2);
myComm.ExecuteNonQuery();
str3 = Convert.ToString(num2);
}
else
{
str2 = "update FC_BILLZL set djsn='" + djsn + "',dj_name='" + dj_name + "',djlx='" + djlx + "',djposition='" + djposition + "',userType='" + userType + "'" + str5 + " where djid=" + djid;
myComm.CommandText = (str2);
myComm.ExecuteNonQuery();
str3 = djid;
}
if (databasetype == "oracle")
{
if (myComm is OleDbCommand)
{
str2 = "update FC_BILLZL set designtext=?,xmltext=? where djid=? ";
myComm.CommandText = (str2);
object[] objParams = new object[] { designtext, xmltext, str3 };
oDb.AddParams(myComm, objParams);
}
else if (myComm is OracleCommand)
{
str2 = "update FC_BILLZL set designtext=:pdesigntext,xmltext=:pxmltext where djid=" + str3;
myComm.Parameters.Clear();
OracleLob lob = Tools.strToClob((OracleCommand)myComm, designtext);
myComm.Parameters.Clear();
OracleLob lob2 = Tools.strToClob((OracleCommand)myComm, xmltext);
myComm.Parameters.Clear();
myComm.CommandText = (str2);
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = ("pdesigntext");
parameter.OracleType = (OracleType)(4);
parameter.Value = (lob);
myComm.Parameters.Add(parameter);
OracleParameter parameter2 = new OracleParameter();
parameter2.ParameterName = ("pxmltext");
parameter2.OracleType = (OracleType)(4);
parameter2.Value = (lob2);
myComm.Parameters.Add(parameter2);
}
myComm.ExecuteNonQuery();
}
transaction.Commit();
myComm.Dispose();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
myComm.Dispose();
}
catch (Exception)
{
}
message = exception.Message;
num2 = 0;
}
}
catch (Exception exception2)
{
message = exception2.Message;
num2 = 0;
}
finally
{
try
{
oDb.Close();
}
catch (Exception)
{
}
}
return(new string[] { Convert.ToString(num2), message });
}
public long CopyTo(OracleLob destination, long destinationOffset) {}
public bool InsertOrUpdatePicture(decimal building_id, decimal? building_vacancy_id, string filename, string floor, string username, Stream input)
{
using (OracleConnection myConnection = new OracleConnection(ConnectionString))
{
using (BinaryReader r = new BinaryReader(input))
{
myConnection.Open();
OracleLob myLob = new OracleLob(myConnection, OracleDbType.Blob);
int streamLength = (int)input.Length;
myLob.Write(r.ReadBytes(streamLength), 0, streamLength);
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("ID", OracleDbType.Number);
myParam1.OracleValue = building_id;
OracleParameter floorParam = dbcmd.Parameters.Add("BUILDING_VACANCY_ID", OracleDbType.Number);
floorParam.OracleValue = building_vacancy_id;
dbcmd.CommandText = "UPDATE TBUILDING_PICTURES SET STATUS='D' WHERE BUILDING_ID = :ID AND BUILDING_VACANCY_ID =:BUILDING_VACANCY_ID";
dbcmd.ExecuteNonQuery();
}
string qry = @"INSERT INTO TBUILDING_PICTURES (BUILDING_ID,PICTURE, CREATE_DATE, CREATE_USER, FILENAME, FLOOR_NO, BUILDING_VACANCY_ID )
VALUES(:ID, :PICTURE, :ADATE, :AUSER, :FILENAME, :FLOOR_NO, :BUILDING_VACANCY_ID)";
using (OracleCommand dbcmd = myConnection.CreateCommand())
{
OracleParameter myParam1 = dbcmd.Parameters.Add("PICTURE", OracleDbType.Blob);
myParam1.OracleValue = myLob;
OracleParameter myParam2 = dbcmd.Parameters.Add("ID", OracleDbType.Number);
myParam2.OracleValue = building_id;
OracleParameter myParam3 = dbcmd.Parameters.Add("FLOOR_NO", OracleDbType.VarChar, 50);
myParam3.OracleValue = floor;
OracleParameter myParam4 = dbcmd.Parameters.Add("AUSER", OracleDbType.VarChar, 50);
myParam4.OracleValue = username;
OracleParameter myParam5 = dbcmd.Parameters.Add("ADATE", OracleDbType.TimeStampLTZ);
myParam5.OracleValue = DateTime.Now;
OracleParameter myParam6 = dbcmd.Parameters.Add("FILENAME", OracleDbType.VarChar, 200);
myParam6.OracleValue = filename;
OracleParameter floorParam = dbcmd.Parameters.Add("BUILDING_VACANCY_ID", OracleDbType.Number);
floorParam.OracleValue = building_vacancy_id;
dbcmd.CommandText = qry;
var result = dbcmd.ExecuteNonQuery();
}
}
}
return false;
}
