using the SQL item type as a view

Question

Querying a SQL view I built in SSMS from an Aras server method: 
 string SQL_View = "innovator.my_custom_sql_view"; 
 string SQL = "select * from " + SQL_View; Item SQL_qry = inn.applySQL(SQL); 
 This is quick for me because I do a lot in SQL, but I know it is bad practice. So, I want to replace this SQL injection using IOM: 
 I created a new SQL item, type=View, with the contents from the custom SSMS view. 
 What is the correct C# code to get the view results? 
 Item SQL_qry = inn.newItem("SQL", "get"); SQL_qry.setProperty("keyed_name", SQL_View); 
 SQL_qry = SQL_qry.apply(); 
 This code just gets the SQL item, not the contents of the View. 
 I couldn't find anything in the Programmers guide for this, and the Blog posts were only about Stored Procedures, not Views. 
 I'm thinking it should work the same way, but I haven't figured out the correct IOM syntax. 
 
 Thanks, 
 Paul Sheehy

Gopikrishnan · Accepted Answer

Hi Paul 
 You can use below method to get the required view. I used item_number of NCMR as ID is unique for each item type 
 Innovator inn = this.getInnovator(); //Verify the Item type name in system var ncmrItem = inn.newItem("NCMR MANAGEMENT", "get"); ncmrItem.setAttribute("select", "id,item_number"); ncmrItem = ncmrItem.apply(); if (ncmrItem.isError()) { return inn.newError("No NCMR Item Found"); } else { var ncmrIdList = new List < string > (); for (int i = 0; i < ncmrItem.getItemCount(); i++) { var ncmrOneItem = ncmrItem.getItemByIndex(i); //I'm using item_number here as ID is unique for each item type and we might not have same ID used for Part and NCMR. If you want to use ID, then change item_number to id ncmrIdList.Add("'" + ncmrOneItem.getProperty("item_number", string.Empty) + "'"); } var partView = inn.newItem("Part", "get"); partView.setAttribute("select", "id,item_number"); partView.setProperty("is_current", "1"); partView.setProperty("state", "Released"); partView.setProperty("item_number", string.Join(",", ncmrIdList)); // Change it to ID if you want to use ID instead item_number partView.setPropertyAttribute("item_number", "condition", "not in"); // Change it to ID if you want to use ID instead item_number partView = partView.apply(); if (partView.isError()) { return inn.newError("No Part item found using this criteria"); } else { return partView; } } 
 We can SQL also but it is good practice to use AML as SQL directly interacts with tables and other server events will not be triggered. 
 Views in database is created for Poly Item and are system created. If you want to use SQL for any complex get query, you can use stored procedure and call the SQL Process in your method

using the SQL item type as a view

Top Replies