public static String getCountryCodeByERPID(String _erpid)
{
var obj = DataAccess.MyAdvantechDAL.GetSAPDIMCompanyByERPID(_erpid).FirstOrDefault();
if (obj == null)
{
var obj_sap = OracleProvider.ExecuteScalar("SAP_PRD", "select LAND1 from saprdp.kna1 where KUNNR = '" + _erpid + "' AND rownum=1");
if (obj_sap == null)
{
return("");
}
else if (String.IsNullOrEmpty(obj_sap.ToString()))
{
return("");
}
else
{
return(obj_sap.ToString());
}
}
else if (String.IsNullOrEmpty(obj.COUNTRY))
{
return("");
}
else
{
return(obj.COUNTRY);
}
}
//以下为测试代码：一个可以兼容各种数据库事务的使用范例
//1、传入实体执行执行事务测试
private bool InEntityTransactionTest(string mainId, string detailId)
{
//可以支持任意流行数据库类型，指定相关的数据库提供者即可（OracleProvider、SqlProvider、SqLiteProvider、MySqlProvider、DB2Provider、OleDbProvider）
IDbProvider dbProvider = new OracleProvider(SystemInfo.BusinessDbConnectionString);
bool result = true;
try
{
dbProvider.BeginTransaction();
//主表
CASE_PRODUCTIN_MAINManager manager = new CASE_PRODUCTIN_MAINManager(dbProvider, Utils.UserInfo);
CASE_PRODUCTIN_MAINEntity mainEntity = manager.GetEntity(dbProvider.SqlSafe(mainId));
manager.Delete(mainEntity);
//子表
CASE_PRODUCTIN_DETAILManager detailManager = new CASE_PRODUCTIN_DETAILManager(dbProvider, Utils.UserInfo);
CASE_PRODUCTIN_DETAILEntity detailEntity = detailManager.GetEntity(dbProvider.SqlSafe(detailId));
detailManager.Delete(detailEntity);
//事务提交
dbProvider.CommitTransaction();
}
catch (Exception ex)
{
//事务回滚
dbProvider.RollbackTransaction();
result = false;
}
return(result);
}
public static List <DataAccess.SAPModel.SAPShipTo> GetSAPShipToAddr(String ERPID)
{
String str = "select a.KUNNR,b.MC_STREET from saprdp.kna1 a inner join saprdp.adrc b on a.adrnr=b.addrnumber " +
" where a.kunnr like '" + ERPID + "%' order by a.kunnr desc ";
DataTable dt = OracleProvider.GetDataTable("SAP_PRD", str);
if (dt != null && dt.Rows.Count > 0)
{
List <DataAccess.SAPModel.SAPShipTo> list = new List <DataAccess.SAPModel.SAPShipTo>();
foreach (DataRow r in dt.Rows)
{
DataAccess.SAPModel.SAPShipTo P = new DataAccess.SAPModel.SAPShipTo();
P.SHIPTOID = r["KUNNR"].ToString();
P.ADDR = r["MC_STREET"].ToString();
list.Add(P);
}
if (list.Count > 0)
{
return(list);
}
}
return(null);
}
/// <summary>
/// This function returns all the application
/// </summary>
/// <returns></returns>
public DataTable GetAllApplications()
{
string lQuery = "select Application_ID As \"Application Id\",Org_name As \"Organization Name\",org_phone As \"Organization Phone\",created_date As \"Created Date\" from T_Application Order by application_id";
OracleProvider lProvider = new OracleProvider();
return(lProvider.FetchDataFromDB(lQuery));
}
/// <summary>
/// This function fetches data for comboboxes based on the input parameter
/// </summary>
/// <param name="lCtrlName"></param>
/// <returns></returns>
public DataTable FetchControlData(ControlName lCtrlName)
{
string lQuery = string.Empty;
OracleProvider lProvider = new OracleProvider();
switch (lCtrlName)
{
case ControlName.AssignedTo:
lQuery = "Select EMP_LANID, LAST_NAME||', '||FIRST_NAME AS FullName from T_USERS_ACCESS where ACCESS_GROUP='ADMIN' order by 2";
break;
case ControlName.OrganizationType:
lQuery = "Select LOOKUP_ID, LOOKUP_DESC from T_LOOKUP where LOOKUP_GROUP='ORG_TYPE' and DELETED is Null order by 2";
break;
case ControlName.OrganizationProvince:
lQuery = "Select LOOKUP_ID, LOOKUP_DESC from T_LOOKUP where LOOKUP_GROUP='PROV' and DELETED is Null order by 2";
break;
case ControlName.PayableProvince:
lQuery = "Select LOOKUP_ID, LOOKUP_DESC from T_LOOKUP where LOOKUP_GROUP='PROV' and DELETED is Null order by 2";
break;
case ControlName.RequestType:
lQuery = "Select LOOKUP_ID, LOOKUP_DESC from T_LOOKUP where LOOKUP_GROUP='REQTYPE' and DELETED is Null order by 2";
break;
}
return(lProvider.FetchDataFromDB(lQuery));
}
public static IDbProvider AutoDetectProvider()
{
IDbProvider provider = null;
try
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(DWKitRuntime.ConnectionStringData)) { };
provider = new SQLServerProvider();
}
catch (ArgumentException) { }
if (provider == null)
{
try
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(DWKitRuntime.ConnectionStringData)) { };
provider = new PostgreSqlProvider();
}
catch (ArgumentException) { }
}
if (provider == null)
{
try
{
using (IDbConnection connection = new OracleConnection(DWKitRuntime.ConnectionStringData)) {}
provider = new OracleProvider();
}
catch (ArgumentException)
{
}
}
return(provider);
}
private static IDbProvider CreateAppDbProvider()
{
IDbProvider dbProvider = null;
switch (DbType)
{
case DatabaseType.SqlServer:
dbProvider = new SqlServerProvider();
break;
case DatabaseType.MySql:
dbProvider = new MySqlProvider();
break;
case DatabaseType.Oracle:
dbProvider = new OracleProvider();
break;
default:
dbProvider = new SqlServerProvider();
break;
}
return(dbProvider);
}
private void Button_GetConnStr_Click(object sender, EventArgs e)
{
try
{
this.textBox_ConnStr.Text = OracleProvider.GetConnStr(this.textBox_Address.Text, int.Parse(this.textBox_Port.Text), this.textBox_ServiceName.Text, this.textBox_UserName.Text, this.textBox_Password.Text);
}
catch (Exception) { }
}
public void LoadSchemaSpecification_should_throw_exception_when_connection_string_is_wrong()
{
// Arrange
OracleProvider db = new OracleProvider(@"wrong database connection string", "ICRFSLDV");
// Act & Assert
Assert.Throws<ArgumentException>(() => db.LoadDbSpecification());
}
private WorkflowRuntime CreateRuntimeOracle()
{
var provider = new OracleProvider(Parameters.ConnectionString);
callbackProvider = new WorkflowCallbackProvider(Parameters, provider);
var builder = new WorkflowBuilder <XElement>(callbackProvider, new XmlWorkflowParser(), provider).WithDefaultCache();
return(new WorkflowRuntime(Parameters.RuntimeId)
.WithBuilder(builder)
.WithPersistenceProvider(provider));
}
public void LoadSchemaSpecification_should_update_TableCount_property()
{
// Arrange
OracleProvider db = new OracleProvider(@"DATA SOURCE=DWQA;PERSIST SECURITY INFO=True;USER ID=ICRFSLDV;PASSWORD=icrfsldv;Pooling=FALSE", "ICRFSLDV");
// create schema specification
SchemaSpecification scdb = db.LoadDbSpecification();
// Assert
Assert.That(Is.Equals(scdb.Tables.Count, 143));
}
public static IDataProvider GetDataProvider(
OracleVersion version = OracleVersion.v12,
OracleProvider provider = OracleProvider.Managed)
{
return((provider, version) switch
{
(OracleProvider.Native, OracleVersion.v11) => _oracleNativeDataProvider11.Value,
(OracleProvider.Native, OracleVersion.v12) => _oracleNativeDataProvider12.Value,
(OracleProvider.Managed, OracleVersion.v11) => _oracleManagedDataProvider11.Value,
(OracleProvider.Managed, OracleVersion.v12) => _oracleManagedDataProvider12.Value,
(OracleProvider.Devart, OracleVersion.v11) => _oracleDevartDataProvider11.Value,
(OracleProvider.Devart, OracleVersion.v12) => _oracleDevartDataProvider12.Value,
_ => _oracleManagedDataProvider12.Value,
});
public static string GetCustomerPartNoFromSAP(String _PartNo, String _ORGID, String _ERPID)
{
String sql = String.Format("select KDMAT from saprdp.knmt where MANDT = '168' and MATNR = '{0}' and VKORG = '{1}' and KUNNR = '{2}' and rownum = 1", _PartNo, _ORGID, _ERPID);
var objCPN = OracleProvider.ExecuteScalar("SAP_PRD", sql);
if (objCPN != null && !String.IsNullOrEmpty(objCPN.ToString()))
{
return(objCPN.ToString());
}
else
{
return(String.Empty);
}
}
/// <summary>
/// Convert Sibel Country to SAP Country
/// </summary>
/// <param name="partNo"></param>
/// <param name="plant"></param>
/// <returns></returns>
public static string ConvertSiebelCountrToSAPCountry(string siebelCountry)
{
String result = String.Empty;
StringBuilder sb = new StringBuilder();
string sapCountry = "";
switch (siebelCountry.Trim())
{
case "United States":
sapCountry = "USA";
break;
case "Korea":
sapCountry = "South Korea";
break;
case "Russia":
sapCountry = "Russian Fed.";
break;
case "Bosnia and Herzegovina":
sapCountry = "Bosnia-Herz.";
break;
default:
sapCountry = siebelCountry;
break;
}
//var dt = Advantech.Myadvantech.DataAccess.SAPDAL.GetSAPCountryList();
sb.AppendFormat(" select land1, landx from saprdp.t005t where mandt = '168' and spras = 'E' and LANDX = '{0}' order by landx ", sapCountry.Replace("'", "''"));
var dt = OracleProvider.GetDataTable("SAP_PRD", sb.ToString());
if (dt.Rows.Count > 0)
{
DataRow _row = dt.Rows[0];
result = _row["land1"].ToString();
}
else
{
result = siebelCountry;
}
return(result);
}
internal static void TryOracle(string connectionString)
{
if (!IsTryOracle)
{
try
{
OracleProvider db = new OracleProvider(connectionString);
var conn = db.GetAdapter();
IsTryOracle = true;
}
catch (Exception ex)
{
var message = ErrorMessage.GetThrowMessage(
"You need to refer to Oracle.ManagedDataAccess.dll",
"需要引用ManagedDataAccess.dll，请在Nuget安装最新稳定版本,如果有版本兼容问题请先删除原有引用");
throw new Exception(message);
}
}
}
public static void TryOracle()
{
if (!IsTryOracle)
{
try
{
OracleProvider db = new OracleProvider();
var conn = db.GetAdapter();
IsTryOracle = true;
}
catch
{
var message = ErrorMessage.GetThrowMessage(
"You need to refer to Oracle.ManagedDataAccess.Core",
"你需要引用 Oracle.ManagedDataAccess.Core");
throw new Exception(message);
}
}
}
private Provider GetProvider(DbConnectionTypes connectionType, string connectionString)
{
Provider provider;
switch (connectionType)
{
case DbConnectionTypes.MsSql:
provider = new MsSqlProvider(connectionString);
break;
case DbConnectionTypes.Postgres:
provider = new PostgresProvider(connectionString);
break;
case DbConnectionTypes.Oracle:
provider = new OracleProvider(connectionString);
break;
default:
return(null);
}
return(provider);
}
private bool IsInputValid()
{
bool result = false;
if (this.index == 1)
{
result = true;
this.provider = this.provider_def;
}
else if (this.index == 2)
{
result = !string.IsNullOrWhiteSpace(this.textBox_Address.Text) && !string.IsNullOrWhiteSpace(this.textBox_Port.Text) && !string.IsNullOrWhiteSpace(this.textBox_ServiceName.Text) && !string.IsNullOrWhiteSpace(this.textBox_UserName.Text) && !string.IsNullOrWhiteSpace(this.textBox_Password.Text);
if (result)
{
this.provider_dynamic = new OracleProvider(this.textBox_Address.Text, int.Parse(this.textBox_Port.Text), this.textBox_ServiceName.Text, this.textBox_UserName.Text, this.textBox_Password.Text);
}
this.provider = this.provider_dynamic;
}
if (!result)
{
MessageBox.Show("输入不符合要求");
}
return(result);
}
public static List <SAP_DIMCOMPANY> GetSAPDIMCompanyEndCustomerByID(String _SoldtoID, String _ORGID, String _EndCustomerID, String _EndCustomerName)
{
if (String.IsNullOrEmpty(_SoldtoID))
{
return(new List <SAP_DIMCOMPANY>());
}
List <SAP_DIMCOMPANY> result = new List <SAP_DIMCOMPANY>();
//List<String> EndCustomers = (from SAPDimCompany in MyAdvantechContext.Current.SAP_DIMCOMPANY
// join SAPCompanyPartners in MyAdvantechContext.Current.SAP_COMPANY_PARTNERS on SAPDimCompany.COMPANY_ID equals SAPCompanyPartners.COMPANY_ID
// where SAPDimCompany.COMPANY_ID.Equals(_SoldtoID, StringComparison.OrdinalIgnoreCase) &&
// SAPCompanyPartners.PARTNER_FUNCTION.Equals("EM") &&
// !String.IsNullOrEmpty(SAPCompanyPartners.PARENT_COMPANY_ID) &&
// (String.IsNullOrEmpty(_ORGID) ? true : SAPCompanyPartners.ORG_ID.Equals(_ORGID, StringComparison.OrdinalIgnoreCase))
// orderby SAPCompanyPartners.DEFPA descending
// select "'" + SAPCompanyPartners.PARENT_COMPANY_ID + "'").ToList();
String sql = " SELECT distinct A.KUNN2 AS company_id, A.VKORG as ORG_ID, B.NAME1 AS COMPANY_NAME, " +
" D.street || ' ' || D.city1 || ' ' || D.region || ' ' || D.post_code1 || ' ' || (select e.landx from saprdp.t005t e where e.land1 = B.land1 and e.spras = 'E' and rownum = 1) AS Address, " +
" B.Land1 AS COUNTRY, B.Ort01 AS CITY, B.STRAS as STREET, " +
" B.PSTLZ AS ZIP_CODE, D.region AS STATE, B.TELF1 AS TEL_NO, B.TELFX AS FAX_NO, D.NAME_CO as Attention, " +
" '' as PARTNER_FUNCTION, " +
" E.VKBUR as SalesOffice, E.VKGRP As SalesGroup, E.SPART As division, D.STR_SUPPL3, A.DEFPA" +
" From saprdp.kna1 B " +
" Left join saprdp.adr6 C on B.adrnr = C.addrnumber " +
" inner join saprdp.knvp A on A.KUNN2 = B.KUNNR " +
" inner join saprdp.adrc D on D.country = B.land1 And D.addrnumber = B.adrnr " +
" inner join saprdp.knvv E on B.KUNNR = E.KUNNR " +
" where B.loevm <> 'X' AND A.PARVW ='EM' " +
" AND A.KUNNR = '" + _SoldtoID + "' AND A.VKORG = '" + _ORGID + "'";
if (!String.IsNullOrEmpty(_EndCustomerID))
{
sql += String.Format(" AND A.KUNN2 like '%{0}%' ", _EndCustomerID.Replace("'", "''").Trim());
}
if (!String.IsNullOrEmpty(_EndCustomerName))
{
sql += String.Format(" and (Upper(B.NAME1) LIKE '%{0}%' or B.NAME2 like '%{0}%') ", _EndCustomerName.Replace("'", "''").Trim());
}
sql += " ORDER BY A.DEFPA desc, A.KUNN2";
DataTable dt = OracleProvider.GetDataTable("SAP_PRD", sql);
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
if (dt.Select("DEFPA = 'X'").Count() > 0)
{
dr = dt.Select("DEFPA = 'X'")[0];
}
else if (dt.Select("company_id = '" + _SoldtoID + "'").Count() > 0)
{
dr = dt.Select("company_id = '" + _SoldtoID + "'")[0];
}
else
{
dr = dt.Rows[0];
}
SAP_DIMCOMPANY sd = new SAP_DIMCOMPANY();
sd.COMPANY_ID = dr["company_id"].ToString();
sd.COMPANY_NAME = dr["COMPANY_NAME"].ToString();
sd.ADDRESS = dr["Address"].ToString();
sd.CITY = dr["CITY"].ToString();
sd.REGION_CODE = dr["STATE"].ToString();
sd.ZIP_CODE = dr["ZIP_CODE"].ToString();
sd.COUNTRY = dr["COUNTRY"].ToString();
sd.ATTENTION = dr["Attention"].ToString();
sd.TEL_NO = dr["TEL_NO"].ToString();
result.Add(sd);
}
return(result);
}
protected OracleDataProvider(string name, OracleProvider provider, OracleVersion version)
: base(name, GetMappingSchema(provider, version), OracleProviderAdapter.GetInstance(provider))
{
Provider = provider;
Version = version;
SqlProviderFlags.IsIdentityParameterRequired = true;
SqlProviderFlags.IsCommonTableExpressionsSupported = true;
SqlProviderFlags.IsSubQueryOrderBySupported = true;
SqlProviderFlags.IsDistinctOrderBySupported = false;
SqlProviderFlags.IsUpdateFromSupported = false;
SqlProviderFlags.DefaultMultiQueryIsolationLevel = IsolationLevel.ReadCommitted;
SqlProviderFlags.IsNamingQueryBlockSupported = true;
SqlProviderFlags.RowConstructorSupport = RowFeature.Equality | RowFeature.CompareToSelect | RowFeature.In |
RowFeature.Update | RowFeature.Overlaps;
if (version >= OracleVersion.v12)
{
SqlProviderFlags.IsApplyJoinSupported = true;
}
SqlProviderFlags.MaxInListValuesCount = 1000;
SetCharField("Char", (r, i) => r.GetString(i).TrimEnd(' '));
SetCharField("NChar", (r, i) => r.GetString(i).TrimEnd(' '));
SetCharFieldToType <char>("Char", DataTools.GetCharExpression);
SetCharFieldToType <char>("NChar", DataTools.GetCharExpression);
if (version == OracleVersion.v11)
{
_sqlOptimizer = new Oracle11SqlOptimizer(SqlProviderFlags);
}
else
{
_sqlOptimizer = new Oracle12SqlOptimizer(SqlProviderFlags);
}
foreach (var(type, method) in Adapter.CustomReaders)
{
SetProviderField(type, type, method, dataReaderType: Adapter.DataReaderType);
}
if (Adapter.OracleTimeStampTZType != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(DateTimeOffset), ProviderFieldType = Adapter.OracleTimeStampTZType, DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadDateTimeOffsetFromOracleTimeStampTZ;
}
else
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(DateTimeOffset), ProviderFieldType = Adapter.OracleTimeStampType, DataReaderType = Adapter.DataReaderType, DataTypeName = "TIMESTAMP WITH TIME ZONE"
}] = Adapter.ReadDateTimeOffsetFromOracleTimeStampTZ;
}
if (Adapter.ReadDateTimeOffsetFromOracleTimeStamp != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(DateTimeOffset), ProviderFieldType = Adapter.OracleTimeStampType, DataReaderType = Adapter.DataReaderType, DataTypeName = "TIMESTAMP"
}] = Adapter.ReadDateTimeOffsetFromOracleTimeStamp;
}
if (Adapter.ReadOracleDecimalToDecimalAdv != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(decimal), ProviderFieldType = Adapter.OracleDecimalType, DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadOracleDecimalToDecimalAdv;
ReaderExpressions[new ReaderInfo {
ToType = typeof(decimal), FieldType = typeof(decimal), DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadOracleDecimalToDecimalAdv;
}
if (Adapter.ReadOracleDecimalToInt != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(int), FieldType = typeof(decimal), DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadOracleDecimalToInt;
}
if (Adapter.ReadOracleDecimalToLong != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(long), FieldType = typeof(decimal), DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadOracleDecimalToLong;
}
if (Adapter.ReadOracleDecimalToDecimal != null)
{
ReaderExpressions[new ReaderInfo {
FieldType = typeof(decimal), DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadOracleDecimalToDecimal;
}
if (Adapter.ReadDateTimeOffsetFromOracleTimeStampLTZ != null)
{
ReaderExpressions[new ReaderInfo {
ToType = typeof(DateTimeOffset), ProviderFieldType = Adapter.OracleTimeStampLTZType, DataReaderType = Adapter.DataReaderType
}] = Adapter.ReadDateTimeOffsetFromOracleTimeStampLTZ;
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
if (txtConnStr.Text.Trim().Length == 0)
{
MessageBox.Show("连接字符串不能为空!");
return;
}
if (btnConnect.Text == "断开服务器连接")
{
chkCreateAssembly.Checked = false;
chkCreateAssembly_CheckedChanged(sender, e);
EnableGenEntity(false);
return;
}
RefreshConnectionStringAutoComplete();
DataSet dsTables = null;
DataSet dsViews = null;
try
{
if (radioSql.Checked || radioSql2005.Checked)
{
DbProvider dbProvider = new SqlServerProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
if (radioSql2005.Checked)
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams' order by [name]").ToDataSet();
}
else
{
dsTables = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'U' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
if (radioSql2005.Checked)
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' order by [name]").ToDataSet();
}
else
{
dsViews = DbSession.Default.FromSql("select [name] from sysobjects where xtype = 'V' and status > 0 order by [name]").ToDataSet();
}
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioOracle.Checked)
{
DbProvider dbProvider = new OracleProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
dsTables = DbSession.Default.FromSql("select table_name Name from user_tables").ToDataSet();
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
dsViews = DbSession.Default.FromSql("select view_name Name from user_views").ToDataSet();
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioAccess.Checked)
{
DbProvider dbProvider = new MsAccessProvider(txtConnStr.Text);
DbSession.SetDefault(dbProvider);
string connStr = txtConnStr.Text;
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Open(connStr);
ADODB.Recordset rsTables = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaTables }) as ADODB.Recordset;
ADODB.Recordset rsViews = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaViews }) as ADODB.Recordset;
while (!rsViews.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "VIEW")
{
views.Items.Add(rsViews.Fields["TABLE_NAME"].Value.ToString());
}
rsViews.MoveNext();
}
while (!rsTables.EOF)
{
if (rsTables.Fields["TABLE_TYPE"].Value.ToString() == "TABLE")
{
tables.Items.Add(rsTables.Fields["TABLE_NAME"].Value.ToString());
}
rsTables.MoveNext();
}
rsTables.Close();
rsViews.Close();
conn.Close();
}
else if (radioSQLite.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.SQLite, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString().ToUpper() == "TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
else if (radioMySQL.Checked)
{
DbProvider dbProvider = DbProviderFactory.CreateDbProvider(DbProviderType.MySql, txtConnStr.Text);
DbSession.SetDefault(dbProvider);
System.Data.Common.DbConnection conn = DbSession.Default.CreateConnection();
conn.Open();
DataTable table1 = conn.GetSchema("TABLES");
DataTable table2 = conn.GetSchema("VIEWS");
conn.Close();
foreach (DataRow row in table1.Rows)
{
if (row["TABLE_TYPE"].ToString() == "BASE TABLE")
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
}
foreach (DataRow row in table2.Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
}
EnableGenEntity(true);
}
catch (Exception ex)
{
EnableGenEntity(false);
MessageBox.Show(ex.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// This function return the application Id
/// </summary>
/// <returns></returns>
public string GetApplicationId()
{
OracleProvider lProvider = new OracleProvider();
return(lProvider.FetchDataFromDBUsingExecuteScaler("Select SEQ_APPLICATION_ID.Nextval from dual"));
}
public static UpdateDBResult AddProject2Cart(string mainItem, string ERPID, string cartID, string currency, string orgID)
{
UpdateDBResult udr = new UpdateDBResult();
try
{
DataTable dt = DataCore.CBOMV2_ConfiguratorDAL.ExpandBOM(mainItem, "TWH1");
if (dt == null || dt.Rows.Count == 0)
{
udr.IsUpdated = false;
udr.ServerMessage = "No data";
return(udr);
}
DataTable dt2 = OracleProvider.GetDataTable("SAP_PRD", string.Format("select distinct mast.matnr as Parent_item, stpo.idnrk as child_item, stpo.potx1 as memo from saprdp.mast inner join saprdp.stas on stas.stlal = mast.stlal AND stas.stlnr = mast.stlnr INNER JOIN saprdp.stpo on stpo.stlkn = stas.stlkn AND stpo.stlnr = stas.stlnr AND stpo.stlty = stas.stlty where stas.LKENZ<>'X' and mast.matnr='{0}'", mainItem));
List <string> exclude = new List <string>();
if (dt2 != null && dt2.Rows.Count > 0)
{
foreach (DataRow dr in dt2.Rows)
{
string pn = CBOMV2_EditorDAL.FormatSAPPartNoToNormal(dr["child_item"].ToString().Trim());
string m = dr["memo"].ToString();
if ((m.IndexOf("耗材") > -1 || m.IndexOf("客供") > -1) && !exclude.Contains(pn))
{
exclude.Add(pn);
}
}
}
List <ConfiguredItems> items = new List <ConfiguredItems>();
string prjOriginalPartNo = mainItem; //保存原始料號 Ex. CM-10A3-T4A719901
string[] pitem = mainItem.ToString().Split('-');
if (pitem.Length != 3)
{
udr.IsUpdated = false;
udr.ServerMessage = string.Format("{0} does not have BTO parent item", mainItem);
return(udr);
}
mainItem = string.Format("{0}-{1}-BTO", pitem[0], pitem[1]);
if (CheckItemOrdereable(mainItem, orgID) == false)
{
udr.IsUpdated = false;
udr.ServerMessage = "BTO item can not be sold";
return(udr);
}
object mainItemDesc = SqlProvider.dbExecuteScalar("MY", string.Format("SELECT TOP 1 PRODUCT_DESC FROM SAP_PRODUCT WHERE PART_NO ='{0}'", mainItem));
ConfiguredItems mi = new ConfiguredItems();
mi.name = mainItem;
mi.desc = mainItemDesc != null?mainItemDesc.ToString() : string.Empty;
mi.qty = 1;
mi.category = prjOriginalPartNo;
items.Add(mi);
foreach (DataRow dr in dt.Rows)
{
string pn = CBOMV2_EditorDAL.FormatSAPPartNoToNormal(dr["IDNRK"].ToString().Trim());
if (!exclude.Contains(pn))
{
if (CheckItemOrdereable(pn, orgID) == false)
{
udr.IsUpdated = false;
udr.ServerMessage = string.Format("{0} can not be sold", pn);
return(udr);
}
double qty = 1;
double.TryParse(dr["MENGE"].ToString().Trim(), out qty);
int q = Convert.ToInt32(Math.Floor(qty));
ConfiguredItems item = new ConfiguredItems();
item.name = pn;
item.desc = dr["OJTXP"].ToString().Trim();
item.qty = q;
items.Add(item);
}
}
//ICC 20170731 中科組裝單要在最後附上 assembly part No.- 目前鎖定 AGS-CTOS-SYS-C
ConfiguredItems ags = new ConfiguredItems();
object agsDesc = SqlProvider.dbExecuteScalar("MY", "SELECT TOP 1 PRODUCT_DESC FROM SAP_PRODUCT WHERE PART_NO ='AGS-CTOS-SYS-C'");
ags.name = "AGS-CTOS-SYS-C";
ags.desc = agsDesc != null?agsDesc.ToString() : string.Empty;
ags.qty = 1;
items.Add(ags);
if (string.IsNullOrEmpty(prjOriginalPartNo))
{
udr.IsUpdated = false;
udr.ServerMessage = "Original part No. is null";
return(udr);
}
udr = Configurator2Cart(Newtonsoft.Json.JsonConvert.SerializeObject(items), ERPID, cartID, currency, orgID, true);
return(udr);
}
catch (Exception ex)
{
udr.IsUpdated = false;
udr.ServerMessage = ex.Message;
return(udr);
}
}
/// <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 static IDbProvider AutoDetectProvider(IConfigurationRoot configuration, ILogger logger)
{
IDbProvider provider = null;
bool createDb = "true".Equals(configuration["DWKit:CreateDatabaseObjects"], StringComparison.InvariantCultureIgnoreCase);
try
{
using (new System.Data.SqlClient.SqlConnection(DWKitRuntime.ConnectionStringData))
{ }
provider = new SQLServerProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new MSSQL.DbCreator(DWKitRuntime.ConnectionStringData), "MSSQL", logger);
}
}
catch (ArgumentException)
{
}
if (provider == null)
{
try
{
using (IDbConnection connection = new Npgsql.NpgsqlConnection(DWKitRuntime.ConnectionStringData)) { }
provider = new PostgreSqlProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new PostgreSQL.DbCreator(DWKitRuntime.ConnectionStringData), "PostgreSql", logger);
}
}
catch (ArgumentException)
{
}
}
if (provider == null)
{
try
{
using (IDbConnection connection = new OracleConnection(DWKitRuntime.ConnectionStringData)) { }
provider = new OracleProvider();
if (createDb)
{
CreateDatabaseIfNotExists(configuration, new Oracle.DbCreator(DWKitRuntime.ConnectionStringData), "Oracle", logger);
}
}
catch (ArgumentException)
{
}
}
if (provider == null)
{
throw new Exception($"Can't autodetect provider for connection string: {DWKitRuntime.ConnectionStringData}");
}
return(provider);
}
public void Constructor_should_accept_a_string_parameter()
{
// Act
OracleProvider db = new OracleProvider(@"DATA SOURCE=DWQA;PERSIST SECURITY INFO=True;USER ID=ICRFSLDV;PASSWORD=icrfsldv;Pooling=FALSE", "ICRFSLDV");
}
private WorkflowRuntime CreateRuntimeOracle()
{
var provider = new OracleProvider(Parameters.ConnectionString);
callbackProvider = new WorkflowCallbackProvider(Parameters, provider);
var builder = new WorkflowBuilder<XElement>(callbackProvider, new XmlWorkflowParser(), provider).WithDefaultCache();
return new WorkflowRuntime(Parameters.RuntimeId)
.WithBuilder(builder)
.WithPersistenceProvider(provider);
}
public PersistenceProviderContainer()
{
var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
_provider = new OracleProvider(connectionString);
}
public DataGateway(OracleProvider provider = OracleProvider.OracleClient)
{
Provider = provider;
}
public FormOracleTest()
{
InitializeComponent();
this.provider = this.provider_def;
this.comboBox_Mode.SelectedIndex = 0;
}
/// <summary>
/// Configure connection to use specific Oracle provider, dialect and connection string.
/// </summary>
/// <param name="builder">Instance of <see cref="LinqToDBConnectionOptionsBuilder"/>.</param>
/// <param name="connectionString">Oracle connection string.</param>
/// <param name="dialect">Oracle dialect support level.</param>
/// <param name="provider">ADO.NET provider to use.</param>
/// <returns>The builder instance so calls can be chained.</returns>
public static LinqToDBConnectionOptionsBuilder UseOracle(this LinqToDBConnectionOptionsBuilder builder, string connectionString, OracleVersion dialect, OracleProvider provider)
{
return(builder.UseConnectionString(
OracleTools.GetDataProvider(dialect, provider),
connectionString));
}
public PersistenceProviderContainer(IConfiguration config)
{
_provider = new OracleProvider(config.GetConnectionString("DefaultConnection"));
}
private static OracleVersion DetectProviderVersion(IConnectionStringSettings css, string connectionString, OracleProvider provider)
{
try
{
var cs = string.IsNullOrWhiteSpace(connectionString) ? css.ConnectionString : connectionString;
var providerAdapter = OracleProviderAdapter.GetInstance(provider);
using (var conn = providerAdapter.CreateConnection(cs))
{
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "SELECT VERSION from PRODUCT_COMPONENT_VERSION WHERE ROWNUM = 1";
if (command.ExecuteScalar() is string result)
{
var version = int.Parse(result.Split('.')[0]);
if (version <= 11)
{
return(OracleVersion.v11);
}
return(OracleVersion.v12);
}
return(DefaultVersion);
}
}
catch
{
return(DefaultVersion);
}
}
