public string Execute(string itemID, string srlnCode) { string retVal = String.Empty; var qry = new SelectQuery(); var srln = FromTerm.Table("GXSERIALNUMBER", "srnl"); qry.FromClause.BaseTable =srln; qry.Columns.Add(new SelectColumn("GXID", srln)); qry.WherePhrase.Terms.Add(WhereTerm.CreateCompare( SqlExpression.Field("GXITEMID", srln), SqlExpression.Parameter("@itemID"), CompareOperator.Equal)); qry.WherePhrase.Terms.Add(WhereTerm.CreateCompare( SqlExpression.Field("GXCODE"), SqlExpression.Parameter("@srlnCode"), CompareOperator.Equal)); var dataAccess = AppContext.ServiceLocator.GetService<IslDataAccessDbProvider>(); string sqlText = slRendererFactory.GetRenderer(dataAccess.Info.DBProviderName).RenderSelect(qry); slQueryParameters qryParams = new slQueryParameters(); qryParams.Add("@itemID", itemID); qryParams.Add("@srlnCode", srlnCode); object res = dataAccess.ExecuteScalar(sqlText, qryParams); if (res != null && res != DBNull.Value) retVal = Convert.ToString(res); return retVal; }
// Helper private string GetMembID(string custID) { string retVal = string.Empty; string sql = @"SELECT MEMB.CMID FROM CMMEMBERSHIPS MEMB INNER JOIN CMCONTACTS CONT ON MEMB.CMISSUEDFORCONTACTID = CONT.CMID INNER JOIN GXTRADER TRDR ON CONT.CMERPLINKID = TRDR.GXID INNER JOIN GXCUSTOMER CUST ON TRDR.GXID = CUST.GXTRDRID WHERE CUST.GXID = @CUSTID"; var prx = _appContext.ServiceLocator.GetService<IslObjectProxyActivator>(); using (var db = prx.GetObjectProxy<IslDataAccessProxy>("Sand:DataAccessProxy", false)) { var prm = new slQueryParameters("@CUSTID", custID); object res = db.ExecuteScalar(sql, prm); if (res != null && res != DBNull.Value) retVal = Convert.ToString(res); } return retVal; }