Cloud插件，链接oracle数据库
业务场景：客户需要在Cloud中获取第三方系统的数据，但是第三方系统的数据库是oracle，这是就需要连接oracle数据库获取数据了。
需要引用Oracle.ManagedDataAccess.dll这个类库
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.Metadata.EntityElement;
using System.Text.RegularExpressions;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.Bill.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel;
using Kingdee.BOS.Core.Metadata;
using System.Data;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.JSON;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.Metadata.ControlElement;
using Kingdee.BOS.Cache;
//using System.Data.OracleClient;
using Oracle.ManagedDataAccess.Client;
using Kingdee.BOS.WebApi.Client;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Kingdee.BOS.Orm.DataEntity;
namespace GetDataPlugIn
{
public class GetData : AbstractBillPlugIn
{
K3CloudApiClient client = new K3CloudApiClient("http://win-bu6omsa7a39/K3Cloud/");
public override void ButtonClick(ButtonClickEventArgs e)
{
base.ButtonClick(e);
if (e.Key.ToUpperInvariant() == "F_DEV_BUTTON")
{
OracleConnection conn11 = null;
conn11 = OpenConn();
var cmd11 = conn11.CreateCommand();
string Sql;
Sql = "select * from EAS_T_Voucher ";
cmd11.CommandText += Sql;
cmd11.CommandType = CommandType.Text;
using (OracleDataReader rdr11 = cmd11.ExecuteReader())
{
DataTable dt11 = new DataTable();
dt11.Load(rdr11);
int RowCount11;
RowCount11 = dt11.Rows.Count;
for (int i = 0; RowCount11 > i; i++)
{
OracleConnection conn = null;
conn = OpenConn();
var cmd = conn.CreateCommand();
String VoucherId = dt11.Rows[i]["fid"].ToString();
String seleSql = "select * from EAS_T_VoucherEntry where 凭证ID='" + VoucherId + "'";
cmd.CommandText += seleSql;
cmd.CommandType = CommandType.Text;
using (OracleDataReader rdr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(rdr);
int RowCount;
RowCount = dt.Rows.Count;
int EntryRow = 0;
for (int j = 0; j < RowCount; j++)
{
EntryRow = this.View.Model.GetEntryRowCount("FEntity");
String LoanType = dt.Rows[j]["方向"].ToString();
Decimal FLocalAmount = Decimal.Parse(dt.Rows[j]["FLocalAmount"].ToString());
String DESCR = dt.Rows[j]["FDescription"].ToString();
String AccountItem = dt.Rows[j]["会计科目"].ToString();
String StrAssAct = dt.Rows[j]["核算项目"].ToString();
String Account = dt.Rows[j]["科目编码"].ToString();
if (!string.IsNullOrWhiteSpace(StrAssAct))
{
DateTime FBizDate = DateTime.Parse(dt.Rows[j]["业务日期"].ToString());
Decimal ItemAmount = Decimal.Parse(dt.Rows[j]["核算行金额"].ToString());
String EASAssNum = null;
String cus = dt.Rows[j]["客户"].ToString();
String Sup = dt.Rows[j]["供应商"].ToString();
String por = dt.Rows[j]["其他"].ToString();
String bank = dt.Rows[j]["银行账户"].ToString();
String admin = dt.Rows[j]["行政组织"].ToString();
String comp = dt.Rows[j]["公司"].ToString();
String cost = dt.Rows[j]["成本中心"].ToString();
String person = dt.Rows[j]["职员"].ToString();
String StrAssAct1 = dt.Rows[j]["FGeneralAssActType1ID"].ToString();
String StrAssAct2 = dt.Rows[j]["FGeneralAssActType2ID"].ToString();
String StrAssAct3 = dt.Rows[j]["FGeneralAssActType3ID"].ToString();
String StrAssAct4 = dt.Rows[j]["FGeneralAssActType4ID"].ToString();
String StrAssAct5 = dt.Rows[j]["FGeneralAssActType5ID"].ToString();
String StrAssAct6 = dt.Rows[j]["FGeneralAssActType6ID"].ToString();
String StrAssAct7 = dt.Rows[j]["FGeneralAssActType7ID"].ToString();
String StrAssAct8 = dt.Rows[j]["FGeneralAssActType8ID"].ToString();
String StrAssAct9 = dt.Rows[j]["FGeneralAssActType9ID"].ToString();
String StrAssAct10 = dt.Rows[j]["FGeneralAssActType10ID"].ToString();
String entryId = dt.Rows[j]["分录ID"].ToString();
if (StrAssAct8 != null)
{
String haha = StrAssAct8;
}
if (string.IsNullOrWhiteSpace(cus)
&& string.IsNullOrWhiteSpace(por)
&& string.IsNullOrWhiteSpace(bank)
&& string.IsNullOrWhiteSpace(admin)
&& string.IsNullOrWhiteSpace(comp)
&& string.IsNullOrWhiteSpace(cost)
&& string.IsNullOrWhiteSpace(person)
)
{
String AsstActType = "select fnumber from T_BD_GeneralAsstActType ";
if (!string.IsNullOrWhiteSpace(StrAssAct1))
{
AsstActType += "where fid = '" + StrAssAct1 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct2)
)
{
AsstActType += "where fid = '" + StrAssAct2 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct3))
{
AsstActType += "where fid = '" + StrAssAct3 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct4))
{
AsstActType += "where fid = '" + StrAssAct4 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct5))
{
AsstActType += "where fid = '" + StrAssAct5 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct6))
{
AsstActType += "where fid = '" + StrAssAct6 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct7))
{
AsstActType += "where fid = '" + StrAssAct7 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct8))
{
AsstActType += "where fid = '" + StrAssAct8 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct9))
{
AsstActType += "where fid = '" + StrAssAct9 + "'";
}
if (!string.IsNullOrWhiteSpace(StrAssAct10))
{
AsstActType += "where fid = '" + StrAssAct10 + "'";
}
OracleConnection conn2 = null;
conn2 = OpenConn();
var cmd111 = conn2.CreateCommand();
cmd111.CommandText += AsstActType;
cmd111.CommandType = CommandType.Text;
using (OracleDataReader rdr1 = cmd111.ExecuteReader())
{
DataTable zjy = new DataTable();
zjy.Load(rdr1);
int zjyCount;
zjyCount = zjy.Rows.Count;
for (int g = 0; g < zjyCount; g++)
{
EntryRow = this.View.Model.GetEntryRowCount("FEntity");
EASAssNum = zjy.Rows[g]["Fnumber"].ToString();
this.View.Model.SetValue("F_DEV_LoanType", LoanType, EntryRow - 1);
this.View.Model.SetValue("F_DEV_FLocalAmount", FLocalAmount, EntryRow - 1);
this.View.Model.SetValue("F_DEV_ItemAmount", ItemAmount, EntryRow - 1);
this.View.Model.SetValue("F_DEV_AccountItem", AccountItem, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Account", Account, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Voucherid", dt11.Rows[i]["fid"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_EASACCNO", dt11.Rows[i]["凭证号"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_EASAssNum", EASAssNum, EntryRow - 1);
this.View.Model.SetValue("F_DEV_StrAssAct", StrAssAct, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Date", FBizDate, EntryRow - 1);
this.View.Model.SetValue("F_DEV_FEXPLANATION", DESCR, EntryRow - 1);
this.View.Model.CreateNewEntryRow("FEntity");
}
conn2.Close();
}
}
else
{
EntryRow = this.View.Model.GetEntryRowCount("FEntity");
EASAssNum = cus + Sup + por + bank + admin + comp + cost + person;
this.View.Model.SetValue("F_DEV_StrAssAct", StrAssAct, EntryRow - 1);
this.View.Model.SetValue("F_DEV_EASAssNum", EASAssNum, EntryRow - 1);
this.View.Model.SetValue("F_DEV_LoanType", LoanType, EntryRow - 1);
this.View.Model.SetValue("F_DEV_FLocalAmount", FLocalAmount, EntryRow - 1);
this.View.Model.SetValue("F_DEV_ItemAmount", ItemAmount, EntryRow - 1);
this.View.Model.SetValue("F_DEV_AccountItem", AccountItem, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Account", Account, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Voucherid", dt11.Rows[i]["fid"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_EASACCNO", dt11.Rows[i]["凭证号"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_Date", FBizDate, EntryRow - 1);
this.View.Model.SetValue("F_DEV_FEXPLANATION", DESCR, EntryRow - 1);
this.View.Model.CreateNewEntryRow("FEntity");
}
}
else
{
EntryRow = this.View.Model.GetEntryRowCount("FEntity");
this.View.Model.SetValue("F_DEV_StrAssAct", StrAssAct, EntryRow - 1);
this.View.Model.SetValue("F_DEV_LoanType", LoanType, EntryRow - 1);
this.View.Model.SetValue("F_DEV_FLocalAmount", FLocalAmount, EntryRow - 1);
this.View.Model.SetValue("F_DEV_AccountItem", AccountItem, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Account", Account, EntryRow - 1);
this.View.Model.SetValue("F_DEV_Voucherid", dt11.Rows[i]["fid"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_EASACCNO", dt11.Rows[i]["凭证号"].ToString(), EntryRow - 1);
this.View.Model.SetValue("F_DEV_FEXPLANATION", DESCR, EntryRow - 1);
this.View.Model.CreateNewEntryRow("FEntity");
}
}
}
conn.Close();
}
}
conn11.Close();
}
}
static OracleConnection OpenConn()
{
try
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "Data Source= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.33)(PORT = 1521))" +
"(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)));User Id=hrpdb;Password=password;";
conn.Open();
return conn;
}
catch (Exception)
{
//MessageBox.Show(ex.Message);
}
return null;
}
}
}
