金蝶调用数据库sql 写法

// 调用数据库方法 方法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_B