摘要由AI智能服务提供本文介绍了四种调用数据库的方法，每种方法通过构建不同的SQL查询语句来执行特定的数据操作。方法1通过拼接SQL语句查询特定条件下的数据；方法2查询物料BOM信息；方法3查询指定日期范围内的数据并按供应商名称排序；方法4通过递归查询遍历金蝶中的物料BOM，并处理金蝶不支持的语法问题。每种方法都使用了不同的SQL语句和参数，并通过DBUtils或DBServiceHelper类执行查询，返回查询结果。
// 调用数据库方法 方法1:
var ydlParams = StringUtils.GetSqlWithCardinalityAndParam(mtonos, "@YDLPARAMS", 2, true);
var Dys = DBServiceHelper.ExecuteDynamicObject(this.Context,
string.Format(@"SELECT DISTINCT A.FID FROM {0} FF INNER JOIN T_BAS_NEWPREBDTHREE A ON FF.FID = A.F_JHGZH WHERE A.fuseorgid = @fuseorgid ", ydlParams.Item1),
null, null, CommandType.Text, new SqlParam[] { ydlParams.Item2, new SqlParam("@fuseorgid", KDDbType.Int64, this.View.Model.DataObject["SaleOrgId_Id"]) });
if (Dys.Count() == 0)
return;
// 调用数据库方法 方法2:
string wlid = Convert.ToString(wl["Id"]);
string sql = $@"select t2.FMATERIALID as FMATERIALID from T_ENG_BOM t1
left join T_ENG_BOMCHILD t2 on t1.fid = t2.fid
where t1.FMATERIALID = {wlid}";
DynamicObjectCollection doc = DBUtils.ExecuteDynamicObject(this.Context, sql);
// 调用数据库方法 方法3:
string sql = string.Format(@"SELECT * FROM YDL_WWJGF WHERE 转入日期 >= @firstDayOfMonth AND 转入日期 <= @lastDayOfMonth ORDER BY 供应商名称");
var a1 = DBUtils.ExecuteDynamicObject(Context, sql, null, null, System.Data.CommandType.Text, new SqlParam[] {
new SqlParam("@firstDayOfMonth", KDDbType.Date, firstDayOfMonth),
new SqlParam("@lastDayOfMonth", KDDbType.Date, lastDayOfMonth)
});
// 调用数据库方法 方法4：
// 该方法可以遍历金蝶中物料的BOM ，按物料BOM 去展示，因为有些语法金蝶不支持，所以要添加/*dialect*/
string sqls = string.Format(@"/*dialect*/
with bom (物料内码,物料编码,母件新品状态,物料名称,BOM版本,子项物料,层级,fentryid,子件图纸号,子件新品状态,物料属性,母件物料属性,默认供应商,母件默认供应商) as
(select s1.物料内码,s1.物料编码,s1.母件新品状态,s1.物料名称,s1.BOM版本,s1.子项物料,s1.层级,s1.fentryid,s1.子件图纸号,s1.子件新品状态,s1.物料属性,s1.母件物料属性,s1.默认供应商,s1.母件默认供应商
from
(select t1.fmaterialid 物料内码,t1.fnumber 物料编码,t1.F_YDL_XPZT 母件新品状态,t2.fname 物料名称,t3.fnumber BOM版本,t4.FMATERIALID 子项物料,1 as 层级,t4.fentryid,t5.F_TZH 子件图纸号,t5.F_YDL_XPZT 子件新品状态,t6.FERPCLSID 物料属性,t7.FERPCLSID 母件物料属性,t9.FNUMBER 默认供应商,t11.FNUMBER 母件默认供应商
from t_bd_material t1
left join t_bd_material_l t2 on t1.fmaterialid = t2.fmaterialid
left join T_ENG_BOM t3 on t3.fmaterialid = t1.fmaterialid
left join T_ENG_BOMCHILD t4 on t4.fid = t3.fid
left join t_bd_material t5 on t4.FMATERIALID = t5.FMATERIALID
left join T_BD_MATERIALBASE t6 on t4.fmaterialid = t6.fmaterialid
left join T_BD_MATERIALBASE t7 on t1.fmaterialid = t7.fmaterialid
left join T_BD_MATERIALPURCHASE t8 on t4.fmaterialid = t8.fmaterialid
left join T_BD_SUPPLIER t9 on t8.FDEFAULTVENDORID = t9.FSUPPLIERID
left join T_BD_MATERIALPURCHASE t10 on t1.fmaterialid = t10.fmaterialid
left join T_BD_SUPPLIER t11 on t10.FDEFAULTVENDORID = t11.FSUPPLIERID) s1
where s1.物料内码 = {0} and s1.bom版本 not like '%跳层%'
union all
select s2.物料内码,s2.物料编码,s2.母件新品状态,s2.物料名称,s2.BOM版本,s2.子项物料,c.层级+1 层级,s2.fentryid,s2.子件图纸号,s2.子件新品状态,s2.物料属性,s2.母件物料属性,s2.默认供应商,s2.母件默认供应商 from
(select t1.fmaterialid 物料内码,t1.fnumber 物料编码,t1.F_YDL_XPZT 母件新品状态,t2.fname 物料名称,t3.fnumber BOM版本,t4.FMATERIALID 子项物料,t4.fentryid,t5.F_TZH 子件图纸号,t5.F_YDL_XPZT 子件新品状态,t6.FERPCLSID 物料属性,t7.FERPCLSID 母件物料属性,t9.FNUMBER 默认供应商,t11.FNUMBER 母件默认供应商
from t_bd_material t1
left join t_bd_material_l t2 on t1.fmaterialid = t2.fmaterialid
left join T_ENG_BOM t3 on t3.fmaterialid = t1.fmaterialid
left join T_ENG_BOMCHILD t4 on t4.fid = t3.fid
left join t_bd_material t5 on t4.FMATERIALID = t5.FMATERIALID
left join T_BD_MATERIALBASE t6 on t4.fmaterialid = t6.fmaterialid
left join T_BD_MATERIALBASE t7 on t1.fmaterialid = t7.fmaterialid
left join T_BD_MATERIALPURCHASE t8 on t4.fmaterialid = t8.fmaterialid
left join T_BD_SUPPLIER t9 on t8.FDEFAULTVENDORID = t9.FSUPPLIERID
left join T_BD_MATERIALPURCHASE t10 on t1.fmaterialid = t10.fmaterialid
left join T_BD_SUPPLIER t11 on t10.FDEFAULTVENDORID = t11.FSUPPLIERID) s2,bom c
where s2.物料内码 = c.子项物料 and s2.Bom版本 not like '%跳层%' and s2.母件物料属性 <> 1 AND (s2.母件默认供应商 <> 'CY22' OR s2.母件默认供应商 IS NULL)
)
select * from bom", item);
var fmaterialid = DBUtils.ExecuteDynamicObject(Context, sqls, null, null, System.Data.CommandType.Text);
赞 91
打赏推荐阅读